[Geoserver-users] Error configuring Layer with Time Dimension on Oracle 12c

We are attempting to use the WMS TIME parameter with a temporal view on Oracle 12c. The view is based on a join of several tables, one of which has geometry, and another of which has a valid time period defined using the columns VALID_START and VALID_END. According to Oracle 12c practice, rows which are do not have a set end time have VALID_END = NULL.

When we set the Time dimension columns on the Layer, when a WMS request with no TIME parameter is made, we see an error:

Caused by: java.io.IOException: Problem visiting CUT_BLOCK_FULL visiting CUT_BLOCK_FULL.fid–2a68243e_142b4b4a4e4_-4d8b:java.lang.NullPointerException
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:698)
at org.geotools.data.store.ContentFeatureCollection.accepts(ContentFeatureCollection.java:121)
at org.geoserver.wms.WMS.getCurrentTime(WMS.java:1044)
at org.geoserver.wms.WMS.getTimeElevationToFilter(WMS.java:1208)
at org.geoserver.wms.GetMap.executeInternal(GetMap.java:337)
at org.geoserver.wms.GetMap.run(GetMap.java:201)
at org.geoserver.wms.GetMap.run(GetMap.java:111)
… 104 more
Caused by: java.lang.NullPointerException
at java.sql.Timestamp.compareTo(Timestamp.java:513)
at java.sql.Timestamp.compareTo(Timestamp.java:70)
at org.geotools.feature.visitor.MaxVisitor.visit(MaxVisitor.java:104)
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:689)

Is this expected behaviour? Is the TIME parameter required on all requests?

Further to this issue, there’s good news and bad news.

The good news is that including the TIME= parameter eliminates the error, and appears to work as designed. (Although it would be nice to be able to omit the TIME= parameter and have some sensible default in place to avoid the error. Perhaps the default could be TIME= ?)

The bad news is that the temporal query emitted by GeoServer does not match the temporal semantics of Oracle, and thus does not return enough data. For a query of a time instant:

GeoServer emits: SELECT … WHERE ((? >= VALID_START AND ? <= VALID_END) AND SDO_FILTER(… )

As I mentioned before, Oracle uses a closed-open period model, with VALID-END = NULL representing an open period. Therefore to return all records current at the given time the query should be:

SELECT … WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS NULL) AND SDO_FILTER(… )

In my particular case there are many records which have a NULL VALID_END, and none of these get returned by the current GeoServer query.

I suspect that other temporal databases (such as DB2) have similar semantics.

Any thoughts on how this might be addressed?

···

On Mon, Dec 2, 2013 at 12:51 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

We are attempting to use the WMS TIME parameter with a temporal view on Oracle 12c. The view is based on a join of several tables, one of which has geometry, and another of which has a valid time period defined using the columns VALID_START and VALID_END. According to Oracle 12c practice, rows which are do not have a set end time have VALID_END = NULL.

When we set the Time dimension columns on the Layer, when a WMS request with no TIME parameter is made, we see an error:

Caused by: java.io.IOException: Problem visiting CUT_BLOCK_FULL visiting CUT_BLOCK_FULL.fid–2a68243e_142b4b4a4e4_-4d8b:java.lang.NullPointerException
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:698)
at org.geotools.data.store.ContentFeatureCollection.accepts(ContentFeatureCollection.java:121)
at org.geoserver.wms.WMS.getCurrentTime(WMS.java:1044)
at org.geoserver.wms.WMS.getTimeElevationToFilter(WMS.java:1208)
at org.geoserver.wms.GetMap.executeInternal(GetMap.java:337)
at org.geoserver.wms.GetMap.run(GetMap.java:201)
at org.geoserver.wms.GetMap.run(GetMap.java:111)
… 104 more
Caused by: java.lang.NullPointerException
at java.sql.Timestamp.compareTo(Timestamp.java:513)
at java.sql.Timestamp.compareTo(Timestamp.java:70)
at org.geotools.feature.visitor.MaxVisitor.visit(MaxVisitor.java:104)
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:689)

Is this expected behaviour? Is the TIME parameter required on all requests?

Hi Martin, DB2 does not allow VALID_END to be null. I am using 2999-12-31 for example.

DB2 also uses a closed/open period, the query should be

(? >= VALID_START AND ? < VALID_END)

I am wondering here, because using (less then or equal instead of less then)

(? >= VALID_START AND ? <= VALID_END)

would allow 2 historical versions for a point in time. The first version has this point in time as the end time, the second as the start time. Does this make sense ?, I think not.

Cheers
Christian

···

On Mon, Dec 2, 2013 at 11:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Further to this issue, there’s good news and bad news.

The good news is that including the TIME= parameter eliminates the error, and appears to work as designed. (Although it would be nice to be able to omit the TIME= parameter and have some sensible default in place to avoid the error. Perhaps the default could be TIME= ?)

The bad news is that the temporal query emitted by GeoServer does not match the temporal semantics of Oracle, and thus does not return enough data. For a query of a time instant:

GeoServer emits: SELECT … WHERE ((? >= VALID_START AND ? <= VALID_END) AND SDO_FILTER(… )

As I mentioned before, Oracle uses a closed-open period model, with VALID-END = NULL representing an open period. Therefore to return all records current at the given time the query should be:

SELECT … WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS NULL) AND SDO_FILTER(… )

In my particular case there are many records which have a NULL VALID_END, and none of these get returned by the current GeoServer query.

I suspect that other temporal databases (such as DB2) have similar semantics.

Any thoughts on how this might be addressed?


Rapidly troubleshoot problems before they affect your business. Most IT
organizations don’t have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk


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

DI Christian Mueller MSc (GIS), MSc (IT-Security)
OSS Open Source Solutions GmbH

On Mon, Dec 2, 2013 at 12:51 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

We are attempting to use the WMS TIME parameter with a temporal view on Oracle 12c. The view is based on a join of several tables, one of which has geometry, and another of which has a valid time period defined using the columns VALID_START and VALID_END. According to Oracle 12c practice, rows which are do not have a set end time have VALID_END = NULL.

When we set the Time dimension columns on the Layer, when a WMS request with no TIME parameter is made, we see an error:

Caused by: java.io.IOException: Problem visiting CUT_BLOCK_FULL visiting CUT_BLOCK_FULL.fid–2a68243e_142b4b4a4e4_-4d8b:java.lang.NullPointerException
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:698)
at org.geotools.data.store.ContentFeatureCollection.accepts(ContentFeatureCollection.java:121)
at org.geoserver.wms.WMS.getCurrentTime(WMS.java:1044)
at org.geoserver.wms.WMS.getTimeElevationToFilter(WMS.java:1208)
at org.geoserver.wms.GetMap.executeInternal(GetMap.java:337)
at org.geoserver.wms.GetMap.run(GetMap.java:201)
at org.geoserver.wms.GetMap.run(GetMap.java:111)
… 104 more
Caused by: java.lang.NullPointerException
at java.sql.Timestamp.compareTo(Timestamp.java:513)
at java.sql.Timestamp.compareTo(Timestamp.java:70)
at org.geotools.feature.visitor.MaxVisitor.visit(MaxVisitor.java:104)
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:689)

Is this expected behaviour? Is the TIME parameter required on all requests?

Interesting that DB2 does not allow VALID_END = NULL. Oracle does allow this (although documentation for this is thin - the best I can find is this example in the docs: http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#CACJBAJJ)

Agreed that closed-open semantics require (? >= VALID_START AND ? < VALID_END) - my mistake. (But how does this support models which records information at a point in time?)

···

On Tue, Dec 3, 2013 at 2:31 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

Hi Martin, DB2 does not allow VALID_END to be null. I am using 2999-12-31 for example.

DB2 also uses a closed/open period, the query should be

(? >= VALID_START AND ? < VALID_END)

I am wondering here, because using (less then or equal instead of less then)

(? >= VALID_START AND ? <= VALID_END)

would allow 2 historical versions for a point in time. The first version has this point in time as the end time, the second as the start time. Does this make sense ?, I think not.

Cheers
Christian

On Mon, Dec 2, 2013 at 11:47 PM, Martin Davis <mtnclimb@anonymised.com.84…> wrote:

Further to this issue, there’s good news and bad news.

The good news is that including the TIME= parameter eliminates the error, and appears to work as designed. (Although it would be nice to be able to omit the TIME= parameter and have some sensible default in place to avoid the error. Perhaps the default could be TIME= ?)

The bad news is that the temporal query emitted by GeoServer does not match the temporal semantics of Oracle, and thus does not return enough data. For a query of a time instant:

GeoServer emits: SELECT … WHERE ((? >= VALID_START AND ? <= VALID_END) AND SDO_FILTER(… )

As I mentioned before, Oracle uses a closed-open period model, with VALID-END = NULL representing an open period. Therefore to return all records current at the given time the query should be:

SELECT … WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS NULL) AND SDO_FILTER(… )

In my particular case there are many records which have a NULL VALID_END, and none of these get returned by the current GeoServer query.

I suspect that other temporal databases (such as DB2) have similar semantics.

Any thoughts on how this might be addressed?


Rapidly troubleshoot problems before they affect your business. Most IT
organizations don’t have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk


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

DI Christian Mueller MSc (GIS), MSc (IT-Security)
OSS Open Source Solutions GmbH

On Mon, Dec 2, 2013 at 12:51 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

We are attempting to use the WMS TIME parameter with a temporal view on Oracle 12c. The view is based on a join of several tables, one of which has geometry, and another of which has a valid time period defined using the columns VALID_START and VALID_END. According to Oracle 12c practice, rows which are do not have a set end time have VALID_END = NULL.

When we set the Time dimension columns on the Layer, when a WMS request with no TIME parameter is made, we see an error:

Caused by: java.io.IOException: Problem visiting CUT_BLOCK_FULL visiting CUT_BLOCK_FULL.fid–2a68243e_142b4b4a4e4_-4d8b:java.lang.NullPointerException
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:698)
at org.geotools.data.store.ContentFeatureCollection.accepts(ContentFeatureCollection.java:121)
at org.geoserver.wms.WMS.getCurrentTime(WMS.java:1044)
at org.geoserver.wms.WMS.getTimeElevationToFilter(WMS.java:1208)
at org.geoserver.wms.GetMap.executeInternal(GetMap.java:337)
at org.geoserver.wms.GetMap.run(GetMap.java:201)
at org.geoserver.wms.GetMap.run(GetMap.java:111)
… 104 more
Caused by: java.lang.NullPointerException
at java.sql.Timestamp.compareTo(Timestamp.java:513)
at java.sql.Timestamp.compareTo(Timestamp.java:70)
at org.geotools.feature.visitor.MaxVisitor.visit(MaxVisitor.java:104)
at org.geotools.data.store.ContentFeatureSource.accepts(ContentFeatureSource.java:689)

Is this expected behaviour? Is the TIME parameter required on all requests?

On Mon, Dec 2, 2013 at 11:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Further to this issue, there's good news and bad news.

The good news is that including the TIME= parameter eliminates the error,
and appears to work *as designed*. (Although it would be nice to be able
to omit the TIME= parameter and have some sensible default in place to
avoid the error. Perhaps the default could be TIME=<current time> ?)

The bad news is that the temporal query emitted by GeoServer does not
match the temporal semantics of Oracle, and thus does not return enough
data. For a query of a time instant:

GeoServer emits: SELECT ... WHERE ((? >= VALID_START AND ? <=
VALID_END) AND SDO_FILTER(... )

As I mentioned before, Oracle uses a closed-open period model, with
VALID-END = NULL representing an open period. Therefore to return all
records current at the given time the query should be:

SELECT ... WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS
NULL) AND SDO_FILTER(... )

This filter is actually written as an OGC filter somewhere in the WMS
module (WMS class, buildDimensionFilter method), adding an extra to it
would be easy.
DB2 should not break because of the extra condition right? I mean, the
validation should be done on inserts/updates, but now on the
queries being performed on the data, or am I missing something?

As far as I remember Ian added support for time periods in GeoServer vector
data, would be useful to also hear
his perspective on this (maybe there was a reason to setup the code to work
on closed periods only?)

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

Sounds like a good plan to me, if Christian and Ian are in accord.

···

On Tue, Dec 3, 2013 at 9:29 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Mon, Dec 2, 2013 at 11:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Further to this issue, there’s good news and bad news.

The good news is that including the TIME= parameter eliminates the error, and appears to work as designed. (Although it would be nice to be able to omit the TIME= parameter and have some sensible default in place to avoid the error. Perhaps the default could be TIME= ?)

The bad news is that the temporal query emitted by GeoServer does not match the temporal semantics of Oracle, and thus does not return enough data. For a query of a time instant:

GeoServer emits: SELECT … WHERE ((? >= VALID_START AND ? <= VALID_END) AND SDO_FILTER(… )

As I mentioned before, Oracle uses a closed-open period model, with VALID-END = NULL representing an open period. Therefore to return all records current at the given time the query should be:

SELECT … WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS NULL) AND SDO_FILTER(… )

This filter is actually written as an OGC filter somewhere in the WMS module (WMS class, buildDimensionFilter method), adding an extra to it would be easy.
DB2 should not break because of the extra condition right? I mean, the validation should be done on inserts/updates, but now on the
queries being performed on the data, or am I missing something?

As far as I remember Ian added support for time periods in GeoServer vector data, would be useful to also hear
his perspective on this (maybe there was a reason to setup the code to work on closed periods only?)

Hi

(? >= VALID_START AND (? < VALID_END OR VALID_END IS NULL))

will not hurt DB2.

The important thing is to switch to the closed/open semantics, otherwise the system will not work correctly. I hope this is possible.

Cheers
Christian

···

On Tue, Dec 3, 2013 at 6:51 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Sounds like a good plan to me, if Christian and Ian are in accord.


Rapidly troubleshoot problems before they affect your business. Most IT
organizations don’t have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk


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

DI Christian Mueller MSc (GIS), MSc (IT-Security)
OSS Open Source Solutions GmbH

On Tue, Dec 3, 2013 at 9:29 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Mon, Dec 2, 2013 at 11:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Further to this issue, there’s good news and bad news.

The good news is that including the TIME= parameter eliminates the error, and appears to work as designed. (Although it would be nice to be able to omit the TIME= parameter and have some sensible default in place to avoid the error. Perhaps the default could be TIME= ?)

The bad news is that the temporal query emitted by GeoServer does not match the temporal semantics of Oracle, and thus does not return enough data. For a query of a time instant:

GeoServer emits: SELECT … WHERE ((? >= VALID_START AND ? <= VALID_END) AND SDO_FILTER(… )

As I mentioned before, Oracle uses a closed-open period model, with VALID-END = NULL representing an open period. Therefore to return all records current at the given time the query should be:

SELECT … WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS NULL) AND SDO_FILTER(… )

This filter is actually written as an OGC filter somewhere in the WMS module (WMS class, buildDimensionFilter method), adding an extra to it would be easy.
DB2 should not break because of the extra condition right? I mean, the validation should be done on inserts/updates, but now on the
queries being performed on the data, or am I missing something?

As far as I remember Ian added support for time periods in GeoServer vector data, would be useful to also hear
his perspective on this (maybe there was a reason to setup the code to work on closed periods only?)

Good point.

The problematic code is here: https://github.com/geoserver/geoserver/blob/master/src/wms/src/main/java/org/geoserver/wms/WMS.java#L1166

It’s not clear to me whether the WMS spec imposes particular semantics on the intersection test, or whether different dimensions require different semantics (e.g. TIME vs ELEVATION).

···

On Wed, Dec 4, 2013 at 1:57 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

Hi

(? >= VALID_START AND (? < VALID_END OR VALID_END IS NULL))

will not hurt DB2.

The important thing is to switch to the closed/open semantics, otherwise the system will not work correctly. I hope this is possible.

Cheers

Christian

On Tue, Dec 3, 2013 at 6:51 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Sounds like a good plan to me, if Christian and Ian are in accord.


Rapidly troubleshoot problems before they affect your business. Most IT
organizations don’t have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk


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

DI Christian Mueller MSc (GIS), MSc (IT-Security)
OSS Open Source Solutions GmbH

On Tue, Dec 3, 2013 at 9:29 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Mon, Dec 2, 2013 at 11:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Further to this issue, there’s good news and bad news.

The good news is that including the TIME= parameter eliminates the error, and appears to work as designed. (Although it would be nice to be able to omit the TIME= parameter and have some sensible default in place to avoid the error. Perhaps the default could be TIME= ?)

The bad news is that the temporal query emitted by GeoServer does not match the temporal semantics of Oracle, and thus does not return enough data. For a query of a time instant:

GeoServer emits: SELECT … WHERE ((? >= VALID_START AND ? <= VALID_END) AND SDO_FILTER(… )

As I mentioned before, Oracle uses a closed-open period model, with VALID-END = NULL representing an open period. Therefore to return all records current at the given time the query should be:

SELECT … WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS NULL) AND SDO_FILTER(… )

This filter is actually written as an OGC filter somewhere in the WMS module (WMS class, buildDimensionFilter method), adding an extra to it would be easy.
DB2 should not break because of the extra condition right? I mean, the validation should be done on inserts/updates, but now on the
queries being performed on the data, or am I missing something?

As far as I remember Ian added support for time periods in GeoServer vector data, would be useful to also hear
his perspective on this (maybe there was a reason to setup the code to work on closed periods only?)

On Tue, Dec 3, 2013 at 10:29 AM, Andrea Aime
<andrea.aime@anonymised.com>wrote:

On Mon, Dec 2, 2013 at 11:47 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Further to this issue, there's good news and bad news.

The good news is that including the TIME= parameter eliminates the error,
and appears to work *as designed*. (Although it would be nice to be able
to omit the TIME= parameter and have some sensible default in place to
avoid the error. Perhaps the default could be TIME=<current time> ?)

The bad news is that the temporal query emitted by GeoServer does not
match the temporal semantics of Oracle, and thus does not return enough
data. For a query of a time instant:

GeoServer emits: SELECT ... WHERE ((? >= VALID_START AND ? <=
VALID_END) AND SDO_FILTER(... )

As I mentioned before, Oracle uses a closed-open period model, with
VALID-END = NULL representing an open period. Therefore to return all
records current at the given time the query should be:

SELECT ... WHERE ((? >= VALID_START AND (? <= VALID_END OR VALID_END IS
NULL) AND SDO_FILTER(... )

This filter is actually written as an OGC filter somewhere in the WMS
module (WMS class, buildDimensionFilter method), adding an extra to it
would be easy.
DB2 should not break because of the extra condition right? I mean, the
validation should be done on inserts/updates, but now on the
queries being performed on the data, or am I missing something?

As far as I remember Ian added support for time periods in GeoServer
vector data, would be useful to also hear
his perspective on this (maybe there was a reason to setup the code to
work on closed periods only?)

Sorry for the delay and I got lucky in even seeing this email - have to
adjust my filters :frowning:

I'm not totally clear on the question being asked but it sounds like you
have records with a start but no end and a time instant query is not
returning the correct results because the null check is missing? The
semantics make sense to me and I cannot recall any reason why only closed
periods were implemented. I can also verify the current query emitted does
not work correctly on postgres w/ respect to the desired semantics.

No need to reply unless I'm not understanding things :slight_smile:

--
Ian Schneider
Software Engineer | Boundless
ischneider@anonymised.com