[PostGRES] 웹크롤링으로 찾지 못한 인허가 데이터 붙이기
이제 손수 하나하나 검색하기 이전,
내가 쓸 수 있는 마지막 재료를 넣을 차례다...
이전, NDTP 들을 네이버에 손수 검색하는 크롤링 코드를 짜뒀던 나, 칭찬한다.
일단,
전체 인허가 데이터: 208,664개
전체 NAVER 데이터: 158,249개
1. 같은 건물에 비슷한 이름을 가진 데이터셋 (text similarity > 0.15): (VIEW: buld_match)
1:1 매칭으로 105,338개
(남은 인허가 데이터는 103,320개 (왜 합이 안맞냐면, 나도 몰라. 아니 왜 query 할때마다 숫자가 다른건데 미친거 아니야?)
2. 같은 도로에 비슷한 이름을 가진 데이터셋 (text similarity > 0.3):
1:1 매칭으로 1,370개.
(왜 이렇게 적나? 해서 QGIS로 확인해봤더니 진짜로 매치가 안되더라...
폐업된 인허가 데이터도 NAVER에 남아있거나, 크롤링이 안된 인허가 데이터가 남아있거나)
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)
SELECT first_unmatched.* FROM
(SELECT permit_place.* FROM permit_place
LEFT JOIN best_matched ---------------------------같은 건물 내 1:1 매칭
ON permit_place.pmid = best_matched.pmid
WHERE best_matched.n_title IS NULL) first_unmatched
LEFT JOIN (SELECT * FROM t_best_geom_matched WHERE text_sim > 0.3) geom_matched ------------같은 도로명 내 매칭
ON first_unmatched.pmid = geom_matched.pmid
WHERE geom_matched.n_title IS NULL;
이렇게 해서 남은 인허가 데이터는 총
101,983개...
뭐 이렇게 많냐...
NDTP가 많다면 박수를 칠 일이지만,
하나하나 체크해야할 게 저렇게 많다는 건 비명을 지를 일이다.
최대한 줄여보자...
내가 이전에 돌려둔 NDTP 의 체크 결과를 살펴보자.
네이버에 "구" + "NDTP 이름" 으로 검색해서 나온 이름을 기준으로
검색 결과가 잘 나오면 이름을, 검색 결과가 안 나오면 0을, 에러가 발생하면 fail 을 기록해뒀다.
1. (구 명)_searchable.csv 파일
이 파일에는 해당하는 구별로
음식점 이름과 매칭에 성공한 NAVER 이름을 포함해두었다.
이걸 보아하니 다음에는 naver_id가 없는 녀석을 활용했으리라.
2. (구 이름)_serchable_check_test.csv
이 파일에는 끝의 몇 개의 데이터를 제외한 나머지 데이터들의 NAVER check 결과가 있다.
3. (구 이름)_serchable_check_test_(숫자).csv
마지막 열까지 모두 검색한 결과가 포함된 것으로 보인다.
아마 100개 단위로 저장하도록 알고리즘을 짜뒀는데, 100개 단위 이전에 저장되는 경우를 대비해 바로 끊는 코드를 만들어 놓은 것 같다.
그렇다면 이제 전략은,
searchable.csv 에 있는 주소 정보를 이용해서 unmatched 와 JOIN 할 거다 (IN SQL)
그러려면, searchable_test.csv 에 있는 id 를 이용해서 searchable.csv 와 naver title 을 join 해야 한다. (IN Python).
굳이 나누는 이유는, 저 데이터들을 다 SQL로 처리하자니 답도 안나와서 그렇다.
그럼 일단, Python 에서 친구들을 불러보자.
import pandas as pd
import glob
########################## 다른 이름 형식을 가진 파일들을 각자 불러보자
searchable_names = glob.glob('*_searchable.csv')
searchable_test_names = glob.glob('*_searchable_check_test_*.csv') #glob의 결과는 list다
#########################
results = [] # Dataframe 을 concate 로 붙일 거니까 for문 돌기 전에 빈 리스트를 미리 만들고
for n in range(len(searchable_names)): #for문을 돌면서 구별로 파일을 연다
search = searchable_names[n]
test = searchable_test_names[n]
s_df = pd.read_csv(search, encoding = 'utf8').rename(columns = {'Unnamed: 0': 'tid'}) #망할 Unnamed
t_df = pd.read_csv(test, encoding = 'utf8').rename(columns = {'Unnamed: 0': 'tid'})
t_df = t_df[(t_df['naver_check'] != '0') & (t_df['naver_check'] != 'fail')] # 검색이 안되거나 에러가 난 경우는 제외
t_df_check = t_df[['tid', 'naver_check']]
result = pd.merge(s_df, t_df_check, on='tid')
results.append(result)
naver_checked = pd.concat(results)
naver_checked.drop(columns = 'tid', inplace = True) #tid 는 임시였으니까 날려준다
일단 18,486개의 매칭을 찾았다... (뭔가 생각보다 많이 줄어든 기분인데...)
걱정인 건 실제 NAVER 검색 결과랑 매칭이 안될 수도 있을 것 같다는 기분...
예를들어 본-스미카츠 압구정 본점돈가스 는 text similarity 로 걸러질 것 같지만,
'세진' 이 '세진 미용실' 이었을 수도 있다는 거다.
일단 믿음을 가지고 진행하자.
위에서 얻은 데이터를 SQL로 옮겨야 한다.
Dataframe 을 SQL Table로 옮겨보자. (가장 에러가 많이 발생하는 과정이다.)
from sqlalchemy import create_engine
username = '이름이뭐에요'
password = '비번이뭐에요'
localhost = '주인님존함이뭐에요'
port = '5432'
database = '데이터베이스이름이뭐에요'
tablename = '테이블이름뭐로저장할거에요'
engine = create_engine(f'postgresql+psycopg2://{username}:{password}@{localhost}:{port}/{database}')
naver_checked.to_sql(tablename, engine, if_exists='replace', index=False) #sql로 dataframe 을 보내주는 마법
'''
마음아픈 사실. SQL에서 읽어올 때에는
import psycopg2 as psql
conn = psql.connect(host = localhost, port = port, user = username, password = password, databse = database)
pd.read_sql('SQL문', con =conn)
으로 읽을 수 있지만,
SQL로 내보낼 때에는 sqlalchemy 를 사용해야 한다.
개인적으로는 conn이 더 편한데, 어쩔 수 없지
'''
옮긴 테이블을 기존 결과물과 붙여본 결과
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),
unmatched AS
(SELECT first_unmatched.* FROM
(SELECT permit_place.* FROM permit_place
LEFT JOIN best_matched
ON permit_place.pmid = best_matched.pmid
WHERE best_matched.n_title IS NULL) first_unmatched
LEFT JOIN (SELECT * FROM t_best_geom_matched WHERE text_sim > 0.3) geom_matched
ON first_unmatched.pmid = geom_matched.pmid
WHERE geom_matched.n_title IS NULL)
SELECT u.pmid, u.title, o.naver_check
FROM unmatched u
LEFT JOIN overflow_result o
ON u.title = o.title AND u.addr = o.addr AND u.cls_main = o.cls_main --이름, 주소, main 만 같으면 되지 않을까?
WHERE o.naver_check is not null;
흠... 우선
1. 아까 파이썬 데이터 프레임에서는 18,486개였는데, 그러면 6,204 개의 매칭은 도대체 어디로 사라진건가
- Python 의 Drop duplicate 로 확인한 결과는 18,477개인데...
- permit_place 와의 매칭은 18,568 개 (distinct on pmid 로는 18,545 개의 매칭을 준다)
--> 변경된 매칭 과정이 약 6,000개를 추가 확보하면서 생긴 결과인 듯 싶다. 물론, 우리가 앞서 거친 매칭 과정이 더욱 신뢰 가능하므로, 먼저 소거한다고 생각하자.
2. 그리고 건방지게 6분이나 query 가 걸리는 이유는 무엇인가.
- Where 문이 포함되면서 시간이 갑자기 점프하는 경우가 많았는데,
일단 무엇보다 table 을 안만들겠다는 일념 때문에 with 문이 많아진 것도 사실이다.
매 쿼리마다 건축물을 죄다 join 하는 짓을 하고 있으므로, 임시 table 을 만드는 것도 방법이다.
사실 view가 제일 좋은 것 같은데, 뭐가 좋은지 잘 모르겠어서...
깜짝 검색
table 은 실제 저장용량을 먹고, view 는 그 때 그 때 query 하는 방식에 가까우니 속도로 치면 view가 더 느릴 수도 있다는 말. 다만, materialized view 는 실제로 저장을 하기 때문에 속도에 도움을 줄 수도 있다.
그럼, 1, 2차까지 분석된 unmatched place 들을 일단 materialized view 로 만들어놓고 그 뒤의 query 는 materialized view 를 사용하기로 하자.
(테스트 결과, view 로 저장해두는 건 query time 에 큰 도움이 되지 않았다.)
CREATE materialized VIEW unmatched AS --우리 이 materialized view 를 기억하기로 하자
(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)
SELECT first_unmatched.* FROM
(SELECT permit_place.* FROM permit_place
LEFT JOIN best_matched
ON permit_place.pmid = best_matched.pmid
WHERE best_matched.n_title IS NULL) first_unmatched --건물 내에서 매치업 성사 X
LEFT JOIN (SELECT * FROM t_best_geom_matched WHERE text_sim > 0.3) geom_matched
ON first_unmatched.pmid = geom_matched.pmid
WHERE geom_matched.n_title IS NULL); --도로명 내에서 매치업 성사 X
SELECT * FROM unmatched;
unmatched는 101,987개다. (사실 생각해보면 이럴거면 매번 view 를 만드는 게 낫겠다 싶은 생각이 든다)
깜짝 task. view 만들기
즉흥적으로 view 작업을 시작했다.
CREATE VIEW buld_match 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)
SELECT * FROM addr_match
WHERE pn_sim = 1 AND np_sim = 1 AND text_sim > 0.15 ORDER BY text_sim, pmid);