[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷)
Greenplum and PostgreSQL Database 2019. 4. 11. 13:22이번 포스팅에서는 공간지리 데이터 포맷 중에서도 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'. : |
(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" [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 |
서두에 KML 데이터 포맷이 Google Earth 에서 2D, 3D로 시각화해볼 수 있다고 소개하였습니다. 실제로 Google Earth 애플리케이션에서 '2.5_day_age.kml' 데이터셋을 시각화해보면 아래와 같습니다.
많은 도움이 되었기를 바랍니다.
