postgis 공간 SQL

제가 사용하는 공간 쿼리는 특정 사업에 관여된거라 공식문서를 참조하여 올립니다.

 

postgis는 설치했는데 공간필드를 생성할 수 없는 경우 sql창이 psql 프롬프트에서 다음과 같이 extension을 생성해야 합니다. 마지막으로 select postgis_full_version(); 버전을 확인하면 됩니다.

create extension postgis;

create extension postgis_topology;

select postgis_full_version();

 

공간함수에는 많은 종류가 있으나 그 동안 작업에서는

st_area(면적계산), st_contains(포함관계), st_intersects(교차관계)을 주로 많이 사용했습니다.

그 외에도 데이터작업 시에는 st_makevalid, st_setsrid, st_point, st_x, st_y 등이 사용되었습니다.

 

공간정보는 형상에 따라 폴리곤(면), 라인, 포인트(점)등이 있습니다.

폴리곤 형태중에는 도넛모양으로 구멍이 파진 것도 있습니다. 이러한 폴리곤은 멀티폴리곤리고 하는데 대부분의 경계형 데이터는 멀티폴리곤입니다. 따라서 공간속성을 저장하는 공간필드도 멀티폴리곤, 폴리곤, 폴리라인, 라인, 포인트등의 유형이 있습니다. 그리고 공간필드에는 어떤 좌표계를 사용하는 지 EPSG좌표계가 설정됩니다.

경계와 포인트 데이터의 공간연산을 위해서 두개의 데이터의 좌표가 같아야 합니다. 그래서 일반적으로 공간연산전에 좌표계를 통일하는 작업이 선행됩니다.

공간연산은 경계가 많고 복잡할 수록 시간이 매우 많이 걸립니다. 한두시간은 빠른 거고 일주일이상이 걸리기도 합니다. 이 경우 지역분할 등을 잘해서 처리를 해야 합니다.

예시들은 선형 도로 테이블 및 폴리곤형 지자체 경계 테이블 입니다. 

bc_roads 테이블은 다음과 같이 정의됩니다:

Column      | Type              | Description
------------+-------------------+-------------------
gid         | integer           | Unique ID
name        | character varying | Road Name
the_geom    | geometry          | Location Geometry (Linestring)

bc_municipality 테이블은 다음과 같이 정의됩니다:

Column     | Type              | Description
-----------+-------------------+-------------------
gid        | integer           | Unique ID
code       | integer           | Unique ID
name       | character varying | City / Town Name
the_geom   | geometry          | Location Geometry (Polygon)

 

예제1) 프린스 조지(Prince George) 시의 면적이 몇 헥타르입니까?

이 쿼리는 속성 조건(지자체 명칭)과 공간 계산(면적)을 결합하고 있습니다.

SELECT
  ST_Area(the_geom)/10000 AS hectares
FROM bc_municipality
WHERE name = 'PRINCE GEORGE';

hectares
------------------
32657.9103824927
(1 row)

 

예제2) 해당 지역에서 가장 면적이 넓은 지자체는 어디입니까?

이 쿼리는 쿼리 조건에 공간 측정을 넣어야 합니다. 이 문제에 접근하는 데에는 몇 가지 방법이 있지만, 가장 효율적인 방법은 다음과 같습니다:

SELECT
  name,
  ST_Area(the_geom)/10000 AS hectares
FROM
  bc_municipality
ORDER BY hectares DESC
LIMIT 1;

name           | hectares
---------------+-----------------
TUMBLER RIDGE  | 155020.02556131
(1 row)

예제3) 각 지자체 안에 완전히 들어가 있는 도로의 총연장은 얼마나 됩니까?

이 문제는 "공간 연결(spatial join)"의 예시입니다. 두 테이블로부터 데이터를 함께 모으지만(연결), 연결 조건으로 흔히 쓰이는 공통 키(common key)를 기반으로 연결하는 관계 접근법 대신 공간 교차 조건("들어가 있는")을 이용하기 때문입니다:

SELECT
  m.name,
  sum(ST_Length(r.the_geom))/1000 as roads_km
FROM
  bc_roads AS r,
  bc_municipality AS m
WHERE
  ST_Contains(m.the_geom,r.the_geom)
GROUP BY m.name
ORDER BY roads_km;

name                        | roads_km
----------------------------+------------------
SURREY                      | 1539.47553551242
VANCOUVER                   | 1450.33093486576
LANGLEY DISTRICT            | 833.793392535662
BURNABY                     | 773.769091404338
PRINCE GEORGE               | 694.37554369147
...
테이블에 있는 모든 도로를 최종 결과(현재 예시 테이블의 경우 약 250,000개의 도로)로 요약하기 때문에 이 쿼리는 시간이 걸립니다. 더 작은 오버레이(수백 개의 도로에 대한 수천 레코드 정도)라면, 응답도 매우 빠를 것입니다.

 

예제4) 프린스 조지 시 내부에 있는 모든 도로를 가진 새로운 테이블을 생성하십시오.

이 지시는 "오버레이"에 대한 예시입니다. 오버레이 쿼리는 두 테이블을 읽어들여 공간적으로 오려내거나 잘라낸 결과값으로 이루어진 새로운 테이블을 산출합니다. 앞에서 나온 "공간 연결"과는 달리, 이 쿼리는 실제로 새 도형을 생성합니다. 오버레이는 터보차저가 달린 공간 연결이라 할 수 있고, 더 정확한 분석 작업에 유용합니다:

CREATE TABLE pg_roads as
SELECT
  ST_Intersection(r.the_geom, m.the_geom) AS intersection_geom,
  ST_Length(r.the_geom) AS rd_orig_length,
  r.*
FROM
  bc_roads AS r,
  bc_municipality AS m
WHERE  m.name = 'PRINCE GEORGE' AND ST_Intersects(r.the_geom, m.the_geom);

 

예제5) 빅토리아 시의 "더글러스 거리(Douglas St)"의 길이는 몇 킬로미터입니까?

SELECT
  sum(ST_Length(r.the_geom))/1000 AS kilometers
FROM
  bc_roads r,
  bc_municipality m
WHERE  r.name = 'Douglas St' AND m.name = 'VICTORIA'
        AND ST_Contains(m.the_geom, r.the_geom) ;

kilometers
------------------
4.89151904172838
(1 row)

 

예제6) 구멍이 있는 지자체 폴리곤 가운데 가장 넓은 곳은 어디입니까?

SELECT gid, name, ST_Area(the_geom) AS area
FROM bc_municipality
WHERE ST_NRings(the_geom) > 1
ORDER BY area DESC LIMIT 1;

gid  | name         | area
-----+--------------+------------------
12   | SPALLUMCHEEN | 257374619.430216
(1 row)

 

[참고] https://postgis.net/docs/manual-3.0/postgis-ko_KR.html

PostGIS 3.0.0 사용자 지침서