[PostGRES] 서로 다른 소스의 두 건물 데이터, 특히 날짜를 병합

내가 지금까지 활용한 데이터는 juso.go.kr 에서 제공하는 건축물 정보 데이터.

geometry 가 가장 깔끔하고, 건축물 용도도 나오고, 가장 정리가 잘 되어있는 데이터다.

 

하지만 문제는, 준공연도, 건폐율, 용적률 등에 대한 정보 등 상세한 정보가 없다는 게 흠.

매일경제, 노후건축물 이미지, 국토부, 소규모 노후 건축물 실태조사 - 매일경제

그리고, 그런 상세한 데이터는 nsdi 데이터에 있는데,

이녀석은 분명 key, id로 보였던 column 들에 중복이 있다...

이런 잘못되어먹은 DB 같으니라고.

 

생각해보니, 이런 잘못되어먹은 DB의 데이터상의 한계를

내가 극복할 (수 있다면 해야겠지만 한계가 있으니까 여기에 매달리고 있을) 수는 없지 않은가?

 

일단 붙여보자.

우선, nsdi 데이터에서 가장 key (역할을 하는 것 처럼 보이고, juso 건물 데이터에도 있는 column 인) bd_mgt_sn 를 살려보자.

 

----------------------------bd_mgt_sn 을 dtp 데이터에 붙이려면 primary key 로 붙여보자.
----------------------------놀랍게도 1:1로 다 붙었다.
SELECT dtp.*, buld.bd_mgt_sn FROM temp_dtp_addcbd dtp
LEFT JOIN "JUSO_BULDINFO" buld
ON dtp.buld_id = buld.bul_man_no || '_' || buld.sig_cd

 

이제 bd_mgt_sn 으로 사용승인 날짜 등을 붙여보자.

------------------------------------------총 209,508개의 row가 나왔다.
SELECT dtp_juso.*, nsdi.useapr_day FROM
	(SELECT dtp.*, buld.bd_mgt_sn FROM temp_dtp_addcbd dtp
	LEFT JOIN "JUSO_BULDINFO" buld
	ON dtp.buld_id = buld.bul_man_no || '_' || buld.sig_cd) dtp_juso
LEFT JOIN "NSDI_BUILDING_INFO" nsdi
ON dtp_juso.bd_mgt_sn = nsdi.bd_mgt_sn

결과물이 약 10000개 더 많은데, 중복된 건물들이 제법 있는 것 같다.

 

안붙은 애들이나, 여러개 붙은 애들이 누구누군지 찾아보자.

------------------총 6,133개의 건물 데이터가 여러 개가 붙어있었고,
------------------최대 8개가 붙어있는 애들이 10개 정도. 대부분은 2개중 하나다.
SELECT count_id.pmid, count(count_id.id) FROM
	(SELECT dtp_juso.*, nsdi.useapr_day FROM
		(SELECT dtp.*, buld.bd_mgt_sn, buld.id FROM temp_dtp_addcbd dtp
		LEFT JOIN "JUSO_BULDINFO" buld
		ON dtp.buld_id = buld.bul_man_no || '_' || buld.sig_cd) dtp_juso
	LEFT JOIN "NSDI_BUILDING_INFO" nsdi
	ON dtp_juso.bd_mgt_sn = nsdi.bd_mgt_sn) count_id
GROUP BY pmid
HAVING count(count_id.id) != 1
ORDER BY count(count_id.id) DESC


----------------날짜가 여러개로 묶인 애들은 3,571개로,
----------------최대 7개가 붙어 있는 애가 1명이고, 나머지는 다 큰 차이가 없어보인다.
SELECT count_id.pmid, count(count_id.useapr_day) FROM
	(SELECT dtp_juso.*, nsdi.useapr_day FROM
		(SELECT dtp.*, buld.bd_mgt_sn, buld.id FROM temp_dtp_addcbd dtp
		LEFT JOIN "JUSO_BULDINFO" buld
		ON dtp.buld_id = buld.bul_man_no || '_' || buld.sig_cd) dtp_juso
	LEFT JOIN "NSDI_BUILDING_INFO" nsdi
	ON dtp_juso.bd_mgt_sn = nsdi.bd_mgt_sn) count_id
GROUP BY pmid
HAVING count(count_id.id) != 1
ORDER BY count(count_id.useapr_day) DESC

막상 찾아보니 다 똑같다 이 놈들...

 

내가 봤을 때, 이유는 모르겠지만 nsdi 빌딩 데이터는 중복이 겁나게 많고 (아마 일자별로 업데이트를 하면서 중복이 발생했건),

 

그러니 안전하게 distinct 로 붙여줘도 될 것 같다는 나의 생각.

사실 전체 데이터 상으로 차지하는 포션도 높지 않아서, 

distinct로 붙이는 거로 진행하기로 한다.

 

-------------깔끔하게 199651개가 나오고,
-------------useapr_day 가 null인 애들은 44,490개... 이런 젠장...
-------------다행히 인허가 사업체의 open date 까지 합쳤을 때 에는 0...
-------------고로 15.5만개 정도는 건축물의 연령으로, 4.5만개는 인허가 날짜로 대체한다는 뜻...
-------------하지만 그냥 간다. 왜냐하면 데이터가 없잖아.
-------------이전 논문에서도 이렇게 대체했었거든.


SELECT distinct on (pmid) dtp_juso.*, nsdi.useapr_day FROM
		(SELECT dtp.*, buld.bd_mgt_sn FROM temp_dtp_addcbd dtp
		LEFT JOIN "JUSO_BULDINFO" buld
		ON dtp.buld_id = buld.bul_man_no || '_' || buld.sig_cd) dtp_juso
	LEFT JOIN "NSDI_BUILDING_INFO" nsdi
	ON dtp_juso.bd_mgt_sn = nsdi.bd_mgt_sn
	WHERE nsdi.useapr_day IS NULL;

 

 

이제 새로운 테이블을 만들고, 날짜 데이터를 처리해보자.

-----------------건축물 사용승인 날짜를 붙이고,
CREATE TABLE dtp_data_2024_bulddate AS
(SELECT distinct on (pmid) dtp_juso.*, nsdi.useapr_day FROM
		(SELECT dtp.*, buld.bd_mgt_sn FROM dtp_data_2024_buld dtp
		LEFT JOIN "JUSO_BULDINFO" buld
		ON dtp.buld_id = buld.bul_man_no || '_' || buld.sig_cd) dtp_juso
	LEFT JOIN "NSDI_BUILDING_INFO" nsdi
	ON dtp_juso.bd_mgt_sn = nsdi.bd_mgt_sn);

-----------------일단 날짜의 원 상태를 저장해둘 column을 만들고
ALTER TABLE dtp_data_2024_bulddate ADD COLUMN buld_date VARCHAR(15);

-----------------사용승인날짜가 붙은 애들을 우선으로 쓰고
UPDATE dtp_data_2024_bulddate
SET buld_date = useapr_day;

-----------------안 붙은 애들은 고시일자로 만들어준다.
UPDATE dtp_data_2024_bulddate
SET buld_date = ntfc_de
WHERE buld_date IS NULL;

----------------date 포맷의 column 을 만들고
ALTER TABLE dtp_data_2024_bulddate ADD COLUMN buld_date_d DATE;

----------------한방에 날짜 변환 시켜보지만
UPDATE dtp_data_2024_bulddate
SET buld_date_d = TO_DATE(buld_date, 'YYYYMMDD');

인생에 쉬운 일 하나도 없죠~~??

그거 아니? 난 이럴 줄 알고 이전에 오류 케이스들을 다 저장해뒀단다.

UPDATE dtp_data_2024_bulddate
SET buld_date =
	CASE
		WHEN buld_date LIKE '840416' THEN '19840416'
		WHEN buld_date LIKE '09780927' THEN '19780927'
		WHEN buld_date LIKE '11111111' THEN '20230731'
		WHEN buld_date LIKE '20025401' THEN '20020504'
		WHEN buld_date LIKE '19700230' THEN '19700228'
		WHEN buld_date LIKE '19710931' THEN '19710930'
		WHEN buld_date LIKE '19360230' THEN '19360228'
		WHEN buld_date LIKE '84041601' THEN '20230714'
		WHEN buld_date LIKE '19871610' THEN '19871010'
		WHEN buld_date LIKE '19780001' THEN '19780101'
		WHEN buld_date LIKE '19850001' THEN '19850101'
		WHEN buld_date LIKE '19600230' THEN '19600228'
		WHEN buld_date LIKE '19350230' THEN '19350228'
		WHEN buld_date LIKE '19710230' THEN '19710228'
		WHEN buld_date LIKE '2002' THEN '19350228'
		WHEN length(buld_date) = 4 THEN buld_date || '0101'
		WHEN buld_date LIKE '840416' THEN '19840416'
		WHEN length(buld_date) = 6 THEN buld_date || '01'
		WHEN length(buld_date) = 7 THEN '20220629'
		WHEN length(buld_date) = 14 THEN LEFT(buld_date, 8)
	ELSE buld_date
	END;

UPDATE dtp_data_2024_bulddate
SET buld_date_d = TO_DATE(buld_date, 'YYYYMMDD');

 

그런데 왜 돌릴 때마다 case when 이 먹을 때가 있고 안 먹을 때가 있지?

데이터베이스의 pointer 구조 상 그런 건가?

 

진짜 개열받는다. 하지만 일단 마쳤으니 아름다운 마무리라고 하자.

 

---------------------건축물의 나이를 세어주기 위해 INT column을 추가하고
ALTER TABLE dtp_data_2024_bulddate ADD COLUMN buld_age INT;

---------------------오늘 날짜를 기록해주는 CURRENT_DATE 함수랑 건축물 생일을 빼줘서 일자를 세준다
UPDATE dtp_data_2024_bulddate SET buld_age = CURRENT_DATE - buld_date_d;


---------------------이제 필요한 raw 데이터는 모두 구축했다.
CREATE TABLE dtp_data_2024_final AS
(SELECT dtp_raw.*, date_t.buld_age
FROM temp_dtp_addcbd dtp_raw
LEFT JOIN dtp_data_2024_bulddate date_t
ON dtp_raw.pmid = date_t.pmid)

 

그리고 두 geometry 사이의 거리를 column으로 추가해보자

ALTER TABLE dtp_data_2024_final ADD COLUMN dist_tord NUMERIC;
ALTER TABLE dtp_data_2024_final ADD COLUMN dist_toapt NUMERIC;
ALTER TABLE dtp_data_2024_final ADD COLUMN dist_tocbd NUMERIC;

UPDATE dtp_data_2024_final SET
dist_tord = ST_Distance(geom, road_geom);

UPDATE dtp_data_2024_final SET
dist_toapt = ST_Distance(geom, apt_geom);

UPDATE dtp_data_2024_final SET
dist_tocbd = ST_Distance(geom, cbd_geom);

 

이제 남은 작업은 무엇이냐?

파이썬으로 넘어가는 거다...

오늘 안에 공간분석을 마쳐보자.