[Geoserver-devel] Oracle DataStore and Filter

Patrick_Mollbrink · November 1, 2007, 12:20pm

Hi list,

Would anyone out there happen to know if the oracle datastore handles all
possible scenarios in
the04-095_OpenGIS_Filter_Encoding_Implementation_Specification_V1.1.pdf
documentation?

I'v been trying to search the forum for more info on this but decided to
post a new message.

The Geoserver documentation states about filters "All this is handled
transparently by the datastore so the programmer just has to send a Query
object off to the DataStore and not have to worry about how it processes it.
The features returned by the FeatureReader will only be ones that pass the
Filter conditions."

So, does the oracle datastore handles all filter options defined by OGC, and
do you need to apply any specific syntax to make them work?

Best regards
Patrick
--
View this message in context: http://www.nabble.com/Oracle-DataStore-and-Filter-tf4730931.html#a13527572
Sent from the GeoServer - Dev mailing list archive at Nabble.com.

Justin_Deoliveira · November 1, 2007, 1:05pm

In theory yes... in practice... doubtful, there are usually issues that
come up when encoding filters. Especially with things like dates.
However, here is how it works:

Whenever a jdbc datastore like oracle receives a filter, it analyzes the
filter to determine which parts of it can be encoded to sql. The parts
that can be encoded into sql are used to query the underlying database.
The rest is executed in memory after the fact.

So simple filters like:

Are easy to encode as straight sql:

WHERE foo = 'boo'

However for more complex filters, like if a function is used:

There is no good way of encoding this as sql since the function "bar"
properly does not exist in the database. So the filter is processed
after the fact.

Hope that helps answer your question. If not please ask more.

-Justin

patrick@anonymised.com wrote:

Hi list,

Would anyone out there happen to know if the oracle datastore handles all
possible scenarios in
the04-095_OpenGIS_Filter_Encoding_Implementation_Specification_V1.1.pdf
documentation?

I'v been trying to search the forum for more info on this but decided to
post a new message.

The Geoserver documentation states about filters "All this is handled
transparently by the datastore so the programmer just has to send a Query
object off to the DataStore and not have to worry about how it processes it.
The features returned by the FeatureReader will only be ones that pass the
Filter conditions."

So, does the oracle datastore handles all filter options defined by OGC, and
do you need to apply any specific syntax to make them work?

Best regards
Patrick

--
Justin Deoliveira
The Open Planning Project
http://topp.openplans.org

Patrick_Mollbrink · November 1, 2007, 1:52pm

Hi Justin,

Thanks for your reply, as you probably understand I’m new to Geoserver and I’m still trying to grasp the big picture of things.

So, I guess that in real life it’s really a matter of trail and error depending on the DataStore
That is ok, I really wanted to find out if there are any major do’s and dont’s to handling this.

You mention date as a potential problem, have you any experience with syntax like the one below?

START_TIME 2008-01-01T00:00:00.00 2008-01-01T12:00:00.00

I have just configured my test environment Oracle XE, Geoserver and MapInfo Pro and everything works fine.
Next week I will try to define the syntax for the filters, so I might just take your offer of asking more

Best regards
Patrick mollbrink

Justin Deoliveira skrev följande, 2007-11-01 14:05:

···

–

Andrea_Aime3 · November 1, 2007, 2:12pm

Patrick Mollbrink ha scritto:

Hi Justin,

Thanks for your reply, as you probably understand I'm new to Geoserver and I'm still trying to grasp the big picture of things.

So, I guess that in real life it's really a matter of trail and error depending on the DataStore
That is ok, I really wanted to find out if there are any major do's and dont's to handling this.

You mention date as a potential problem, have you any experience with syntax like the one below?

<Filter>
  <Between>
    <PropertyName>START_TIME</PropertyName>
      <LowerBoundary>
        <Literal>2008-01-01T00:00:00.00</Literal>
      </LowerBoundary>
      <UpperBoundary>
        <Literal>2008-01-01T12:00:00.00</Literal>
      </UpperBoundary>
  </Between>
</Filter>

I have just configured my test environment Oracle XE, Geoserver and MapInfo Pro and everything works fine.
Next week I will try to define the syntax for the filters, so I might just take your offer of asking more

As far as I know Oracle + dates -> boom, there are some issues
open about it:
http://jira.codehaus.org/browse/GEOT-725
http://jira.codehaus.org/browse/GEOT-1353

Unfortunately there is no maintainer for the Oracle data store
(I'm the assignee of the issues just as a placeholder, waiting
for someone to step up and become a real mantainer). If you want
these to be fixed quickly you should explore the idea of contracting
some company to do the associated developments, such as TOPP, Refractions, Geomatys, Geosolutions and the like.
I guess fixing these should not take more than a few days.

Cheers
Andrea

Patrick_Mollbrink · November 1, 2007, 2:56pm

Thanks Andrea,

Thanks for the input!

I guess we have to consider a quick work around by changing the data model by using two timestamp columns, one “real” and one populated by a trigger/function where we can store a “string version” of the timestamp and point the filter to that one. Or perhaps build a view where the timestamp is in string format. will try this out and let you know.

/Patrick

Andrea Aime skrev följande, 2007-11-01 15:12:

···

–

Andrea_Aime3 · November 1, 2007, 2:57pm

Patrick Mollbrink ha scritto:

Thanks Andrea,

Thanks for the input!

I guess we have to consider a quick work around by changing the data model by using two timestamp columns, one "real" and one populated by a trigger/function where we can store a "string version" of the timestamp and point the filter to that one. Or perhaps build a view where the timestamp is in string format. will try this out and let you know.

Hmmm... if you're going for equality it may work, otherwise
I suggest you find a way to turn that into a number so that you can
do < and > comparisons as well (and if you do so, use GeoServer 1.6)
Cheers
Andrea