[python_주소DB가지고놀기] 파이썬 으로 주소DB에 위도 경도 값 api로 받아 update 하기 #2

[python_주소DB가지고놀기] 파이썬 으로 주소DB에 위도 경도 값 api로 받아 update 하기 #2

 

안녕하세요.

 

지난번에 주소 DB를 공공데이터 포털에서 다운로드하여서 구축하는 작업을 했습니다.

 

## python_주소DB가지고놀기 1편 보러 가기

2020/01/17 - [DB엔지니어가 공부하는 python] - [python_주소DB가지고놀기]공공데이터포털 에서 주소DB 다운 받아 DB에 insert 하기 #1

[python_주소DB가지고놀기]공공데이터포털 에서 주소DB 다운 받아 DB에 insert 하기 #1

 

테이블 4개에 주소 데이터들이 잘 들어가 있을 텐데요.

 

아마 잘 따라 하신 분들은 데이터 건수도 확인하셨을 텐데, 주소 테이블이 지번 테이블보다 건수가 약 2백만 건 정도 적습니다.

 

일단 이 내용 머릿속에 기억해 두시고, 오늘 할 것은 구축한 주소 DB에 위도, 경도 값을 api로 받아서 update 하는 작업!

 

해보겠습니다.

 

아까 한번 말했듯이 주소 테이블이 가진 데이터가 지번 데이터보다 적습니다.

 

이것은 한 도로명주소가 1개 이상의 지번과 매핑이 된다고 봐도 되는 거겠죠. 그리고 일부 지번주소는 도로명주소가

 

매칭 되지 않는 경우도 있습니다. 현재 시스템상 그렇다고 합니다.

 

그래서 1개 이상의 지번주소와 매칭 되는 도로명주소는 그 지번주소 중 대표 주소를 찾아 1:1로 매칭을 해주어야 합니다.

 

위도 경도 API를 호출하기 위해서는 주소 full text가 필요합니다.

 

하지만 우리가 구축한 주소 DB에는 주소 full text가 없습니다.

 

만들어 줘야 합니다. 먼저 지번, 도로명 주소를 update 할 칼럼을 생성하겠습니다.

 

두 개의 칼럼을 juso_info_m(주소 테이블)에 추가하도록 하겠습니다.

# 지번주소값을 저장 할 컬럼
alter table schema_name.juso_info_m add inf_full_jb_addr varchar(300) null comment 'inf_지번주소';

# 도로명주소값을 저장 할 컬럼
alter table schema_name.juso_info_m add inf_full_road_addr varchar(300) null comment 'inf_도로명주소';

 

 

 

* 저는 mariadb를 쓴다는 점 참고하시기 바랍니다.

 

위와 같이 지번주소와 도로명 주소를 update 할 칼럼을 생성합니다.

 

그리고 주소 정보를 update 하는 sql문을 공유합니다.

 

-- # 도로명주소 update 문
update address_svc_m.juso_info_m ori
inner join
    (select
        CASE
            WHEN B.umd_nm IS NULL OR B.umd_nm = ''
                THEN CONCAT(B.sd_nm, ' ', B.sgg_nm, ' ', B.road_nm, ' ',
                            IF(A.build_sub_num = 0, A.build_main_num,
                               CONCAT(A.build_main_num, '-', A.build_sub_num)),
                            IF(D.multi_unit_yn = 1, IF(D.sgg_build_nm IS NULL OR D.sgg_build_nm = '', '',
                                                       CONCAT(' (', D.sgg_build_nm, ')')), ''))
            ELSE CONCAT(B.sd_nm, ' ', B.sgg_nm, ' ', B.road_nm, ' ',
                        IF(A.build_sub_num = 0, A.build_main_num,
                           CONCAT(A.build_main_num, '-', A.build_sub_num)), ', (',
                        B.umd_nm, IF(D.multi_unit_yn = 1, IF(D.sgg_build_nm IS NULL OR D.sgg_build_nm = '', '',
                                                             CONCAT(', ', D.sgg_build_nm)), ''), ')')
            END as road_addr,
            A.mgmt_num


FROM address_svc_m.juso_info_m A,
    address_svc_m.road_cd_info_m B,
    address_svc_m.add_info_m D
WHERE A.road_nm_cd = B.road_nm_cd
  AND A.umd_seq = B.umd_seq
  AND A.mgmt_num = D.mgmt_num) src
on ori.mgmt_num = src.mgmt_num

set ori.inf_full_road_addr_2 = src.road_addr
;


-- # 지번주소 update 문
update address_svc_m.juso_info_m ori
    inner join
    (SELECT CASE
                WHEN C.sgg_nm IS NULL OR C.sgg_nm = ''
                    THEN CONCAT(C.sd_nm, ' ', C.law_umd_nm, ' ',
                                IF(C.law_ri_nm IS NULL OR C.law_ri_nm = '', '', CONCAT(C.law_ri_nm, ' ')),
                                IF(C.san_yn = 0, '', '산 '),
                                IF(C.jb_sub_num = 0, C.jb_main_num, CONCAT(C.jb_main_num, '-', C.jb_sub_num)),
                                IF(D.multi_unit_yn = 1, IF(D.sgg_build_nm IS NULL OR D.sgg_build_nm = '', '',
                                                           CONCAT(' (', D.sgg_build_nm, ')')), ''))

                ELSE CONCAT(C.sd_nm, ' ', C.sgg_nm, ' ', C.law_umd_nm, ' ',
                            IF(C.law_ri_nm IS NULL OR C.law_ri_nm = '', '', CONCAT(C.law_ri_nm, ' ')),
                            IF(C.san_yn = 0, '', '산 '),
                            IF(C.jb_sub_num = 0, C.jb_main_num, CONCAT(C.jb_main_num, '-', C.jb_sub_num)),
                            IF(D.multi_unit_yn = 1,
                               IF(D.sgg_build_nm IS NULL OR D.sgg_build_nm = '', '', CONCAT(' (', D.sgg_build_nm, ')')),
                               ''))
                END as jb_addr,
            C.mgmt_num

     FROM address_svc_m.jibun_info_m C,
          address_svc_m.add_info_m D
     WHERE C.mgmt_num = D.mgmt_num
       AND C.main_yn = 1) src
    on ori.mgmt_num = src.mgmt_num

set ori.inf_full_jb_addr_2 = src.jb_addr
;

도로명주소와 지번주소 update 문입니다.

 

oracle과 mariadb 간에 조금 다른 문법이 있으니, 이 부분 잘 확인하시어 적용 바랍니다.

 

이렇게 까지 작업을 하면 주소 DB에 지번주소와 도로명 주소가 full text로 update 되었습니다.

#문의사항 있으시면 댓글을 남겨주시면 상세하게 안내드리겠습니다.

 

자, 이젠 이 주소 텍스트를 이용해서 각 주소별 위도, 경도 값을 api로 받아서 update 하는 걸 파이썬으로 처리하겠습니다.

 

주소별 위도, 경도 api는 구글맵, 카카오 맵, 네이버 클라우드 플랫폼 등등 에서 제공하고 있습니다.

 

그러나, 위에서 나열한 api들은 좀 제약이 있습니다. 네이버 클라우드 플랫폼이 그래도 좀 덜한 편이데..

 

전 아무도 잘 알려지지 않은 곳에서 api를 괜찮은걸 찾았습니다!! ㅎㅎ

 

여긴 계정당 하루 제한이 있으나, 계정을 많이 만들 수 있습니다.

 

나중에는 api 받아오는 파이썬 프로그램을 제 DB가 깔려있는 서버에 crontab을 걸어서 작업했는데도 거의 3주 정도 걸린 것 같습니다.

 

위도 경도받아오는 건 정말 시간과 끈기의 싸움입니다.

 

만약 구축하고 싶으시면 끈기 있게 도전하시기 바랍니다!!

 

제가 위도, 경도 api를 제공받은 곳은 아래와 같습니다.

http://www.vworld.kr/dev/v4dv_geocoderguide2_s001.do

공간정보 오픈플랫폼 오픈API

 

이곳에 접속하셔서 회원 가입하시고, Geocoder API 키를 발급받으시길 바랍니다.

 

키는 계정당 10개씩 발급받을 수 있으며, 키다 하루 3만 개의 API를 호출할 수 있습니다.

 

그럼 계정당 약 30만 개씩 하루에 받을 수 있고, 지도 데이터는 약 620여만 개이니, 산술적으로 약 21일 정도가 걸립니다.

 

제가 올려드릴 파이썬 코드를 crontab으로 스케줄 걸어서 사용하시면 빨리 할 수 있지 않을까 싶습니다.

 

참, api를 이용해서 호출하다 보면 exception 처리를 하기 위해서 inf_flag 칼럼이 하나 필요합니다.

 

이 칼럼을 주소테이블에 add 해 줍니다.

 

 

 

alter table schema_name.juso_info_m add inf_flag varchar(1) null comment 'inf_플래그';

이 플래그 칼럼을 통해서 아직 변환하지 않은 주소와, 한 주소, 에러가 난 주소를 구분 할 수 있습니다.

 

위 플래그 컬럼을 추가한 후 기본값으로 'N'을 update 해 줍니다.

UPDATE address_svc_m.juso_info_m SET inf_flag = 'N';

 

Geocoder API 호출하여 주소 DB에 update 하는 파이썬 코드는 아래와 같습니다.

 

import urllib.request
import urllib.parse
import pandas
import pymysql
import ast

#필요한 라이브러리들을 import 합니다.

# db connect 부분 입니다.
conn = pymysql.connect(host='XX.51.102.XXX',
                      user = 'user', password='password', db = 'address_svc_m',charset = 'utf8')

curs = conn.cursor(pymysql.cursors.DictCursor)

job_seq = 0

for time in range(0,2800):

    # key (mgmt_num) 과 full 도로명 주소를 쿼리 합니다.
    sql = "SELECT mgmt_num, inf_full_road_addr FROM address_svc_m.juso_info_m WHERE inf_flag = 'N' LIMIT 1"
    curs.execute(sql)
   
    #쿼리한 결과를 p_val에 저장
    p_val = curs.fetchall()
    
    #판다스에 저장 합니다.
    df = pandas.DataFrame(p_val, columns = ["mgmt_num","inf_full_road_addr"])

    p_mgmt_num = df.mgmt_num[0]
    inf_full_road_addr = df.inf_full_road_addr[0]

    # Geocoder API 2.0
    ApiKey = "발급받으신 Goecoder API의 key"
    
    address = inf_full_road_addr
    
    
    # API 호출 부분
    apiUrl = 'http://api.vworld.kr/req/address?service=address&request=getCoord&key='+ApiKey+'&'

    values = {
        'address':address,
        'type':'PARCEL'
    }

    param = urllib.parse.urlencode(values)
    Adding = apiUrl+param

    req = urllib.request.Request(Adding)
    res = urllib.request.urlopen(req)

    respon_data = res.read().decode()

    DataDict = ast.literal_eval(respon_data)

    v_check = DataDict['response']['status']
    
    # 위도, 경도값을 못찾은 경우를 대비해 못찾았으면 inf_flag에 'E'를 찍고 넘어감
    # 이렇게 처리 하지 않으면 프로그램이 돌다가 멈춰요..ㅠㅠ
    if v_check == 'NOT_FOUND' :
        sqlFail = "UPDATE address_svc_m.juso_info_m SET inf_flag = 'E' WHERE mgmt_num = %s"
        val = (p_mgmt_num)
        curs.execute(sqlFail,val)
        
        conn.commit()
    
    else :
        
        # 위도, 경도 데이터 받아서 update
        longtitude = DataDict['response']['result']['point']['x']
        latitude = DataDict['response']['result']['point']['y']
    
        sqlUpdate = "UPDATE address_svc_m.juso_info_m SET inf_flag = 'Y', inf_latitude = %s, inf_longtitude = %s WHERE mgmt_num = %s"
        val = (latitude,longtitude,p_mgmt_num)
        curs.execute(sqlUpdate,val)
    
    job_seq = job_seq + 1
    
    conn.commit()
        
    if job_seq%10 == 0 :
        conn.commit()
        print(job_seq)

print(job_seq)
conn.commit()

conn.close()

소스 안에 주석을 확인하시어 코드를 확인해 보세요.

 

아무래도 제가 만든 거다 보니 곳곳에 허점이 있을 수 있습니다.

 

그래도 이렇게 까지 한건 참... 개인적으론 뿌듯합니다.

 

전 위 소스를 아까도 말씀드렸다시피, ubuntu 서버에 올려서 crontab으로 스케쥴링해두고 update를 했습니다.

 

그러지 않고는 절대 할 수가 없었을 겁니다. 워낙 시간이 오래 걸리는 작업이라서요.

 

여러분들도 한번 필요하시다면 도전해 보시기 바랍니다.

 

오늘 주소 DB 가지고 놀기는 여기까지 입니다.

 

다음은 이걸로 뭘 해볼까요? ㅎㅎ

 

괜찮은 아이디어를 주시면 감사하겠습니다!

 

한주의 시작 월요일입니다. 활기차게 시작하세요!!

 

방문해 주시고, 읽어주셔서 감사합니다.

 

 

 

 

by.sTricky