Re: Oracle Spatial/Locator issue
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 29 Oct 2009 20:56:45 +0100
Message-ID: <4ae9f37e$0$83246$e4fe514c_at_news.xs4all.nl>
Dan schreef:
> On Oct 28, 7:26 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
> 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
Date: Thu, 29 Oct 2009 20:56:45 +0100
Message-ID: <4ae9f37e$0$83246$e4fe514c_at_news.xs4all.nl>
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 Received on Thu Oct 29 2009 - 14:56:45 CDT