Re: Oracle Spatial Selecting Overlaps returning no results (when it should)?

john.e.major.jr_at_gmail.com wrote:
> Hello-
>
> I'm looking for advice on a problem I've hit with Oracle 10g spatial.
> I am storing a large number of rows with a feature_rectangle column
> defined for each entry(which is just a variable sized solid 2d
> rectangle).
>
> I have followed a table & index creation scenario like this:
> http://www.oreillynet.com/pub/a/network/2003/11/10/oracle_spatial.html
>
> and have populated my database with no errors.
>
> When I select * from the table, I return entries which look like this:
> feature_rectangle
> --------------------------
> (2003, , , (1, 1003,
> 3, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ),
> (14949509, 3, 14949760,
> 3, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ))
>
>
> However, a query like this returns no rows (even though i am defining
> a rectangle which overlaps the rectangle above?!?!
>
> select * from MYTABLENAME a where
> SDO_ANYINTERACT(
> a.feature_rectangle,
> mdsys.SDO_GEOMETRY(2003,NULL,NULL,mdsys.SDO_ELEM_INFO_ARRAY(1,1003,3),
> mdsys.SDO_ORDINATE_ARRAY(14949508,3,14949510,3))
> ) = 'TRUE'
>
> This returns no errors and no results..
>
> any help would be greatly appreciated-thanks!
> john

Hi john,
my 2 cents...

try to set the coordinate system first.

check view CS_SRS on schema MDSYS to find the appropriate SRID. check USER_SDO_GEOM_METADATA to see if the SRID is set, otherwise try to update
check if SDO_SRID on MYTABLENAME.feature_rectangle.sdo_srid (try this SELECT T.FEATURE_RECTANGLE.SDO_SRID FROM MYTABLETABLE T WHERE ROWNUM < 2), if null returns, update the table.column.sdo_srid to reflect the SRID found.
rebuild index
try again

:)

hope it helps!

pedro Received on Tue Aug 05 2008 - 18:54:24 CDT