Oracle SQL with multiple SPATIAL WHERE Clause FAIL

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle SQL with multiple SPATIAL WHERE Clause FAIL

Boni Gopalan (BioImagene)

There seems to be some difference in behavior between the two OGC_STRICT modes.  I am stuck between the proverbial devil and the sea;  OGC_STRICT mode – all queries work  but performance is pathetic; OGC_STRICT=false, Performance rocks when the Queries run, But all Queries does not run.  I am not sure whether this has anything to do with my indexes or SRID.  I am storing either points or polygons, so have not mentioned anything special for the INDEX dimensions (Documentation says you need to if dimensions are > 2).  Is it confirmed that ORACLE does not use SPATIAL indexes when running OGC Queries? 

 

I tried to recreate the scenario through a test case that I can send across but am unable to do so yet.  In the mean time will greatly appreciate if someone with similar experience can help me out.  I am detailing the test case behavior below.

 

Case 1: [Single SPATIAL item in WHERE CLAUSE], [OGC_STRICT=false]

The following Query fails with the mentioned error message.

 

select tile0_.tile_id as tile1_5_, tile0_.canchecktoexclude as cancheck2_5_, tile0_.columnindex as columnin3_5_, tile0_.hasTissue as hasTissue5_, tile0_.location as location5_, tile0_.request_Id as request9_5_, tile0_.roiquality as roiquality5_, tile0_.rowindex as rowindex5_, tile0_.tileResultSummary as tileResu8_5_ from TILEROI tile0_

where

tile0_.request_Id=?

and SDO_RELATE(tile0_.location,?,'mask=INSIDE+COVEREDBY') ='TRUE'

and SDO_RELATE(tile0_.location,?,'mask=ANYINTERACT') ='FALSE'

 

01 Mar 2011 12:44:48,865        ERROR org.hibernate.util.JDBCExceptionReporter  ORA-29902: error in executing ODCIIndexStart() routine

ORA-13207: incorrect use of the [SDO_RELATE] operator

ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333

 

Case 2: [MULTIPLE SPATIAL items in WHERE CLAUSE], [OGC_STRICT=false]

However if I change the test case to omit the second SDO_RELATE WHERE Clause the Test Passes.

 

select tile0_.tile_id as tile1_5_, tile0_.canchecktoexclude as cancheck2_5_, tile0_.columnindex as columnin3_5_, tile0_.hasTissue as hasTissue5_, tile0_.location as location5_, tile0_.request_Id as request9_5_, tile0_.roiquality as roiquality5_, tile0_.rowindex as rowindex5_, tile0_.tileResultSummary as tileResu8_5_ from TILEROI tile0_

where tile0_.request_Id=?

and SDO_RELATE(tile0_.location,?,'mask=INSIDE+COVEREDBY') ='TRUE'

01 Mar 2011 12:49:42,368        INFO  com.bioimagene.iii.analysis.dao.impl.TestQuickWsaDataLayer    Tile:45401

 

 

Case 3: [Single SPATIAL item in WHERE CLAUSE], [OGC_STRICT=true]

 

select tile0_.tile_id as tile1_5_, tile0_.canchecktoexclude as cancheck2_5_, tile0_.columnindex as columnin3_5_, tile0_.hasTissue as hasTissue5_, tile0_.location as location5_, tile0_.request_Id as request9_5_, tile0_.roiquality as roiquality5_, tile0_.rowindex as rowindex5_, tile0_.tileResultSummary as tileResu8_5_ from TILEROI tile0_

where tile0_.request_Id=? and MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(tile0_.location),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1

 

01 Mar 2011 12:53:16,697        INFO  com.bioimagene.iii.analysis.dao.impl.TestQuickWsaDataLayer    Tile:45401

 

 

Case 4: [MULTIPLE SPATIAL items in WHERE CLAUSE], [OGC_STRICT=true]

select tile0_.tile_id as tile1_5_, tile0_.canchecktoexclude as cancheck2_5_, tile0_.columnindex as columnin3_5_, tile0_.hasTissue as hasTissue5_, tile0_.location as location5_, tile0_.request_Id as request9_5_, tile0_.roiquality as roiquality5_, tile0_.rowindex as rowindex5_, tile0_.tileResultSummary as tileResu8_5_ from TILEROI tile0_ where tile0_.request_Id=?

and MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(tile0_.location),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=1

and MDSYS.OGC_INTERSECTS(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(tile0_.location),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=0

and MDSYS.OGC_INTERSECTS(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(tile0_.location),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))=0

 

01 Mar 2011 12:58:27,189        INFO  com.bioimagene.iii.analysis.dao.impl.TestQuickWsaDataLayer    Tile:45401

 

 

Thanks

boni

 



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5915 (20110228) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: Oracle SQL with multiple SPATIAL WHERE Clause FAIL

Karel Maesen
Administrator
Hi Boni,

On 01 Mar 2011, at 08:40, Boni Gopalan (BioImagene) wrote:

> There seems to be some difference in behavior between the two OGC_STRICT modes.  I am stuck between the proverbial devil and the sea;  

Yes, this is briefly discussed in the documentation. And I don't like it either. In fact I don't like SDO_GEOMETRY that much (hope the Oracle 11g ST_GEOMETRY is better)

> OGC_STRICT mode – all queries work  but performance is pathetic; OGC_STRICT=false, Performance rocks when the Queries run, But all Queries does not run.  I am not sure whether this has anything to do with my indexes or SRID.  I am storing either points or polygons, so have not mentioned anything special for the INDEX dimensions (Documentation says you need to if dimensions are > 2).

You need to ensure that alle geometries in your tables are valid, have the same SRID, that the metadata tables are filled in correctly, and that the search or filter geometry is in the same SRID. Otherwise you will get these kinds of errors.

>  Is it confirmed that ORACLE does not use SPATIAL indexes when running OGC Queries?

I haven't investigated deeply, but I've alwasy suspected that the conversion step from SDO_GEOMETRY to ST_GEOMETRY messes up the query planner.

>  
> I tried to recreate the scenario through a test case that I can send across but am unable to do so yet.  In the mean time will greatly appreciate if someone with similar experience can help me out.  I am detailing the test case behavior below.

Could you add the HQL or SpatialRestrictions statements that caused the SQL?  Then I can try to assist on how to resolve these problems.

Regards,

Karel

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users