Building Lines into Polygons in Oracle Locator / Spatial
The Spatial Companion For Oracle (SC4O) package uses the JTS Topology Suite’s (JTS) geoprocessing/overlay functions to implement free union, intersection, difference and XOR functions for Oracle Locator.
SC4O also includes a function called:
FUNCTION ST_PolygonBuilder(p_resultSet IN codesys.SC4O.refcur_t, p_precision IN pls_integer) RETURN mdsys.sdo_geometry Deterministic;
This function is a wrapper over the JTS’s Polygonizer() class.
In essence, this function takes a collection of linestrings/multilinestrings and tries to form polygons from them.
To pass in a collection of linestrings from Oracle I have chosen to pass in a SQL resultSet, though a 2006 multilinestring could just as easily be supported. (Perhaps another day.)
First off, let’s test some bad input.
No result set
SELECT SC4O.ST_PolygonBuilder(NULL,1) AS polygon FROM dual; -- Result SQL Error: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.IllegalArgumentException: No ResultSet passed TO PolygonBuilder. ORA-06512: at "CODESYS.SC4O", line 51 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.
Result set with no sdo_geometry
SELECT SC4O.ST_PolygonBuilder(CURSOR(SELECT * FROM DUAL),1) AS polygon FROM dual; -- Result SQL Error: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.IllegalArgumentException: No SDO_GEOMETRY COLUMN can be found IN DATA TO be exported. ORA-06512: at "CODESYS.SC4O", line 51 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.
Empty result set
SELECT SC4O.ST_PolygonBuilder(CURSOR(SELECT mdsys.sdo_geometry(2002,NULL,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10)) AS geom FROM dual WHERE rownum < 1),1) AS polygon FROM dual; -- Result POLYGON ----------- NULL
Result set with one linestring (I expect the linestring back)
SELECT SC4O.ST_PolygonBuilder(CURSOR(SELECT mdsys.sdo_geometry(2002,82469,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(1,1,10,10)) AS geom FROM dual),1) AS polygon FROM dual; -- Result POLYGON -------------------------------------------------------------------------------------------------------- MDSYS.SDO_GEOMETRY(2002,82469,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,10,10))
Now, to test the function we need a set of linestrings. The best is to take an Oracle normalised rectangle, “stroke” it (rectangle2polygon) and then “explode” it into its component linestrings. If, when we supply this set to the st_PolYgonBuilder() function, we get the same polygon back then our function must be working correctly.
Create Linestrings from Stroked Normalized Rectangle
(Needs my GEOM PL/SQL package.)
SELECT mdsys.sdo_geometry(2002,82469,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(t.startCoord.x,t.startCoord.y,t.endCoord.x,t.endCoord.y)) AS line FROM TABLE(GEOM.getVector(geom.rectangle2polygon(mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(1,1,10,10))),0.05)) t -- Result LINE --------------------------------------------------------------------------------------------------------- MDSYS.SDO_GEOMETRY(2002,82469,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,10,1)) MDSYS.SDO_GEOMETRY(2002,82469,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(10,1,10,10)) MDSYS.SDO_GEOMETRY(2002,82469,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(10,10,1,10)) MDSYS.SDO_GEOMETRY(2002,82469,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(1,10,1,1))
Create polygon from a polygon’s lines by “sending” the lineset into the PolygonBuilder() as follows:
WITH LINES AS ( SELECT mdsys.sdo_geometry(2002,82469,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(t.startCoord.x,t.startCoord.y,t.endCoord.x,t.endCoord.y)) AS line FROM TABLE(GEOM.getVector(geom.rectangle2polygon(mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(1,1,10,10))),0.05)) t ) SELECT SC4O.ST_PolygonBuilder(CURSOR(SELECT * FROM LINES),1) AS polygon FROM dual; -- Result POLYGON -------------------------------------------------------------------------------------------------------------------------- MDSYS.SDO_GEOMETRY(2003,82469,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(1,10,1,1,10,1,10,10,1,10))
The result appears to be the original polygon. Let’s test this a different way.
Compare start/end polygons
SELECT sdo_geom.relate(f.opoly,'DETERMINE',f.rpoly,0.05) AS compare, f.rpoly FROM (SELECT p.opoly, SC4O.ST_PolygonBuilder(CURSOR(SELECT mdsys.sdo_geometry(2002,82469,NULL,sdo_elem_info_array(1,2,1),sdo_ordinate_array(t.startCoord.x,t.startCoord.y,t.endCoord.x,t.endCoord.y)) FROM TABLE(geom.getVector(p.opoly,0.05)) t),1) AS rpoly FROM (SELECT GEOM.rectangle2polygon(mdsys.sdo_geometry(2003,82469,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(1,1,10,10))) AS opoly FROM dual) p ) f; -- Result COMPARE RPOLY ------- --------------------------------------------------------------------------------------------------------------------------- EQUAL MDSYS.SDO_GEOMETRY(2003,82469,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(10,10,1,10,1,1,10,1,10,10))
You can download the ST_PolygonBuilder functions by clicking on this link (after filling in the email, select the link “EXPORTER and SC4O (geoprocessing) Installer”.
If anyone uses this function and has any issues or suggestions please contact me directly.