WFS request with OGC filter subtracts time zone offset from database date_time - GML output only

Would be interesting to know if the same issue happens without going through SDE, hitting Oracle directly

Hi Andrea,

How could I set up that kind of connection to Oracle SDE
so I could test that?

Thanks,

Andrew

Not 100% sure, but I believe you might be able to by installing the Oracle extension and the configuring the data from it. If SDE uses the native oracle geometries you should be able to configure it as a spatial layer, otherwise you should still be able to configure it as geometryless (the geometry will likely be represented as a binary blob) and query it via WFS anyways

Adding Gabriel, he might have a clue if this is SDE specific or not

Hi Andrea,

I think if I installed the Oracle Extension this may not work as our
Oracle is not 'Oracle Spatial' or 'Oracle Locator' its Oracle with the
SDE plugin. One of our machines has Oracle 9i and uses SDBINARY (use ESRI ARCSDE 9.2 on this machine)
in SHAPE Column. while the other machine has newer Oracle 10g and
uses newer ST_GEOMETRY in SHAPE column (use ERSI ARCSDE 9.3.1 on this machine).
Is there any way that the Geoserver Oracle extension would work with these
Oracle setups?

Andrew

I never tried, but my understanding is that the Oracle data store will see the SDE geometry and not understand it, as a result it will treat it as a BLOB field, either showing as is in binary format, or completely skip it. But the other fields should be visible.

Do you have a sample of the data that we could use?
A dump with some instructions on how to import it in SDE so that it's registered the same way as yours would help

Hi Andrea,

Sorry for the delay. Attached is a CSV dump of part of table BEACH_TEMP
that could be used to load/test with ORACLE-SDE

csv of beach_temp table

Andrea,

Sorry attachment added more than once! this jira interface confuses me re. attachments.
Anyway continuing..

To reproduce the environment use:
Oracle 10g (Oracle SDE) using Geometry column type ST_GEOMETRY
ARCSDE version 9.3.1 which can handle ST_GEOMETRY (not ARCSDE 9.2 as only handles Geometry type SDBINARY)

Here are specs for BEACH_TEMP table:

COLUMN NAME ORACLE DATA TYPE

OBJECTID NUMBER
LON NUMBER(10,4)
LAT NUMBER(10,4)
LOC_ID NUMBER(10,4)
LOCATION NVARCHAR(200)
OBS_DATE_TIME DATE
TZONE VARCHAR2(10)
SST NUMBER(10,4)
SHAPE SDE.ST_GEOMETRY

If you need any more information please let me know.

Thanks for offer to look further into this.

Andrew

I'm still not very familiar with SDE, do you have commands to import that data and register it in SDE?
Right now I only know how to upload shapefiles using shp2sde :-p

Andrea,

I not in office today. Tomorrow I will consult with
an expert in office about how this could be done.
Normailly we don't directly load to Oracle-SDE but
rather through ESRI's ARCGIS Desktop-ArcCatalog which manages
OBJECTID and spatial index. Do you have ESRI
ESRI's ARCGIS Desktop running there?

If you don't have ESRI software there maybe there
is direct loading way, I will ask tomorrow
if that's possible.

Andrew

Nope, I don't own any ESRI software, I actually just happen to have temporary access to an SDE instance

Hi Andrea,

You should be able to use the arcsde shp2sde utility. I have attached a test shapefile
set you can use. I checked the shapefile with the Geoserver shapefile
data store and it looks OK. (Geoserver added the beach_tempMod.qix on store creation)

Note because of limitation of the shapefile DATE type which
only can store the dd/mm/yyyy part of a date_time the shapefile attached
doesn't have an OBS_DATE_TIME column. But that's no problem as you
can add an ORACLE DATE type column once the shapefile is in an SDE table.

To do this in Oracle SQL:

SQL> alter table beach_tempMod
add OBS_DATE_TIME date;

Then populate the OBS_DATE_TIME column with values. Repeat the insert
statement below for the number of rows in the table. Doesn't matter
if they are all same date_time for our test purposes:

SQL>insert into beach_tempMod(obs_date_time)
values(to_date('2012-03-01 11:56:00','yyyy-mm-dd hh24:mi:ss'));

End result should be table in Oracle-SDE with these columns

LON
LAT
OBJECTID
LOC_ID
LOCATION
SST
OBS_DATE_TIME
SHAPE

Table should then be ready to to create a Geoserver ARCSDE
data store from.

Let me know how it goes.

Regards,

Andrew

I have a similar problem:

This is a request with a filter for "incident" having 2008/01/01 <= "dateacte" <= 2008/01/31:
http://localhost:8080/geoserver/wfs?service=WFS&version=1.0.0&request=GetFeature&outputFormat=json&typeName=d:incident&filter=<ogc:Filter xmlns:ogc="http://www.opengis.net/ogc"><ogc:And><ogc:PropertyIsGreaterThanOrEqualTo><ogc:PropertyName>dateacte</ogc:PropertyName><ogc:Literal>2008/01/01</ogc:Literal></ogc:PropertyIsGreaterThanOrEqualTo><ogc:PropertyIsLessThanOrEqualTo><ogc:PropertyName>dateacte</ogc:PropertyName><ogc:Literal>2008/01/31</ogc:Literal></ogc:PropertyIsLessThanOrEqualTo></ogc:And></ogc:Filter>

Response excerpt:
{"type":"Feature","id":"incident.1706","geometry":

,"geometry_name":"geom","properties":{"gid":1706,"dateacte":"2007-12-31Z","numero":"72174"}},
{"type":"Feature","id":"incident.1707","geometry":

,"geometry_name":"geom","properties":{"gid":1707,"dateacte":"2008-01-01Z","numero":"72224"}}

As you can see, some features do not fit in the expected time range. Offset is always 1 day before, my result set actually range from 2007/12/31 to 2008/01/30.

The other problem is the UTC flag appended after date strings (not sure if it's a bug or a feature but it's a change compared to previous GeoServer versions).

This change appears with GeoServer 2.1.3 and still exists in the recent 2.1.4. I can't reproduce it with older versions (tried 2.1.1 and 2.1.2).

My backend is a PostGIS database and the "dateacte" column has a date type (date only, not datetime).

I'm running GeoServer within TomCat on a Debian system. My /etc/timezone says "Europe/Paris".

I've try to set -Duser.timezone=UTC in JAVA_OPTS but it didn't solve the problem.

I hope this will help to find the cause of the problem. Let me know if you need other informations.

Regards
Gilles

Hi Gilles,

Thanks.
Yes your problem is similar and shows it's an issue with other databases (postGIS)and
other WFS output formats (JSON in your case)
I haven't had time to investigate. Your report will hopefully add
some more weight into getting this fixed. Basically Geoserver shouldn't
be altering the date/time that is stored in the db.

Andrew