Top 5 Recent Articles

ARTICLES CATEGORIES

Replacement for SDO_GEOM.RELATE – ST_Relate based on JTS

Even old dogs like me forget things and have to learn them again. You know, I am sure that I knew that SDO_GEOM.RELATE was a Spatial licensed feature and not available in Locator, but I clean forgot until a situation with a customer required me to look at an alternative.

The reason is because this customer is trying to reduce their Oracle license costs (as part of a future migration to SQL Server) by dropping their Enterprise license and going back to Standard Edition. As part of this, an audit of Spatial feature usage showed that a limited number of functions were being used eg SDO_UNION and one or two SDO_LRS functions but what I had forgotten about was the use of SDO_GEOM.RELATE.

Now my SC4O wrapper over the Java Topology Suite 1.12 has been a great success but did not include a replacement for RELATE. Dipping in to the JTS toolkit I have found, and now exposed, the relevant components to be able to release a “replacement” for SDO_GEOM.RELATE.

I put “replacement” in inverted commas as it does not replace SDO_GEOM.RELATE exactly as it is implemented. To do so would require another 3 or 4 days work or testing but I can see little value in doing so. If anyone is interested in what this work might entail please contact me.

Anyway, here is the SC4O function wrapper:

  /**
     * ST_Relate
     * Implements a license free version of sdo_geom.RELATE.
     * @note Supports JTS named topological relationships and not Oracle specific keywords like OVERLAPBDYDISJOINT
     * @param p_geom1     : sdo_geometry : geometry which will be compared to second
     * @param p_mask      : varchar2     : Mask containing DETERMINE, ANYINTERACT or a list of comma separated topological relationships
     * @param p_geom2     : sdo_geometry : geometry which will be compared to first.
     * @param p_precision : number of decimal places of precision of a geometry
     * @return String     : Result of processing
     * @throws SQLException
     * @history Simon Greener, November 2011, Original coding.
     */
   FUNCTION ST_Relate(p_geom1     IN mdsys.sdo_geometry,
                      p_mask      IN varchar2,
                      p_geom2     IN mdsys.sdo_geometry,
                      p_precision IN NUMBER)
     RETURN varchar2 Deterministic;

Here is an example test SQL statement.

 SELECT a.id1, a.geom1.sdo_gtype,
        a.id2, a.geom2.sdo_gtype,
        sdo_geom.relate(geom1,'DETERMINE',  geom2,0.05) AS sdo_relate,
            SC4O.ST_Relate(geom1,'DETERMINE',  geom2,1   ) AS jtso_relate,
        sdo_geom.relate(geom1,'TOUCH',      geom2,0.05) AS sdo_touch,
            SC4O.ST_Relate(geom1,'TOUCHES',    geom2,1   ) AS jtso_touch,
        sdo_geom.relate(geom1,'ANYINTERACT',geom2,0.05) AS sdo_anyinteract,
            SC4O.ST_Relate(geom1,'ANYINTERACT',geom2,1   ) AS jtso_anyinteract,
        sdo_geom.relate(geom2,'DETERMINE',  geom1,0.05) AS sdo_relate_rev,
            SC4O.ST_Relate(geom2,'DETERMINE',  geom1,1   ) AS jtso_relate_rev
  FROM (SELECT 104 AS id1, sdo_geometry('LINESTRING(1700 200, 1700 900, 2200 900, 1700 1300, 2200 1300)',NULL) AS geom1,
               110 AS id2, sdo_geometry('POINT(1700 200)',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 104 AS id1, sdo_geometry('LINESTRING(1700 200, 1700 900, 2200 900, 1700 1300, 2200 1300)',NULL) AS geom1,
               109 AS id2, sdo_geometry('POINT(2200 900)',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 104 AS id1, sdo_geometry('LINESTRING(1700 200, 1700 900, 2200 900, 1700 1300, 2200 1300)',NULL) AS geom1,
               111 AS id2, sdo_geometry('LINESTRING(1300 400, 2200 1300)',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               108 AS id2, sdo_geometry('POINT(1400 600)',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               109 AS id2, sdo_geometry('POINT(2200 900)',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               105 AS id2, sdo_geometry('POINT(1400 1100)',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               106 AS id2, sdo_geometry('POINT(1300 1300)',NULL)  AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               104 AS id1, sdo_geometry('LINESTRING(1700 200, 1700 900, 2200 900, 1700 1300, 2200 1300)',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               112 AS id2, sdo_geometry('POLYGON((1200 300, 1600 300, 1600 800, 1200 800, 1200 300))',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               102 AS id2, sdo_geometry('POLYGON((1400 1300, 1600 1300, 1600 1650, 1400 1650, 1400 1300))',NULL) AS geom2
          FROM dual
        UNION ALL
        SELECT 101 AS id1, sdo_geometry('POLYGON((1300 600, 1700 600, 1700 1300, 1300 1300, 1300 600))',NULL) AS geom1,
               103 AS id2, sdo_geometry('POLYGON((1600 1300, 1400 1300, 1400 1000, 1600 1000, 1600 1300))',NULL) AS geom2
          FROM dual
        ) a;

And the answer is as follows.

ST_Relate is downloadable as part of the C4O package.

I hope this is of interest to someone.