Re: Oracle Spatial/Locator issue

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 29 Oct 2009 21:12:59 +0100
Message-ID: <4ae9f74b$0$83246$e4fe514c_at_news.xs4all.nl>

Shakespeare schreef:

> Dan schreef:
>> On Oct 28, 7:26 pm, Shakespeare <what..._at_xs4all.nl> wrote:

>>> Dan schreef:
>>>
>>>
>>>
>>>
>>>
>>>> I'm learning Oracle Locator and I've created 2 tables that each have a
>>>> field of type SDO_GEOMETRY that represents a lat/long value. I'm
>>>> writing a query to join the tables and find the distance between the
>>>> two points. I'm using the example in the Oracle Locator doc, so I
>>>> know the syntax is correct. However, I keep getting ORA-13249 "SDO_NN
>>>> cannot be evaluated without using index".
>>>> I have created the index, created the metadata records for the 2
>>>> fields in the 2 tables, yet I keep getting this error. Any ideas?
>>>> Here is my query:
>>>> select /*+ordered*/ a.eastern_point,b.southern_point, sdo_nn_distance
>>>> (1) distance
>>>> from eastmost_point a, southmost_point b
>>>> where a.zip5=b.zip5 and a.zip4=b.zip4 and a.zip5='01001' and
>>>> a.zip4='1101' and
>>>> sdo_nn
>>>> (a.eastern_point, b.southern_point, 'sdo_num_res=3', 1) = 'TRUE'
>>>> Here is an example of an SDO_GEOMETRY value:
>>>> (2001, 8307, (42.096136, -72.638013, ), , )
>>>> Thanks,
>>>> Dan
>>> Did you put geo-indexes on both tables, and did you register them?
>>>
>>> Shakespeare- Hide quoted text -
>>>
>>> - Show quoted text -

>>
>> Yes, did both, as long as "register them" means inserting the metadata
>> into the USER_SDO_GEOM_METADATA view.
>>
>> Here is the create index code:
>> CREATE INDEX SDO_SOUTHMOST_POINT_IDX ON SOUTHMOST_POINT
>> (SOUTHERN_POINT) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
>> CREATE INDEX SDO_EASTMOST_POINT_IDX ON EASTMOST_POINT (EASTERN_POINT)
>> INDEXTYPE IS MDSYS.SPATIAL_INDEX;
>>
>> Here is the metadata inserts:
>> INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO,
>> SRID)
>>    VALUES ('SOUTHMOST_POINT', 'SOUTHERN_POINT',
>>    SDO_DIM_ARRAY
>>      (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
>>       SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
>>       8307);
>>
>> INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO,
>> SRID)
>>    VALUES ('EASTMOST_POINT', 'EASTERN_POINT',
>>    SDO_DIM_ARRAY
>>      (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
>>       SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
>>       8307);
>>
>> Tell me if I'm doing anything wrong.
>> Thanks, Dan
> 
> Ok that seems correct.
> Could you check (just to be sure) whether your indexes are in the 
> USER_SDO_INDEX_METADATA view?
> 
> One more thing though: there should be a space in your hint /*+ ordered 
> */ hint between + and o.
> 
> Shakespeare

Correction. The space is optional. My mistake.

Shakespeare Received on Thu Oct 29 2009 - 15:12:59 CDT