Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.