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

[ValiSystem]

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