[Geoserver-users] GeoServer and Oracle 12c Valid Time models ?

GeoServer GeoServer User
1

We’re looking at using GeoServer with data stored in Oracle 12c modelled using its new valid time capability [1]. (Actually the data is modelled using the Flashback Archive support for transaction time as well, making it bitemporal. But the valid time dimension is of most interesting for mapping).

Has anyone had experience of using 12c valid time queries through GeoServer?

It would be great if the WMS TIME dimension worked with 12c, but that seems like it might require some enhancements. (Any estimate of level of difficulty for this?)

The use of SQLViews also comes to mind, but this seems to have two issues:

  • it would be necessary to supply a fairly complex substitution parameter value (e.g. “AS OF PERIOD FOR etc”
  • this would have to be supplied for each temporal layer in a WMS query
    But possibly this is doable, via some smarts in the client (or even a proxy which adds the time filter to the request)

In the bigger picture, with SQL:2011 temporal features [2] slowly making their way into database implementations, this kind of capability might be worth addressing in GeoServer at a deeper level.

[1] http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967

[2] http://en.wikipedia.org/wiki/SQL:2011#Temporal_support

2

Same situation in DB2. Since version 10.x. DB2 has temporal support and I am using it for a customer. Temporal support is a really cool feature.

For DB2 I can set a special register for a connection

set current business_time = “”

After this statement, all read requests work as expected. (DB2 includes “for business_time as of” for each statement automatically).

Unfortunately, this may cause problems concerning connection pooling.

+1 for integrating temporal support, but as always, its a matter of time and funding.

Cheers
Chrsitan

···

On Sat, Nov 16, 2013 at 1:06 AM, Martin Davis <mtnclimb@anonymised.com4…> wrote:

We’re looking at using GeoServer with data stored in Oracle 12c modelled using its new valid time capability [1]. (Actually the data is modelled using the Flashback Archive support for transaction time as well, making it bitemporal. But the valid time dimension is of most interesting for mapping).

Has anyone had experience of using 12c valid time queries through GeoServer?

It would be great if the WMS TIME dimension worked with 12c, but that seems like it might require some enhancements. (Any estimate of level of difficulty for this?)

The use of SQLViews also comes to mind, but this seems to have two issues:

  • it would be necessary to supply a fairly complex substitution parameter value (e.g. “AS OF PERIOD FOR etc”
  • this would have to be supplied for each temporal layer in a WMS query
    But possibly this is doable, via some smarts in the client (or even a proxy which adds the time filter to the request)

In the bigger picture, with SQL:2011 temporal features [2] slowly making their way into database implementations, this kind of capability might be worth addressing in GeoServer at a deeper level.

[1] http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967

[2] http://en.wikipedia.org/wiki/SQL:2011#Temporal_support

DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
Free app hosting. Or install the open source package on any LAMP server.
Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
http://pubads.g.doubleclick.net/gampad/clk?id=63469471&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

3

Christian, good to hear that other GeoServer users are interested in SQL:2011-style temporal capability.

Oracle 12c also has a capability to set the valid (business) time at the session level, via the ENABLE_AT_VALID_TIME procedure [1]. How do you set the “register for a connection”? Via GeoServer’s SQL session scripts? [2] If so, why do you say that it’s an issue for connection pooling?

We’re working on a mulit-year, multi-million dollar project, so funding (and/or developer resources) may not be an issue for moving this forward.

I’m interested to hear ideas for how this might be integrated into GeoServer. I have a couple of ideas so far:

  1. Enhance the SQL View capability to allow more complex assembly of SQL statements, including at a minimum the ability to include templated fragments based on the value of variables. The SQL View would also need access to the TIME request parameter value - not sure if this is already available? There might also need to be some reformatting of the TIME values to match Oracle timestamp syntax. This would be a powerful facility with all kinds of applications, I think. But it does have the downside that it would require a SQL View to be defined for every temporal layer (which in our case is potentially several hundred).

  2. Push the processing of the TIME request parameter down into the JDBC driver, and somehow allow it to drive the generation of an AS OF clause where appropriate. This would be a slick way of providing temporal support, but I’m not sure how feasible this is or how much development effort would be required?

It’s unfortunate that (as your reply reveals) the SQL:2011 temporal capability is not standardized across DB vendors. But I suppose it’s no different to spatial SQL syntax in that respect, so GeoServer obviously has a rich enough framework to handle these differences.

[1] http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_flashb_archive.htm#ARPLS74552
[2] http://docs.geoserver.org/stable/en/user/data/database/sqlsession.html

···

On Mon, Nov 18, 2013 at 12:31 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

Same situation in DB2. Since version 10.x. DB2 has temporal support and I am using it for a customer. Temporal support is a really cool feature.

For DB2 I can set a special register for a connection

set current business_time = “”

After this statement, all read requests work as expected. (DB2 includes “for business_time as of” for each statement automatically).

Unfortunately, this may cause problems concerning connection pooling.

+1 for integrating temporal support, but as always, its a matter of time and funding.

Cheers
Chrsitan

On Sat, Nov 16, 2013 at 1:06 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

We’re looking at using GeoServer with data stored in Oracle 12c modelled using its new valid time capability [1]. (Actually the data is modelled using the Flashback Archive support for transaction time as well, making it bitemporal. But the valid time dimension is of most interesting for mapping).

Has anyone had experience of using 12c valid time queries through GeoServer?

It would be great if the WMS TIME dimension worked with 12c, but that seems like it might require some enhancements. (Any estimate of level of difficulty for this?)

The use of SQLViews also comes to mind, but this seems to have two issues:

  • it would be necessary to supply a fairly complex substitution parameter value (e.g. “AS OF PERIOD FOR etc”
  • this would have to be supplied for each temporal layer in a WMS query
    But possibly this is doable, via some smarts in the client (or even a proxy which adds the time filter to the request)

In the bigger picture, with SQL:2011 temporal features [2] slowly making their way into database implementations, this kind of capability might be worth addressing in GeoServer at a deeper level.

[1] http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967

[2] http://en.wikipedia.org/wiki/SQL:2011#Temporal_support

DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
Free app hosting. Or install the open source package on any LAMP server.
Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
http://pubads.g.doubleclick.net/gampad/clk?id=63469471&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

4

Hi Martin

Sorry for the late reply. I am quite under pressure in these days and my customer has a major milestone on 3rd of the December. For this milestone, we use DB2 bitemporal geometries and are lucky to use constant date values only. These data values are encoded in “GeoServer sql views” as described here
http://docs.geoserver.org/stable/en/user/data/database/sqlview.html

I am the module maintainer of the DB2 module and with a probability of 99%, my customer will ask me how to integrate bitemporal support using variable date values.

http://docs.geoserver.org/stable/en/user/services/wms/time.html#wms-time

I will investigate within the next weeks but I would feel unhappy to to integrate the is feature only for DB2. (if this is possible). I think your proposal number 2 is thew way to go.

About the sql syntax:
The Geotools jdbc modules are prepared for different syntax. Each supported database has its own dialect formulating the proper SQL statements.

About connection pooling:
If you set a special register/variable for a connection int thread number 1 and the connection is reused by thread number 2, the later one may get unexpected results from the database.

I wil keep you informed

Cheers
Christian

···

On Mon, Nov 18, 2013 at 7:20 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Christian, good to hear that other GeoServer users are interested in SQL:2011-style temporal capability.

Oracle 12c also has a capability to set the valid (business) time at the session level, via the ENABLE_AT_VALID_TIME procedure [1]. How do you set the “register for a connection”? Via GeoServer’s SQL session scripts? [2] If so, why do you say that it’s an issue for connection pooling?

We’re working on a mulit-year, multi-million dollar project, so funding (and/or developer resources) may not be an issue for moving this forward.

I’m interested to hear ideas for how this might be integrated into GeoServer. I have a couple of ideas so far:

  1. Enhance the SQL View capability to allow more complex assembly of SQL statements, including at a minimum the ability to include templated fragments based on the value of variables. The SQL View would also need access to the TIME request parameter value - not sure if this is already available? There might also need to be some reformatting of the TIME values to match Oracle timestamp syntax. This would be a powerful facility with all kinds of applications, I think. But it does have the downside that it would require a SQL View to be defined for every temporal layer (which in our case is potentially several hundred).

  2. Push the processing of the TIME request parameter down into the JDBC driver, and somehow allow it to drive the generation of an AS OF clause where appropriate. This would be a slick way of providing temporal support, but I’m not sure how feasible this is or how much development effort would be required?

It’s unfortunate that (as your reply reveals) the SQL:2011 temporal capability is not standardized across DB vendors. But I suppose it’s no different to spatial SQL syntax in that respect, so GeoServer obviously has a rich enough framework to handle these differences.

[1] http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_flashb_archive.htm#ARPLS74552
[2] http://docs.geoserver.org/stable/en/user/data/database/sqlsession.html

–

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

On Mon, Nov 18, 2013 at 12:31 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

Same situation in DB2. Since version 10.x. DB2 has temporal support and I am using it for a customer. Temporal support is a really cool feature.

For DB2 I can set a special register for a connection

set current business_time = “”

After this statement, all read requests work as expected. (DB2 includes “for business_time as of” for each statement automatically).

Unfortunately, this may cause problems concerning connection pooling.

+1 for integrating temporal support, but as always, its a matter of time and funding.

Cheers
Chrsitan

On Sat, Nov 16, 2013 at 1:06 AM, Martin Davis <mtnclimb@anonymised.com> wrote:

We’re looking at using GeoServer with data stored in Oracle 12c modelled using its new valid time capability [1]. (Actually the data is modelled using the Flashback Archive support for transaction time as well, making it bitemporal. But the valid time dimension is of most interesting for mapping).

Has anyone had experience of using 12c valid time queries through GeoServer?

It would be great if the WMS TIME dimension worked with 12c, but that seems like it might require some enhancements. (Any estimate of level of difficulty for this?)

The use of SQLViews also comes to mind, but this seems to have two issues:

  • it would be necessary to supply a fairly complex substitution parameter value (e.g. “AS OF PERIOD FOR etc”
  • this would have to be supplied for each temporal layer in a WMS query
    But possibly this is doable, via some smarts in the client (or even a proxy which adds the time filter to the request)

In the bigger picture, with SQL:2011 temporal features [2] slowly making their way into database implementations, this kind of capability might be worth addressing in GeoServer at a deeper level.

[1] http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS967

[2] http://en.wikipedia.org/wiki/SQL:2011#Temporal_support

DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps
OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access
Free app hosting. Or install the open source package on any LAMP server.
Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native!
http://pubads.g.doubleclick.net/gampad/clk?id=63469471&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

5

On Thu, Nov 21, 2013 at 9:31 AM, Christian Mueller <
christian.mueller@anonymised.com> wrote:

I will investigate within the next weeks but I would feel unhappy to to
integrate the is feature only for DB2. (if this is possible). I think your
proposal number 2 is thew way to go.

I agree that direction is better. Wondering, do we need to add special API
to get the list of intervals for
representing the WMS time dimension?
Right now the code is doing a simple distinct query I believe, but a
database with built-in temporal support
will likely just return one range, the one of the currently "valid"
record... or not?

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

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

6

I will give you an example:

Given a normal select

select * from table mytable where …

If this table has business time support it would be

select * from table mytable as of business_time of ‘2011-01-01’ where …

The syntax extension is in the “from” clause. The above query returns only records valid at ‘2011-01-01’. A simple

select * from table mytable where …

would return all historical versions, this is unwanted in most scenarios. If the date is missing it should be

select * from table mytable as of business_time of current time where …

This select returns all actual versions.

The second dimension is system time support. DB2 uses a second table to store modified and deleted records. This happens completely behind the scenes, no java coding necessary. System time support uses the current time stamp of the operating system.

The select would be

select * from table mytable as of system_time of ‘2011-01-01’ where …

Last not least, you can combine those 2 histories, having a “logical” history and “physical” one. This is called bitemporal and gives you the possibility to reconstruct the logical history on the fly for each physical point in time.

For business time , there are some syntax extensions for delete and update.

delete from mytable for portion of business time from ‘2011-01-01’ to ‘2012-01-01’ where …

For insert statments, you have to specify from and to as usual.

I will stop here, I hope you got the idea.

Cheers
Chrstian

···

On Thu, Nov 21, 2013 at 9:42 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

–

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

On Thu, Nov 21, 2013 at 9:31 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

I will investigate within the next weeks but I would feel unhappy to to integrate the is feature only for DB2. (if this is possible). I think your proposal number 2 is thew way to go.

I agree that direction is better. Wondering, do we need to add special API to get the list of intervals for
representing the WMS time dimension?
Right now the code is doing a simple distinct query I believe, but a database with built-in temporal support
will likely just return one range, the one of the currently “valid” record… or not?

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

7

On Thu, Nov 21, 2013 at 10:31 AM, Christian Mueller <
christian.mueller@anonymised.com> wrote:

I will give you an example:

Given a normal select

select * from table mytable where ....

If this table has business time support it would be

select * from table mytable as of business_time of '2011-01-01' where ....

The syntax extension is in the "from" clause. The above query returns only
records valid at '2011-01-01'. A simple

select * from table mytable where ...

would return all historical versions, this is unwanted in most scenarios.
If the date is missing it should be

select * from table mytable as of business_time of current time where ...

This select returns all actual versions.

Hmm... seems pretty close to what GeoServer is doing when time is activated
on vector data, without need for
any special database support:
* if you specify a date range in TIME, it will add that as a search clause
* if you don't specify any, it will use the most current time in the
database... the only thing is that it will take time to extract it, which
is likely suboptimal

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

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

8

Ha, good news.

I will have deeper look at time activation, maybe I can find a solution which requires some modifications in the SQL dialect only. The advantage is that you need not to bother about “date_from” an “date_to” sql attributes, only formulate the “from” clause with a date or a date range. Of course, it is necessary to check the db version and to check if a table support business time.

I will do some investigations.

Cheers

···

On Thu, Nov 21, 2013 at 10:38 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

–

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

On Thu, Nov 21, 2013 at 10:31 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

I will give you an example:

Given a normal select

select * from table mytable where …

If this table has business time support it would be

select * from table mytable as of business_time of ‘2011-01-01’ where …

The syntax extension is in the “from” clause. The above query returns only records valid at ‘2011-01-01’. A simple

select * from table mytable where …

would return all historical versions, this is unwanted in most scenarios. If the date is missing it should be

select * from table mytable as of business_time of current time where …

This select returns all actual versions.

Hmm… seems pretty close to what GeoServer is doing when time is activated on vector data, without need for
any special database support:

  • if you specify a date range in TIME, it will add that as a search clause
  • if you don’t specify any, it will use the most current time in the database… the only thing is that it will take time to extract it, which is likely suboptimal

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

9

Andrea,

If I understand the GeoServer/OGC TIME model, then it is actually quite different to the temporal model in SQL:2011 (which is similar to what IBM and Oracle provide).

In GeoServer, is the case that the TIME request parameter defines a time instant or a time range, which is then used as a filter against a single timestamp column in the underlying datastore? In this case, this allows filtering records which occur at a single point in time (such as observations, which is probably the original use case addressed by the OGC).

In SQL:2011 the model is richer, since it supports modelling and querying records which occur over a time period. To model this two timestamp columns are required, START_TIME and END_TIME, Queries can filter records based on their period intersecting an instant or another period. An important case of this is querying current records (which are typically modelled by having a null value for END_TIME. The queries get a lot more complicated because of the multiple columns involved, which is why the DBs are starting to provide sugar to make this simpler.

It would be interesting if GeoServer started to move towards supporting this model. It should be possible to implement this kind of query in the middle tier, as long as the start and end time columns were providing in the layer metadata. Of course, in this case it would be ideal to push the query down into the DB whereever it is supported natively.

There’s an additional wrinkle with Oracle’s temporal model, which I’ll describe in a followup email.

BTW, in the GeoServer manual the TIME request syntax is well described, but it would be nice to have a description of the the GeoServer TIME query model semantics as well (or at least, I wasn’t able to find it). Perhaps this should go with the description of how to enter the Dimension parameters in the Layers page (also missing?)

···

On Thu, Nov 21, 2013 at 1:38 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Thu, Nov 21, 2013 at 10:31 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

I will give you an example:

Given a normal select

select * from table mytable where …

If this table has business time support it would be

select * from table mytable as of business_time of ‘2011-01-01’ where …

The syntax extension is in the “from” clause. The above query returns only records valid at ‘2011-01-01’. A simple

select * from table mytable where …

would return all historical versions, this is unwanted in most scenarios. If the date is missing it should be

select * from table mytable as of business_time of current time where …

This select returns all actual versions.

Hmm… seems pretty close to what GeoServer is doing when time is activated on vector data, without need for
any special database support:

  • if you specify a date range in TIME, it will add that as a search clause
  • if you don’t specify any, it will use the most current time in the database… the only thing is that it will take time to extract it, which is likely suboptimal
10

On Mon, Nov 25, 2013 at 7:34 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Andrea,

If I understand the GeoServer/OGC TIME model, then it is actually quite
different to the temporal model in SQL:2011 (which is similar to what IBM
and Oracle provide).

In GeoServer, is the case that the TIME request parameter defines a time
instant or a time range, which is then used as a filter against a single
timestamp column in the underlying datastore? In this case, this allows
filtering records which occur at a single point in time (such as
observations, which is probably the original use case addressed by the OGC).

Yes and no. We support both single time and interval.
If you filter against an instant, we use it to extract all records matching
that instant (more than one could apply), or all the records with a
interval containing that time.
If you provide an interval for filtering , then all the ones with a time in
that interval (or with an interval overlapping that interval) will be
returned.

In SQL:2011 the model is richer, since it supports modelling and querying
records which occur over a time period. To model this *two* timestamp
columns are required, START_TIME and END_TIME, Queries can filter records
based on their period intersecting an instant or another period. An
important case of this is querying current records (which are typically
modelled by having a null value for END_TIME. The queries get a lot more
complicated because of the multiple columns involved, which is why the DBs
are starting to provide sugar to make this simpler.

It's not actually that complicated to implement, and we already have it :slight_smile:

It would be interesting if GeoServer started to move towards supporting
this model. It should be possible to implement this kind of query in the
middle tier, as long as the start and end time columns were providing in
the layer metadata. Of course, in this case it would be ideal to push the
query down into the DB whereever it is supported natively.

I'm still confused about what this actually buys us.
What I could see is that the db is in charge of ensuring data integrity
(something that GeoServer does not care about in fact), but at the price of
making
the code to deal with the db actually more complicated than it is now
(since we have to support the plain model, and the one with temporal
support enabled)

There's an additional wrinkle with Oracle's temporal model, which I'll
describe in a followup email.

BTW, in the GeoServer manual the TIME request syntax is well described,
but it would be nice to have a description of the the GeoServer TIME query
model semantics as well (or at least, I wasn't able to find it). Perhaps
this should go with the description of how to enter the Dimension
parameters in the Layers page (also missing?)

Read the WMS specification, time/elevation support, we implement that, with
no extensions

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

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

11

Christian, you have things a bit easier with DB2, since it appears to support only a single business time dimension [1]. This means that in the query

SELECT * FROM mytable FOR BUSINESS_TIME AS OF …

no information about temporal column names is needed, only the timestamps of the filter.

In contrast, Oracle 12c (and SQL:2011) supports multiple valid time periods ( = business time) on a table. This means that they need to be named, which in turn means that temporal queries need to specify the name of the valid time period being queried.

SELECT * FROM mytable AS OF PERIOD FOR user_valid_time TO_TIMESTAMP(‘01-Jun-10’);

However, as in DB2 the column names of the actual time period do not need to be provided, so that’s less metadata to specify.

I suppose in theory there should be schema metadata available which would provide the name of the valid time period(s), and if there was only one then it could be used as the default. Not sure how this works via JDBC, however.

[1] http://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/

···

On Thu, Nov 21, 2013 at 1:31 AM, Christian Mueller <christian.mueller@anonymised.com> wrote:

I will give you an example:

Given a normal select

select * from table mytable where …

If this table has business time support it would be

select * from table mytable as of business_time of ‘2011-01-01’ where …

The syntax extension is in the “from” clause. The above query returns only records valid at ‘2011-01-01’. A simple

select * from table mytable where …

would return all historical versions, this is unwanted in most scenarios. If the date is missing it should be

select * from table mytable as of business_time of current time where …

This select returns all actual versions.

The second dimension is system time support. DB2 uses a second table to store modified and deleted records. This happens completely behind the scenes, no java coding necessary. System time support uses the current time stamp of the operating system.

The select would be

select * from table mytable as of system_time of ‘2011-01-01’ where …

Last not least, you can combine those 2 histories, having a “logical” history and “physical” one. This is called bitemporal and gives you the possibility to reconstruct the logical history on the fly for each physical point in time.

For business time , there are some syntax extensions for delete and update.

delete from mytable for portion of business time from ‘2011-01-01’ to ‘2012-01-01’ where …

For insert statments, you have to specify from and to as usual.

12

On Mon, Nov 25, 2013 at 11:19 AM, Andrea Aime
<andrea.aime@anonymised.com>wrote:

On Mon, Nov 25, 2013 at 7:34 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Andrea,

If I understand the GeoServer/OGC TIME model, then it is actually quite
different to the temporal model in SQL:2011 (which is similar to what IBM
and Oracle provide).

In GeoServer, is the case that the TIME request parameter defines a time
instant or a time range, which is then used as a filter against a single
timestamp column in the underlying datastore? In this case, this allows
filtering records which occur at a single point in time (such as
observations, which is probably the original use case addressed by the OGC).

Yes and no. We support both single time and interval.
If you filter against an instant, we use it to extract all records
matching that instant (more than one could apply), or all the records with
a interval containing that time.
If you provide an interval for filtering , then all the ones with a time
in that interval (or with an interval overlapping that interval) will be
returned.

OK, great. I missed that (because it isn't in the docs, and I hadn't
noticed that the Layer def screen allows you to enter both a start and end
time).

In SQL:2011 the model is richer, since it supports modelling and querying
records which occur over a time period. To model this *two* timestamp
columns are required, START_TIME and END_TIME, Queries can filter records
based on their period intersecting an instant or another period. An
important case of this is querying current records (which are typically
modelled by having a null value for END_TIME. The queries get a lot more
complicated because of the multiple columns involved, which is why the DBs
are starting to provide sugar to make this simpler.

It's not actually that complicated to implement, and we already have it :slight_smile:

I meant it's complicated/error-prone if a table is bi-temporal, and even
more complicated to create temporal queries across joins - for a human.
But agreed, creating the queries automatically for a single time dimension
isn't too hard.

It would be interesting if GeoServer started to move towards supporting
this model. It should be possible to implement this kind of query in the
middle tier, as long as the start and end time columns were providing in
the layer metadata. Of course, in this case it would be ideal to push the
query down into the DB whereever it is supported natively.

I'm still confused about what this actually buys us.
What I could see is that the db is in charge of ensuring data integrity
(something that GeoServer does not care about in fact), but at the price of
making
the code to deal with the db actually more complicated than it is now
(since we have to support the plain model, and the one with temporal
support enabled)

Yes, the code will be more complicated.

As for what it buys, I'm not sure. There might be some performance
improvements if the underlying DB temporal system is used. There's less
metadata required (although that's a wash, since it's already in place).
Also, in Oracle the actual timestamp columns for a valid time period I
think are hidden columns (using a new feature in Oracle) - so not sure how
this will work with GeoServer.

I'm going to try the current GeoServer TIME implementation with our data.
If it works and performs, then I'm happy.... 8^)

There's an additional wrinkle with Oracle's temporal model, which I'll
describe in a followup email.

BTW, in the GeoServer manual the TIME request syntax is well described,
but it would be nice to have a description of the the GeoServer TIME query
model semantics as well (or at least, I wasn't able to find it). Perhaps
this should go with the description of how to enter the Dimension
parameters in the Layers page (also missing?)

Read the WMS specification, time/elevation support, we implement that,
with no extensions

Well, sure, but we all know how easy the OGC specs are to read...