Oracle sdo_geomery 자료를 PostgreSQL + PostGIS 기반으로 옮겨가기

CREATE DOMAIN mdsys.sdo_elem_info_array AS numeric[];
CREATE DOMAIN mdsys.sdo_ordinate_array AS numeric[];
CREATE TYPE mdsys.sdo_point_type AS (x numeric, y numeric, z numeric);
CREATE TYPE mdsys.sdo_geometry AS (
 sdo_gtype numeric,
 sdo_srid numeric,
 sdo_point mdsys.sdo_point_type,
 sdo_elem_info mdsys.sdo_elem_info_array,
 sdo_ordinates mdsys.sdo_ordinate_array);

CREATE OR REPLACE PACKAGE pg_user_geom_util AS
-- copyright (c) 2012, Nicander Ltd
-- url: http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/
-- modified by ioseph

    -- Translates a geometry object into a string representation
    -- for storage and transport. See also StringToGeom.
    FUNCTION GeomToString(pGeom IN mdsys.sdo_geometry ) RETURN VARCHAR2 DETERMINISTIC;

END pg_user_geom_util;

CREATE OR REPLACE PACKAGE BODY ADMCHOI.pg_user_geom_util AS
-- copyright (c) 2012, Nicander Ltd
-- url: http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/
-- modified by ioseph
 
-- Gets the next token in a separated list. See StringToGeom for an example.
PROCEDURE get_token( iStart   IN NUMBER,
           sPattern in VARCHAR2,
           sBuffer  in VARCHAR2,
           sResult  OUT NOCOPY VARCHAR2,
           iNextPos OUT NOCOPY NUMBER ) IS
  nPos1 number;
  nPos2 number;
BEGIN
  nPos1 := Instr(sBuffer, sPattern, iStart);
  IF nPos1 = 0 then
  sResult := rtrim(ltrim(substr(sBuffer, iStart, LENGTH(sBuffer) - iStart)));
  ELSE
  sResult  := Rtrim(Ltrim(Substr(sBuffer, iStart, nPos1 - iStart)));
  iNextPos := nPos1 + 1;
  END IF;
END;
 
--------------------------------------------------------------------------------
   
FUNCTION NumberToString(pNumber IN NUMBER) RETURN VARCHAR2 IS
BEGIN
  IF (pNumber IS NULL) THEN
    RETURN '';
  ELSE
    RETURN TO_CHAR(pNumber);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN '';
END;




function emptytonull(pString in varchar2) return varchar2 is
begin
  return case when pString = '' then 'null' else pString end;
end;
   
--------------------------------------------------------------------------------
   
FUNCTION StringToNumber(pString IN VARCHAR2) RETURN NUMBER IS
BEGIN
    RETURN TO_NUMBER(pString);
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
   
--------------------------------------------------------------------------------
   
  -- String format is as follows:
  --
  -- ,,,,
  --
  -- Where:
  --  := 
  --  := 
  --  := NULL | SDO_POINT_TYPE, OPTIONAL_NUMBER, OPTIONAL_NUMBER, OPTIONAL_NUMBER
  --  := NULL | SDO_ELEM_INFO_ARRAY, NUMBER, {OPTIONAL_NUMBER, ...}
  --  := NULL | SDO_ORDINATE_ARRAY, NUMBER, {OPTIONAL_NUMBER, ...}
  --
  --  := NULL | NUMBER
  -- entries in curly braces are repeated from n times
FUNCTION GeomToString(pGeom IN mdsys.sdo_geometry ) RETURN VARCHAR2 DETERMINISTIC IS
  vString VARCHAR(32767);
  vCount NUMBER;
BEGIN
  -- GTYPE
  vString := NumberToString(pGeom.SDO_GTYPE);
   
  -- SRID
  vString := vString || ',' || NumberToString(pGeom.SDO_SRID);
   
  -- POINT
  IF (pGeom.SDO_POINT IS NULL) THEN
    vString := vString || ',';
  ELSE
    vString := vString || ',"(' || NumberToString(pGeom.SDO_POINT.X) || ',' || NumberToString(pGeom.SDO_POINT.Y) || ',' || NumberToString(pGeom.SDO_POINT.Z) || ')"';
  END IF;
   
  -- ELEM_INFO
  IF (pGeom.SDO_ELEM_INFO IS NULL) THEN
    vString := vString || ',';
  ELSE
    vCount := pGeom.SDO_ELEM_INFO.COUNT;
    vString := vString || ',"{';
    FOR i IN 1..vCount LOOP
      if i > 1 then
        vString := vString || ',';
      end if;    
      vString := vString || emptytonull(NumberToString(pGeom.SDO_ELEM_INFO(i)));
    END LOOP;
    vString := vString || '}"';
  END IF;
   
  -- ORDINATES
  IF (pGeom.SDO_ORDINATES IS NULL) THEN
    vString := vString || ',';
  ELSE
    vCount := pGeom.SDO_ORDINATES.COUNT;
    vString := vString || ',"{';
    FOR i IN 1..vCount LOOP
      if i > 1 then
        vString := vString || ',';
      end if;
      vString := vString || emptytonull(NumberToString(pGeom.SDO_ORDINATES(i)));
    END LOOP;
    vString := vString || '}"';
  END IF;
   
  RETURN '(' || vString || ')';
END;
END pg_user_geom_util;