PLSQL ST_AsEWKT and ST_FromWKT Functions For Oracle (any version)
Previously I created the following Java stored procedures to enable the import/export of ZM aware WKT.
- ST_GeomFromText
- ST_GeomFromEWKT
- ST_AsText
- ST_AsEWKT
These functions are necessary if one wishes to exchange geometry data with users of other databases that support WKT functions for 2D, 3D or 4D (ZM).
For example, PostGIS supports the EWKT format and provides functions to read/write it
Whereas SQL Server Spatial provides the AsTextZM() non-OGC instance function for exporting WKT, but has made its ordinary OGC WKT import functions ZM aware.
For those that don’t have permission to install Java stored procedures I have just added the following member function to my Oracle Object product to create ZM aware WKT.
/* Note the ability to provide one's own number format model for the ordinates. * Now that is cool! * p_format_model (varchar2) -- Oracle Number Format Model (see documentation) * default 'FM999999999999990D0') */ Member FUNCTION ST_AsEWKT(p_format_model IN varchar2 DEFAULT 'FM999999999999990D0') RETURN CLOB Deterministic,
The full documentation is available here
Here is an example of how to use it.
WITH DATA AS ( SELECT t_geometry(sdo_geometry(3001,NULL,sdo_point_type(100,100,-37.38),NULL,NULL),0.005,2,1) AS geom FROM dual UNION ALL SELECT t_geometry(sdo_geometry(4001,NULL,NULL, sdo_elem_info_array(1,1,1), sdo_ordinate_array(100,100,-37.38,345.24)),0.005,2,1) AS geom FROM dual UNION ALL SELECT t_geometry(sdo_geometry(2002,28355,NULL, sdo_elem_info_array(1,2,2), sdo_ordinate_array(252230.478,5526918.373, 252400.08,5526918.373,252230.478,5527000.0)),0.0005,3,1) AS geom FROM dual UNION ALL SELECT t_geometry(SDO_GEOMETRY(3302,28355,NULL, sdo_elem_info_array(1,2,2), sdo_ordinate_array(252230.478,5526918.373,0.0, 252400.08,5526918.373,417.4, 252230.478,5527000.0,506.88)),0.0005,3,1) AS geom FROM dual UNION ALL SELECT t_geometry(sdo_geometry(2002,NULL,NULL, sdo_elem_info_array(1,2,1), sdo_ordinate_array(100,100,900,900.0)),0.005,2,1) AS geom FROM dual UNION ALL SELECT t_geometry(sdo_geometry(3002,NULL,NULL, sdo_elem_info_array(1,2,1), sdo_ordinate_array(0,0,1,10,0,2,10,5,3,10,10,4,5,10,5,5,5,6)),0.005,3,1) AS geom FROM dual UNION ALL SELECT t_geometry(sdo_geometry(3302,NULL,NULL, sdo_elem_info_array(1,2,1), sdo_ordinate_array(0,0,1,10,0,2,10,5,3,10,10,4,5,10,5,5,5,6)),0.005,3,1) AS geom FROM dual UNION ALL SELECT t_geometry(sdo_geometry(4402,4283,NULL, sdo_elem_info_array(1,2,1), sdo_ordinate_array(147.5,-42.5,849.9,102.0, 147.6,-42.5,1923.0,2100.0)),0.005,2,0) AS geom FROM dual UNION ALL SELECT T_GEOMETRY(sdo_geometry(3003,NULL,NULL, sdo_elem_info_array(1,1003,1), sdo_ordinate_array(0,0,1,10,0,2,10,5,3,10,10,4,5,10,5,5,5,6)),0.005,2,1) AS geom FROM Dual ) SELECT a.geom.geom.sdo_gtype AS gtype, a.geom.ST_AsEWKT() AS ewkt FROM DATA a; . GTYPE EWKT ----- ----------------------------------------------------------------------------------------------------------- 3001 POINTZ (100 100 -37.38) 4001 POINTZM (100 100 -37.38 345.24) 2002 CIRCULARSTRING (252230.478 5526918.373, 252400.08 5526918.373, 252230.478 5527000.0) 3302 SRID=28355;CIRCULARSTRINGM (252230.478 5526918.373 0,252400.08 5526918.373 417.4,252230.478 5527000 506.88) 2002 LINESTRING (100.0 100.0, 900.0 900.0) 3002 LINESTRINGZ (0 0 1,10 0 2,10 5 3,10 10 4,5 10 5,5 5 6) 3302 LINESTRINGM (0 0 1,10 0 2,10 5 3,10 10 4,5 10 5,5 5 6) 4402 SRID=4283;LINESTRINGZM (147.5 -42.5 849.9 102,147.6 -42.5 1923 2100) 3003 POLYGONZ ((0 0 1,10 0 2,10 5 3,10 10 4,5 10 5,5 5 6)) . 9 ROWS selected
I have also written a partner ST_FromEWKT() function to allow for EWKT to be imported from SQL Server Spatial (AsTextZM) or PostGIS.
Static FUNCTION ST_FromEWKT(p_ewkt IN CLOB) RETURN T_GEOMETRY Deterministic,
The full documentation is available here
Here is an example of how to use it.
WITH DATA AS ( SELECT 'SRID=28355;POINTZ (0.1 0.2 0.3)' AS ewkt FROM dual UNION ALL SELECT 'SRID=28355;POINTZM (0.1 0.2 0.3 0.4)' AS ewkt FROM dual UNION ALL SELECT 'LINESTRING (0.1 0.1,10 0,10 5,10 10,5 10,5 5)' AS ewkt FROM dual UNION ALL SELECT 'SRID=28355;LINESTRINGZ (0.1 0.1 1,10 0 2,10 5 3,10 10 4,5 10 5,5 5 6)' AS ewkt FROM dual UNION ALL SELECT 'MULTILINESTRINGM ((50.0 55.0 1, 50.0 60.0 2, 55.0 58.0 3, 50.0 55.0 4), (56.0 58.0 5, 60.0 55.0 6, 60.0 60.0 7, 56.0 58.0 8))' AS ewkt FROM dual UNION ALL SELECT 'CIRCULARSTRINGZ (10.0 15.0 3.0, 15.0 20.0 3.0, 20.0 15.0 3.0)' AS ewkt FROM dual UNION ALL SELECT 'CIRCULARSTRINGZM (10.0 15.0 3.0 0.0, 15.0 20.0 3.0 5.67, 20.0 15.0 3.0 9.84)' AS ewkt FROM dual UNION ALL SELECT 'CIRCULARSTRINGM (10.0 15.0 0.0, 15.0 20.0 5.67, 20.0 15.0 9.84)' AS ewkt FROM dual UNION ALL SELECT 'COMPOUNDCURVEZ ((10.0 45.0 0.0, 20.0 45.0 1.6), CIRCULARSTRING (20.0 45.0 1.8, 23.0 48.0 1.8, 20.0 51.0 1.8), (20.0 51.0 1.8, 10.0 51.0 1.8))' AS ewkt FROM dual UNION ALL SELECT 'SRID=28355;MULTICURVEZ (CIRCULARSTRING (50.0 35.0 3.2, 55.0 40.0 3.2, 60.0 35.0 3.2), CIRCULARSTRING (65.0 35.0 4.6, 70.0 30.0 5.6, 75.0 35.0 2.3))' AS ewkt FROM dual UNION ALL SELECT 'CURVEPOLYGON (COMPOUNDCURVE ((10.0 128.0, 10.0 125.0, 20.0 125.0, 20.0 128.0), CIRCULARSTRING (20.0 128.0, 15.0 130.0, 10.0 128.0)))' AS ewkt FROM dual UNION ALL SELECT 'MULTIPOLYGONZ (((1500.0 100.0 0.0, 1900.0 100.0 0.1, 1900.0 500.0 0.2, 1500.0 500.0 0.3, 1500.0 100.0 0.0)), ((1900.0 500.0 2.0, 2300.0 500.0 2.1, 2300.0 900.0 2.2, 1900.0 900.0 1.8, 1900.0 500.0 2.0)))' AS ewkt FROM dual UNION ALL SELECT 'GEOMETRYCOLLECTION (POINT (10.0 5.0), LINESTRING (10.0 10.0, 20.0 10.0), POLYGON ((10.0 105.0, 15.0 105.0, 20.0 110.0, 10.0 110.0, 10.0 105.0)))' AS ewkt FROM dual UNION ALL SELECT 'SRID=28355;GEOMETRYCOLLECTIONZ (POINT (10.0 5.0 1.0), LINESTRING (10.0 10.0 1.1, 20.0 10.0 1.2), POLYGON ((10.0 105.0 1.3, 15.0 105.0 1.3, 20.0 110.0 1.4, 10.0 110.0 1.2, 10.0 105.0 1.3)))' AS ewkt FROM dual ) SELECT T_GEOMETRY.ST_FromEWKT(a.ewkt).geom AS geomFromEWkt FROM DATA a; . GEOMFROMEWKT ---------------------------------------------------------------------------------------------------------------------------------------------------------- SDO_GEOMETRY(3001,28355,SDO_POINT_TYPE(0.1,0.2,0.3),NULL,NULL) SDO_GEOMETRY(4401,28355,NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(0.1,0.2,0.3,0.4)) SDO_GEOMETRY(2002,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(0.1,0.1,10,0,10,5,10,10,5,10,5,5)) SDO_GEOMETRY(3002,28355,NULL, SDO_ELEM_INFO_ARRAY(1,2,1), SDO_ORDINATE_ARRAY(0.1,0.1,1,10,0,2,10,5,3,10,10,4,5,10,5,5,5,6)) SDO_GEOMETRY(3306,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,2,1,13,2,1), SDO_ORDINATE_ARRAY(50,55,1,50,60,2,55,58,3,50,55,4,56,58,5,60,55,6,60,60,7,56,58,8)) SDO_GEOMETRY(3002,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,2,2), SDO_ORDINATE_ARRAY(10,15,3,15,20,3,20,15,3)) SDO_GEOMETRY(4402,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,2,2), SDO_ORDINATE_ARRAY(10,15,3,0,15,20,3,5.67,20,15,3,9.84)) SDO_GEOMETRY(3302,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,2,2), SDO_ORDINATE_ARRAY(10,15,0,15,20,5.67,20,15,9.84)) SDO_GEOMETRY(3002,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,4,3,1,2,1,4,2,2,10,2,1), SDO_ORDINATE_ARRAY(10,45,0,20,45,1.6,20,45,1.8,23,48,1.8,20,51,1.8,20,51,1.8,10,51,1.8)) SDO_GEOMETRY(3006,28355,NULL, SDO_ELEM_INFO_ARRAY(1,2,2,10,2,2), SDO_ORDINATE_ARRAY(50,35,3.2,55,40,3.2,60,35,3.2,65,35,4.6,70,30,5.6,75,35,2.3)) SDO_GEOMETRY(2003,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,7,2,2), SDO_ORDINATE_ARRAY(10,128,10,125,20,125,20,128,15,130,10,128)) SDO_GEOMETRY(3007,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,1003,1,16,1003,1), SDO_ORDINATE_ARRAY(1500,100,0,1900,100,0.1,1900,500,0.2,1500,500,0.3,1500,100,0,1900,500,2,2300,500,2.1,2300,900,2.2,1900,900,1.8,1900,500,2)) SDO_GEOMETRY(2004,NULL,NULL, SDO_ELEM_INFO_ARRAY(1,1,1,3,2,1,7,1003,1), SDO_ORDINATE_ARRAY(10,5,10,10,20,10,10,105,15,105,20,110,10,110,10,105)) SDO_GEOMETRY(3004,28355,NULL, SDO_ELEM_INFO_ARRAY(1,1,1,4,2,1,10,1003,1), SDO_ORDINATE_ARRAY(10,5,1,10,10,1.1,20,10,1.2,10,105,1.3,15,105,1.3,20,110,1.4,10,110,1.2,10,105,1.3)) . 14 ROWS selected
I hope these functions are of use to someone.