[postgis-users] RE: [Geoserver-users] Time series in PostGIS

[Ragi Y. Burhum]

On 5/10/07, postgis-users-request at postgis.refractions.net <
postgis-users-request at postgis.refractions.net> wrote:
>
> Message: 8
> Date: Thu, 10 May 2007 11:54:59 +0200
> From: ValiSystem <vali.system at free.fr>
> Subject: Re: [postgis-users] RE: [Geoserver-users] Time series in
>         postGIS?
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <37FC4F18-0DB6-4291-A26A-D10D3AD01789 at free.fr>
> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
>
>
> 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.
>

This is not completely accurate.

You can do what you want using Cross-Tabulations. Although I am not going to
go into the details of these procedures (a simple Google search will lead
you articles from people that are far better than me at articulating the
step-by-step procedures), I can tell you that you can do crosstabs by
cross-joins, by outer-joins, by Subqueries, by CASE expressions, etc - all
standard SQL.

Hope this helps,

Ragi Y. Burhum
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070510/bd06b501/attachment.html>