16.4. Lesson: 공간 쿼리

공간 쿼리는 다른 데이터베이스 쿼리와 크게 다르지 않습니다. 도형 열도 다른 어떤 데이터베이스 열과 똑같이 사용할 수 있습니다. 데이터베이스에 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’ 값은 이 페이지의 공간을 절약하기 위해 중간에서 잘랐습니다. 사람이 읽을 수 있는 좌표로 포인트를 보려면, 앞의 “View a point as WKT” 부분에서 한 작업과 비슷하게 해보십시오.

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

Answer

The units being used by the example query are degrees, because the CRS that the layer is using is WGS 84. This is a Geographic CRS, which means that its units are in degrees. A Projected CRS, like the UTM projections, is in meters.

Remember that when you write a query, you need to know which units the layer’s CRS is in. This will allow you to write a query that will return the results that you expect.

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. Try Yourself

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

Answer

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.the_geom) as point
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_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.the_geom
    FROM building a, region b
      WHERE st_within(a.the_geom, b.the_geom)
      AND b.name = 'KwaZulu';

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

인접 검색

Hokkaido 지역에 붙어 있는 모든 지역들의 명칭 목록을 가져오십시오:

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

결과 :

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

뷰 생성:

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

QGIS에서 뷰를 봅니다:

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

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

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

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

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

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

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

QGIS에서 뷰를 봅니다:

버퍼 생성이라는 추가적인 단계 없이 어떤 주어진 거리 안에 있는 모든 객체를 선택할 수도 있습니다:

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

이렇게 하면 임시적인 버퍼 단계 없이도 동일한 결과를 달성할 수 있습니다.

유일 값 선택

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

SELECT DISTINCT a.name
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_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.the_geom, b.the_geom)) as text,
        ST_SHORTESTLINE(a.the_geom, b.the_geom) AS the_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.the_geom, b.the_geom)) as text,
         ST_LONGESTLINE(a.the_geom, b.the_geom) AS the_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.the_geom) as the_geom
    FROM road a
      WHERE a.id = 1;

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

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

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

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

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(the_geom, 50) AS the_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.the_geom)) AS the_geom
    FROM building a
    GROUP BY a.name;

16.4.5. In Conclusion

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

16.4.6. What’s Next?

다음으로 좀 더 복잡한 도형의 구조 및 PostGIS를 써서 생성하는 방법을 알아보겠습니다.