'공간지리 shape files을 PostgreSQL DB에 import하는 방법'에 해당되는 글 1건

  1. 2019.04.11 [Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법

이번 포스팅에서는 공간지리 데이터 포맷 중에서 SHP, SHX, DBF, PRJ 로 이루어진 shape files 를 shp2pgsql 툴을 사용하여 PostgreSQL, Greenplum DB로 importing 하는 방법을 소개하겠습니다. 

 

PostgreSQL을 설치하였다면 바로 shp2pgsql 툴을 사용할 수 있습니다. 

 

 

예제 데이터셋은 https://github.com/PacktPublishing/Mastering-PostGIS/tree/master/Chapter01/data/ne_110m_coastline 에서 다운로드한 'ne_110m_coastline.shp', 'ne_110m_coastline.shx', 'ne_110m_coastline.dbf', 'ne_110m_coastline.prj' 의 4개 파일을 사용하였으며, 코드는 'Mastering PostGIS' (by Dominik Mikiewicz 외)를 참조하였습니다. 

 

shp2pgsql 은 (1) shapefile data를 import하는 SQL문을 만들어주고, (2) shapefile data를 import하는 SQL문을 바로 psql로 보내서 import 해주는 command-line utility 입니다.  말이 좀 어려운데요, 아래 예제를 직접 보면 이해가 쉬울 듯 합니다. 

 

Greenplum DB Docker 이미지에 PostGIS 설치해서 Greenplum 시작하는 방법은 https://rfriend.tistory.com/435 를 참고하세요. 

 

(1) shp2pgsql로 shapefile data를 import하는 SQL문 만들기

먼저, 명령 프롬프트 창에서 'ne_110m_coastline.shp', 'ne_110m_coastline.shx', 'ne_110m_coastline.dbf', 'ne_110m_coastline.prj'의 4개 shape files 를 docker cp 명령문을 사용하여 Greenplum docker의 gpdb-ds:/tmp 폴더로 복사하겠습니다. (만약 VMware를 이용해서 Greenplum DB를 설치해서 사용 중이라면 scp 명령문으로 GPDB 안으로 파일 복사)

-- (1) 명령 프롬프트 창에서 shape files 를 docker gpdb-ds:/tmp 경로로 복사 

ihongdon-ui-MacBook-Pro:~ ihongdon$

ihongdon-ui-MacBook-Pro:~ ihongdon$ cd /Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ ls

ne_110m_coastline.dbf ne_110m_coastline.prj ne_110m_coastline.shp ne_110m_coastline.shx

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp 
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.shp   
  gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp 
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.dbf  
  gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp 
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.shx  
  gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp 
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.prj  
  gpdb-ds:/tmp

 

다른 명령 프롬프트 창에서 GPDB에 4개의 shape files 이 잘 들어갔는지 확인해보겠습니다. 

[gpadmin@mdw /]$

[gpadmin@mdw /]$ cd tmp

[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

-rw-r--r--  1     501 games       3179 Apr  8 06:02 ne_110m_coastline.dbf

-rw-r--r--  1     501 games        147 Apr  8 06:03 ne_110m_coastline.prj

-rw-r--r--  1     501 games      89652 Apr  8 06:03 ne_110m_coastline.shp

-rw-r--r--  1     501 games       1172 Apr  8 06:04 ne_110m_coastline.shx

[gpadmin@mdw tmp]$

 

이제 shp2pgsql 유틸리티를 이용해서 data_import.ne_110m_coastline.sql 라는 이름의 ne_coastline shape files 를 import 하는 SQL 문을 만들어보겠습니다. 

-- (2) 명령 프롬프트 창 docker gpdb-ds의 gpadmin@mdw 에서 shp2pgsql 실행하여 sql 생성

[gpadmin@mdw tmp]$ shp2pgsql -s 4326 ne_110m_coastline data_import.ne_coastline  > data_import.ne_coastline.sql

Shapefile type: Arc

Postgis type: MULTILINESTRING[2]

[gpadmin@mdw tmp]$

[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
-rw-rw-r--  1 gpadmin gpadmin   184004 Apr  8 08:03 data_import.ne_coastline.sql
-rw-r--r--  1     501 games       3179 Apr  8 06:02 ne_110m_coastline.dbf
-rw-r--r--  1     501 games        147 Apr  8 06:03 ne_110m_coastline.prj
-rw-r--r--  1     501 games      89652 Apr  8 06:03 ne_110m_coastline.shp
-rw-r--r--  1     501 games       1172 Apr  8 06:04 ne_110m_coastline.shx
[gpadmin@mdw tmp]$

 

다른 명령 프롬프트 창에서 docker GPDB에 만들어진 data_import.ne_coastline.sql 파일을 로컬로 복사해서 SQL 문이 어떻게 생겼는지 눈으로 확인을 해보겠습니다. 아래에 보는 바와 같이 CREATE TABLE "data_import"."ne_coastline" (gid serial, scalerank numeric(10,0), featurecla varchar(12)); ALTER TABLE data_import.ne_coastline ADD PRIMARY KEY (gid); SELECT AddGeometryColumn(data_import.ne_coastline, geom, 4326, MULTILINESTRING, 2); INSERT INTO data_import.ne_coastline VALUES (); 와 같이 우리가 이미 알고 있는 테이블 생성과 데이터 삽입 표준 SQL query 문이 자동으로 만들어졌음을 확인할 수 있습니다.  이 SQL 문을 실행하면 테이블 생성부터 데이터 importing까지 일괄로 수행이 됩니다. 

 

* SQL 파일 첨부 : 

data_import.ne_coastline.sql.txt
0.18MB

-- (3) (optional) 다른 명령 프롬프트 창에서 gpdb-ds:/tmp/data_import.ne_coastline.sql 를 docker gpdb에서 밖으로 복사하여 sql query 확인해 보기

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp  gpdb-ds:/tmp/data_import.ne_coastline.sql /Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$

 

 

-- sql query문을 열어서 확인해보면 아래와 같음

SET CLIENT_ENCODING TO UTF8;

SET STANDARD_CONFORMING_STRINGS TO ON;

BEGIN;

CREATE TABLE "data_import"."ne_coastline" (gid serial,

"scalerank" numeric(10,0),

"featurecla" varchar(12));

ALTER TABLE "data_import"."ne_coastline" ADD PRIMARY KEY (gid);

SELECT AddGeometryColumn('data_import','ne_coastline','geom','4326','MULTILINESTRING',2);

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('1','Coastline','0105000020E61000000100000001020000000B000000BFA9980AD07664C095CA366A1FA653C04B24ADB8626364C0700E7B2E4B8E53C03EE93FF8D72764C0BF309DD0549853C02960B7EFE00764C02CDFE4B164AC53C07CB8A9DB6FEF63C08FDFE431F7C253C0A4D39170A9E663C089F492F9CEDF53C09E264A4F152464C0165BF9DF96E853C0FB978739134E64C026353A8703D253C01A655486E06064C0C3343A0771BB53C0B2C4809F216264C0974C8585ADB753C0BFA9980AD07664C095CA366A1FA653C0');

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('0','Coastline','0105000020E61000000100000001020000000C000000D0347456A2CA18C05BC1C65E0CEF4A403FFBA3ECC62118C08AB5EFBA9B934A4010BA8804CA271BC09650268B4B214A40D09F77358C1F21C036A1DEA9ABD5494040720A9544F423C08AA1DEA904E9494088D170FB225522C0BCBB2928AC6E4A40088BF249866023C04F137F7DD0F04A40F0F906F8EDA720C07E8CF6F40E554B40D08D1B64E6491EC028FE33FFD8904B4040B24E9775EF1AC0BA12E24620964B40B0E392DBD5A516C0BAA0A43CFD464B40D0347456A2CA18C05BC1C65E0CEF4A40');

 

.... 너무 길어서 중간 생략함 .....

 

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('0','Coastline','0105000020E6100000010000000102000000840000008881E7DEC36147C090CC237F30A85440A4E4D53906B445C084177D0569CE5440E0965643E2F243C02AD9B11188CB544044C49448A24F43C09E779CA223E354405026FC523F8B41C0C8EA56CF49E95440608E1EBFB7193BC08842041C42E15440905DA27A6BD834C02615C61682AE5440F8B7921D1BB136C06C2BF697DD955440E0B298D87C843AC0E2E995B20C9354405866666666E63FC0CCCCCCCCCC8C5440102C0E677E653FC094E34EE96081544098CADB114EDB3BC0149161156F88544050205ED72FD838C0B83B6BB75D7254408821AB5B3DE736C0F8BD4D7FF6855440E87C3F355E1236C04C4F58E2016F544020139B8F6B2B37C06CF12900C6495440F8CD3637A69F34C0FAB4C35F9361544000E292E34E892FC00C4FAF94657A544010117008558A29C0B22E6EA3016E54404029CB10C76A28C0765E6397A852544048910A630B4930C0CA9717601F2554408899999999D930C06A6666666616544058C47762D60B34C0AA315A47550B544040A2B437F8BA31C09E508880430854406066666666E632C09C99999999D95340D00182397AB433C0E288B5F814B05340F8DF4A766CAC33C05E6397A8DE685340E0E995B20C7932C05CD3BCE3143F5340F860E0B9F70834C07E130A11703C53402098A3C7EFAD35C07E6132553028534018938C9C85D533C0141956F146065340A89C4B71559933C0C03E3A75E5CF524058D72FD80DAB34C03C454772F9C95240309FE579705F33C0BA973446EB92524018A14ACD1E9835C0340C1F11538E5240C8CD70033E6F34C028A5A0DB4B745240B86ED8B628C334C084640113B85D5240480B5EF4152C36C0849ECDAACF53524040D3D9C9E09037C0427E6FD39F535240E00C1AFA275036C0BA019F1F4628524008D847A7AE4C36C052616C21C80B5240302B4D4A414738C0ACBB79AA432652403809336DFFCA38C0EA482EFF21155240A86F99D3657137C0A49C685721055240E8940ED6FF2136C09A9EB0C403DE5140E892E34EE9C035C0AC2B9FE579AA5140B8AC1743398937C0A21A2FDD249E5140C037DBDC984E38C07CD66EBBD0B65140C851F2EA1C8B39C074A25D8594DB5140B00C71AC8B3339C0B685200725B05140084CE0D6DD5C3AC08C7615527E8E5140C874763238BA37C0C408E1D1C68B514068A6ED5F595936C0FC88981249885140F8C01C3D7E0739C01E0DE02D90505140E802ECA353BF3BC0DE8442041C1E5140F8FC304278AC3EC03641D47D000851403870CE88D2C63FC0B28009DCBA075140DCB5847CD06740C01C4CC3F011EF5040D48C45D3D91941C01C7233DC80AB5040385C72DC292D42C07A832F4CA67E5040041C42959A8542C04287F9F2027C5040B42E6EA3013043C028A5A0DB4B6C50401C9430D3F6E743C0B0777FBC575D5040E8ACDD76A15544C0C4E78711C2355040DCB06D51665744C05A5F24B4E5085040A8605452279844C064D0D03FC1BD4F40887F9F71E16845C03C8AE59656574F4094C6681D553545C01CFE9AAC51F34E40A00F5D50DF6E45C0444C89247A894E40386744696FB045C02015C616820C4E40C4CCCCCCCC6446C088E63A8DB4044E406CF59CF4BE2147C0642A6F47386D4E40B8679604A82148C0E4F3C308E16D4E4004ADC090D59D48C0D0D79E5912B44E4098D2C1FA3FF348C01C4CC3F011314F408C1804560ED149C08CC43D963ED04F40446E861BF0114AC05AA31EA2D11150404C33164D67234AC0024D840D4F4B504024895E46B1D44AC0686AD95A5F865040C0120F289BA64AC0AAC64B3789B55040F81CE6CB0BFC4AC05851836918CC5040CC7F48BF7D7D4AC01AD82AC1E2165140C4E9B298D8BC49C034F44F70B12E5140C022F8DF4A8A49C0660113B87549514078CF0F23846F49C0AEADD85F767B5140A85B3D27BD014AC0CA293A92CB64514064E42CEC69474AC046B79734465B5140F0BEF1B567BA4AC0FCD478E92652514080B2295778574BC00EE544BB0A67514028D6E25300604BC00A850838849251408C3F1878EE2D4BC0BA2DCA6C90B4514018A3755435B74AC0FE1D8A027DB55140406E861BF0B149C08E7A884677A45140F0940ED6FF8D4AC078A52C431CCD51409CFEEC478A004BC0B4AC342905E35140F8FFFFFFFF7F4BC040C3A0B304DA5140045053CBD6EA4BC06E4C4F58E2E95140342861A6ED5B4BC0F051B81E852552402CB05582C5A94BC09AE7C1DD593D5240FC449E245D0F4CC000FBE8D495695240940035B56CA94CC010EA5BE674AD5240344A5E9D634C4DC0C24351A04FC65240AC76DB85E64A4DC0EC4CA1F31AE1524074B0FECF61A24EC0EEF0D7648D06534034BD529621B24FC098900F7A360B53404C07EBFF1C8450C07C62D68BA108534068300DC3472051C072693524EE035340B8AF03E78C6A51C0FAA9F1D24D1853407416F6B4C3D951C0826E2F698C4053407A1EDC9DB53151C0C22B82FFAD54534004486DE2E4B050C09CBB96900F5853401C81785DBFC251C00EF9A067B36853405CBA490C025352C0DE334B02D4825340B8E82B48334A52C0C0A94885B19B53403E2CD49AE65751C0FA1D8A027DBA53401851DA1B7C6D50C072B6B9313DD9534028CB10C7BA5450C08872A25D85F05340921D1B81780151C0EA305F5E800754408C8D40BCAEC950C0DAA7E331032154404C62105839D84FC0BCA94885B14D5440E86F4221021E4FC0B6AF03E78C545440543BFC3559534FC0CE0BB08F4E71544034B9DFA128244EC0868A71FE26825440E8F0D7648D9A4CC082828B15358C5440D0F6AFAC34114BC028E8F692C68C54401849F4328A854AC092831266DA785440C8BE2B82FF3149C03AA06CCA159C54409CE1067C7E0048C00E9DD7D825845440906A9F8EC74C47C0E6A90EB9197F544070E7FBA9F14246C0E81DA7E8486A5440ECC039234A7347C05A04FF5BC98C54408881E7DEC36147C090CC237F30A85440');

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('3','Country','0105000020E6100000010000000102000000060000006666666666A65AC06766666666665240713D0AD7A3505AC0295C8FC2F56852400000000000205AC07B14AE47E15A5240B91E85EB51585AC0713D0AD7A33052405C8FC2F528BC5AC03E0AD7A3705D52406666666666A65AC06766666666665240');

COMMIT;

 

 

 

(2) shp2pgsql로 shapefile data를 import하는 SQL문을 psql로 보내서 import하기

Greenplum docker 명령 프롬프트 창에서 shp2pgsql 유틸리티를 사용해서 (1)번에서 만든 SQL문을 psql로 보내서 실행시켜 보겠습니다. (아래에서 각자 자신의 Greenplum DB 혹은 PostgreSQL DB의 host(-h), port(-p), user(-U), database(-d) 이름을 설정해 줌)

[gpadmin@mdw tmp]$ shp2pgsql -s 4326 ne_110m_coastline data_import.ne_coastline 
| psql -h localhost -p 5432 -U gpadmin -d gpadmin

Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
SET
SET
BEGIN
NOTICE:  CREATE TABLE will create implicit sequence "ne_coastline_gid_seq" for serial column "ne_coastline.gid"
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'gid' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "ne_coastline_pkey" for table "ne_coastline"
ALTER TABLE
                          addgeometrycolumn
----------------------------------------------------------------------
 data_import.ne_coastline.geom SRID:4326 TYPE:MULTILINESTRING DIMS:2
(1 row)

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
COMMIT
[gpadmin@mdw tmp]$

 

마지막으로, DBeaver db tool로 가서 SELECT 문으로 데이터가 잘 들어갔는지 조회를 해보겠습니다. 해안선(coastline)을 MULTILINESTRING 공간지리 데이터 포맷으로 저장해놓은 데이터셋이군요. 

 

SELECT * FROM data_import.ne_coastline ORDER BY gid LIMIT 10;

 

이중에서 첫번째 gid의 geom만 가져다 살펴보면 아래에서 보는 것처럼 22개의 값으로 이루어져 있습니다. 

MULTILINESTRING ((-163.7128956777287 -78.59566741324154, -163.1058009511638 -78.22333871857859, -161.24511349184644 -78.38017669058443, -160.24620805564453 -78.69364592886694, -159.48240454815448 -79.04633757925897, -159.20818356019765 -79.4970077452764, -161.12760128481472 -79.63420867301133, -162.43984676821842 -79.28146534618699, -163.027407803377 -78.92877369579496, -163.06660437727038 -78.8699659158468, -163.7128956777287 -78.59566741324154))

 

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

728x90
반응형
Posted by Rfriend
,