공간정보연구원 PostGIS 강의교재

2014 년 7 월 23 일 공간정보연구원

오픈 소스 GIS 교육㈜엔지스

윤정환 ([email protected])

PostGIS

한국어 지부

mailto:[email protected]

공간정보연구원 - 오픈 소스 GIS 교육 윤정환 ([email protected])

목차

I. PostGIS 개요 I-1. Whati is Spatial Database

I-2. PostGIS Site 둘러보기 I-3. PostGIS 를 지원하는 GIS 프로그램 I-4. Open Source Geostack

II. PostGIS 설치 및 환경설정 II-1. OpenGeo Suite 소개 II-2 PostGIS 설치하기 II-3. PostGIS 환경설정 II-4. 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 이용하기

VI. PostGIS 그밖의 기능 VI-1. Tuning PostgreSQL for Spatial

VI-2. Query Plans

VI-3. 백업 & 복원 VI-4. pgRouting 소개 VI-5. Reference

I. PostGIS 개요

- 일러두기 -

• 본 자료는 모두 Creative Commons License CC-BY-NC 을 따릅니다 .

• 본 교재에서는 교육의 편의상 Windows 32 비트 기반의 프로그램들을 위주로 설명합니다 .

• 본 교재에서 사용하는 샘플 데이터 셋은 실제 정보와 다르므로 교육용 이외에는 사용할 수 없습니다 .


PostGIS 란 ?

I-1. What is a Spatial DataBase

PostgreSQL 기반 spatial types, indexes, functions 의 3 가지 기능을 추가한 공간 데이터베이스

Spatial databases store/manipulate spa-tial 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 relation-

ships.

http://www.postgis.net/

1



PostGIS Site 둘러보기

I-2. PostGIS Site 둘러보기 - 1

PostGIS 관련된 Site 둘러보기


2

http://www.postgresql.org/





PostGIS Site 둘러보기

I-2. PostGIS Site 둘러보기 - 2

PostGIS 관련된 Site 둘러보기

http://boundlessgeo.com/3

http://www.pgadmin.org/


PostGIS 를 지원하는 GIS 프로그램

I-3. PostGIS 를 지원하는 GIS 프로그램

4

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)


Open Source GeoStack

I-4. Open Source GeoStack

5

II. PostGIS 설치 및 환경설정

- 일러두기 -





II-1. OpenGeo Suite 소개

OpenGeo Suite 소개

6

OpenGeo Suite



OpenGeo Suite 설치 - 1

7




8




9

C:\ProgramData\Boundless\OpenGeo


II-2. PostGIS 설치하기

① http://www.postgresql.org/download/에 접속하여 PostgreSQL 최신버전 (PostgreSQL 9.3.2) 을 내려 받음② http://download.osgeo.org/postgis/windows/에 접속하여 PostGIS 최신버전 (PostGIS 2.1.1) 을 내려받음③ [ 설치프로그램 \postgis] 경로로 이동하여 PostgreSQL, PostGIS 최신버전을 설치

– DBMS 설치 : postgresql-9.3.2-1-windows– Spatial Extension 설치 : postgis-bundle-pg93x32-setup-2.1.1-1

④ PostgreSQL, PostGIS 기본적인 설정에 따라 설치를 완료 ⑤ [ 시작메뉴 ] – [PostgreSQL 9.3] – [pgAdmin] 을 실행

PostgreSQL 단독설치 (Application Stack Builder) - 1

PostgreSQL 설치 PostGIS 설치 pgAdmin 실행

10

http://www.postgresql.org/download/


http://download.osgeo.org/postgis/windows/






PostgreSQL: http://www.postgresql.org/download/

11





Password : post-gis

12

사용자 컴퓨터의 등록정보를 확인하여 컴퓨터 이름 또는 사용자 이름이 한글일 경우 오류가 발생할 수 있으므로 변경 후 설치 권장합니다




6

13




6

14




6

15




16



① http://boundlessgeo.com/에 접속하여 OpenGeo Suite 최신버전을 내려받음② [ 설치프로그램 \postgis] 경로로 이동하여 OpenGeo Suite 3.0.2 버전을 복사③ OpenGeo Suite 기본적인 설정에 따라 설치를 완료 ④ [ 시작메뉴 ] – [OpenGeo Suite 3.0.2] – [OpenGeo Suite Dashboard] 를 실행 ⑤ OpenGeo Suite Dashboard 화면 우측 상단 [Start] 버튼을 선택⑥ [Components] – [PostGIS] – [manage] 를 선택하여 pgAdmin 을 실행⑦ Tip : 사용자 컴퓨터의 등록정보를 확인하여 컴퓨터 이름 또는 사용자 이름이 한글일 경우 오류가 발생할 수

있으므로 변경 후 설치 권장

OpenGeo Suit 설치

OpenGeo Suite 설치 OpenGeo Suite Dashboard 실행 pgAdmin 실행

17

http://boundlessgeo.com/

http://boundlessgeo.com/


II-3. PostGIS 환경설정

OpenGeoSuite Dashbord 환결설정

① OpenGeo Suite Dashboard 화면 선택② Preferences 를 선택하고 , [PostGIS] 메뉴에 Port 번호 “ 54321” 을 확인

pgAdmin 환경설정

① [ 시작메뉴 ] – [PostgreSQL 9.3] – [pgAdmin] 을 실행 ② pgAdmin 의 [File] – [Options] 메뉴를 선택 ③ [General tab] – [user language] 의 Default 를 확인 ④ [Preferences tab] – [Font] 선택하여 “돋움 , 9” 로 변경

18


II-4. pgAdmin & Query Tool 사용하기

pgAdmin 사용하기

19

메뉴 툴바

객체 브라우저

View



pgAdmin 사용하기

20

도구 설명

pgAdmin 도구에 새로운 서버를 추가합니다 .

객체의 정보를 생성 , 수정 , 갱신 , 삭제 후 새로고침 합니다 .

테이블 등의 객체의 등록정보를 확인하거나 수정합니다 .

선택된 객체와 같은 형태의 새로운 객체를 생성합니다 .

선택된 객체를 삭제합니다 .

SQL 을 실행할 수 있는 Query 도구를 불러옵니다 .

선택된 테이블을 편집할 수 있는 테이블 편집기를 불러옵니다 .

필터를 적용해서 테이블을 편집할 수 있는 테이블 편집기를 불러옵니다 .

Vacuum, Analyze 등 테이블 유지에 필요한 도구를 불러옵니다 .



pgAdmin Server 구성하기

① [ 시작메뉴 ] – [PostgreSQL 9.3] – [pgAdmin] 을 실행② [File] – [Add Server] & 툴바 아이콘을 선택③ New Server Registration 화면에 Name, Host, Password 를 입력 후 [ok] 선택

21

postgis



Query Tool 사용하기

22

① pgAdmin Tool 툴바 아이콘을 선택 Query Tool 실행 – “ SELECT version();”

② 샘플데이터 폴더의 postgis_korea_epsg_towgs84.sql 을 열기③ [F5] 선택 Query 실행 시킴 , 한국 주요 좌표계 적용

Query 다루기 좌표 등록 시키기

III. 공간데이터베이스 관리

- 일러두기 -





III-1. 공간데이터베이스 생성

① [ 시작메뉴 ] – [PostgreSQL 9.3] – [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 를 이용한 공간데이터베이스 생성

23


III-2. 공간데이터 생성 & 로딩 & 추출

공간데이터 로딩 - nyc

24

① [ 시작메뉴 ] – [PostGIS 2.1 bundle for PostgreSQL 9.3 ] – [PostGIS Shapefile and DBF Loader Ex-porter] 을 실행② [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 파일 추가



공간데이터 로딩 - korea

25

① [ 시작메뉴 ] – [PostGIS 2.1 bundle for PostgreSQL 9.3 ] – [PostGIS Shapefile and DBF Loader Ex-porter] 을 실행② [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 파일 추가



공간데이터 추출

26

① [ 시작메뉴 ] – [PostGIS 2.1 bundle for PostgreSQL 9.3 ] – [pgAdmin III] 을 실행② OpenGeo Suit Dashboard 의 PostGIS [Manage] 선택 실행③ nyc 의 Query Tool 을 선택④ nyc_neighborhoods 테이블을 이용하여 다음 쿼리 문을 작성해 보시기 바랍니다 .

- 브루클린에 있는 모든 지역의 이름 (name) 은 ?

- 브루클린에 있는 모든 지역의 이름에 있는 문자의 수는 ?

- 브루클린에 있는 모든 지역의 이름에 있는 문자의 평균과 표준편차는 ?

⑤ Function List

- avg(expression)

- char_lengtth(string)

- stddev(string)



공간데이터 추출

27

① [ 시작메뉴 ] – [PostGIS 2.1 bundle for PostgreSQL 9.3 ] – [pgAdmin III] 을 실행② OpenGeo Suit Dashboard 의 PostGIS [Manage] 선택 실행③ korea 의 Query Tool 을 선택 ④ admin_emd 테이블과 pop2007, pop2008 컬럼을 이용하여 다음 쿼리 문을 작성해 보시기 바랍니다 .

- 서울특별시 2007, 2008 각 년도 인구수 총 합은 ?

- 서울특별시 종로구 , 서대문구 , 강남구 2007, 2008 각년도 인구수의 총 합은 ?

- 2008 년 인구 중 종로구내 각 동의 인구비율은 백분율로 표시 ?

⑤ Function List

- avg(expression)

- count(expression)

- sum(expression)


III-3. 래스터 데이터 생성 & 로딩

레스터 데이터 생성

28

① [ 시작메뉴 ] – [PostGIS 2.1 bundle for PostgreSQL 9.3 ] – [pgAdmin III] 을 실행② OpenGeo Suit Dashboard 의 PostGIS [Manage] 선택 실행③ 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) );


III-3. 래스터 데이터 생성 & 로딩

레스터 데이터 로딩

29

① 윈도우 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

http://postgis.net/docs/manual-2.1/using_raster_dataman.html#RT_Raster_Loader



IV. 공간함수 사용하기

- 일러두기 -





IV-1. PostGIS Reference

reference

30

http://postgis.net/docs/manual-2.1/ http://suite.opengeo.org/opengeo-docs/dataadmin/in-dex.html

http://postgis.net/docs/manual-2.1/





Management Functions (http://postgis.net/docs/manual-2.1/reference.html#Management_Functions)

31

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 appro-

priate spatial constraints This ensures they will be registered correctly in geometry_columns view. By de-fault will convert all geometry columns with no type modifier to ones with type modifiers. To get old behav-ior 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 con-straint. If the old was enforced by type definition, the type definition will be changed.



Geometry Constructors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Constructors)

32

ST_BdPolyFromText — Construct a Polygon given an arbitrary collection of closed linestrings as a Multi-LineString Well-Known text representation.

ST_BdMPolyFromText — Construct a MultiPolygon given an arbitrary collection of closed linestrings as a Mul-tiLineString 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 ex-

tended (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 repre-

sentation (EWKB). ST_GeomFromEWKT — Return a specified ST_Geometry value from Extended Well-Known Text represen-

tation (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 ob-

ject




33

ST_GeomFromGeoJSON — Takes as input a geojson representation of a geometry and outputs a PostGIS ge-ometry object

ST_GeomFromKML — Takes as input KML representation of geometry and outputs a PostGIS geometry ob-ject

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.




34

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



Geometry Accessors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Accessors)

35

GeometryType — Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTI-POINT', 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 sup-

plied 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, re-turn 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 sur-

face 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 intersec-

tion or self tangency.




36

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 geome-

tries, 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, POLYHE-

DRALSURFACEM. 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.




37

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.



Geometry Editors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Editors)

38

ST_AddPoint — Adds a point to a LineString before point <position> (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 con-

tents. 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.



Geometry Editors (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Editors)

39

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 dis-

tance. Distance computation is performed in 2d only. For geometry, length units are in units of spatial ref-erence. 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.



Geometry Outputs (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Outputs)

40

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 preci-

sion=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.



Operators (http://postgis.net/docs/manual-2.1/reference.html#Operators)

41

&& — 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's bounding box overlaps or is below B's. &> — Returns TRUE if A' bounding box overlaps or is to the right of B's. << — Returns TRUE if A's bounding box is strictly to the left of B's. <<| — Returns TRUE if A's bounding box is strictly below B's. = — 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 strictly to the right of B's. @ — Returns TRUE if A's bounding box is contained by 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 ge-ometries are double precision). Useful for doing distance ordering and nearest neighbor limits using KNN gist functionality.



Spatial Relationships and Measurements (http://postgis.net/docs/manual-2.1/reference.html#Spatial_Relationships_Measurements)

42

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 an-other.

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 verti-

cal 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.




43

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 spa-

tial ref) between two geometries in projected units. For geography type defaults to return spheroidal mini-mum 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 ge-ometries.

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 spher-

ical 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 geome-

try units are in those of spatial reference and For geography units are in meters and measurement is de-faulted to use_spheroid=true (measure around spheroid), for faster check, use_spheroid=false to measure along sphere.




44

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 por-

tion 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 reprojec-tion.

ST_Length2D_Spheroid — Calculates the 2D length of a linestring/multilinestring on an ellipsoid. This is use-ful 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 ac-count. 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 re-turns for g1 and g2.

ST_OrderingEquals — Returns true if the given geometries represent the same geometry and points are in the same directional order.




45

ST_Overlaps — Returns TRUE if the Geometries share space, are of the same dimension, but are not com-pletely contained by each other.

ST_Perimeter — Return the length measurement of the boundary of an ST_Surface or ST_MultiSurface ge-ometry 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 intersec-

tions 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 intersec-tionMatrixPattern 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



SFCGAL Functions (http://postgis.net/docs/manual-2.1/reference.html#reference_sfcgal)

46

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



Geometry Processing (http://postgis.net/docs/manual-2.1/reference.html#Geometry_Processing)

47

ST_Buffer — (T) For geometry: Returns a geometry that represents all points whose distance from this Ge-ometry 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 geome-

try. 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.




49

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. De-

fault 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 comput-

ing 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/multi-

linestrings. ST_Shift_Longitude — Reads every point/vertex in every component of every feature in a geometry, and if

the longitude coordinate is <0, adds 360 to it. The result would be a 0-360 version of the data to be plotted in a 180 centric map

ST_Simplify — Returns a "simplified" version of the given geometry using the Douglas-Peucker algorithm. ST_SimplifyPreserveTopology — Returns a "simplified" version of the given geometry using the Douglas-

Peucker algorithm. Will avoid creating derived geometries (polygons in particular) that are invalid. ST_Split — Returns a collection of geometries resulting by splitting a geometry. ST_SymDifference — Returns a geometry that represents the portions of A and B that do not intersect. It

is called a symmetric difference because ST_SymDifference(A,B) = ST_SymDifference(B,A).




50

ST_Union — Returns a geometry that represents the point set union of the Geometries. ST_UnaryUnion — Like ST_Union, but working at the geometry component level.



Linear Referencing (http://postgis.net/docs/manual-2.1/reference.html#Linear_Referencing)

51

ST_LineInterpolatePoint — Returns a point interpolated along a line. Second argument is a float8 between 0 and 1 representing fraction of total length of linestring the point has to be located.

ST_LineLocatePoint — Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length.

ST_LineSubstring — Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length. Second and third arguments are float8 values between 0 and 1.

ST_LocateAlong — Return a derived geometry collection value with elements that match the specified mea-sure. Polygonal elements are not supported.

ST_LocateBetween — Return a derived geometry collection value with elements that match the specified range of measures inclusively. Polygonal elements are not supported.

ST_LocateBetweenElevations — Return a derived geometry (collection) value with elements that intersect the specified range of elevations inclusively. Only 3D, 4D LINESTRINGS and MULTILINESTRINGS are sup-ported.

ST_InterpolatePoint — Return the value of the measure dimension of a geometry at the point closed to the provided point.

ST_AddMeasure — Return a derived geometry with measure elements linearly interpolated between the start and end points. If the geometry has no measure dimension, one is added. If the geometry has a mea-sure dimension, it is over-written with new values. Only LINESTRINGS and MULTILINESTRINGS are sup-ported.



Long Transactions Support (http://postgis.net/docs/manual-2.1/reference.html#Long_Transactions_Support)

52

AddAuth — Add an authorization token to be used in current transaction. CheckAuth — Creates trigger on a table to prevent/allow updates and deletes of rows based on authoriza-

tion token. DisableLongTransactions — Disable long transaction support. This function removes the long transaction

support metadata tables, and drops all triggers attached to lock-checked tables. EnableLongTransactions — Enable long transaction support. This function creates the required metadata

tables, needs to be called once before using the other functions in this section. Calling it twice is harmless. LockRow — Set lock/authorization for specific row in table UnlockRows — Remove all locks held by specified authorization id. Returns the number of locks released. This module and associated pl/pgsql functions have been implemented to provide long locking support re-

quired by Web Feature Service specification.



Miscellaneous Functions (http://postgis.net/docs/manual-2.1/reference.html#Miscellaneous_Functions)

53

ST_Accum — Aggregate. Constructs an array of geometries. Box2D — Returns a BOX2D representing the maximum extents of the geometry. Box3D — Returns a BOX3D representing the maximum extents of the geometry. ST_EstimatedExtent — Return the 'estimated' extent of the given spatial table. The estimated is taken

from the geometry column's statistics. The current schema will be used if not specified. ST_Expand — Returns bounding box expanded in all directions from the bounding box of the input geome-

try. Uses double-precision ST_Extent — an aggregate function that returns the bounding box that bounds rows of geometries. ST_3DExtent — an aggregate function that returns the box3D bounding box that bounds rows of geome-

tries. Find_SRID — The syntax is find_srid(a_db_schema, a_table, a_column) and the function returns the integer

SRID of the specified column by searching through the GEOMETRY_COLUMNS table. ST_Mem_Size — Returns the amount of space (in bytes) the geometry takes. ST_Point_Inside_Circle — Is the point geometry insert circle defined by center_x, center_y, radius



Exceptional Functions (http://postgis.net/docs/manual-2.1/reference.html#Exceptional_Functions)

54

PostGIS_AddBBox — Add bounding box to the geometry. PostGIS_DropBBox — Drop the bounding box cache from the geometry. PostGIS_HasBBox — Returns TRUE if the bbox of this geometry is cached, FALSE otherwise.

IV-2. 실습예제 -Ⅰ

- 일러두기 -





IV-2-1. 실습예제 데이터셋 보기

실습예제 데이터셋 보기

55

Shapefile 레이어명 유형 SRID 비고admin_emd 읍면동경계 MultiPolygon 5174

admin_sgg 시군구경계 MultiPolygon 5174

admin_sid 시도경계 MultiPolygon 5174

river 실폭하천 MultiPolygon 5174

road_link2 도로 MultiLinestring 5174

firestation 소방서 Point 5174

healthcenter 보건소 Point 5174

policestation 경찰서 Point 5174

stores 대형마트 Point 5174

wardoffice 시군구청 Point 5174

subway 지하철노선 MultiLinestring 5174

Subway_station 지하철역 Point 5174


IV-2-2. 레이어 확인

레이어 확인

56


IV-2-3. PostGIS Documentation

PostGIS Documention

57


IV-2-4. 테이블 관리

공간테이블 생성하기

58

1. cityhall 이라는 공간 테이블을 생성하고 , 다음의 정보를 입력하시오 . X=197977.3885 , Y=451601.1444, city_name = 서울

Function AddGeometryColumn( <schema_name>, <table_name>, <column_name>, <srid>,

<type>, <dimension> ) AddGeometryColumn( <table_name>, <column_name>, <srid>, <type>, <dimension> )

http://postgis.net/docs/manual-2.0/using_postgis_dbmanagement.html#OpenGISWKBWKT





공간인덱싱 생성하기

59

2. 앞 페이지에서 생성한 cityhall 테이블에 공간인덱싱을 생성하시오 .

SQL CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );

http://postgis.org/docs/ch04.html#id2628148

http://postgis.org/docs/ch04.html

http://postgis.org/docs/ch04.html



공간테이블 삭제하기

60

3. 앞에서 생성한 cityhall 테이블을 삭제하시오 .

일반적인 과정 Remove refs from geometry_columns table Remove table

Function DropGeometryTable(varchar table_name); DropGeometryTable(varchar schema_name, varchar table_name); DropGeometryTable(varchar catalog_name, varchar schema_name,

varchar table_name);



공간 뷰 (View) 생성 후 등록하기

61

4. 읍면동 행정경계 (admin_emd) 를 이용하여 종로구에 해당하는 읍면동의 중심점을 admin_emd_point View 로 등록하시오 .

Function CREATE OR REPLACE VIEW, DROP VIEW ST_Centroid(geometry g1) Populate_Geometry_Columns()



SQL 을 이용하여 공간테이블 생성 후 등록하기

62

5. 시군구 행정경계 (admin_sgg) 를 이용하여 종로구에 해당하는 시군구 폴리곤과 Intersects 되는 도로 (road_link2) 를 Clip 하여 새로운 테이블로 생성하시오 .


IV-2-5. 공간함수

Measurement

63

6. 서울특별시 중구의 행정경계 (admin_sgg) 면적은 얼마인가 ? 단위는 ㎢로 표현하시오 .

7. 도로 레이어 (road_link2) 중 6 차선 (lanes) 이상의 도로의 길이는 얼마인가 ? 단 . ㎞로 표현하시오 .

8. 서울시의 읍면동 (admin_emd) 중 면적이 가장 큰 상위 10 개를 선택하여 읍면동이름 및 면적값을 조회하시오 .

9. 강 (river) 레이어 중 hole 을 가진 강은 ?

공간정보연구원 - 오픈 소스 GIS 교육 윤정환 ([email protected])64


Proximity & Distance

10. 좌표 X = 197215 Y = 447711 지점에서 1KM 반경 내에 있는 대형매장(stores) 은 무엇인가 ?

Function ST_Distance


Function ST_Dwithin


Function ST_Buffer, ST_Intersects



Spatial Join

65

13. 한강 (river, river_cd = '1') 과 인접한 서울시의 구 (admin_sgg) 는 무엇인가 ?

14. 영등포구 (admin_sgg) 에 속한 대형매장 (stores) 은 무엇인가 ?

15. 소방서 (firestation) 에서 500 미터 반경 내의 도로 중 가장 가까운 도로(road_link2) 와 거리를 계산하시오 .



Overlay

66

16. 서울시 구별 (admin_sgg) 6 차선 이상 도로 (road_link2, lanes) 의 길이를 구하시오 .

17. 서울특별시 중구의 중심점은 어디인가 ? 단 , 경위도로 표시하시오 .

Projection

IV-3. 실습예제 -Ⅱ

- 일러두기 -





IV-3-1. Geometry

Geometry

67

① nyc 의 Query Tool 실행CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES

('Point', 'POINT(0 0)'),

('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),

('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),

('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),

('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

SELECT name, ST_AsText(geom) FROM geometries;

② Spatial Type

SELECT name, ST_GeometryType(geom), ST_NDims(geom),

ST_SRID(geom) FROM geometries;


IV-3-1. Geometry

Geometry

68

③ Spatial Type

SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom) FROM geometries;

④ Point

SELECT ST_AsText(geom) FROM geometries WHERE name = 'Point';

SELECT ST_X(geom), ST_Y(geom) FROM geometries WHERE name = 'Point';

SELECT name, ST_AsText(geom) FROM nyc_subway_stations LIMIT 1;

⑤ Linestrings

SELECT ST_AsText(geom) FROM geometries WHERE name = 'Linestring';

SELECT ST_Length(geom) FROM geometries WHERE name = 'Linestring';


IV-3-1. Geometry

Geometry

69

⑥ Polygons

SELECT ST_AsText(geom) FROM geometries WHERE name LIKE 'Polygon%';

SELECT name, ST_Area(geom) FROM geometries WHERE name LIKE 'Polygon%

⑦ Collections – MultiPoint, MultiLineString, MultiPolygon, GeometryCollection

SELECT name, ST_AsText(geom) FROM geometries WHERE name = 'Collection';

⑧ Geometry Input and Output

SELECT encode( ST_AsBinary(ST_GeometryFromText('LINESTRING(0 0 0,1 0 0,1 1 2)')), 'hex');

SELECT ST_AsEWKT(ST_GeometryFromText('LINESTRING(0 0 0,1 0 0,1 1 2)'));

SELECT encode(ST_AsEWKB(ST_GeometryFromText( 'LINESTRING(0 0 0,1 0 0,1 1 2)')), 'hex');

SELECT ST_AsGeoJSON(ST_GeomFromGML('<gml:Point><gml:coordinates>1,1</gml:coordinates></gml:Point>'));

⑨ Casting from Text

SELECT 0.9::text; SELECT 'POINT(0 0)'::geometry; SELECT 'SRID=4326;POINT(0 0)'::geometry;


IV-3-1. Geometry

Geometry 연습문제 사용할 공간함수

70

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_GeometryType(geometry) returns the type of the geometry

ST_NDims(geometry) returns the number of dimen-sions of the geometry

ST_SRID(geometry) returns the spatial reference iden-tifier number of the geometry

ST_X(point) returns the X ordinate ST_Y(point) returns the Y ordinate ST_Length(linestring) returns the length of the

linestring ST_StartPoint(geometry) returns the first coordinate

as a point ST_EndPoint(geometry) returns the last coordinate as

a point ST_NPoints(geometry) returns the number of coordi-

nates in the linestring ST_Area(geometry) returns the area of the polygons ST_NRings(geometry) returns the number of rings

(usually 1, more if there are holes) ST_ExteriorRing(polygon) returns the outer ring as a

linestring

ST_InteriorRingN(polygon, integer) returns a speci-fied interior ring as a linestring

ST_Perimeter(geometry) returns the length of all the rings

ST_NumGeometries(multi/geomcollection) returns the number of parts in the collection

ST_GeometryN(geometry, integer) returns the speci-fied part of the collection

ST_GeomFromText(text) returns geometry ST_AsText(geometry) returns WKT text ST_AsEWKT(geometry) returns EWKT text ST_GeomFromWKB(bytea) returns geometry ST_AsBinary(geometry) returns WKB bytea ST_AsEWKB(geometry) returns EWKB bytea ST_GeomFromGML(text) returns geometry ST_AsGML(geometry) returns GML text ST_GeomFromKML(text) returns geometry ST_AsKML(geometry) returns KML text ST_AsGeoJSON(geometry) returns JSON text ST_AsSVG(geometry) returns SVG text


IV-3-1. Geometry

Geometry 연습문제

71

Nyc 데이터베이스 사용 , 사용 테이블 nyc_census_blocks

– blkid, popn_total, boroname, geom nyc_streets

– name, type, geom nyc_subway_stations

– name, geom nyc_neighborhoods

– name, boroname, geom

① ‘West Village’ 지역 (neighborhood) 의 면적은 ?

- nyc_neighborhoods

② ‘Manhattan’ 의 면적은 km 표기 ?( 기본단위 m)

- nyc_neighborhoods, nyc_census_blocks

③ 뉴욕시의 얼마나 많은 인구조사 블록 (hole) 이 있는가 ?

- nyc_census_blocks

④ 뉴욕시의 총 도로의 길이는 km 표기 ?

- nyc_streets

⑤ ‘Columbus Cir’ 도로의 길이는 ?

- nyc_streets

⑥ ‘West Village’ 의 boundary 를 JSON 으로 표현 ?

- nyc_neighborhoods

⑦ ‘West Village’ 다중 폴리곤 (Multipolygon) 몇 개 ?

- nyc_neighborhoods

⑧ 뉴욕시의 도로타입별 총 길이는 ?

- nyc_streets


IV-3-2. Spatial Relationships / Joins / Indexing / Project-ing

Spatial Relationships

72

① ST_Equals

- SELECT name, geom, ST_AsText(geom) FROM nyc_subway_stations WHERE name = 'Broad St';

- SELECT name FROM nyc_subway_stations WHERE ST_Equals(geom, '0101000020266900000EEB-D4CF27CF2141BC17D69516315141');

② ST_Intersects

- SELECT name, ST_AsText(geom) FROM nyc_subway_stations WHERE name = 'Broad St';

- SELECT name, boroname FROM nyc_neighborhoods WHERE ST_Intersects(geom, ST_GeomFromText('POINT(583571 4506714)',26918));




73

③ ST_Disjoint

④ ST_Crosses




74

⑤ ST_Overlaps

⑥ ST_Touches




75

⑦ ST_Within and ST_Contains

⑧ ST_Distance and ST_Dwithin

- SELECT ST_Distance( ST_GeometryFromText('POINT(0 5)'), ST_GeometryFromText('LINESTRING(-2 2, 2 2)'));

- SELECT name FROM nyc_streets WHERE ST_DWithin( geom, ST_GeomFromText('POINT(583571 4506714)',26918), 10 );



Spatial Relationships 연습문제 사용할 공간함수

76


count(expression) aggregate to return the size of a set of records

ST_Contains(geometry A, geometry B) returns true if geometry A contains geometry B

ST_Crosses(geometry A, geometry B) returns true if geometry A crosses geometry B

ST_Disjoint(geometry A , geometry B) returns true if the geometries do not “spatially intersect”

ST_Distance(geometry A, geometry B) returns the minimum distance between geometry A and geometry B

ST_DWithin(geometry A, geometry B, radius) returns true if geometry A is radius distance or less from geometry B

ST_Equals(geometry A, geometry B) returns true if geometry A is the same as geometry B

ST_Intersects(geometry A, geometry B) returns true if geometry A intersects geometry B

ST_Overlaps(geometry A, geometry B) returns true if geometry A and geometry B share space, but are not completely contained by each other.

ST_Touches(geometry A, geometry B) returns true if the boundary of geometry A touches geometry B

ST_Within(geometry A, geometry B) returns true if geometry A is within geometry B


Spatial Relationships 연습문제

77


– name, popn_total, boroname, geom nyc_streets


– name, routes, geom nyc_neighborhoods


① ‘Atlantic Commons’ 이름의 도로의 Geometry 값은 ?

- nyc_streets

② ‘Atlantic Commons’d 의 지역 , 자치구는 ?

- nyc_neighborhoods

③ ‘Atlantic Commons’ 도로와 만나는 도로의 이름은 ?

- nyc_streets

④ ‘Atlantic Commons’ 반경 50m 이내의 인구수 ?

- nyc_census_blocks




Spatial Joins

78

① Join

SELECT

subways.name AS subway_name,

neighborhoods.name AS neighborhood_name,

neighborhoods.boroname AS borough

FROM nyc_neighborhoods AS neighborhoods

JOIN nyc_subway_stations AS subways

ON ST_Contains(neighborhoods.geom, subways.-geom) WHERE subways.name = 'Broad St';

② Join and Summarize

‘Manhattan 지역의 총 인구수와 백인 / 흑인의 비율 ?

SELECT

neighborhoods.name AS neighborhood_name, Sum(census.popn_total) AS population,

Round(100.0 * Sum(census.popn_white) / Sum(census.popn_total),1) AS white_pct,

Round(100.0 * Sum(census.popn_black) / Sum(census.popn_total),1) AS black_pct

FROM nyc_neighborhoods AS neighborhoods

JOIN nyc_census_blocks AS census

ON ST_Intersects(neighborhoods.geom, census.-geom) WHERE neighborhoods.boroname = 'Manhattan' GROUP BY neighborhoods.name

ORDER BY white_pct DESC;



Spatial Joins

79

③ Join and Summarize

A 지하철의 반경 200m 이내의 총인구 , 인종별 비율 ?

SELECT

100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,

100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,

Sum(popn_total) AS popn_total

FROM nyc_census_blocks AS census

JOIN nyc_subway_stations AS subways

ON ST_DWithin(census.geom, subways.geom, 200) WHERE strpos(subways.routes,'A') > 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


Spatial Joins 연습문제

80




– name, routes, geom nyc_neighborhoods


① ‘Little Italy’ 무슨 역이고 , 어떤 노선 인가 ?

- nyc_subway_stations, nyc_subway_stations

② 6-train 지나는 모든 지역은 어디인가 ?


③ ‘Battery Park’ 의 총인구 ?


④ ‘Upper West Side’, ‘Upper East Side’ 인구밀도는 ?




Spatial Indexing

81

① 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)



ON ST_Intersects(neighborhoods.geom, blocks.-geom)





Projecting 연습문제 사용할 공간함수

82


ST_Length(linestring) returns the length of the linestring

ST_SRID(geometry, srid) returns the SRID of the geometry

ST_Transform(geometry, srid) converts ge-ometries into different spatial reference systems

ST_GeomFromText(text) returns geometry ST_AsText(geometry) returns WKT text ST_AsGML(geometry) returns GML text




– name, geom nyc_neighborhoods


① 뉴욕시 모든 도로 길이는 ?

- nyc_streets

② SRID 2831 로 변환된 뉴욕시 모든 도로 길이는 ?

- nyc_streets

③ ‘Broad St’ subway station 을 KML 표시 ?

- nyc_subway_stations

Projecting 연습문제


IV-3-3. Geograpy

Gemotry & Geograpy

83

① 거리 구하기- 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)') -- Ice-land );

- 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;


IV-3-3. Geograpy

Gemotry & Geograpy

84

② 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;


IV-3-4. Geometry Constructing Functions

ST_Centroid / ST_PointOnSurface

85

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';


IV-3-4. Geometry Constructing Functions

ST_Intersection

86

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


IV-3-5. More Spatial Joins

Census Tracts Table 생성

87

① 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.boron-ame 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;

문제


IV-3-5. More Spatial Joins

Polygon/Polygon Joins

88

SELECT

Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct, n.name, n.boron-ame

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


IV-4. Raster Reference

Raster Management (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Management_Functions)

89

AddRasterConstraints — Adds raster constraints to a loaded raster table for a specific column that con-strains 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.



Raster Constructors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Constructors)

90

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 spa-tial 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 dimen-sions of the output rasters.

ST_FromGDALRaster — Returns a raster from a supported GDAL raster file.



Raster Accessors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Accessors)

91

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 lati-

tude) 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).



Raster Accessors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Accessors)

92

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 (longi-

tude 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 co-

ordinate (xw, yw) represented in world spatial reference system of raster.



Raster Band Accessors (http://postgis.net/docs/manual-2.1/RT_reference.html#RasterBand_Accessors)

93

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 as-sumed.

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 as-

sumed. 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.



Raster Pixel Accessors and Setters (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Pixel_Accessors)

94

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 rep-resented 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.



Raster Pixel Accessors and Setters (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Pixel_Accessors)

95

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.



Raster Editors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Editors)

96

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 resam-pling 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 algo-rithm. Default is NearestNeighbor.



Raster Editors (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Editors)

97

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.



Raster Band Editors (http://postgis.net/docs/manual-2.1/RT_reference.html#RasterBand_Editors)

98

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.



Raster Band Statistics and Analytics (http://postgis.net/docs/manual-2.1/RT_reference.html#RasterBand_Stats)

99

ST_Count — Returns the number of pixels in a given band of a raster or raster coverage. If no band is speci-fied 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 pop-ulation. 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 de-faults 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.



Raster Outputs (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Outputs)

100

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) im-age (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.



Raster Processing - Map Algebra (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Processing_MapAlgebra)

101

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 in-dexes 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" param-eter. 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 Post-greSQL function on the input raster band and of pixeltype prodived. Band 1 is assumed if no band is speci-fied.

ST_MapAlgebraFct — 2 band version - Creates a new one band raster formed by applying a valid Post-greSQL 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.



Raster Processing - Map Algebra (http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Processing_MapAlgebra)

102

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.



Raster Processing - Built-in Map Algebra Callback Functions

103

ST_Distinct4ma — Raster processing function that calculates the number of unique pixel values in a neigh-borhood.

ST_InvDistWeight4ma — Raster processing function that interpolates a pixel's value from the pixel's neigh-borhood.

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) be-

tween 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.



Raster Processing - DEM (Elevation)

104

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 ter-

rain. ST_TPI — Returns a raster with the calculated Topographic Position Index. ST_TRI — Returns a raster with the calculated Terrain Ruggedness Index.



Raster Processing - Raster to Geometry

105

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 BandNo-

DataValue. 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.



Raster Operators (http://postgis.net/docs/manual-2.1/RT_reference.html#RT_Operators)

106

&& — 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.



Raster and Raster Band Spatial Relationships (http://postgis.net/docs/manual-2.1/RT_reference.html#RT_Operators)

107

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.


IV-5. Topology

Topology and TopoGeometry Management

108

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.topol-ogy 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


IV-5. Topology

Topology Constructors

109

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 de-

tailing success. TopoGeo_AddPoint — Adds a point to an existing topology using a tolerance and possibly splitting an exist-

ing 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 ex-

isting edges/faces.


IV-5. Topology

Topology Editors

110

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 ex-isting 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 re-place 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 origi-nal 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 re-

placing 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 geome-

try exists as a node an error is thrown. REturns description of move.


IV-5. Topology

Topology Editors

111

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 iso-lated (is start or end of an edge), then an exception is thrown.


IV-5. Topology

Topology Accessors

112

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 topol-

ogy. 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.


IV-5. Topology

Topology Processing

113

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 exist-ing) 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 algo-

rithm. 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)


IV-5. Topology

TopoGeometry Editors

114

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

V. 어플리케이션을 이용한 PostGIS 사용하기

- 일러두기 -





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 설치하기 - 설치화면 기본 순서대로 설치


V-2. QGIS 이용한 PostGIS 이용하기

QGIS 에서 레이어 추가하기

116

① QGIS 2.0 실행② [ 플러그인 ] – [ 플러그인관리및설치 ] SPIT 설치 확인③ [ 데이터베이스 ] – [Spit(s)] –[ 쉐입 파일을 PostGIS에 가져오기④ [ 새로만들기 ] 선택 , 정보 입력 후 [ 확인 ] 선택⑤ [ 연결 ] 선택⑥ [ 추가 ] 선택 후 , Shape 파일 선택 추가⑦ 기본 지오매트리 컬럼 이름 사용 체크 해제 후 ‘ geom’ 변경⑧ 기본 SRID 사용 체크 해제 후 ‘ 5174’ 변경⑨ [ 확인 ] 선택⑩ pgAdmin 실행 후 korea 데이터베이스에 입력한 shape 파일 테이블 확인


V-2. QGIS 이용한 PostGIS 이용하기

QGIS 에서 PostGIS 레이어 불러와 사용하기

117

① QGIS 2.0 실행② [ 레이어 ] – [PostGIS 레이어 추가하기 ] 선택③ [ 새로만들기 ] 선택 , 정보 입력 후 [ 확인 ] 선택 – nyc 사용④ [ 연결 ] 선택⑤ public 선택 원하는 Shape 선택 후 [ 추가 ] 선택⑥ 선택된 레이어 변경하기


V-3. OpenJUMP 이용한 PostGIS 이용하기

OpenJUMP 에서 PostGIS 레이어 불러와 사용하기

118

① OpenJUMP 1.6 실행② Working, System 오른 쪽 마우스 선택 후 [Add Datas-tore Layer] 선택③ Coonection[ ] 아이콘 선택④ [Add] 선택⑤ Connection Manager 화면에서 [ok] 선택⑥ Add Datastore Layer 에서 Dataset( 레이어선택 ) 후 [ok] 선택⑦ 여러레이어를 올려 레이어 변경하기


V-4. uDig 이용한 PostGIS 이용하기

uDig 에서 PostGIS 레이어 불러와 이용하기

119

① uDig 1.46 실행② [ 파일 ] – [ 새로운 지도작업 ] – [ 지도 ] 선택③ [ 레이어 ] – [ 추가 ] 선택④ [PostGIS] 선택 , [ 다음 ] 선택⑤ [Add] 선택⑥ Host, User Name, Password 입력⑦ Database 콤보박스 [korea] 선택 후 [List] 선택⑧ 여러 레이어 체크박스 선택 후 [ 완료 ]


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 통한 설치

http://jdbc.postgresql.org/download.html

http://jdbc.postgresql.org/download.html


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 -- <connec-tionStrings> <add name="DSN" connection-String="server=localhost;database=mydb;Port=5432;User Id=myuser;password=mypwd"/> </con-nectionStrings>

③ .NET Driver 설치 방법 - 직접 다운로드 사이트 접속 다운 후 등록 - Application Stack Builder 통한 설치

// Code for TestRaster.ashx <%@ WebHandler Language="C#" Class="TestRaster" %> 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)); re-sult = (byte[]) command.ExecuteScalar(); conn.Close(); } } catch (Exception ex) { result = null; context.Response.Write(ex.Message.Trim()); } return result; } }

http://npgsql.projects.postgresql.org/

VI. PostGIS 그밖의 기능

- 일러두기 -





VI-1. Tuning PostgreSQL for Spatial

Backend Configuration Editer

122

① pgAdmin 실행② [File] – [Open postgresql.conf] – postgresql.conf 파일 선택

Shared_buffer

Default Value : 32MB Recommended Value : 500MB (75%)



Work_mem

123

Default Value : 1MB Recommended value : 16MB

Maintenance_work_mem

Default Value : 16MB Recommended Value : 16MB(ex.128MB)



Wal_buffers

124

Default Value : 1MB Recommended value : 16MB

Checkpoint_segments

Default Value : 3MB Recommended Value : 6MB



Ramdom_page_cost

125

Default Value : 4.0 Recommended value : 2.0

Seq_page_cost

Default Value : 1.0 Recommended Value : 1.0



Reload configuration

126

① pgAdmin 접속 종료 ( 종료 )

② OpenSeo Suit Dashboard Shutdown, Start

③ pgAdim 접속 (재시작 )

④ 설정 정보가 반영된다 .


VI-2. Query Plans

Query Plans

127

① pgAdmin 실행 ② Query Tool 실행③ [Query] – [Query Analyze] 선택④ Query 실행 – nyc db 사용SELECT

Sum(popn_total)



ON ST_Intersects(neighborhoods.geom, blocks.-geom)


⑤ [Output pane] – [Explain] 선택⑥ Sequence Scan : 테이블의모든 Row Scan

⑦ Index Scan : 제약조건 스캔⑧ Nested Loop

⑨ Hash Aggregate


VI-3. 백업 & 복원

백업

128



백업

129



복원

130

절차– 템플릿 데이터베이스 생성–Restore( 복원 )



복원

131



주의사항

132

① 상위버전과 하위버전 호환안됨② 하위버전 백업 상위버전 Restore 가능③ 상위버전 백업 하위버전 Restore 오류

버전충돌시 테이블 / 레이어 분리백업 / 복원


VI-4. pgRouting

pgRouting

133

pgRouting 은 PostGIS/PostgreSQL 에 라우팅 기능을 추한 PostGIS 의 ExtensionCamptocamp SA 에 의해 시작된 pgDijkstra 의 확장판이며 Orkney 에 의해 확장현재 Georepublic 에 의해 개발 및 유지 관리되고 있음pgRouting 는 GPLv2 라이선스 하에 사용 가능하며 개인 , 기업 및 조직의 커뮤니티에 의해 지원

데이터베이스 기반 라우팅 접근 방법의 장점– 데이터와 속성은 Quantum GIS, uDig 그리고 JDBC, ODBC 또는 Pl/pgSQL 등 다양한

클라이언트에 의해 직접 갱신 가능하며 , 클라이언트는 PC나 모바일 기기가 될 수 있음– 데이터 변경 내용은 라우팅 엔진을 통해 즉시 반영되며 , 재계산이 필요 없음– “cost” 파라미터는 SQL 을 통해 동적으로 계산되고 그 값은 다중 필드 및 테이블의 다양한 값을

사용할 수 있음

http://www.pgrouting.org/

http://www.camptocamp.com/

http://www.camptocamp.com/

http://www.orkney.co.jp/en/

http://georepublic.de/en/projects/pgrouting/


VI-4. pgRouting

지원 알고리즘

134

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


VI-4. pgRouting

Network 데이터셋

135

도로 네트워크 데이터셋은 최소한 다음의 정보를 포함

– 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 등


VI-4. pgRouting

공공기관 생산 도로 네트워크

136

공공기관에서 생산하는 도로네트워크로 활용 가능한 데이터는 다음의 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) 는 없음

http://nodelink.its.go.kr/

http://www.utis.go.kr/

http://www.ktdb.go.kr/


VI-4. pgRouting

공공기관 생산 도로 네트워크 ( 사례지역 : 제주도 , 배경은 주거지 (건물 ) 밀집도 )

137

새주소 도로중심선


VI-4. pgRouting

Installing pgRouting

138

웹사이트에 http://pgrouting.org/download.html PostGIS 2.1.1 bundle 설치시 기본적으로 pgrout-

ing 2.0 설치됨 [pgAdmin] 실행 [New database] 생성 CREATE EXTENSION postgis;

CREATE EXTENSION pgrouting;

http://pgrouting.org/download.html




VI-4. pgRouting

pgRouting reference

139

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/– 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

http://websi.openvrp.com/

http://map.veloland.ch/

http://www.ridethecity.com/

http://www.pgrouting.org/gallery.html

http://www.pgrouting.org/

http://workshop.pgrouting.org/

http://underdark.wordpress.com/2011/02/07/a-beginners-guide-to-pgrouting/

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


VI-5. Reference

reference

140

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/

http://postgis.org/

http://postgis.org/docs/

http://www.postgresl.org/


http://www.postgresql.org/docs/

http://jdbc.postgresql.org/

http://npgsql.projects.postgresql.org/

http://www.pygresql.org/

http://www.postgresql.org/docs/8.4/static/libpq.html

http://www.pgadmin.org/

http://udig.refractions.net/

http://qgis.org/

http://openjump.org/

감사합니다 .

Q&A

한국어 지부 함께 성장하는 새로운 방법 , 오픈 소스 소프트웨어 !!

공간정보연구원 PostGIS 강의교재

Software

Transcript of 공간정보연구원 PostGIS 강의교재