This discussion is archived
2 Replies Latest reply: Dec 26, 2012 2:35 AM by 977219 RSS

SDO_WITHIN_DISTANCE and layer_gtype question

977219 Newbie
Currently Being Moderated
Greetings,

I met a problem on the operator SDO_WITHIN_DISTANCE just like below:

I just want to use this operator to search all objects(point & polygon) within 5 miles of a piont, however this operator just return all points within 5 miles of this piont, not any polygons,
or if I use this operator to search all objects(point & polygon) within 5 miles of a polygon, however this operator just return all polygons within 5 miles of this polygon, not any points...

SQL just like below:

select hl_neighbors.geometry.GET_GTYPE()
from hz_locations hl_neighbors, hz_locations hl_center
where hl_center.location_id = 36076(a point)/36156(a polygon)
and sdo_within_distance(hl_neighbors.geometry,
hl_center.geometry,
'distance=50 unit=mile') = 'TRUE'

If location_id=36076, all results are 1, if location_id=36156, all results are 3, actually these points and polygons are gather within 5 miles.

The index in this column just like below:

Index_Name Index_Type Table_Owner Table_Name Status Ityp_Owner Ityp_Name SDO_LAYER_GTYPE
HZ_LOCATIONS_N15 DOMAIN AR HZ_LOCATIONS VALID MDSYS SPATIAL_INDEX DEFAULT

I didn't add the layer_gtype parameters, you could see this value is default, why the result identify the gtype automatically? I just want to search all type of object use this operator...

Could you give me some advice?

Thanks & Regards,
James
  • 1. Re: SDO_WITHIN_DISTANCE and layer_gtype question
    Simon Greener Journeyer
    Currently Being Moderated
    James,

    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"!
    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
    So, there is no issue with the sdo_within_distance operator that I can see. The data is public data describing San Francisco.
    The data is not geodetic. The rtree was deliberately built to include a layer_gtype parameter as follows:
    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');
    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');
    ... 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, 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 [2001] while geometry inserted has type [2002]
    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.
    Helpful? Award points. If not, please give us more information about your situation via real data.

    regards
    Simon
  • 2. Re: SDO_WITHIN_DISTANCE and layer_gtype question
    977219 Newbie
    Currently Being Moderated
    Simon,


    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,
    James

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points