Trouble with SDO_OVERLAPBDYDISJOINT and spatial index

Question

i am using Oracle 11 g and I need to know if a specific point is inside de buffer of another point from a table with spatial index, i am using the follow sentence:

'''SELECT A.fieldX FROM TABLE A WHERE SDO_OVERLAPBDYDISJOINT(sdo_geom.sdo_buffer(A.geometry,2,0.1),SDO_GEOMETRY(2001,NULL ,SDO_POINT_TYPE(497644.6,2432725.8,NULL),NULL,NULL)) = 'TRUE';'''

And I obtain the follow error:

13226. 00000 - "interface not supported without a spatial index" Cause: The geometry table does not have a spatial index. Action: Verify that the geometry table referenced in the spatial operator has a spatial index on it.

The operator SDO_OVERLAPBDYDISJOINT uses only geometries from tables with spatial index, and I understand that this error is caused for the buffer operator but if I invert the order and put first the SDO_POINT_TYPE, I have the same error. Is there any way to use this operator or another similar without a spatial index? I dont want to use pl sql because I need to use the sentence in a VBA code. Thanks a lot!!!

Answer 1

What you essentially want is to find out all the geometries that are within some distance of another. This is easily and better done this way. It is also much more efficient.

SELECT A.fieldX 
FROM TABLE A 
WHERE sdo_within_distance(A.geometry,SDO_GEOMETRY(2001,NULL,SDO_POINT_TYPE(497644.6,2432725.8,NULL),NULL,NULL)),'distance=2') = 'TRUE';

Answer 2

I think your problem is that the A.geometry is indexed, but its buffer is not.
The first thing you should try, is to use
SDO_OVERLAPBDYDISJOINT(A.geometry, buffer(sdo_point(...),2,0.1)) - and, while at it, it would be more correct to use SDO_INSIDE here.
If this does not work, you should check if your index is, indeed, ok. You can easily test it using a specific id from your table - lets say, 10 - and run:
select a.id from your_table a, your_table b where a.id=b.id and b.id=10 and sdo_equals(a.geometry,b.geometry)='TRUE'; If it returns your id (10 in my example), your index is ok.