Re: Oracle Spatial/Locator issue

From: Dan <daniel.ostertag_at_visaer.com>
Date: Fri, 30 Oct 2009 08:25:33 -0700 (PDT)
Message-ID: <1aeaa157-59ea-4669-bc48-aa43000ef71a_at_r5g2000yqb.googlegroups.com>

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 Received on Fri Oct 30 2009 - 10:25:33 CDT