Re: Oracle Spatial/Locator issue

From: Dan <daniel.ostertag_at_visaer.com>
Date: Thu, 29 Oct 2009 06:18:09 -0700 (PDT)
Message-ID: <bd273daf-7d9e-4f93-b96a-90fbb55dd433_at_a31g2000yqn.googlegroups.com>

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 Received on Thu Oct 29 2009 - 08:18:09 CDT