[PostGRES] 최단거리 기준으로 공간 변수 붙이기

가장 큰 관건이었던 데이터의 재생성 작업을 마쳤다.

이제 남은 건 분석만 하면 된다.

늘 그렇듯, 요리에 비유하면 재료 준비 과정이 가장 오래 걸린다. 이제 본격적으로 요리를 해볼 차례.

 

내가 붙여야 할 공간 변수들은

도로 위계, 도로 등급, 도로 너비, 도로까지의 거리 (도로데이터)

아파트까지 거리 (아파트 데이터)

CBD까지의 거리 (CBD)

지하철역까지의 거리 (지하철역)

층수, 지하층수, 총연면적, 건물 용도, 건물 연식 (빌딩데이터를 juso 에다가 nsdi 건물까지)

토지용도 (토지용도 데이터)

 

등이다.

 

일단, dtp_data_2024_buld 에 geometry 를 추가해준다.

ALTER TABLE dtp_data_2024_buld ADD COLUMN geom GEOMETRY;

UPDATE dtp_data_2024_buld
SET geom = buld.geom
FROM "JUSO_BULDINFO" buld
WHERE dtp_data_2024_buld.buld_id = buld.bul_man_no||'_'||buld.sig_cd;

아무리 해도 적응되지 않는 것은, 불필요한 테이블과 column이 너무 많아진다는 것.

백엔드 개발자들과 빅데이터 분석가들은 도대체 어떻게 하는 걸까?

 

도로데이터부터 시작해보자.

---------------------------------도로데이터 접합은 6시간 45분이 걸렸다.
CREATE MATERIALIZED VIEW temp_dtp_addroad AS
(SELECT nearest_ROAD.*
FROM dtp_data_2024_buld dd
LEFT JOIN LATERAL(
	SELECT dd.*, ROAD.geom road_geom, ROAD.ROA_CLS_SE, ROAD.RDS_DPN_SE, ROAD.ROAD_BT
	FROM "JUSO_ROADINFO" ROAD
	ORDER BY dd.geom <-> ROAD.geom --KNN Operator
	LIMIT 1
	) nearest_ROAD ON True);

--------------------------------아파트 데이터 접합은 3시간 45분이 걸렸다.
CREATE MATERIALIZED VIEW temp_dtp_addapt AS
(SELECT nearest_ROAD.*
FROM (SELECT  * FROM temp_dtp_addroad) thirdP_rest
LEFT JOIN LATERAL(
	SELECT thirdP_rest.*, apt.geom apt_geom
	FROM "JUSO_APTINFO" apt
	ORDER BY thirdP_rest.geom <-> apt.geom --KNN Operator
	LIMIT 1
	) nearest_ROAD ON True);
    
--------------------------------CBD 데이터 접합은 6초가 걸렸다.
CREATE MATERIALIZED VIEW temp_dtp_addcbd AS
(SELECT nearest_ROAD.*
FROM (SELECT  * FROM temp_dtp_addapt) thirdP_rest
LEFT JOIN LATERAL(
	SELECT thirdP_rest.*, apt.geom cbd_geom
	FROM "CBD_pt" apt
	ORDER BY thirdP_rest.geom <-> apt.geom --KNN Operator
	LIMIT 1
	) nearest_ROAD ON True);

자꾸만 늘어나는 materialized view도 맘에 들지 않지만,

쿼리 시간이 오래 걸리는 공간 분석의 결과물이라면 별 수 없다.

 

---------------------------dist_to_station 으로 가는 길
UPDATE dtp_data_2024_final
SET dist_tostation = station_geom.dist_tostation
FROM (SELECT dtp_data_2024_final.pmid, ST_Distance(dtp_data_2024_final.geom, temp_dtp_addstation.station_geom) dist_tostation 
	FROM dtp_data_2024_final LEFT JOIN temp_dtp_addstation
	ON temp_dtp_addstation.pmid = dtp_data_2024_final.pmid) station_geom
WHERE station_geom.pmid = dtp_data_2024_final.pmid;


--------------------------land use 로 가는 길
ALTER TABLE dtp_data_2024_final ADD COLUMN mlsfc_cl VARCHAR(10);

UPDATE dtp_data_2024_final
SET mlsfc_cl = add_lu.mlsfc_cl
FROM (SELECT dtp_data_2024_final.pmid,  lu.mlsfc_cl
	FROM dtp_data_2024_final LEFT JOIN "LANDUSE" lu
	ON ST_Within(dtp_data_2024_final.geom, lu.geom)) add_lu
WHERE add_lu.pmid = dtp_data_2024_final.pmid;

UPDATE dtp_data_2024_final
SET mlsfc_cl = add_lu.mlsfc_cl
FROM (SELECT dtp_data_2024_final.pmid,  lu.mlsfc_cl
	FROM dtp_data_2024_final LEFT JOIN "LANDUSE" lu
	ON ST_Intersects(dtp_data_2024_final.geom, lu.geom)) add_lu
WHERE dtp_data_2024_final.mlsfc_cl IS NULL AND add_lu.pmid = dtp_data_2024_final.pmid;