[Project/4th] 법정동, 좌표값 데이터 DB에 저장하기(1)

 최근 구현하게 된 기능은 '지역 기반 게시글 검색'이다. 너무나도 유명한 서비스인 당근마켓의 홈화면에서 내가 살고 있는 동네 근처에 있는 중고거래 게시글을 보는 것과 비슷하다고 생각하면 된다. 내가 프로젝트에서 구현하게 된 기능도 사용자가 선택한 지역의 근처 지역에 있는 모든 게시글들을 보여주는 것이다.

 

 원래는 주소를 사용하는 기능들(사용자 주소 등록, 공사 내역 등록)은 프론트엔드에서 select box를 통해 법정동 기반으로 시도, 시군구, 읍명동을 입력을 하면, 주소명으로 요청이 와서 DB에 주소명 그대로 저장하는 방식이었다.

 

 하지만, '지역 기반 검색 기능'이 들어가게 되면서 확장성을 위해 백엔드 서버에도 법정동 코드와 함께 시도명, 시군구명, 읍면동명을 저장할 필요가 있었다. 게다가, '근처 지역의 게시물 검색'이 되면서, 법정동 정보뿐만 아니라 법정동의 좌표 정보(위도, 경도)까지 필요하게 되었다...

 

 

 

 

 

기능 구현에 필요한 데이터를 얻기 위해 데이터 수집과 가공에 많은 고민과 시간을 쏟게 되어서, 그 과정을 적어보고자 한다!

 

1. 법정동 코드와 동이름 저장하기 -> 현재 포스팅

2. 법정동의 좌표값(위도, 경도) 저장하기 -> 다음 포스팅

 

 

 

 

 

 

 

 

 

개발 환경

---

• OS : MAC M2
• Data : Python3
• IDE : Visual Studio Code
• DB : MySQL 8.0.36

 

 

 

 

 

 

 

 

 

 

법정동 데이터 수집하기

---

 현재 프론트엔드(React Native)에서 쓰고 있는 JSON 형태의 법정동 주소 파일이 있었다. 하지만, 저장된 주소가 계층화되어 있어 복잡해 DB에 저장하기에는 어려워보였다.

 

 그래서 XLSX(엑셀)이나 SCV 파일을 구해서 직접 가공하기로 했다. 파일을 이용하면 법정동 코드, 시도명, 시군구명, 읍명동명을 줄(row) 단위로 가져와 그대로 DB에 저장하기 좋기 때문이다.

 

 

 

법정동 데이터 예시

 

 

검색을 통해 정부 기관에서 공개한 법정동 데이터 파일을 찾을 수 있었다.

 

• 행전안전부(2024.02.08 등록 버전)  : https://www.mois.go.kr/frt/bbs/type001/commonSelectBoardArticle.do?bbsId=BBSMSTR_000000000052&nttId=107074
  • jscode20240228.zip 파일을 다운받으면, 다양한 버전의 xlsx(엑셀파일)이 있다. 파일마다 얻을 수 있는 상세 정보가 다르다.
  • 나는 법정동의 주소명만 필요하기 때문에 KIKcd_B.20240208.xlsx을 사용했다.

 

• 공공데이터포털(2024.05.13 수정 버전) : https://www.data.go.kr/data/15063424/fileData.do
  • csv 형태의 파일을 다운받을 수 있다.

 

 

 

 

 

 

 

 

 

 

법정동 데이터 가공하기

---

방법1) XLSX 파일을 SQL 쿼리로 바꿔서 DB에 저장하기

 

 XLSX이나 SCV 파일을 파이썬에서 가져와 DB로 저장하는 방법이 있었다. 하지만, 코드를 작성하기 귀찮았기에.. SQL에 삽입하는 쿼리로 변환해 실행시키고자, Exel 파일을 SQL로 바꿔주는 프로그램을 사용하게 되었다.

 

 

 

엑셀 파일 데이터

 

 

 

법정동코드, 시도명, 읍면동명을 가져와 삽입하도록 했다.

INSERT INTO ADDRESS (ADDRESS_CODE, SIDO, SIGUNGU, EUBMYEONDONG) VALUES ('1100000000', '서울특별시', '', '');
INSERT INTO ADDRESS (ADDRESS_CODE, SIDO, SIGUNGU, EUBMYEONDONG) VALUES ('1111000000', '서울특별시', '종로구', '');
INSERT INTO ADDRESS (ADDRESS_CODE, SIDO, SIGUNGU, EUBMYEONDONG) VALUES ('1111010100', '서울특별시', '종로구', '청운동');
INSERT INTO ADDRESS (ADDRESS_CODE, SIDO, SIGUNGU, EUBMYEONDONG) VALUES ('1111010200', '서울특별시', '종로구', '신교동');
INSERT INTO ADDRESS (ADDRESS_CODE, SIDO, SIGUNGU, EUBMYEONDONG) VALUES ('1111010300', '서울특별시', '종로구', '궁정동');
INSERT INTO ADDRESS (ADDRESS_CODE, SIDO, SIGUNGU, EUBMYEONDONG) VALUES ('1111010400', '서울특별시', '종로구', '효자동');

 

 

 

 하지만, 시군구명, 읍면동명이 비어있는 것도 함께 저장이 되었다. 나중에는 시군구명, 읍면동명에 나중에 추가될 위도, 경도가 null인 데이터가 꽤 생긴다는 것이 문제였다. 단순히 변환으로는 이 경우가 저장되지 않게 처리하지 못한다.

 

 그리고, 좌표 정보도 저장을 해야하는데, 어차피 코드를 작성해 API로 좌표를 받아와 저장해야 돼서 파이썬으로 돌리기로 했다!

 

 

 

 

 

 

방법2) 파이썬으로 엑셀 파일을 읽어 DB에 저장하기

 

 검색을 통해 파이썬으로 작성된 행정동 데이터를 MySQL DB에 저장하는 방법을 찾을 수 있었다. 파이썬의 openpysl과 pymysql 라이브러리를 사용해 엑셀 파일을 읽어, mysql에 저장하는 방법이다.

 

 코드를 내 액셀 파일과 필요한 데이터에 맞게 수정해 DB에 저장할 수 있었다.

 

 

MySQL에 저장된 데이터 결과

 

 

 

 

 

 

 

 

 

 

 

파이썬으로 엑셀 데이터 DB에 저장하기

---

1) 파이썬 패키지 설치하기

pip3 install openpyxl
pip3 install pymysql

 

• openpyxl : 엑셀 파일 사용을 위한 라이브러리 
• pymysql : MySQL 사용을 위한 라이브러리

 

 

*주의 : 맥에서는 pip 말고, pip3라고 명시를 해줘야 실행이 된다. 만약, pip3 명령어도 안먹힌다면, 설정을 따로 또 해줘야 한다!

python3 -m pip config set global.break-system-packages true

 

 

 

 

 

2) 엑셀 파일 불러와서 읽기

엑셀 데이터 예시

 

 

 

 

 

데이터를 가져올 때, 고려해야 될 부분이 있었다.

• 시군구명, 읍명동명이 비어있는 줄은 저장하지 않는다.
• 동리명 단위까지는 필요하지 않으므로 저장하지 않는다. (여기서 주의하기)

 

읍단위가 있는 부분

 

 

위의 경우처럼, 읍단위에서는 리까지 존재한다. 그래서 동리명이 null인 경우에만 저장을 하도록 했다.

 

 

 

 

 

from openpyxl import load_workbook
import pymysql

mydict = {}

def import_xlsxfile(filename):
    load_wb = load_workbook(filename, data_only=True)
    load_ws = load_wb['KIKcd_B']

    head = True
    for row in load_ws.rows:
        if head is True:
            head = False
            continue

        if row[2].value is None or row[3].value is None:
            continue

        if not row[4].value: #동리명까진 출력 안함
            fullCode = row[0].value  # A = row[0] 법정동코드 
            sido = row[1].value  # B = row[1] 시도명
            sigungu = row[2].value  # C = row[2] 시군구명
            eubmyeondong = row[3].value  # D = row[3] 읍면동명

            mydict[fullCode] = [fullCode, sido, sigungu, eubmyeondong]

 

 

 

 

 

 

 

 

 

 

2) MySQL에 저장하기

 

 MySQL에 먼저 테이블을 생성해주었다.

 

테이블 구조

 

 

 

 

 

그리고 MySQL에 연결하여, 앞서 Dictionary에 저장한 값들을 DB에 넣었다.

 

def insert_into_mysql():
    conn = pymysql.connect(host='호스트 주소', user='계정명', password='비밀번호', db='DB명', charset='utf8')
    curs = conn.cursor()

    for key in mydict:
        arr = mydict[key]
        sql = "INSERT INTO address VALUES(\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\");"\
            % (arr[0], arr[1], arr[2], arr[3], arr[4], arr[5])
        curs.execute(sql)

    conn.commit()
    conn.close()

 

 

 

 

 

 

 

4) 실행 결과

 

 

 

 

 

 

 

 

 

 

참고 자료

---

[Python] 대한민국 행정동 데이터 MySQL DB 테이블 생성 예시 : https://woonizzooni.tistory.com/entry/Python-%EB%8C%80%ED%95%9C%EB%AF%BC%EA%B5%AD-%ED%96%89%EC%A0%95%EB%8F%99-%EB%8D%B0%EC%9D%B4%ED%84%B0-MySQL-DB-%ED%85%8C%EC%9D%B4%EB%B8%94-%EC%83%9D%EC%84%B1-%EC%98%88%EC%8B%9C