[postgis-users] RE: [Geoserver-users] Time series in postGIS?

[Stephen Crawford]

Thanks a lot, that really helps.   -Steve 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
ValiSystem
Sent: Thursday, May 10, 2007 5:55 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] RE: [Geoserver-users] Time series in postGIS?


On 9 mai 07, at 15:06, Stephen Crawford wrote:

> Andrea,
>
> Is there a good way to define the view so that the dates, which are 
> rows in the normal table, become columns in the view, and also that 
> the view is always updated with the most recent date?

Stephen,

There is now way to do such a thing in a pure relational way (read simple
sql statements). You could imagine aggregate several rows on a result
column, but you cannot  distribute row values of the same columns into
several result columns. (this would be a mix of grouping but showing each
row values of groups). The problem is that there is now way to differentiate
rows in SQL. You are manipulating data sets, not values.

But you could achieve that using triggers : create a trigger for each date
number, and update it on each main table modification. But what ever you do,
you will have to fix the number of dates in your history.

The other way is to concatenate all the dates into one string in your view,
creating an aggregate function, and be able to do something like :

SELECT siteID, concat_rows(date) FROM my_table GROUP BY siteID;

with concat_rows defined like this :

CREATE OR REPLACE FUNCTION concat(text, text)
   RETURNS text AS
$BODY$
    select $1 || $2 || ', ';
$BODY$
   LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE concat_rows(
   BASETYPE=text,
   SFUNC=concat,
   STYPE=text
);

this will concatenate dates, comma separated. Quickly, you need to define an
aggregate function, here 'concat', that will define how to make to different
elements a single one (note that you can used a predefined postgres
function). Then you declare the aggregate 'concat_rows' in the database.

The main issue is that you cannot use date formating function after the call
in your programming language (i mean, directly on result set access), but
note that you can use it inside your query (
"concat_rows(to_char('YYYY-MM-DD', date))" ).
Also you may not want to have everything concatenated in your final result
display, but you may split it later.

( http://www.postgresql.org/docs/8.2/static/xaggr.html )

I hope it'll help, i often wanted to do what you want, but it's incompatible
with relational database and SQL axioms (relational algebra).

> What I hope to have is a feature
> layer on Geoserver where a GetFeature request will always return the 
> entire "year to date" without having to use a filter.
>
> Thanks Again,
> Steve
>
>
>
> -----Original Message-----
> From: geoserver-users-bounces at lists.sourceforge.net
> [mailto:geoserver-users-bounces at lists.sourceforge.net] On Behalf Of 
> Andrea Aime
> Sent: Tuesday, May 08, 2007 5:00 PM
> To: Stephen Crawford
> Cc: geoserver-users at lists.sourceforge.net; 'PostGIS Users Discussion'
> Subject: Re: [Geoserver-users] Time series in postGIS?
>
> Stephen Crawford ha scritto:
>> All,
>>
>> I have a question on setting up a postGIS database for use with 
>> geoserver; please pardon the double-posting but I wasn't sure where I
> should send it.
>>
>> I have created quite a few postGIS/geoserver services already, but I 
>> haven't yet had a "dynamic" one like this.  I have a set of point 
>> locations for which data will be collected daily.  In a "normal"
>> (non-spatial) database I would just have a table with columns for 
>> siteID,date,value where siteID and date make up the primary key. But 
>> to use with geoserver, it seems like I need to have a row for each 
>> location, where the columns are the date, like
>> this:
>> siteID,date_1,date_2,date_3,.......
>>
>> What's the best way to do this?  I plan to use a GetFeature WFS 
>> request with geoserver, and would like get all the dates/values back 
>> for each location so my client can "animate" through time.
>
> Hum, I would model the database in normal form (as you described in 
> the first sample) and then build views joining the site and data 
> table, and register it against the geometry colums so that Geoserver 
> can view that as a table.
> Then use a filter to show the situation at a given time. Hope this 
> helps Cheers Andrea
>
> ----------------------------------------------------------------------
> ---
> This SF.net email is sponsored by DB2 Express Download DB2 Express C - 
> the FREE version of DB2 express and take control of your XML. No 
> limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> Geoserver-users mailing list
> Geoserver-users at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users