본문 바로가기
Project/DelFood

[이슈 #4] 주소를 외래키로 관리하도록 변경(공공데이터 사용)

by EricJeong 2019. 10. 27.

기존 방식의 문제점

기존에 주소를 입력하는 방식은 '주소', '상세주소' 컬럼을 사용하여 사용자의 주소를 직접 입력받는 식이였습니다. 당연히 이 주소들은 공통적인 포맷으로 관리하기도 어렵고, 주소 체계를 변경한다면 모든 테이블의 주소 체계를 전부 변경해야 했습니다.

 

간단하게, 변경 전 회원 테이블은 다음과 같이 생겼습니다.

MEMBER Table Schema

 

위 방식과 같이 주소를 직접 입력하도록 한다면, 주소를 이용한 좌표 검색, 입력한 주소 별 정렬 등 주소 관리가 어려울 것이라 생각됩니다.

 

또한 도로명주소와 지번 주소의 관리도 어렵게 되어 테이블의 변경이 필요하다고 판단하였습니다.

 

 

1. 주소 정보를 공공데이터에서 가져오기

'공공데이터포털'에서는 도로명주소, 지번주소, 좌표 등의 데이터를 가지고 있는 '건물정보'DB를 제공하고있습니다.

DB 데이터만 약 1GB에 달하는 어마어마한 양을 가지고 있습니다. 우선 데이터 사용 가이드에 적힌 대로 테이블을 설계하였습니다.

 

건물정보 Table Schema

 

 

 

위 테이블은 음식 배달에 필요한 위치정보, 건물명, 출입구 좌표, 도로명주소, 지번주소 등 필요한 정보를 대부분 가지고 있습니다. PK인 '건물관리번호'를 address컬럼 대신 FK로 부여한다면 주소를 하나의 테이블로 관리할 수 있을 것입니다.

 

주소 데이터를 DB에 넣기 위한 DDL

CREATE TABLE 건물정보
(
    `주소관할읍면동코드`  VARCHAR(10)      NULL        COMMENT '주소관할읍면동코드', 
    `시도명`        VARCHAR(40)      NULL        COMMENT '시도명', 
    `시군구명`       VARCHAR(40)      NULL        COMMENT '시군구명', 
    `읍면동명`       VARCHAR(40)      NULL        COMMENT '읍면동명', 
    `도로명코드`      VARCHAR(12)      NULL        COMMENT '도로명코드', 
    `도로명`        VARCHAR(80)      NULL        COMMENT '도로명', 
    `지하여부`       VARCHAR(1)       NULL        COMMENT '지하여부', 
    `건물본번`       INT(5)           NULL        COMMENT '건물본번', 
    `건물부번`       INT(5)           NULL        COMMENT '건물부번', 
    `우편번호`       VARCHAR(5)       NULL        COMMENT '우편번호', 
    `건물관리번호`     VARCHAR(25)      NOT NULL    COMMENT '건물관리번호', 
    `시군구용건물명`    VARCHAR(40)      NULL        COMMENT '시군구용건물명', 
    `건축물용도분류`    VARCHAR(100)     NULL        COMMENT '건축물용도분류', 
    `행정동코드`      VARCHAR(10)      NULL        COMMENT '행정동코드', 
    `행정동명`       VARCHAR(40)      NULL        COMMENT '행정동명', 
    `지상층수`       INT(3)           NULL        COMMENT '지상층수', 
    `지하층수`       INT(3)           NULL        COMMENT '지하층수', 
    `공동주택구분`     VARCHAR(1)       NULL        COMMENT '공동주택구분', 
    `건물수`        INT(10)          NULL        COMMENT '건물수', 
    `상세건물명`      VARCHAR(100)     NULL        COMMENT '상세건물명', 
    `건물명변경이력`    VARCHAR(1000)    NULL        COMMENT '건물명변경이력', 
    `상세건물명변경이력`  VARCHAR(1000)    NULL        COMMENT '상세건물명변경이력', 
    `거주여부`       VARCHAR(1)       NULL        COMMENT '거주여부', 
    `건물중심점_x좌표`  DOUBLE(15,6)     NULL        COMMENT '건물중심점_x좌표', 
    `건물중심점_y좌표`  DOUBLE(15,6)     NULL        COMMENT '건물중심점_y좌표', 
    `출입구_x좌표`    DOUBLE(15,6)     NULL        COMMENT '출입구_x좌표', 
    `출입구_y좌표`    DOUBLE(15,6)     NULL        COMMENT '출입구_y좌표', 
    `시도명_영문`     VARCHAR(40)      NULL        COMMENT '시도명_영문', 
    `시군구명_영문`    VARCHAR(40)      NULL        COMMENT '시군구명_영문', 
    `읍면동명_영문`    VARCHAR(40)      NULL        COMMENT '읍면동명_영문', 
    `도로명_영문`     VARCHAR(80)      NULL        COMMENT '도로명_영문', 
    `읍면동구분`      VARCHAR(1)       NULL        COMMENT '읍면동구분', 
    `이동사유코드`     VARCHAR(2)       NULL        COMMENT '이동사유코드', 
    PRIMARY KEY (건물관리번호)
);

ALTER TABLE 건물정보 COMMENT '건물정보';

 

검색을 위한 index는 추후에 주소 검색 기능을 만들며 추가하고자 합니다.

테이블을 만들었으니 PHPMyAdmin에서 데이터를 넣어줍니다.

 

 

 

주소 데이터 삽입

DB는 Cafe24 호스팅을 이용하여 MariaDB를 사용하고, 관리 툴로 PHPMyAdmin을 사용하고 있습니다. 데이터를 넣기 전 구조를 살펴보았습니다.

 

4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|23|6|51196|4812110100102560003015609||주택|4812151000|의창동|2|0|0|1||||1|1101909.651325|1697227.507274|1101904.866558|1697224.026133|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|23|10|51196|4812110100102550000017505||주택|4812151000|의창동|2|0|0|1||||1|1101899.76064|1697241.536802|1101898.696823|1697234.586881|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|25|0|51196|4812110100102600000016144|의안동주민센터|업무시설|4812151000|의창동|1|0|0|2||||0|1101947.274403|1697230.855377|1101936.406344|1697207.151468|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|25|0|51196|4812110100102600000017487|의안동주민센터|업무시설|4812151000|의창동|2|1|0|2||||0|1101932.818265|1697210.975234|1101936.406344|1697207.151468|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|27|0|51196|4812110100102370000017508|동창원농협|업무시설|4812151000|의창동|3|1|0|3||||0|1101967.688393|1697211.678518|1101966.994309|1697201.773352|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|27|0|51196|4812110100102370000017509|동창원농협|공장/창고시설|4812151000|의창동|1|0|0|3||||0|1101968.825908|1697240.276272|1101966.994309|1697201.773352|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|27|0|51196|4812110100102370000017829|동창원농협|공장/창고시설|4812151000|의창동|2|0|0|3||||0|1101961.868189|1697237.462568|1101966.994309|1697201.773352|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|27|3|51196|4812110100102590000017485||주택|4812151000|의창동|2|0|0|1||||1|1101949.483998|1697217.508135|1101951.944417|1697212.514674|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|27|11|51196|4812110100102570004017512||유통시설|4812151000|의창동|1|0|0|1||||0|1101930.798898|1697242.727293|1101935.596219|1697243.906678|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|29|0|51196|4812110100102350003017482||유통시설|4812151000|의창동|2|0|0|1||||1|1101983.706377|1697214.525391|1101987.350541|1697212.289564|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|35|0|51198|4812110100101100000017474||유통시설|4812151000|의창동|1|0|0|1||||0|1102037.424063|1697200.90458|1102037.031726|1697199.669696|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|35|1|51198|4812110100101060000017476||의료시설|4812151000|의창동|4|1|0|1||||1|1102050.325008|1697202.132312|1102045.925636|1697200.166212|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|0|51198|4812110100101050002017475||유통시설|4812151000|의창동|2|0|0|1||||0|1102063.812431|1697201.971818|1102057.645097|1697200.791467|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|3|51198|4812110100101040002017479||주택|4812151000|의창동|1|0|0|2||||0|1102057.01971|1697210.642656|1102070.932038|1697208.106261|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|3|51198|4812110100101040002017480||주택|4812151000|의창동|1|0|0|2||||0|1102065.272416|1697212.1198|1102070.932038|1697208.106261|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|4|51198|4812110100100610000017477||유통시설|4812151000|의창동|2|0|0|2||||0|1102078.086464|1697207.971505|1102073.942952|1697211.975144|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|4|51198|4812110100100610000017478||주택|4812151000|의창동|1|0|0|2||||0|1102077.113091|1697214.98065|1102073.942952|1697211.975144|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|5|51198|4812110100101040001017488||주택|4812151000|의창동|1|0|0|2||||1|1102057.825607|1697217.997189|1102063.030493|1697223.43196|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|5|51198|4812110100101040001017489||주택|4812151000|의창동|1|0|0|2||||1|1102061.216705|1697226.013735|1102063.030493|1697223.43196|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|7|51198|4812110100101020000017501||주택|4812151000|의창동|1|0|0|3||||1|1102061.460504|1697231.593039|1102060.935282|1697235.628089|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|7|51198|4812110100101020000017502||주택|4812151000|의창동|1|0|0|3||||1|1102058.013693|1697237.895264|1102060.935282|1697235.628089|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|
4812110100|경상남도|창원시 의창구|북동|481213327042|읍성로|0|37|7|51198|4812110100101020000017503||주택|4812151000|의창동|1|0|0|3||||1|1102057.403923|1697245.956671|1102060.935282|1697235.628089|Gyeongsangnam-do|Uichang-gu, Changwon-si|Buk-dong|Eupseong-ro|1|

데이터의 구분은 '|'을 이용하여 제공되고 있습니다. Data encoding은  'ANSI'로 설정되어 있어서 DB 기본 설정인 'UTF-8'로 변경하여 저장했습니다.

 

 

 

관련 설정을 적용해 준 후 DB에 데이터를 하나씩 파일로 삽입하였습니다.

 

 

데이터 파일 쪼개기

DB에 파일을 이용하여 데이터를 넣으려 하니 파일이 너무 커서 DB에 한번에 넣을 수 없었습니다. PHPMyAdmin에서 받을 수 있는 파일의 최대 크기는 21MB, 그에 맞게 파일을 쪼개기 위해 'Text File Cleaver'를 사용하였습니다. 경기도 파일을 쪼개니 25개의 파일로 분해되었습니다.

 

 

 

DB에 데이터 파일을 업로드하였습니다. 파일 크기를 작게 쪼개주니 문제 없이 INSERT가 이루어졌습니다.

 

2. 변경한 주소 정보에 맞게 테이블 스키마 수정하기

 

주소 정보를 입력한 '건물정보'테이블을 참조하도록 테이블 스키마를 변경합니다.

MEMBER 테이블은 다음과 같이 변화합니다.

 

MEMBER Table Schema (변경 후)

 

 

다른 테이블 또한 위와 같은 방식으로 변경하였습니다.

 

ORDERS Table Schema

 

 

저작자표시

'Project > DelFood' 카테고리의 다른 글

[이슈 #6] 주소 데이터의 빠른 조회를 위해 인덱스 설정하기  (2) 2019.11.01
[이슈 #5] 반복되는 로그인 체크 로직을 AOP로 리팩토링하기  (4) 2019.11.01
[이슈 #3] 아이디 중복 체크시 Http Status값을 어떻게 설정해야 할까?  (2) 2019.10.06
[이슈 #2] 고객의 주문 내역을 조회할 때 테이블 다수 조인 이슈 - 2  (1) 2019.09.23
[이슈 #1] 고객의 주문 내역을 조회할 때 테이블 다수 조인 이슈  (0) 2019.09.23

관련글

댓글

티스토리툴바