ST_DeleteVertex for Oracle SDO_Geometry based on Jaspa/JTS

I have exposed the JTS/Jaspa coordinate editing functionality via the ST_DeleteVertex function within my Spatial Companion For Oracle (SC4O). Here are some examples:

 -- ***********************************************************************************
 -- ST_DeleteVertex
 -- ***********************************************************************************
 -- Test single point
 SELECT SC4O.ST_DeleteVertex(mdsys.SDO_Geometry(3001,NULL,sdo_point_type(1.1,2.4,3.5),NULL,NULL),
                           1) AS point
   FROM dual;
 -- Result
 POINT
 --------------------------------------------
 SDO_GEOMETRY(NULL,NULL,NULL,NULL,NULL)
 --
 -- Remove vertices from multipoint
 SELECT CASE WHEN LEVEL < 4 THEN LEVEL ELSE -1 END  AS point,
        SC4O.ST_DeleteVertex(a.geom,CASE WHEN LEVEL < a.numVertices THEN LEVEL ELSE -1 END ) AS DeletedVertex
   FROM (SELECT sdo_util.getNumVertices(b.geom)+1 AS numVertices, b.geom
           FROM (SELECT SDO_Geometry(2005,NULL,NULL,sdo_elem_info_array(1,1,3),sdo_ordinate_array(1.1,1.1,2.2,2.2,3.2,3.2)) AS geom
                   FROM dual) b
        ) a
  CONNECT BY level <= a.numVertices;
 -- Results
 POINT DELETEDVERTEX
 ----- -------------------------------------------------------------------------------------------
 1     SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(2.2,2.2,3.2,3.2))
 2     SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(1.1,1.1,3.2,3.2))
 3     SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(1.1,1.1,2.2,2.2))
 -1    SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(1.1,1.1,2.2,2.2))
 --
 -- Remove first coordinate in standard LineString
 SELECT SC4O.ST_DeleteVertex(a.linestring,1)/*.Get_WKT()*/ AS geom
   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2,3.3 3.3)',NULL) AS linestring
           FROM dual) a;
 -- Results
 GEOM
 -------------------------------------------------------------------------------------------
 SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.2,2.2,3.3,3.3))
 --
 -- Try to remove a point from 2 point linestring (should get error)
 SELECT SC4O.ST_DeleteVertex(a.linestring,1).Get_WKT() AS st_geom
   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2)',NULL) AS linestring
           FROM dual) a;
 -- Results
 Error starting at line 24 IN command:
 SELECT SC4O.ST_DeleteVertex(a.linestring,1).Get_WKT() AS st_geom
   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2)',NULL) AS linestring
           FROM dual) a
 Error report:
 SQL Error: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.SQL.SQLException: Invalid NUMBER OF points IN LineString (found 1 - must be 0 OR >= 2)
 ORA-06512: at "CODESYS.SC4O", line 679
 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
 *Cause:    A Java exception OR error was signaled AND could NOT be
            resolved BY the Java code.
 *Action:   MODIFY Java code, IF this behavior IS NOT intended.
 --
 -- Try to remove 2 points from three point linestring (should get error)
 SELECT SC4O.ST_DeleteVertex(SC4O.ST_DeleteVertex(a.linestring,1),1).Get_WKT() AS st_geom
   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2,3.3 3.3)',NULL) AS linestring
           FROM dual) a;
 -- Results
 Error starting at line 29 IN command:
 SELECT SC4O.ST_DeleteVertex(SC4O.ST_DeleteVertex(a.linestring,1),1).Get_WKT() AS st_geom
   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2,3.3 3.3)',NULL) AS linestring
           FROM dual) a
 Error report:
 SQL Error: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.SQL.SQLException: Invalid NUMBER OF points IN LineString (found 1 - must be 0 OR >= 2)
 ORA-06512: at "CODESYS.SC4O", line 679
 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
 *Cause:    A Java exception OR error was signaled AND could NOT be
            resolved BY the Java code.
 *Action:   MODIFY Java code, IF this behavior IS NOT intended.
 --
 -- Remove points 1-4 in a 3D LineString, note 0 and NULL denote is the last coord
 SELECT CASE WHEN LEVEL < 4 THEN LEVEL ELSE -1 END  AS point,
        SC4O.ST_DeleteVertex(a.geom,CASE WHEN LEVEL < a.numVertices THEN LEVEL ELSE -1 END ) AS DeletedVertex
   FROM (SELECT sdo_util.getNumVertices(b.geom)+1 AS numVertices, b.geom
           FROM (SELECT SDO_Geometry(3002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1.1,1.1,9,2.2,2.2,9,3.3,3.3,9)) AS geom
                   FROM dual) b
        ) a
  CONNECT BY level <= a.numVertices;
 -- Results
 POINT DELETEDVERTEX
 ----- -----------------------------------------------------------------------------------------------
 1     SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.2,2.2,9,3.3,3.3,9))
 2     SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1.1,1.1,9,3.3,3.3,9))
 3     SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1.1,1.1,9,2.2,2.2,9))
 -1    SDO_GEOMETRY(3002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(1.1,1.1,9,2.2,2.2,9))
 --
 -- Test single polygon
 SELECT NULL AS DeletedVertex, sdo_geometry('POLYGON((2 2, 12 2, 12 7, 2 7, 2 2))',NULL) AS geom FROM dual
 UNION ALL SELECT 2 AS removedVertex, SC4O.ST_DeleteVertex(sdo_geometry('POLYGON((2 2, 12 2, 12 7, 2 7, 2 2))',NULL),2) AS geom FROM dual
 UNION ALL SELECT 3 AS removedVertex, SC4O.ST_DeleteVertex(sdo_geometry('POLYGON((2 2, 12 2, 12 7, 2 7, 2 2))',NULL),3) AS geom FROM dual;
 -- Results
 DELETEDVERTEX GEOM
 ------------- ----------------------------------------------------------------------------------------------------
 NULL          SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(2,2,12,2,12,7,2,7,2,2))
 2             SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(2,2,12,7,2,7,2,2))
 3             SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(2,2,12,2,2,7,2,2))

I hope this is useful to someone.

Leave a Reply

Your email address will not be published. Required fields are marked *