[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷)

이번 포스팅에서는 공간지리 데이터 포맷 중에서도 GML format, MapInfo MIF & TAB format, KML format 등의 벡터 데이터 (vector data)를 GDAL의 ogr2ogr 툴을 사용하여 PostgreSQL, Greenplum DB에 import하는 방법을 소개하겠습니다. 

 

 

 

ogr2ogr 은 GDAL(Geospatial Data Abstraction Library)의 벡터 변환 유틸리티이며, 소스파일 다운로드 및 설치는 아래 링크된 사이트를 참조하세요. 

•  GDAL download: https://trac.osgeo.org/gdal/wiki/DownloadSource 
• GDAL 설치 가이드 :  https://trac.osgeo.org/gdal/wiki/BuildingOnUnix

참고로, 저는 처음에 GDAL 1.x 버전으로 깔았더니 아래처럼 importing 에 필요한 driver 를 찾을 수 없다는 에러가 나더군요. 그래서 GDAL2.4.1 최신 버전으로 새로 설치를 했더니 문제가 해결되었습니다. (Thanks Jack!)

ERROR 1: Unable to find driver PostgreSQL'.   The following drivers are available:     ->PCIDSK' -> JP2OpenJPEG'     ->PDF' -> ESRI Shapefile'     ->MapInfo File' -> UK .NTF'     ->OGR_SDTS' -> S57'     ->DGN' -> OGR_VRT'     ->REC' -> Memory'     ->BNA' -> CSV'     ->GML' -> GPX'     ->KML' -> GeoJSON'          :

 

(1) GML 포맷의 공간지리 벡터 데이터 Import 하기

 

포스팅에 사용한 샘플 데이터(sx9090.gml 주소 데이터)와 예제 코드는 'Mastering PostGIS' (by Dominikwicz 외) 을 참고하였습니다. 

• sx9090.gml 파일 다운로드: https://github.com/PacktPublishing/Mastering-PostGIS/tree/master/Chapter01/data/os-addressbase-gml-sample-data  GML 데이터 포맷은 아래에 sx9090.gml 파일을 열어서 화면캡쳐 해놓은 것처럼 Geometry 데이터를 XML(eXtensible Markup Language) 형태로 저장해놓은 파일 형식입니다. 

 

docker로 Greenplum DB 설치하고 PostGIS 설치, 시작하는 방법은 https://rfriend.tistory.com/435 를 참고하세요. 

 

자, 샘플 데이터를 다운로드 했다면 이제 시작해볼까요?

먼저 명령 프롬프트 창에서 sx9090.gml 파일을 docker cp로 Greenplum DB w/PostGIS 의 tmp 경로로 복사를 하겠습니다. 

-- (명령 프롬프트에서) sx9090.gml 파일을 docker gpdb에 복사 ihongdon-ui-MacBook-Pro:data ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/os-addressbase-gml-sample-data/sx9090.gml gpdb-ds:/tmp ihongdon-ui-MacBook-Pro:data ihongdon$

 

다른 명령 프롬프트 창에서 Docker의 Greenplum DB의 gpadmin 계정으로 tmp 폴더를 확인해보면 sx9090.gml 파일이 잘 복사되었음을 확인할 수 있습니다. 

-- (docker gpdb 명령 프롬프트에서) importing GML data [gpadmin@mdw gdal-2.4.1]$ cd /tmp [gpadmin@mdw tmp]$ ls 2.5_day_age.kml     a.sql                         gdal-2.4.1                 hsperfdata_root   ne_110m_coastline.dbf  ne_110m_coastline.shx  sx9090.gml [gpadmin@mdw tmp]$

 

명령 프롬프트 창에서 ogrinfo 유틸리티로 sx9090.gml 데이터셋의 메타데이터(metadata) 정보를 알아보겠습니다. 2015년에 GeoPlace가 만든 Ordnance Survey의 주소(address) 공간지리 데이터셋이네요. 

[gpadmin@mdw tmp]$ ogrinfo sx9090.gml INFO: Open of `sx9090.gml'       using driver `GML' successful. Metadata:   DESCRIPTION=Ordnance Survey (c) Crown Copyright. All rights reserved, 2015 and produced by GeoPlace 1: Address (Point) [gpadmin@mdw tmp]$

 

ogrinfo 유틸리티로 '1: Address (Point)' layer 정보를 더 자세히 살펴보겠습니다. (ogrinfo의 -so 파라미터는 요약 정보만 보여달라는 의미임) 

gml_id를 key로 하고, 총 22개의 칼럼을 가진 공간지리 데이터셋이네요. 

[gpadmin@mdw tmp]$ ogrinfo sx9090.gml Address -so INFO: Open of `sx9090.gml'       using driver `GML' successful. Metadata:   DESCRIPTION=Ordnance Survey (c) Crown Copyright. All rights reserved, 2015 and produced by GeoPlace Layer name: Address Geometry: Point Feature Count: 42861 Extent: (-3.560100, 50.699470) - (-3.488340, 50.744770) Layer SRS WKT: GEOGCS["ETRS89",     DATUM["European_Terrestrial_Reference_System_1989",         SPHEROID["GRS 1980",6378137,298.257222101,             AUTHORITY["EPSG","7019"]],         TOWGS84[0,0,0,0,0,0,0],         AUTHORITY["EPSG","6258"]],     PRIMEM["Greenwich",0,         AUTHORITY["EPSG","8901"]],     UNIT["degree",0.0174532925199433,         AUTHORITY["EPSG","9122"]],     AUTHORITY["EPSG","4258"]] gml_id: String (0.0) NOT NULL uprn: Real (0.0) osAddressTOID: String (20.0) udprn: Integer (0.0) subBuildingName: String (25.0) buildingName: String (36.0) thoroughfare: String (27.0) postTown: String (6.0) postcode: String (7.0) postcodeType: String (1.0) rpc: Integer (0.0) country: String (1.0) changeType: String (1.0) laStartDate: String (10.0) rmStartDate: String (10.0) lastUpdateDate: String (10.0) class: String (1.0) buildingNumber: Integer (0.0) dependentLocality: String (27.0) organisationName: String (55.0) dependentThoroughfare: String (27.0) poBoxNumber: Integer (0.0) doubleDependentLocality: String (21.0) departmentName: String (37.0) [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$

ogr2ogr 로 sm9090.gml 데이터셋을 PostgreSQL, Greenplum DB에 import 해보겠습니다. 아래 ogr2ogr에서 사용한 파라미터들의 기능은 아래와 같으며, 이 외에 ogr2ogr 의 여러 파라미터 기능은 https://www.gdal.org/ogr2ogr.html 를 참고하세요. DB접속 정보는 각자 자신의 host, port, user, dbname 을 설정해주시면 됩니다. 

• -f : 아웃풋의 포맷이며, PostGIS로 importing할 경우 -f "PostgreSQL" 이라고 해주면 됩니다. 

• -nln : Importing 할 DB 스키마와 테이블 이름 (예: data_import 스키마의 osgb_address_base_gml 테이블 이름)

• geomfield : 공간 필터가 동작하는 geometry field의 이름

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL"  PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin"  sx9090.gml  -nln data_import.osgb_address_base_gml -geomfield geom [gpadmin@mdw tmp]$

 

이제 DBeaver query tool에서 data_import.osgb_address_base_gml 테이블을 조회해보겠습니다. 

SELECT * FROM data_import.osgb_address_base_gml ORDER BY gml_id LIMIT 10;

 

SELECT gml_id, uprn, osaddresstoid, wkb_geometry  FROM data_import.osgb_address_base_gml  ORDER BY gml_id  LIMIT 10;

 

 

(2) MIF 포맷 (MapInfo formats) 데이터셋을 ogr2ogr 유틸리티로 PostGIS에 Import 하기

다음으로 MIF 포맷(MapInfo formats)데이터셋을 import 하는 방법을 소개할텐데요, 위에서 GML 포맷 데이터 import하는 방법과 동일합니다. 먼저 명령 프롬프트 창에서 docker cp 를 사용해서 EX_sample.mif 이름의 MIF 파일을 docker GPDB로 복사해서 넣겠습니다. (VM 환경에서 GPDB 사용 시 scp 로 파일 복사)

-- (명령 프롬프트 창에서) MIF 파일을 docker gpdb로 복사해서 넣기 ihongdon-ui-MacBook-Pro:data ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/os-code-point-polygons-mif-sample-data/EX_sample.mif  gpdb-ds:/tmp ihongdon-ui-MacBook-Pro:data ihongdon$

 

다른 명령 프롬프트 창에서 docker GPDB의 gpadmin 계정으로 들어가서 /tmp 경로에 들어있는 파일을 조회해보면 EX_sample.mif 파일이 잘 복사되었음을 알 수 있습니다. 

--  [gpadmin@mdw]$ cd /tmp [gpadmin@mdw tmp]$ ls -la total 50844 drwxrwxrwt 1 root    root        4096 Apr 10 01:42 . drwxr-xr-x 1 root    root        4096 Apr  9 07:11 .. drwxrwxrwt 2 root    root        4096 Sep 11  2017 .ICE-unix drwxrwxrwt 2 root    root        4096 Sep 11  2017 .Test-unix drwxrwxrwt 2 root    root        4096 Sep 11  2017 .X11-unix drwxrwxrwt 2 root    root        4096 Sep 11  2017 .XIM-unix drwxrwxrwt 2 root    root        4096 Sep 11  2017 .font-unix srwxrwxr-x 1 gpadmin gpadmin        0 Mar 22 07:19 .s.GPMC.sock srwxrwxrwx 1 gpadmin gpadmin        0 Apr 10 01:20 .s.PGSQL.40000 -rw------- 1 gpadmin gpadmin       25 Apr 10 01:20 .s.PGSQL.40000.lock srwxrwxrwx 1 gpadmin gpadmin        0 Apr 10 01:20 .s.PGSQL.40001 -rw------- 1 gpadmin gpadmin       25 Apr 10 01:20 .s.PGSQL.40001.lock srwxrwxrwx 1 gpadmin gpadmin        0 Apr 10 01:20 .s.PGSQL.5432 -rw------- 1 gpadmin gpadmin       25 Apr 10 01:20 .s.PGSQL.5432.lock -rw-r--r-- 1     501 games    3624013 Apr  8 06:12 EX_sample.mif [gpadmin@mdw tmp]$

 

명령 프롬프트 창에서 ogrinfo 유틸리티로 EX_sample.mif의 메타 데이터와 요약 설명을 알아보겠습니다. 

[gpadmin@mdw tmp]$ ogrinfo ./EX_sample.mif INFO: Open of `./EX_sample.mif'       using driver `MapInfo File' successful. 1: EX_sample [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$ ogrinfo ./EX_sample.mif EX_Sample -so INFO: Open of `./EX_sample.mif'       using driver `MapInfo File' successful. Layer name: EX_sample Geometry: Unknown (any) Feature Count: 4142 Extent: (281282.800000, 85614.570000) - (300012.000000, 100272.000000) Layer SRS WKT: PROJCS["unnamed",     GEOGCS["unnamed",         DATUM["OSGB_1936",             SPHEROID["Airy 1930",6377563.396,299.3249646],             TOWGS84[375,-111,431,0,0,0,0]],         PRIMEM["Greenwich",0],         UNIT["degree",0.0174532925199433]],     PROJECTION["Transverse_Mercator"],     PARAMETER["latitude_of_origin",49],     PARAMETER["central_meridian",-2],     PARAMETER["scale_factor",0.9996012717],     PARAMETER["false_easting",400000],     PARAMETER["false_northing",-100000],     UNIT["Meter",1.0]] POSTCODE: String (8.0) UPP: String (20.0) PC_AREA: String (2.0) [gpadmin@mdw tmp]$

 

준비가 되었으니 ogr2ogr 로 EX_sample.mif 데이터셋을 data_import.osgb_code_point_polygons_mif 라는 이름으로 Greenplum DB에 import 하겠습니다. (아래 PG: "xxxx" 안의 DB 설정 정보는 각자 자신의 것으로 입력해주면 됨)

• -lco GEOMETRY_NAME : 레이어 생성 옵션 (디폴트 wkb_geometry)
• -s_srs : input SRID
• -a_srs : output SRID

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin" EX_sample.mif -nln data_import.osgb_code_point_polygons_mif -lco GEOMETRY_NAME=geom -a_srs EPSG:27700 [gpadmin@mdw tmp]$

 

DB query tool에서 data_import.osgb_code_point_polygons_mif 테이블을 조회해보면 아래와 같이 POLYGON 공간지리 정보가 들어있는 테이블이 잘 생성되었음을 알 수 있습니다. 

-- DBeaver에서 조회 SELECT * FROM data_import.osgb_code_point_polygons_mif ORDER BY ogc_fid LIMIT 10;

 

 

(3) KML(Keyhole Markup Language) 데이터셋을 ogr2ogr 유틸리티로 PostgreSQL, Greenplum DB에 import 하기

KML (Keyhole Markup Language) 데이터셋은 Google Earth에서 2D 혹은 3D로 웹브라우저 상에서 시각화할 수 있는 XML 기반의 공간지리 데이터 포맷입니다. 

 

PostgreSQL, Greenplum DB에 KML 포맷 데이터를 Import 할 때도 GDAL의 ogr2ogr 유틸리티를 사용합니다. 

 

먼저, 명령 프롬프트 창에서 docker cp 로 '2.5_day_age.kml' 데이터셋을 Greenplum DB docker container로 복사하겠습니다. 

-- (1) Copy '2.5_day_age.kml' file to GPDB ihongdon-ui-MacBook-Pro:~ ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/usgs-earthquakes/2.5_day_age.kml  gpdb-ds:/tmp ihongdon-ui-MacBook-Pro:~ ihongdon$

 

 

다음으로, 다른 명령 프롬프트에서 Greenplum gpadmin 계정으로 들어가서 파일이 잘 복사가 되었는지 확인해보겠습니다. 

-- (2) (GPDB 명령 프롬프트 창에서) orginfo => 4개의 layer가 있음 [gpadmin@mdw tmp]$ ls -la total 123532 drwxrwxrwt  1 root    root        4096 Apr 10 13:13 . drwxr-xr-x  1 root    root        4096 Apr  9 07:11 .. drwxrwxrwt  2 root    root        4096 Sep 11  2017 .ICE-unix drwxrwxrwt  2 root    root        4096 Sep 11  2017 .Test-unix drwxrwxrwt  2 root    root        4096 Sep 11  2017 .X11-unix drwxrwxrwt  2 root    root        4096 Sep 11  2017 .XIM-unix drwxrwxrwt  2 root    root        4096 Sep 11  2017 .font-unix srwxrwxr-x  1 gpadmin gpadmin        0 Mar 22 07:19 .s.GPMC.sock srwxrwxrwx  1 gpadmin gpadmin        0 Apr 16 05:36 .s.PGSQL.40000 -rw-------  1 gpadmin gpadmin       27 Apr 16 05:36 .s.PGSQL.40000.lock srwxrwxrwx  1 gpadmin gpadmin        0 Apr 16 05:36 .s.PGSQL.40001 -rw-------  1 gpadmin gpadmin       27 Apr 16 05:36 .s.PGSQL.40001.lock srwxrwxrwx  1 gpadmin gpadmin        0 Apr 16 05:36 .s.PGSQL.5432 -rw-------  1 gpadmin gpadmin       27 Apr 16 05:36 .s.PGSQL.5432.lock -rw-r--r--  1 gpadmin gpadmin     4787 Apr  8 06:21 2.5_day.csv -rw-r--r--  1     501 games      30548 Apr  8 06:21 2.5_day_age.kml [gpadmin@mdw tmp]$

 

 

ogrinfo 명령어로 '2.5_day_age.kml' 데이터의 메타정보를 확인해보겠습니다. Layer가 총 4개 있고, 3D Point 정보가 들어있는 KML 포맷을 공간지리 데이터셋임을 알 수 있습니다. 

-- (3) metadata info. [gpadmin@mdw tmp]$ ogrinfo 2.5_day_age.kml INFO: Open of `2.5_day_age.kml'       using driver `KML' successful. 1: Magnitude 5 (3D Point) 2: Magnitude 4 (3D Point) 3: Magnitude 3 (3D Point) 4: Magnitude 2 (3D Point) [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$

 

ogrinfo 2.5_day_age.kml -al -so 로 메타정보의 4개 Layer에 대한 상세 정보를 확인해보겠습니다. 

-- (4) review metadata for each layer at once in depth [gpadmin@mdw tmp]$ ogrinfo 2.5_day_age.kml -al -so INFO: Open of `2.5_day_age.kml'       using driver `KML' successful. Layer name: Magnitude 5 Geometry: 3D Point Feature Count: 2 Extent: (-101.000100, -36.056300) - (120.706400, 13.588200) Layer SRS WKT: GEOGCS["WGS 84",     DATUM["WGS_1984",         SPHEROID["WGS 84",6378137,298.257223563,             AUTHORITY["EPSG","7030"]],         AUTHORITY["EPSG","6326"]],     PRIMEM["Greenwich",0,         AUTHORITY["EPSG","8901"]],     UNIT["degree",0.0174532925199433,         AUTHORITY["EPSG","9122"]],     AUTHORITY["EPSG","4326"]] Name: String (0.0) Description: String (0.0) Layer name: Magnitude 4 Geometry: 3D Point Feature Count: 8 Extent: (-93.869400, -30.966800) - (127.154100, 41.012000) Layer SRS WKT: GEOGCS["WGS 84",     DATUM["WGS_1984",         SPHEROID["WGS 84",6378137,298.257223563,             AUTHORITY["EPSG","7030"]],         AUTHORITY["EPSG","6326"]],     PRIMEM["Greenwich",0,         AUTHORITY["EPSG","8901"]],     UNIT["degree",0.0174532925199433,         AUTHORITY["EPSG","9122"]],     AUTHORITY["EPSG","4326"]] Name: String (0.0) Description: String (0.0) Layer name: Magnitude 3 Geometry: 3D Point Feature Count: 6 Extent: (-155.372167, 18.242700) - (-64.691100, 36.431400) Layer SRS WKT: GEOGCS["WGS 84",     DATUM["WGS_1984",         SPHEROID["WGS 84",6378137,298.257223563,             AUTHORITY["EPSG","7030"]],         AUTHORITY["EPSG","6326"]],     PRIMEM["Greenwich",0,         AUTHORITY["EPSG","8901"]],     UNIT["degree",0.0174532925199433,         AUTHORITY["EPSG","9122"]],     AUTHORITY["EPSG","4326"]] Name: String (0.0) Description: String (0.0) Layer name: Magnitude 2 Geometry: 3D Point Feature Count: 9 Extent: (-154.990005, 17.871900) - (-65.022300, 63.207400) Layer SRS WKT: GEOGCS["WGS 84",     DATUM["WGS_1984",         SPHEROID["WGS 84",6378137,298.257223563,             AUTHORITY["EPSG","7030"]],         AUTHORITY["EPSG","6326"]],     PRIMEM["Greenwich",0,         AUTHORITY["EPSG","8901"]],     UNIT["degree",0.0174532925199433,         AUTHORITY["EPSG","9122"]],     AUTHORITY["EPSG","4326"]] Name: String (0.0) Description: String (0.0) [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$

 

마지막으로, 명령 프롬프트 창에서 ogr2ogr 유틸리티로 PosgreSQL, Greenplum DB에 KML 파일을 Import 해보겠습니다. (사용하고 있는 DB의 host, port, user, DBname 으로 설정 변경해주세요.) 

제일 마지막에 '-append' 인자는 '2.5_day_age.kml' 데이터셋의 4개 Layer를 하나씩 순차적으로 읽어서 먼저 읽은 데이터셋 뒤에 붙여넣기로 Import 하라는 뜻입니다. ('-append' 인자를 추가하지 않으면 기존에 테이블이 존재한다는 에러 메시지가 뜹니다).  아래처럼 Warning 메시지가 나왔으면 잘 Import 가 된 것입니다. 

-- (5) Import KML dataset to GPDB [gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin" 2.5_day_age.kml -nln data_import.usgs_earthquakes_kml -lco GEOMETRY_NAME=geom -append Warning 1: Layer creation options ignored since an existing layer is          being appended to. Warning 1: Layer creation options ignored since an existing layer is          being appended to. Warning 1: Layer creation options ignored since an existing layer is          being appended to. [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$

 

데이터가 잘 Import 되었으니 DBeaver DB tool에서 SQL query로 데이터를 조회해 보겠습니다. 

-- (DBeaver tool에서) Select KML dataset SELECT * FROM data_import.usgs_earthquakes_kml LIMIT 10;

 

서두에 KML 데이터 포맷이 Google Earth 에서 2D, 3D로 시각화해볼 수 있다고 소개하였습니다. 실제로 Google Earth 애플리케이션에서 '2.5_day_age.kml' 데이터셋을 시각화해보면 아래와 같습니다. 

 

많은 도움이 되었기를 바랍니다.