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

[Mike Toews]

One approach is to use joins from the same att_table onto your geom_table
using aliases:

SELECT geom_table.GeometryUniqueID, geom_table.TheGeometry,
Year1Scenario1.Value AS Year1Scenario1Value, Year1Scenario2.Value AS
Year1Scenario2Value, Year1Scenario3.Value AS Year1Scenario3Value
FROM geom_table
LEFT JOIN att_table Year1Scenario1 ON Year1Scenario1.GeometryUniqueID =
geom_table.GeometryUniqueID AND Year1Scenario1.Scenario = 1 AND
Year1Scenario1.Year = 1
LEFT JOIN att_table Year1Scenario2 ON Year1Scenario2.GeometryUniqueID =
geom_table.GeometryUniqueID AND Year1Scenario2.Scenario = 2 AND
Year1Scenario2.Year = 1
LEFT JOIN att_table Year1Scenario3 ON Year1Scenario3.GeometryUniqueID =
geom_table.GeometryUniqueID AND Year1Scenario2.Scenario = 3 AND
Year1Scenario3.Year = 1

http://www.postgresql.org/docs/current/static/tutorial-join.html

<http://www.postgresql.org/docs/current/static/tutorial-join.html>-Mike

On 19 April 2010 10:24, 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/20100421/21f4d0b1/attachment.html>