Re: Oracle Spatial/Locator issue
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sun, 01 Nov 2009 18:12:23 +0100
Message-ID: <4aedc16a$0$83245$e4fe514c_at_news.xs4all.nl>
Dan schreef:
> On Oct 29, 4:12 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
> Shakespeare,
> Thanks for your help on this. After alot of trial and error I got the
> query working yesterday. This issue was with the order of the fields
> in the SDO_NN function in relation to the order in the FROM clause.
> Not sure why, but it seems like 2nd table in the FROM clause must
> match the 1st field in the SDO_NN function. If there are two like lat/
> long fields, why does the order count, not sure?
>
> My new question is: how is the join made between the two tables? When
> I put many qualifiers (i.e. where zip5='01001', etc), I get my
> original 13249 error again. Without the where clause it seems to be a
> many to many relationship. I finally put all the lat/long fields into
> 1 table, but it doesn't seem to be able to look at 2 fields in one
> table and get the distance between them. Seems like this should be
> simple.
>
> Here is my query that again gets 13249:
> select ZIP5,zip4,north,south, sdo_nn_distance (1) distance
> from zip4_bounds
> where zip5='01001' and zip4='1717' and
> sdo_nn
> (south, north,'unit=mile sdo_num_res=3', 1) = 'TRUE';
>
> Again, I'm just looking for the distance between the southern most lat/
> long and the northern most lat/long in the above zip+4.
>
> Thanks,
> Dan
Date: Sun, 01 Nov 2009 18:12:23 +0100
Message-ID: <4aedc16a$0$83245$e4fe514c_at_news.xs4all.nl>
Dan schreef:
> On Oct 29, 4:12 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>> 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- Hide quoted text -
>>
>> - Show quoted text -
>
> Shakespeare,
> Thanks for your help on this. After alot of trial and error I got the
> query working yesterday. This issue was with the order of the fields
> in the SDO_NN function in relation to the order in the FROM clause.
> Not sure why, but it seems like 2nd table in the FROM clause must
> match the 1st field in the SDO_NN function. If there are two like lat/
> long fields, why does the order count, not sure?
>
> My new question is: how is the join made between the two tables? When
> I put many qualifiers (i.e. where zip5='01001', etc), I get my
> original 13249 error again. Without the where clause it seems to be a
> many to many relationship. I finally put all the lat/long fields into
> 1 table, but it doesn't seem to be able to look at 2 fields in one
> table and get the distance between them. Seems like this should be
> simple.
>
> Here is my query that again gets 13249:
> select ZIP5,zip4,north,south, sdo_nn_distance (1) distance
> from zip4_bounds
> where zip5='01001' and zip4='1717' and
> sdo_nn
> (south, north,'unit=mile sdo_num_res=3', 1) = 'TRUE';
>
> Again, I'm just looking for the distance between the southern most lat/
> long and the northern most lat/long in the above zip+4.
>
> Thanks,
> Dan
Actually, I just found in the docs that SDO_NN can not be used for spatial joins, sop that's why your 2-table example (or selecting 2 columns from 1 table, which is actaully a self-join as well) does not work. The first parameter must be from an indexed table, the second one must be fixed. See
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10826/sdo_operat.htm#i80529
(SDO_NN is not supported for spatial joins)
One solution could be using a plsql procedure with a cursor loop.
Shakespeare Received on Sun Nov 01 2009 - 11:12:23 CST