이번 포스팅에서는 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 조회
SELECT * FROM data_import.gray_50m_partial LIMIT 10;

 

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

Posted by R Friend R_Friend

댓글을 달아 주세요

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

 

 

 

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

참고로, 저는 처음에 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 외) 을 참고하였습니다. 

 

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' 데이터셋을 시각화해보면 아래와 같습니다. 

 

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

Posted by R Friend R_Friend

댓글을 달아 주세요

이번 포스팅에서는 Greenplum DB, Postgresql DB에서 중복된 관측치(duplicated observations, duplicated rows)가 있을 경우에 제일 처음나 제일 마지막의 관측치 하나만 남겨놓고 나머지 중복 관측치들은 삭제하여 유일한 관측치만 남기는 2가지 방법을 소개하겠습니다. 


(방법 1) 원래의 테이블에서 중복된 관측치들 중에서 하나만 남기고 나머지 중복된 관측치들은 삭제하기

            : DELETE FROM original_table


(방법 2) 중복된 관측치들중에서 하나씩만 가져와서 새로운 테이블 만들고, 원래의 테이블은 제거(drop)하기

            : CREATE TABLE new_table & DROP TABLE original_table






  (방법 1) 원래의 테이블에서 중복된 관측치들 중에서 하나만 남기고 나머지 중복된 관측치들은 삭제하기

            : DELETE FROM original_table


Greenplum Database에 중복된 관측치가 들어있는 간단한 예제 테이블을 만들어보겠습니다. 'name'과 'price'의 두 개 변수를 기준으로 해서 중복 여부를 판단하여 중복된 관측치를 제거하는 예제입니다. 



drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

) distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);


select * from prod_master;

 





이제 DELETE query를 사용하여 중복된 관측치 중에서 첫번째 것만 남기고 나머지 중복된 관측치들은 제거해보겠습니다. DELETE 문은 'DELETE FROM table_name WHERE [conditio];' 의 형태로 사용합니다. 


이때 주의할 점은 sub query로 row_number() over (partition by ) 라는 window function을 사용해야만 중복된 관측치들 중에서 각각의 "첫번째 관측치"를 남겨놓을 수 있다는 것입니다 (아래 query의 빨간색 부분).  자칫 잘못하면 중복이 된 값은 하나도 남김없이 모조리 삭제하는 실수를 범할 수 있으니 조심하시기 바랍니다. 



delete from prod_master where id in (

select id 

from 

(select id, 

row_number() over (partition by name, price order by id) as row_num 

from prod_master) a

where a.row_num > 1

);

 


[Messages]

DELETE 4 Query returned successfully in 177 msec



select * from prod_master;






혹시 중복된 관측치들 중에서 "가장 앞에 있는"(위의 예시) 관측치 대신에 "가장 뒤에 있는" 관측치를 남기고 나머지 중복된 관측치는 제거하고 싶다면 row_number() over() 의 window function 에서 order by id desc 를 사용해주면 됩니다. 



--- Create a sample table

drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

) distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);



---- keep the last observation in case of duplication

delete from prod_master where id in (

select id 

from 

(select id, 

row_number() over (partition by name, price order by id desc) as row_num 

from prod_master) a

where a.row_num > 1

);


select * from prod_master;


 



위의 방법 1은 원래의 테이블을 그대로 유지한 상태에서 중복된 관측치를 삭제하므로, 새로운 테이블을 만들거나 기존 테이블을 삭제할 필요가 없습니다만, 대용량 데이터를 대상으로 다수의 중복된 관측치를 제거해야 하는 경우 (아래의 방법2 대비 상대적으로) 속도가 느리다는 단점이 있습니다.  대용량 데이터의 경우 빠른 속도로 중복처리하려면 아래의 '방법2'를 고려해보길 권합니다. ('Messages'에 나오는 실행 속도를 비교해보면 아래의 '방법2'가 빠른 것을 알 수 있습니다. 지금 예제야 관측치 7개짜리의 간단한 예제인지라 177 msec vs. 118 msec로 밀리세컨 단위 차이라고 무시할 수도 있겠지만, 데이터가 대용량이 되면 차이가 무시할 수 없게 커질 수 있습니다.)




  (방법 2) 중복된 관측치들중에서 하나씩만 가져와서 새로운 테이블 만들고, 원래의 테이블은 제거하기

            : CREATE TABLE new_table & DROP TABLE original_table


 

--- Create a sample table

drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);


---- keep the first observation in case of duplication by creating a new table

drop table if exists prod_master_unique;

create table prod_master_unique as (

select * from prod_master 

where id NOT IN (

select id

from 

(select id, 

row_number() over (partition by name, price order by id) as row_num 

from prod_master) a

where a.row_num > 1)

) distributed randomly;




[Messages]

SELECT 3 Query returned successfully in 118 msec.

 



select * from prod_master_unique order by id;





-- Drop the original table to save disk storage

drop table prod_master;

 




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


Posted by R Friend R_Friend

댓글을 달아 주세요