17.4. Lesson: 공간 쿼리

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

이 강의의 목표: 공간 기능이 얼마나 “일반적인” 비공간 기능과 비슷하게 시행되는지 보기.

17.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가 아닐까요? 다른 단위를 쓸 수도 있지 않을까요?

결과 확인

17.4.2. 공간 인덱스

We also can define spatial indexes. A spatial index makes your spatial queries much faster. To create a spatial index on the geometry column use:

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)

17.4.3. Try Yourself moderate 중급

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

결과 확인

17.4.4. PostGIS 공간 기능 시연

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

To start, create a new database (exit the psql shell first):

createdb postgis_demo

Remember to install the postgis extensions:

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

Next, import the data provided in the exercise_data/postgis/ directory. Refer back to the previous lesson for instructions, but remember that you’ll need to create a new PostGIS connection to the new database. You can import from the terminal or via DB Manager. Import the files into the following database tables:

  • points.shp 를 building 테이블로

  • lines.shp 파일을 road 테이블로

  • polygons.shp 파일을 region 테이블로

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

이제 테이블을 임포트했으니, PostGIS를 사용해서 데이터를 쿼리할 수 있습니다. 다시 터미널(커맨드 입력 창)으로 가서 다음 명령을 실행해서 psql 프롬프트로 들어가십시오.

psql postgis_demo

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

17.4.4.1. 위치에 따른 선택

Get all the buildings in the KwaZulu region:

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)

Or, if we create a view from it:

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에 뷰를 레이어로 추가해서 볼 수 있습니다.

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

17.4.4.2. 인접 검색

Show a list of all the names of regions adjoining the Hokkaido region:

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)

As a view:

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

QGIS에서 뷰를 봅니다.

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

Note the missing region (Queensland). This may be due to a topology error. Artifacts such as this can alert us to potential problems in the data. To solve this enigma without getting caught up in the anomalies the data may have, we could use a buffer intersect instead:

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

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

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

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

Select using the buffer:

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

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

It is also possible to select all objects within a given distance, without the extra step of creating a buffer:

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

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

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

17.4.4.3. 유일 값 선택

Show a list of unique town names for all buildings in the Queensland region:

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)

17.4.4.4. 심화 예시

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;

17.4.5. In Conclusion

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

17.4.6. What’s Next?

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