Top 5 Recent Articles

ARTICLES CATEGORIES

Free Union, Intersection, Xor and Difference Functions for Oracle Locator – Part 1

Both the OGC and SQL3/MM Spatial standards for Simple Features include four standard geoprocessing operations as part of the basic type. Here is an extract from the OpenGIS’s Simple Features Specification for SQL, Revision1.1:

2.1.1.3 Methods that support Spatial Analysis
…
Intersection(anotherGeometry:Geometry):Geometry
– Returns a geometry that represents the point set intersection of this Geometry with anotherGeometry.
Union(anotherGeometry:Geometry):Geometry
– Returns a geometry that represents the point set union of this Geometry with anotherGeometry.
Difference(anotherGeometry:Geometry):Geometry
– Returns a geometry that represents the point set difference of this Geometry with anotherGeometry.
SymDifference(anotherGeometry:Geometry):Geometry
– Returns a geometry that represents the point set symmetric difference of this Geometry with anotherGeometry.

(SymDifference is also known as XOR.)

PostGIS and SQL Server 2008 R1/2 all provide these at no cost (so does ESRI in its ST_Geometry type). Oracle, from the very first day it produced SDO_GEOMETRY, made these four methods subject to Enterprise licensing. (This was also the case for length, area, buffer etc but these have been slowly pushed back in to the basic type as each version was released – causing much confusion.)

This meant that, for a user of SDO_GEOMETRY, to access these four methods legally one had to purchase:

  • A copy of Oracle Enterprise Database;
  • A Spatial license (which also includes access to SDO_TOPO, GeoRaster, LRS etc)

While the question of licensing is one that only Oracle can make, I have always argued that the restriction of access to these basic standards defined and approved methods was a decision made without fully acknowledging or supporting how the large majority of people use the basic spatial type inside a database.

I for one have wasted hours in discussions with people about these four methods. In fact, I would say the whole issue long ago was decided in people’s minds as being mean spirited: profit before all else. Yet, the release of these four little methods might have made people a little happier and less willing to “bag out” Oracle.

Interestingly, I am starting to see conversions from Oracle to SQL Server 2008 coming across my desk. Start of a trend?

PostgreSQL/PostGIS have always had a rich SQL Spatial API (an embarassment of riches for an Oracle person) for nothing. But the real kicker is that from SQL Server 2008 onwards, Microsoft has made available such methods also for nothing across its whole database range (Enterprise down to Express). With Denali they will soon release spatial aggregates that are free and actually work fast.

For many years I have known that one could augment Oracle Locator on SE by adding in such operators via use of the Java Virtual Machine (JVM) and Java Topology Suite (JTS).

Recently, in re-writing large parts of my in-database JVM hosted shapefile exporter I took the opportunity to add in some simple calls to the necessary JTS methods to make it happen.

Here is part of the Spatial Companion For Oracle (SC4O) package that wraps the Java methods (Note: Union is an Oracle reserved word so could not be used for a function name):

   /** ========================== OVERLAY ======================== **/
 .
   /**
   * ST_Union
   * Unions two geometries together using suppied p_precision to compare coordinates.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @param p_distanceTolerance : Number : Optional maximum distance difference (see ST_TopologyPreservingSimplify)
   *                                       for use with simplifying the resultant geometry. Enter 0.0 for no simplification.
   * @return SDO_GEOMETRY : Result of Union
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Union(p_geom1             IN mdsys.sdo_geometry,
                      p_geom2             IN mdsys.sdo_geometry,
                      p_precision         IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_Union
   * Unions two geodetic geometries together using suppied p_precision to compare coordinates.
   * Computations occur in projected space described by p_srid parameter.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
   *                              being projected back to p_geom1.sdo_srid.
   * @return SDO_GEOMETRY : Result of Union
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Union(p_geom1             IN mdsys.sdo_geometry,
                      p_geom2             IN mdsys.sdo_geometry,
                      p_precision         IN NUMBER,
                      p_srid              IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_Difference
   * Computes difference between two geometries using supplied p_precision to compare coordinates.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @return SDO_GEOMETRY : Result of Difference
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Difference(p_geom1     IN mdsys.sdo_geometry,
                           p_geom2     IN mdsys.sdo_geometry,
                           p_precision IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_Difference
   * Wrapper Function ST_that enables computation of geometry difference for geodetic (long/lat)
   * geometries.  Computations occur in projected space described by p_srid parameter.
   * 
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
   *                              being projected back to p_geom1.sdo_srid.
   * @return SDO_GEOMETRY : Result of Difference
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Difference(p_geom1     IN mdsys.sdo_geometry,
                           p_geom2     IN mdsys.sdo_geometry,
                           p_precision IN NUMBER,
                           p_srid      IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_Intersection
   * Computes intersection between two geometries using suppied p_precision to compare coordinates.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @return SDO_GEOMETRY : Result of Intersection
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Intersection(p_geom1     IN mdsys.sdo_geometry,
                             p_geom2     IN mdsys.sdo_geometry,
                             p_precision IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_Intersection
   * Wrapper Function ST_that enables computation of geometry intersection for geodetic (long/lat)
   * geometries.  Computations occur in projected space described by p_srid parameter.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
   *                              being projected back to p_geom1.sdo_srid.
   * @return SDO_GEOMETRY : Result of Intersection
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Intersection(p_geom1     IN mdsys.sdo_geometry,
                             p_geom2     IN mdsys.sdo_geometry,
                             p_precision IN NUMBER,
                             p_srid      IN NUMBER )
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_Xor
   * Computes xor between two geometries using suppied p_precision to compare coordinates.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @return SDO_GEOMETRY : Result of Xor
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Xor(p_geom1     IN mdsys.sdo_geometry,
                    p_geom2     IN mdsys.sdo_geometry,
                    p_precision IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_Xor
   * Wrapper Function ST_that enables computation of geometry xor for geodetic (long/lat)
   * geometries.  Computations occur in projected space described by p_srid parameter.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
   *                              being projected back to p_geom1.sdo_srid.
   * @return SDO_GEOMETRY : Result of Xor
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_Xor(p_geom1     IN mdsys.sdo_geometry,
                    p_geom2     IN mdsys.sdo_geometry,
                    p_precision IN NUMBER,
                    p_srid      IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_SymDifference (wrapper over Xor)
   * Computes symbolic difference between two geometries using suppied p_precision to compare coordinates.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @return SDO_GEOMETRY : Result of SymDifference
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_SymDifference(p_geom1     IN mdsys.sdo_geometry,
                              p_geom2     IN mdsys.sdo_geometry,
                              p_precision IN NUMBER)
     RETURN mdsys.sdo_geometry
            Deterministic;
 .
   /**
   * ST_SymDifference
   * Wrapper Function ST_(over Xor) that enables computation of geometry symbolic difference for geodetic (long/lat)
   * geometries.  Computations occur in projected space described by p_srid parameter.
   *
   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
   *                              being projected back to p_geom1.sdo_srid.
   * @return SDO_GEOMETRY : Result of SymDifference
   * @history Simon Greener, August 2011, Original Coding
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               http://creativecommons.org/licenses/by-sa/2.5/au/
   */
   FUNCTION ST_SymDifference(p_geom1     IN mdsys.sdo_geometry,
                              p_geom2     IN mdsys.sdo_geometry,
                              p_precision IN NUMBER,
                              p_srid      IN NUMBER )
     RETURN mdsys.sdo_geometry
            Deterministic;

Once the JAR file has been loaded, we can see what classes, methods and arguments are available as follows.

 -- Methods in the JTS class relating to ST_Union etc operators
 --
 SELECT DISTINCT a.method_name
   FROM user_java_methods a
  WHERE a.name = 'com/spatialdbadvisor/dbutils/ora/JTS'
    AND a.accessibility = 'PUBLIC'
    AND a.method_name IN ('ST_Difference','ST_Intersection','ST_Union','ST_Xor')
  ORDER BY a.method_name;
 -- Results
 METHOD_NAME
 ----------------------
 ST_Difference
 ST_Intersection
 ST_Union
 ST_Xor
 .
  13 ROWS selected
 --
 -- What methods and arguments are there registered in the JVM for GeoProcessing?
 --
 SELECT f.accessibility, f.is_static, f.base_type, f.method_name, a.argument_position,
        CASE WHEN a.base_type IS NOT NULL THEN a.base_type ELSE a.argument_class END AS argument_type,
        f.return_class
   FROM (SELECT m.name, m.accessibility, m.is_static, m.base_type, REPLACE(m.method_name,'com/spatialdbadvisor/dbutils/ora/GeoProcessing',NULL) AS method_name, m.return_class, m.method_index
           FROM user_java_methods m
          WHERE m.method_name IN ('ST_Difference','ST_Intersection','ST_Union','ST_Xor')
        ) f
        INNER JOIN user_java_arguments a ON (a.name = f.name AND a.method_index = f.method_index)
  WHERE f.accessibility IS NOT NULL
 GROUP BY f.accessibility, f.is_static, f.base_type, f.method_name, a.argument_position, CASE WHEN a.base_type IS NOT NULL THEN a.base_type ELSE a.argument_class END, f.return_class
  ORDER BY f.method_name, a.argument_position;
 -- Results
 ACCESSIBILITY IS_STATIC BASE_TYPE METHOD_NAME              ARGUMENT_POSITION      ARGUMENT_TYPE              RETURN_CLASS
 ------------- --------- --------- ------------------------ ---------------------- -------------------------  ------------
 PUBLIC  YES   ST_Difference 0 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Difference 1 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Difference 2 INT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Intersection 0 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Intersection 1 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Intersection 2 INT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Union  0 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Union  1 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Union  2 INT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Xor  0 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Xor  1 oracle/SQL/STRUCT oracle/SQL/STRUCT
 PUBLIC  YES   ST_Xor  2 INT oracle/SQL/STRUCT
 .
  25 ROWS selected
 --
 -- Test a null geometry
 --
 SELECT codesys.SC4O.ST_Intersection(g1,g2,1) AS GeoProcess
   FROM (SELECT CAST(NULL AS mdsys.sdo_geometry) g1,
                 SDO_GEOMETRY(2002,32639,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(548766.398, 3956415.329, 548866.753, 3956341.844, 548845.366, 3956342.941)) g2
           FROM dual);
 -- Results
 ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.Exception: One OR other OF supplied Sdo_Geometries IS NULL.
 ORA-06512: at "CODESYS.JTS", line 25
 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.
 --
 -- Test different SRIDs..
 --
 SELECT codesys.SC4O.ST_Intersection(g1,g2,1) AS GeoProcess
   FROM (SELECT SDO_GEOMETRY(2002, NULL,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(548938.421,3956363.864,548823.852,3956379.758,548818.010,3956381.297)) g1,
                 SDO_GEOMETRY(2002,32639,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(548766.398,3956415.329,548866.753,3956341.844,548845.366,3956342.941)) g2
           FROM dual);
 -- Results
 ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.Exception: SRIDs OF Sdo_Geometries must be equal
 ORA-06512: at "CODESYS.JTS", line 25
 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.
 --
 -- Test Invalid Geometry
 --
 SELECT sdo_geom.validate_geometry(g1,0.005) polygon_with_hole,
        sdo_geom.validate_geometry(g2,0.005) self_intersecting_polygon
   FROM (SELECT SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY (1,1003,3, 5,2003,3), SDO_ORDINATE_ARRAY (50,135, 60,140, 51,136, 59,139)) g1,
                 SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(58.048, 137.595, 60.758, 139.284, 57.744, 138.276, 60.709, 138.324, 58.048, 137.595)) g2
           FROM dual);
 -- Results
 POLYGON_WITH_HOLE SELF_INTERSECTING_POLYGON
 ----------------- -------------------------
 TRUE              13349
 --
 SELECT codesys.SC4O.ST_Intersection(g1,g2,1) jts
   FROM (SELECT SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY (1,1003,3, 5,2003,3), SDO_ORDINATE_ARRAY (50,135, 60,140, 51,136, 59,139)) g1,
                 SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(58.048, 137.595, 60.758, 139.284, 57.744, 138.276, 60.709, 138.324, 58.048, 137.595)) g2
           FROM dual);
 -- Results
 ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.Exception: Converted SECOND geometry IS invalid.
 ORA-06512: at "CODESYS.JTS", line 25
 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.

The testing I have done is very simple and will be covered in another few articles.

Anyone interested in taking a copy and helping with testing and packaging, let me know. Otherwise, the install package is available here:

NOTE 1: JTS doesn’t currently support geodetic computations. If you wish to use this package with geodetic data then you should:

  • Transform (sdo_cs.transform) your data to a suitable projected coordinate system, eg UTM;
  • Call the relevant JTS function (ST_Union, ST_Intersection etc);
  • Transform the result back to you original geodetic SRID.

Worked examples are provided in other parts of this series of articles on geoprocessing.

NOTE 2: Code like this is no substitute for fully tested, production quality code. However, all I have done is create a simple wrapper around a limited set of Java Topology Suite methods. JTS is an awesome piece of open source code. It has revolutionised open source GIS. It has also been rigorously tested in many, many deployments. What I have done is nothing special: JTS does all the “heavy lifting”. On request, you can get the source code from me that shows how easy it was to do. If you wished to use this code in a production environment then you must understand that this is entirely your decision. While I am happy to provide support for what I have done, the final decision for deployment is yours and yours alone.