중요

번역은 여러분이 참여할 수 있는 커뮤니티 활동입니다. 이 페이지는 현재 98.18% 번역되었습니다.

16.4. 수업: 공간 쿼리

공간 쿼리는 다른 데이터베이스 쿼리와 크게 다르지 않습니다. 도형 열도 다른 모든 데이터베이스 열과 똑같이 사용할 수 있습니다. 데이터베이스에 PostGIS를 설치하면, 데이터베이스를 쿼리할 수 있는 추가적인 기능들이 생깁니다.

이 수업의 목표: 공간 기능이 얼마나 “일반적인” 비공간 기능과 비슷하게 구현되는지 알아보기.

16.4.1. 공간 연산자

포인트(X,Y)에서 2˚ 거리 안에 어떤 포인트가 있는지 알고 싶을 경우 다음과 같이 쿼리할 수 있습니다:

select *
from people
where st_distance(the_geom,'SRID=4326;POINT(33 -34)') < 2;

결과:

 id |     name     | house_no | street_id |   phone_no    |   the_geom
----+--------------+----------+-----------+---------------+---------------
  6 | Fault Towers |       34 |         3 | 072 812 31 28 | 01010008040C0
(1 row)

참고

앞의 ‘the_geom’ 값은 이 페이지의 공간을 절약하기 위해 중간에서 잘랐습니다. 사람이 읽을 수 있는 좌표로 포인트를 보려면, 앞의 “포인트를 WKT로 보기” 부분에서 한 작업과 비슷하게 해보십시오.

이 쿼리가 2˚ 내에 있는 모든 포인트를 반환하는지 어떻게 알 수 있을까요? 왜 2m 가 아닐까요? 다른 단위를 쓸 수도 있지 않을까요?

해답

이 예제 쿼리는 도 단위를 사용합니다. 레이어가 사용하는 좌표계가 WGS84이기 때문입니다. WGS84는 지리 좌표계로, 다시 말해 도 단위를 사용한다는 뜻입니다. UTM 투영체 같은 투영 좌표계가 미터 단위를 사용합니다.

여러분이 쿼리를 작성할 때 레이어의 좌표계가 어떤 단위를 사용하는지 알고 있어야 한다는 점을 기억하십시오. 단위를 알고 있어야 여러분이 기대하는 결과를 반환하는 쿼리를 작성할 수 있습니다.

16.4.2. 공간 인덱스

여러분이 공간 인덱스도 정의할 수 있습니다. 공간 인덱스는 공간 쿼리를 더 빠르게 해줍니다. 도형 열에 대해 공간 인덱스를 생성하려면 다음 선언문을 사용하십시오:

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (the_geom);

\d people

결과:

Table "public.people"
   Column   |         Type          |                Modifiers
 -----------+-----------------------+----------------------------------------
  id        | integer               | not null default
            |                       | nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  the_geom  | geometry              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (the_geom)  <-- new spatial key added
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(the_geom) = 'ST_Point'::text
  OR the_geom IS NULL)
Foreign-key constraints:
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

16.4.3. 혼자서 해보세요: ★★☆

‘cities’ 테이블을 수정해서 도형 열에 공간 인덱스를 생성하십시오.

해답

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (the_geom);

16.4.4. PostGIS 공간 기능 시연

PostGIS의 공간 기능을 시연하기 위해, 몇몇 (가상) 데이터를 담고 있는 새 데이터베이스를 생성하겠습니다.

첫 번째로, 새 데이터베이스를 생성하십시오 (먼저 psql 셸에서 나오십시오):

createdb postgis_demo

PostGIS 확장 프로그램을 설치하는 것을 잊지 마십시오:

psql -d postgis_demo -c "CREATE EXTENSION postgis;"

두 번째로, exercise_data/postgis/ 디렉터리에서 제공하는 데이터를 가져오십시오. 이전 강의에서 배운 내용대로 하되, 새 데이터베이스에는 새 PostGIS 연결을 생성해야 한다는 것을 기억하십시오. 터미널에서 또는 데이터베이스 관리자를 통해 데이터를 가져올 수 있습니다. 파일을 다음 데이터베이스 테이블로 가져오십시오:

  • points.shp 를 building 테이블로

  • lines.shp 를 road 테이블로

  • polygons.shp 를 region 테이블로

평상시대로 Add PostGIS Layers 대화 창을 통해 이 세 데이터베이스 레이어를 QGIS로 불러오십시오. 이 레이어들의 속성 테이블을 열면, id 필드와 함께 PostGIS 가져오기 과정에서 생성된 gid 필드를 둘 다 가지고 있다는 사실을 알 수 있습니다.

이제 테이블을 가져왔으니, PostGIS를 사용해서 데이터를 쿼리할 수 있습니다. 다시 터미널(명령줄 창)로 가서 다음 명령을 실행해서 psql 프롬프트로 들어가십시오:

psql postgis_demo

다음 선택된 선언문들을 시연해서 뷰를 생성하겠습니다. 이렇게 하면 QGIS에서 뷰를 열어 결과물을 볼 수 있습니다.

위치로 선택하기

KwaZulu 지역에 있는 모든 건물을 선택하십시오:

SELECT a.id, a.name, st_astext(a.geom) as point
  FROM building a, region b
    WHERE st_within(a.geom, b.geom)
    AND b.name = 'KwaZulu';

결과:

 id | name |                  point
----+------+------------------------------------------
 30 | York | POINT(1622345.23785063 6940490.65844485)
 33 | York | POINT(1622495.65620524 6940403.87862489)
 35 | York | POINT(1622403.09106394 6940212.96302097)
 36 | York | POINT(1622287.38463732 6940357.59605424)
 40 | York | POINT(1621888.19746548 6940508.01440885)
(5 rows)

또는, 다음과 같이 뷰를 생성할 경우:

CREATE VIEW vw_select_location AS
  SELECT a.gid, a.name, a.geom
    FROM building a, region b
      WHERE st_within(a.geom, b.geom)
      AND b.name = 'KwaZulu';

QGIS에 뷰를 레이어로 추가해서 볼 수 있습니다:

../../../_images/kwazulu_view_result.png

이웃 선택하기

Hokkaido 지역에 인접한 모든 지역들의 이름 목록을 봅시다:

SELECT b.name
  FROM region a, region b
    WHERE st_touches(a.geom, b.geom)
    AND a.name = 'Hokkaido';

결과:

    name
--------------
 Missouri
 Saskatchewan
 Wales
(3 rows)

뷰를 생성하려면:

CREATE VIEW vw_regions_adjoining_hokkaido AS
  SELECT b.gid, b.name, b.geom
    FROM region a, region b
      WHERE st_touches(a.geom, b.geom)
      AND a.name = 'Hokkaido';

QGIS에서 뷰를 봅니다:

../../../_images/adjoining_result.png

누락된 지역(Queensland)이 보이십니까? 위상 오류 때문일지도 모릅니다. 이런 오류는 데이터 안에 어떤 잠재적인 문제가 있다고 경고해주는 것일 수 있습니다. 데이터에 있을지도 모르는 이상을 피해 이 수수께끼를 풀려면, 버퍼 교차(buffer intersect)를 대신 사용할 수 있습니다:

CREATE VIEW vw_hokkaido_buffer AS
  SELECT gid, ST_BUFFER(geom, 100) as geom
    FROM region
      WHERE name = 'Hokkaido';

Hokkaido 지역 주변에 100m의 버퍼를 생성했습니다.

어두운 영역이 버퍼입니다:

../../../_images/hokkaido_buffer.png

이 버퍼를 써서 선택합니다:

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.geom, b.geom)
    AND b.name != 'Hokkaido';

In this query, the original buffer view is used as any other table would be. It is given the alias a, and its geometry field, a.geom, is used to select any polygon in the region table (alias b) that intersects it. However, Hokkaido itself is excluded from this select statement, because we don’t want it; we only want the regions adjoining it.

QGIS에서 뷰를 봅니다:

../../../_images/hokkaido_buffer_select.png

버퍼를 생성한다는 추가 단계 없이 지정한 거리 안에 있는 모든 객체를 선택할 수도 있습니다:

CREATE VIEW vw_hokkaido_distance_select AS
  SELECT b.gid, b.name, b.geom
    FROM region a, region b
      WHERE ST_DISTANCE (a.geom, b.geom) < 100
      AND a.name = 'Hokkaido'
      AND b.name != 'Hokkaido';

이렇게 하면 중간 버퍼 단계 없이도 동일한 결과를 달성할 수 있습니다:

../../../_images/hokkaido_distance_select.png

유일 값 선택하기

Queensland 지역에 있는 모든 건물들에 대해 유일한 도시 이름 목록을 봅시다:

SELECT DISTINCT a.name
  FROM building a, region b
    WHERE st_within(a.geom, b.geom)
    AND b.name = 'Queensland';

결과:

  name
---------
 Beijing
 Berlin
 Atlanta
(3 rows)

심화 예시

CREATE VIEW vw_shortestline AS
  SELECT b.gid AS gid,
        ST_ASTEXT(ST_SHORTESTLINE(a.geom, b.geom)) as text,
        ST_SHORTESTLINE(a.geom, b.geom) AS geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_longestline AS
  SELECT b.gid AS gid,
         ST_ASTEXT(ST_LONGESTLINE(a.geom, b.geom)) as text,
         ST_LONGESTLINE(a.geom, b.geom) AS geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_road_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
    FROM road a
      WHERE a.id = 1;

CREATE VIEW vw_region_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
    FROM region a
      WHERE a.name = 'Saskatchewan';

SELECT ST_PERIMETER(a.geom)
  FROM region a
    WHERE a.name='Queensland';

SELECT ST_AREA(a.geom)
  FROM region a
    WHERE a.name='Queensland';

CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(geom, 20) AS geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(geom, 50) AS geom
    FROM road;

CREATE VIEW vw_convex_hull AS
  SELECT
    ROW_NUMBER() over (order by a.name) as id,
    a.name as town,
    ST_CONVEXHULL(ST_COLLECT(a.geom)) AS geom
    FROM building a
    GROUP BY a.name;

16.4.5. 결론

PostGIS의 새로운 데이터베이스 기능을 사용해서 공간 객체를 쿼리하는 방법을 배웠습니다.

16.4.6. 다음은 무엇을 배우게 될까요?

다음으로 좀 더 복잡한 도형들의 구조를 살펴보고 PostGIS를 사용해서 이들을 생성하는 방법을 알아보겠습니다.