Oracle Spatial/Locator issue
in [Oracle]
|
From: Dan on 30 Oct 2009 11:25 On Oct 29, 4:12 pm, Shakespeare <what...(a)xs4all.nl> wrote: > 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- 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
From: Shakespeare on 1 Nov 2009 12:06 Dan schreef: > On Oct 29, 4:12 pm, Shakespeare <what...(a)xs4all.nl> wrote: >> 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- 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 I know that combining sdo_num_res with other clauses in the where clause can have unpredictable results. It could be the spatial index gets ignored because there is an index on zip4 and/or zip5. You can hint the spatial index into the query like SELECT /*+ INDEX(zip4_bounds, <your index name here>) */ ..... Shakespeare
From: Shakespeare on 1 Nov 2009 12:12
Dan schreef: > On Oct 29, 4:12 pm, Shakespeare <what...(a)xs4all.nl> wrote: >> 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- 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 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 http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10826/sdo_operat.htm#i80529 (SDO_NN is not supported for spatial joins) One solution could be using a plsql procedure with a cursor loop. Shakespeare |