[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기
Greenplum and PostgreSQL Database 2019. 4. 10. 21:50이번 포스팅에서는 (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 |
다음으로 명령 프롬프트 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; |
많은 도움이 되었기를 바랍니다.
