Hi list,
I am getting some problems using OGC filter and CQL filter
on a Oracle DATE type through ARCSDE-Oracle.
The database table is a view which contains an attribute OBS_DATE_TIME
I am using Geoserver 2.0.1 WFS with an ESRI ARCSDE-Oracle 9.3 data store.
Operator PropertyIsGreaterThan (>) and PropertyIsLessThan (<)
work OK but PropertyIsLessThanEqualTo (<=) and
PropertyIsGreaterThanEqualTo (>=) do not work, that is the
<= and >= filter had no effect and returned 'True'
This works - I get the expected 2 results
******Bilgola and year 2010 only filter ******
<wfs:GetFeature service="WFS" version="1.1.0"
xmlns:test="http://www.metoc.gov.au/test"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:gml="http://www.opengis.net/gml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.opengis.net/wfs
http://schemas.opengis.net/wfs/1.1.0/wfs.xsd">
<wfs:Query typeName="test:TEST.BEACH_TEMPS">
<ogc:Filter>
<ogc:And>
<ogc:PropertyIsEqualTo escapeChar="\" singleChar="_" wildCard="%">
<ogc:PropertyName>test:LOCATION</ogc:PropertyName>
<ogc:Literal>Bilgola</ogc:Literal>
</ogc:PropertyIsEqualTo>
<ogc:PropertyIsGreaterThan>
<ogc:PropertyName>test:OBS_DATE_TIME</ogc:PropertyName>
<ogc:Function name="dateParse">
<ogc:Literal>yyyy-MM-dd HH:mm:ss</ogc:Literal>
<ogc:Literal>2009-12-31 23:59:59</ogc:Literal>
</ogc:Function>
</ogc:PropertyIsGreaterThan>
<ogc:PropertyIsLessThan>
<ogc:PropertyName>test:OBS_DATE_TIME</ogc:PropertyName>
<ogc:Function name="dateParse">
<ogc:Literal>yyyy-MM-dd HH:mm:ss</ogc:Literal>
<ogc:Literal>2011-01-01 00:00:00</ogc:Literal>
</ogc:Function>
</ogc:PropertyIsLessThan>
</ogc:And>
</ogc:Filter>
</wfs:Query>
</wfs:GetFeature>
but these don't,
<ogc:PropertyIsLessThanEqualTo>and <ogc:PropertyIsGreaterThanEqualTo>
This filter has no effect and just get 3 results i.e all results for 'Bilgola'.
I notice that the DATE string returned by Geoserver has a random 1/1000
second values appended to them and a time zone. For example a date is stored
in Oracle as:
2010-11-18 09:09:00
but is returned in GML as ISO8601 to 1/1000 sec precision with a timezone
2010-11-18T09:09:00.005+11:00
and in CSV output as ISO8601 to 1/1000 sec precsion WITHOUT a timezone
2010-11-18T09:09:00.926
Perhaps the comparison IsEqual (=) bit is not working because of
the random adding of the 1/1000 seconds and this in turn causes
the >= and <= to not work?
I feel the addition of the random 1/1000 value in the output is not correct
as its different to the time which was stored in Oracle. Oracle doesn't
store fractional seconds in a DATE type . Quoting from the Oracle
documentation at http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm
it says:
"The TIMESTAMP datatype is an extension of the DATE datatype. It stores year,
month, day, hour, minute, and second values. It also stores fractional seconds,
which are not stored by the DATE datatype."
So actually the GML and CSV returned are more like an Oracle TIMESTAMP
type.
I also tried to implement date comparsion using the cql_filter and the 'AFTER' operator
as follows but this didn't work:
This gave back 0 results and the following error msg. in the geoserver.log:
2011-04-11 13:39:31,083 WARN [data.ArcSDEQuery] - Error fetching row for TEST.BEACH_TEMPS[
Filter: [[ LOCATION = Bilgola ] AND [ OBS_DATE_TIME > Sat Jan 01 00:01:00 EST 2011 ]]
where clause sent: (TEST.BEACH_TEMPS.LOCATION = 'Bilgola' AND TEST.BEACH_TEMPS.OBS_DATE_TIME > 'Sat Jan 01 00:01:00 EST 2011')
geometry filter:Filter.INCLUDE
org.geotools.arcsde.ArcSdeException: [SDE error -51][Error desc=DATABASE LEVEL ERROR OCCURRED.][Extended desc=
]
Looks like Oracle was not liking the SQL sent to it.
Andrew Walsh