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

[John Abraham]

MarkW wrote:

> How about the CASE statement to throw specific years into specific columns?
> 
> 
> http://www.postgresql.org/docs/8.4/static/functions-conditional.html
> 
> Does this help?

Well, sort of.  Honestly, I was hoping you'd say "no, you don't have to crosstab your tables, you can do xyz instead" and then you'd explain xyz to me.   

> Works if years/scenarios are limited/known.

I don't know the years/scenarios in advance, but can query the database for them.  Last I checked, using the CASE statement for crosstabbing required knowing the columns in advance.

One option I'm considering is writing PGLSQL code for it.  I think there already is some PGLSQL routing for crosstabbing, but I've never been able to get it to work for my needs.  Maybe we need to write one that's specific to this GIS problem.

--
John 
jabraham at ucalgary.ca


> 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/03064398/attachment.html>