1

My request in postgis :

create table "schema1"."table1" as select p.field1 ,p.field2, l.field3, l.id from
"schema1"."table2" p inner join "schema1"."table3" l on (p."GEOM" && l."GEOM" and st_intersects(st_buffer(p."GEOM",0),st_buffer(l."GEOM",0)));

I want to execute this request on Oracle so st_intersects is SDO_ANYINTERACT but I don't find for && operator.

What is the equivalence of && operator in oracle sdo ?

Improve this question
3
  • && is a PostGIS quirk you won't see elsewhere. I believe you are looking for SDO_FILTER You'll also want to shed all those double-quotes.
    – Vince
    Commented Sep 30, 2016 at 11:26
  • 1
    && means bounding box intersection and is implied in any ST_Intersects type query since Postgis 2.0 (possibly earlier). I assume Oracle also uses a BBOX check for any query using ST_Intersects, so you can drop the &&. Finally, in Postgres, at least, if you use ST_Buffer in the where clause, this will mean the spatial index is not used (and ST_Buffer(geom,0) is a bit pointless anyway). You should use ST_DWithin instead. I can't answer for the Oracle side, but I would improve the Postgis side first, before trying to translate what is both out of date and very inefficient query.
    – John Powell
    Commented Sep 30, 2016 at 12:55
  • If you are using ST_Buffer(geom, 0) as a trick to deal with invalid geometries, again, I would deal with that with ST_MakeValid first, before using it as a join condition. I realize that you asked about Oracle, but the Postgres query really needs to be sorted before you can think about converting it. The short answer, though, is the Oracle equivalent of ST_Intersects.
    – John Powell
    Commented Sep 30, 2016 at 12:57

1 Answer 1

Reset to default
1

It would look like this in Oracle:

create table schema1.table1 as 
select p.field1, p.field2, l.field3, l.id 
from schema1.table2 p inner join schema1.table3 l 
on sdo_anyinteract (p.GEOM,l.GEOM) = 'TRUE';

Some observations:

  • Don't quote names. Doing so means that the names (tables, columns) will be case-sensitive (i.e. explicitly lower or upper case in your example). That introduces all sorts of unnecessary complications (in particular all accesses will also need to quote the names).
  • As said, && is a PostGis artifact to request filtering via a spatial index. That is not needed with Oracle: the spatial index is used automatically (or not -depending on what the optimized decides).
  • Also as said, the st_buffer(x,0) looks strange to me: what is the point of computing a zero-size buffer ?
  • The ordering of the arguments passed to SDO_INTERACT is important for performance. In the above, I assume that table3 is the smallest table (less rows than table2). If that is not the case, reverse the arguments.
  • If you are using Oracle 12c and have the proper licence for Oracle Spatial and Graph, then make sure to enable the Vector Performance Accelerator (see the manual for details).
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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