목적
프로젝트를 진행하면서 주소DB를 활용할 일이 있었는데, 이 과정에서 하드코딩(?)의 어려움과 팀원이 같은 환경을 구성하는데 어려움을 겪을 수도 있다는 점이 문제점으로 다가왔습니다.
그래서 이 문제를 해결한 과정을 공유하기 위해 작성하였습니다.
개요
배달어플 프로젝트를 진행하다가 주소 정보가 필요해졌습니다.
- 유저가 카테고리를 선택하면 지정한 주소로 배달이 가능한 가게들을 보여줘야 합니다.
- 그런데 String 타입으로 주소를 저장하면 같은 이름의 구나 동이 존재하면 구분이 불가능하며 띄어쓰기 하나로 다른 주소가 될수 도 있습니다.
검색해본 결과 이미 여기서 주소관련 데이터베이스를 제공하고 있었습니다.
우선 저에게 필요한 것은 다음과 같습니다.
- 배달가능지역을 표시하기 위해 '동' 단위의 주소를 구별할 수 있어야하고
- 구체적인 배달지를 기록하기 위해 각 건물의 위치를 파악할 수 있어야 합니다.
여러 종류의 DB중, 네비게이션용 DB가 시+군/구+읍면/동을 조합한 ID, 도로명주소, 건물관리번호, 추후에 배달관련해서 필요할 수 있는 건물의 좌표까지 제공하고 있었습니다.
그래서 저는 네비게이션용 DB 를 사용하기로 하였습니다.
내비게이션용 DB | 도로명주소 DB 다운로드 | 도로명주소 개발자센터
https://www.juso.go.kr/addrlink/addressBuildDevNew.do?menu=navi
필요한 과정
위의 주소로 이동해서 최신월의 전체 자료를 받고 압축을 풀면 .txt 파일로 데이터가 제공되고 있습니다.
저는 지금 맥북프로를 사용하고 있는데 파일을 열어보려하면 인코딩 문제로 읽을 수가 없습니다.
인코딩
인코딩은 생각보다 간단합니다. iconv 라는 프로그램을 사용하면 되는데요, 파일의 인코딩을 변경시켜줍니다.
제가 사용하는 맥북 기준으로 따로 설치할 필요 없이 터미널에서 실행할 수 있습니다.
우선 inconv는 iconv -f "현재포맷" -t "바꿀포맷" "바꿀파일" > "새로운이름" 이렇게 사용합니다.
# 주어진 경로에 있는 match_build_로 시작하는 txt 데이터를 cp949 -> utf8 로 인코딩
find ./your/directory -type f -name 'myfile.txt' -exec iconv -f UTF-8 -t UTF-16 {} > {}.tmp \; -exec mv -f '{}.tmp' '{}' \;
간략하게 설명하자면
find로 주어진 경로에서 이름이 매칭되는 file만 검색합니다.
- 쉘을 실행시킵니다.
iconv로 CP949 인코딩을 UTF-8로 변경한 뒤 원래 파일명 뒤에 .tmp를 붙여 저장합니다."$0" > "$0" 처럼 같은 이름으로 저장하려고 하면 인코딩된 내용이 사라진 'sh' 란 이름의 파일이 생깁니다.- 그래서 다른 이름으로 저장한 뒤, 다시 원래 이름으로 복구시키는 과정이 필요합니다
- .tmp 를 붙인 파일들을 다시 원래 이름으로 복구시킵니다.
이제 인코딩이 끝났으니 데이터베이스에서 불러올 차례입니다.
데이터베이스에서 로딩
데이터베이스에 txt 파일을 로딩해오는 쿼리가 있습니다.
LOAD DATA LOCAL INFILE './your/directory/myfile.txt' -- txt 파일 1개 지정
INTO TABLE building_address -- 삽입할 테이블 지정
FIELDS TERMINATED BY '|' -- 구분자 지정. 주소 txt 데이터는 '|' 를 구분자로 구분되어 있다.
OPTIONALLY ENCLOSED BY '' -- 각 필드가 무엇으로 감싸져있냐인데, 아무것도 감싸고 있지 않다.
LINES TERMINATED BY '\n' -- 각 라인의 구분자.
(@변수이름, @변수이름2 ...)
SET (컬럼=@변수이름 ...)주의할 점은 @변수이름 에 해당하는 부분이다. 반드시 주소 데이터(txt파일) 의 순서와 맞춰주어야 합니다.
가령, 네비게이션 DB는 [주소관할읍면동코드], [시도명] ... 순으로 되어있으니, 변수도 반드시 그 순서를 지켜주어야한다. 아니면 엉뚱한 데이터가 들어가게 됩니다.
문제점
- 주소 DB의 컬럼은 수십개에다가, 저 쿼리는 하나의 파일만 불러오는 쿼리입니다.
- 일일이 복붙하고 파일이름만 바꿔줘도 되지만, 어디서 실수를 할지, 그럼 어디가 실수였는지 알기 어렵습니다.
- 팀원이 환경 구성에 어려움을 겪어 시간이 낭비될 수 있습니다.
그래서 저는 인코딩 + 전체 파일의 쿼리 생성을 해결해주는 스크립트를 작성했습니다.
#!/bin/sh
# 이 스크립트를 실행하면 juso.go.kr에서 받은 주소DB를 UTF8로 인코딩하고, 데이터베이스에 불러올 수 있는 쿼리를 생성한다.(실행위치의 query.txt 파일에 저장)
#############################################################################
######## '~/project/mysql/buildings' 경로는 실제 txt파일이 있는 경로로 수정해야 함######
#############################################################################
# 주어진 경로에 있는 match_build_로 시작하는 txt 데이터를 cp949 -> utf8 로 인코딩
find ~/project/mysql/buildings -type f -name 'match_build_*.txt' -exec sh -c 'iconv -f CP949 -t UTF-8 "$0" > "$0.tmp"' '{}' \; -exec mv -f '{}.tmp' '{}' \;
# 각 txt 파일을 불러오는 쿼리 생성
for entry in `find . -name match_build_\*.txt`; do
filename=`basename $entry`
echo "\
LOAD DATA LOCAL INFILE '~/project/mysql/buildings/${filename}' \
INTO TABLE building_address \
FIELDS TERMINATED BY '|' \
OPTIONALLY ENCLOSED BY '' \
LINES TERMINATED BY '\\\n' \
( @Address_Jurisdiction_Eup_Myon_Dong_Code, @city, @si_gun_gu, @Eup_Myon_Dong_Name, @road_name_code,
@road_name, @is_basement, @building_main_number, @building_sub_number, @postal_code,
@building_management_number, @building_name_for_si_gun_gu, @Classification_Of_Building_Use,
@administrative_dong_code, @administrative_dong_name,
@number_Of_Ground_Floor, @number_Of_Basement_Floor, @Classification_Apartment_House,
@Number_Of_Buildings, @detail_building_name,
@Building_Name_History, @Detail_Building_Name_History, @Is_Resident, @X_Pos_Building, @Y_Pos_Building,
@X_Pos_Entrance, @Y_Pos_Entrance, @City_Name_Eng, @Si_Gun_Gu_Name_Eng, @Eup_Myon_Dong_Name_Eng,
@Road_Name_Eng, @Classification_Eup_Myon_Dong, @move_reason_code
)
SET Number_Of_Buildings=@Number_Of_Buildings, city=@city, si_gun_gu=@si_gun_gu,
Address_Jurisdiction_Eup_Myon_Dong_Code=@Address_Jurisdiction_Eup_Myon_Dong_Code, Is_Resident=@Is_Resident,
X_Pos_Building=@X_Pos_Building, Y_Pos_Building=@Y_Pos_Building, X_Pos_Entrance=@X_Pos_Entrance,
road_name_code=@road_name_code, road_name=@road_name, is_basement=@is_basement,
building_main_number=@building_main_number, building_sub_number=@building_sub_number,
Y_Pos_Entrance=@Y_Pos_Entrance, detail_building_name=@detail_building_name,
building_management_number=@building_management_number, Eup_Myon_Dong_Name_Eng=@Eup_Myon_Dong_Name_Eng,
administrative_dong_code=@administrative_dong_code, administrative_dong_name=@administrative_dong_name,
postal_code=@postal_code, Road_Name_Eng=@Road_Name_Eng, move_reason_code=@move_reason_code,
City_Name_Eng=@City_Name_Eng, Si_Gun_Gu_Name_Eng=@Si_Gun_Gu_Name_Eng,
building_name_for_si_gun_gu=@building_name_for_si_gun_gu, Classification_Apartment_House=@Classification_Apartment_House,
Classification_Eup_Myon_Dong=@Classification_Eup_Myon_Dong, Classification_Of_Building_Use=@Classification_Of_Building_Use,
Building_Name_History=@Building_Name_History,Detail_Building_Name_History=@Detail_Building_Name_History,
number_Of_Ground_Floor=@number_Of_Ground_Floor, number_Of_Basement_Floor=@number_Of_Basement_Floor,
Eup_Myon_Dong_Name=@Eup_Myon_Dong_Name;
" >> query.txt;
done
vi 나 다른 에디터를 이용해서 .sh 파일을 하나 생성하고 복붙하셔서 사용하시면 됩니다.
이제 여기부터는 제 프로젝트와 관련된 부분입니다.
저는 배달가능지역을 FK로 사용하고 싶었지만, 제공되는 DB는 건물관리번호를 PK로 사용하고 있어서 불가능했습니다.
그래서 하나의 테이블을
- [시-군/구-읍면/동] 까지의 정보만 가지는 테이블
- 건물정보를 가지는 테이블
2개로 분리했습니다.
( 일부 불필요한 컬럼은 제외했습니다)
-- 추가한 주소들 중 도/시군구/동 관련 부분은 분리
insert into city_to_dong
select address_jurisdiction_eup_myon_dong_code,
city,
city_name_eng,
classification_eup_myon_dong,
eup_myon_dong_name,
eup_myon_dong_name_eng,
si_gun_gu,
si_gun_gu_name_eng
from (select distinct address_jurisdiction_eup_myon_dong_code,
city,
city_name_eng,
classification_eup_myon_dong,
eup_myon_dong_name,
eup_myon_dong_name_eng,
si_gun_gu,
si_gun_gu_name_eng
from building_address
) DT;
-- 추가한 주소들 중 건물관련 정보만 분리
insert into building_info
select building_management_number,
building_name_for_si_gun_gu,
building_main_number,
building_sub_number,
classification_apartment_house,
detail_building_name,
is_basement,
number_of_basement_floor,
number_of_buildings,
number_of_ground_floor,
postal_code,
road_name,
road_name_code,
road_name_eng,
x_pos_building,
x_pos_entrance,
y_pos_building,
y_pos_entrance,
address_jurisdiction_eup_myon_dong_code
from building_address;
이로써 주소관련 기능을 다룰 준비가 되었습니다.