oracle-specific restrictions

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

oracle-specific restrictions

John Cartwright
Hello All,

I'm using hibernate spatial 1.0 w/ Oracle and Grails and have a
restriction like:

SpatialRestrictions.intersects('geometry', cmd.bbox))

It's generating SQL like the listing below which seems to be causing
Oracle problems that I'm unable to track down.  Is there a way to use
restrictions from the OracleSpatialRestrictions class like SDOFilter
instead of the more generic "intersects"?

Thanks!

--john


select
        this_.objectid as objectid8_0_,
        this_.version as version8_0_,
        this_.comments as comments8_0_,
        this_.discovery_id as discovery4_8_0_,
        this_.feature_id as feature5_8_0_,
        this_.geometry as geometry8_0_,
        this_.history as history8_0_,
        this_.last_updated as last8_8_0_,
        this_.name as name8_0_,
        this_.proposal_id as proposal10_8_0_,
        this_.remarks as remarks8_0_,
        this_.status as status8_0_,
        this_.type_id as type13_8_0_,
        this_.wkt as wkt8_0_,
        this_.class as class8_0_
    from
        ufn.ufn_feature this_
    where
        MDSYS.OGC_INTERSECTS(
        MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(this_.geometry),
        MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?)) = 1
        and this_.geometry is not null
        and 1=1;
_______________________________________________
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-specific restrictions

John Cartwright
(answering myself ) I think I've got this sorted now.  Changing from
 
     SpatialRestrictions.intersects('geometry', cmd.bbox))
to
     OracleSpatialRestrictions.SDORelate("geometry", cmd.bbox, sdoParams)

changes the SQL from

MDSYS.OGC_INTERSECTS(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(this_.geometry),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?))
= 1 and this_.geometry is not null

to

SDO_RELATE(this_.geometry,?,'mask=ANYINTERACT') = 'TRUE'


This solves my still unexplained Oracle error and also speeds up the query.

--john


On 11/09/2011 05:19 PM, John Cartwright wrote:

> Hello All,
>
> I'm using hibernate spatial 1.0 w/ Oracle and Grails and have a
> restriction like:
>
> SpatialRestrictions.intersects('geometry', cmd.bbox))
>
> It's generating SQL like the listing below which seems to be causing
> Oracle problems that I'm unable to track down.  Is there a way to use
> restrictions from the OracleSpatialRestrictions class like SDOFilter
> instead of the more generic "intersects"?
>
> Thanks!
>
> --john
>
>
> select
>         this_.objectid as objectid8_0_,
>         this_.version as version8_0_,
>         this_.comments as comments8_0_,
>         this_.discovery_id as discovery4_8_0_,
>         this_.feature_id as feature5_8_0_,
>         this_.geometry as geometry8_0_,
>         this_.history as history8_0_,
>         this_.last_updated as last8_8_0_,
>         this_.name as name8_0_,
>         this_.proposal_id as proposal10_8_0_,
>         this_.remarks as remarks8_0_,
>         this_.status as status8_0_,
>         this_.type_id as type13_8_0_,
>         this_.wkt as wkt8_0_,
>         this_.class as class8_0_
>     from
>         ufn.ufn_feature this_
>     where
>         MDSYS.OGC_INTERSECTS(
>         MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(this_.geometry),
>         MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?)) = 1
>         and this_.geometry is not null
>         and 1=1;
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
_______________________________________________
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-specific restrictions

eting
Hi John,
I'm also having problems with Oracle when using SpatialRestrictions.intersects. I'm just curious about whether you had the same problems before. My problems seem to occur in Oracle 11 only (Oracle 10 is fine).

Problem #1): Occasionally I get an error like this from Oracle:
ORA-22165: given index [20]
must be in the range of [0] to [3]

We've already reported this to Oracle and they could reproduce the error on their side too.

Problem #2): Occasionally Oracle would return all geometries in the database, instead of just the ones that intersect with the given input, when doing an intersection search.

Both of these problems seem to happen randomly.

I'll try to use  OracleSpatialRestrictions.SDORelate as you suggested and see if that solves my problems..
Thanks,
E