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 <mtncl...@gmail.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
>
>
> On Mon, Nov 18, 2013 at 12:31 AM, Christian Mueller <
> christian.muel...@os-solutions.at> 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 <mtncl...@gmail.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
>>
>>
>
--
DI Christian Mueller MSc (GIS), MSc (IT-Security)
OSS Open Source Solutions GmbH
------------------------------------------------------------------------------
Shape the Mobile Experience: Free Subscription
Software experts and developers: Be at the forefront of tech innovation.
Intel(R) Software Adrenaline delivers strategic insight and game-changing
conversations that shape the rapidly evolving mobile landscape. Sign up now.
http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
