중요

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

16.4. 수업: 공간 쿼리

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

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

16.4.1. 공간 연산자

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

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

결과:

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

참고

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

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

해답

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

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

16.4.2. 공간 인덱스

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

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (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     |
  geom      | geometry              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (geom)  <-- new spatial key added
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(geom) = 'ST_Point'::text
  OR 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 (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에 뷰를 레이어로 추가해서 볼 수 있습니다:

이웃 선택하기

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에서 뷰를 봅니다:

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

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

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

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

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

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

이 쿼리에서 다른 어떤 테이블과도 마찬가지로 원본 버퍼 뷰를 사용했습니다. 뷰에 a 라는 별명을 부여하고, 해당 뷰의 a.geom 이라는 도형 필드를 사용해서 region 테이블(별명 b)에서 뷰와 교차하는 모든 폴리곤을 선택했습니다. 그러나 Hokkaido 자체는 이 선택 선언문에서 제외되었습니다. 해당 지역이 아니라 인접한 지역만을 원하기 때문입니다.

QGIS에서 뷰를 봅니다:

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

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

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

유일 값 선택하기

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를 사용해서 이들을 생성하는 방법을 알아보겠습니다.