[postgis-users] Crosstabbing a bunch of value columns (un-normalizing for GIS)

[MarkW]

How about the CASE statement to throw specific years into specific columns?
Works if years/scenarios are limited/known.

http://www.postgresql.org/docs/8.4/static/functions-conditional.html

Does this help?
Mark


On Mon, Apr 19, 2010 at 1:24 PM, John Abraham <jabraham at ucalgary.ca> wrote:

> We have a bunch of tables with columns like this:
>
> *GeometryUniqueID
> *Year
> *Scenario
> Value
>
> where * indicates the primary key
>
> (And, of course, a separate table for the Geometry
>
> *GeometryUniqueID
> TheGeometry)
>
> To compare values across scenarios with MapServer, QGIS, ArcGIS etc. we
> need it like this
>
> *GeometryUniqueID
> TheGeometry
> Year1Scenario1Value
> Year1Scenario2Value
> Year1Scenario3Value
>
> and to compare values across years we need it like this
>
> *GeometryUniqueID
> TheGeometry
> Year1Scenario1Value
> Year2Scenario1Value
> Year3Scenario1Value
>
> That is, we want to compare values across scenarios and across years for
> the same geometry.  But we've normalized all our tables so that there is a
> row for each unique combination of scenario and year.  And, unfortunately,
> all of the GIS tools that will plot our data seem to expect the data to be
> in different columns in the same row (i.e., not normalized, at least not to
> my mind).
>
> Is there a standard approach to appending each column to a view so that the
> PostGIS layer contains comparable values in different columns?
>
> Right now we're thinking of a fairly complex select that joins the table to
> itself once for each year (or each scenario) but that doesn't feel right for
> many reasons, not the least of which we need to know the number of years (or
> scenarios) before we can write the view.
>
> Thanks in advance.  I don't want to reinvent the wheel here.  Crosstabbing
> in SQL is always hard but I bet someone on this list has solved this before.
> --
> John Abraham
> jabraham at ucalgary.ca
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100419/4583e25b/attachment.html>