[Greenplum PostGIS] spatial_ref_sys 복제 테이블 생성하여 st_transform 함수 사용하기

PostgreSQL database에서 오픈소스 PostGIS 를 사용하여 지리공간 데이터 변환, 연산 및 분석을 하는데 있어 첫번째로 챙겨야 하는 것이 있다면 지리공간의 기준이 되는 좌표계(Coordinate Reference System)인  SRID (Spatial Reference IDentifier) 를 spatial_ref_sys 테이블의 값으로 설정해주는 것입니다.  

 

SRID 별로 지리공간 좌표 참조 정보가 들어있는 spatial_ref_sys 테이블은 아래와 같이 srid, auth_name, auth_srid, srtext, proj4text 의 칼럼으로 구성되어 있습니다. 

 

예를 들어서, WGS(World Geodetic System) 84 좌표계는 SRID = 4326 으로 조회를 하면 됩니다. 

 

 

 

SRID 설정은 PostGIS의  ST_SetSRID() 함수를 사용합니다. 

SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611), 4326) AS wgs84long_lat; 
wgs84long_lat
-------------------------
POINT(-123.365556 48.428611)

 

 

PostgreSQL DB 에서 PostGIS 를 사용해서 두 개의 좌표계 간에 좌표 변환을 하려면  ST_Transform() 함수를 사용하면 간단하게 좌표 변환을 할 수 있습니다. 그런데 만약 Greenplum DB 에서 PostGIS 의 좌표변환 함수인 ST_Transform() 함수를 사용한다면 아래와 같은 에러가 발생할 것입니다. (몇 년 전에 POC를 하는데 아래 에러 해결하려고 workaround 만드느라 반나절 고생했던게 생각나네요. ^^;;;)

 

"ERROR: function cannot execute on QE slice because it accesses relation spatial_ref_sys" 

 

이런 에러가 발생하는 이유는 Greenplum database의 경우 PostgreSQL 엔진을 기반으로 하고 있지만,  Shared nothing 아키텍쳐의 MPP (Massively Parallel Processing) database 로서, ST_Transform() 함수 실행 시 각 segment nodes 가 spatial_ref_sys 좌표계 테이블을 참조하지 못하기 때문입니다. Greenplum DB 에서 이 에러를 해결하기 위해서는 spatial_ref_sys 좌표계 정보가 들어있는 테이블을 여러개의 segment nodes에 복제를 해주면 됩니다.  

 

Greenplum 6.x 버전 부터는 'DISTRIBUTED REPLICATED' 를 사용해서 쉽게 테이블을 각 segment nodes 에 복제할 수 있습니다.  (Greenplum 5.x 버전에서는  CROSS JOIN 을 사용해서 각 segment nodes 에 복제해주면 됩니다.)

 

 

[ Greenplum 6.x 버전에서 spatial_ref_sys 테이블을 각 segment nodes 에 복제하여 생성하는 절차 ]

 

(1) 기존의 spatial_ref_sys 테이블을 spatial_ref_sys_old 로 테이블 이름을 바꿔줍니다. 

(2) spatial_ref_sys 라는 이름의 테이블을 'DISTRIBUTED REPLICATED' 모드로 해서 각 segment nodes에 복제해서 생성해줍니다. 이때 칼럼 이름과 속성, 제약조건은 아래의 SQL query 를 그래도 복사해서 사용하시면 됩니다. 

(3) 위의 (1)번에서 이름을 바꿔놓았던 기존의 테이블인  spatial_ref_sys_old 테이블에서 SELECT 문으로 데이터를 조회해와서 새로 각 테이블에 복제 모드로 생성해놓은  spatial_ref_sys 테이블에 데이터를 삽입해줍니다. 

 

-- (1) changing the spatial_ref_sys table's name
ALTER TABLE spatial_ref_sys RENAME TO spatial_ref_sys_old;


-- (2) creating spatial_ref_sys table using DISTRIBUTED REPLICATED
CREATE TABLE spatial_ref_sys(
    srid int4 NOT NULL
    , auth_name VARCHAR(256) NULL
    , auth_srid INT4 NULL
    , srtext VARCHAR(2048) NULL
    , proj4text TEXT NOT NULL
    , CONSTRAINT spatial_ref_sys_pkey_1 PRIMARY KEY (srid)
    , CONSTRAINT spatial_ref_sys_srid_check_1 CHECK (((srid > 0) AND (srid <= 998999)))
)
DISTRIBUTED REPLICATED;


-- (3) inserting spatial_ref_sys_old data inot replicated segments' tables
INSERT INTO spatial_ref_sys SELECT * FROM spatial_ref_sys_old;

 

 

 

이제 spatial_ref_sys 테이블이 모든 segment nodes에 복제가 되었으니 Greenplum DB에서 PostGIS의 ST_Transform() 함수를 사용해서 좌표계 SRID 4326 (WGS 84) 인 데이터를 좌표계 SRID 3785 로 변환해보겠습니다. 

 

-- Mark a point as WGS 84 long lat and then transform to web mercator (Spherical Mercator) --
SELECT ST_Transform(ST_SetSRID(ST_Point(-123.365556, 48.428611), 4326), 3785) AS spere_merc; 

spere_merc
---------------------------------
SRID=3785;POINT(-13732990.8753491 6178458.96425423)

 

 

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요! :-)