It was a showdown between the three different methods to compare geometry in Oracle today. My lesson for the day was what the subtle differences are between SDO_RELATE and SDO_GEOM.RELATE.

I had a left outer join to a table with a geometry attribute  in it. If there was no matching record in the table I was joining to, it would return null for the geometry:

SELECT a.id, b.name, b.geometry
FROM   voyage a
LEFT OUTER JOIN voyage_track b ON b.voyage_id = a.voyage_id

OK, simple stuff, but I wanted to only return voyage tracks that intersect with the MBR of a user-specified geometry. To make things difficult, I also needed to return any records that ended up with a null geometry. So in other words, I needed to filter out only the records that have a geometry, which don't intersect with our user-specified MBR.

We can rule one method out straight up: SDO_FILTER. Why? SDO_FILTER is fast but it compares the MBR of both geometry objects that you feed it. If I have a voyage track that goes from Tasmania to New Zealand the MBR is going to be quite large, but I just wanted to see if the voyage had passed through a particular place.

Next I tried SDO_GEOM.RELATE, using the 'ANYINTERACT' property:

SELECT a.id, b.name, b.geometry
FROM   voyage a
LEFT OUTER JOIN voyage_track b ON b.voyage_id = a.voyage_id
WHERE  (
    b.geometry IS NULL
) OR (
    SDO_GEOM.RELATE (
       b.geometry,       
       'ANYINTERACT',
       SDO_GEOMETRY(2003, 8307, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3),
           SDO_ORDINATE_ARRAY(131.563, -65.246, 134.421, -62.388)
       ),
       0.000005
    ) = 'TRUE'
) 

One would have thought that this would be a reasonable way to approach the task. ERRRRR: fail. Why? Oracle, for some reason, doesn't always check what the left predicate of an OR statement evaluates to first, before trying the right predicate. I mean, think about this statement:

IF (a OR b)

Now, do you think it would be optimal to continue evaluating what 'b' is if you already knew 'a' to be true? Well guess what, Oracle does, well, sometimes. So even if my geometry was null, it was still trying to compare it with a real geometry object using SDO_GEOM.RELATE. This confused me somewhat!

ORA-13050: unable to construct spatial object

Looking further into the docs I find:

A null value (empty geometry) as an input parameter to a geometry function (for example, SDO_GEOM.RELATE) produces an error.

Enter SDO_RELATE. It turns out that Oracle have three functions that do more or less the same thing in three different ways, and yielding three different results. Oracle say:

Unlike with the SDO_GEOM.RELATE function, DISJOINT and DETERMINE masks are not allowed in the relationship mask with the SDO_RELATE operator. This is because SDO_RELATE uses the spatial index to find candidates that may interact, and the information to satisfy DISJOINT or DETERMINE is not present in the index.

OK, fine. So I gather that means SDO_GEOM.RELATE will accept two geometry objects that you feed it and will not work as efficient as SDO_RELATE it whereas SDO_RELATE is optimised to use indexes on existing tables to help out with the query. So I tried using SDO_RELATE and it started working:

SELECT a.id, b.name, b.geometry
FROM   voyage a
LEFT OUTER JOIN voyage_track b ON b.voyage_id = a.voyage_id
WHERE  (
    b.geometry IS NULL
) OR (
    SDO_RELATE (
       b.geometry,
       SDO_GEOMETRY(2003, 8307, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3),
           SDO_ORDINATE_ARRAY(131.563, -65.246, 134.421, -62.388)
       ),
       'mask=anyinteract'
    ) = 'TRUE'
) 

Well, yippee, it actually returns null if there isn't any geometry, and for all other rows it returns only the ones with geometries that intersect.

Now, just to explore a bit further, I tried SDO_GEOM.RELATE again, and, uh... even though the docs contradict it... it works with a null value passed into it. I think I'll stick to SDO_RELATE for now though - it at least uses the spatial index on the voyage_track table.