What's the proper way to do a spatial join in Oracle?

Question

Oracle has a table function called SDO_JOIN that is used to do join tables based on spatial relations. An example query to find what neighbourhood a house is in is something like this:

select 
    house.address,
    neighbourhood.name
from table(sdo_join('HOUSE', 'GEOMETRY', 'NEIGHBOURHOOD', 'GEOMETRY', 'mask=INSIDE')) a 
inner join house
    on a.rowid1 = house.rowid
inner join neighbourhood
    on a.rowid2 = neighbourhood.rowid;

But I get the same result by just doing a regular join with a spatial relation in the on clause:

select 
    house.address,
    neighbourhood.name
from house
inner join neighbourhood
    on sdo_inside(house.geometry, neighbourhood.geometry) = 'TRUE';

I prefer the second method because I think it's easier to understand what exactly is happening, but I wasn't able to find any Oracle documentation on whether or not this is the proper way to do a spatial join.

Is there any difference between these two methods? If there is, what? If there isn't, which style is more common?

Answer 1

The difference is in performance.

The first approach (SDO_JOIN) isolates the candidates by matching the RTREE indexes on each table.

The second approach will search the HOUSE table for each geometry of the NEIGHBORHOOD table.

So much depends on how large your tables are, and in particular, how large the NEIGHBORHOOD table is - or more precisely, how many rows of the NEIGHBORHOOD table your query actually uses. If the NEIGHBORHOOD table is small (less than 1000 rows) then the second approach is good (and the size of the HOUSE table does not matter).

On the other hand, if you need to match millions of houses and millions of neighborhoods, then the SDO_JOIN approach will be more efficient.

Note that the SDO_INSIDE approach can be efficient too: just make sure you enable SPATIAL_VECTOR_ACCELERATION (only if you use Oracle 12.1 or 12.2 and you have the proper licensed for Oracle Spatial and Graph) and use parallelism.