대한민국 우편번호별 쉐이프 구해서 Shape 합성 및 DB에 json 형태로 저장하기

서비스를 목적으로 웹 페이지에 지도를 그려야할 필요가 있어서 여러 가지 방향을 찾아보았다. 아무래도 개발적인 부분에서 가장 흥미를 가진 부분은 지도를 그리기 위한 Shape 정보(Polygon/Geometry)를 공공기관에서 제공하는 api를 통해 호출해올 것인지, 아니면 DB에 직접 저장해서 사용할 것인지였다.
(참고로 이번 편에서는 후자를 기준으로 포스팅을 하려고 한다.)
공공기관 지도 api를 사용하면 일단 간단하지만 장애나 점검 등에 대해서 그 기관에 의존성을 둘 수 밖에 없고 불러온 좌표 데이터를 그대로 사용해야 한다는 단점이 있다. 불러온 좌표 데이터를 합성하거나 수정할 경우 그때그때 화면에서 해줘야하기 때문에 아무래도 속도에 이슈가 있기 때문이다. 따라서 이러한 단점을 제거하기 위해 좌표 데이터를 미리 DB에 저장해놓고 이를 미리 합성해두는 방법을 사용할 수 있다.

크게 작업 순서는 다음과 같다.
1. 폴리곤을 정보를 구하고
2. MSSQL에 올리고
3. MSSQL내에서 폴리곤 합성/수정하기


먼저 우편번호별 폴리곤은 행안부에서 제공하고 있다.
http://www.juso.go.kr/addrlink/addressBuildDevNew.do?menu=bsin

우편번호로 활용되고 있는 전국 기초구역번호 정보를 전자지도(SHAPE)형식으로 제공한다고 되어있고 1년에 한번 업데이트를 하고 있으므로 데이터 시점에 대한 보정은 따로 고려해주어야 한다.

데이터의 형식은 다음과 같다. 활용가이드를 다운받으면 볼 수 있는 문서를 캡쳐했다.

아무래도 여기에서 필요한 것은 1,2,3,4,5 일 것이다. 5번 BAS_ID가 우편번호이기 때문이다.

201712기초구역DB_전체분.zip를 다운받아보면 약 70메가 정도 되는데 압축을 풀어보면 다음과 같이 17개로 나뉘어져 있고 각각의 폴더에는 .dbf, .shp, .shx 파일이 존재한다.

이 파일을 이제 Qgis라는 프로그램을 활용해서 열어보자.

서울특별시 파일만 열었으며 속성 데이터를 보면 다음과 같이 공간에 대한 정보를 볼 수 있다.
참고로 한글이 깨지는 경우 속성->소스->데이터소스 인코딩을 System으로 바꿔주면 정상적으로 볼 수 있다.

이제 이 벡터 정보를 Geojson 형태로 저장한다.

이렇게 저장한 11.geojson 파일을 열어보면 다음과 같은 구조로 되어있다.

우편번호별 폴리곤을 구하는 것이 목적이나 시도와 시군구까지 제공해주므로 이 데이터도 들고가자. 따라서 CTP_KOR_NM(시도명), SIG_KOR_NM(시군구명), BAS_ID(우편번호), geometry(타입과 좌표)를 사용하면 될 것이며 이제 json 형식의 파일을 db에서 제공해주는 기능을 활용해서 직접 엑세스를 하거나 그 방법을 사용할 수 없으면 직접 코드를 짜서 업로드하기 편하게 파싱해주도록 하자.

코드는 다음과 같다.
전체 코드는 드라이브에 올려놓았다.
드라이브 주소 :
https://drive.google.com/open?id=11UXBF06V3Q1_sZiDVTr8mGfDBZO5t-Lq

실행시 no module named 'shapely' 이라는 메시지가 나와서 직접 설치를 해주었으며
https://pypi.org/project/Shapely/에서 직접 모듈 whl파일을 다운받아서 설치하였다.
https://www.lfd.uci.edu/~gohlke/pythonlibs/#shapely

필자는 파이썬 3.7 버전이며 Shapely‑1.6.4.post1‑cp37‑cp37m‑win_amd64.whl를 설치하였고 드라이브에 올려놓았다.

코드를 돌려보면 서울만 실행했을 때 5667개의 우편번호가 존재했다.

아래는 결과 파일이며 이 파일을 이제 db에 업로드 할 것이다.

DB에 업로드 할 때 유의할 점은 COORD 컬럼을 텍스트 스트림(VARCHAR MAX)로 넣어주는 것이 중요하다.

최종 업로드 하면 위와 같은 화면이 된다.

테스트용으로 한번 폴리곤을 합성해보자.
SELECT  CODE
          , CTP_KOR_NM
          , SIG_KOR_NM
  , geometry::UnionAggregate(COORD).ToString() AS POLYGON
  , geometry::UnionAggregate(COORD) AS WKT
FROM TESTDB..KOREA_GEOMETRY
GROUP BY CODE
         , CTP_KOR_NM
         , SIG_KOR_NM

코드, 시도, 시군구로 GROUP BY하여 폴리곤을 합성하고 geometry::UnionAggregate을 사용하면 ssms에서 바로 합성 결과를 다음처럼 확인할 수 있다.

합성 결과는 총 25개였다. POLYGON 컬럼이 최종적으로 합성된 결과이다.

서울특별시 기준으로 group by 결과가 25개이기 때문에 지도 객체도 25개가 출력되었고 폴리곤끼리 합성이다보니 폴리곤 내에 선 등 깔끔하지 못한 것은 mssql 내에서 제공하는 reduce나 buffer 함수를 사용하면 훨씬 깔끔하게 줄어들 수 있다.

필자도 여러가지 시도를 해보았고 지도를 최대한 간단하게(?)하기 위해 reduce 값을 높게 주면 생각보다 시간이 오래걸려서 적당한 타협점을 찾아야한다.

마지막으로 GEOMETRY 형식의 데이터를 화면에서 사용하기 쉽도록 JSON 형태로 저장해놓기로 하자.

GEOMETRY를 JSON으로 CONVERT하는 FUNCTION은 Hasan Savran BLOG를 참조하였고 드라이브에 저장해놓았다.

- hasan savran 님 감사합니다.


최종 결과는 다음과 같다.

지금은 의미없이(?) 시도, 시군구 기준으로 GROUP BY를 했지만 사실은 원하는 우편번호 끼리 지역을 묶어서 사용해야 의미있는 결과가 나올 것이다. 대한민국 배송은 우편번호 기준으로 이루어지기 때문이다.


이제 이 폴리곤 정보를 SGIS등에서 제공하는 api나 솔루션(highcharts)을 활용하여 그릴 수 있고 폴리곤 데이터를 직접 들고있기 때문에 api 제공 기관에 종속성이 없을 뿐만 아니라 속도 측면에서도 훨씬 우월한 성능을 낼 수 있다.

SSMS에서 프로시저를 실행했을 때 수행 결과가 부족하다 - SET ROWCOUNT 설정으로 인한 실수

시나리오
원격지 서버의 프로시저를 사용자 로컬 PC에서 수동으로 돌리고자 한다. 프로시저 결과로 대략 수백~수억 row가 insert될 것이라고 생각하는데 자꾸 500row만 insert가 되는 이상한 상황이 연출되었다. 아무리 프로시저 내 로직을 검증해봐도 이상이 없었고 유독 프로시저만 수행하면 결과가 부족한 현상을 경험하였다. SSMS의 버그인지, 원래 이렇게 유도를 한 것인지는 잘 모르겠지만 현상을 확인한 후 주의를 하고 있다.

원인

SSMS의 도구->옵션->쿼리실행->SQL Server ->일반 탭에서 SET ROWCOUNT를 500으로 맞춰놓았다. default는 0이며 set rowcount 0은 쿼리 결과를 제한 없이 모두 출력한다는 것이다. 여기에서 500으로 제한을 해둔 탓에 로컬 pc에서 원격지 프로시저를 수행하여도 로컬 pc의 set rowcount 500 설정에 막혀 결국 테이블에도 500 row만 insert되는 상황이었다. 결국 이를 풀어줌으로써 해결하였다.


증상재현

증상 재현을 위해 먼저 위와 5000 ROW가 들어있는 TEST 테이블을 생성하였다.

그리고 똑같은 스키마의 TBL_TEST_2와 프로시저를 생성하였다.

그리고 프로시저를 수행해보았다.
TRUNCATE TABLE TBL_TEST_2

EXEC USPS_TEST

SELECT COUNT(*)
FROM TBL_TEST_2

SSMS의 도구->옵션->쿼리실행->SQL Server ->일반 탭에서 SET ROWCOUNT를 500으로 맞춰놓았기 때문에 쿼리 결과가 500 row밖에 되지 않는 현상이 재현되었다.

이를 해결하기 위해 SSMS에서 SET ROWCOUTN를 0으로 풀어도 바로 해결이 되지 않고 세션을 새로 열어야 한다. 세션을 새로 열지 않으면 명시적으로 SET ROWCOUNT 0을 해당 세션창에서 한번수행해주어야 한다.



느낀점
일반적으로 FULL SCAN을 방지하기 위해 SSMS의 설정에서 최대 행 갯수를 제한해놓을 것이다. 하지만 이 설정으로 인해 수동으로 서버의 프로시저를 수행할 경우 원하는 결과가 나오지 않을 수 있다. 따라서 이런 상황이 발생할 수 있다는 것을 인지하고 있어야겠다.

SSIS에서 MSSQL Polybase로 Hadoop 데이터를 외부 테이블 생성할 때 팁

시나리오
하둡 데이터를 MSSQL로 매일 특정 시간에 가져오는 배치를 짠다고 가정한다. 이 경우 MSSQL에서는 해당 테이블을 polybase로 External Table을 만들게 된다.

예를 들어 하둡의 특정 경로 /app/analy/ods/visit/20181101처럼 끝에 날짜 포맷만 바꾸면서 데이터를 저장하고 이 데이터를 mssql로 가져오는 경우이다. 하나의 프로세스(배치)로 이루어지면 떨궈질때까지 기다리고 다 떨궈지면 가져오면 되지만 이 경우에는 하둡 데이터를 저장하는 것, MSSQL로 가져오는 것이 분리되어 있는 경우라고 가정한다.

우리는 하둡 경로를 알고 있을 때 MSSQL에서 다음처럼 데이터를 가져올 수 있다.
CREATE EXTERNAL TABLE ~
LOCATION ~
그러나 이 처럼 단순하게 해결되지 않는 경우가 있다.


문제점
특정 상황이 발생하여 하둡에 데이터가 생성되지 못했을 경우 CREATE 문을 실행하면 PATH가 존재하지 않는다는 에러가 난다.


해결방법
우리는 주기적으로 하둡 경로를 반복체크하면서 데이터가 생성된 이후에 MSSQL로 가져오면 된다.

하둡 명령어 중에 fs -test -s /app/analy/ods/visit/20181101 (-e는 path, -d는 directory, -f는 file 등 여러 옵션이 있다) 를 먼저 날려보고 결과로 0을 리턴하면 path가 존재한다는 의미이다.


즉 정리하면 다음과 같다.

1. 하둡 데이터를 떨굴 때 원하는 것을 다 떨구면 해당 경로에 success라는 크기가 0인 파일이나 폴더를 하나 더 떨구게 한다. (혹은 데이터 생성을 시작 할 때 start라는 폴더나 파일을 생성하게 하고 완료되면 success를 생성하게 해도 된다.)
2. 배치에서 데이터를 가져올 때 루프를 만들고 안에서 테이블을 생성하기 전에 fs -test -s /app/analy/ods/visit/20181101/success 를 날리도록 한다.
3. 리턴값이 0이면 success가 데이터가 존재하는 것이기 때문에 루프를 탈출하고 아니면 주기적으로 위의 명령어를 하둡에 찔러보게 한다.
4. 루프를 탈출하면 MSSQL에서 CREATE EXTERNAL TABLE 문 실행한다.

이런 방법을 데이터가 생성되기전까지 기다리는 것이기 때문에 보다 안전할 수 있다.

MSSQL RAISEERROR VS THROW 팁

시나리오
MSSQL의 가장 강력한 힘은 T-SQL이다. 저장프로시저가 강력한 배치 수단으로 쓰일 수 있기 때문인데 프로그래밍 코드를 작성하는 것 처럼 프로시저 안에서 모든 걸 처리할 수 있다. 그리고 요즘 같이 AI 흐름을 따라가기 위해서 인지 MS에서도 SQL Server 2017 버전부터 프로시저 내에서 Python을 지원한다.
저장 프로시저로 배치를 짜는 경우 의도적으로 에러를 내거나 에러가 났을 때 디버깅을 할 필요가 있다. 이때 유용하게 사용할 수 있는 것이 RAISEERROR와 THROW이다.
잘만 사용하면 배치에서 디버깅으로 정말 유용하게 사용할 수 있다.
필자는 Raiserror를 프로시저의 특정 구간에 걸어놓고 배치 시 디버깅을 할때 사용하거나 후속 배치를 일부러 돌리지 않기 위해 의도적으로 에러를 내는 경우에 종종 사용한다.


RAISEERROR VS THROW
1. Raiseerror는 msg_id가 RAISERROR에 전달되는 경우 ID가 sys.messages에 정의되어있어야 하지만 THROW는 error_number 매개 변수가 sys.messages에 정의되어있지 않아도 된다.
2. RAISEERROR는 msg_str 매개 변수는 printf 서식 지정 스타일을 포함할 수 있지만 THROW는 아니다.
3. RAISEEROR에서 severity 매개 변수는 예외의 심각도를 지정하지만 THROW에는 그런 파라메터가 없고 예외 심각도는 항상 16으로 고정되어있다.


사용방법
1. RAISEERROR
계획된 오류가 있는 테스트용 프로시저를 생성한다. (duplication error를 의도했다.)

그리고 프로시저를 실행해본다. 실무에서는 배치 프로시저라고 생각을 하면 되겠다.

2. THROW
마찬가지로 계획된 오류가 있는 테스트용 프로시저를 생성한다. 
(똑같이 duplication error를 의도했다.)

프로시저를 수행해본다.

3. SQL Server Log에 RAISEERROR가 남는 것을 볼 수 있다.

MSSQL 컬럼스토어 인덱스 팁 (180GB 테이블을 컬럼스토어 인덱스로 변경한 결과)

MSSQL에는 컬럼스토어라는 특수한 저장 방법이 존재한다.

잘 사용하면 저장 공간과 속도 측면에서 엄청난 성능을 발휘할 수 있으며 적용 전에 컬럼스토어란 무엇인가에 대해서 알아보자.

컬럼스토어란?

MSSQL은 8KB 사이즈 페이지 단위로 데이터를 저장하고 읽는다. (페이지는 오라클의 블록하고 같은 개념이다.) 이 페이지 안에 테이블의 행이 저장되어 있는 구조이다.

예를들어 어떤 테이블에 컬럼이 많아서 한 ROW의 사이즈가 1KB이다.

그럼 조회결과가 100만건일 경우에는 8KB * 100만 = 약 7.6GB를 읽어야한다.

컬럼 스토어란 컬럼별로 데이터를 저장하여 컬럼들끼리 데이터를 모아놓는 것이다. 

쉽게 생각해서 ROW 스토어가 수평적으로 저장하면 컬럼스토어는 수직적으로 저장된다고 보면 된다.

이는 열이 동일한 타입이기 때문에 유사한 값을 저장하므로 압축 비율이 높고 일반적으로 테이블을 조회할 때 모든 컬럼을 SELECT 하지 않기때문에 실제로는 더 큰 효과를 낼 수 있다.

 

다른 DBMS에서 컬럼스토어를 흉내내려면 각 컬럼을 경우에 수에 따라 쪼개놓고 모든 컬럼에 인덱스를 만든다고 생각하면 될 것이다.

특이사항

1. 제약사항은 테이블당 1개만 생성이 가능하다. 

(사실 각 컬럼에 인덱스를 만드는 것과 같으므로 1개만 있으면 된다.)

2. 업데이트가 발생할 경우 데이터를 메모리에 올려놓고 압축을 풀고 업데이트를 하고 다시 압축해서 디스크에 쓰므로 비용 발생이 크다.

3. 컬럼이 1024개가 최대

4. ALTER INDEX, REORGRANIZE 불가하므로 삭제 후에 다시 생성해야됨

5. 메모리 제약(8MB X 컬럼 수 X DOP )

등이 있다.

컬럼스토어 권장 상황

• 1. 테이블 - ROW가 100만건 이상(압축률 보장)
• 2. 쿼리 - 집계형, GROUP BY, full scan이 자주 발생하는 테이블
• 3. 트랜잭션 - OLAP 환경. 데이터가 자주 변경되지 않는 테이블

로우스토어 권장 상황

• 1. 테이블 - 적은 ROW
• 2. 쿼리 - 집계형이 아닌 일반적 필터링 쿼리가 주로 사용
• 3. 트랜잭션 - OLTP 환경. 데이터가 자주 삽입, 삭제, 업데이트가 발생하는 테이블

정리하면 일반적으로 OLTP보다는 집계성 OLAP 환경에 더 적합하며 적절하게 사용하면 압축률이 좋아서 I/O 성능이 매우 향상된다.

사용 결과

실제로 180GB 테이블을 컬럼스토어 인덱스로 변경한 결과이다.

테이블 A와 테이블 B의 스키마에 컬럼을 추가할 일이 생겨서 스키마를 바꾸기로 했다.
크게 바뀐 것이 있다면 아무래도 테이블 특성 상 기간별 조회가 이루어지고 추후에 데이터를 지울 계획이라서 파티션을 줬고, 용량이나 성능을 향상시키기 위해 컬럼 스토어 인덱스로 변경하였다.

최근 기준으로 테이블 A는 하루 평균 150만건 정도 데이터가 쌓인다.
최근 테이블 B는 하루 평균 250만 건 정도 데이터가 쌓인다.

변경 전 테이블 A 스키마는 다음과 같다.

변경 전 테이블 B 스키마는 다음과 같다.

A 테이블을 다음과 같이 변경하였다. (10일파티션, 컬럼스토어 적용)

B 테이블을 다음과 같이 변경하였다. (10일파티션, 컬럼스토어 적용)

바뀐 점은 컬럼 하나를 추가하고(VARCHAR(10)) 10일 파티션을 주었으며 컬럼스토어 인덱스를 생성하였다.

변경 결과를 보자.

테이블 A와 테이블 B의 모습이고 인덱스 조각화가 없진 않을테니 인덱스 리빌드를 하면 차지하는 용량이 더 줄어들텐데 워낙 ROW수가 많다보니 리빌드 할 엄두가 안났고, 그냥 바로 새로운 테이블에 데이터를 붓기 시작했다.

다음은 변경 전 A, B 테이블 사이즈이다.

테이블 A는 ROW수가 8억건에 순수 데이터 사이즈만 52GB 이상이다.

테이블 B는 ROW수가 13억8천건에 순수 데이터 사이즈만 128GB 이상이다.

두 테이블 모두 약 2년 반정도의 데이터를 갖고 있었다.

테이블 A에서 테이블 A_NEW 에 붓는 작업은 약 2시간이 소요되었다.

테이블 B에서 테이블 B_NEW 에 붓는 작업은 약 4시간이 소요되었다.

결과는 다음과 같았다.
1. A테이블 52GB -> 22GB
2. B테이블 128GB -> 25GB

특히 테이블 B의 사이즈가 크게 작아진 것을 확인할 수 있었다.

아무래도 DISK에서 읽어야할 SIZE가 훨씬 줄어들었다보니 I/O에서 큰 성능을 보일 것으로 기대되며 데이터가 자주 변경되지 않는 집계성 테이블이라서 이런 결정을 할 수 있었다.

MSSQL 어셈블리 만들기 - How to Implementing CLR Object DLL

시나리오
1. SQL Server에서 PDW(Parallel Data Warehouse)로 동적 쿼리를 만들어서 날리려고 하는데 8000자 이상의 쿼리문을 수행해야 한다.
2. A서버의 SQL Server (가정 주소:321.321.1.321,9999)에서 B서버의 PDW (가정 주소 : 123.123.1.123,8888)로 쿼리를 날려 SELECT 결과를 받아오고자 한다.
3. 특정 사용자에게만 DB 접근권한을 주고 계정을 분리하고자 한다.

해결방법
CLR Object DLL를 만들어서 ASSEMBLY로 등록하고 CLR을 통해 쿼리를 수행한다.
CLR을 통해 DB 접근 권한을 분리하고 8000자 이상의 동적쿼리를 쿼리를 수행할 수 있다.

  고려사항
얼마 전 MSSQL이 설치된 윈도우 서버에서 메모리가 부족한 현상을 겪었고 덕분에 수 번의 장애를 겪었다. 윈도우 서버의 메모리가 약 100기가 이상이 되지만 보통의 경우에는 DB에 대부분의 메모리를 할당하고 최소한의 메모리를 OS에 할당하겠지만 필자의 경우에는 MSSQL에서 PDW로 CLR을 호출하는 경우 딱 그 시점에 윈도우 서버의 메모리가 확 튀며 장애 현상을 겪었다. 이런 경우에는 MS 엔지니어가 메모리를 DB에 적당히 할당한 후 안전하다고 판단되는 수준까지 점진적으로 올릴 필요가 있다고 한다.


방법

1. DLL을 만들기 위해 Visual Studio에서 Visual C# CLR Database Project를 만든다.

(파일 -> 새 프로젝트 -> SQL Server -> SQL Server 데이터베이스 프로젝트 생성)

2. 솔루션 탐색기에서 추가 -> 새항목 -> SQL CLR C# -> SQL CLR C# 저장 프로시저 생성한다.

3. 쿼리를 날릴 때 SelectPdwByLogin 함수를 호출할 것이고 이 함수 내부에서 분리시키고자 하는 login_name을 "exuser"를 구분했다.

4. 빌드를 하고 DLL(CLR_PDW.dll)을 얻는다. 이를 서버(321.321.1.321,9999)의 적당한 위치에 복사한다.

5. 서버(321.321.1.321,9999)에서 CREATE ASSEMBLY문으로 CREATE ASSEMBLY를 하고

이 때 위에서 만든 CLR_PDW.dll의 경로를 입력해준다. 그리고 프로시저를 하나 생성하여 어셈블리를 수행하도록 한다.

6. 테스트를 한다.

A 서버 SQL Server(321.321.1.321,9999)에서 USP_CLR_PDW를 호출하여 B 서버의 PDW(123.123.1.123,8888)의 TEST DB CUSTOMER 테이블을 SELECT 해본다.

※ 별도로 test DB에는 로그인 생성, 사용자 추가, 롤에 사용자를 추가해야한다.

--로그인 생성
CREATE LOGIN exuser WITH PASSWORD = 'exuser',
CHECK_EXPIATION = OFF,
CHECK_POLICY = OFF;

--TEST DB에 사용자 추가
USE test;
CRATE USER exuser WITH DEFAULT_SCHEMA=dbo;

--롤에 사용자 추가
EXEC test..sp_addrolemember db_datareader, exuser;

대한민국 지도에 인구수, 세대수 표현하기 - 시도, 시군구, 행정동, 법정동 코드를 활용한 데이터 매핑 과정

웹사이트에 대한민국 지도(시도, 시군구, 법정동)를 표현하고 각각 권역에 데이터를 입혀야하는 사람들에게 도움이 되기를 바라며 포스팅한다.

필자도 필요해서 집에서 며칠동안 연구해본 결과 전처리 과정이 꽤나 난감했다. 사실 데이터를 엮어서 표현하기 위해 가장 중요한 것은 기준인데 우리나라는 주소체계가 참 복잡하다. 신우편번호, 구우편번호, 행정동기준, 법정동기준 등 여러 기준이 있고 (게다가 행정구역이 통폐합이 된다.) 각 데이터가 여러 곳에서 독립적으로 제공하기때문에 데이터를 이어붙이기가 참 애매하다.

그나마 행정안전부에서 제공하는 것들끼리는 기준이 같기 때문에 행정동-법정동 매핑 데이터와 주민등록기준 세대수, 인구수 데이터를 붙일 수가 있었다. (하지만 어느정도 전처리 과정을 통해 가공이 필요하다.)

필자가 사용한 행정안전부 데이터이다.
링크바로가기 - https://drive.google.com/open?id=1_eBNYuvm6EVWFnQ2fPpebNUzCjCDXa0C

먼저 행정안전부>업무안내>지방자치분권실>주민등록 및 인감>주민등록 및 인감에서
시도, 시군구, 행정동/법정동 코드 기반의 데이터를 다운받는다.

링크 : http://www.mois.go.kr/frt/bbs/type001/commonSelectBoardList.do?bbsId=BBSMSTR_000000000052

여기에서 KIKmix.YYYYMMDD를 다운받아보면 행정동과 법정동(리 단위까지)을 매핑해놓았다. 이를 법정동 기준으로 사용하기 위해서는 조금 가공해야한다.

먼저 우리나라 행정구역코드는 다음과 같은 규칙으로 이루어져있다.
총 10자리이며 시도2자리+시군구2자리+읍면동4자리+리2자리인 것 같다. (아니면 참 난감해진다.) 따라서 법정동 기준으로 하기 위해서 5열의 법정동 코드 끝 2자리를 모두 '00'으로 치환한 후 중복을 제거하면 된다.

대한민국 법정동 코드를 조회할 수 있는 링크이다.
https://www.code.go.kr/index.do

위의 링크에 몇가지 케이스를 검증해보면 '%)', '%리'가 존재하고 이를 REPLACE(법정동,행정동)으로 치환해주도록 했다.

위의 과정을 처리하는 파이썬 코드는 다음과 같다.

import urllib.request
import time
from openpyxl import load_workbook
import os

try:
    if not(os.path.isdir("map_json")):
        os.makedirs(os.path.join("map_json"))
except OSError as e:
    print("Failed to create directory!!!!!")
    raise

angelEx=load_workbook(filename='KIKmix.20181008.xlsx')
sheet = angelEx.active
arr = []

for i in sheet.iter_rows(min_row=2):
    arr.append(i[4].value[0:8]+'00')
    
arr2 = list(set(arr))

"""
쉐이프 api 호출 부분
"""

5번째 열을 읽어서 끝 2자리를 모두 '00'으로 치환 후 배열에 넣고 중복을 제거했다.
이렇게 유니크한 법정동 코드를 구했으니 이를 이용하여 api나 크롤링을 하여 주소나 지도 쉐이프를 구하면 된다.

대략 글을 쓰는 기준으로 법정동코드는 5338개가 존재한다. 같은 파일에서 유니크한 행정동 코드는 3858개가 존재했다. 따라서 행정동-법정동이 M:N 관계라고 할 지라도 행정동 코드 1개에 법정동 코드가 N개가 매핑될 가능성이 더 높다는 것이다.
파일 전체를 db에 올리고 이에 따른 5338개에 매핑되는 행정동 코드도 남겨놓도록 하자.
다행히 행정동 코드가 더 적기때문에 이 과정은 문제가 없을 것이고 행정동 코드가 있어야 인구수와 세대수를 붙일 수 있기때문이다.




이제 이 법정동 코드에 세대수와 인구수를 붙이는 방법이다.
먼저 행정동 기준 세대수와 인구수 데이터를 구해야한다.

데이터를 구할 수 있는 행정안전부 주소는 다음과 같다.

링크 : http://www.mois.go.kr/frt/sub/a05/totStat/screen.do

이 곳에서 인구수와 세대수 파일을 다운 받아서 필요한 데이터만 병합하였다.
구글 드라이브에 올려놓은 ingu_sedae_merge.csv 파일이다. 병합하는 과정은 코드로 짜는 것보다 수기로 하는 것이 빠르다.
(참고로 인구수는 생산가능 인구(만15~64세)수의 총합이다.)

저 파일을 열어보면 컬럼 하나에 "서울특별시 강북구 수유제1동(1130561000)"이 모두 들어가있다. 따라서 이 파일에서 행정코드, 시도, 시군구, 읍면동을 분리하는 작업이 필요하다.

코드는 다음과 같다.

import pandas as pd
from pandas import DataFrame, Series

csv = pd.read_csv("ingu_sedae_merge.csv", encoding = 'euc-kr')
columns = ['HJ_CODE','SD','SSG','EMD','SPL_POP_CNT','GEN_CNT']
df = DataFrame(columns=columns)

hjgg_code = ''
sd = ''
sgg = ''
emd = ''
spl_pop_cnt = ''
gen_cnt = ''
a = ''
b = ''
c = ''

for i, row in csv.iterrows():

    if(len(row.iloc[0].split('(')[0].split(' ')) == 3):
        hjgg_code = row.iloc[0].split('(')[1].split(')')[0]
        sd = row.iloc[0].split('(')[0].split(' ')[0]
        sgg = row.iloc[0].split('(')[0].split(' ')[1]
        emd = row.iloc[0].split('(')[0].split(' ')[2]
        spl_pop_cnt = row.iloc[1]
        gen_cnt = row.iloc[2]
        
        df = df.append(Series([hjgg_code,sd,sgg,emd,spl_pop_cnt,gen_cnt]
        ,index=columns), ignore_index=True)

        print(hjgg_code)
        print(sd)
        print(sgg)
        print(emd)
        print(spl_pop_cnt)
        print(gen_cnt)
    
    if(len(row.iloc[0].split('(')[0].split(' ')) == 4):
        hjgg_code = row.iloc[0].split('(')[1].split(')')[0]
        sd = row.iloc[0].split('(')[0].split(' ')[0]
        b = row.iloc[0].split('(')[0].split(' ')[1]
        c = row.iloc[0].split('(')[0].split(' ')[2]
        sgg = b+' '+c
        emd = row.iloc[0].split('(')[0].split(' ')[3]
        spl_pop_cnt = row.iloc[1]
        gen_cnt = row.iloc[2]
        
        print(hjgg_code)
        print(sd)
        print(sgg)
        print(emd)
        print(spl_pop_cnt)
        print(gen_cnt)

        df = df.append(Series([hjgg_code,sd,sgg,emd,spl_pop_cnt,gen_cnt] ,index=columns), ignore_index=True)
        
print("완료")
df.to_csv(r'result.csv',index=False,encoding='euc-kr')

결과는 다음과 같다.

이제 위에서 구한 행정동-법정동 매핑 데이터(+@ map polygon 등)와 아래에서 구한 인구, 세대 데이터를 매핑할 수 있하여 법정동 기준으로 지도를 표현할 수 있게 되었다.
추가적으로 엑셀 파일들을 한번에 merge하는 코드는 시간이 나면 작업하도록 하겠다.
하지만 수동으로 작업하는 것이 빠르기 때문에 안했다.


정리하면 다음과 같다.
1. 법정동으로 지도와 인구수, 세대수를 표현하고싶다.
2. 행정동-법정동 매핑 데이터를 구한다.
3. 행정동 기반 인구수, 세대수 데이터를 구해서 2번의 법정동에 매핑한다.
(M:N관계로 인한 1/N + SUM 과정이 필요)
4. 2번 법정동 기준으로 MAP POLYGON(MAP SHAPE)를 구해서 지도를 그리고 데이터를 입힌다.