[PostGIS] 연속지적도를 Partition table 로 만드는 방법

식빵·2023년 11월 25일
GISPostgreSQLpartition table
0

postgis

목록 보기
8/9
post-thumbnail

만약 본인이 Window OS 사용자면 pwsh 스크립트 (*.ps1) 를 통한 더 쉬운 방법이
있습니다. 해당 방법은 제가 다른 글에 기재했습니다.
아래 과정 보다 훨배 간단하니, 해당 글을 먼저 참고해보시길 바랍니다.


사전 지식

아래 사항을 어느정도 알고 있다면 읽는데 큰 지장이 없을 겁니다!

  • GIS, PostGIS
  • Partition Table
    • Partition Table 이 생소하면? 이 글을 가볍게 보고 오시길 바랍니다.



연속지적도는 무겁습니다.

GIS 분야에서 어느정도 일해봤다면 항상 마주치는 데이터가 있습니다.
바로 연속지적도죠. 얼마나 인기가 좋은지 국가공간정보포털에서 항상 인기검색어
1, 2등을 차지하고 있죠.

그런데 이 데이터가 좀 무겁습니다.
얼마나 무거운지 각 시도별로 데이터를 나눴을 정도니까요.

그렇다면 이렇게 무거우면서도 심지어 여러 개의 파일로 나뉘어진
연속지적도 데이터(=Esri ShapeFile)를 PostgreSQL DB 에 넣기
위해서는 어떻게 해야될까요?

저는 크게 3가지 작업으로 나눕니다.

  • DB 에 파티셔닝 테이블 생성
    • 1개의 파티션(논리) 테이블 생성
    • 파티션(논리) 하부에 들어갈 여러 파티션(물리) 테이블들을 생성
  • 각 파티션 테이블에 맞는 ShapeFile 데이터를 insert / append
    • 이때 사용할 수 있는 Tool
      • gdal library : CLI 환경에서 더 특화된 Tool
      • shp2pgsql-gui : GUI 환경에서 더 특화된 Tool
  • 파티션(논리) 테이블에 인덱스 생성
    • gist 인덱스
    • pnu 인덱스
    • 등등...

지금부터 이 작업들의 상세한 내용을 작성해보겠습니다.
실습을 원하시면 연속지적도 데이터를 미리 다운 받으시길 바랍니다! (다운로드 링크)

참고) 연속지적도 다운로드 방식의 변화

주의! 기존 다운로드 방식이 조금 바뀌었습니다.
제가 기존에 자료를 다운로드 받던 국가공간정보포탈이 서비스가 중단되고,
브이월드에 해당 서비스가 이전되었습니다.

이에 따라 변경된 다운로드 방법을 먼저 알아보겠습니다.


1. 브이월드 사이트에 접속하고 아래처럼 메뉴버튼을 클릭합니다.


2. 검색란에 연속지적도 라고 입력하고 검색 버튼을 클릭합니다.


3. 연속지적도_전국 이라는 목록이 나옵니다. 해당 목록의 제목을 클릭합니다.


4. 데이터 리소스 항목에서 다운로드 할 수 있는 목록이 나옵니다.
여기서 필요한 것들을 검색하여 다운로드 받습니다.
예전처럼 *_서울.zip 이 아니라, *_서울_~~.zip 처럼 나와서 하나하나 검색해야 됩니다.



파티셔닝 계획

연속지적도 데이터는 오픈마켓을 통해서 다운로드 받는데,
데이터의 형태는 아래와 같습니다.

(출처: http://data.nsdi.go.kr/dataset/12771)

보시면 알겠지만, 시도별로 데이터를 나눠서 줍니다.
그래서 저도 그에 맞춰 시도별로 테이블을 파티셔닝하려고 합니다.

하지만 파티셔닝을 하려면 연속지적도의 어떤 컬럼이 시도를 구분하는지 알아야겠죠?
잠시 연속지적도 데이터를 같이 보시죠.

여기서 주의해서 봐야될 값은 바로 pnu 입니다!
왜냐하면 pnu 앞 2자리가 각 시도를 구분하는 코드값이기 때문이죠!!!

출처: http://www.gisdeveloper.co.kr/?p=1562


참고: PNU 앞 2자리와 시도 명칭 매핑 - (출처: 법정동코드목록조회 페이지 html 중 일부)


그러면 저희의 최종 목표는 뭘까요? 그건 바로...

"PNU 앞 2자리 기반의 파티셔닝이 테이블 생성입니다!"






파티셔닝 테이블 작업

논리/물리 테이블 생성

논리 테이블은 모든 파티션을 담는 Master Table,
물리 테이블은 각각의 파티션을 의미하는 partition table 을 표현한 말입니다.
표현들을 약간 혼용해서 사용하니 착오 없으시기 바랍니다.

LSMD_CONT_REG 라는 명칭의 논리 테이블을 만들어보겠습니다.

LSMD_CONT_LDREG 라는 명칭은 오픈마켓에서 주는 연속지적도 데이터의 명칭과
동일하게 하기 위함입니다. 이래야 나중에 더 찾기 쉽겠죠?

-- 파티션 테이블을 모두 담을 Master 테이블 생성
create table lsmd_cont_ldreg
(
    sgg_oid    integer,
    jibun      varchar(100),
    bchk       varchar(1),
    pnu        varchar(19),
    col_adm_se varchar(5),
    geom       geometry(MultiPolygon, 5186)
) partition by range(pnu); -- 파티셔닝 range 지정!

-- comment 는 나 그리고 동료들을 위해서 꼭 지정해줍니다.
comment on table lsmd_cont_ldreg is '연속지적도_전국';
comment on column lsmd_cont_ldreg.sgg_oid is '원천오브젝트ID';
comment on column lsmd_cont_ldreg.jibun is '지번';
comment on column lsmd_cont_ldreg.bchk is '발급승인코드';
comment on column lsmd_cont_ldreg.pnu is '필지고유번호';
comment on column lsmd_cont_ldreg.col_adm_se is '원천시군구코드';
comment on column lsmd_cont_ldreg.geom is '지오메트리';

-- 파티셔닝 테이블 생성!
create table lsmd_cont_ldreg_51 (like lsmd_cont_ldreg); -- 강원특별자치도
create table lsmd_cont_ldreg_41 (like lsmd_cont_ldreg); -- 경기도
create table lsmd_cont_ldreg_48 (like lsmd_cont_ldreg); -- 경상남도
create table lsmd_cont_ldreg_47 (like lsmd_cont_ldreg); -- 경상북도
create table lsmd_cont_ldreg_29 (like lsmd_cont_ldreg); -- 광주광역시
create table lsmd_cont_ldreg_27 (like lsmd_cont_ldreg); -- 대구광역시
create table lsmd_cont_ldreg_30 (like lsmd_cont_ldreg); -- 대전광역시
create table lsmd_cont_ldreg_26 (like lsmd_cont_ldreg); -- 부산광역시
create table lsmd_cont_ldreg_11 (like lsmd_cont_ldreg); -- 서울특별시
create table lsmd_cont_ldreg_36 (like lsmd_cont_ldreg); -- 세종특별자치시
create table lsmd_cont_ldreg_31 (like lsmd_cont_ldreg); -- 울산광역시
create table lsmd_cont_ldreg_28 (like lsmd_cont_ldreg); -- 인천광역시
create table lsmd_cont_ldreg_46 (like lsmd_cont_ldreg); -- 전라남도
create table lsmd_cont_ldreg_45 (like lsmd_cont_ldreg); -- 전라북도
create table lsmd_cont_ldreg_50 (like lsmd_cont_ldreg); -- 제주특별자치도
create table lsmd_cont_ldreg_44 (like lsmd_cont_ldreg); -- 충청남도
create table lsmd_cont_ldreg_43 (like lsmd_cont_ldreg); -- 충청북도

-- 파티셔닝 테이블을 Master 테이블에 부착(Attach)!
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_51
    for values from ('5100000000000000000') to ('5200000000000000000'); -- 강원특별자치도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_41
    for values from ('4100000000000000000') to ('4200000000000000000'); -- 경기도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_48
    for values from ('4800000000000000000') to ('4900000000000000000'); -- 경상남도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_47
    for values from ('4700000000000000000') to ('4800000000000000000'); -- 경상북도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_29
    for values from ('2900000000000000000') to ('3000000000000000000'); -- 광주광역시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_27
    for values from ('2700000000000000000') to ('2800000000000000000'); -- 대구광역시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_30
    for values from ('3000000000000000000') to ('3100000000000000000'); -- 대전광역시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_26
    for values from ('2600000000000000000') to ('2700000000000000000'); -- 부산광역시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_11
    for values from ('1100000000000000000') to ('1200000000000000000'); -- 서울특별시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_36
    for values from ('3600000000000000000') to ('3700000000000000000'); -- 세종특별자치시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_31
    for values from ('3100000000000000000') to ('3200000000000000000'); -- 울산광역시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_28
    for values from ('2800000000000000000') to ('2900000000000000000'); -- 인천광역시
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_46
    for values from ('4600000000000000000') to ('4700000000000000000'); -- 전라남도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_45
    for values from ('4500000000000000000') to ('4600000000000000000'); -- 전라북도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_50
    for values from ('5000000000000000000') to ('5100000000000000000'); -- 제주특별자치도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_44
    for values from ('4400000000000000000') to ('4500000000000000000'); -- 충청남도
alter table lsmd_cont_ldreg attach partition lsmd_cont_ldreg_43
    for values from ('4300000000000000000') to ('4400000000000000000'); -- 충청북도




Shape File 데이터 Insert!

이제 데이터 마켓에서 받은 모든 연속지적도 데이터를 앞서 만든
파티션 테이블에 넣어보죠!

제가 앞서 얘기했지만, 데이터 주입을 위한 Tool 로는

  • shp2pgsql-gui
  • gdal library

...가 있다고 말씀 드렸습니다.
두 개의 tool 로 import 하는 법 모두 알아보겠습니다.


방법1: shp2pgsql-gui

일단 PostGIS Shapefile Import/Export Manager 를 띄우고...

View Connection 을 통해서 DB 연결하고

아래처럼 세팅합니다.

  • ① Add file 클릭하고 다운로드 받은 SHP 선택, 여러 개 선택도 가능합니다
  • ② Import List 에서 목록이 생기면 해당 목록 세팅
    • ③ Schema, Table 명 작성
      • Table 명은 앞서 만든 파티션 테이블과 데이터를 맞춰서 작성해야 합니다.
      • ex: LSMD_CONT_LDREG_29_xxxxxx.shp 이면 테이블명은 lsmd_cont_ldreg_29 !
    • ④ SRID=5186 으로 수정, Mode=Append 로 세팅
  • ⑤ Options... 클릭
    • ⑥ 인코딩을 EUC-KR 로 수정
    • ⑦ Do not create 'bigint' columns 체크 ✔
    • ⑦ Create spatial index automatically after load 체크 ❌
    • ⑧ Load data using COPY rather than INSERT 체크 ✔
    • ⑨ OK 클릭
  • ⑩ import 클릭

여기서는 제가 Add file 로 한 개의 shp 만 했지만
실제로는 모든 시도 SHP 파일을 한번에 해야합니다.
1~4 번 과정을 SHP 수만큼 반복해서 하고, 이후에
5~10번 과정을 한번만 진행하면 됩니다.

좀... 귀찮죠? 그래서 저는 개인적으로 gdal 을 쓰는 걸 더 좋아합니다.




방법2: gdal library (추천)

gdal library 는 간단하게 말해서 gis 데이터에는 여러 포맷이 있는데,
그런 서로 다른 유형의 데이터간에 변환작업을 도와주는 라이브러리입니다.

gdal library 에서 제공하는 프로그램이 많은데,
지금 필요한 건 그중에서도 ogr2ogr 이라는 프로그램입니다.
ogr2ogr 로 연속지적도를 DB에 적재하는 방법은 정말 간단합니다.

GDAL 설치법
제가 이전에 작성한 설치법 목차만 읽고, 직접 세팅하시면 됩니다.
참고로 저는 PATH 환경변수에 gdal library 경로를 추가하고,
pwsh 상에서 실습을 진행했습니다.

그러니 여러분들도 저와 같은 방식으로 하려면 pwsh 를 설치해야합니다.
pwsh 설치법 또한 제가 이전에 작성한 글을 참고하시기 바랍니다.


먼저 다운로드 받은 모든 연속지적도 zip 파일들을 하나의 디렉토리에
몰아 넣습니다.


pwsh 을 실행시키고, 다운로드 받은 연속지적도 zip 파일들이
모인 디렉토리로 이동하고 압축해제를 합니다.

cd "shp 파일을 모아 놓은 경로";
ls "*.zip" | ForEach-Object {
   Expand-Archive $_ ./
}

그러면 아래처럼 압축해제되어서 shp 파일들이 모두 한 디렉토리에 모입니다.

해당 디렉토리에서 다시 아래와 같이 명령어를 입력해줍니다.

ls '*.shp' | foreach-Object {
    $baseName = $_.BaseName;
    $lastIndexOfUnderBar = $baseName.LastIndexOf('_');
    $importTableName = $baseName.Substring(0, $lastIndexOfUnderBar);
    echo "importing '$_' file To '$importTableName'"
    ogr2ogr `
    --config PG_USE_COPY YES -append -progress -f PostgreSQL `
    PG:"host=localhost port=5432 dbname=postgres schemas=public user=postgres password=root" `
    "$_" `
    --config SHAPE_ENCODING "EUC-KR" --config OGR_TRUNCATE YES `
    -nln $importTableName -nlt multipolygon
}

이러면 대충 아래처럼 콘솔에 문구가 출력됩니다.

이러고 기다리기만 하면 끝입니다!

이후에 database 에 가서 lsmd_cont_ldreg 를 조회해보면 데이터가
잘 들어간 것을 확인할 수 있습니다.




Master 테이블 인덱스 생성

마지막으로 파티션 Master 테이블(= 논리 테이블)에 인덱스를 걸어주면 끝입니다!
인덱스는 아래처럼 줬습니다!

create index lsmd_cont_ldreg_pnu_idx
    on lsmd_cont_ldreg (pnu);

-- geometry 데이터가 있으면 거의 관례적으로 넣는 gist 인덱스
create index lsmd_cont_ldreg_geom_idx
    on lsmd_cont_ldreg using gist (geom);

실행하면 제법 시간이 걸립니다. 인내심을 갖고 기다립니다.
저는 한 10분 걸렸습니다.


이상으로 연속지적도를 PostgreSQL Import 작업이 모두 끝났습니다.
같이 따라하셨던 분들 모두 고생하셨습니다. 👍👍




참고링크

profile
식빵
백엔드를 계속 배우고 있는 개발자입니다 😊
이전 포스트

[PostGIS] 사분원?

다음 포스트

[PostGIS] pwsh script (ps1) + gdal library 를 사용한 연속지적도 한방에 partiton table 로 import 하기

0개의 댓글