Top 5 Recent Articles

ARTICLES CATEGORIES

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.