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 <[email protected]> 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 > [email protected] > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
