# Efficient way to find geometries that intersect a given rectangle

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",
)='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)),
)='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",
)='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 `SDO_GEOMETRY`.

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 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
``````
• It should not be difficult at all. For me queries like this are fast and use spatial index `SELECT ID FROM MY_TABLE mt WHERE SDO_ANYINTERACT (mt.geometry,SDO_GEOMETRY('POLYGON (( 40 0, 40 20, 60 20, 60 0, 40 0 ))',4326))='TRUE';` Using SDO_ANYINTERACT instead of RELATE and defining geometry with WKT should make no difference at all but I like that they are easier to write. – user30184 Sep 12 '16 at 9:30
• This seems like an issue to address with Oracle support. You haven't provided the total number of features or the count within the extent, or the number and size of the attributes selected. You may find that `SDO_FILTER` is more efficient on simple queries. – Vince Sep 12 '16 at 11:23
• The proper way to do this query is definitely your first approach (except the syntax can be simplified by using SDO_ANYINTERACT. It will be implemented by Oracle using the viewport to locate the matching polygons using the spatial index. The second approach is incorrect: it will match each and every of your areas against the viewport. So I am really surprised that it comes up faster. It may be simply because your first test caused the areas to be loaded in the buffer cache and so the second case skipped some I/Os. – Albert Godfrind Sep 14 '16 at 11:29
• You did not say how many areas you have in the AREAS table, and how many the query is supposed to return. Also does the query fetch the geometries ? Or does count them ? Finally what version of Oracle do you run and on what platform ? – Albert Godfrind Sep 14 '16 at 11:31
• Just did a quick test: On my laptop (in a Linux VM), a similar query that returns 180 polygons from a small table (150000 rows) completes in 180ms. That is Oracle 12.1.0.2 on Linux. No optimizations, out of the box. – Albert Godfrind Sep 14 '16 at 12:23

The problem is Option A, you're missing something very basic. (I do it all the time, so I'm not throwing stones.) I believe you are misinterpreting the Oracle documentation on `SDO_RELATE()` found here. Single geometries (described as "transient geometries" in the documentation) are SUPPOSED to be the second parameter, while the table-full of geometries are supposed to be the first argument.
Not to put too fine a point on it, but even in the suggested `SDO_ANYINTERACT()` function, the documentation shows your singleton geometry should be the second parameter: