ADTP 찾아내기

하... 내가 왜 ADTP를 붙일 수 있도록 nid 를 계속 추가하는 걸 까먹었을까...

 

문제:

내가 멍청하게 인허가데이터 기준으로 ndtp와 dtp 를 구분해두어서,

네이버 크롤링(naver place) 데이터베이스에 nid 로 지정해둔 adtp 데이터를

다시 인허가 데이터에서 찾는 멍청한 짓을 해야 한다는 사실이다.

 

그럼 ADTP를 추가하는 방법은 2가지가 있다.

1. 이성: 현재 DTP는 인허가 데이터 기준으로 분류가 되어있다. dtp를 구분하면서 했던 진행방식을 그대로 거치며, 인허가 데이터에 존재하는 내에서 adtp를 찾아낸다.

2. 본능: 네이버에 예약 링크 있으면 그게 장사 한다는 뜻 아님? 굳이 인허가 데이터를 왜 찾음?

 

하지만 나는 이성애자다.

 

일단 예약 가능 링크가 있는 음식점들의 pmid를 찾도록

"지금까지 했던 순서대로 다시 거슬러 올라가는" 짓을 해보자.

 

일단, 

addr_match 단계에서

인허가 - 네이버 매칭 된 음식점들 중, 예약 링크가 있는 애들을 Materialized view 로 저장해두자.

 

CREATE MATERIALIZED VIEW adtp_byaddress AS
(WITH addr_match AS
		(SELECT p.cls_main, p.pmid, p.title p_title, n.nid, n.title n_title,
		 similarity(p.title, n.title) AS text_sim,
		ROW_NUMBER () OVER (PARTITION BY pmid ORDER BY similarity(p.title, n.title) DESC) AS pn_sim, 
		 ROW_NUMBER () OVER (PARTITION BY nid ORDER BY similarity(p.title, n.title) DESC) AS np_sim
				-- Partition 기능은 유용하다. 같은 pmid 내에서 정렬할 수 있도록 partition 별 구분을 가능케 해준다
		FROM
			(SELECT pmid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main FROM permit_place) p
		LEFT JOIN
			(SELECT nid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main FROM naver_place) n
		ON 
			(p.cls_main = n.cls_main AND p.sig_nm = n.sig_nm AND p.rd_nm = n.rd_nm AND p.bd_bon = n.bd_bon AND p.bd_bu = n.bd_bu)
		WHERE nid is not null), --NULL인 아이들은 일단, 같은 건물 내에 네이버 검색 결과가 없는 게다.;
	-------------------------------------------1:1 매칭 성공
	best_matched AS
		(SELECT * FROM addr_match
		WHERE pn_sim = 1 AND np_sim = 1 AND text_sim > 0.15 ORDER BY text_sim, pmid)
--------------------------------------------1:1 매칭 실패	
SELECT b.pmid, b.p_title, ad.nid, ad.title n_title FROM best_matched b
JOIN (SELECT * FROM naver_place WHERE book LIKE 'h%') ad --예약 링크가 있는 애들을 중 매칭된 애들을 뽑는다.
ON b.nid = ad.nid);

 

총 21,473 개의 예약 가능 음식점들 중

17,043개의 음식점들은 한방에 매칭되었다.

 

이제 저 4,430개는 어디에...있는지..찾아야...한다...

 

일단 address로 걸러내지 못한 adtp 들을 따로 빼보자

 

SELECT ad_raw.* FROM adtp_byaddress ad
	RIGHT JOIN
	 	(SELECT * FROM naver_place WHERE book LIKE 'h%') ad_raw
	ON ad.nid = ad_raw.nid
	WHERE ad.nid IS NULL

아름답게 4430개, 딱 나머지 ADTP들이 나왔다.

 

얘들을 대상으로, geom query 를 다시 한 번 던져본다...

CREATE Materialized View ADTP_bygeom AS
(WITH
-----------------------------------------------주소로 매칭이 안된 ADTP들이다.
missed_adtp AS
	(SELECT ad_raw.* FROM adtp_byaddress ad
	RIGHT JOIN
	 	(SELECT * FROM naver_place WHERE book LIKE 'h%') ad_raw
	ON ad.nid = ad_raw.nid
	WHERE ad.nid IS NULL),
--------------------------------------------인허가데이터에 geom 추가
unmatched_permit AS
(SELECT pmid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main,
 x, ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 5174), 5179) geom ----------인허가데이터는 5174로 좌표를 제공한다.
 FROM permit_place),
-------------------------------------------놓친 ADTP에 geom 추가
unmatched_naver AS
(SELECT nid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main,
 ST_Transform(geom, 5179) geom
 FROM missed_adtp),
------------------------------------------포인트 좌표로 매칭
pointed_permit AS
(SELECT pmid, title, cls_main, sig_nm, rd_nm, geom
FROM unmatched_permit
WHERE x != 0 AND x is not null),

pointed_naver AS
(SELECT nid, title, cls_main, sig_nm, rd_nm, geom
FROM unmatched_naver),
----------------------------------------같은 도로명 주소를 가진 애들끼리 붙인다.
geom_joined AS
(SELECT pe.cls_main, pmid, pe.title p_title, nid, na.title n_title, 
ST_Distance(pe.geom, na.geom) dist,
 CASE
	WHEN array_length(regexp_split_to_array(pe.title, '\s+'), 1) > 1 --띄어쓰기가 있으면
	THEN 
		array_to_string(
		ARRAY(
			SELECT p_unset FROM
			unnest(regexp_split_to_array(pe.title, '\s+')) AS p_unset
			WHERE p_unset NOT LIKE '%점' AND array_length(regexp_split_to_array(pe.title, '\s+'), 1) > 1
			), ' '
		) -- '~~점' 으로 끝나는 단어를 제외하고 다시 텍스트를 구성한다
	ELSE pe.title END AS p_onlytitle, -- 그렇지 않으면 그냥 원래 이름을 유지
 CASE
	WHEN array_length(regexp_split_to_array(na.title, '\s+'), 1) > 1
	THEN 
		array_to_string(
		ARRAY(
			SELECT n_unset FROM
			unnest(regexp_split_to_array(na.title, '\s+')) AS n_unset
			WHERE n_unset NOT LIKE '%점' AND array_length(regexp_split_to_array(na.title, '\s+'), 1) > 1
			), ' '
		)
	ELSE na.title END AS n_onlytitle
FROM (SELECT * FROM pointed_permit) pe
LEFT JOIN pointed_naver na
ON pe.sig_nm = na.sig_nm AND pe.rd_nm = na.rd_nm AND pe.cls_main = na.cls_main),

----------------------------------------text similarity 도 확dls
geom_match AS
(SELECT cls_main, pmid, p_title, p_onlytitle, nid, n_title, n_onlytitle, similarity(p_onlytitle, n_onlytitle) text_sim,
ROW_NUMBER () OVER (PARTITION BY pmid ORDER BY similarity(p_onlytitle, n_onlytitle) DESC) AS pn_sim, 
ROW_NUMBER () OVER (PARTITION BY nid ORDER BY similarity(p_onlytitle, n_onlytitle) DESC) AS np_sim
FROM geom_joined WHERE dist < 100), -- 도로명 주소를 공유하는 애들 중 100m 이내


best_geom_matched AS
	(SELECT *FROM geom_match
	WHERE pn_sim = 1 AND np_sim = 1 AND text_sim > 0.2 ORDER BY text_sim, pmid)
	 --WHERE pn_sim = 1 AND np_sim = 1 AND text_sim > 0.5 ORDER BY text_sim, pmid)

SELECT * FROM best_geom_matched);

 

아아, 981개다. 솔직히 text sim 0.15 로 해도 되는데 진짜 한번 봐줬다.

 

나머지 애들 중에서 우리가 얻어낸 dtp_2024랑 매치될 수 있는 녀석들은 누구인가.

SELECT d.pmid, d.p_onlytitle, adtp_left.nid, adtp_left.n_onlytitle, similarity(d.p_onlytitle, adtp_left.n_onlytitle) sim FROM
	(SELECT *, CASE
				WHEN array_length(regexp_split_to_array(dtp_2024.title, '\s+'), 1) > 1 --띄어쓰기가 있으면
				THEN 
					array_to_string(
					ARRAY(
						SELECT p_unset FROM
						unnest(regexp_split_to_array(dtp_2024.title, '\s+')) AS p_unset
						WHERE p_unset NOT LIKE '%점' AND array_length(regexp_split_to_array(dtp_2024.title, '\s+'), 1) > 1
						), ' '
					) -- '~~점' 으로 끝나는 단어를 제외하고 다시 텍스트를 구성한다
				ELSE dtp_2024.title END AS p_onlytitle
	 FROM dtp_2024) d --우리는 dtp_2024 중에서 adtp들을 골라내는 과정 중에 있다.
LEFT JOIN
	(SELECT ad_raw.*,  CASE
							WHEN array_length(regexp_split_to_array(ad_raw.title, '\s+'), 1) > 1
							THEN 
								array_to_string(
								ARRAY(
									SELECT n_unset FROM
									unnest(regexp_split_to_array(ad_raw.title, '\s+')) AS n_unset
									WHERE n_unset NOT LIKE '%점' AND array_length(regexp_split_to_array(ad_raw.title, '\s+'), 1) > 1
									), ' '
								)
							ELSE ad_raw.title END AS n_onlytitle
	 FROM adtp_byaddress ad 
	RIGHT JOIN
	 	(SELECT np.* FROM
		 	(SELECT * FROM naver_place WHERE book LIKE 'h%') np
		LEFT JOIN adtp_bygeom adgeom
		ON np.nid = adgeom.nid
		WHERE adgeom.nid IS NULL) ad_raw -- NAVER Place 데이터에서 geom으로 붙은애 빼고
	ON ad.nid = ad_raw.nid
	WHERE ad.nid IS NULL) adtp_left --주소로 붙은 애들도 거르고, 나머지 adtp만 대상으로 본다
ON d.rd_nm = adtp_left.rd_nm AND
d.bd_bon = adtp_left.bd_bon AND d.bd_bu = adtp_left.bd_bu AND d.cls_main = adtp_left.cls_main --분류도 같고, 건물도 같은 애들끼리
WHERE similarity(d.p_onlytitle, adtp_left.n_onlytitle) >= 0.2 ORDER BY sim ASC --'점'을 떼고 이름으로 유사도 0.2 이상

코드가 괴랄해지는 이유는 저 '~~점'을 빼는 작업 때문이다 젠장.

 

122개 더 뺐다...

사실 0.2가 다소 애매하다고 느껴질 수 있긴 한데,

이렇게 해도 1082개밖에 안나오고, (3348개는 그냥 못찾은거다;;)

ADTP는 샘플 자체가 수가 적으니, 최대한 많이 뽑을 수 있는 대로 사용하는 것이 좋다.

 

SELECT pmid FROM adtp_byaddress byad
UNION 
SELECT pmid FROM adtp_bygeom byg
UNION
SELECT pmid FROM adtp_bydtp byd;

 

이렇게 17512 개의 샘플을 확보했다...

솔직히 여기서 3000개 더 찾아야 한다는 얘기인데...

본능을 따르면 되겠지만, 그냥 정상적인 루트로 찾는 방법을 최대한 썼으니, 이대로 가자.

 

 

SELECT * FROm dtp_2024
JOIN 
	(SELECT pmid FROM adtp_byaddress byad
	UNION 
	SELECT pmid FROM adtp_bygeom byg
	UNION
	SELECT pmid FROM adtp_bydtp byd) adtp
ON dtp_2024.pmid = adtp.pmid;

아마 adtp 의 pmid 에 중복이 있었을테니, 다소 숫자가 줄어드는 것도 이해가 간다.

 

그럼 이제 진짜 DTP에서 ADTP를 분리해내서, 새로운 materialized view를 만들어보자.

CREATE materialized view ADTP_2024 AS
	(SELECT dtp_2024.* FROm dtp_2024
	JOIN 
		(SELECT pmid FROM adtp_byaddress byad
		UNION 
		SELECT pmid FROM adtp_bygeom byg
		UNION
		SELECT pmid FROM adtp_bydtp byd) adtp
	ON dtp_2024.pmid = adtp.pmid);
    
 CREATE MATERIALIZED VIEW dtp_2024_new AS
(SELECT d.* FROM dtp_2024 d
LEFT JOIN adtp_2024 ad
ON d.pmid = ad.pmid
WHERE ad.pmid IS NULL);

 

이렇게 해서 나의 새로운 데이터 셋 NDTP2024, DTP_2024, ADTP_2024가 완성되었다...

 

이제... 다시 분석 돌리러 가자...