[Geoserver-users] Problem with OracleNG and Loose BBOX

GeoServer GeoServer User
1

Hi,

I think I found a bug in the OracleNG plugin.

When the parameter Loose bbox is set to true and a bbox filter is used the method doSDOFilter is called.

This results in the following query:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE SDO_FILTER(GEOMETRY, ?) = ‘ TRUE’

Which is an invalid query.

SDO_FILTER uses 3 parameters so it should be:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE SDO_FILTER(GEOMETRY, ?, ‘querytype=WINDOW’) = ‘ TRUE’

Workaround is to set the loose bbox parameter to false so the plugin creates an SDO_RELATE version of the query like this:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE SDO_RELATE(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘ TRUE’

Cheers

Kris Geusebroek

Consultant



cid:image001.jpg@anonymised.com

Email: kgeusebroek@anonymised.com



Tel: +31 (0)35 538 1921



Fax: +31 (0)35 538 1922



Mobile: +31 (0)6 30 697 223



http://www.xebia.com

Utrechtseweg 49



1213 TL Hilversum



The Netherlands

Xebia Blog ! http://blog.xebia.com/

Xebia Podcast! http://podcast.xebia.com/

2

Kris Geusebroek ha scritto:

Hi,

I think I found a bug in the OracleNG plugin.

When the parameter Loose bbox is set to true and a bbox filter is used the method doSDOFilter is called.

This results in the following query:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE SDO_FILTER(GEOMETRY, ?) = ‘ TRUE’

Which is an invalid query.

SDO_FILTER uses 3 parameters so it should be:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE SDO_FILTER(GEOMETRY, ?, ‘querytype=WINDOW’) = ‘ TRUE’

Workaround is to set the loose bbox parameter to false so the plugin creates an SDO_RELATE version of the query like this:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE SDO_RELATE(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘ TRUE’

Funny, I think I benchmarked the datastore with loose bbox = true
and it was working? Anyways, I checked the syntax and it's indeed missing the "PARAMS" part:
http://www.mpi-inf.mpg.de/departments/d5/teaching/ss05/is05/oracle/appdev.920/a96630/sdo_operat.htm

Can you open a bug report on jira.codehaus.org?
Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

3

Done: http://jira.codehaus.org/browse/GEOT-2383

Cheers Kris

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Thursday, March 12, 2009 2:53 PM
To: Kris Geusebroek
Cc: geoserver-users
Subject: Re: [Geoserver-users] Problem with OracleNG and Loose BBOX

Kris Geusebroek ha scritto:

Hi,

I think I found a bug in the OracleNG plugin.

When the parameter Loose bbox is set to true and a bbox filter is used

the method doSDOFilter is called.

This results in the following query:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE
SDO_FILTER(GEOMETRY, ?) = ' TRUE'

Which is an invalid query.

SDO_FILTER uses 3 parameters so it should be:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE
SDO_FILTER(GEOMETRY, ?, 'querytype=WINDOW') = ' TRUE'

Workaround is to set the loose bbox parameter to false so the plugin
creates an SDO_RELATE version of the query like this:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE
SDO_RELATE(GEOMETRY, ?, 'mask=anyinteract querytype=WINDOW') = ' TRUE'

Funny, I think I benchmarked the datastore with loose bbox = true
and it was working? Anyways, I checked the syntax and it's indeed
missing the "PARAMS" part:
http://www.mpi-inf.mpg.de/departments/d5/teaching/ss05/is05/oracle/appde
v.920/a96630/sdo_operat.htm

Can you open a bug report on jira.codehaus.org?
Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

4

Hi,
I think you work with an old Oracle release (pre 10.1). In previous releases, the SDO_FILTER operator required three parameters. Effective with Oracle Spatial release 10.1, the operator has only two parameters: a geometry_column and geometry from a table or a transient SDO_GEOMETRY object.
In newer versions any keywords for the third parameter that were supported in the previous release will still work (for backward compatibility); but the use of those keywords is deprecated.

Cheers,
Ivano

2009/3/12 Kris Geusebroek <kgeusebroek@anonymised.com>

Done: http://jira.codehaus.org/browse/GEOT-2383

Cheers Kris

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]
Sent: Thursday, March 12, 2009 2:53 PM
To: Kris Geusebroek
Cc: geoserver-users
Subject: Re: [Geoserver-users] Problem with OracleNG and Loose BBOX

Kris Geusebroek ha scritto:

Hi,

I think I found a bug in the OracleNG plugin.

When the parameter Loose bbox is set to true and a bbox filter is used

the method doSDOFilter is called.

This results in the following query:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE
SDO_FILTER(GEOMETRY, ?) = ’ TRUE’

Which is an invalid query.

SDO_FILTER uses 3 parameters so it should be:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE
SDO_FILTER(GEOMETRY, ?, ‘querytype=WINDOW’) = ’ TRUE’

Workaround is to set the loose bbox parameter to false so the plugin
creates an SDO_RELATE version of the query like this:

SELECT column1, GEOMETRY as GEOMETRY from table1 WHERE
SDO_RELATE(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ’ TRUE’

Funny, I think I benchmarked the datastore with loose bbox = true
and it was working? Anyways, I checked the syntax and it’s indeed
missing the “PARAMS” part:
http://www.mpi-inf.mpg.de/departments/d5/teaching/ss05/is05/oracle/appde
v.920/a96630/sdo_operat.htm

Can you open a bug report on jira.codehaus.org?
Cheers
Andrea

–
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

Apps built with the Adobe(R) Flex(R) framework and Flex Builder™ are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse™based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com

Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

5

Ivano ha scritto:

Hi,
I think you work with an old Oracle release (pre 10.1). In previous releases, the SDO_FILTER operator required three parameters. Effective with Oracle Spatial release 10.1, the operator has only two parameters: a geometry_column and geometry from a table or a transient SDO_GEOMETRY object.
In newer versions any keywords for the third parameter that were supported in the previous release will still work (for backward compatibility); but the use of those keywords is deprecated.

Aaah, this would explain why it works for my Oracle XE, it's a
reduced functionality version of 10.2 afaik.
Anyways, if having three params works for all Oracle versions,
let's change it.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

6

Hi,

I agree with you, but I think it’s better to code Oracle support distinguishing specific versions. It’s more clear and simpler to mantain. Finally, this helps to code specific query, as SDO_FILTER and SDO_RELATE in Oracle prior 10.1 with the sdo_cs.viewport_transform function.

Cheers,

Aaah, this would explain why it works for my Oracle XE, it’s a
reduced functionality version of 10.2 afaik.
Anyways, if having three params works for all Oracle versions,
let’s change it.

Cheers
Andrea

–
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.