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

식빵·2023년 11월 26일

postgis

목록 보기

9/9

작성계기
이 글은 제가 이전에 작성했던 연속지적도를 Partition table 로 만드는 방법 게시물의
내용을 좀 더 쉽게 할 수 있는 방법에 대해 고민을 해봤고,
그 끝에 ps1 을 통해서 더 쉽게 하는 방안을 알아내서 이 글을 작성하게 됐습니다.

주의!
제목에도 나와있지만 반드시 본인 PC 에 pwsh 과 gdal library 가
설치되어 있어야 합니다! 이미 설치되어 있다면 바로 시작하셔도 됩니다.
설치가 안되어 있다면 아래 링크를 참조해주세요.

pwsh 설치

gdal library 설치 (QGIS 우회사용법)

이 글에서 설치법 목차를 참조해주세요.
다만 이 방법은 QGIS 설치를 동반해야 되서 약간의 불편함이 있을 수 있습니다.
만약 순수 GDAL 설치를 원하면 따로 찾아보시기 바랍니다.

사전 지식

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

GIS, PostGIS

Partition Table

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

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

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

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

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

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

DB 에 파티셔닝 테이블 생성
- 1개의 파티션(논리) (=Master) 테이블 생성
- 파티션(논리) 하부에 들어갈 여러 파티션(물리) (=Child) 테이블들을 생성

각 파티션 테이블에 맞는 ShapeFile 데이터를 insert / append

파티션(논리) 테이블에 인덱스 생성

이 글에서는 위의 과정들을 단 하나의 pwsh 스크립트 (ps1) 으로
할 수 있는 방법을 작성해봤습니다.

실습을 원하시면 연속지적도 데이터를 미리 다운 받으시길 바랍니다. (다운로드 링크)

ps1 스크립트로 한방에 끝내기

ps1 스크립트는 pwsh 에서 사용하는 스크립트 파일입니다.
bash 쉘에서 사용하는 shell script 과 비슷한 개념입니다.

그리고 이 ps1 스크립트에 powershell 명령어 + gdal 명령어 + psql 명령어
를 적절하게 섞어서 한방에 연속지적도 데이터를 Postgresql 의 Partiton table 로
만들 생각입니다.

지금부터 그 방법을 알아보겠습니다.

연속지적도 데이터 unzip

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

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

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

그러면 아래처럼 압축해제되어서 shp 파일들이 모두 한 디렉토리에 모입니다.
지저분해 보이지만 의도적으로 하나의 디렉토리에 모은 겁니다.

참고! 꼭 위처럼 압축해제를 해야되는 건 아닙니다.
예를 들어 아래처럼 ShapeFile 이 들어있는 디렉토리 형태로 압축해제해도
정상적으로 아래 ps1 스크립트가 동작하도록 했습니다.

심지어는 아래처럼 몇개는 디렉토리, 몇개는 외부에 ShapeFile 로 압축해제해도 동작합니다.

ps1 스크립트 생성

다음으로 아래와 같은 내용을 갖은 *.ps1 확장자를 갖는 파일을 생성합니다.
저는 스크립트 명을 import_multi_shape.ps1 이라고 짓겠습니다.

2024-02-15 변경사항!

(개선 사항) 이제는 O...N Depth 의 Shape File 을 재귀적으로 모두 찾아냅니다.

(버그 해결) 파라미터로 전달하는 모든 userid, password, host, port 값이 psql 에 적용됩니다.

param (
  # DB Host 명 또는 IP
  [string] $dbHost,
  # DB Port 번호
  [string] $dbPort,
  # import DB 명
  [string] $importDbName,
  # import DB 스키마명
  [string] $importSchema,
  # postgresql 사용자 ID
  [string] $dbConnectUserId,
  # postgresql 사용자 비번
  [string] $dbConnectUserPw,
  # postgresql 에 partition table 을 모두 담을 논리테이블(Master 테이블 명)
  [string] $partitionMasterTableNm,
  # shape file 이 모두 담겨있는 디렉토리 경로. default: 스크립트 실행 디렉토리
  [string] $shapeFileDirectoryPath = $(Get-Location));

<#
사용 예시 (ps1 스크립트명이 import_multi_shape.ps1 이라고 가정함)

.\import_multi_shape.ps1 `
-dbHost "localhost"`
-dbPort "5432" `
-importDbName "postgres" `
-importSchema "public" `
-dbConnectUserId "postgres" `
-dbConnectUserPw "postgres" `
-partitionMasterTableNm "lsmd_cont_ldreg" `
-shapeFileDirectoryPath "C:\Users\Desktop\연속지적도_2024"
#>

Write-Host "
You Had Insert Param Like Below...
"
Write-Host "dbHost=$dbHost"
Write-Host "dbPort=$dbPort"
Write-Host "importDbName=$importDbName"
Write-Host "importSchema=$importSchema"
Write-Host "dbConnectUserId=$dbConnectUserId"
Write-Host "dbConnectUserPw=$dbConnectUserPw"
Write-Host "partitionMasterTableNm=$partitionMasterTableNm"
Write-Host "shapeFileDirectoryPath=$shapeFileDirectoryPath"
Write-Host ""

if (!(Test-Path -PathType Container $shapeFileDirectoryPath)) {
  throw "There is No Such Directory name [$shapeFileDirectoryPath]!";
}
elseif ((Get-ChildItem "$shapeFileDirectoryPath/*.shp").Length -eq 0) {
  throw "There is no shape file in [$shapeFileDirectoryPath] directory";
}

# setting PGPASSWORD for using "psql -c" without password prompt.
$env:PGPASSWORD = $dbConnectUserPw;

# checking database connection.
psql -h "$dbHost" -p "$dbPort" -U "$dbConnectUserId" -d "$importDbName" -c "select 1";
# if connection state is bad it will stop the process
if($LASTEXITCODE -ne 0) {
  throw "DataBase Connection Error Occured!!!"
}

# Drop table first and then create a new partition master table
Write-Host "REMOVING $importSchema.$partitionMasterTableNm table and create a new one";

# remove existing table
psql -h "$dbHost" -p "$dbPort" -U "$dbConnectUserId" -d "$importDbName" -c `
"drop table if exists $importSchema.$partitionMasterTableNm cascade;";

# creating new table.
psql -h "$dbHost" -p "$dbPort" -U "$dbConnectUserId" -d "$importDbName" -c `
"create table $importSchema.$partitionMasterTableNm (
  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);";

Write-Host "
looping all the shapefiles inside '$shapeFileDirectoryPath'.
each shapefile will be imported as a parititon table
";

# loop all the shape file inside the directory you had config.
Get-ChildItem "$shapeFileDirectoryPath/*.shp" -Recurse | foreach-Object -ErrorAction Stop {
  $shapeFileFullPath = $_;
  $baseName = $_.BaseName;
  $lastIndexOfUnderBar = $baseName.LastIndexOf('_');
    
  # remove creation date Postfix from file name
  $partitionTableNm = $baseName.Substring(0, $lastIndexOfUnderBar).ToLower();

  # getting sido code which is middle of the shape file name 
  $sidoCode = $partitionTableNm.Substring($partitionTableNm.LastIndexOf('_') + 1);

  # create pnu start range value
  $pnuStart = $sidoCode.PadRight(19, '0');

  # create pnu end range value
  $pnuEnd = (([int16]$sidoCode) + 1).ToString().PadRight(19, '0');

  Write-Host "importing `"$_`" file To `"$partitionTableNm`"
           pnu range: [ '$pnuStart' ~ '$pnuEnd' ]";

  # create partition table and attach to master table at once
  psql -h "$dbHost" -p "$dbPort" -U "$dbConnectUserId" -d "$importDbName" `
  -c "CREATE TABLE $importSchema.$partitionTableNm 
  PARTITION OF $importSchema.$partitionMasterTableNm 
  FOR VALUES FROM ('$pnuStart') TO ('$pnuEnd');";

  # import shape file data to partition table.
  ogr2ogr `
    --config PG_USE_COPY YES -append -progress -f PostgreSQL `
    PG:"host=$dbHost port=$dbPort dbname=$importDbName schemas=$importSchema user=$dbConnectUserId password=$dbConnectUserPw" `
    "$shapeFileFullPath" `
    --config SHAPE_ENCODING "EUC-KR" --config OGR_TRUNCATE YES `
    -nln $partitionTableNm -nlt multipolygon;
}

Write-Host "
creating index for partiton master table $partitionMasterTableNm
";

# creating new index for "pnu"
Write-Host "EXECUTE: [psql -U $dbConnectUserId -c `"create index ${partitionMasterTableNm}_pnu_idx on $importSchema.$partitionMasterTableNm (pnu);`"]";

psql -h "$dbHost" -p "$dbPort" -U "$dbConnectUserId" -d "$importDbName" -c `
"create index ${partitionMasterTableNm}_pnu_idx 
  on $importSchema.$partitionMasterTableNm (pnu);";

# creating new gist index for geometry column
Write-Host "EXECUTE: [psql -U $dbConnectUserId -c `"create index $importSchema.${partitionMasterTableNm}_geom_idx on ${partitionMasterTableNm} using gist (geom);`"]";

psql -h "$dbHost" -p "$dbPort" -U "$dbConnectUserId" -d "$importDbName" -c `
"create index ${partitionMasterTableNm}_geom_idx 
  on $importSchema.${partitionMasterTableNm} using gist (geom);";

위 스크립트가 하는 일은 다음과 같습니다.

파티션 마스터 테이블을 지웠다가 다시 생성합니다.
마스터 테이블 명은 $partitionMasterTableNm 을 따릅니다.

디렉토리 내에 있는 모든 *.shp 을 재귀적으로 찾아내서 for loop 을 돕니다.

각 *.shp 파일은 순회하면서 아래와 같은 상세 작업을 합니다.
- 파일 명 뒤에 있는 연월(ex: *_202311.shp) 표기를 지운 string 추출하고
  partitionTableNm 변수에 저장
- 각 파일에 표기되어 있는 시도 코드를 추출하여 sidoCode 변수에 저장
- sidoCode 를 통해서 PNU RANGE 를 생성 (pnuStart <= x < pnuEnd)
- 생성한 변수를 통한 파티션 테이블 생성 및 마스터 테이블에 attach
- 테이블에 ogr2ogr 를 통해서 shp -> db table 로 데이터 COPY

for 문을 모두 순회하고 나서 최종적으로 마스터 테이블에 필요한 인덱스 생성

pwsh 에서 ps1 실행

스크립트 파일을 생성하고 pwsh 에서 아래처럼 스크립트를 실행 시키면 됩니다.

실행 예시

.\import_multi_shape.ps1 `
-dbHost "localhost" `
-dbPort "5432" `
-importDbName "postgres" `
-importSchema "public" `
-dbConnectUserId "postgres" `
-dbConnectUserPw "root" `
-partitionMasterTableNm "lsmd_cont_ldreg" `
-shapeFileDirectoryPath "C:\shp_files\"