[PostGRES] 공간 쿼리로 비슷한 이름 찾기

PostGRES

[PostGRES] 공간 쿼리로 비슷한 이름 찾기

이언배 2024. 10. 2. 22:11

앞에서 같은 주소를 가진 인허가와 NAVER Place 의 매칭을 완료했다.

이제 남은 건 매칭에 실패한 인허가 103325개, 매칭에 실패한 NAVER place 52907 개를 매칭하는 것.

베스트 시나리오는 저 NAVER Place 가 모두 인허가 데이터에 붙어 약 50000개만 손수 검색하면 되게 만들어주는 것 (하 진짜) 이지만,

인생은 그렇게 호락호락하지 않다.

쓸 수 있는 전략은 2개다.

1. x, y 포인트 좌표 사용하기

인허가 데이터에는 5179 기준의 x, y 좌표가, NAVER 데이터에는 4326 기준의 x, y 좌표가 있다.

다만, 인허가 데이터 중 학원 데이터 일부는 x, y좌표가 없고, 누락되어있는 경우도 상당하다.

2. 건축물 데이터 사용하기

모든 데이터에는 건물 본번과 부번이 있다. 건축물 geometry 를 활용하면 공간 쿼리가 가능하다.

초기에 사용했던 전략은 2번. 어차피 건축물은 붙여야 하기 때문에.

하지만, 건축물 데이터는 항상 정답이 되어주지 않는다.

1. 건축물 geometry가 제대로 작성되지 않은 경우 (개같은 경우)

2. 집합 건축물이어서 같은 본번과 부번을 가진 건축물이 많은 경우 (더 개같은 경우)

그러니 일단, 포인트 좌표가 있는 경우는 최대한 살리고 (사실 이것도 믿을 수 있다는 보장은 없지만...),

부득이한 경우에만 건축물 geometry 를 활용하는 방안을 찾아보자.

그리고, 공간 쿼리는 몇 m 로 날릴 것인가.

건물 몇 개 떨어진 정도를 보겠다면 50m, 한 블럭 단위로 보겠다는 100m, 동 절반 정도 보겠다면 250m, 동 너머까지 보겠다면 500m 쿼리가 적절해보인다.

솔직히 동이 바뀐 건 그냥 매칭이 안되는 거라고 봐도 될 것 같고, 나는 겸손하게 100m 기준으로 보겠다.

1. 인허가, NAVER 데이터로 POINT Geometry column 생성.

2. Point geometry 로 00m 공간쿼리 수행.

2. 유효하지 않은 POINT 에 대하여 건축물 geometry 추가.

1. Point Geometry Column 을 생성하고, 상태가 영 안좋은 녀석들까지 찾아내보자.

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인 아이들은 일단, 같은 건물 내에 네이버 검색 결과가 없는 게다.;

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),
	
unmatched_permit AS
(SELECT permit_place.pmid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.pmid bpmid,
 ST_SetSRID(ST_MakePoint(x, y), 5179) geom --x, y 좌표로 Geometry 를 추가
 FROM permit_place
 LEFT JOIN (SELECT pmid, nid, n_title FROM best_matched) b
 ON permit_place.pmid = b.pmid
 WHERE b.pmid is null),

unmatched_naver AS
(SELECT naver_place.nid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.nid bnid,
geom -- Naver는 아예 geometry 가 있었다.
 FROM naver_place
LEFT JOIN (SELECT pmid, nid, p_title FROM best_matched) b
ON naver_place.nid = b.nid
WHERE b.nid is null)

SELECT * FROM unmatched_naver;

일단, 상태가 좋지 않은 경우는 2가지이다.

x 가 0으로 기록되어있거나, null 인 경우. 이 경우에 만들어진 포인트좌표는 탈락이라고 봐도 무방하다.

x좌표가 서울 이내에 들어와있느냐도 하고싶지만, 솔직히 그건 걸러낼 자신 없으니까 넘어가겠다.

pointed_permit AS
(SELECT pmid, title, cls_main, geom
FROM unmatched_permit
WHERE x != 0 AND x is not null), -- 잘못된 좌표 데이터. 약 4515개 날림.

pointed_naver AS
(SELECT nid, title, cls_main, geom
FROM unmatched_naver)

그렇게 해서 포인트 좌표가 유의미한 데이터 98810개. 전체 103325개와 비교했을 때 약 4,515개 정도가 날아갔다.

이제 52909개의 naver place 와 좌표 기반으로 query 를 날려보자.

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 매칭 실패	
unmatched_permit AS
(SELECT permit_place.pmid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.pmid bpmid,
 x, ST_SetSRID(ST_MakePoint(x, y), 5179) geom
 FROM permit_place
 LEFT JOIN (SELECT pmid, nid, n_title FROM best_matched) b
 ON permit_place.pmid = b.pmid
 WHERE b.pmid is null),

unmatched_naver AS
(SELECT naver_place.nid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.nid bnid, geom
 FROM naver_place
LEFT JOIN (SELECT pmid, nid, p_title FROM best_matched) b
ON naver_place.nid = b.nid
WHERE b.nid is null),
------------------------------------------포인트 좌표 생성
pointed_permit AS
(SELECT pmid, title, cls_main, geom
FROM unmatched_permit
WHERE x != 0 AND x is not null),

pointed_naver AS
(SELECT nid, title, cls_main, geom
FROM unmatched_naver)
-----------------------------------------포인트 좌표 기준 100m 버퍼 LEFT JOIN
SELECT
    buf_pmid, p_title, nid, title as n_title,
	similarity(p_title, pointed_naver.title) AS text_sim
FROM
    pointed_naver
JOIN
    (
        SELECT
            pmid AS buf_pmid, -- 100m 버퍼의 아이디 살린다
			title AS p_title,
            ST_Buffer(geom, 100) AS buffer_geom --포인트좌표 기준으로 100m 버퍼 만든다
        FROM
            pointed_permit 
    ) buffer_pointed
ON
    ST_Within(pointed_naver.geom, buffer_pointed.buffer_geom);

코드를 돌리고 25분째, 쿼리가 끝나지 않았다.

시간이 꽤 걸리는 모양.

50m로 바꾸고 20분 기다려보다가, 안되면 100m로 바꾸고 테이블을 별도로 저장해둬야겠다.

Sample 100개로 따서 분석해본 결과

완전히 망했다.

그럼 여기 있는 인허가 데이터들은 크롤링한 데이터랑 매칭이 안된다는 건데,

도대체 검색이 안되는 네이버 크롤링 결과물이 왜 5만개씩이나 있는건가?

와아, 13시간 돌렸는데 안돌아갔다. 역시 buffer query 만만치 않다.

그렇다면, 다른 방식을 선택해보자.

1. 같은 동끼리 묶는다

2. 묶은 포인트 간의 distance 를 잰다.

3. distance 가 100m 이하인 녀석들 끼리 이름을 비교한다.

문제는 같은 동끼리 묶을 수 없다는 것. 왜냐하면 도로명 주소니까.

도로명을 동으로 바꾼다? 아니 그것도 위험하다. 동에 해당되지 않은 도로들도 제법 많다.

일단 믿음을 가지고, 도로명이 같은 녀석들 (같은 도로에 있는 녀석들) 을 기준으로 거리를 재보자.

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 매칭 실패	
unmatched_permit AS
(SELECT permit_place.pmid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.pmid bpmid,
 x, ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 5174), 5179) geom ----------인허가데이터는 5174로 좌표를 제공한다.
 FROM permit_place
 LEFT JOIN (SELECT pmid, nid, n_title FROM best_matched) b
 ON permit_place.pmid = b.pmid
 WHERE b.pmid is null),

unmatched_naver AS
(SELECT naver_place.nid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.nid bnid,
 ST_Transform(geom, 5179) geom
 FROM naver_place
LEFT JOIN (SELECT pmid, nid, p_title FROM best_matched) b
ON naver_place.nid = b.nid
WHERE b.nid is null),
------------------------------------------포인트 좌표로 매칭
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
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 도 확인
SELECT cls_main, pmid, p_title, nid, n_title, similarity(p_title, n_title) text_sim
--ROW_NUMBER () OVER (PARTITION BY pmid ORDER BY similarity(p_title, n_title) DESC) AS pn_sim
FROM geom_joined WHERE dist < 100 AND similarity(p_title, n_title) > 0
ORDER BY text_sim desc;

유레카!! 매칭되는 녀석들이 제법 생겼다!!

다만 이 매칭은 전체 다 돌아가면 46분이 소요되고, 같은 백화점 또는 멀티플렉스 내에서는 많은 점포들이 포함되어 있으므로, text similarity 의 기준을 보수적으로 잡을 필요가 있다. (~~~~점 이라는 텍스트가 공통으로 들어가있으면 text similarity 가 높게 잡힘)

우선 워낙 쿼리가 오래 걸리니, sample 을 1000개 정도만 이용해서 같은 도로 내 100m 이내 매칭 결과 중 서로 1:1 매치인 경우를 또 걸러내보자.

아래와 같은 코드를 추가하고,

----------------------------------------text similarity 도 확인
geom_match AS
(SELECT cls_main, pmid, p_title, nid, n_title, similarity(p_title, n_title) 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
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.15 ORDER BY text_sim, pmid)
	 WHERE pn_sim = 1 AND np_sim = 1 AND text_sim > 0.15 ORDER BY text_sim, pmid)

하... 이 애매 애매 애매한... text similarity 가 0.28이 넘어도 False matching 인 경우가 있다니...

그렇다면 '~~점'을 제외한 query 로 매칭 분석을 높여보자.

우리 Chat 교수님과의 면담 결과,

"텍스트 내에서 특정 글자를 가진 단어를 제외한 나머지 그룹을 추출한 쿼리" 하는 법을 배웠다.

SELECT p_title,
array_to_string( -----------array 였던 결과를 다시 string 으로
	ARRAY( -----------------일단 array로 바꿔서 요소별로 보겠다
		SELECT p_unset FROM
		unnest(regexp_split_to_array(p_title, '\s+')) AS p_unset ------'띄어쓰기' 기준으로 요소를 만들어 array 로
		WHERE p_unset NOT LIKE '%점' ------------~~점 으로 끝나는 요소를 제거
	), ' '
) AS p_onlytitle,
n_title,
array_to_string(
	ARRAY(
		SELECT n_unset FROM
		unnest(regexp_split_to_array(n_title, '\s+')) AS n_unset
		WHERE n_unset NOT LIKE '%점'
	), ' '
) AS n_onlytitle
FROM geom_joined WHERE dist < 100;

PostGRES 에 대한 믿음이 부족했다. 다시 한 번, 믿음이 부족했던 제 자신을 반성합니다...

다만 조심해야할 점은 '진미생고기전문점' 은 아예 사라져버렸다는 점, '점'밖에 없으니 이런 문제가 생긴다.

이런 경우를 대비하기 위해 array 길이가 하나인 경우를 제외해야 할 수도 있겠다.

그러면 띄어쓰기없이 이름 정한 녀석들은 다 살아 남겠지만...

걔들이 다 사라지는 것보다야는 낫지 않을까.

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 매칭 실패	
unmatched_permit AS
(SELECT permit_place.pmid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.pmid bpmid,
 x, ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 5174), 5179) geom ----------인허가데이터는 5174로 좌표를 제공한다.
 FROM permit_place
 LEFT JOIN (SELECT pmid, nid, n_title FROM best_matched) b
 ON permit_place.pmid = b.pmid
 WHERE b.pmid is null),

unmatched_naver AS
(SELECT naver_place.nid, title, sig_nm, rd_nm, bd_bon, bd_bu, cls_main, b.nid bnid,
 ST_Transform(geom, 5179) geom
 FROM naver_place
LEFT JOIN (SELECT pmid, nid, p_title FROM best_matched) b
ON naver_place.nid = b.nid
WHERE b.nid is null),
------------------------------------------포인트 좌표로 매칭
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 LIMIT 1000) 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.15 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;

이야 이 정도면 이제야 편안하다.

이제 이러고도 매칭이 안된 녀석들은 버퍼 매칭으로 들어가던지, 바로 크롤링을 하던지 해야 하므로,

50분이 넘어가는 이 쿼리 작업은 따로 테이블을 만들어 저장해두고 일단락 짓도록 하자.

728x90