[Geoserver-devel] Oracle Spatial Problems

GeoServer GeoServer Developer
1

Hi there,

I’m working with the Social Change Online guys on developing the capability to deal with complex queries and objects. I’m having a few problems with the Oracle Spatial query at the moment and I’m wondering whether anyone else is having problems with BBox type queries against Oracle.

The relevant part of the query that Geoserver (probably actually Geotools) is generating is this:

( NOT SDO_RELATE(“LATLONG_GEOM”,MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO
_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(126.641345,-26.427555,126.64
1345,-25.637043,128.991336,-25.637043,128.991336,-26.427555,126.641345,-26.42755
5)),‘mask=disjoint querytype=WINDOW’) = ‘TRUE’ )

it generates this Oracle error:

Caused by: java.sql.SQLException: ORA-13029: Invalid SRID in the SDO_GEOMETRY object
ORA-06512: at “MDSYS.SDO_CS”, line 23
ORA-06512: at “MDSYS.SDO_3GL”, line 117

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2337)
at oracle.jdbc.oci8.OCIDBAccess.fetch(OCIDBAccess.java:2012)

I’ve checked with a few people who have experience with SDO in Oracle and they have pointed out that that there’s no SRID in the querying bounding box geometry. I’m not sure why this is hapening. Any ideas? (NB I’m using the alias & OCI to connect to Oracle, I don’t think this should be causing a problem).

If someone who is successfully doing spatial queries against Oracle using Geoserver could send me the query that Geoserver is generating (which is pretty easy to get from the webserver logs) that would be really useful.

Cheers,
Stuart

2

Hi,

Geoserver checks the table user_sdo_geom_metadata to get the SRID of the table. Did you specify the SRID in the user_sdo_geom_metadata table? There should be an entry for each geometry column you're serving. (You could check that first, e.g. try on your db: select TABLE_NAME, COLUMN_NAME, DIMINFO, SRID from USER_SDO_GEOM_METADATA where TABLE_NAME = 'ÝOURTABLENAME' ).

I'm not having any problems on Oracle (just tried OCI and that also works fine). Geoserver 1.2.3 sends this SQL: (...) WHERE NOT SDO_RELATE("GEOM",MDSYS.SDO_GEOMETRY(2003,90112,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(120173.0,489015.0,120173.0,489717.0,120818.0,489717.0,120818.0,489015.0,120173.0,489015.0)),'mask=disjoint querytype=WINDOW') = 'TRUE'

The SRID (90112) is specified here.

(By the way, check out http://jira.codehaus.org/browse/GEOS-232, since this deals with a problem on Oracle and spatial filters. Maybe you could test whether there is a difference between NOT DISJOINT and ANYINTERACT in Oracle as well. With the data I use, there seems to be no difference but ANYINTERACT is much faster, but I didn't have the time to test it extensively.)

Thijs

At 06:58 18-11-2004, Stuart.Girvan@anonymised.com wrote:

Hi there,

I'm working with the Social Change Online guys on developing the capability to deal with complex queries and objects. I'm having a few problems with the Oracle Spatial query at the moment and I'm wondering whether anyone else is having problems with BBox type queries against Oracle.

The relevant part of the query that Geoserver (probably actually Geotools) is generating is this:

( NOT SDO_RELATE("LATLONG_GEOM",MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO
_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(126.641345,-26.427555,126.64
1345,-25.637043,128.991336,-25.637043,128.991336,-26.427555,126.641345,-26.42755
5)),'mask=disjoint querytype=WINDOW') = 'TRUE' )

it generates this Oracle error:

Caused by: java.sql.SQLException: ORA-13029: Invalid SRID in the SDO_GEOMETRY object
ORA-06512: at "MDSYS.SDO_CS", line 23
ORA-06512: at "MDSYS.SDO_3GL", line 117

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2337)
        at oracle.jdbc.oci8.OCIDBAccess.fetch(OCIDBAccess.java:2012)

I've checked with a few people who have experience with SDO in Oracle and they have pointed out that that there's no SRID in the querying bounding box geometry. I'm not sure why this is hapening. Any ideas? (NB I'm using the alias & OCI to connect to Oracle, I don't think this should be causing a problem).

If someone who is successfully doing spatial queries against Oracle using Geoserver could send me the query that Geoserver is generating (which is pretty easy to get from the webserver logs) that would be really useful.

Cheers,
Stuart

3

Yeah, I believe that's a bug I accidentally introduced in 1.2.3.
Apologies. I was getting weird errors with NOT DISJOINT returning
diffeerent results than INTERSECTS or BBOX, which according to the OGC
specs should not be. The BBOX and INTERECTS queries were using
'anyinteract', so I attempted to change them to really use a not disjoint.
This ended up being not only a performance hit, but appears to also have
messed up the query, as I have had another similar report. Thijs never
got the same error, but I made a new jar for him that used anyinteract,
and also added his BEYOND and DWITHIN filters. I just put it up for
download as a DataStore extra, try it out:
https://sourceforge.net/project/showfiles.php?group_id=25086&package_id=129885&release_id=271218
Hopefully this should fix your problem, let me know if it doesn't. And if
you do have time to do the anyinteract vs. not disjoint testing let me
know.

best regards,

Chris

On Thu, 18 Nov 2004 Stuart.Girvan@anonymised.com wrote:

Hi there,

I'm working with the Social Change Online guys on developing the capability
to deal with complex queries and objects. I'm having a few problems with the
Oracle Spatial query at the moment and I'm wondering whether anyone else is
having problems with BBox type queries against Oracle.

The relevant part of the query that Geoserver (probably actually Geotools) is
generating is this:

( NOT SDO_RELATE("LATLONG_GEOM",MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO
_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(126.641345,-26.427555,126
.64
1345,-25.637043,128.991336,-25.637043,128.991336,-26.427555,126.641345,-26.42
755
5)),'mask=disjoint querytype=WINDOW') = 'TRUE' )

it generates this Oracle error:

Caused by: java.sql.SQLException: ORA-13029: Invalid SRID in the SDO_GEOMETRY
object
ORA-06512: at "MDSYS.SDO_CS", line 23
ORA-06512: at "MDSYS.SDO_3GL", line 117

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2337)
        at oracle.jdbc.oci8.OCIDBAccess.fetch(OCIDBAccess.java:2012)

I've checked with a few people who have experience with SDO in Oracle and
they have pointed out that that there's no SRID in the querying bounding box
geometry. I'm not sure why this is hapening. Any ideas? (NB I'm using the
alias & OCI to connect to Oracle, I don't think this should be causing a
problem).

If someone who is successfully doing spatial queries against Oracle using
Geoserver could send me the query that Geoserver is generating (which is
pretty easy to get from the webserver logs) that would be really useful.

Cheers,
Stuart

--