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가 아닐까요? 다른 단위를 쓸 수도 있지 않을까요?
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.4. PostGIS 공간 기능 시연¶
PostGIS의 공간 기능을 시연하기 위해, 몇몇 (가상) 데이터를 담고 있는 새 데이터베이스를 생성하겠습니다.
첫 번째로, 새 데이터베이스를 생성하십시오. (먼저 psql 셸에서 나오십시오.)
createdb postgis_demo
PostGIS 확장 프로그램을 설치하는 것을 잊지 마십시오.
psql -d postgis_demo -c "CREATE EXTENSION postgis;"
두 번째로, exercise_data/postgis/ 디렉터리에서 제공하는 데이터를 임포트하십시오. 이전 강의에서 배운 내용대로 하되, 새 데이터베이스에는 새 PostGIS 연결을 생성해야 한다는 것을 기억하십시오. 터미널에서 또는 SPIT을 통해 임포트할 수 있습니다. 파일을 다음 데이터베이스 테이블에 임포트하십시오.
points.shp 를 building 테이블로
lines.shp 파일을 road 테이블로
polygons.shp 파일을 region 테이블로
평상시대로 Add PostGIS Layers 대화 창을 통해 이 세 데이터베이스 레이어를 QGIS로 불러오십시오. 이 레이어들의 속성 테이블을 열면, id 필드와 함께 PostGIS 임포트 과정에서 생성된 gid 필드를 둘 다 가지고 있다는 사실을 알 수 있습니다.
이제 테이블을 임포트했으니, PostGIS를 사용해서 데이터를 쿼리할 수 있습니다. 다시 터미널(커맨드 입력 창)으로 가서 다음 명령을 실행해서 psql 프롬프트로 들어가십시오.
psql postgis_demo
다음 선택된 선언문들을 시연해서 뷰를 생성하겠습니다. 이렇게 하면 QGIS에서 뷰를 열어 결과물을 볼 수 있습니다.
16.4.4.1. 위치에 따른 선택¶
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에 뷰를 레이어로 추가해서 볼 수 있습니다.
16.4.4.2. 인접 검색¶
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 TOUCHES(a.the_geom, b.the_geom)
AND a.name = 'Hokkaido';
QGIS에서 뷰를 봅니다.
오류 지역(Queensland)이 보이십니까? 위상 오류 때문일지도 모릅니다. 이런 오류는 데이터 안에 어떤 잠재적인 문제가 있다고 경고해주는 것일 수 있습니다. 데이터에 있을지도 모르는 이상을 피해 이 수수께끼를 풀려면, 대신 버퍼 인터섹트를 이용할 수 있습니다.
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';
이렇게 하면 임시적인 버퍼 단계 없이도 동일한 결과를 달성할 수 있습니다.
16.4.4.3. 유일 값 선택¶
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)
16.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;
16.4.5. In Conclusion¶
PostGIS의 새로운 데이터베이스 기능을 사용해 공간 오브젝트를 쿼리하는 방법을 배웠습니다.
16.4.6. What’s Next?¶
다음으로 좀 더 복잡한 도형의 구조 및 PostGIS를 써서 생성하는 방법을 알아보겠습니다.