OSS_FOSS4G 2015년 03월 12일 LX공간정보아카데미 오픈 소스 GIS 기초 과정 ㈜엔지스 윤정환(
[email protected]) 한국어 지부 www.osgeo.kr 목차 I. PostGIS 개요 I-0. Spatial Database I-1. Whati is PostGIS I-2. PostGIS Site 둘러보기 I-3. PostGIS를 지원하는 GIS 프로그램 II. PostGIS 설치 II-1. OpenGeo Suite 소개 II-2. PostGIS 설치하기 II-3. pgAdmin & Query Tool 사용하기 III. 공간데이터베이스 관리 III-1. 공간 데이터베이스 생성 III-2. 공간 데이터 생성 & 로딩 & 추출 III-3. 래스터 데이터 생성 & 로딩 IV. 공간함수 사용하기 IV-1. PostGIS Reference IV-2. 실습예제 - I IV-3. 실습예제 - II IV-4. Raster Reference IV-5. Topology V. 어플리케이션을 이용한 PostGIS 사용하기 V-1. 프로그램 설치하기 V-2. QGIS 이용한 PostGIS 사용하기 V-3. OpenJump 이용한 PostGIS 사용하기 V-4. uDig 이용한 PostGIS 사용하기 V-5. JDBC Driver 이용한 PostGIS 이용하기 V-6. .NET Driver 이용한 PostGIS 이용하기 V-7. 기타 PostGIS 이용하기 VI. PostGIS 그밖의 기능 VI-1. Tuning PostgreSQL for Spatial VI-2. Query Plans VI-3. 백업 & 복원 VI-4. pgRouting 소개 VI-5. Reference LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) I. PostGIS 개요 - 일러두기 - 본 자료는 모두 Creative Commons License CC-BY-NC을 따릅니다. 본 교재에서는 교육의 편의상 Windows 32비트 기반의 프로그램들을 위주로 설명합니다. 본 교재에서 사용하는 샘플 데이터 셋은 실제 정보와 다르므로 교육용 이외에는 사용할 수 없습니다. I-0. Spatial Database 0 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) PostGIS ? I-1. What is PostGIS 관계형 데이터베이스 PostgreSQL 확장한 공간 데이터베이스 (공간 객체 쿼리 지원) 1 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) PostGIS 특징 I-1. What is PostGIS PostgreSQL 기반 spatial types, indexes, functions의 3가지 기능을 추가한 공간 데이터베이스 Spatial databases store/manipulate spatial objects data types, indexes, and functions Spatial data types shapes - point, line, polygon Spatial indexing efficient processing of spatial operations Spatial functions, querying of spatial properties and relationships. http://www.postgis.net/ 2 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) PostGIS Site 둘러보기 I-2. PostGIS Site 둘러보기 - 1 PostGIS 관련된 Site 둘러보기 http://www.postgis.net/ 3 http://www.postgresql.org/ LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) PostGIS Site 둘러보기 I-2. PostGIS Site 둘러보기 - 2 PostGIS 관련된 Site 둘러보기 http://boundlessgeo.com/ 4 http://www.pgadmin.org/ LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) PostGIS를 지원하는 GIS 프로그램 I-3. PostGIS를 지원하는 GIS 프로그램 5 Open/Free Closed/Proprietary Loading/Extracting Shp2Pgsql ogr2ogr Dxf2PostGIS Web-Based Mapserver GeoServer (Java-based WFS / WMS -server ) SharpMap SDK - for ASP.NET 2.0 MapGuide Open Source (using FDO) Desktop uDig QGIS mezoGIS OpenJUMP OpenEV SharpMap SDK for Microsoft.NET 2.0 ZigGIS for ArcGIS/ArcObjects.NET GvSIG GRASS Loading/Extracting Safe FME Desktop Translator/Converter Web-Based Ionic Red Spider (now ERDAS) Cadcorp GeognoSIS Iwan Mapserver MapDotNet Server MapGuide Enterprise (using FDO) ESRI ArcGIS Server 9.3+ Desktop Cadcorp SIS Microimages TNTmips GIS ESRI ArcGIS 9.3+ Manifold GeoConcept MapInfo (v10) AutoCAD Map 3D (using FDO) LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II. PostGIS 설치 - 일러두기 - 본 자료는 모두 Creative Commons License CC-BY-NC을 따릅니다. 본 교재에서는 교육의 편의상 Windows 32비트 기반의 프로그램들을 위주로 설명합니다. 본 교재에서 사용하는 샘플 데이터 셋은 실제 정보와 다르므로 교육용 이외에는 사용할 수 없습니다. II-1. OpenGeo Suite 소개 OpenGeo Suite 소개 6 OpenGeo Suite https://vimeo.com/97134667 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 0 7 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 1 8 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 2 9 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 3 10 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 4 11 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 5 12 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 6 13 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 7 14 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 8 15 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-1. OpenGeo Suite 소개 OpenGeo Suite 설치 - 9 16 http://suite.opengeo.org/docs/latest/intro/installation/windows/install.html#intro-installation-windows-install LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-2. PostGIS 설치하기 ① http://www.postgresql.org/download/에 접속하여 PostgreSQL 최신버전(PostgreSQL 9.4.1)을 내려 받음 ② http://download.osgeo.org/postgis/windows/에 접속하여 PostGIS 최신버전(PostGIS 2.1.5)을 내려받음 ③ [설치프로그램\postgis] 경로로 이동하여 PostgreSQL, PostGIS 최신버전을 설치 DBMS 설치 : postgresql-9.4.1-1-windows.exe, postgresql-9.4.1-1-windows-x64.exe Spatial Extension 설치 : postgis-bundle-pg94x32-setup-2.1.5-2 ④ PostgreSQL, PostGIS 기본적인 설정에 따라 설치를 완료 ⑤ [시작메뉴] – [PostgreSQL 9.4] – [pgAdmin] 을 실행, 윈도우8에서 검색 PostgreSQL 단독설치 (Application Stack Builder) - 1 PostgreSQL 설치 PostGIS 설치 pgAdmin 실행 17 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-2. PostGIS 설치하기 PostgreSQL 단독설치 (Application Stack Builder) - 2 PostgreSQL: http://www.postgresql.org/download/ 18 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-2. PostGIS 설치하기 PostgreSQL 단독설치 (Application Stack Builder) - 3 Password : postgis 19 사용자 컴퓨터의 등록정보를 확인하여 컴퓨터 이름 또는 사용자 이름이 한글일 경우 오류가 발생할 수 있으므로 변경 후 설치 권장합니다 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-2. PostGIS 설치하기 PostgreSQL 단독설치 (Application Stack Builder) - 4 6 20 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-2. PostGIS 설치하기 PostgreSQL 단독설치 (Application Stack Builder) - 5 6 21 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-2. PostGIS 설치하기 PostgreSQL 단독설치 (Application Stack Builder) - 7 6 22 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-2. PostGIS 설치하기 PostgreSQL 단독설치 (Application Stack Builder) - 8 23 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-3. pgAdmin & Query Tool 사용하기 pgAdmin 사용하기 24 메뉴 툴바 객체 브라우저 View LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-3. pgAdmin & Query Tool 사용하기 pgAdmin 사용하기 25 도구 설명 pgAdmin 도구에 새로운 서버를 추가합니다. 객체의 정보를 생성, 수정, 갱신, 삭제 후 새로고침 합니다. 테이블 등의 객체의 등록정보를 확인하거나 수정합니다. 선택된 객체와 같은 형태의 새로운 객체를 생성합니다. 선택된 객체를 삭제합니다. SQL을 실행할 수 있는 Query 도구를 불러옵니다. 선택된 테이블을 편집할 수 있는 테이블 편집기를 불러옵니다. 필터를 적용해서 테이블을 편집할 수 있는 테이블 편집기를 불러옵니다. Vacuum, Analyze 등 테이블 유지에 필요한 도구를 불러옵니다. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-3. pgAdmin & Query Tool 사용하기 pgAdmin Server 구성하기 ① [시작메뉴] – [PostgreSQL 9.4] – [pgAdmin] 을 실행 ② [File] – [Add Server] & 툴바 아이콘을 선택 ③ New Server Registration 화면에 Name, Host, Password를 입력 후 [ok] 선택 26 postgis LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) II-3. pgAdmin & Query Tool 사용하기 Query Tool 사용하기 27 ① pgAdmin Tool 툴바 아이콘을 선택 Query Tool 실행 – “SELECT version();” ② 샘플데이터 폴더의 postgis_korea_epsg_towgs84.sql을 열기 ③ [F5] 선택 Query 실행 시킴, 한국 주요 좌표계 적용 Query 다루기 좌표 등록 시키기 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) III. 공간데이터베이스 관리 - 일러두기 - 본 자료는 모두 Creative Commons License CC-BY-NC을 따릅니다. 본 교재에서는 교육의 편의상 Windows 32비트 기반의 프로그램들을 위주로 설명합니다. 본 교재에서 사용하는 샘플 데이터 셋은 실제 정보와 다르므로 교육용 이외에는 사용할 수 없습니다. III-1. 공간데이터베이스 생성 ① [시작메뉴] – [PostgreSQL 9.4] – [pgAdmin] 을 실행 ② [Databases] 오른쪽 마우스 선택 후 [New Database]를 선택 ③ [Name] – nyc 입력 ④ [Owner] – postgres 선택, [ok] 선택 ⑤ [ ] 선택, Query Tool 실행 ⑦ CREATE EXTENSION postgis; 입력, [F5] 실행 ⑧ CREATE EXTENSION postgis_topology; 입력, [F5] 실행 ⑨ SELECT postgis_full_version(); 입력, [F5] 실행 GUI를 이용한 공간데이터베이스 생성 28 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) III-2. 공간데이터 생성 & 로딩 & 추출 공간데이터 로딩 - nyc 29 ① [시작메뉴] – [PostGIS 2.1 bundle for PostgreSQL 9.4 ] – [PostGIS Shapefile and DBF Loader Exporter] 을 실행 ② [PostGIS Connection] – [View connenction details] 선택 후 아래의 내용 입력 - Username : postgres / Password : postgis /Database : nyc ③ [Options] - UTF-8 확인 후 [ok] 선택 ④ [Add File] – [02_실습예제\02_PostGIS\data\nyc] 폴더의 Shape 파일 추가 ⑤ Import List [SRID] 컬럼 26918 로 변경 입력 ⑥ [Import] 선택 Shape 파일 추가 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) III-2. 공간데이터 생성 & 로딩 & 추출 공간데이터 로딩 - korea 30 ① [시작메뉴] – [PostGIS 2.1 bundle for PostgreSQL 9.4 ] – [PostGIS Shapefile and DBF Loader Exporter] 을 실행 ② [PostGIS Connection ] – [View connenction details] 선택 후 아래의 내용 입력 - Username : postgres / Password : postgis /Database : korea ③ [Options] – encoding CP949 or EUC-KR 변경 입력 후 [ok] 선택 ④ [Add File] – [02_실습예제\02_PostGIS\data\seoul] 폴더의 Shape 파일 추가 ⑤ Import List [SRID] 컬럼 5174 로 변경 입력 ⑥ [Import] 선택 Shape 파일 추가 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) III-2. 공간데이터 생성 & 로딩 & 추출 공간데이터 추출 31 ① [시작메뉴] – [PostGIS 2.1 bundle for PostgreSQL 9.4 ] – [pgAdmin III] 을 실행 ② nyc의 Query Tool을 선택 ③ nyc_neighborhoods 테이블을 이용하여 다음 쿼리 문을 작성해 보시기 바랍니다. - 브루클린에 있는 모든 지역의 이름(name)은 ? - 브루클린에 있는 모든 지역의 이름에 있는 문자의 수는 ? - 브루클린에 있는 모든 지역의 이름에 있는 문자의 평균과 표준편차는? ④ Function List - avg(expression) - char_lengtth(string) - stddev(string) LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) III-2. 공간데이터 생성 & 로딩 & 추출 공간데이터 추출 32 ① [시작메뉴] – [PostGIS 2.1 bundle for PostgreSQL 9.4 ] – [pgAdmin III] 을 실행 ② korea의 Query Tool을 선택 ③ admin_emd 테이블과 pop2007, pop2008 컬럼을 이용하여 다음 쿼리 문을 작성해 보시기 바랍니다. - 서울특별시 2007, 2008 각 년도 인구수 총 합은 ? - 서울특별시 종로구, 서대문구, 강남구 2007, 2008 각년도 인구수의 총 합은? - 2008년 인구 중 종로구내 각 동의 인구비율은 백분율로 표시 ? ④ Function List - avg(expression) - count(expression) - sum(expression) LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) III-3. 래스터 데이터 생성 & 로딩 레스터 데이터 생성 33 ① [시작메뉴] – [PostGIS 2.1 bundle for PostgreSQL 9.4 ] – [pgAdmin III] 을 실행 ② nyc의 Query Tool을 선택 ③ PostGIS 래스터 공간함수를 사용한 래스터 테이블 생성 - CREATE TABLE myrasters(rid serial primary key, rast raster); - CREATE INDEX myrasters_rast_st_convexhull_idx ON myrasters USING gist( ST_ConvexHull(rast) ); LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) III-3. 래스터 데이터 생성 & 로딩 레스터 데이터 로딩 34 ① 윈도우 cmd 화면을 실행 ([시작메뉴] – [실행] – [cmd] 선택) ② PostgreSQL 설치경로 이동 ③ raster2pgsql –G 입력 실행 ④ 실행 예제 ⑤ http://postgis.net/docs/manual-2.1/using_raster_dataman.html#RT_Raster_Loader ⑥ C:\OpenGISEdu\postgis\data\seoul_raster\ 경로 raster 파일 올리기 Raster2pgsql raster_options_go_here raster_file someschema.sometable > out.sql LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV. 공간함수 사용하기 - 일러두기 - 본 자료는 모두 Creative Commons License CC-BY-NC을 따릅니다. 본 교재에서는 교육의 편의상 Windows 32비트 기반의 프로그램들을 위주로 설명합니다. 본 교재에서 사용하는 샘플 데이터 셋은 실제 정보와 다르므로 교육용 이외에는 사용할 수 없습니다. IV-1. PostGIS Reference reference 35 http://postgis.net/docs/manual-2.1/ http://suite.opengeo.org/opengeo-docs/dataadmin/index.html LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Management Functions (http://postgis.net/docs/manual-2.1/reference.html#Management_Functions) 36 AddGeometryColumn — Adds a geometry column to an existing table of attributes. By default uses type modifier to define rather than constraints. Pass in false for use_typmod to get old check constraint based behavior DropGeometryColumn — Removes a geometry column from a spatial table. DropGeometryTable — Drops a table and all its references in geometry_columns. PostGIS_Full_Version — Reports full postgis version and build configuration infos. PostGIS_GEOS_Version — Returns the version number of the GEOS library. PostGIS_LibXML_Version — Returns the version number of the libxml2 library. PostGIS_Lib_Build_Date — Returns build date of the PostGIS library. PostGIS_Lib_Version — Returns the version number of the PostGIS library. PostGIS_PROJ_Version — Returns the version number of the PROJ4 library. PostGIS_Scripts_Build_Date — Returns build date of the PostGIS scripts. PostGIS_Scripts_Installed — Returns version of the postgis scripts installed in this database. PostGIS_Scripts_Released — Returns the version number of the postgis.sql script released with the installed postgis lib. PostGIS_Version — Returns PostGIS version number and compile-time options. Populate_Geometry_Columns — Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints This ensures they will be registered correctly in geometry_columns view. By default will convert all geometry columns with no type modifier to ones with type modifiers. To get old behavior set use_typmod=false UpdateGeometrySRID — Updates the SRID of all features in a geometry column, geometry_columns metadata and srid. If it was enforced with constraints, the constraints will be updated with new srid constraint. If the old was enforced by type definition, the type definition will be changed. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Constructors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Constructors) 37 ST_BdPolyFromText — Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString Well-Known text representation. ST_BdMPolyFromText — Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation Well-Known text representation. ST_Box2dFromGeoHash — Return a BOX2D from a GeoHash string. ST_GeogFromText — Return a specified geography value from Well-Known Text representation or extended (WKT). ST_GeographyFromText — Return a specified geography value from Well-Known Text representation or extended (WKT). ST_GeogFromWKB — Creates a geography instance from a Well-Known Binary geometry representation (WKB) or extended Well Known Binary (EWKB). ST_GeomCollFromText — Makes a collection Geometry from collection WKT with the given SRID. If SRID is not give, it defaults to 0. ST_GeomFromEWKB — Return a specified ST_Geometry value from Extended Well-Known Binary representation (EWKB). ST_GeomFromEWKT — Return a specified ST_Geometry value from Extended Well-Known Text representation (EWKT). ST_GeometryFromText — Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText ST_GeomFromGeoHash — Return a geometry from a GeoHash string. ST_GeomFromGML — Takes as input GML representation of geometry and outputs a PostGIS geometry object LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Constructors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Constructors) 38 ST_GeomFromGeoJSON — Takes as input a geojson representation of a geometry and outputs a PostGIS geometry object ST_GeomFromKML — Takes as input KML representation of geometry and outputs a PostGIS geometry object ST_GMLToSQL — Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML ST_GeomFromText — Return a specified ST_Geometry value from Well-Known Text representation (WKT). ST_GeomFromWKB — Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID. ST_LineFromMultiPoint — Creates a LineString from a MultiPoint geometry. ST_LineFromText — Makes a Geometry from WKT representation with the given SRID. If SRID is not given, it defaults to 0. ST_LineFromWKB — Makes a LINESTRING from WKB with the given SRID ST_LinestringFromWKB — Makes a geometry from WKB with the given SRID. ST_MakeBox2D — Creates a BOX2D defined by the given point geometries. ST_3DMakeBox — Creates a BOX3D defined by the given 3d point geometries. ST_MakeLine — Creates a Linestring from point or line geometries. ST_MakeEnvelope — Creates a rectangular Polygon formed from the given minimums and maximums. Input values must be in SRS specified by the SRID. ST_MakePolygon — Creates a Polygon formed by the given shell. Input geometries must be closed LINESTRINGS. ST_MakePoint — Creates a 2D,3DZ or 4D point geometry. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Constructors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Constructors) 39 ST_MakePointM — Creates a point geometry with an x y and m coordinate. ST_MLineFromText — Return a specified ST_MultiLineString value from WKT representation. ST_MPointFromText — Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to 0. ST_MPolyFromText — Makes a MultiPolygon Geometry from WKT with the given SRID. If SRID is not give, it defaults to 0. ST_Point — Returns an ST_Point with the given coordinate values. OGC alias for ST_MakePoint. ST_PointFromGeoHash — Return a point from a GeoHash string. ST_PointFromText — Makes a point Geometry from WKT with the given SRID. If SRID is not given, it defaults to unknown. ST_PointFromWKB — Makes a geometry from WKB with the given SRID ST_Polygon — Returns a polygon built from the specified linestring and SRID. ST_PolygonFromText — Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to 0. ST_WKBToSQL — Return a specified ST_Geometry value from Well-Known Binary representation (WKB). This is an alias name for ST_GeomFromWKB that takes no srid ST_WKTToSQL — Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Accessors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Accessors) 40 GeometryType — Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTIPOINT', etc. ST_Boundary — Returns the closure of the combinatorial boundary of this Geometry. ST_CoordDim — Return the coordinate dimension of the ST_Geometry value. ST_Dimension — The inherent dimension of this Geometry object, which must be less than or equal to the coordinate dimension. ST_EndPoint — Returns the last point of a LINESTRING geometry as a POINT. ST_Envelope — Returns a geometry representing the double precision (float8) bounding box of the supplied geometry. ST_ExteriorRing — Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon. Will not work with MULTIPOLYGON ST_GeometryN — Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON, POLYHEDRALSURFACE Otherwise, return NULL. ST_GeometryType — Return the geometry type of the ST_Geometry value. ST_InteriorRingN — Return the Nth interior linestring ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range. ST_IsClosed — Returns TRUE if the LINESTRING's start and end points are coincident. For Polyhedral surface is closed (volumetric). ST_IsCollection — Returns TRUE if the argument is a collection (MULTI*, GEOMETRYCOLLECTION, ...) ST_IsEmpty — Returns true if this Geometry is an empty geometrycollection, polygon, point etc. ST_IsRing — Returns TRUE if this LINESTRING is both closed and simple. ST_IsSimple — Returns (TRUE) if this Geometry has no anomalous geometric points, such as self intersection or self tangency. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Accessors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Accessors) 41 ST_IsValid — Returns true if the ST_Geometry is well formed. ST_IsValidReason — Returns text stating if a geometry is valid or not and if not valid, a reason why. ST_IsValidDetail — Returns a valid_detail (valid,reason,location) row stating if a geometry is valid or not and if not valid, a reason why and a location where. ST_M — Return the M coordinate of the point, or NULL if not available. Input must be a point. ST_NDims — Returns coordinate dimension of the geometry as a small int. Values are: 2,3 or 4. ST_NPoints — Return the number of points (vertexes) in a geometry. ST_NRings — If the geometry is a polygon or multi-polygon returns the number of rings. ST_NumGeometries — If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, for single geometries will return 1, otherwise return NULL. ST_NumInteriorRings — Return the number of interior rings of the a polygon in the geometry. This will work with POLYGON and return NULL for a MULTIPOLYGON type or any other type ST_NumInteriorRing — Return the number of interior rings of the first polygon in the geometry. Synonym to ST_NumInteriorRings. ST_NumPatches — Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries. ST_NumPoints — Return the number of points in an ST_LineString or ST_CircularString value. ST_PatchN — Return the 1-based Nth geometry (face) if the geometry is a POLYHEDRALSURFACE, POLYHEDRALSURFACEM. Otherwise, return NULL. ST_PointN — Return the Nth point in the first linestring or circular linestring in the geometry. Return NULL if there is no linestring in the geometry. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Accessors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Accessors) 42 ST_SRID — Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table. ST_StartPoint — Returns the first point of a LINESTRING geometry as a POINT. ST_Summary — Returns a text summary of the contents of the geometry. ST_X — Return the X coordinate of the point, or NULL if not available. Input must be a point. ST_XMax — Returns X maxima of a bounding box 2d or 3d or a geometry. ST_XMin — Returns X minima of a bounding box 2d or 3d or a geometry. ST_Y — Return the Y coordinate of the point, or NULL if not available. Input must be a point. ST_YMax — Returns Y maxima of a bounding box 2d or 3d or a geometry. ST_YMin — Returns Y minima of a bounding box 2d or 3d or a geometry. ST_Z — Return the Z coordinate of the point, or NULL if not available. Input must be a point. ST_ZMax — Returns Z minima of a bounding box 2d or 3d or a geometry. ST_Zmflag — Returns ZM (dimension semantic) flag of the geometries as a small int. Values are: 0=2d, 1=3dm, 2=3dz, 3=4d. ST_ZMin — Returns Z minima of a bounding box 2d or 3d or a geometry. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Editors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Editors) 43 ST_AddPoint — Adds a point to a LineString before point (0-based index). ST_Affine — Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step. ST_Force2D — Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates. ST_Force3D — Forces the geometries into XYZ mode. This is an alias for ST_Force3DZ. ST_Force3DZ — Forces the geometries into XYZ mode. This is a synonym for ST_Force3D. ST_Force3DM — Forces the geometries into XYM mode. ST_Force4D — Forces the geometries into XYZM mode. ST_ForceCollection — Converts the geometry into a GEOMETRYCOLLECTION. ST_ForceSFS — Forces the geometries to use SFS 1.1 geometry types only. ST_ForceRHR — Forces the orientation of the vertices in a polygon to follow the Right-Hand-Rule. ST_LineMerge — Returns a (set of) LineString(s) formed by sewing together a MULTILINESTRING. ST_CollectionExtract — Given a (multi)geometry, returns a (multi)geometry consisting only of elements of the specified type. ST_CollectionHomogenize — Given a geometry collection, returns the "simplest" representation of the contents. ST_Multi — Returns the geometry as a MULTI* geometry. If the geometry is already a MULTI*, it is returned unchanged. ST_RemovePoint — Removes point from a linestring. Offset is 0-based. ST_Reverse — Returns the geometry with vertex order reversed. ST_Rotate — Rotate a geometry rotRadians counter-clockwise about an origin. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Editors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Editors) 44 ST_RotateX — Rotate a geometry rotRadians about the X axis. ST_RotateY — Rotate a geometry rotRadians about the Y axis. ST_RotateZ — Rotate a geometry rotRadians about the Z axis. ST_Scale — Scales the geometry to a new size by multiplying the ordinates with the parameters. Ie: ST_Scale(geom, Xfactor, Yfactor, Zfactor). ST_Segmentize — Return a modified geometry/geography having no segment longer than the given distance. Distance computation is performed in 2d only. For geometry, length units are in units of spatial reference. For geography, units are in meters. ST_SetPoint — Replace point N of linestring with given point. Index is 0-based. ST_SetSRID — Sets the SRID on a geometry to a particular integer value. ST_SnapToGrid — Snap all points of the input geometry to a regular grid. ST_Snap — Snap segments and vertices of input geometry to vertices of a reference geometry. ST_Transform — Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter. ST_Translate — Translates the geometry to a new location using the numeric parameters as offsets. Ie: ST_Translate(geom, X, Y) or ST_Translate(geom, X, Y,Z). ST_TransScale — Translates the geometry using the deltaX and deltaY args, then scales it using the XFactor, YFactor args, working in 2D only. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Outputs (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Outputs) 45 ST_AsBinary — Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data. ST_AsEWKB — Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data. ST_AsEWKT — Return the Well-Known Text (WKT) representation of the geometry with SRID meta data. ST_AsGeoJSON — Return the geometry as a GeoJSON element. ST_AsGML — Return the geometry as a GML version 2 or 3 element. ST_AsHEXEWKB — Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding. ST_AsKML — Return the geometry as a KML element. Several variants. Default version=2, default precision=15 ST_AsSVG — Returns a Geometry in SVG path data given a geometry or geography object. ST_AsX3D — Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML ST_GeoHash — Return a GeoHash representation of the geometry. ST_AsText — Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata. ST_AsLatLonText — Return the Degrees, Minutes, Seconds representation of the given point. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Operators (http://postgis.net/docs/manual-2.1/reference.html#Operators) 46 && — Returns TRUE if A's 2D bounding box intersects B's 2D bounding box. &&& — Returns TRUE if A's 3D bounding box intersects B's 3D bounding box. &< — Returns TRUE if A's bounding box overlaps or is to the left of B's. & — Returns TRUE if A' bounding box overlaps or is to the right of B's. — Returns TRUE if A's bounding box overlaps or is above B's. |>> — Returns TRUE if A's bounding box is strictly above B's. ~ — Returns TRUE if A's bounding box contains B's. ~= — Returns TRUE if A's bounding box is the same as B's. — Returns the distance between two points. For point / point checks it uses floating point accuracy (as opposed to the double precision accuracy of the underlying point geometry). For other geometry types the distance between the floating point bounding box centroids is returned. Useful for doing distance ordering and nearest neighbor limits using KNN gist functionality. — Returns the distance between bounding box of 2 geometries. For point / point checks it's almost the same as distance (though may be different since the bounding box is at floating point accuracy and geometries are double precision). Useful for doing distance ordering and nearest neighbor limits using KNN gist functionality. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Spatial Relationships and Measurements (http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements) 47 ST_3DClosestPoint — Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line. ST_3DDistance — For geometry type Returns the 3-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. ST_3DDWithin — For 3d (z) geometry type Returns true if two geometries 3d distance is within number of units. ST_3DDFullyWithin — Returns true if all of the 3D geometries are within the specified distance of one another. ST_3DIntersects — Returns TRUE if the Geometries "spatially intersect" in 3d - only for points and linestrings ST_3DLongestLine — Returns the 3-dimensional longest line between two geometries ST_3DMaxDistance — For geometry type Returns the 3-dimensional cartesian maximum distance (based on spatial ref) between two geometries in projected units. ST_3DShortestLine — Returns the 3-dimensional shortest line between two geometries ST_Area — Returns the area of the surface if it is a polygon or multi-polygon. For "geometry" type area is in SRID units. For "geography" area is in square meters. ST_Azimuth — Returns the north-based azimuth as the angle in radians measured clockwise from the vertical on pointA to pointB. ST_Centroid — Returns the geometric center of a geometry. ST_ClosestPoint — Returns the 2-dimensional point on g1 that is closest to g2. This is the first point of the shortest line. ST_Contains — Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A. ST_ContainsProperly — Returns true if B intersects the interior of A but not the boundary (or exterior). A does not contain properly itself, but does contain itself. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Spatial Relationships and Measurements (http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements) 48 ST_Covers — Returns 1 (TRUE) if no point in Geometry B is outside Geometry A ST_CoveredBy — Returns 1 (TRUE) if no point in Geometry/Geography A is outside Geometry/Geography B ST_Crosses — Returns TRUE if the supplied geometries have some, but not all, interior points in common. ST_LineCrossingDirection — Given 2 linestrings, returns a number between -3 and 3 denoting what kind of crossing behavior. 0 is no crossing. ST_Disjoint — Returns TRUE if the Geometries do not "spatially intersect" - if they do not share any space together. ST_Distance — For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters. ST_HausdorffDistance — Returns the Hausdorff distance between two geometries. Basically a measure of how similar or dissimilar 2 geometries are. Units are in the units of the spatial reference system of the geometries. ST_MaxDistance — Returns the 2-dimensional largest distance between two geometries in projected units. ST_Distance_Sphere — Returns minimum distance in meters between two lon/lat geometries. Uses a spherical earth and radius of 6370986 meters. Faster than ST_Distance_Spheroid ST_Distance_Spheroid, but less accurate. PostGIS versions prior to 1.5 only implemented for points. ST_Distance_Spheroid — Returns the minimum distance between two lon/lat geometries given a particular spheroid. PostGIS versions prior to 1.5 only support points. ST_DFullyWithin — Returns true if all of the geometries are within the specified distance of one another ST_DWithin — Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid), for faster check, use_spheroid=false to measure along sphere. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Spatial Relationships and Measurements (http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements) 49 ST_Equals — Returns true if the given geometries represent the same geometry. Directionality is ignored. ST_HasArc — Returns true if a geometry or geometry collection contains a circular string ST_Intersects — Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect) ST_Length — Returns the 2d length of the geometry if it is a linestring or multilinestring. geometry are in units of spatial reference and geography are in meters (default spheroid) ST_Length2D — Returns the 2-dimensional length of the geometry if it is a linestring or multi-linestring. This is an alias for ST_Length ST_3DLength — Returns the 3-dimensional or 2-dimensional length of the geometry if it is a linestring or multi-linestring. ST_Length_Spheroid — Calculates the 2D or 3D length of a linestring/multilinestring on an ellipsoid. This is useful if the coordinates of the geometry are in longitude/latitude and a length is desired without reprojection. ST_Length2D_Spheroid — Calculates the 2D length of a linestring/multilinestring on an ellipsoid. This is useful if the coordinates of the geometry are in longitude/latitude and a length is desired without reprojection. ST_3DLength_Spheroid — Calculates the length of a geometry on an ellipsoid, taking the elevation into account. This is just an alias for ST_Length_Spheroid. ST_LongestLine — Returns the 2-dimensional longest line points of two geometries. The function will only return the first longest line if more than one, that the function finds. The line returned will always start in g1 and end in g2. The length of the line this function returns will always be the same as st_maxdistance returns for g1 and g2. ST_OrderingEquals — Returns true if the given geometries represent the same geometry and points are in the same directional order. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Spatial Relationships and Measurements (http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements) 50 ST_Overlaps — Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other. ST_Perimeter — Return the length measurement of the boundary of an ST_Surface or ST_MultiSurface geometry or geography. (Polygon, Multipolygon). geometry measurement is in units of spatial reference and geography is in meters. ST_Perimeter2D — Returns the 2-dimensional perimeter of the geometry, if it is a polygon or multi-polygon. This is currently an alias for ST_Perimeter. ST_3DPerimeter — Returns the 3-dimensional perimeter of the geometry, if it is a polygon or multi-polygon. ST_PointOnSurface — Returns a POINT guaranteed to lie on the surface. ST_Project — Returns a POINT projected from a start point using a distance in meters and bearing (azimuth) in radians. ST_Relate — Returns true if this Geometry is spatially related to anotherGeometry, by testing for intersections between the Interior, Boundary and Exterior of the two geometries as specified by the values in the intersectionMatrixPattern. If no intersectionMatrixPattern is passed in, then returns the maximum intersectionMatrixPattern that relates the 2 geometries. ST_RelateMatch — Returns true if intersectionMattrixPattern1 implies intersectionMatrixPattern2 ST_ShortestLine — Returns the 2-dimensional shortest line between two geometries ST_Touches — Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect. ST_Within — Returns true if the geometry A is completely inside geometry B LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference SFCGAL Functions (http://postgis.net/docs/manual-2.1/reference.html#reference_sfcgal) 51 postgis_sfcgal_version — Returns the version of SFCGAL in use ST_Extrude — Extrude a surface to a related volume ST_StraightSkeleton — Compute a straight skeleton from a geometry ST_Orientation — Determine surface orientation ST_ForceLHR — Force LHR orientation ST_MinkowskiSum — Perform Minkowski sum ST_3DIntersection — Perform 3D intersection ST_3DArea — Computes area of 3D geometries ST_Tesselate — Perform surface Tesselation LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Processing (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Processing) 52 ST_Buffer — (T) For geometry: Returns a geometry that represents all points whose distance from this Geometry is less than or equal to distance. Calculations are in the Spatial Reference System of this Geometry. For geography: Uses a planar transform wrapper. Introduced in 1.5 support for different end cap and mitre settings to control shape. buffer_style options: quad_segs=#,endcap=round|flat|square,join=round|mitre|bevel,mitre_limit=#.# ST_BuildArea — Creates an areal geometry formed by the constituent linework of given geometry ST_Collect — Return a specified ST_Geometry value from a collection of other geometries. ST_ConcaveHull — The concave hull of a geometry represents a possibly concave geometry that encloses all geometries within the set. You can think of it as shrink wrapping. ST_ConvexHull — The convex hull of a geometry represents the minimum convex geometry that encloses all geometries within the set. ST_CurveToLine — Converts a CIRCULARSTRING/CURVEDPOLYGON to a LINESTRING/POLYGON ST_DelaunayTriangles — Return a Delaunay triangulation around the given input points. ST_Difference — Returns a geometry that represents that part of geometry A that does not intersect with geometry B. ST_Dump — Returns a set of geometry_dump (geom,path) rows, that make up a geometry g1. ST_DumpPoints — Returns a set of geometry_dump (geom,path) rows of all points that make up a geometry. ST_DumpRings — Returns a set of geometry_dump rows, representing the exterior and interior rings of a polygon. ST_FlipCoordinates — Returns a version of the given geometry with X and Y axis flipped. Useful for people who have built latitude/longitude features and need to fix them. ST_Intersection — (T) Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-1. PostGIS Reference Geometry Processing (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Processing) 53 ST_LineToCurve — Converts a LINESTRING/POLYGON to a CIRCULARSTRING, CURVED POLYGON ST_MakeValid — Attempts to make an invalid geometry valid without losing vertices. ST_MemUnion — Same as ST_Union, only memory-friendly (uses less memory and more processor time). ST_MinimumBoundingCircle — Returns the smallest circle polygon that can fully contain a geometry. Default uses 48 segments per quarter circle. ST_Polygonize — Aggregate. Creates a GeometryCollection containing possible polygons formed from the constituent linework of a set of geometries. ST_Node — Node a set of linestrings. ST_OffsetCurve — Return an offset line at a given distance and side from an input line. Useful for computing parallel lines about a center line ST_RemoveRepeatedPoints — Returns a version of the given geometry with duplicated points removed. ST_SharedPaths — Returns a collection containing paths shared by the two input linestrings/multilinestrings. ST_Shift_Longitude — Reads every point/vertex in every component of every feature in a geometry, and if the longitude coordinate is 0; Spatial Joins 연습문제 사용할 공간함수 sum(expression): aggregate to return a sum for a set of records count(expression): aggregate to return the size of a set of records ST_Area(geometry) returns the area of the polygons ST_AsText(geometry) returns WKT text ST_Contains(geometry A, geometry B) returns the true if geometry A contains geometry B ST_Distance(geometry A, geometry B) returns the minimum distance between geometry A and geometry B ST_DWithin(geometry A, geometry B, radius) returns the true if geometry A is radius distance or less from geometry B ST_GeomFromText(text) returns geometry ST_Intersects(geometry A, geometry B) returns the true if geometry A intersects geometry B ST_Length(linestring) returns the length of the linestring ST_Touches(geometry A, geometry B) returns the true if the boundary of geometry A touches geometry B ST_Within(geometry A, geometry B) returns the true if geometry A is within geometry B LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) Spatial Joins 연습문제 84 Nyc 데이터베이스 사용, 사용 테이블 nyc_census_blocks name, popn_total, boroname, geom nyc_streets name, type, geom nyc_subway_stations name, routes, geom nyc_neighborhoods name, boroname, geom ① ‘Little Italy’ 무슨 역이고, 어떤 노선 인가? - nyc_subway_stations, nyc_subway_stations ② 6-train 지나는 모든 지역은 어디인가? - nyc_subway_stations, nyc_subway_stations ③ ‘Battery Park’ 의 총인구? - nyc_subway_stations, nyc_subway_stations ④ ‘Upper West Side’, ‘Upper East Side’ 인구밀도는 ? - nyc_subway_stations, nyc_subway_stations IV-3-2. Spatial Relationships / Joins / Indexing / Projecting LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) Spatial Indexing 85 ① Spatial Indexing 테스트 - DROP INDEX nyc_census_blocks_geom_gist; - SELECT blocks.blkid FROM nyc_census_blocks blocks JOIN nyc_subway_stations subways ON ST_Contains(blocks.geom, subways.geom) WHERE subways.name = 'Broad St'; - Query 시간 확인 - CREATE INDEX nyc_census_blocks_geom_gist ON nyc_census_blocks USING GIST (geom); - 위의 Select 문을 실행 시켜 Query 시간을 확인 ② Index-Only Queris - PostGIS index 내장 공간함수 제공 (ST_Contains, ST_Intersects, ST_DWithin, etc) - Index 와 ‘&&’ 연산자 SELECT Sum(popn_total) FROM nyc_neighborhoods neighborhoods JOIN nyc_census_blocks blocks ON neighborhoods.geom && blocks.geom WHERE neighborhoods.name = 'West Village'; SELECT Sum(popn_total) FROM nyc_neighborhoods neighborhoods JOIN nyc_census_blocks blocks ON ST_Intersects(neighborhoods.geom, blocks.geom) WHERE neighborhoods.name = 'West Village'; IV-3-2. Spatial Relationships / Joins / Indexing / Projecting LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-3-2. Spatial Relationships / Joins / Indexing / Projecting Projecting 연습문제 사용할 공간함수 86 sum(expression) aggregate to return a sum for a set of records ST_Length(linestring) returns the length of the linestring ST_SRID(geometry, srid) returns the SRID of the geometry ST_Transform(geometry, srid) converts geometries into different spatial reference systems ST_GeomFromText(text) returns geometry ST_AsText(geometry) returns WKT text ST_AsGML(geometry) returns GML text Nyc 데이터베이스 사용, 사용 테이블 nyc_census_blocks name, popn_total, boroname, geom nyc_streets name, type, geom nyc_subway_stations name, geom nyc_neighborhoods name, boroname, geom ① 뉴욕시 모든 도로 길이는 ? - nyc_streets ② SRID 2831로 변환된 뉴욕시 모든 도로 길이는? - nyc_streets ③ ‘Broad St’ subway station을 KML 표시 ? - nyc_subway_stations Projecting 연습문제 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-3-3. Geograpy Gemotry & Geograpy 87 ① 거리 구하기 - SELECT ST_Distance( ST_GeometryFromText('POINT(-118.4079 33.9434)', 4326), -- Los Angeles (LAX) ST_GeometryFromText('POINT(2.5559 49.0083)', 4326) -- Paris (CDG) ); - SELECT ST_Distance( ST_GeographyFromText('POINT(-118.4079 33.9434)'), -- Los Angeles (LAX) ST_GeographyFromText('POINT(2.5559 49.0083)') -- Paris (CDG) ); SELECT ST_Distance( ST_GeographyFromText('LINESTRING(-118.4079 33.9434, 2.5559 49.0083)'), -- LAX-CDG ST_GeographyFromText('POINT(-21.8628 64.1286)') -- Iceland ); - SELECT ST_Distance( ST_GeometryFromText('Point(-118.4079 33.9434)'), -- LAX ST_GeometryFromText('Point(139.733 35.567)')) -- NRT (Tokyo/Narita) AS geometry_distance, ST_Distance( ST_GeographyFromText('Point(-118.4079 33.9434)'), -- LAX ST_GeographyFromText('Point(139.733 35.567)')) -- NRT (Tokyo/Narita) AS geography_distance; LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-3-3. Geograpy Gemotry & Geograpy 88 ② Geograpy 테이블 생성 -CREATE TABLE airports ( code VARCHAR(3), geog GEOGRAPHY(Point) ); INSERT INTO airports VALUES ('LAX', 'POINT(-118.4079 33.9434)'); INSERT INTO airports VALUES ('CDG', 'POINT(2.5559 49.0083)'); INSERT INTO airports VALUES ('REK', 'POINT(-21.8628 64.1286)'); - SELECT * FROM geography_columns; ③ Geometry 변환 - SELECT code, ST_X(geog::geometry) AS longitude FROM airports; LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-3-4. Geometry Constructing Functions ST_Centroid / ST_PointOnSurface 89 ST_Buffer CREATE TABLE liberty_island_zone AS SELECT ST_Buffer(geom,500)::geometry(Polygon,26918) AS geom FROM nyc_census_blocks WHERE blkid = '360610001001001'; LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-3-4. Geometry Constructing Functions ST_Intersection 90 SELECT ST_AsText(ST_Intersection( ST_Buffer('POINT(0 0)', 2), ST_Buffer('POINT(3 0)', 2) ) ); SELECT ST_AsText(ST_Union( ST_Buffer('POINT(0 0)', 2), ST_Buffer('POINT(3 0)', 2) ) ); CREATE TABLE nyc_census_counties AS SELECT ST_Union(geom)::Geometry(MultiPolygon,26918) AS geom, SubStr(blkid,1,5) AS countyid FROM nyc_census_blocks GROUP BY countyid; SELECT SubStr(blkid,1,5) AS countyid, Sum(ST_Area(geom)) AS area FROM nyc_census_blocks GROUP BY countyid; SELECT countyid, ST_Area(geom) AS area FROM nyc_census_counties; SELECT countyid, ST_AsText(geom) FROM nyc_census_counties; ST_Union LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-3-5. More Spatial Joins Census Tracts Table 생성 91 ① pgAdim 실행 – Query Tool 실행 ② C:\OpenGISEdu\postgis\data\nyc 폴더 nyc_census_sociodata.sql 파일 열기 ③ [F5] 실행 Census tracts 테이블 생성 ④ Refresh 선택, 테이블 추가 확인 ⑤ ST_Union을 이용한 새로운 테이블 생성 - CREATE TABLE nyc_census_tract_geoms AS SELECT ST_Union(geom) AS geom, SubStr(blkid,1,11) AS tractid FROM nyc_census_blocks GROUP BY tractid; -- Index the tractid CREATE INDEX nyc_census_tract_geoms_tractid_idx ON nyc_census_tract_geoms (tractid); ⑥ Join을 통한 속성정보 추가 테이블 생성 - CREATE TABLE nyc_census_tracts AS SELECT g.geom, a.* FROM nyc_census_tract_geoms g JOIN nyc_census_sociodata a ON g.tractid = a.tractid; -- Index the geometries CREATE INDEX nyc_census_tract_gidx ON nyc_census_tracts USING GIST (geom); 뉴욕 지역의 graduate degrees 사람 비율이 높은 10개 지역은 ? SELECT Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct, n.name, n.boroname FROM nyc_neighborhoods n JOIN nyc_census_tracts t ON ST_Intersects(n.geom, t.geom) WHERE t.edu_total > 0 GROUP BY n.name, n.boroname ORDER BY graduate_pct DESC LIMIT 10; 문제 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-3-5. More Spatial Joins Polygon/Polygon Joins 92 SELECT Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct, n.name, n.boroname FROM nyc_neighborhoods n JOIN nyc_census_tracts t ON ST_Contains(n.geom, ST_Centroid(t.geom)) WHERE t.edu_total > 0 GROUP BY n.name, n.boroname ORDER BY graduate_pct DESC LIMIT 10; ① SELECT Sum(popn_total) FROM nyc_census_blocks; ② SELECT Sum(popn_total) FROM nyc_census_blocks census JOIN nyc_subway_stations subway ON ST_DWithin(census.geom, subway.geom, 500); ③ WITH distinct_blocks AS ( SELECT DISTINCT ON (blkid) popn_total FROM nyc_census_blocks census JOIN nyc_subway_stations subway ON ST_DWithin(census.geom, subway.geom, 500) ) SELECT Sum(popn_total) FROM distinct_blocks; 큰 반경 거리 Join LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Management (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Management_Functions) 93 AddRasterConstraints — Adds raster constraints to a loaded raster table for a specific column that constrains spatial ref, scaling, blocksize, alignment, bands, band type and a flag to denote if raster column is regularly blocked. The table must be loaded with data for the constraints to be inferred. Returns true of the constraint setting was accomplished and if issues a notice. DropRasterConstraints — Drops PostGIS raster constraints that refer to a raster table column. Useful if you need to reload data or update your raster column data. PostGIS_Raster_Lib_Build_Date — Reports full raster library build date. PostGIS_Raster_Lib_Version — Reports full raster version and build configuration infos. ST_GDALDrivers — Returns a list of raster formats supported by your lib gdal. These are the formats you can output your raster using ST_AsGDALRaster. UpdateRasterSRID — Change the SRID of all rasters in the user-specified column and table. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Constructors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Constructors) 94 ST_AddBand — Returns a raster with the new band(s) of given type added with given initial value in the given index location. If no index is specified, the band is added to the end. ST_AsRaster — Converts a PostGIS geometry to a PostGIS raster. ST_Band — Returns one or more bands of an existing raster as a new raster. Useful for building new rasters from existing rasters. ST_MakeEmptyRaster — Returns an empty raster (having no bands) of given dimensions (width & height), upperleft X and Y, pixel size and rotation (scalex, scaley, skewx & skewy) and reference system (srid). If a raster is passed in, returns a new raster with the same size, alignment and SRID. If srid is left out, the spatial ref is set to unknown (0). ST_Tile — Returns a set of rasters resulting from the split of the input raster based upon the desired dimensions of the output rasters. ST_FromGDALRaster — Returns a raster from a supported GDAL raster file. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Accessors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Accessors) 95 ST_GeoReference — Returns the georeference meta data in GDAL or ESRI format as commonly seen in a world file. Default is GDAL. ST_Height — Returns the height of the raster in pixels. ST_IsEmpty — Returns true if the raster is empty (width = 0 and height = 0). Otherwise, returns false. ST_MetaData — Returns basic meta data about a raster object such as pixel size, rotation (skew), upper, lower left, etc. ST_NumBands — Returns the number of bands in the raster object. ST_PixelHeight — Returns the pixel height in geometric units of the spatial reference system. ST_PixelWidth — Returns the pixel width in geometric units of the spatial reference system. ST_ScaleX — Returns the X component of the pixel width in units of coordinate reference system. ST_ScaleY — Returns the Y component of the pixel height in units of coordinate reference system. ST_RasterToWorldCoord — Returns the raster's upper left corner as geometric X and Y (longitude and latitude) given a column and row. Column and row starts at 1. ST_RasterToWorldCoordX — Returns the geometric X coordinate upper left of a raster, column and row. Numbering of columns and rows starts at 1. ST_RasterToWorldCoordY — Returns the geometric Y coordinate upper left corner of a raster, column and row. Numbering of columns and rows starts at 1. ST_Rotation — Returns the rotation of the raster in radian. ST_SkewX — Returns the georeference X skew (or rotation parameter). ST_SkewY — Returns the georeference Y skew (or rotation parameter). LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Accessors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Accessors) 96 ST_SRID — Returns the spatial reference identifier of the raster as defined in spatial_ref_sys table. ST_Summary — Returns a text summary of the contents of the raster. ST_UpperLeftX — Returns the upper left X coordinate of raster in projected spatial ref. ST_UpperLeftY — Returns the upper left Y coordinate of raster in projected spatial ref. ST_Width — Returns the width of the raster in pixels. ST_WorldToRasterCoord — Returns the upper left corner as column and row given geometric X and Y (longitude and latitude) or a point geometry expressed in the spatial reference coordinate system of the raster. ST_WorldToRasterCoordX — Returns the column in the raster of the point geometry (pt) or a X and Y world coordinate (xw, yw) represented in world spatial reference system of raster. ST_WorldToRasterCoordY — Returns the row in the raster of the point geometry (pt) or a X and Y world coordinate (xw, yw) represented in world spatial reference system of raster. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Band Accessors (http://postgis.net/docs/manual-2.1/RT_reference.html#RasterBand_Accessors) 97 ST_BandMetaData — Returns basic meta data for a specific raster band. band num 1 is assumed if none-specified. ST_BandNoDataValue — Returns the value in a given band that represents no data. If no band num 1 is assumed. ST_BandIsNoData — Returns true if the band is filled with only nodata values. ST_BandPath — Returns system file path to a band stored in file system. If no bandnum specified, 1 is assumed. ST_BandPixelType — Returns the type of pixel for given band. If no bandnum specified, 1 is assumed. ST_HasNoBand — Returns true if there is no band with given band number. If no band number is specified, then band number 1 is assumed. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Pixel Accessors and Setters (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Pixel_Accessors) 98 ST_PixelAsPolygon — Returns the polygon geometry that bounds the pixel for a particular row and column. ST_PixelAsPolygons — Returns the polygon geometry that bounds every pixel of a raster band along with the value, the X and the Y raster coordinates of each pixel. ST_PixelAsPoint — Returns a point geometry of the pixel's upper-left corner. ST_PixelAsPoints — Returns a point geometry for each pixel of a raster band along with the value, the X and the Y raster coordinates of each pixel. The coordinates of the point geometry are of the pixel's upper-left corner. ST_PixelAsCentroid — Returns the centroid (point geometry) of the area represented by a pixel. ST_PixelAsCentroids — Returns the centroid (point geometry) for each pixel of a raster band along with the value, the X and the Y raster coordinates of each pixel. The point geometry is the centroid of the area represented by a pixel. ST_Value — Returns the value of a given band in a given columnx, rowy pixel or at a particular geometric point. Band numbers start at 1 and assumed to be 1 if not specified. If exclude_nodata_value is set to false, then all pixels include nodata pixels are considered to intersect and return value. If exclude_nodata_value is not passed in then reads it from metadata of raster. ST_NearestValue — Returns the nearest non-NODATA value of a given band's pixel specified by a columnx and rowy or a geometric point expressed in the same spatial reference coordinate system as the raster. ST_Neighborhood — Returns a 2-D double precision array of the non-NODATA values around a given band's pixel specified by either a columnX and rowY or a geometric point expressed in the same spatial reference coordinate system as the raster. ST_SetValue — Returns modified raster resulting from setting the value of a given band in a given columnx, rowy pixel or the pixels that intersect a particular geometry. Band numbers start at 1 and assumed to be 1 if not specified. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Pixel Accessors and Setters (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Pixel_Accessors) 99 ST_SetValues — Returns modified raster resulting from setting the values of a given band. ST_DumpValues — Get the values of the specified band as a 2-dimension array. ST_PixelOfValue — Get the columnx, rowy coordinates of the pixel whose value equals the search value. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Editors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Editors) 100 ST_SetGeoReference — Set Georeference 6 georeference parameters in a single call. Numbers should be separated by white space. Accepts inputs in GDAL or ESRI format. Default is GDAL. ST_SetRotation — Set the rotation of the raster in radian. ST_SetScale — Sets the X and Y size of pixels in units of coordinate reference system. Number units/pixel width/height. ST_SetSkew — Sets the georeference X and Y skew (or rotation parameter). If only one is passed in, sets X and Y to the same value. ST_SetSRID — Sets the SRID of a raster to a particular integer srid defined in the spatial_ref_sys table. ST_SetUpperLeft — Sets the value of the upper left corner of the pixel to projected X and Y coordinates. ST_Resample — Resample a raster using a specified resampling algorithm, new dimensions, an arbitrary grid corner and a set of raster georeferencing attributes defined or borrowed from another raster. ST_Rescale — Resample a raster by adjusting only its scale (or pixel size). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor. ST_Reskew — Resample a raster by adjusting only its skew (or rotation parameters). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor. ST_SnapToGrid — Resample a raster by snapping it to a grid. New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Editors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Editors) 101 ST_Resize — Resize a raster to a new width/height ST_Transform — Reprojects a raster in a known spatial reference system to another known spatial reference system using specified resampling algorithm. Options are NearestNeighbor, Bilinear, Cubic, CubicSpline, Lanczos defaulting to NearestNeighbor. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Band Editors (http://postgis.net/docs/manual-2.1/RT_reference.html#RasterBand_Editors) 102 ST_SetBandNoDataValue — Sets the value for the given band that represents no data. Band 1 is assumed if no band is specified. To mark a band as having no nodata value, set the nodata value = NULL. ST_SetBandIsNoData — Sets the isnodata flag of the band to TRUE. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Band Statistics and Analytics (http://postgis.net/docs/manual-2.1/RT_reference.html#RasterBand_Stats) 103 ST_Count — Returns the number of pixels in a given band of a raster or raster coverage. If no band is specified defaults to band 1. If exclude_nodata_value is set to true, will only count pixels that are not equal to the nodata value. ST_Histogram — Returns a set of record summarizing a raster or raster coverage data distribution separate bin ranges. Number of bins are autocomputed if not specified. ST_Quantile — Compute quantiles for a raster or raster table coverage in the context of the sample or population. Thus, a value could be examined to be at the raster's 25%, 50%, 75% percentile. ST_SummaryStats — Returns record consisting of count, sum, mean, stddev, min, max for a given raster band of a raster or raster coverage. Band 1 is assumed is no band is specified. ST_ValueCount — Returns a set of records containing a pixel band value and count of the number of pixels in a given band of a raster (or a raster coverage) that have a given set of values. If no band is specified defaults to band 1. By default nodata value pixels are not counted. and all other values in the pixel are output and pixel band values are rounded to the nearest integer. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Outputs (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Outputs) 104 ST_AsBinary — Return the Well-Known Binary (WKB) representation of the raster without SRID meta data. ST_AsGDALRaster — Return the raster tile in the designated GDAL Raster format. Raster formats are one of those supported by your compiled library. Use ST_GDALRasters() to get a list of formats supported by your library. ST_AsJPEG — Return the raster tile selected bands as a single Joint Photographic Exports Group (JPEG) image (byte array). If no band is specified and 1 or more than 3 bands, then only the first band is used. If only 3 bands then all 3 bands are used and mapped to RGB. ST_AsPNG — Return the raster tile selected bands as a single portable network graphics (PNG) image (byte array). If 1, 3, or 4 bands in raster and no bands are specified, then all bands are used. If more 2 or more than 4 bands and no bands specified, then only band 1 is used. Bands are mapped to RGB or RGBA space. ST_AsTIFF — Return the raster selected bands as a single TIFF image (byte array). If no band is specified, then will try to use all bands. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Processing - Map Algebra (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Processing_MapAlgebra) 105 ST_Clip — Returns the raster clipped by the input geometry. If band number not is specified, all bands are processed. If crop is not specified or TRUE, the output raster is cropped. ST_ColorMap — Creates a new raster of up to four 8BUI bands (grayscale, RGB, RGBA) from the source raster and a specified band. Band 1 is assumed if not specified. ST_Intersection — Returns a raster or a set of geometry-pixelvalue pairs representing the shared portion of two rasters or the geometrical intersection of a vectorization of the raster and a geometry. ST_MapAlgebra — Callback function version - Returns a one-band raster given one or more input rasters, band indexes and one user-specified callback function. ST_MapAlgebra — Expression version - Returns a one-band raster given one or two input rasters, band indexes and one or more user-specified SQL expressions. ST_MapAlgebraExpr — 1 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the input raster band and of pixeltype provided. Band 1 is assumed if no band is specified. ST_MapAlgebraExpr — 2 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the two input raster bands and of pixeltype provided. band 1 of each raster is assumed if no band numbers are specified. The resulting raster will be aligned (scale, skew and pixel corners) on the grid defined by the first raster and have its extent defined by the "extenttype" parameter. Values for "extenttype" can be: INTERSECTION, UNION, FIRST, SECOND. ST_MapAlgebraFct — 1 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the input raster band and of pixeltype prodived. Band 1 is assumed if no band is specified. ST_MapAlgebraFct — 2 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the 2 input raster bands and of pixeltype prodived. Band 1 is assumed if no band is specified. Extent type defaults to INTERSECTION if not specified. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Processing - Map Algebra (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Processing_MapAlgebra) 106 ST_MapAlgebraFctNgb — 1-band version: Map Algebra Nearest Neighbor using user-defined PostgreSQL function. Return a raster which values are the result of a PLPGSQL user function involving a neighborhood of values from the input raster band. ST_Reclass — Creates a new raster composed of band types reclassified from original. The nband is the band to be changed. If nband is not specified assumed to be 1. All other bands are returned unchanged. Use case: convert a 16BUI band to a 8BUI and so forth for simpler rendering as viewable formats. ST_Union — Returns the union of a set of raster tiles into a single raster composed of 1 or more bands. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Processing - Built-in Map Algebra Callback Functions 107 ST_Distinct4ma — Raster processing function that calculates the number of unique pixel values in a neighborhood. ST_InvDistWeight4ma — Raster processing function that interpolates a pixel's value from the pixel's neighborhood. ST_Max4ma — Raster processing function that calculates the maximum pixel value in a neighborhood. ST_Mean4ma — Raster processing function that calculates the mean pixel value in a neighborhood. ST_Min4ma — Raster processing function that calculates the minimum pixel value in a neighborhood. ST_MinDist4ma — Raster processing function that returns the minimum distance (in number of pixels) between the pixel of interest and a neighboring pixel with value. ST_Range4ma — Raster processing function that calculates the range of pixel values in a neighborhood. ST_StdDev4ma — Raster processing function that calculates the standard deviation of pixel values in a neighborhood. ST_Sum4ma — Raster processing function that calculates the sum of all pixel values in a neighborhood. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Processing - DEM (Elevation) 108 ST_Aspect — Returns the aspect (in degrees by default) of an elevation raster band. Useful for analyzing terrain. ST_HillShade — Returns the hypothetical illumination of an elevation raster band using provided azimuth, altitude, brightness and scale inputs. ST_Roughness — Returns a raster with the calculated "roughness" of a DEM. ST_Slope — Returns the slope (in degrees by default) of an elevation raster band. Useful for analyzing terrain. ST_TPI — Returns a raster with the calculated Topographic Position Index. ST_TRI — Returns a raster with the calculated Terrain Ruggedness Index. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Processing - Raster to Geometry 109 Box3D — Returns the box 3d representation of the enclosing box of the raster. ST_ConvexHull — Return the convex hull geometry of the raster including pixel values equal to BandNoDataValue. For regular shaped and non-skewed rasters, this gives the same result as ST_Envelope so only useful for irregularly shaped or skewed rasters. ST_DumpAsPolygons — Returns a set of geomval (geom,val) rows, from a given raster band. If no band number is specified, band num defaults to 1. ST_Envelope — Returns the polygon representation of the extent of the raster. ST_MinConvexHull — Return the convex hull geometry of the raster excluding NODATA pixels. ST_Polygon — Returns a multipolygon geometry formed by the union of pixels that have a pixel value that is not no data value. If no band number is specified, band num defaults to 1. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster Operators (http://postgis.net/docs/manual-2.1/RT_reference.html#RT_Operators) 110 && — Returns TRUE if A's bounding box intersects B's bounding box. &< — Returns TRUE if A's bounding box is to the left of B's. &> — Returns TRUE if A's bounding box is to the right of B's. @ — Returns TRUE if A's bounding box is contained by B's. Uses double precision bounding box. = — Returns TRUE if A's bounding box is the same as B's. Uses double precision bounding box. ~= — Returns TRUE if A's bounding box is the same as B's. ~ — Returns TRUE if A's bounding box is contains B's. Uses double precision bounding box. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-4. Raster Reference Raster and Raster Band Spatial Relationships (http://postgis.net/docs/manual-2.1/RT_reference.html#RT_Operators) 111 ST_Contains — Return true if no points of raster rastB lie in the exterior of raster rastA and at least one point of the interior of rastB lies in the interior of rastA. ST_ContainsProperly — Return true if rastB intersects the interior of rastA but not the boundary or exterior of rastA. ST_Covers — Return true if no points of raster rastB lie outside raster rastA. ST_CoveredBy — Return true if no points of raster rastA lie outside raster rastB. ST_Disjoint — Return true if raster rastA does not spatially intersect rastB. ST_Intersects — Return true if raster rastA spatially intersects raster rastB. ST_Overlaps — Return true if raster rastA and rastB intersect but one does not completely contain the other. ST_Touches — Return true if raster rastA and rastB have at least one point in common but their interiors do not intersect. ST_SameAlignment — Returns true if rasters have same skew, scale, spatial ref and false if they don't with notice detailing issue. ST_NotSameAlignmentReason — Returns text stating if rasters are aligned and if not aligned, a reason why. ST_Within — Return true if no points of raster rastA lie in the exterior of raster rastB and at least one point of the interior of rastA lies in the interior of rastB. ST_DWithin — Return true if rasters rastA and rastB are within the specified distance of each other. ST_DFullyWithin — Return true if rasters rastA and rastB are fully within the specified distance of each other. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-5. Topology Topology and TopoGeometry Management 112 This section lists the Topology functions for building new Topology schemas, validating topologies, and managing TopoGeometry Columns AddTopoGeometryColumn — Adds a topogeometry column to an existing table, registers this new column as a layer in topology.layer and returns the new layer_id. DropTopology — Use with caution: Drops a topology schema and deletes its reference from topology.topology table and references to tables in that schema from the geometry_columns table. DropTopoGeometryColumn — Drops the topogeometry column from the table named table_name in schema schema_name and unregisters the columns from topology.layer table. TopologySummary — Takes a topology name and provides summary totals of types of objects in topology ValidateTopology — Returns a set of validatetopology_returntype objects detailing issues with topology LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-5. Topology Topology Constructors 113 CreateTopology — Creates a new topology schema and registers this new schema in the topology.topology table. CopyTopology — Makes a copy of a topology structure (nodes, edges, faces, layers and TopoGeometries). ST_InitTopoGeo — Creates a new topology schema and registers this new schema in the topology.topology table and details summary of process. ST_CreateTopoGeo — Adds a collection of geometries to a given empty topology and returns a message detailing success. TopoGeo_AddPoint — Adds a point to an existing topology using a tolerance and possibly splitting an existing edge. TopoGeo_AddLineString — Adds a linestring to an existing topology using a tolerance and possibly splitting existing edges/faces. Returns edge identifiers TopoGeo_AddPolygon — Adds a polygon to an existing topology using a tolerance and possibly splitting existing edges/faces. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-5. Topology Topology Editors 114 ST_AddIsoNode — Adds an isolated node to a face in a topology and returns the nodeid of the new node. If face is null, the node is still created. ST_AddIsoEdge — Adds an isolated edge defined by geometry alinestring to a topology connecting two existing isolated nodes anode and anothernode and returns the edge id of the new edge. ST_AddEdgeNewFaces — Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces. ST_AddEdgeModFace — Add a new edge and, if in doing so it splits a face, modify the original face and add a new face. ST_RemEdgeNewFace — Removes an edge and, if the removed edge separated two faces, delete the original faces and replace them with a new face. ST_RemEdgeModFace — Removes an edge and, if the removed edge separated two faces, delete one of the them and modify the other to take the space of both. ST_ChangeEdgeGeom — Changes the shape of an edge without affecting the topology structure. ST_ModEdgeSplit — Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge. ST_ModEdgeHeal — Heal two edges by deleting the node connecting them, modifying the first edge and deleting the second edge. Returns the id of the deleted node. ST_NewEdgeHeal — Heal two edges by deleting the node connecting them, deleting both edges, and replacing them with an edge whose direction is the same as the first edge provided. ST_MoveIsoNode — Moves an isolated node in a topology from one point to another. If new apoint geometry exists as a node an error is thrown. REturns description of move. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-5. Topology Topology Editors 115 ST_NewEdgesSplit — Split an edge by creating a new node along an existing edge, deleting the original edge and replacing it with two new edges. Returns the id of the new node created that joins the new edges. ST_RemoveIsoNode — Removes an isolated node and returns description of action. If the node is not isolated (is start or end of an edge), then an exception is thrown. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-5. Topology Topology Accessors 116 GetEdgeByPoint — Find the edge-id of an edge that intersects a given point GetFaceByPoint — Find the face-id of a face that intersects a given point GetNodeByPoint — Find the id of a node at a point location GetTopologyID — Returns the id of a topology in the topology.topology table given the name of the topology. GetTopologySRID — Returns the SRID of a topology in the topology.topology table given the name of the topology. GetTopologyName — Returns the name of a topology (schema) given the id of the topology. ST_GetFaceEdges — Returns a set of ordered edges that bound aface. ST_GetFaceGeometry — Returns the polygon in the given topology with the specified face id. GetRingEdges — Returns an ordered set of edges forming a ring with the given edge . GetNodeEdges — Returns an ordered set of edges incident to the given node. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-5. Topology Topology Processing 117 Polygonize — Find and register all faces defined by topology edges AddNode — Adds a point node to the node table in the specified topology schema and returns the nodeid of new node. If point already exists as node, the existing nodeid is returned. AddEdge — Adds a linestring edge to the edge table and associated start and end points to the point nodes table of the specified topology schema using the specified linestring geometry and returns the edgeid of the new (or existing) edge. AddFace — Registers a face primitive to a topology and get it's identifier. ST_Simplify — Returns a "simplified" geometry version of the given TopoGeometry using the Douglas-Peucker algorithm. TopoGeometry Constructors CreateTopoGeom — Creates a new topo geometry object from topo element array - tg_type: 1:[multi]point, 2:[multi]line, 3:[multi]poly, 4:collection toTopoGeom — Converts a simple Geometry into a topo geometry TopoElementArray_Agg — Returns a topoelementarray for a set of element_id, type arrays (topoelements) LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) IV-5. Topology TopoGeometry Editors 118 clearTopoGeom — Clears the content of a topo geometry toTopoGeom — Adds a geometry shape to an existing topo geometry GetTopoGeomElementArray — Returns a topoelementarray (an array of topoelements) containing the topological elements and type of the given TopoGeometry (primitive elements) GetTopoGeomElements — Returns a set of topoelement objects containing the topological element_id,element_type of the given TopoGeometry (primitive elements) AsGML — Returns the GML representation of a topogeometry. AsTopoJSON — Returns the TopoJSON representation of a topogeometry. Equals — Returns true if two topogeometries are composed of the same topology primitives. Intersects — Returns true if two topogeometries are composed of the same topology primitives. TopoGeometry Accessors TopoGeometry Outputs Topology Spatial Relationships LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V. 어플리케이션을 이용한 PostGIS 사용하기 - 일러두기 - 본 자료는 모두 Creative Commons License CC-BY-NC을 따릅니다. 본 교재에서는 교육의 편의상 Windows 32비트 기반의 프로그램들을 위주로 설명합니다. 본 교재에서 사용하는 샘플 데이터 셋은 실제 정보와 다르므로 교육용 이외에는 사용할 수 없습니다. V-1. 프로그램 설치하기 프로그램 설치하기 115 ① QGIS 2.0 설치 하기 - 설치프로그램 폴더 : QGIS-OSGeo4W-2.0.1-3-Setup-x86 설치 - 설치화면 기본 순서대로 설치 ② uDig 1.4 설치 하기 - 설치프로그램 폴더 : udig-1.4.0.win32.win32.x86 - 설치화면 기본 순서대로 설치 ③ OpenJUMP 1.6 설치하기 - 설치프로그램 폴더 : OpenJUMP-Installer-1.6.3-r3576-CORE - 설치화면 기본 순서대로 설치 ④ JDK 설치하기 - 설치프로그램 폴더 : JDK 설치 및 환경설정.txt 참조 ⑤ visual studio 평가판 설치하기 - 설치프로그램 폴더 : vcs_web.exe 설치하기 - 설치화면 기본 순서대로 설치 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-2. QGIS 이용한 PostGIS 이용하기 QGIS에서 레이어 추가하기 116 ① QGIS 2.0 실행 ② [플러그인] – [플러그인관리및설치] SPIT 설치 확인 ③ [데이터베이스] – [Spit(s)] –[쉐입 파일을 PostGIS에 가져오기 ④ [새로만들기] 선택, 정보 입력 후 [확인] 선택 ⑤ [연결] 선택 ⑥ [추가] 선택 후, Shape 파일 선택 추가 ⑦ 기본 지오매트리 컬럼 이름 사용 체크 해제 후 ‘geom’ 변경 ⑧ 기본 SRID 사용 체크 해제 후 ‘5174’ 변경 ⑨ [확인] 선택 ⑩ pgAdmin 실행 후 korea 데이터베이스에 입력한 shape파일 테이블 확인 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-2. QGIS 이용한 PostGIS 이용하기 QGIS에서 PostGIS 레이어 불러와 사용하기 117 ① QGIS 2.0 실행 ② [레이어] – [PostGIS레이어 추가하기] 선택 ③ [새로만들기] 선택, 정보 입력 후 [확인] 선택 – nyc 사용 ④ [연결] 선택 ⑤ public 선택 원하는 Shape 선택 후 [추가] 선택 ⑥ 선택된 레이어 변경하기 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-3. OpenJUMP 이용한 PostGIS 이용하기 OpenJUMP에서 PostGIS 레이어 불러와 사용하기 118 ① OpenJUMP 1.6 실행 ② Working, System 오른 쪽 마우스 선택 후 [Add Datastore Layer] 선택 ③ Coonection[ ] 아이콘 선택 ④ [Add] 선택 ⑤ Connection Manager 화면에서 [ok] 선택 ⑥ Add Datastore Layer에서 Dataset(레이어선택) 후 [ok] 선택 ⑦ 여러레이어를 올려 레이어 변경하기 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-4. uDig 이용한 PostGIS 이용하기 uDig에서 PostGIS 레이어 불러와 이용하기 119 ① uDig 1.46 실행 ② [파일] – [새로운 지도작업] – [지도] 선택 ③ [레이어] – [추가] 선택 ④ [PostGIS] 선택, [다음] 선택 ⑤ [Add] 선택 ⑥ Host, User Name, Password 입력 ⑦ Database 콤보박스 [korea] 선택 후 [List] 선택 ⑧ 여러 레이어 체크박스 선택 후 [완료] LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-5. JDBC Driver 이용한 PostGIS 이용하기 JDBC Driver 이용한 PostGIS 이용하기 120 ① 최신 PostgreSQL JDBC Driver 다운로드 http://jdbc.postgresql.org/download.html ② jar파일 CLASSPATH 등록 ③ JDBC Driver 설치 방법 - 직접 다운로드 사이트 접속 다운 후 등록 - Application Stack Builder 통한 설치 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-6. .NET Driver 이용한 PostGIS 이용하기 .NET Driver 이용한 PostGIS 이용하기 121 ① 최신 npgsql .NET PostgreSQL driver 다운로드 http://npgsql.projects.postgresql.org/ ② Web.config 설정 -- web.config connection string section -- ③ .NET Driver 설치 방법 - 직접 다운로드 사이트 접속 다운 후 등록 - Application Stack Builder 통한 설치 // Code for TestRaster.ashx using System; using System.Data; using System.Web; using Npgsql; public class TestRaster : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "image/png"; context.Response.BinaryWrite(GetResults(context)); } public bool IsReusable { get { return false; } } public byte[] GetResults(HttpContext context) { byte[] result = null; NpgsqlCommand command; string sql = null; int input_srid = 26986; try { using (NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DSN"].ConnectionString)) { conn.Open(); if (context.Request["srid"] != null) { input_srid = Convert.ToInt32(context.Request["srid"]); } sql = @"SELECT ST_AsPNG( ST_Transform( ST_AddBand( ST_Union(rast,1), ARRAY[ST_Union(rast,2),ST_Union(rast,3)]) ,:input_srid) ) As new_rast FROM aerials.boston WHERE ST_Intersects(rast, ST_Transform(ST_MakeEnvelope(-71.1217, 42.227, -71.1210, 42.218,4326),26986) )"; command = new NpgsqlCommand(sql, conn); command.Parameters.Add(new NpgsqlParameter("input_srid", input_srid)); result = (byte[]) command.ExecuteScalar(); conn.Close(); } } catch (Exception ex) { result = null; context.Response.Write(ex.Message.Trim()); } return result; } } LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-7. 기타 PostGIS 이용하기 Open Source DeskTop Editing/Viewing Tool : gvSIG, TileMill(MapBox) 122 ① http://www.gvsig.com 접속 gvSIG Desktop download ② http://www.gvsig.com/en/products/gvsig-desktop/downloads ③ http://live.osgeo.org/en/quickstart/gvsig_quickstart.html ① https://www.mapbox.com/tilemill/ 접속 ② https://www.mapbox.com/tilemill/docs/crashcourse/introduction/ ③ https://www.mapbox.com/tilemill/docs/guides/postgis-work/ LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-7. 기타 PostGIS 이용하기 Open Source Mapping Server : MapServer, Deegree, QGIS Server, MapGuide Open Source 123 ① http://mapserver.org/ 접속 download ② http://mapserver.org/download.html ③ http://mapserver.org/documentation.html ① http://www.deegree.org/ 접속 ② http://www.deegree.org/download ③ http://download.deegree.org/documentation/3.3.13/html/ LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) V-7. 기타 PostGIS 이용하기 Open Source Mapping Server : MapServer, Deegree, QGIS Server, MapGuide Open Source 124 ① http://live.osgeo.org/ko/quickstart/qgis_mapserver_quickstart.html ① http://mapguide.osgeo.org/ LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI. 그밖의 기능 - 일러두기 - 본 자료는 모두 Creative Commons License CC-BY-NC을 따릅니다. 본 교재에서는 교육의 편의상 Windows 32비트 기반의 프로그램들을 위주로 설명합니다. 본 교재에서 사용하는 샘플 데이터 셋은 실제 정보와 다르므로 교육용 이외에는 사용할 수 없습니다. VI-1. Tuning PostgreSQL for Spatial Backend Configuration Editer 125 ① pgAdmin 실행 ② [File] – [Open postgresql.conf] – postgresql.conf 파일 선택 Shared_buffer Default Value : 32MB Recommended Value : 500MB (75%) LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-1. Tuning PostgreSQL for Spatial Work_mem 126 Default Value : 1MB Recommended value : 16MB Maintenance_work_mem Default Value : 16MB Recommended Value : 16MB(ex.128MB) LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-1. Tuning PostgreSQL for Spatial Wal_buffers 127 Default Value : 1MB Recommended value : 16MB Checkpoint_segments Default Value : 3MB Recommended Value : 6MB LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-1. Tuning PostgreSQL for Spatial Ramdom_page_cost 128 Default Value : 4.0 Recommended value : 2.0 Seq_page_cost Default Value : 1.0 Recommended Value : 1.0 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-1. Tuning PostgreSQL for Spatial Reload configuration 129 ① pgAdmin 접속 종료(종료) ② OpenSeo Suit Dashboard Shutdown, Start ③ pgAdim 접속 (재시작) ④ 설정 정보가 반영된다. LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-2. Query Plans Query Plans 130 ① pgAdmin 실행 ② Query Tool 실행 ③ [Query] – [Query Analyze] 선택 ④ Query 실행 – nyc db 사용 SELECT Sum(popn_total) FROM nyc_neighborhoods neighborhoods JOIN nyc_census_blocks blocks ON ST_Intersects(neighborhoods.geom, blocks.geom) WHERE neighborhoods.name = 'West Village'; ⑤ [Output pane] – [Explain] 선택 ⑥ Sequence Scan : 테이블의모든 Row Scan ⑦ Index Scan : 제약조건 스캔 ⑧ Nested Loop ⑨ Hash Aggregate LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-3. 백업 & 복원 백업 131 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-3. 백업 & 복원 백업 132 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-3. 백업 & 복원 복원 133 절차 템플릿 데이터베이스 생성 Restore(복원) LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-3. 백업 & 복원 복원 134 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-3. 백업 & 복원 주의사항 135 ① 상위버전과 하위버전 호환안됨 ② 하위버전 백업 상위버전 Restore 가능 ③ 상위버전 백업 하위버전 Restore 오류 버전충돌시 테이블/레이어 분리백업/복원 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting pgRouting 136 http://pgrouting.org/ LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting pgRouting 137 http://pgrouting.org/documentation.html http://workshop.pgrouting.org/ LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting pgRouting 138 pgRouting 은 PostGIS/PostgreSQL에 라우팅 기능을 추한 PostGIS의 Extension Camptocamp SA 에 의해 시작된 pgDijkstra의 확장판이며 Orkney 에 의해 확장 현재 Georepublic 에 의해 개발 및 유지 관리되고 있음 pgRouting는 GPLv2 라이선스 하에 사용 가능하며 개인, 기업 및 조직의 커뮤니티에 의해 지원 데이터베이스 기반 라우팅 접근 방법의 장점 데이터와 속성은 Quantum GIS, uDig 그리고 JDBC, ODBC 또는 Pl/pgSQL 등 다양한 클라이언트에 의해 직접 갱신 가능하며, 클라이언트는 PC나 모바일 기기가 될 수 있음 데이터 변경 내용은 라우팅 엔진을 통해 즉시 반영되며, 재계산이 필요 없음 “cost” 파라미터는 SQL을 통해 동적으로 계산되고 그 값은 다중 필드 및 테이블의 다양한 값을 사용할 수 있음 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting 지원 알고리즘 139 Shortest Path Dijkstra 휴리스틱 기법(heuristic method)을 사용하지 않는 최단거리 알고리즘, 알고리즘을 개발한 Dr. Edsger Wybe Dijkstra 교수의 이름을 사용. Shortest Path A-Star(A*) 휴리스틱 기법을 사용하며 대용량 데이터셋에 적합한 최단거리 알고리즘 Shortest Path Shooting-Star(Shooting*) 휴리스틱 기법을 사용하며 turn restrictions(U-turn, P-turn, left-turn 등), 신호등, 편도차선 등의 시제 도로 네트워크를 위한 최단거리 알고리즘 Traveling Salesperson Problem (TSP) 최대 40개의 포인트를 지원하는 Traveling Salesman Problem(TSP, 외판원 문제) 알고리즘 Driving Distance calculation (Isolines) 도로네트워크에 기반을 둔 특정지점에서 특정 시간내에 도달 할 수 있는 영역. Service area LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting Network 데이터셋 140 도로 네트워크 데이터셋은 최소한 다음의 정보를 포함 Road link ID (gid) Road class (class_id): primary roads, secondary roads, and local roads 등 Hierarchies Road link length (length): Cost - Length, Travel Time 등 Road name (name) Road geometry (the_geom) Road restrictions & rule (optional): Lanes, Speed Limit, traffic light, one-way streets 등 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting 공공기관 생산 도로 네트워크 141 공공기관에서 생산하는 도로네트워크로 활용 가능한 데이터는 다음의 4가지가 가능 1. ITS 전국표준노드링크: http://nodelink.its.go.kr/ 2. UTIS 통합노드링크: http://www.utis.go.kr/ 3. 국가교통DB센터 노드링크: http://www.ktdb.go.kr/ 4. 새주소 도로구간 1. 2. 3. 데이터는 1:5000 축척을 기반으로 제작되었으며 주요 도로(고속국도/일반국도/지방도 수준) 중심으로 상세한 지역에서의 활용은 어려움 4. 새주소 도로구간은 도로중심선으로 구축되어 있으나 네트워크 분석자료로 활용하기 위해서는 위상구조 편집이 필요하며 도로에 대한 상세정보(Restrictions & rules)는 없음 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting 공공기관 생산 도로 네트워크 (사례지역 : 제주도, 배경은 주거지(건물) 밀집도) 142 새주소 도로중심선 LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting Installing pgRouting 143 웹사이트에 http://pgrouting.org/download.html PostGIS 2.1.1 bundle 설치시 기본적으로 pgrouting 2.0 설치됨 [pgAdmin] 실행 [New database] 생성 CREATE EXTENSION postgis; CREATE EXTENSION pgrouting; LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-4. pgRouting pgRouting reference 144 pgRouting Demo http://websi.openvrp.com http://map.veloland.ch http://www.ridethecity.com http://www.pgrouting.org/gallery.html Reference http://www.pgrouting.org/ http://workshop.pgrouting.org/ http://underdark.wordpress.com/2011/02/07/a-beginners-guide-to-pgrouting/ http://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/ https://github.com/pgRouting/pgrouting/ http://2011.foss4g.org/sites/2011.foss4g.org/files/slides/FOSS4G2011_pgrouting2.pdf http://2010.foss4g.org/presentations/3284.pdf LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) VI-5. Reference reference 145 PostGIS - http://postgis.org/ Docs - http://postgis.org/docs/ PostgreSQL - http://www.postgresl.org/ Downloads - http://www.postgresql.org/download/ Docs - http://www.postgresql.org/docs/ JDBC Driver - http://jdbc.postgresql.org/ .Net Driver - http://npgsql.projects.postgresql.org/ Python Driver - http://www.pygresql.org/ C/C++ Driver - http://www.postgresql.org/docs/8.4/static/libpq.html PgAdmin III - http://www.pgadmin.org/ Open Source Desktop Clients uDig - http://udig.refractions.net/ QGIS - http://qgis.org/ OpenJUMP - http://openjump.org/ gvSIG - http://www.gvsig.org TileMil - https://www.mapbox.com/tilemill LX 공간정보아카데미 – 오픈 소스 GIS 기초과정 윤정환 (
[email protected]) 감사합니다. Q&A 한국어 지부 함께 성장하는 새로운 방법, 오픈 소스 소프트웨어!! www.osgeo.kr