우편번호 DB 구성하기 (mysql)

세종특별자치시.txt

우편번호 DB 다운로드 : 인터넷 우체국 

( http://www.epost.go.kr/search/zipcode/cmzcd002k01.jsp )

- 개발중인 웹 어플리케이션에서 우편번호 검색이 필요해, 우편번호DB를 구축하다가 기억해야할 것들을 가볍게 정리해본다.

- 몇 달 전에 구축하고, 검색을 좀 더 원활하게 하기 위해 다시 구축하려니, 같은 문제를 다시 겪게 되어 정리함.

- 상단의 URL을 통해 우편번호 DB를 다운로드할 수 있음

우편번호 DB파일 바로가기를 클릭하면 데이터를 다운받을 수 있다

지역별 주소 DB를 다운로드 받으면 됨

- 인터넷 익스플로러로 접근하면 깨지고, 크롬으로 접근하면 잘되는 사이트네

- txt 파일로 제공

다운받은 txt 파일

- 다운로드 받아보니 용량이 엄청남..

- euc-kr 인가 ms949 인가 ? / 아무튼 ANSI 

- 기본 윈도우 에디터에서 다른이름으로 저장하면서 UTF-8로 저장해도 깨지는 한글 있음 ㅠㅠ

- 어쩔 수 없이 editplus 를 급 다운로드받아서 일괄 UTF-8로 변환함.

CREATE TABLE IF NOT EXISTS `zipcode` (

  `zipCode` VARCHAR(5) NULL ,

  `sido` VARCHAR(21) NULL COMMENT '',

  `sidoE` VARCHAR(17) NULL COMMENT '',

  `sigungu` VARCHAR(25) NULL COMMENT '',

  `sigunguE` VARCHAR(27) NULL COMMENT '',

  `eupmyun` VARCHAR(15) NULL COMMENT '',

  `eupmyunE` VARCHAR(20) NULL COMMENT '',

  `doroCode` VARCHAR(12) NULL COMMENT '',

  `doro` VARCHAR(33) NULL COMMENT '',

  `doroE` VARCHAR(46) NULL COMMENT '',

  `underground` CHAR(1) NULL COMMENT '',

  `buildingNo1` VARCHAR(5) NULL COMMENT '\'건물본번 + 건물부번\'',

  `buildingNo2` VARCHAR(4) NULL COMMENT '',

  `buildingManageNo` VARCHAR(25) NULL COMMENT '',

  `daryang` VARCHAR(1) NULL COMMENT '',

  `building` VARCHAR(60) NULL COMMENT '\'건물명\'',

  `dongCode` VARCHAR(10) NULL COMMENT '\'건물명\'',

  `dong` VARCHAR(16) NULL COMMENT '\'건물명\'',

  `ri` VARCHAR(18) NULL COMMENT '',

  `dongAdmin` VARCHAR(24) NULL COMMENT '',

  `san` VARCHAR(4) NULL COMMENT '',

  `zibun1` VARCHAR(4) NULL COMMENT '',

  `zibunSerial` VARCHAR(2) NULL COMMENT '',

  `zibun2` VARCHAR(4) NULL COMMENT '' )

ENGINE = InnoDB

- 대략 데이터베이스 구조는 위와 같음

- 해당 TXT 파일을 엑셀로 변환하려고 하면 파일이 너무 크거나, 내 시스템이 구려서 그런지 엑셀이 다운됨. ㅠㅠ

- 프로그램으로 돌리려면 엄청난 시간이 필요함..

- 다행이 mysql 에는 LOAD DATA 라는 명령어가 있음

mysql> LOAD DATA LOCAL INFILE '/home/iwan2fly/zip5/강원도.txt' INTO TABLE zipcode CHARACTER SET 'utf8' FIELDS TERMINATED BY '|' IGNORE 1 LINES;

Query OK, 329891 rows affected (14.22 sec)

Records: 329891  Deleted: 0  Skipped: 0  Warnings: 0

- 강원도가 14초 정도 걸림

- 가장 파일이 큰 경기도는 54초 정도 걸렸음

- 만약 프로그램 짜서 올리려고 했으면 ?  / 상상할 수 없음

- 17개 파일이므로, 웹서핑하면서 대충 1시간 안에 쌈싸먹을 수 있음

- 아래 쿼리는 위 CREATE 문에 검색용 주소와 보여주기용 주소 칼럼을 추가해서 컬럼별로 나뉘어진 주소를 하나로 모았음

- 대략 우체국에서 우편번호 검색하면 나오는 것과 같은 형태

- 만들어진 주소로 정렬하면 우체국과는 정렬 순서가 조금 다름

- 우체국은 buindingNo 를 숫자로 저장해서 정렬하는 데 쓰는 듯.

UPDATE zipcode

SET searchAddressDoro = concat(

eupmyun,

            doro,

            buildingNo1,

            CASE buildingNo2 WHEN 0 THEN '' 

ELSE CONCAT('-',buildingNo2) 

END,

            CASE CONCAT(dong,building) 

WHEN '' THEN ''

                ELSE

CASE dong WHEN '' THEN CONCAT('(',building,')')

                    ELSE

CASE building WHEN '' THEN CONCAT('(',dong,')') ELSE CONCAT( '(',dong,',',building,')') END

END

END

),

        searchAddressZibun = concat(

dong,

            zibun1,

            CASE zibun2 WHEN 0 THEN '' 

ELSE CONCAT('-',zibun2) 

END,

            CASE CONCAT(building) WHEN '' THEN '' 

ELSE CONCAT('(',building,')')

END

),

        addressDoro = concat(

CASE eupmyun WHEN '' THEN '' ELSE CONCAT(eupmyun, ' ') END,

            CASE doro WHEN '' THEN '' ELSE CONCAT(doro, ' ') END,

            buildingNo1,

            CASE buildingNo2 WHEN 0 THEN '' ELSE CONCAT('-',buildingNo2) END,

            CASE CONCAT(dong,building) 

WHEN '' THEN ''

                ELSE

CASE dong WHEN '' THEN CONCAT(' (',building,')')

                    ELSE

CASE building WHEN '' THEN CONCAT(' (',dong,')') ELSE CONCAT( ' (',dong,',',building,')') END

END

END

),

        addressZibun = concat(

            CASE dong WHEN '' THEN '' ELSE CONCAT(dong, ' ') END,

            zibun1,

            CASE zibun2 WHEN 0 THEN '' 

ELSE CONCAT('-',zibun2) 

END,

            CASE CONCAT(building) WHEN '' THEN '' 

ELSE CONCAT(' (',building,')')

END

)

- 이정도로 해서 인덱스 걸면 빠르지는 않지만 써먹을 수 있을 정도의 속도는 나옴..

- 아는 사람이 살짝 손봐주면 더 빨라질 듯.. - 난 몰라서 대충 ㅠㅠ -