Oracle Spatial/Locator issue
in [Oracle]
|
From: Dan on 28 Oct 2009 17:39 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
From: Shakespeare on 28 Oct 2009 19:26 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
From: Dan on 29 Oct 2009 09:18 On Oct 28, 7:26 pm, Shakespeare <what...(a)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
From: Shakespeare on 29 Oct 2009 15:56 Dan schreef: > On Oct 28, 7:26 pm, Shakespeare <what...(a)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
From: Shakespeare on 29 Oct 2009 16:12
Shakespeare schreef: > Dan schreef: >> On Oct 28, 7:26 pm, Shakespeare <what...(a)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 |