[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기

이번 포스팅에서는 (1) Greenplum database에 위도와 경도를 포함하고 있는 csv파일을 psql, DBeaver tool을 사용하여 import 하고, (2) PostGIS의 sql query문을 사용하여 공간정보를 뽑아내는 방법을 소개하겠습니다. 

 

참고로, PostgreSQL, Greenplum DB에 지리공간 데이터를 importing할 수 있는 PostGIS, importing tool들은 아래와 같이 매우 다양합니다. 

 

 

(1) psql을 사용하여 위경도를 포함한 csv 파일을 Greenplum DB에 import하기

 

예제로 사용한 데이터셋은 https://github.com/PacktPublishing/Mastering-PostGIS/tree/master/Chapter01/data/usgs-earthquakes 에서 '2.5_day.csv' 파일을 다운로드 하였으며, 사용한 예제 코드는 'Mastering PostGIS' 책을 참조하였습니다. 

 

Greenplum docker image를 사용해서 Greenplum을 시작합니다. (자세한 설명은 아래 링크 참조)

==> https://rfriend.tistory.com/435

 

 

먼저 DBeaver sql query 편집창에서 아래와 같이 data_import schema와 earthquakes_csv 테이블을 만들어주겠습니다. 

 

-- create schema CREATE SCHEMA data_import; ---------- -- (1) Importing CSV data format ---------- -- create table DROP TABLE IF EXISTS data_import.earthquakes_csv; CREATE TABLE data_import.earthquakes_csv (     "time" timestamp with time zone,      latitude numeric,      longitude numeric,      depth numeric,      mag numeric,      magType varchar,      nst numeric,      gap numeric,      dmin numeric,      rms numeric,      net varchar,      id varchar,      updated timestamp with time zone,      place varchar,      type varchar,      horizontalError numeric,      depthError numeric,     magError numeric,     magNst numeric,      status varchar,     locationSource varchar,      magSource varchar );

 

 

다음으로 명령 프롬프트 cmd 창에서 PostGIS가 설치된 Greenplum Docker 에 docker cp를 사용하여 '2.5_day.csv'파일을 복사해 넣습니다. 

MacBook-Pro:~ ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/usgs-earthquakes/2.5_day.csv  gpdb-ds:/tmp

 

 

Greenplum의 gpadmin 계정으로 들어간 명령 프롬프트 창에서 '2.5_day.csv' 파일이 잘 복사가 되었는지 확인해보겠습니다.  그리고 root 계정으로 들어가서 gpadmin 으로 소유권한을 수정(chown)하겠습니다. 

[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 10 12:30 .s.PGSQL.40000 -rw-------  1 gpadmin gpadmin       27 Apr 10 12:30 .s.PGSQL.40000.lock srwxrwxrwx  1 gpadmin gpadmin        0 Apr 10 12:30 .s.PGSQL.40001 -rw-------  1 gpadmin gpadmin       27 Apr 10 12:30 .s.PGSQL.40001.lock srwxrwxrwx  1 gpadmin gpadmin        0 Apr 10 12:30 .s.PGSQL.5432 -rw-------  1 gpadmin gpadmin       27 Apr 10 12:30 .s.PGSQL.5432.lock -rw-r--r--  1     501 games       4787 Apr  8 06:21 2.5_day.csv [gpadmin@mdw tmp]$     [root@mdw tmp]# exitlogout

 

 

다음으로 psql 을 실행해서 copy 문으로 '2.5_day.csv' 파일을 data_import.earthquakes_csv 테이블에 복사해서 importing 하겠습니다. 

[gpadmin@mdw tmp]$ pwd /tmp [gpadmin@mdw tmp]$ psql psql (8.3.23) Type "help" for help. gpadmin=# copy data_import.earthquakes_csv from '/tmp/2.5_day.csv' with DELIMITER ',' CSV HEADER; COPY 25 gpadmin=# \q [gpadmin@mdw tmp]$

 

 

다시 DBeaver query tool 로 돌아와서, data_import.earthquakes_csv 테이블에 데이터가 잘 들어갔는지 조회를 해보겠습니다. 잘 들어갔네요. ^^

SELECT * FROM data_import.earthquakes_csv LIMIT 10;

 

 

(2) PostGIS 함수로 공간 정보 뽑아내기

이제 원천 데이터 준비가 되었으니 PostGIS의 ST_Point(경도, 위도) 로 공간데이터 점(geometry Point)을 만들고, ST_SetSRID() 로 공간 참조 ID를 만들어보겠습니다. 

-- (2) Extracting spatial information from flat data DROP TABLE IF EXISTS data_import.earthquakes_subset_with_geom; CREATE TABLE data_import.earthquakes_subset_with_geom AS (     SELECT          id,          "time",          depth,          mag,          magtype,          place,          ST_SetSRID(ST_Point(longitude, latitude), 4326) AS geom     FROM data_import.earthquakes_csv );   SELECT * FROM data_import.earthquakes_subset_with_geom LIMIT 10;

 

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