Re: Oracle Spatial/Locator issue
Date: Sun, 01 Nov 2009 18:12:23 +0100
> 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 -
> 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
> 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
> (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.
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
(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