This content has been marked as final. Show 2 replies
It is hard to answer your question because we don't have your data.
We don't know if your data is projected or not.
The provision of a small set of data (two geometries would do), plus the commands to create the host table, populate it, create the metadata and then the RTree statement (not a dump from the relevant mdsys metadata table) would enable us to help you.
As it is all I can say is that "it works for me"!
So, there is no issue with the sdo_within_distance operator that I can see. The data is public data describing San Francisco.
select a.geom.sdo_gtype as gtype,count(*) from CITY_FURNITURE a where sdo_within_distance(a.geom, SDO_GEOMETRY(2001,2872,SDO_POINT_TYPE(6010523.198,2119023.596,NULL),NULL,NULL), 'distance=1, unit=MILE') = 'TRUE' group by a.geom.sdo_gtype; -- Results -- GTYPE COUNT(*) ---------- ---------- 2001 111 2002 73
The data is not geodetic. The rtree was deliberately built to include a layer_gtype parameter as follows:
Dropping the index and recreating it without the layer_gtype etc parameters ....
CREATE INDEX "BOOK"."CITY_FURNITURE_GEOM_SPIX" ON "BOOK"."CITY_FURNITURE" ("GEOM") INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS ('sdo_indx_dims=2, layer_gtype=COLLECTION, tablespace=USERS, work_tablespace=USERS');
... made no difference to the sdo_within_distance operator (as expected). Don't forget you can't create an RTree index with a layer_gtype other than COLLECTION if the column being indexed contains geometries with types other than that allowed by the layer_gytype parameter. So, an attempt to build an index with layer_gtype=POINT when the column also contains LINESTRING data will fail.
CREATE INDEX "BOOK"."CITY_FURNITURE_GEOM_SPIX" ON "BOOK"."CITY_FURNITURE" ("GEOM") INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS ('sdo_indx_dims=2');
Helpful? Award points. If not, please give us more information about your situation via real data.
CREATE INDEX "BOOK"."CITY_FURNITURE_GEOM_SPIX" ON "BOOK"."CITY_FURNITURE" ("GEOM") INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS ('sdo_indx_dims=2, layer_gtype=POINT') Error at Command Line:12 Column:21 Error report: SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-13249: internal error in Spatial index: [mdidxrbd] ORA-13249: Error in Spatial index: index build failed ORA-13249: Error in spatial index: [mdrcrtxfergm] ORA-13249: Error in spatial index: [mdpridxtxfergm] ORA-13200: internal error [ROWID:AAAhSuAAEAAB8erAAA] in spatial indexing. ORA-13206: internal error  while creating the spatial index ORA-13375: the layer is of type  while geometry inserted has type  ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10 29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine" *Cause: Failed to successfully execute the ODCIIndexCreate routine. *Action: Check to see if the routine has been coded correctly.
Thanks very much for your reply, you are correct, it is our data's problem, SDO_WITHIN_DISTANCE works well for us too~:)
And your post is very helpful!!!
Thanks & Regards,