Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles

    GeoRaptor 4.x Update 2 (Project has Stalled)
    Tip: Drop that Spatial Index!
    Convert Single Geometry to Multi-part Geometry in Oracle Spatial
    Optimized Rectangle to 5 Point Polygon
    Centroid Package now supports Y ordinate seeding
    Convert GeoJSON document to Sdo_Geometry objects
    Implementation Of Travelling Salesman Problem
    Create Polygon From Bearings And Distances
    Function That Returns a Compass Point From a Whole Circle Bearing
    Playing around with Centroids by using different seed values
    Simple Oracle C Sprintf or Java String.format
    Some Oriented Point Functions
    Extracting Inner Rings Changed Ordinate Ordering: A Trap For Players Who Don't Read Documentation!
    PLS-00306: wrong number or types of arguments in call to 'SDO_GEOMETRY'
    Converting Google Earth Formatted Longitude/Latitude points to decimal degrees
    Oracle Business Intelligence Warehousing and Analytics - Spatial Summit
    How far inside, is inside? Measuring actual distance.
    Noding and building a polygon from single, overlapping linestrings
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison
    ST_VertexN / ST_PointN - Extracting a specific point from any geometry
    Convert Single Point stored in SDO_ORDINATES to SDO_POINT_TYPE
    Aggregate APPEND Islands and XOR polygons
    Circular Arcs in Geodetic Polygons
    Some SDO_GEOMETRY/DIMINFO handling functions
    Applying And Extending Oracle Spatial - Book Released
    Changing all DIMINFO sdo_tolerance values for all metadata records in one go.
    Building Polygons from Incomplete Linestrings using ST_PolygonBuilder
    Computing Cardinal Directions to nearby geometries
    Intersecting two aggregated polygon layers with SC4O
    Spatial and Oracle 12c
    Update Triggers and SDO_GEOMETRY Equality
    Duplicate Geometry data and Data Models
    CENTROID package update
    How to calculate cumulative length of a linestring
    Useful Package of Wrapper Functions for Sdo_Util.AffineTransforms
    Compute Location from known Lat/Long point using delta easting and northing in miles
    SDO_AGGR_SET_UNION
    Sorting SDO_GEOMETRY data using the ORDER BY clause of a SELECT statement
    Creating linestrings from points
    Rounding Coordinates or Ordinates in SDO_GEOMETRY
    Effects of Sdo_Geometry Ordinate Precision on Performance
    Effects of Sdo_Geometry Ordinate Precision on Storage
    The Spatial filtering of geometries: The effect of tolerances on relationships
    Application of Delaunay Triangulation and Inverse Distance Weighting (IDW) in Oracle for Soils Interpolation
    Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema
    CENTROID package - Tips for Use
    Announcing the Spatial Companion For Oracle (SC4O)
    Filtering Rings (Oracle Spatial)
    Splitting a polygon using one or more linestrings
    isValid, isSimple, Dimension and CoordDim methods for SDO_Geometry
    Line Merging or Collecting lines together: ST_LineMerger
    ST_DeleteVertex for Oracle SDO_Geometry based on Jaspa/JTS
    3D/4D and SRID aware Conversion functions for SDO_Geometry: WKT and EWKT
    Topological vs Non-Topological Simplification/Generalization of Aggregated Area Geometies in Oracle
    Filtering very short linestrings via bitmap function index
    CENTROID For Oracle
    Gridding a sdo_geometry line/polygon object (Oracle)
    Finding centre and radius of a circular geometry
    Constraining geometry type for sdo_geometry column in a table.
    CASE Statements and SDO_GEOMETRY
    The Power of Constraints and Indexes for Spatial Constraints: stopping duplicate points
    Replacement for SDO_GEOM.RELATE - ST_Relate based on JTS
    Changing Oracle Spatial Index Parameters on existing index
    Writing Excel Spreadsheets files from within the Oracle database using Java and PL/SQL
    Writing xSV (eg csv) files from within the Oracle database using Java and PL/SQL
    A simple spike finder for Spatial/Locator
    JTS Java class compilation for 11g and above
    Random Spatial Search Procedure
    Geometry Snapping using JTS in Oracle
    Exposing JTS's MinimumBoundingCircle functionality
    Exposing JTS's Densifier functionality
    Using JTS's Comparison Functions - HausdorffSimilarityMeasure & AreaSimilarityMeasure with SDO_GEOMETRY
    Free JTS-based Area/Length Functions
    Handy way of systematically fixing polygon geometries with 13349 and other errors
    Standalone CENTROID package now available for download
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 4 Processing Geodetic data
    Configurable Buffer: JTS and Oracle
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 3
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 2
    Free Union, Intersection, Xor and Difference Functions for Oracle Locator - Part 1
    Building Lines into Polygons in Oracle Locator / Spatial
    Finding Intersection Points between Line and Polygon
    SDO2GeoJSON
    Free version of sdo_length
    Alternative to my SQL based GetNumRings function
    External Tables and SDO_Geometry data.
    layer_gtype keyword issue when indexing linear data on 11g
    String Tokenizer for Oracle
    Free Aggregate Method for Concatenating 2D Lines in Oracle Locator 10g
    Reducing 5 Vertex Polygon to Optimized Rectangle
    Square Buffer
    Converting decimal seconds to string
    SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT - 13356 Issues
    Valid conversion unit values for Oracle sdo_geom.sdo_length()
    Removing Steps in Gridded Vector Data - SmoothGrid for Oracle
    Oracle Spatial DISJOINT search/filtering
    Creating SDO_Geometry from geometric data recorded in the columns of a table
    Concave Hull Geometries in Oracle 11gR2
    Projecting SDO_GEOM_METADATA DIMINFO XY ordinates
    Instantiating MDSYS.VERTEX_TYPE
    New PL/SQL Packages - Rotate oriented point
    GeoRaptor Development Team
    Fast Refreshing Materialized View Containing SDO_GEOMETRY and SDO_GEOM.SDO_AREA function
    Performance of PL/SQL Functions using SQL vs Pure Code
    Implementing the BEST VicGrid Projection in Oracle 10gR2
    Making Sdo Geometry Metadata Update Generic Code
    ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
    Extract Polygons from Compound Polygon
    Detecting sdo_geometries with compound (3-point Arcs) segments
    GEOMETRY_COLUMNS for Oracle Spatial
    Convert GML to SDO_Geometry in Oracle 10gR2
    Spatial Sorting of Data via Morton Key
    Swapping Ordinates in an SDO_GEOMETRY object
    New To_3D Function
    Extend (Reduce/Contract/Skrink) Function for Oracle
    Loading and Processing GPX 1.1 files using Oracle XMLDB
    Loading Spatial Data from an external CSV file in Oracle
    Calling the Oracle Spatial shapefile loader from within the Oracle database itself
    Implementing SDO_VertexUpdate/ST_VertexUpdate for Oracle
    Implementing SDO_RemovePoint/ST_RemovePoint for Oracle
    Implementing SDO_AddPoint/ST_AddPoint for Oracle
    ESRI ArcSDE Exverted and Inverted Polygons and Oracle Spatial
    Funky Fix Ordinates By Formula
    Implementing a SetPoint/ST_SetPoint function in Oracle
    Implementing an ST_SnapToGrid (PostGIS) function for Oracle Spatial
    Generating random point data
    Implementing an Affine/ST_Affine function for Oracle Spatial
    Implementing a Scale/ST_Scale function for Oracle Spatial
    Implementing a Parallel/ST_Parallel function for linestring data for Oracle Spatial
    Implementing a Rotate/ST_Rotate function for Oracle Spatial
    Limiting table list returned when connecting to Oracle Database using ODBC
    ST_Azimuth for Oracle: AKA Cogo.Bearing
    Implementing a Translate/ST_Translate/Move function for Oracle Spatial
    Elem_Info_Array Processing: An alternative to SDO_UTIL.GetNumRings and querying SDO_ELEM_INFO itself
    Minumum Bounding Rectangle (MBR) Object Type for Oracle
    How to extract elements from the result of an sdo_intersection of two polygons.
    How to restart a database after failed parameter change
    Fixing failed spatial indexes after import using data pump
    generate_series: an Oracle implementation in light of SQL Design Patterns
    Multi-Centroid Shootout
    Oracle Spatial Centroid Shootout
    On the use of ROLLUP in Oracle SELECT statements
    Surrounding Parcels
    Spatial Pipelining
    Using Oracle's SDO_NN Operator - Some examples
    Converting distances and units of measure in Oracle Locator
    Split Sdo_Geometry Linestring at a known point
    Forcing an Sdo_Geometry object to contain only points, lines or areas
    Unpacking USER_SDO_GEOM_METADATA's DIMINFO structure using SQL
    Generating multi-points from single point records in Oracle Spatial
    Object Tables of Sdo_Geometry
    Oracle Locator vs Oracle Spatial: A Reflection on Oracle Licensing of the SDO_GEOM Package
    FAST REFRESHing of Oracle Materialized Views containing Sdo_Geometry columns
    Australian MGA/AMG Zone Calculation from geographic (longitude/latitude) data
    Loading Shapefiles (SHP) into Oracle Spatial
    Oracle Spatial Mapping and Map Rendering Performance Tips
    The significance of sdo_lb/sdo_ub in USER_SDO_GEOM_METDATA: Do I need it?
    Oracle Spatial Forum - Melbourne April 2007
    Layer_GTypes for spatial indexes
    Oracle's SQL/MM Compliant Types
    Tips and Tricks

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.

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

Wednesday August 17 2011 at 06:23

Keywordsfree union intersection difference xor geoprocessing functions oracle sdo_geometry
Summary

This article introduces my exposure of the Java Topology Suite’s union, insersection, difference and xor geoprocessing functions within the Oracle database Java Virtual Machine. These functions are free, drop-in replacements for Oracle’s sdo_geom.sdo_union etc functions for processing planar/projected data (they do not support geographic/geodetic) data.

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:

  1. A copy of Oracle Enterprise Database;
  2. 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):

  1.   /** ========================== OVERLAY ======================== **/
  2. .
  3.   /**
  4.   * ST_Union
  5.   * Unions two geometries together using suppied p_precision to compare coordinates.
  6.   *
  7.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  8.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  9.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  10.   * @param p_distanceTolerance : Number : Optional maximum distance difference (see ST_TopologyPreservingSimplify)
  11.   *                                       for use with simplifying the resultant geometry. Enter 0.0 for no simplification.
  12.   * @return SDO_GEOMETRY : Result of Union
  13.   * @history Simon Greener, August 2011, Original Coding
  14.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  15.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  16.   */
  17.   FUNCTION ST_Union(p_geom1             IN mdsys.sdo_geometry,
  18.                     p_geom2             IN mdsys.sdo_geometry,
  19.                     p_precision         IN NUMBER)
  20.     RETURN mdsys.sdo_geometry
  21.            Deterministic;
  22. .
  23.   /**
  24.   * ST_Union
  25.   * Unions two geodetic geometries together using suppied p_precision to compare coordinates.
  26.   * Computations occur in projected space described by p_srid parameter.
  27.   *
  28.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  29.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  30.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  31.   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
  32.   *                              being projected back to p_geom1.sdo_srid.
  33.   * @return SDO_GEOMETRY : Result of Union
  34.   * @history Simon Greener, August 2011, Original Coding
  35.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  36.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  37.   */
  38.   FUNCTION ST_Union(p_geom1             IN mdsys.sdo_geometry,
  39.                     p_geom2             IN mdsys.sdo_geometry,
  40.                     p_precision         IN NUMBER,
  41.                     p_srid              IN NUMBER)
  42.     RETURN mdsys.sdo_geometry
  43.            Deterministic;
  44. .
  45.   /**
  46.   * ST_Difference
  47.   * Computes difference between two geometries using supplied p_precision to compare coordinates.
  48.   *
  49.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  50.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  51.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  52.   * @return SDO_GEOMETRY : Result of Difference
  53.   * @history Simon Greener, August 2011, Original Coding
  54.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  55.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  56.   */
  57.   FUNCTION ST_Difference(p_geom1     IN mdsys.sdo_geometry,
  58.                          p_geom2     IN mdsys.sdo_geometry,
  59.                          p_precision IN NUMBER)
  60.     RETURN mdsys.sdo_geometry
  61.            Deterministic;
  62. .
  63.   /**
  64.   * ST_Difference
  65.   * Wrapper Function ST_that enables computation of geometry difference for geodetic (long/lat)
  66.   * geometries.  Computations occur in projected space described by p_srid parameter.
  67.   *
  68.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  69.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  70.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  71.   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
  72.   *                              being projected back to p_geom1.sdo_srid.
  73.   * @return SDO_GEOMETRY : Result of Difference
  74.   * @history Simon Greener, August 2011, Original Coding
  75.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  76.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  77.   */
  78.   FUNCTION ST_Difference(p_geom1     IN mdsys.sdo_geometry,
  79.                          p_geom2     IN mdsys.sdo_geometry,
  80.                          p_precision IN NUMBER,
  81.                          p_srid      IN NUMBER)
  82.     RETURN mdsys.sdo_geometry
  83.            Deterministic;
  84. .
  85.   /**
  86.   * ST_Intersection
  87.   * Computes intersection between two geometries using suppied p_precision to compare coordinates.
  88.   *
  89.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  90.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  91.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  92.   * @return SDO_GEOMETRY : Result of Intersection
  93.   * @history Simon Greener, August 2011, Original Coding
  94.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  95.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  96.   */
  97.   FUNCTION ST_Intersection(p_geom1     IN mdsys.sdo_geometry,
  98.                            p_geom2     IN mdsys.sdo_geometry,
  99.                            p_precision IN NUMBER)
  100.     RETURN mdsys.sdo_geometry
  101.            Deterministic;
  102. .
  103.   /**
  104.   * ST_Intersection
  105.   * Wrapper Function ST_that enables computation of geometry intersection for geodetic (long/lat)
  106.   * geometries.  Computations occur in projected space described by p_srid parameter.
  107.   *
  108.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  109.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  110.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  111.   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
  112.   *                              being projected back to p_geom1.sdo_srid.
  113.   * @return SDO_GEOMETRY : Result of Intersection
  114.   * @history Simon Greener, August 2011, Original Coding
  115.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  116.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  117.   */
  118.   FUNCTION ST_Intersection(p_geom1     IN mdsys.sdo_geometry,
  119.                            p_geom2     IN mdsys.sdo_geometry,
  120.                            p_precision IN NUMBER,
  121.                            p_srid      IN NUMBER )
  122.     RETURN mdsys.sdo_geometry
  123.            Deterministic;
  124. .
  125.   /**
  126.   * ST_Xor
  127.   * Computes xor between two geometries using suppied p_precision to compare coordinates.
  128.   *
  129.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  130.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  131.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  132.   * @return SDO_GEOMETRY : Result of Xor
  133.   * @history Simon Greener, August 2011, Original Coding
  134.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  135.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  136.   */
  137.   FUNCTION ST_Xor(p_geom1     IN mdsys.sdo_geometry,
  138.                   p_geom2     IN mdsys.sdo_geometry,
  139.                   p_precision IN NUMBER)
  140.     RETURN mdsys.sdo_geometry
  141.            Deterministic;
  142. .
  143.   /**
  144.   * ST_Xor
  145.   * Wrapper Function ST_that enables computation of geometry xor for geodetic (long/lat)
  146.   * geometries.  Computations occur in projected space described by p_srid parameter.
  147.   *
  148.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  149.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  150.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  151.   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
  152.   *                              being projected back to p_geom1.sdo_srid.
  153.   * @return SDO_GEOMETRY : Result of Xor
  154.   * @history Simon Greener, August 2011, Original Coding
  155.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  156.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  157.   */
  158.   FUNCTION ST_Xor(p_geom1     IN mdsys.sdo_geometry,
  159.                   p_geom2     IN mdsys.sdo_geometry,
  160.                   p_precision IN NUMBER,
  161.                   p_srid      IN NUMBER)
  162.     RETURN mdsys.sdo_geometry
  163.            Deterministic;
  164. .
  165.   /**
  166.   * ST_SymDifference (wrapper over Xor)
  167.   * Computes symbolic difference between two geometries using suppied p_precision to compare coordinates.
  168.   *
  169.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  170.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  171.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  172.   * @return SDO_GEOMETRY : Result of SymDifference
  173.   * @history Simon Greener, August 2011, Original Coding
  174.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  175.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  176.   */
  177.   FUNCTION ST_SymDifference(p_geom1     IN mdsys.sdo_geometry,
  178.                             p_geom2     IN mdsys.sdo_geometry,
  179.                             p_precision IN NUMBER)
  180.     RETURN mdsys.sdo_geometry
  181.            Deterministic;
  182. .
  183.   /**
  184.   * ST_SymDifference
  185.   * Wrapper Function ST_(over Xor) that enables computation of geometry symbolic difference for geodetic (long/lat)
  186.   * geometries.  Computations occur in projected space described by p_srid parameter.
  187.   *
  188.   * @param p_geom1       : sdo_geometry : first geometry subject to overlay action
  189.   * @param p_geom2       : sdo_geometry : second geometry subject to overlay action
  190.   * @param p_precision   : int : number of decimal places of precision when comparing ordinates.
  191.   * @param p_srid        : int : SRID of projected space in which actual overlay occurs before
  192.   *                              being projected back to p_geom1.sdo_srid.
  193.   * @return SDO_GEOMETRY : Result of SymDifference
  194.   * @history Simon Greener, August 2011, Original Coding
  195.   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
  196.   *               http://creativecommons.org/licenses/by-sa/2.5/au/
  197.   */
  198.   FUNCTION ST_SymDifference(p_geom1     IN mdsys.sdo_geometry,
  199.                             p_geom2     IN mdsys.sdo_geometry,
  200.                             p_precision IN NUMBER,
  201.                             p_srid      IN NUMBER )
  202.     RETURN mdsys.sdo_geometry
  203.            Deterministic;

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

  1. -- Methods in the JTS class relating to ST_Union etc operators
  2. --
  3. SELECT DISTINCT a.method_name
  4.   FROM user_java_methods a
  5.  WHERE a.name = 'com/spatialdbadvisor/dbutils/ora/JTS'
  6.    AND a.accessibility = 'PUBLIC'
  7.    AND a.method_name IN ('ST_Difference','ST_Intersection','ST_Union','ST_Xor')
  8.  ORDER BY a.method_name;
  9. -- Results
  10. METHOD_NAME
  11. ----------------------
  12. ST_Difference
  13. ST_Intersection
  14. ST_Union
  15. ST_Xor
  16. .
  17.  13 ROWS selected
  18. --
  19. -- What methods and arguments are there registered in the JVM for GeoProcessing?
  20. --
  21. SELECT f.accessibility, f.is_static, f.base_type, f.method_name, a.argument_position,
  22.        CASE WHEN a.base_type IS NOT NULL THEN a.base_type ELSE a.argument_class END AS argument_type,
  23.        f.return_class
  24.   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
  25.           FROM user_java_methods m
  26.          WHERE m.method_name IN ('ST_Difference','ST_Intersection','ST_Union','ST_Xor')
  27.        ) f
  28.        INNER JOIN user_java_arguments a ON (a.name = f.name AND a.method_index = f.method_index)
  29.  WHERE f.accessibility IS NOT NULL
  30. 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
  31.  ORDER BY f.method_name, a.argument_position;
  32. -- Results
  33. ACCESSIBILITY IS_STATIC BASE_TYPE METHOD_NAME              ARGUMENT_POSITION      ARGUMENT_TYPE              RETURN_CLASS
  34. ------------- --------- --------- ------------------------ ---------------------- -------------------------  ------------
  35. PUBLIC  YES   ST_Difference 0 oracle/SQL/STRUCT oracle/SQL/STRUCT
  36. PUBLIC  YES   ST_Difference 1 oracle/SQL/STRUCT oracle/SQL/STRUCT
  37. PUBLIC  YES   ST_Difference 2 INT oracle/SQL/STRUCT
  38. PUBLIC  YES   ST_Intersection 0 oracle/SQL/STRUCT oracle/SQL/STRUCT
  39. PUBLIC  YES   ST_Intersection 1 oracle/SQL/STRUCT oracle/SQL/STRUCT
  40. PUBLIC  YES   ST_Intersection 2 INT oracle/SQL/STRUCT
  41. PUBLIC  YES   ST_Union  0 oracle/SQL/STRUCT oracle/SQL/STRUCT
  42. PUBLIC  YES   ST_Union  1 oracle/SQL/STRUCT oracle/SQL/STRUCT
  43. PUBLIC  YES   ST_Union  2 INT oracle/SQL/STRUCT
  44. PUBLIC  YES   ST_Xor  0 oracle/SQL/STRUCT oracle/SQL/STRUCT
  45. PUBLIC  YES   ST_Xor  1 oracle/SQL/STRUCT oracle/SQL/STRUCT
  46. PUBLIC  YES   ST_Xor  2 INT oracle/SQL/STRUCT
  47. .
  48.  25 ROWS selected
  49. --
  50. -- Test a null geometry
  51. --
  52. SELECT codesys.SC4O.ST_Intersection(g1,g2,1) AS GeoProcess
  53.   FROM (SELECT CAST(NULL AS mdsys.sdo_geometry) g1,
  54.                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
  55.           FROM dual);
  56. -- Results
  57. ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.Exception: One OR other OF supplied Sdo_Geometries IS NULL.
  58. ORA-06512: at "CODESYS.JTS", line 25
  59. 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
  60. *Cause:    A Java exception OR error was signaled AND could NOT be
  61.            resolved BY the Java code.
  62. *Action:   MODIFY Java code, IF this behavior IS NOT intended.
  63. --
  64. -- Test different SRIDs..
  65. --
  66. SELECT codesys.SC4O.ST_Intersection(g1,g2,1) AS GeoProcess
  67.   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,
  68.                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
  69.           FROM dual);
  70. -- Results
  71. ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.Exception: SRIDs OF Sdo_Geometries must be equal
  72. ORA-06512: at "CODESYS.JTS", line 25
  73. 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
  74. *Cause:    A Java exception OR error was signaled AND could NOT be
  75.            resolved BY the Java code.
  76. *Action:   MODIFY Java code, IF this behavior IS NOT intended.
  77. --
  78. -- Test Invalid Geometry
  79. --
  80. SELECT sdo_geom.validate_geometry(g1,0.005) polygon_with_hole,
  81.        sdo_geom.validate_geometry(g2,0.005) self_intersecting_polygon
  82.   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,
  83.                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
  84.           FROM dual);
  85. -- Results
  86. POLYGON_WITH_HOLE SELF_INTERSECTING_POLYGON
  87. ----------------- -------------------------
  88. TRUE              13349
  89. --
  90. SELECT codesys.SC4O.ST_Intersection(g1,g2,1) jts
  91.   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,
  92.                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
  93.           FROM dual);
  94. -- Results
  95. ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.lang.Exception: Converted SECOND geometry IS invalid.
  96. ORA-06512: at "CODESYS.JTS", line 25
  97. 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
  98. *Cause:    A Java exception OR error was signaled AND could NOT be
  99.            resolved BY the Java code.
  100. *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:

  1. Transform (sdo_cs.transform) your data to a suitable projected coordinate system, eg UTM;
  2. Call the relevant JTS function (ST_Union, ST_Intersection etc);
  3. 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.

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 [1]

Hi Simon,

I could not able to find the jar file. Could you please tell me from where I can download the jar (with name please).

Thanks,
Sanket.

— Sanket · 17 September 2015, 16:36 · #