3D/4D and SRID aware Conversion functions for SDO_Geometry: WKT and EWKT

Oracle Spatial does not read or write 3D Well-known text (WKT); nor does it read/write measures. Oracle’s SDO_Geometry implementation only supports the OGC Simple Features 1.1 specification which is 2D only.

What I have done is expose the Jaspa/JTS implementation of methods more 3D+ aware to PL/SQL and therefore SQL via my Spatial Companion for Oracle (SC4).
See my SC4O package for details.

The specific methods/functions are:

  • ST_GeomFromText
  • ST_GeomFromEWKT
  • ST_AsText
  • ST_AsEWKT

Here are some examples:

 SET NULL *NULL*
 SELECT SC4O.ST_GeomFromText('POINT EMPTY') AS point
   FROM dual;
 -- Result
 POINT
 -----
 *NULL*
 --
 SELECT SC4O.ST_GeomFromEWKT('BOX(-32 147, -33 148)',8307) AS optRect
   FROM dual;
 -- Result
 OPTRECT
 ----------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(2003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-33,147,-32,148))
 --
 SELECT SC4O.ST_AsEWKT(sdo_geometry(2003,8307,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(-32,147,-33,148))) AS Box
   FROM dual;
 -- Result
 BOX
 -----------------------------------------------------------------
 SRID=8307;POLYGON ((-32 147, -33 147, -33 148, -32 148, -32 147))
 --
 SELECT SC4O.ST_GeomFromText('BOX(-32 147, -33 148)') AS optRect
   FROM dual;
 -- Result
 OPTRECT
 ----------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(-33,147,-32,148))
 --
 SELECT SC4O.ST_AsText(sdo_geometry(2003,8307,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(-32,147,-33,148))) AS Box
   FROM dual;
 -- Result
 BOX
 -------------------------------------------------------
 POLYGON ((-32 147, -33 147, -33 148, -32 148, -32 147))
 --
 SELECT SC4O.ST_GeomFromEWKT('SRID=8307;POLYGON((-76.57418668270113 38.91891450597657 0, -76.57484114170074 38.91758725401061 0, -76.57661139965057 38.91881851059802 0, -76.57418668270113 38.91891450597657 0))') AS geom
   FROM dual;
 -- Result
 GEOM
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(3003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-76.5741866827011,38.9189145059766,0,-76.5766113996506,38.918818510598,0,-76.5748411417007,38.9175872540106,0,-76.5741866827011,38.9189145059766,0))
 --
 SELECT SC4O.ST_GeomFromEWKT('POLYGON((-76.57418668270113 38.91891450597657 0, -76.57484114170074 38.91758725401061 0, -76.57661139965057 38.91881851059802 0, -76.57418668270113 38.91891450597657 0))',8307) AS geom
   FROM dual;
 -- Result
 GEOM
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 MDSYS.SDO_GEOMETRY(3003,8307,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-76.5741866827011,38.9189145059766,0,-76.5766113996506,38.918818510598,0,-76.5748411417007,38.9175872540106,0,-76.5741866827011,38.9189145059766,0))
 --
 SELECT SC4O.ST_AsText(a.box) AS text
   FROM (SELECT SC4O.ST_GeomFromEWKT('BOX(-32 147,-33 148)',8307) AS Box
           FROM dual) a;
 -- Result
 TEXT
 -------------------------------------------------------
 POLYGON ((-33 147, -32 147, -32 148, -33 148, -33 147))
 --
 SELECT SC4O.ST_AsText(sdo_geometry(3003,NULL,NULL,sdo_elem_info_array(1,1003,1),sdo_ordinate_array(-76.57418668270113,38.91891450597657,0, -76.57484114170074,38.91758725401061,0, -76.57661139965057,38.91881851059802,0, -76.57418668270113,38.91891450597657,0))) AS text
 FROM dual;
 -- Result
 TEXT
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 POLYGON ((-76.57418668270113 38.91891450597657 0, -76.57484114170074 38.91758725401061 0, -76.57661139965057 38.91881851059802 0, -76.57418668270113 38.91891450597657 0))
 --
 SELECT SC4O.ST_AsEWKT(
        SC4O.ST_CoordinateRounder(
        SDO_GEOMETRY(3003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-76.5741866827011,38.9189145059766,0,-76.5766113996506,38.918818510598,0,-76.5748411417007,38.9175872540106,0,-76.5741866827011,38.9189145059766,0)),
        8)
        ) AS EWKT
 FROM dual;
 -- Result
 EWKT
 -----------------------------------------------------------------------------------------------------------------------------------
 SRID=8307;POLYGON ((-76.57418668 38.91891451 0, -76.5766114 38.91881851 0, -76.57484114 38.91758725 0, -76.57418668 38.91891451 0))

I hope this work is of use to someone.