2

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 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
1

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: enter image description here

Two more supportive points:
Perhaps a not-so-temporary table of transient 'Viewport' geometries isn't such a bad design. You could keep it around, indexing it, inserting requested viewports then referencing each request row from it (by PK) in a WITH clause?

Lastly, your 11.2.0.1 release needs some SDO patching; I suggest well-placed comments to your DBAs on reving/patching up to at least 11.2.0.4

  • thanks for the answer Jason, i will keep investigating on this and tbh i'm leaning towards a "lookup" table with all viewport-rectangles stored there. still i cant understand how this hacky way makes it at half the time as the "official"-as per docs- way. i'm going to say it again: postgres, very same scenario works soooo fine, it makes oracle look terrible -as long as i m not doing anything stupid here – Sharky Sep 14 '16 at 18:28
  • FYI, we've looked at ST geometry storage and spatial functions (still running in Oracle) so we don't have to deal with these types of SDO idiosyncrasies. – JasonInVegas Sep 14 '16 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.