How to load the network data to the Postgresql DB

2024-12-03 skanto Comments 0 Comment

도로네트워크 데이터 DB생성

PostgreSQL DBMS에 배포되는 원본 데이터를 데이터베이스 적재하기 위한 데이터베이스를 먼저 생성한다. 이 과정은 PostgreSQL 설치시 생성한 postgres 계정으로 접속하여 작업을 진행한다.

이 예제에서는 생성할 데이터베이스 이름을 “network” 로 한다. 이 이름은 필요에 따라 변경해도 무방하다.

sudo 권한으로 postgres 계정으로 접속
psql 접속
아래와 같이 데이터 베이스 생성 (DDL) 실행CREATE DATABASE <DATABASE_NAME>WITH ENCODING = 'UTF8'TEMPLATE = template0LC_COLLATE = 'ko_KR.UTF-8'LC_CTYPE = 'ko_KR.UTF-8'CONNECTION LIMIT = -1;
생성된 데이터베이스에 스키마를 생성한다.
스키마는 위에서 생성한 데이터베이스에 배포된 버전별로 관리하기 위한 용도로 활용한다. 보통 데이터 배포는 압축파일 형태(예: Network_20240118_V10.0_Full.zip)로 배포되며 여기서는 배포일 기준으로 스키마(예: v_20240118)를 생성한다. 스키마 생성 후 \dn 명령어를 이용하여 스키마가 정상적으로 생성되었는지 확인한다.CREATE SCHEMA <schema_name>;
생성된 DB(network)으로 접속 (\c network)
PostGIS extension 설치CREATE EXTENSION postgis;

아래는 위에서 설명한(1~6) 일련의 과정으로 보여준다.

skanto@skanto-HP-ZBook:~$ sudo su - postgres
postgres@skanto-HP-ZBook:~$ psql
psql (16.1 (Ubuntu 16.1-1.pgdg20.04+1))
Type "help" for help.
 
postgres=# CREATE DATABASE network
postgres-# WITH ENCODING = 'UTF8'
postgres-# TEMPLATE = template0
postgres-# LC_COLLATE = 'ko_KR.UTF-8'
postgres-# LC_CTYPE = 'ko_KR.UTF-8'
postgres-# CONNECTION LIMIT = -1;
CREATE DATABASE
postgres-#
postgres=# \c network
You are now connected to database "network" as user "postgres".
network=#
network=# CREATE SCHEMA v_20240118;
CREATE SCHEMA
network=#
network=# \dn
        List of schemas
    Name    |       Owner      
------------+-------------------
 public     | pg_database_owner
 v_20240118 | postgres
(2 rows)
network=# CREATE EXTENSION postgis;
CREATE EXTENSION
network=#

한국에서 사용하는 좌표계(UTMK, Katech^{자동차연구원}) 등록

경로탐색엔진과 KT지도에서 UTMK좌표를 사용하고 현재는 많이 사용되지 않지만 사용할 가능성이 있어 Katech좌표계도 같이 등록한다. 참고로 맵퍼스로부터 수급받는 원본 데이터는 WGS84 좌표계로 제작되어 있어 좌표계 변환시 내부적으로 참조가 필요하다.

-- UTMK 좌표계 등록
network=# INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 97165, 'sr-org', 7165, '+proj=tmerc +lat_0=38 +lon_0=127.5 +k=0.9996 +x_0=1000000 +y_0=2000000 +ellps=GRS80 +units=m +no_defs ','PROJCS["UTMK",GEOGCS["GCS_ITRF_2000",DATUM["D_ITRF_2000",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",1000000.0],PARAMETER["False_Northing",2000000.0],PARAMETER["Central_Meridian",127.5],PARAMETER["Scale_Factor",0.9996],PARAMETER["Latitude_Of_Origin",38.0],UNIT["Meter",1.0]]');
 
-- KATECH 좌표계 등록
network=# INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 98030, 'sr-org', 8030, '+proj=tmerc +lat_0=38 +lon_0=128 +k=0.9999 +x_0=400000 +y_0=600000 +ellps=bessel +towgs84=-145.907,505.034,685,0,0,0,0 +units=m +no_defs ', 'PROJCS["Katech",GEOGCS["Bessel 1841",DATUM["unknown",SPHEROID["bessel",6377397.155,299.1528128],TOWGS84[-145.907,505.034,685,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",38],PARAMETER["central_meridian",128],PARAMETER["scale_factor",0.9999],PARAMETER["false_easting",400000],PARAMETER["false_northing",600000],UNIT["Meter",1]]');

위에서 사용된 spatial_ref_sys DB 테이블은 PostGIS Extension 설치시 자동으로 생성되는 테이블이다.

Command line 툴을 이용한 데이터 로딩

Command line툴을 이용한 데이터 로딩은 shp2pgsql 명령어를 활용하며 배포된 원본 도로네트워크 데이터를 앞에서 생성한 데이터베이스로 로딩한다. shp2pgsql 는 PostgreSQL 데이터베이스 Extension인 PostGIS를 설치하면 같이 설치되는 명령어이다. 만약 설치되어 있지 않다면 아래와 같이 설치 가능하다(설치 후 도로네트워크 DB생성부터 시작!).

sudo apt-get install postgis

shp2pgsql이 설치되어 있다면 shp2pgsql 명령어 실행 시 아래와 같이 출력된다.

network=# quit
postgres@skanto-HP-ZBook:~$ shp2pgsql
RELEASE: 3.4.1 (ca035b9)
USAGE: shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]
OPTIONS:
  -s [<from>:]<srid> Set the SRID field. Defaults to 0.
      Optionally reprojects from given SRID.
 (-d|a|c|p) These are mutually exclusive options:
     -d  Drops the table, then recreates it and populates
         it with current shape file data.
     -a  Appends shape file into current table, must be
         exactly the same table schema.
     -c  Creates a new table and populates it, this is the
         default if you do not specify any options.
     -p  Prepare mode, only creates the table.
  -g <geocolumn> Specify the name of the geometry/geography column
      (mostly useful in append mode).
..
..

shp/dbf 파일 로드

shp2pgsql command를 이용하여 도로네트워크 원본 데이터를 데이터베이스로 로딩한다. shp2pgsql 명령어에서 활용하는 옵션은 아래와 같다.

-D: postgresql dump 포멧을 사용(default는 SQL insert문 사용)
-s: SRID (srid: 4326 – WGS84 좌표계)
-W: 인코딩 (Linux는 UTF-8이 default임, 맵퍼스 원도 데이터는 UTF-8로 인코딩되어 있어 운영체제 Encoding이 UTF-8일 경우 이 옵션을 생략해도 무방)
-I: 공간 인덱스(GIST) 생성 여부
-S: simple geometry 사용여부
-n: DBF 파일만 로드할 경우 설정 (이 경우에는 -s, -I, -S가 불필요)

postgres@skanto-HP-ZBook:~$ shp2pgsql -D -s 4326 -I -S <shapefile> [[<schema>.]<table>] | psql -d <database_name>

예를 들어 NET_LINK_L.shp파일을 데이터베이스로 로딩하려면 아래와 같이 수행하면 된다. 이때 shp 파일을 지정하면 명령어가 dbf(데이터), shx(인덱스)를 자동으로 찾아 데이터베이스로 로딩한다.

활용예

postgres@skanto-HP-ZBook:~$
postgres@skanto-HP-ZBook:~$ shp2pgsql -D -s 4326 -I -S /home/skanto/Documents/00.Development/data/Network_20240118_V10.0_Full/NET_LINK_L.shp v_20240118.NET_LINK_L | psql -d network
Field link_l is an FTDouble with width 11 and precision 0
Field road_num is an FTDouble with width 11 and precision 0
Shapefile type: Arc
Postgis type: LINESTRING[2]
SET
SET
BEGIN
CREATE TABLE
ALTER TABLE
                    addgeometrycolumn                    
----------------------------------------------------------
 public.net_link_l.geom SRID:4326 TYPE:LINESTRING DIMS:2
(1 row)
 
COPY 3698757
CREATE INDEX
COMMIT
ANALYZE

shp파일이 아닌 dbf파일만 데이터베이스로 로딩할 경우 위에서 설명한 것처럼 불필요한 옵션을 제거 후 실행하면 된다.

postgres@skanto-HP-ZBook:~$ shp2pgsql -D /home/skanto/Documents/00.Development/data/Network_20240118_V10.0_Full/NET_CNINFO.dbf v_20240118.NET_CNINFO | psql -d network
Unable to open /home/skanto/Documents/00.Development/data/Network_20240118_V10.0_Full/NET_CNINFO.shp or /home/skanto/Documents/00.Development/data/Network_20240118_V10.0_Full/NET_CNINFO.SHP.
/home/skanto/Documents/00.Development/data/Network_20240118_V10.0_Full/NET_CNINFO.dbf: shape (.shp) or index files (.shx) can not be opened, will just import attribute data.
SET
SET
BEGIN
CREATE TABLE
ALTER TABLE
COPY 20750040
COMMIT
ANALYZE

참고로 배포되는 도로네트워크 데이터 종류는 아래와 같다. (2024.1월 데이터 기준)

도로네트워크 원본 데이터 종류

skanto@skanto-HP-ZBook:~/Documents/00.Development/data/Network_20240118_V10.0_Full$ ls -l
total 6372940
-rw-rw-r-- 1 skanto skanto 3195506481  1월 19 11:11 NET_CNINFO.dbf
-rw-rw-r-- 1 skanto skanto   93339225  1월 19 11:11 NET_DIR.dbf
-rw-rw-r-- 1 skanto skanto   79247465  1월 19 11:13 NET_FARE.dbf
-rw-rw-r-- 1 skanto skanto   79247465  1월 19 11:13 NET_FARE_EV.dbf
-rw-rw-r-- 1 skanto skanto   80876241  1월 19 11:13 NET_FARE_EV_HI.dbf
-rw-rw-r-- 1 skanto skanto   80876241  1월 19 11:13 NET_FARE_HI.dbf
-rw-rw-r-- 1 skanto skanto     203679  1월 19 11:13 NET_FERRY_INFO.dbf
-rw-rw-r-- 1 skanto skanto      75768  1월 19 11:13 NET_FERRY_LINK.dbf
-rw-rw-r-- 1 skanto skanto    5083165  1월 19 11:12 NET_GUIDEPT_P.dbf
-rw-rw-r-- 1 skanto skanto    1949684  1월 19 11:12 NET_GUIDEPT_P.shp
-rw-rw-r-- 1 skanto skanto     557124  1월 19 11:12 NET_GUIDEPT_P.shx
-rw-rw-r-- 1 skanto skanto    1527988  1월 19 11:14 NET_IMAGE.dbf
-rw-rw-r-- 1 skanto skanto   10323681  1월 19 11:11 NET_LANE.dbf
-rw-rw-r-- 1 skanto skanto   26382177  1월 19 11:02 NET_LINKIN.dbf
-rw-rw-r-- 1 skanto skanto 1335252526  1월 19 11:02 NET_LINK_L.dbf
-rw-rw-r-- 1 skanto skanto  567197260  1월 19 11:02 NET_LINK_L.shp
-rw-rw-r-- 1 skanto skanto   29590156  1월 19 11:02 NET_LINK_L.shx
-rw-rw-r-- 1 skanto skanto  682972565  1월 19 11:04 NET_NODE_P.dbf
-rw-rw-r-- 1 skanto skanto   86923832  1월 19 11:04 NET_NODE_P.shp
-rw-rw-r-- 1 skanto skanto   24835452  1월 19 11:04 NET_NODE_P.shx
-rw-rw-r-- 1 skanto skanto   16455785  1월 19 11:13 NET_OIL_LINK.dbf
-rw-rw-r-- 1 skanto skanto     357268  1월 19 11:13 NET_OIL_LINK.shp
-rw-rw-r-- 1 skanto skanto     102148  1월 19 11:13 NET_OIL_LINK.shx
-rw-rw-r-- 1 skanto skanto    2948381  1월 19 11:12 NET_SAFETY_L.dbf
-rw-rw-r-- 1 skanto skanto      24435  1월 19 11:12 NET_SAFETY_PAIR.dbf
-rw-rw-r-- 1 skanto skanto   76003615  1월 19 11:12 NET_SAFETY_P.dbf
-rw-rw-r-- 1 skanto skanto   31762684  1월 19 11:12 NET_SAFETY_P.shp
-rw-rw-r-- 1 skanto skanto    9075124  1월 19 11:12 NET_SAFETY_P.shx
-rw-rw-r-- 1 skanto skanto      76563  1월 19 11:13 NET_SCENIC_INFO.dbf
-rw-rw-r-- 1 skanto skanto     284252  1월 19 11:13 NET_SCENIC_LINK.dbf
-rw-rw-r-- 1 skanto skanto     152161  1월 19 11:13 NET_SVCAREA.dbf
-rw-rw-r-- 1 skanto skanto    4760321  1월 19 11:13 NET_TD_LINK.dbf
-rw-rw-r-- 1 skanto skanto     561209  1월 19 11:13 NET_TD_PASS.dbf
-rw-rw-r-- 1 skanto skanto      73392  1월 19 11:12 NET_TOLL.dbf
-rw-rw-r-- 1 skanto skanto     274145  1월 19 11:12 NET_TOLLLINK.dbf
-rw-rw-r-- 1 skanto skanto     512865  1월 19 11:04 NET_TURN_P.dbf
-rw-rw-r-- 1 skanto skanto     293092  1월 19 11:04 NET_TURN_P.shp

도로네트워크 데이터를 데이터베이스로 로딩하는 일련의 과정을 배포되는 데이터파일별(위 목록 참고)로 각각 수행해야 전체 작업이 마무리 된다. 이 작업을 자동화 한 Shell 스트립트가 있으며 Git 에 정리된 내용을 참고하면 된다.

Shell Script를 활용한 데이터 로딩 자동화

지금까지 Manual한 방법으로 데이터를 로딩했다. 이 경우 시간도 많이 걸리고 실수로 인한 오류가 발생할 가능성이 높다. Shell Script를 활용하면 Manual하게 처리했던 작업을 자동화 할 수 있다. 아래의 순서대로 진행한다. (로컬에 DB와 데이터가 같이 있다는 가정하에)

“sudo su postgres” 명령어로 postgres 계정으로 변경
원본 데이터 파일을 압축해제한 디렉터리로 이동(이 경우 디렉터리 이름이 “Network_20240118_V10.0_Full”의 형태를 가짐)
해당 디렉터리에 첨부된 Script파일을 다운로드 받은 다음 실행 권한을 준다.
command line에서 install.sh 명령어를 실행한다.

install.sh

#!/bin/bash
# This script loads data files for road network into the database
 
echo ""
echo "##### LOADS ROAD NETWORK DATA INTO THE DATBASE #####"
echo ""
read -p 'Enter the name for the database to create(default: network): ' db
if [ "$db" = "" ]; then
  db="network"
fi
# schema name will be extracted from the current directory name
schema=""
if [[ $(pwd) =~ _[0-9]{8} ]]; then
  schema="v${BASH_REMATCH[0]}"
fi
 
read -p "Enter the name for the schema to create(default: $schema): " _schema
if [ "$_schema" != "" ]; then
  schema="$_schema"
fi
echo ""
echo "The database name to create is '$db'"
echo "The schema name of the database($db) is '$schema'"
 
echo ""
read -p 'If the information is correct hit the [ENTER] key to do the jobs or [Ctrl-C] to exit! '
 
echo ""
echo "Now creates database using the name: $db"
echo "select 'create database $db with encoding = \"UTF8\" template = template0 connection limit = -1' where not exists (select from pg_database where datname = '$db')\gexec" | psql
 
echo ""
echo "Now creates schema using the name: $schema"
echo "create schema if not exists $schema ;" | psql -d "$db"
 
echo ""
echo "Now creates postgis extension..."
echo "create extension postgis;" | psql -d "$db"
 
echo ""
echo "Now inserts CRS(UTMK, KATECH) to extension table..."
# Insert UTMK Coordinates
echo "INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 97165, 'sr-org', 7165, '+proj=tmerc +lat_0=38 +lon_0=127.5 +k=0.9996 +x_0=1000000 +y_0=2000000 +ellps=GRS80 +units=m +no_defs ','PROJCS["UTMK",GEOGCS["GCS_ITRF_2000",DATUM["D_ITRF_2000",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",1000000.0],PARAMETER["False_Northing",2000000.0],PARAMETER["Central_Meridian",127.5],PARAMETER["Scale_Factor",0.9996],PARAMETER["Latitude_Of_Origin",38.0],UNIT["Meter",1.0]]');" | psql -d "$db"
 
# Insert KATECH Coordinates
echo "INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 98030, 'sr-org', 8030, '+proj=tmerc +lat_0=38 +lon_0=128 +k=0.9999 +x_0=400000 +y_0=600000 +ellps=bessel +towgs84=-145.907,505.034,685,0,0,0,0 +units=m +no_defs ', 'PROJCS["Katech",GEOGCS["Bessel 1841",DATUM["unknown",SPHEROID["bessel",6377397.155,299.1528128],TOWGS84[-145.907,505.034,685,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",38],PARAMETER["central_meridian",128],PARAMETER["scale_factor",0.9999],PARAMETER["false_easting",400000],PARAMETER["false_northing",600000],UNIT["Meter",1]]');" | psql -d "$db"
 
files=(*)
idx=1
for file in "${files[@]}"; do
  table="${file:0:(-4)}"
  shp="$table.shp"
  echo ""
  if [[ ${files[@]} =~ $shp ]]; then # if files array contains .shp file
    if [ "$file" = "$shp" ]; then # if the extension of the file is .shp
      echo "### ($idx) Now loads shp data file... [$file]"
      shp2pgsql -D -s 4326 -I -S "$file" "$schema.$table" | psql -d "$db"
      idx=$(expr $idx + 1)
    fi
  elif [ "${file:(-3)}" = "dbf" ]; then
    echo "### ($idx) Now loads dbf data file... [$file]"
    shp2pgsql -D "$file" "$schema.$table" | psql -d "$db"
    idx=$(expr $idx + 1)
  fi
done
 
echo ""
echo "The jobs for loading data into database are all completed!!!"
echo ""
# End of Script

추가 검토해 볼만 한 작업

데이터 로딩작업을 자동화 하기 위해 Shell Script를 많이 활용한 부분이 있다. 이 부분은 Shell Script에 익숙하지 않은 사람이라면 러닝커브가 있을 것으로 예상되며 윈도우 환경이라든가 Shell Script를 실행하기 어려운 환경에서는 활용이 제약적이다. 이런 경우를 대비해서 Java 이용하여 일반 Install 프로그램처럼 데이터 로딩 과정 자동화 프로그램 제작을 고려해 볼만한 하다.

Reference

PostgreSQL for Geospatial Data
데이터 Import (과거 데이터)
- 과거에 작성된 자료로 CP949관련 Characterset 설정은 무시해야 한다. 근래 배포되는 도로네트워크 데이터의 Characterset은 UTF-8로 맞춰져 있다.
Aporia 개발 환경 구축
[Git] 도로네트워크 데이터 db load
- 관련 Script 참고

Supiami

The Hidden Life of TREES

How to load the network data to the Postgresql DB

2024-12-03 skanto Comments 0 Comment

도로네트워크 데이터 DB생성

한국에서 사용하는 좌표계(UTMK, Katech^{자동차연구원}) 등록

Command line 툴을 이용한 데이터 로딩

shp/dbf 파일 로드

Shell Script를 활용한 데이터 로딩 자동화

추가 검토해 볼만 한 작업

Reference

답글 남기기 응답 취소

도로네트워크 데이터 DB생성

한국에서 사용하는 좌표계(UTMK, Katech자동차연구원) 등록

Command line 툴을 이용한 데이터 로딩

shp/dbf 파일 로드

Shell Script를 활용한 데이터 로딩 자동화

추가 검토해 볼만 한 작업

Reference

답글 남기기 응답 취소

한국에서 사용하는 좌표계(UTMK, Katech^{자동차연구원}) 등록