Transporting SDO_GEOMETRY objects across an Oracle DBLINK in PLSQL

At my work we have a system that generally speaking consists of two Oracle 10g databases, the ‘SpatialDB’ and the ‘AppDB’. The SpatialDB holds a multi-gigabyte set of static national road-network geometry data and the AppDB holds the dynamic runtime data. A database link is defined in the AppDB so that it can query the SpatialDB and invoke stored procedures.

Generally what happens is that an action somewhere in the system (user interaction, external system communications, etc.), will result in something happening in the database.

In this case a package method in the AppDB needed to obtain a geometry from the SpatialDB. Surprisingly, this turned out to be a quite a difficult thing to achieve. It seems that along with cursors, LOBs and objects in general, an SDO_GEOMETRY cannot be transported across a database link as an out parameter of a package method. The following is a summary of the methods I discovered for overcoming this.

Method #1 – Use the SQL Engine

Whilst PLSQL cannot cope with transporting objects across a database link, it turns out that the SQL engine copes just fine. Objects from the remote database can be selected into a local table and then that table can be queried to obtain the result.

INSERT INTO LOCAL_TABLE SELECT ORA_GEOMETRY FROM REMOTE_TABLE@SPATIALDB;
SELECT ORA_GEOMETRY FROM LOCAL_TABLE;

Method #2 – Transport via WKT

Oracle 10g contains methods for transforming a geometry into a format called ‘Well Known Text’, which is a string representation for geometries. Two methods are provided for these transformations, SDO_UTIL.TO_WKTGEOMETRY and SDO_UTIL.FROM_WKTGEOMETRY.

The technique here is to transform the geometry into a WKT string on the remote side, transfer the string across as a PLSQL out parameter, then transform the string back into a geometry object on the local side.

However, the following limitations must be noted.

  • Transfering the string as a VARCHAR2 type will limit its length to 32767 characters.
  • No 3D support.
  • The SRID (reference system) is not preserved.
  • Complex geometries are not supported.

Method #3 – Transport via GML

Having read that Oracle’s GML implementation gave better geometry support, I thought I would give it a try instead of WKT. I was a bit concerned about the amount of valuable VARCHAR2 space that would be wasted on XML tags, but I never got as far as testing whether it would be an issue.

So in a similar fashion to the WKT support, in the SDO_UTIL package you will find a TO_GMLGEOMETRY method to convert your geometry object into a GML string. Unfortunately for users of 10g, you will not find any corresponding method to convert GML into a geometry object. Disappointing to say the least.

Method #4 – Transport via Custom String

My final solution was to write my own conversion routine that simply took the geometry object and encoded its data members into a CSV string. This format does not attempt to interpret the geometry data, so all geometries and all dimensions are supported. SRID data is also preserved and because the transformation is doing less work that the WKT methods, it turns out to be faster too.

The tables below give the times recorded for encoding and decoding geometries of varying sizes and quantities. Times are in seconds.

2 Vertex Line Encoding
Time taken to encode a line with 2 vertices.

WKT GML Custom
1 Geometry 0.009328 0.002073 0.000057
100 Geometries 0.124836 0.154757 0.002367

2 Vertex Line Decoding
Time taken to decode a line with 2 vertices.

WKT GML Custom
1 Geometry 0.001482 0.009657 0.000616
100 Geometries 0.036376 0.081836 0.046199

100 Vertex Line Encoding
Time taken to encode a line with 100 vertices.

WKT GML Custom
1 Geometry 0.001141 0.003194 0.000627
100 Geometries 0.293802 0.273061 0.06347

100 Vertex Line Decoding
Time taken to decode a line with 2 vertices.

WKT GML Custom
1 Geometry 0.001889 0.003083 0.002381
100 Geometries 0.24252 0.251887 0.239928

Observations

The encoding of the sdo_geometry objects to a custom string format is clearly faster that encoding to WKT or GML using the stand methods in SDO_UTIL. This is especially true for simple geometries with few vertices.

The decoding of the custom string format back into sdo_geometry objects did not exhibit the same performance benefit, although the performance of decoding one simple 2-vertex geometry did show a marked improvement.

Conclusions

There are two main reasons I can see for the custom string encoding methods having a performance advantage.

  1. The custom string format is a closer representation of the binary format of the object with less translation required by the encoding logic.

  2. The custom string format is implemented in PL/SQL and not Java like the other methods. Although Java code should have a performance advantage, it seems likely that the cost of switching context between PL/SQL and Java is a significant overhead.

Code

I hope you find the code useful. If you find any bugs or make any improvements, I would appreciate the feedback.

code on github

Spec

CREATE OR REPLACE PACKAGE pg_user_geom_util AS
-- copyright (c) 2012, Nicander Ltd
	
	-- 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;
	PROCEDURE Test_GeomToString;
	
	-- Translate a geometry string representation into a geometry object.
	-- See also GeomToString.
	FUNCTION StringToGeom(pString IN VARCHAR2) RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
	PROCEDURE Test_StringToGeom;
	
	-- Tests the conversion between sdo_geometry objects and a string representation.
	-- Tests for pGeomCount geometries, each with pGeomVertexCount vertices.
	PROCEDURE PerfTest_GeomStrings(pGeomCount IN INTEGER, pGeomVertexCount IN INTEGER);
	
	-- Runs PerfTest_GeomStrings(INTEGER, INTEGER) for a selection of values.
	PROCEDURE PerfTest_GeomStrings;
	
END pg_user_geom_util;
/

show errors package pg_user_geom_util;

Body

CREATE OR REPLACE PACKAGE BODY pg_user_geom_util AS
-- copyright (c) 2012, Nicander Ltd

-- 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 'NULL';
  ELSE
    RETURN TO_CHAR(pNumber);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    --DBMS_OUTPUT.PUT_LINE('Exception caught: ' || DBMS_UTILITY.FORMAT_ERROR_STACK() || ', ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
    RETURN 'NULL';
END;
  
--------------------------------------------------------------------------------
  
FUNCTION StringToNumber(pString IN VARCHAR2) RETURN NUMBER IS
BEGIN
    RETURN TO_NUMBER(pString);
EXCEPTION
  WHEN OTHERS THEN
    --DBMS_OUTPUT.PUT_LINE('Exception caught: ' || DBMS_UTILITY.FORMAT_ERROR_STACK() || ', ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
    RETURN NULL;
END;
  
--------------------------------------------------------------------------------
  
  -- String format is as follows:
  --
  -- <GTYPE>,<SRID>,<POINT>,<ELEM_INFO>,<ORDINATES>
  --
  -- Where:
  -- <GTYPE> := <OPTIONAL_NUMBER>
  -- <SRID> := <OPTIONAL_NUMBER>
  -- <POINT> := NULL | SDO_POINT_TYPE, OPTIONAL_NUMBER, OPTIONAL_NUMBER, OPTIONAL_NUMBER
  -- <ELEM_INFO> := NULL | SDO_ELEM_INFO_ARRAY, NUMBER, {OPTIONAL_NUMBER, ...}
  -- <ORDINATES> := NULL | SDO_ORDINATE_ARRAY, NUMBER, {OPTIONAL_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 || ',NULL';
  ELSE
    vString := vString || ',SDO_POINT_TYPE,' || 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 || ',NULL';
  ELSE
    vCount := pGeom.SDO_ELEM_INFO.COUNT;
    vString := vString || ',SDO_ELEM_INFO_ARRAY,' || vCount;
    FOR i IN 1..vCount LOOP
      vString := vString || ',' || NumberToString(pGeom.SDO_ELEM_INFO(i));
    END LOOP;
  END IF;
  
  -- ORDINATES
  IF (pGeom.SDO_ORDINATES IS NULL) THEN
    vString := vString || ',NULL';
  ELSE
    vCount := pGeom.SDO_ORDINATES.COUNT;
    vString := vString || ',SDO_ORDINATE_ARRAY,' || vCount;
    FOR i IN 1..vCount LOOP
      vString := vString || ',' || NumberToString(pGeom.SDO_ORDINATES(i));
    END LOOP;
  END IF;
  
  RETURN vString;
END;
  
--------------------------------------------------------------------------------
  
PROCEDURE Test_GeomToString IS
  vGeom MDSYS.SDO_GEOMETRY;
  vString VARCHAR2(32767);
BEGIN
  -- TEST CASE 1 - POINT
  dbms_output.put_line('----- TEST CASE 1 -----');
  vGeom := mdsys.sdo_geometry( 2001, NULL, MDSYS.SDO_POINT_TYPE(12345, 67890, NULL), null, null );
  dbms_output.put_line('Src Geom: ' || SDO_UTIL.TO_WKTGEOMETRY(vGeom));
  vString := GeomToString(vGeom);
  IF (vString IS NULL) THEN
    vString := '<NULL>';
  END IF;
  dbms_output.put_line('Dest String: ' || vString);
  
  -- TEST CASE 2 - LINE
  dbms_output.put_line('----- TEST CASE 2 -----');
  vGeom := MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(652737.95,6589964.213,652741.222,6589983.148,652746.77,6590018.745,652752.029,6590050.2,652759.351,6590085.732));
  dbms_output.put_line('Src Geom: ' || SDO_UTIL.TO_WKTGEOMETRY(vGeom));
  vString := GeomToString(vGeom);
  IF (vString IS NULL) THEN
    vString := '<NULL>';
  END IF;
  dbms_output.put_line('Dest String: ' || vString);
END;
  
--------------------------------------------------------------------------------
  
FUNCTION StringToGeom(pString IN VARCHAR2) RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC IS
  vGeom MDSYS.SDO_GEOMETRY;
  vPos NUMBER := 1;
  vNextPos NUMBER := 1;
  vToken VARCHAR2(32767);
  vSep CONSTANT VARCHAR2(2) := ',';
  vCount NUMBER;
BEGIN
  vGeom := MDSYS.SDO_GEOMETRY('POINT(0 0)');
  
  -- GTYPE
  get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
  vGeom.SDO_GTYPE := StringToNumber(vToken);
  
  -- SRID
  get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
  vGeom.SDO_SRID := StringToNumber(vToken);
  
  -- POINT
  get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
  IF (UPPER(vToken) LIKE 'SDO_POINT_TYPE') THEN
    vGeom.SDO_POINT := MDSYS.SDO_POINT_TYPE(NULL, NULL, NULL);
    get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
    vGeom.SDO_POINT.X := StringToNumber(vToken);
    get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
    vGeom.SDO_POINT.Y := StringToNumber(vToken);
    get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
    vGeom.SDO_POINT.Z := StringToNumber(vToken);
  ELSE
    vGeom.SDO_POINT := NULL;
  END IF;
  
  -- ELEM_INFO
  get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
  IF (UPPER(vToken) LIKE 'SDO_ELEM_INFO_ARRAY') THEN
    vGeom.SDO_ELEM_INFO := MDSYS.SDO_ELEM_INFO_ARRAY();
    get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
    vCount := StringToNumber(vToken);
    IF (vCount > 0) THEN
      vGeom.SDO_ELEM_INFO.EXTEND(vCount);
      FOR i IN 1..vCount LOOP
        get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
        vGeom.SDO_ELEM_INFO(i) := StringToNumber(vToken);
      END LOOP;
    END IF;
  ELSE
    vGeom.SDO_ELEM_INFO := NULL;
  END IF;
  
  -- ORDINATES
  get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
  IF (UPPER(vToken) LIKE 'SDO_ORDINATE_ARRAY') THEN
    vGeom.SDO_ORDINATES := MDSYS.SDO_ORDINATE_ARRAY();
    get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
    vCount := StringToNumber(vToken);
    IF (vCount > 0) THEN
      vGeom.SDO_ORDINATES.EXTEND(vCount);
      FOR i IN 1..vCount LOOP
        get_token(vPos, vSep, pString, vToken, vNextPos); vPos := vNextPos;
        vGeom.SDO_ORDINATES(i) := StringToNumber(vToken);
      END LOOP;
    END IF;
  ELSE
    vGeom.SDO_ORDINATES := NULL;
  END IF;

  RETURN vGeom;
END;
  
--------------------------------------------------------------------------------
  
PROCEDURE Test_StringToGeom IS
  vString VARCHAR2(32767);
  vGeom MDSYS.SDO_GEOMETRY;
BEGIN
  -- TEST CASE 1 - POINT
  dbms_output.put_line('----- TEST CASE 1 -----');
  vString := '2001,NULL,SDO_POINT_TYPE,12345,67890,NULL,NULL,NULL';
  dbms_output.put_line('src string: ' || vString);
  vGeom := StringToGeom(vString);
  dbms_output.put_line('dest geom: ' || SDO_UTIL.TO_WKTGEOMETRY(vGeom));

  -- TEST CASE 2 - LINE
  dbms_output.put_line('----- TEST CASE 2 -----');
  vString := '2002,NULL,NULL,SDO_ELEM_INFO_ARRAY,3,1,2,1,SDO_ORDINATE_ARRAY,10,652737.95,6589964.213,652741.222,6589983.148,652746.77,6590018.745,652752.029,6590050.2,652759.351,6590085.732';
  dbms_output.put_line('src string: ' || vString);
  vGeom := StringToGeom(vString);
  dbms_output.put_line('dest geom: ' || SDO_UTIL.TO_WKTGEOMETRY(vGeom));
END;

--------------------------------------------------------------------------------

PROCEDURE PerfTest_GeomStrings(pGeomCount IN INTEGER, pGeomVertexCount IN INTEGER) IS
  TYPE geom_array IS VARRAY(1000) OF MDSYS.SDO_GEOMETRY;
  vGeoms geom_array := geom_array();
  vWKTGeoms geom_array := geom_array();
  vGMLGeoms geom_array := geom_array();
  vCustomGeoms geom_array := geom_array();
  TYPE string_array IS VARRAY(1000) OF VARCHAR2(32767);
  vWkts string_array := string_array();
  vGmls string_array := string_array();
  vCustoms string_array := string_array();
  vWktStart timestamp;
  vWktEncDelta interval day to second;
  vWktDecDelta interval day to second;
  vGmlStart timestamp;
  vGmlEncDelta interval day to second;
  vGmlDecDelta interval day to second;
  vCustomStart timestamp;
  vCustomEncDelta interval day to second;
  vCustomDecDelta interval day to second;
  vTempOrdinates MDSYS.SDO_ORDINATE_ARRAY;
BEGIN
  dbms_output.put_line('----- ' || pGeomCount || ' geometries with ' || pGeomVertexCount || ' vertices -----');

  vGeoms.extend(pGeomCount);
  vWKTGeoms.extend(pGeomCount);
  vGMLGeoms.extend(pGeomCount);
  vCustomGeoms.extend(pGeomCount);
  vWkts.extend(pGeomCount);
  vGmls.extend(pGeomCount);
  vCustoms.extend(pGeomCount);
  
  FOR i IN 1..pGeomCount LOOP
    vTempOrdinates := MDSYS.SDO_ORDINATE_ARRAY();
    vTempOrdinates.extend(pGeomVertexCount*2);
    FOR j IN 1..(pGeomVertexCount*2) LOOP
      vTempOrdinates(j) := i + j;
    END LOOP;
    vGeoms(i) := mdsys.sdo_geometry(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), vTempOrdinates);
  END LOOP;
  
  -- encode to WKT
  vWktStart := systimestamp;
  FOR i IN 1..pGeomCount LOOP
    vWkts(i) := SDO_UTIL.TO_WKTGEOMETRY(vGeoms(i));
  END LOOP;
  vWktEncDelta := systimestamp - vWktStart;
  
  -- encode to GML
  vGmlStart := systimestamp;
  FOR i IN 1..pGeomCount LOOP
    vGmls(i) := SDO_UTIL.TO_GMLGEOMETRY(vGeoms(i));
  END LOOP;
  vGmlEncDelta := systimestamp - vGmlStart;
  
  -- encode to custom
  vCustomStart := systimestamp;
  FOR i IN 1..pGeomCount LOOP
    vCustoms(i) := GeomToString(vGeoms(i));
  END LOOP;
  vCustomEncDelta := systimestamp - vCustomStart;
  
  -- decode from WKT
  vWktStart := systimestamp;
  FOR i IN 1..pGeomCount LOOP
    vWKTGeoms(i) := SDO_UTIL.FROM_WKTGEOMETRY(vWkts(i));
  END LOOP;
  vWktDecDelta := systimestamp - vWktStart;
  
  -- decode from GML
  vGmlStart := systimestamp;
  FOR i IN 1..pGeomCount LOOP
    vGMLGeoms(i) := SDO_UTIL.FROM_GMLGEOMETRY(vGMLs(i));
  END LOOP;
  vGmlDecDelta := systimestamp - vGmlStart;
  
  -- decode from custom
  vCustomStart := systimestamp;
  FOR i IN 1..pGeomCount LOOP
    vCustomGeoms(i) := StringToGeom(vCustoms(i));
  END LOOP;
  vCustomDecDelta := systimestamp - vCustomStart;
  
  dbms_output.put_line('Encoding to WKT took ' || extract(second from vWktEncDelta) || ' seconds');
  dbms_output.put_line('Encoding to GML took ' || extract(second from vGmlEncDelta) || ' seconds');
  dbms_output.put_line('Encoding to custom took ' || extract(second from vCustomEncDelta) || ' seconds');
  
  dbms_output.put_line('Decoding from WKT took ' || extract(second from vWktDecDelta) || ' seconds');
  dbms_output.put_line('Decoding from GML took ' || extract(second from vGmlDecDelta) || ' seconds');
  dbms_output.put_line('Decoding from custom took ' || extract(second from vCustomDecDelta) || ' seconds');
  
  dbms_output.put_line('----------------------------------------');
  dbms_output.put_line(' ');
END;

--------------------------------------------------------------------------------

PROCEDURE PerfTest_GeomStrings IS
BEGIN
  -- geometries with 2 vertices
  PerfTest_GeomStrings(1, 2);
  PerfTest_GeomStrings(10, 2);
  PerfTest_GeomStrings(100, 2);
  PerfTest_GeomStrings(1000, 2);
  
  -- geometries with 10 vertices
  PerfTest_GeomStrings(1, 10);
  PerfTest_GeomStrings(10, 10);
  PerfTest_GeomStrings(100, 10);
  PerfTest_GeomStrings(1000, 10);

  -- geometries with 100 vertices
  PerfTest_GeomStrings(1, 100);
  PerfTest_GeomStrings(10, 100);
  PerfTest_GeomStrings(100, 100);
  PerfTest_GeomStrings(1000, 100);

  -- geometries with 1000 vertices
  PerfTest_GeomStrings(1, 1000);
  PerfTest_GeomStrings(10, 1000);
  PerfTest_GeomStrings(100, 1000);
  PerfTest_GeomStrings(1000, 1000);

END;

END pg_user_geom_util;
/

show errors package body pg_user_geom_util;

One thought on “Transporting SDO_GEOMETRY objects across an Oracle DBLINK in PLSQL”

  1. Thank you for sharing the code. We struck the same problem and this appears to have solved the problem with minimal coding for us using your package.
    We used an insert trigger on a local table to call a remote procedure with parameters for the fields and geomtostring procedure for the sdo field. The remote procedure then inserted into a remote table with parameters for the fields and stringtogeom procedure. It has worked very nicely.
    Greatly appreciated!

Leave a Reply