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
