Have a polygon in table 2 with its id, no and geometry. In table 1 I have polygons with the same fields. The polygon in table in intersects with a few polygons from table 1.What I am trying to do is clip of the polygons which overlap polygon from table 2 and insert the same fields and geometry in table 3

insert into table 3  
select a.store_id,b.store_id,a.store_number,a.client_id,sdo_geom.sdo_intersection(b.geometry,a.geo metry,0.005)  
from table_1 a, table_2 b  
where b.store_id=34746  
and sdo_anyinteract(b.geometry,a.geometry)='True';  

When I do run this I dont get any output. Where would I be going wrong


This query will fail with ann error like this:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-13207: incorrect use of the [SDO_ANYINTERACT] operator

You need to write "= 'TRUE' " instead of " = 'True' " (notice the capitals).

Apart from that, the query is also incorrectly written: since you lookup table A using a window from table B, then you need to specify the columns in that order in the spatial predicate, so write sdo_anyinteract(a.geometry,b.geometry) instead of sdo_anyinteract(b.geometry,a.geometry)

So the proper way to write your query is this:

insert into table 3  
select a.store_id, b.store_id, a.store_number, a.client_id, 
from table_1 a, table_2 b  
where b.store_id=34746  
and sdo_anyinteract(a.geometry,b.geometry)='TRUE'; 

That may still return no rows. It all depends on the actual data you are using.

| improve this answer | |
  • Also make sure that you have a spatial index on table_2.geometry column. Without it, the operator sdo_anyinteract may fail. – travis Nov 25 '13 at 9:23
  • Not true: table_2 does not need a spatial index for the spatial operators to work. An index is required only on the table/column that is the first argument to the operator (i.e table_1 in this example). You only need a spatial index on table_2 if that is also used for spatial searches = it is used as the first argument in a spatial operator - that includes the SDO_FILTER used for viewing the data. So, it is a good idea to have a spatial index on all spatial columns (even though table_2 does not require one for the above query). – Albert Godfrind Aug 4 '15 at 9:28
  • In rare cases I have seen the oracle optimizer drive the join in the revers order. As in, the A position is driving, not the B. In this particular case it is unlikely, but I think it could happen if you had far fewer rows in table_1 than table_2 (after the predicate is applied). Either way, in specific cases Oracle will drive the query from the reverse order. On older versions (9i I think) this fails without the index. Newer versions this succeeds, but takes longer because there isn't an index to use. – travis Aug 13 '15 at 19:59

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.