안녕하세요? 이번 글은 PostGIS에서 Spatial Joins(공간 조인) 기능을 실습해 보도록 하겠습니다. Spatial Joins는 공간정보를 가지고 있는 두 개 이상의 테이블을 조인할 때 사용되는 기능입니다. 테이블 간에 공통된 키 값을 가지고 있지는 않지만 위치정보를 기준으로 테이블을 조인하고자 할 때 Spatial Joins를 사용할 수 있습니다.
실습을 위해서는 행정경계(읍면동) 테이블인 umd_kr와 박물관, 미술관 정보 테이블은 museum_kr을 다뤄보겠습니다. 앞서 저는 gongju라는 이름의 공간 데이터베이스를 생성하고 PostGIS Shapefile Import/Export Manager를 통해 해당 데이터들을 gongju db에 테이블로 추가했습니다. 또한 umd_kr에서 공주시에 해당하는 행정경계만 추출하여 umd_gongju라는 테이블을 별도 생성한 상태입니다.
자, 먼저 umd_gongju 테이블을 살펴보겠습니다. umd_gongju 테이블의 지오메트리 유형을 확인해 보겠습니다. umd_gongju 테이블의 지오메트리 유형은 ST_MultiPolygon임을 확인합니다.
-- umd_gongju 테이블 첫번째 행의 지오메트리 유형 확인
SELECT ST_GeometryType(geom) FROM umd_gongju LIMIT 1;
umd_gongju 테이블 데이터를 확인하기 위해 첫번째 행만 선택해 보겠습니다. name 필드에 읍면동 이름이 저장된 것을 확인할 수 있습니다.
-- umd_gongju 테이블의 모든 열과 첫번째 행 선택
SELECT * from umd_gongju LIMIT 1;
umd_gongju 테이블의 읍면동 이름과 지오메트리 열을 선택해 보겠습니다. 이때 geom의 좌표계 ID는 4326으로 변경해 주겠습니다. 이는 Geometry Viewer에서 OpenStreetMap과 도형을 중첩해 보기 위함입니다.
-- umd_gongju 테이블의 name, geom 열과 모든 행 선택
SELECT name, ST_Transform(geom, 4326) FROM umd_gongju;
umd_gongju 테이블의 좌표계 ID는 5179입니다.
-- umd_gongju 테이블에서 첫번째 행의 SRID 확인
SELECT ST_SRID(geom) FROM umd_gongju LIMIT 1;
다음으로 museum_kr 테이블의 지오메트리 유형을 확인해 보겠습니다. museum_kr 테이블의 지오메트리 유형은 ST_Point임을 확인합니다.
-- museum_kr 테이블 첫번째 행의 지오메트리 유형 확인
SELECT ST_GeometryType(geom) FROM museum_kr LIMIT 1;
museum_kr 테이블 데이터를 확인하기 위해 첫번째 행만 선택해 보겠습니다. 사업장명 필드에 박물관, 미술관 이름이 저장된 것을 확인할 수 있습니다.
-- museum_kr 테이블의 모든 열과 첫번째 행 선택
SELECT * from museum_kr LIMIT 1;
데이터 테이블에서 필드명은 영문을 사용하기 때문에 사업장명 필드명을 name으로 변경해 보겠습니다.
-- museum_kr 테이블의 사업장명 필드명을 name으로 변경
ALTER TABLE museum_kr RENAME COLUMN 사업장명 TO name;다시 테이블을 읽어보면 변경된 필드명을 확인할 수 있습니다.
-- museum_kr 테이블의 모든 열과 첫번째 행 선택
SELECT * from museum_kr LIMIT 1;
museum_kr 테이블의 박물관, 미술관 이름과 지오메트리 열을 선택해 보겠습니다.
-- museum_kr 테이블의 name, geom 열과 모든 행 선택
SELECT name, ST_Transform(geom, 4326) FROM museum_kr;
museum_kr 테이블의 좌표계 ID는 5186입니다.
-- museum_kr 테이블에서 첫번째 행의 SRID 확인
SELECT ST_SRID(geom) FROM museum_kr LIMIT 1;
자, 공주시 행정경계(읍면동)과 박물관을 공간 조인해 보겠습니다. 공주시 행정경계가 포함하는 박물관을 공간 조인해서 반환하는 쿼리입니다. 이때 행정경계의 좌표계 ID는 5179, 박물관의 좌표계 ID는 5186이기 때문에 어느 한 쪽의 좌표계 ID를 변경해 주도록 합니다. 공간 조인 결과는 행정경계 이름으로 오름차순 정렬했습니다.
-- 공주시 행정경계와 박물관 공간 조인
SELECT u.name, m.name
FROM umd_gongju AS u
JOIN museum_kr AS m
ON ST_Contains(u.geom, ST_Transform(m.geom, 5179))
ORDER BY u.name ASC;
공주시 읍면동별 박물관 개수를 산출해 보겠습니다. 결과는 다음과 같은데요, 반포면은 박물관 개수가 3개로 계산되었지만 앞서 결과를 봤을 때 동산박물관이 3회 중복된 것으로 판단됩니다. 중복성을 제거하여 쿼리를 다시 실행해 보겠습니다.
-- 공주시 읍면동별 박물관 개수
SELECT u.name, COUNT(m.name) AS cnt
FROM umd_gongju AS u
JOIN museum_kr AS m
ON ST_Contains(u.geom, ST_Transform(m.geom, 5179))
GROUP BY u.name
ORDER BY cnt DESC;
박물관 이름의 중복성을 제거하여 공주시 읍면동별 박물관 개수를 재산정한 결과입니다.
-- 공주시 읍면동별 박물관 개수
SELECT u.name, COUNT(DISTINCT m.name) AS cnt
FROM umd_gongju AS u
JOIN museum_kr AS m
ON ST_Contains(u.geom, ST_Transform(m.geom, 5179))
GROUP BY u.name
ORDER BY cnt DESC;
만약 특정 박물관이 어느 행정구역에 속하는지 검색하고자 한다면 어떻게 해야 할까요?! 예를 들어 '충청남도 역사박물관'을 대상으로 공간 조인 기능을 활용해 보겠습니다.
-- museum_kr에서 name이 충청남도 역사박물관인 행 선택
SELECT name, ST_Transform(geom, 4326) from museum_kr
WHERE name = '충청남도 역사박물관';
공간 조인을 통해 충청남도 역사박물관의 행정구역을 다음과 같이 반환합니다.
-- 충청남도 역사박물관의 행정구역 조인
SELECT m.name, u.name
FROM umd_gongju AS u
JOIN museum_kr AS m
ON ST_Contains(
u.geom,
ST_Transform(m.geom, 5179)
)
WHERE m.name = '충청남도 역사박물관';
이번에는 전국 읍면동에 대해 공간 조인을 적용해 보겠습니다. 전국에서 박물관이 가장 많은 읍면동은 어디일까요?! 공간 조인을 통해 쉽게 알 수 있습니다. 정답은 안덕면, 북면, 조천읍으로 총8개의 박물관이 위치하고 있습니다. 참고로 데이터 중복성은 박물관 테이블의 지오메트리를 기준으로 제거했습니다.
-- 박물관이 가장 많은 읍면동 검색
SELECT u.name, COUNT(DISTINCT m.geom) AS cnt
FROM umd_kr AS u
JOIN museum_kr AS m
ON ST_Contains(u.geom, ST_Transform(m.geom, 5179))
GROUP BY u.name
ORDER BY cnt DESC
LIMIT 5;
안덕면의 박물관 목록을 확인해볼까요?! 다음과 같이 공간 조인 기능을 활용합니다. 여기까지 PostGIS의 공간 조인 기능을 실습해 봤습니다. 이와 같이 공간정보를 가지고 있는 테이블들을 위치정보 기준으로 조인할 때 다양한 방식으로 활용할 수 있겠습니다.
-- 안덕면의 박물관 목록
SELECT DISTINCT ON (m.geom)
m.name, ST_Transform(m.geom, 4326)
FROM umd_kr AS u
JOIN museum_kr AS m
ON ST_Contains(u.geom, ST_Transform(m.geom, 5179))
WHERE u.name = '안덕면';
