[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기
Greenplum and PostgreSQL Database 2019. 4. 17. 20:44이번 포스팅에서는 PostgreSQL, Greenplum DB의 PostGIS에 raster2pgsql 유틸리티를 사용하여 raster data를 import하는 방법을 소개하겠습니다.
예제로 사용할 데이터는 'Mastering PostGIS' (by Domink 외) 에서 소개된 raster format의 TIFF(Tagged Image File Format) 데이터인 'GRAY_50M_SR_OB.tif' 파일입니다.
데이터 형태는 왼쪽에 보는 바와 같이 4자리의 숫자와 알파벳의 조합으로 되어 있습니다.
이 tif 파일을 탐색기에서 미리보기 해보면 왼쪽에 보는 바와 같이 회색의 세계지도 이미지 이네요.
그럼 먼저, 명령 프롬프트 창에서 다운로드한 'GRAY_50M_SR_OB.tif' 파일을 docker cp 명령어로 Greenplum docker 의 tmp 폴더로 복사하겠습니다.
|
-- (명령 프롬프트 창에서) copy 'GRAY_50M_SR_OB.tif' file to GPDB docker $ docker cp /Users/ihongdon/Documents/PostGIS/data/GRAY_50M_SR_OB/GRAY_50M_SR_OB.tif gpdb-ds:/tmp |
다른 명령 프롬프트 창에서 Docker GPDB 의 gpadmin 계정으로 들어가서 파일이 잘 복사되었는지 확인해보겠습니다.
|
-- (docker gpdb 명령 프롬프트 창에서) raster matadata 읽기 [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 .. -rw-r--r-- 1 501 games 58405694 Apr 8 06:30 GRAY_50M_SR_OB.tif [gpadmin@mdw tmp]$ |
GPDB gpadmin 명령 프롬프트 창에서 gdalinfo 명령어로 TIFF raster 파일의 메타정보를 조회해보겠습니다.
|
-- (명령 프롬프트 창에서) raster 파일의 메타정보 조회 : gdalinfo [gpadmin@mdw tmp]$ gdalinfo GRAY_50M_SR_OB.tif Driver: GTiff/GeoTIFF Files: GRAY_50M_SR_OB.tif Size is 10800, 5400 Coordinate System is: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.257223563, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]] Origin = (-179.999999999999972,90.000000000000000) Pixel Size = (0.033333333333330,-0.033333333333330) Metadata: AREA_OR_POINT=Area TIFFTAG_DATETIME=2014:10:18 09:28:20 TIFFTAG_RESOLUTIONUNIT=2 (pixels/inch) TIFFTAG_SOFTWARE=Adobe Photoshop CC 2014 (Macintosh) TIFFTAG_XRESOLUTION=342.85699 TIFFTAG_YRESOLUTION=342.85699 Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left (-180.0000000, 90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"N) Lower Left (-180.0000000, -90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"S) Upper Right ( 180.0000000, 90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"N) Lower Right ( 180.0000000, -90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"S) Center ( -0.0000000, 0.0000000) ( 0d 0' 0.00"W, 0d 0' 0.00"N) Band 1 Block=10800x1 Type=Byte, ColorInterp=Gray [gpadmin@mdw tmp]$ |
raster2pgsql 유틸리티를 사용하여 (1) 한개의 Raster 데이터셋을 import 하는 방법과, (2) 여러개의 Raster 데이터셋들을 한꺼번에 import 하는 방법으로 나누어서 소개하겠습니다.
|
(1) 한개의 Raster 데이터셋을 raster2pgsql 유틸리티로 import 하기 |
아래처럼 명령 프롬프트 창에서 raster2pgsql 유틸리티로 'GRAY_50M_SR_OB.tif' 파일을 import 하면 'gray_50m_sr_ob' 테이블이 생성됩니다. 더불어서, 'o_2_gray_50m_sr_ob', 'o_4_gray_50m_sr_ob'라는 미리보기 테이블이 같이 생성됩니다. (아래 소개된 SQL 문이 생성, 실행됩니다).
----------------------------------------------------------------------------------------
[ raster2pgsql 인자 설명 ]
- G: 유틸리티에 의해 지원되는 GDAL 포맷 리스트 인쇄
- s: import한 raster 데이터의 SRID 설정
-t: 타일(tile)의 폭 x 높이 크기
-P: 타일(tile)이 같은 차원을 가지도록 오른쪽/ 아래쪽의 모자란 차원만큼을 채워줌(pad)
-d: 테이블 삭제 및 생성(Drops and creates a table)
-a: 기존 테이블에 이어서 데이터 추가(Appends data to an existing table)
-c: 새로운 테이블 생성(Creates a new table)
-p: 준비 모드 켜기. (단지 테이블만 생성되고, 데이터 importing은 안됨)
-F: raster이름의 칼럼 추가
-l: 콤마로 구분된 overview 테이블 생성 (o__raster_table_name 이름)
-I: raster 칼럼에 GIST 공간 인덱스 생성
-C: raster 데이터 importing 후에 raster 칼럼에 표준 제약 설정
Sets the standard constraints on the raster column after the raster is imported.
* reference: https://postgis.net/docs/using_raster_dataman.html
----------------------------------------------------------------------------------------
('| psql' 뒤에 host, port, user, database name 부분에는 각자의 DB환경정보 입력)
|
-- (명령 프롬프트에서) Import a single raster dataset using raster2pgsql [gpadmin@mdw tmp]$ raster2pgsql -s 4326 -C -l 2,4 -F -t 2700x2700 GRAY_50M_SR_OB.tif data_import.gray_50m_sr_ob | psql -h localhost -p 5432 -U gpadmin -d gpadmin
Processing 1/1: GRAY_50M_SR_OB.tif BEGIN NOTICE: CREATE TABLE will create implicit sequence "gray_50m_sr_ob_rid_seq" for serial column "gray_50m_sr_ob.rid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "gray_50m_sr_ob_pkey" for table "gray_50m_sr_ob" CREATE TABLE NOTICE: CREATE TABLE will create implicit sequence "o_2_gray_50m_sr_ob_rid_seq" for serial column "o_2_gray_50m_sr_ob.rid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "o_2_gray_50m_sr_ob_pkey" for table "o_2_gray_50m_sr_ob" CREATE TABLE NOTICE: CREATE TABLE will create implicit sequence "o_4_gray_50m_sr_ob_rid_seq" for serial column "o_4_gray_50m_sr_ob.rid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "o_4_gray_50m_sr_ob_pkey" for table "o_4_gray_50m_sr_ob" CREATE TABLE INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 INSERT 0 1 NOTICE: Adding SRID constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding alignment constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding number of bands constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding pixel type constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding nodata value constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding out-of-database constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding maximum extent constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN addrasterconstraints ---------------------- t (1 row) NOTICE: Adding SRID constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding alignment constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding number of bands constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding pixel type constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding nodata value constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding out-of-database constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding maximum extent constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN addrasterconstraints ---------------------- t (1 row) NOTICE: Adding SRID constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding alignment constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding number of bands constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding pixel type constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding nodata value constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding out-of-database constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding maximum extent constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN addrasterconstraints ---------------------- t (1 row) addoverviewconstraints ------------------------ t (1 row) addoverviewconstraints ------------------------ t (1 row) COMMIT [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$ |
DBeaver db tool에서 data_import.gray_50m_sr_ob 테이블을 조회해보면 아래와 같습니다.
|
SELECT * FROM data_import.gray_50m_sr_ob LIMIT 10; |
|
(2) 여러개의 Raster 데이터셋들을 한꺼번에 raster2pgsql 로 importing 하기 |
예제로 사용하기 위해 gdalwarp 문을 사용하여 원래의 'GRAY_50M_SR_OB.tif' raster 데이터셋을 4개의 raster 데이터셋으로 분할해보겠습니다.
|
-- split into four parts using gdalwarp utility [gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te -180 -90 0 0 GRAY_50M_SR_OB.tif gray_50m_partial_bl.tif Creating output file that is 5400P x 2700L. Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done. [gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te -180 0 0 90 GRAY_50M_SR_OB.tif gray_50m_partial_tl.tif Creating output file that is 5400P x 2700L. Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done. [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te 0 -90 180 0 GRAY_50M_SR_OB.tif gray_50m_partial_br.tif Creating output file that is 5400P x 2700L. Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done. [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te 0 0 180 90 GRAY_50M_SR_OB.tif gray_50m_partial_tr.tif Creating output file that is 5400P x 2700L. Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done. [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$ ls -la total 180572 drwxrwxrwt 1 root root 4096 Apr 16 10:35 . drwxr-xr-x 1 root root 4096 Apr 9 07:11 .. -rw-r--r-- 1 501 games 58405694 Apr 8 06:30 GRAY_50M_SR_OB.tif -rw-rw-r-- 1 gpadmin gpadmin 14602098 Apr 16 10:34 gray_50m_partial_bl.tif -rw-rw-r-- 1 gpadmin gpadmin 14602098 Apr 16 10:35 gray_50m_partial_br.tif -rw-rw-r-- 1 gpadmin gpadmin 14602098 Apr 16 10:34 gray_50m_partial_tl.tif -rw-rw-r-- 1 gpadmin gpadmin 14602098 Apr 16 10:35 gray_50m_partial_tr.tif [gpadmin@mdw tmp]$ |
이제 raster2pgsql 유틸리티로 'gray_50m_partial*.tif' 처럼 파일 이름에 '*'를 사용하여 '*' 부분에 무엇이 들어있든지 간에 '*' 이외의 파일 이름이 같다면 전부 한꺼번에 importing 해보겠습니다. ('| psql' 뒤에 host, port, user, database name 부분에는 각자의 DB환경정보 입력)
|
-- (명령 프롬프트에서) Importing multiple rasters at once [gpadmin@mdw tmp]$ raster2pgsql -s 4326 -C -F -t 2700x2700 gray_50m_partial*.tif data_import.gray_50m_partial | psql -h localhost -p 5432 -U gpadmin -d gpadmin
Processing 1/4: gray_50m_partial_bl.tif BEGIN NOTICE: CREATE TABLE will create implicit sequence "gray_50m_partial_rid_seq" for serial column "gray_50m_partial.rid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "gray_50m_partial_pkey" for table "gray_50m_partial" CREATE TABLE INSERT 0 1 Processing 2/4: gray_50m_partial_br.tif INSERT 0 1 INSERT 0 1 Processing 3/4: gray_50m_partial_tl.tif INSERT 0 1 INSERT 0 1 Processing 4/4: gray_50m_partial_tr.tif INSERT 0 1 INSERT 0 1 INSERT 0 1 NOTICE: Adding SRID constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding scale-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-X constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding blocksize-Y constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding alignment constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding number of bands constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding pixel type constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding nodata value constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding out-of-database constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN NOTICE: Adding maximum extent constraint CONTEXT: SQL statement "SELECT AddRasterConstraints( $1 , $2 , $3 , VARIADIC $4 )" PL/pgSQL function "addrasterconstraints" line 52 at RETURN addrasterconstraints ---------------------- t (1 row) COMMIT [gpadmin@mdw tmp]$ [gpadmin@mdw tmp]$ |
DBeaver db tool에서 'data_import.gray_50m_partial' 테이블을 조회해 보겠습니다. 제일 마지막의 'filename' 칼럼을 보면 'gray_50m_partial_bl.tif', 'gray_50m_partial_br.tif', 'gray_50m_partial_tl.tif', 'gray_50m_partial_tr.tif' 의 4개 부분의 파일들이 들어가 있음을 알 수 있습니다.
|
-- (DBeaver db tool 에서) raster file 조회 |
많은 도움이 되었기를 바랍니다.
