[PostGRES] 도로에 인접한 건축물을 용도 별로 할당하기
Task:
특정 도로가 어떠한 공간적 속성을 가지고 있는지 구분할 때, 인접한 건축물의 용도가 중요하다.
도로에 인접한 건축물들을 용도별로 구분하여 Join 시키자.
오후 2시에 북촌 어느 골목에는 몇 명이 지나갈까.
이 물음에 답하려면, "그 골목은 어디에 있습니까?" 와 같이,
"그 골목은 뭐하는 골목입니까?" 에 대한 질문도 답해야 한다.
뭐하는 골목인지 아는 데에 가장 좋은 방법은 어떤 건물이 주변에 있습니까? 와도 같은 질문일 터.
1. Data Preprocessing
필요한 준비물은 2개. 도로 중심선 데이터와 건축물 데이터다.
경험 상, 도로 중심선 데이터와 건축물 데이터 모두 juso.go.kr 에서 제공하는 주소를 받는 것이 가장 정밀하다.
도로데이터는 연구하고자 하는 범위에 맞춰서 지혜롭게 선택 또는 자르면 된다.
원하는 범위 만큼의 도로를 잘라내는 데에는 QGIS 의 CLIP 기능이 가장 효율적이다.
주소정보누리집에서 제공하는 건축물 데이터는 아래와 같은 정보를 가진다.
연면적, 용적률, 건폐율 등이 없는 건 아쉽지만, 그나마 가장 정확한 좌표와 오류 없는 geometry 를 제공하고,
무엇보다 건축물 용도 구분이 그나마 가장 정확하다.
우리는 저 많은 항목들 중 건물용도코드, BDTYP_CD 를 중점적으로 다룰 거다.
코드 정의서 상으로는 아래와 같이 상세한 구분을 제공한다.
소분류 항목이 100개가 넘어가다보니 하나하나 구분하자면 머리가 아프지만,
눈치 빠른 사람들은 앞의 2개 숫자가 (01xxx, 02xxx...) 대분류 코드를 나타냄을 어렵지 않게 눈치챌 수 있을 것이다.
대분류는 아래와 같다.
| 대분류코드 | 건축물용도 및 예시 | 대분류코드 | 건축물용도 및 예시 | 대분류코드 | 건축물용도 및 예시 |
| 01 (#FFDF0D) |
단독주택 (단독 다중, 다가구 주택 등) |
09 (#00fabb) |
운동시설 (체육관, 테니스장, 실내낚시터 등) |
17 (#d2d2d2) |
동식물관련시설 (도축장, 버섯재배사, 온실, 축사 등) |
| 02 (#FFC30D) |
공동주택 (아파트, 연립, 다세대주택, 부대시설 등) |
10 (#0145ff) |
업무시설 (국가기관, 금융업소, 오피스텔 등) |
18 (#d2d2d2) |
분뇨, 쓰레기처리시설 (폐기물 처리 시설, 고물상 등) |
| 03 (#FF9E01) |
제1종근린생활시설 (소매점, 휴게음식점, 파출소, 소방서 등) |
11 (#cf575f) |
숙박시설 (호텔, 여관 등) |
19 (#d2d2d2) |
공공용시설 (방송국, 촬영소 등) |
| 04 (#FF0101) |
제2종근린생활시설 (일반음식점, 독서실, 볼링장, 교회, 금융업소 등) |
12 (#ee247c) |
위락시설 (단란주점, 무도장, 카지노 등) |
20 (#d2d2d2) |
묘지관련시설 (화장당, 납골당 등) |
| 05 (#FF19DC) |
문화 및 집회시설 (교회, 기도원, 박물관, 공연장, 예식장 등) |
13 (#f201ff) |
공장 (일반공장, 공해공장) |
21 (#56f318) |
관광휴게시설 (야외음악당, 휴게소 등) |
| 06 (#FF0101) |
판매 및 영업시설 (시장, 백화점, 상점, 철도, 항만 등) |
14 (#f201ff) |
창고시설 (창고, 하역장 등) |
27 (#d2d2d2) |
발전시설 |
| 07 (#3e89c7) |
의료시설 (종합병원, 치과병원, 장례식장 등) |
15 (#c03ebc) |
위험물저장처리시설 (주유소, 액화가스충전소 등) |
90 (#d2d2d2) |
거리가게 (가로판매대, 구두수선대 등) |
| 08 (#03F7FF) |
교육연구 및 복지시설 (학교, 학원, 어린이집, 도서관, 야영장 등) |
16 (#d2d2d2) |
자동차관련시설 (주차장, 세차장, 운전학원, 차고 등) |
주의해야할 점은
- 제1종근린생활시설: 주민들의 생활 편의를 도울 수 있는, 주민생활의 필수 시설
- 제2종근린생활시설: 1종보다 큰 규모의 시설로, 취미 및 편의생활시설.
- 두 근린생활시설은 규모의 차이가 있어 인허가 상에서 면적 등에 있어서 차이가 발생하고, 제1종은 주민 편의가 목적, 제2종은 그것보다 큰 규모, 어느 정도의 사업성이 추가됨.
색상 코드는, 안타깝게도 공식적으로 건축물 용도별 색상 코드를 제공한 공식 자료를 얻지 못해
토지이용 분류 기준을 참고하여
나의 주관대로 - 주거는 노랑, 상업은 빨강, 공업은 보라, 업무는 파랑, 녹지는 초록, 기타는 회색 등.
만들었다.
주의해야할 점은, 제2종근린생활시설에 포함되더라도 업무용 빌딩인 경우가 있고, 같은 종교시설이라도 제1종 또는 2종 근린생활로 구분되는 경우가 있기 때문에, 목적에 따라 소분류까지 세세히 확인해야 할 필요가 있다는 것.
그런 경우에는 (오로지 나의 주관적인 편의에 맞춰서) 새로운 색상 코드를 포함하였다.
첨부한 qml 파일을 활용하길 바란다.
색상 코드에 따른 건축물은 위와 같은 결과물을 준다.
2. NEAREST JOIN
최근접 건축물끼리 붙이는 작업은 SQL을 활용할 것이다.
물론 QGIS의 다양한 기능들을 통해서도 충분히 할 수 있지만,
이번 경우에는 한 도로당 JOIN 되는 건축물이 매우 많아지기 때문에
QGIS 상으로 확인하기에는 너무 비효율적이고, 레이어도 너무 커지고, 무엇보다 '느리다'.
우선 SQL 로 도로 및 빌딩 객체를 넘겨주고, 최근접 건축물을 중심으로 JOIN 을 해보자.
목표는, '건물에서 가장 가까운 도로' 를 찾아, 그 도로에 JOIN 시키는 것이 목표.
예를 들어 "도로에서 가장 가까운 건물 찾기" 라고 하면, 도로 하나당 가장 가까운 건물 하나만 나오므로 우리의 목표에 부적합.
하지만 "건물에서 가장 가까운 도로" 를 찾고, 도로마다 할당된 건물들을 차근차근 살펴보는게 더 적합하다.
SELECT buld.id buld_id, street_id --우리는 건축물마다 가장 가까운 도로의 id 를 찾는다.
FROM "Temp".bukchon_buld buld
LEFT JOIN LATERAL (
SELECT street.id street_id
FROM "Temp".buk_street street
ORDER BY street.geom <-> buld.geom -- 도로와 건물의 거리가 가까운 순서대로 나열한다.
LIMIT 1 -- 그 중 가장 가까운 1개만 찾겠다는 의미이다.
) nearest_t2 ON true;
그런데 만약
이런 녀석들이 걸리면 곤란하다. 거리가 저렇게나 떨어져 있는데, 정독도서관이 저 도로의 성질을 설명한다고 보기에 어려우므로. 그래서 우리는 '거리 필터' 를 어느 정도 걸어줘야 할 필요가 있다.
예를 들어, '건축물에서 가장 가까운 도로를 할당하긴 하는데, 20m 넘어가는거면 말고.' 를 구현해보자.
SELECT buld.id buld_id, street_id, ST_Distance(buld.geom, street_geom) distance --ST_Distance 는 두 geometry 사이의 거리를 보여준다.
FROM "Temp".bukchon_buld buld
LEFT JOIN LATERAL (
SELECT street.id street_id, geom street_geom
FROM "Temp".buk_street street
ORDER BY street.geom <-> buld.geom -- KNN operator
LIMIT 1
) nearest_t2 ON true and ST_Distance(buld.geom, street_geom) < 20 --거리가 20m 이하인 녀석들만 붙여보자.;
이제 남은 일은, street_id 를 바탕으로 buld_id 에 해당하는 건축물의 정보들을 다시 붙여주는 일.
따로 table 을 만드는 건 귀찮으니 WITH 문으로 한방에 붙여보자.
CREATE TABLE "Temp".buk_street_buld AS --이제는 새로운 Table을 만들 때가 됐다.
---------------건물과 도로를 최근접거리 기준으로, 20m 내부에 있을 경우 기준으로 매칭
(WITH
nearest_street AS
(SELECT buld.id buld_id, street_id, ST_Distance(buld.geom, street_geom) distance
FROM "Temp".bukchon_buld buld
LEFT JOIN LATERAL (
SELECT street.id street_id, geom street_geom
FROM "Temp".buk_street street
ORDER BY street.geom <-> buld.geom -- KNN operator
LIMIT 1
) nearest_t2 ON true and ST_Distance(buld.geom, street_geom) < 20)
-------Main Query: 매칭된 최근접 건축물 id 를 기준으로 속성을 매칭
SELECT * FROM
-----------------Subquery: 최근접 매칭 결과물의 도로 id 를 기준으로 원래 도로에 최근접 건물을 매칭
(SELECT id origin_st_id, geom, nearest_match.buld_id FROM "Temp".buk_street origin_street
LEFT JOIN (SELECT buld_id, street_id FROM nearest_street) nearest_match
ON origin_street.id = nearest_match.street_id) street_buld_match
-----------------Subquery: 원래 건축물 데이터 중 건물 id, 용도, 층수를 가져옴
LEFT JOIN (SELECT id origin_bd_id, geom buld_geom, "BDTYP_CD" bdtyp_cd, "GRO_FLO_CO" gro_flo_co
FROM "Temp".bukchon_buld) origin_buld
ON street_buld_match.buld_id = origin_buld.origin_bd_id);
예를 들어, 결과물을 단순히 붙어있는 건축물의 갯수 기준으로 시각화 한다면
위와 같은 결과가 나온다.
이것은 받아들일만 한가?
같은 색상을 가진 건축물끼리는 같은 도로에 할당되었다.
최근접 건축물들끼리 이쁘게 같은 도로로 할당된 것은 흡족하지만,
하나의 건축물에 하나의 도로만 할당하다니, 양쪽에 건물이 다 둘러싸고 있는 도로는 그 특성을 충분히 잡아내지 못한다는 아쉬운 점이 있다.
이걸 잡기 위한 알고리즘과 가정을 덧붙일지, 만족할지는 연구자의 판단이다.
나는 만족하기로 판단했다.
3. Building use analysis
건축물 용도에 따른 분석을 진행할 차례.
우선 차근차근, 각 도로에 붙어있는 주거용 건축물의 갯수와 총 연면적을 먼저 구해보자.
SELECT origin_st_id id, geom,
count(origin_bd_id) r_count, --건축물 id의 갯수를 세준다
sum(ST_area(buld_geom)*gro_flo_co) r_tfa -- 건축물 geometry 의 면적에 층수를 곱해서 총 연면적을 구하고 싹 더한다
FROM "Temp".buk_street_buld
WHERE bdtyp_cd LIKE '01%' OR bdtyp_cd LIKE '02%' -- 주거용 건축물만 모으는 게 목적이므로, 대분류코드가 01로 시작하거나 02로 시작하는 건축물만 고른다.
GROUP BY id, geom;
이상하다. 분명 도로1515 에는 주거용 건축물이 2개가 할당되었는데, 연면적 합은 0이다...
일단 건축물 바닥 면적은 geometry 만 있으면 구해지는 것이므로, 층수에 0으로 입력되어있는 데이터가 있는 모양이다.
층수가 0으로 기록되어있는 못된 데이터가 무엇인지 살펴보자.
지금 일단 많은 건 둘째치고, 대표적인 주요 건축물인 현대건설 사무실, 정독도서관, 학교 등 주요 POI와 건축물들 층수가 모두 0으로 기록되어있다! juso.go.kr 의 데이터가 가장 낫다는 확신을 취소한다.
공공데이터를 활용한 공간 분석의 연구 의욕이 바닥을 치는 순간이다...
방법이 없다... 그나마 오차를 줄이기 위해서는 아래와 같은 근사와 가정이 필요할 수 밖에 없다...
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 10
WHERE gro_flo_co = 0 and bdtyp_cd LIKE '044%'; -- 현대건설 사옥
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 5
WHERE gro_flo_co = 0 and bdtyp_cd LIKE '10101'; -- 헌법재판소, 감사원 및 공관
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 1
WHERE gro_flo_co = 0 and bdtyp_cd LIKE '10299'; -- 북촌문화센터
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 1
WHERE gro_flo_co = 0 and bdtyp_cd LIKE '01%'; -- 단독주택
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 1
WHERE gro_flo_co = 0 and bdtyp_cd LIKE '02%'; -- 다세대주택 등
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 1
WHERE gro_flo_co = 0 and bdtyp_cd LIKE '03%'; -- 근생시설
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 3
WHERE gro_flo_co = 0 and bdtyp_cd LIKE '08%'; -- 교육시설
UPDATE "Temp".buk_street_buld
SET gro_flo_co = 1
WHERE gro_flo_co = 0; -- 기타 등등...
일단 눈물을 머금고 코드를 마저 작성해보자....
CREATE TABLE "Temp".buk_street_buld_group AS
(WITH
street_r AS
(SELECT origin_st_id id, geom, count(origin_bd_id) r_count, sum(ST_area(buld_geom)*gro_flo_co) r_tfa
FROM "Temp".buk_street_buld
WHERE bdtyp_cd LIKE '01%' OR bdtyp_cd LIKE '02%' OR bdtyp_cd LIKE '03%' --제1종근생시설을 포함한 주거시설
GROUP BY id, geom),
street_c AS
(SELECT origin_st_id id, geom, count(origin_bd_id) c_count, sum(ST_area(buld_geom)*gro_flo_co) c_tfa
FROM "Temp".buk_street_buld
WHERE bdtyp_cd LIKE '040%' OR bdtyp_cd LIKE '055%' OR bdtyp_cd LIKE '06%' --제2종근생 중 음식점과 카페, 집회시설 중 박물관과 미술관, 상업시설
GROUP BY id, geom),
street_w AS
(SELECT origin_st_id id, geom, count(origin_bd_id) w_count, sum(ST_area(buld_geom)*gro_flo_co) w_tfa
FROM "Temp".buk_street_buld
WHERE bdtyp_cd LIKE '044%' OR bdtyp_cd LIKE '10%' --제2종근생 중 금융업소 및 사무소, 업무시설
GROUP BY id, geom),
street_n AS
(SELECT origin_st_id id, geom, count(origin_bd_id) n_count, sum(ST_area(buld_geom)*gro_flo_co) n_tfa
FROM "Temp".buk_street_buld
WHERE bdtyp_cd LIKE '08%' OR bdtyp_cd LIKE '09%' --교육시설 및 운동시설
GROUP BY id, geom)
SELECT street.origin_st_id id, street.geom geom, r_count, r_tfa, c_count, c_tfa, w_count, w_tfa, n_count, n_tfa
FROM (SELECT distinct on (origin_st_id, geom) origin_st_id, geom FROM "Temp".buk_street_buld) street --기존 도로 데이터 중 id 와 geometry 만 가져옴
LEFT JOIN street_r ON street.origin_st_id = street_r.id
LEFT JOIN street_c ON street.origin_st_id = street_c.id
LEFT JOIN street_w ON street.origin_st_id = street_w.id
LEFT JOIN street_n ON street.origin_st_id = street_n.id)
그리하야 최근접 건축물들 중 주거용 건축물의 연면적이 높은 도로들,
그러니까 주로 주거 기능을 담당하는 도로들을 색으로 표현하자면 빨간색 도로들이 그러한 경향이 크다는 사실을
간접적으로 알 수 있다.
Task 는 성공하였지만,
데이터의 한계를 극복하는 방법이 아쉬웠다...
++ 건축물 그룹을 다르게 변경하기 위한 예시 코드
DROP TABLE buk_street_buld_group;
CREATE TABLE buk_street_buld_group AS
(WITH
street_r AS
(SELECT origin_st_id id, geom, count(origin_bd_id) r_count, sum(ST_area(buld_geom)*gro_flo_co) r_tfa
FROM buk_street_buld
WHERE bdtyp_cd LIKE '01%' OR bdtyp_cd LIKE '02%' --주거시설
GROUP BY id, geom),
street_c AS
(SELECT origin_st_id id, geom, count(origin_bd_id) c_count, sum(ST_area(buld_geom)*gro_flo_co) c_tfa
FROM buk_street_buld
WHERE bdtyp_cd LIKE '040%' OR bdtyp_cd LIKE '055%' OR bdtyp_cd LIKE '06%' OR bdtyp_cd LIKE '03%'--제1종 근생 및, 2종근생 중 음식점과 카페, 집회시설 중 박물관과 미술관, 상업시설
GROUP BY id, geom),
street_w AS
(SELECT origin_st_id id, geom, count(origin_bd_id) w_count, sum(ST_area(buld_geom)*gro_flo_co) w_tfa
FROM buk_street_buld
WHERE bdtyp_cd LIKE '044%' OR bdtyp_cd LIKE '10%' --제2종근생 중 금융업소 및 사무소, 업무시설
GROUP BY id, geom),
street_f AS
(SELECT origin_st_id id, geom, count(origin_bd_id) f_count, sum(ST_area(buld_geom)*gro_flo_co) f_tfa
FROM buk_street_buld
WHERE bdtyp_cd LIKE '05%' OR bdtyp_cd LIKE '07%' OR bdtyp_cd LIKE '08%' OR bdtyp_cd LIKE '09%' -- 문화시설, 의료시설, 교육시설 및 운동시설
GROUP BY id, geom)
SELECT street.origin_st_id id, street.geom geom, r_count, r_tfa, c_count, c_tfa, w_count, w_tfa, f_count, f_tfa
FROM (SELECT distinct on (origin_st_id, geom) origin_st_id, geom FROM buk_street_buld) street --기존 도로 데이터 중 id 와 geometry 만 가져옴
LEFT JOIN street_r ON street.origin_st_id = street_r.id
LEFT JOIN street_c ON street.origin_st_id = street_c.id
LEFT JOIN street_w ON street.origin_st_id = street_w.id
LEFT JOIN street_f ON street.origin_st_id = street_f.id);