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

[Nicolaas Geldenhuys]

Hi Andrea

You could look into using an array of dates as the field type.
I have not worked with geoserver so I am not sure if it will understand 
the array type.

Nicolaas

On Thu, 2007-05-10 at 11:54 +0200, ValiSystem wrote:
> 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