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.