Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

ST_DeleteVertex for Oracle SDO_Geometry based on Jaspa/JTS

Monday January 30 2012 at 11:48

KeywordsST_DeleteVertex remove point delete vertex JTS Jaspa SDO_Geometry SC4O
Summary

A missing function from the Ogc/SqlMM Apis is a function that allows a user, via Sql, to modify a geometry by removing one or more points. Here is just such a function for Sdo_Geometry users.

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:

  1. -- ***********************************************************************************
  2. -- ST_DeleteVertex
  3. -- ***********************************************************************************
  4. -- Test single point
  5. SELECT SC4O.ST_DeleteVertex(mdsys.SDO_Geometry(3001,NULL,sdo_point_type(1.1,2.4,3.5),NULL,NULL),
  6.                           1) AS point
  7.   FROM dual;
  8. -- Result
  9. POINT
  10. --------------------------------------------
  11. SDO_GEOMETRY(NULL,NULL,NULL,NULL,NULL)
  12. --
  13. -- Remove vertices from multipoint
  14. SELECT CASE WHEN LEVEL < 4 THEN LEVEL ELSE -1 END  AS point,
  15.        SC4O.ST_DeleteVertex(a.geom,CASE WHEN LEVEL < a.numVertices THEN LEVEL ELSE -1 END ) AS DeletedVertex
  16.   FROM (SELECT sdo_util.getNumVertices(b.geom)+1 AS numVertices, b.geom
  17.           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
  18.                   FROM dual) b
  19.        ) a
  20.  CONNECT BY level <= a.numVertices;
  21. -- Results
  22. POINT DELETEDVERTEX
  23. ----- -------------------------------------------------------------------------------------------
  24. 1     SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(2.2,2.2,3.2,3.2))
  25. 2     SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(1.1,1.1,3.2,3.2))
  26. 3     SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(1.1,1.1,2.2,2.2))
  27. -1    SDO_GEOMETRY(2005,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1,2),SDO_ORDINATE_ARRAY(1.1,1.1,2.2,2.2))
  28. --
  29. -- Remove first coordinate in standard LineString
  30. SELECT SC4O.ST_DeleteVertex(a.linestring,1)/*.Get_WKT()*/ AS geom
  31.   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2,3.3 3.3)',NULL) AS linestring
  32.           FROM dual) a;
  33. -- Results
  34. GEOM
  35. -------------------------------------------------------------------------------------------
  36. SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(2.2,2.2,3.3,3.3))
  37. --
  38. -- Try to remove a point from 2 point linestring (should get error)
  39. SELECT SC4O.ST_DeleteVertex(a.linestring,1).Get_WKT() AS st_geom
  40.   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2)',NULL) AS linestring
  41.           FROM dual) a;
  42. -- Results
  43. Error starting at line 24 IN command:
  44. SELECT SC4O.ST_DeleteVertex(a.linestring,1).Get_WKT() AS st_geom
  45.   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2)',NULL) AS linestring
  46.           FROM dual) a
  47. Error report:
  48. 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)
  49. ORA-06512: at "CODESYS.SC4O", line 679
  50. 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
  51. *Cause:    A Java exception OR error was signaled AND could NOT be
  52.            resolved BY the Java code.
  53. *Action:   MODIFY Java code, IF this behavior IS NOT intended.
  54. --
  55. -- Try to remove 2 points from three point linestring (should get error)
  56. SELECT SC4O.ST_DeleteVertex(JTS.ST_DeleteVertex(a.linestring,1),1).Get_WKT() AS st_geom
  57.   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2,3.3 3.3)',NULL) AS linestring
  58.           FROM dual) a;
  59. -- Results
  60. Error starting at line 29 IN command:
  61. SELECT SC4O.ST_DeleteVertex(JTS.ST_DeleteVertex(a.linestring,1),1).Get_WKT() AS st_geom
  62.   FROM (SELECT sdo_geometry('LINESTRING(1.1 1.1,2.2 2.2,3.3 3.3)',NULL) AS linestring
  63.           FROM dual) a
  64. Error report:
  65. 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)
  66. ORA-06512: at "CODESYS.SC4O", line 679
  67. 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
  68. *Cause:    A Java exception OR error was signaled AND could NOT be
  69.            resolved BY the Java code.
  70. *Action:   MODIFY Java code, IF this behavior IS NOT intended.
  71. --
  72. -- Remove points 1-4 in a 3D LineString, note 0 and NULL denote is the last coord
  73. SELECT CASE WHEN LEVEL < 4 THEN LEVEL ELSE -1 END  AS point,
  74.        SC4O.ST_DeleteVertex(a.geom,CASE WHEN LEVEL < a.numVertices THEN LEVEL ELSE -1 END ) AS DeletedVertex
  75.   FROM (SELECT sdo_util.getNumVertices(b.geom)+1 AS numVertices, b.geom
  76.           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
  77.                   FROM dual) b
  78.        ) a
  79.  CONNECT BY level <= a.numVertices;
  80. -- Results
  81. POINT DELETEDVERTEX
  82. ----- -----------------------------------------------------------------------------------------------
  83. 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))
  84. 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))
  85. 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))
  86. -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))
  87. --
  88. -- Test single polygon
  89. SELECT NULL AS DeletedVertex, sdo_geometry('POLYGON((2 2, 12 2, 12 7, 2 7, 2 2))',NULL) AS geom FROM dual
  90. 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
  91. 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;
  92. -- Results
  93. DELETEDVERTEX GEOM
  94. ------------- ----------------------------------------------------------------------------------------------------
  95. 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))
  96. 2             SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(2,2,12,7,2,7,2,2))
  97. 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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment