It feels I'm missing something absolutely basic here OR there is something weird in the internals of Oracle's spatial indexing (I sincerely hope it is just me missing something basic or I am about to lose some faith in Oracle).
I'm trying to find which geometries intersect a given rectangle:
I have a rectangle, let's call it
VIEWPORT with Latitude/Longitude 41.500, 12.500 and 42.000, 13.000
on-the-fly that is
SDO_GEOMETRY(2003,4326,null,SDO_ELEM_INFO_ARRAY(1,1003,3),mdsys.SDO_ORDINATE_ARRAY(12.500, 41.500, 13.000, 42.000))
Also I have in a table
AREAS a bunch of geometries (polygons and multipolygons). Spatial indexed.
Now, I want to see which records from
AREAS intersect that
VIEWPORT. So, in my
SELECT I should have a
WHERE clause like this:
SDO_RELATE ( SDO_GEOMETRY(2003, 4326, null,SDO_ELEM_INFO_ARRAY(1,1003,3),mdsys.SDO_ORDINATE_ARRAY(12.500, 41.500, 13.000, 42.000)), "AREAS"."GEOMETRY", 'mask=ANYINTERACT' )='TRUE'
Well this can't execute because
ORA-13226: interface not supported without a spatial index. That happens because the first parameter in
SDO_RELATE is the on-the-fly
SDO_GEOMETRY, which has no spatial index.
So let's flip the order of the arguments (which is not correct approach but anyway) just to mesaure the execution time. So the where clause goes like this:
SDO_RELATE ( "AREAS"."GEOMETRY", SDO_GEOMETRY(2003, 4326, null,SDO_ELEM_INFO_ARRAY(1,1003,3),mdsys.SDO_ORDINATE_ARRAY(12.500, 41.500, 13.000, 42.000)), 'mask=ANYINTERACT' )='TRUE'
"AREAS"."GEOMETRY" is spatial indexed, so no problem for
SDO_RELATE to execute. I'm getting the correct results in
8.8 seconds. But, that's not efficient at all.
Now let's do something tricky. Instead of providing that
VIEWPORT rectangle as a on-the-fly constructed
SDO_GEOMETRY let's put it in a table, with spatial index. This way we can use it as the first argument on
SDO_RELATE. So eventually we have:
WITH viewport AS ( SELECT * FROM "VIEWPORT_TEMP_TABLE" WHERE "ID" = 1 ) . . . SDO_RELATE ( viewport."GEOMETRY", "AREAS"."GEOMETRY", 'mask=ANYINTERACT' )='TRUE'
This executes just in
3.8 sec, the difference is unexpectedly great!
So Oracle has the potential to perform this in
3.8 sec but only with this hacky way.
I think in this point, either I am ignoring something very basic and mis-using or mis-judging Oracle, or something is very "weird" in how Oracle performs intersections with a given
My question is: What is the correct/efficient way to find the geometries that intersect a given rectangle in Oracle?
As I demonstrated the fastest way is to create a temporary table, insert that given rectangle and rebuild its spatial index and then use it as first argument in the
SDO_RELATE. This is way too hacky to be the correct way to perform something so simple like this.
So, what is the correct way to find geometries intersecting a given rectangle?
(In contrast, postgres has no problem with on-the-fly given rectangle in the very same scenario and that made me very sceptical about Oracle)
edit: running on
Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production PL/SQL Release 18.104.22.168.0 - Production "CORE 22.214.171.124.0 Production" TNS for 64-bit Windows: Version 126.96.36.199.0 - Production NLSRTL Version 188.8.131.52.0 - Production