<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>MarkW wrote:</div><br><blockquote type="cite"><div><font class="Apple-style-span" color="#000000">How about the CASE statement to throw specific years into specific columns?</font></div><div><font class="Apple-style-span" color="#000000"><br></font></div><div><font class="Apple-style-span" color="#000000"><br></font></div><div><font class="Apple-style-span" color="#000000"><a href="http://www.postgresql.org/docs/8.4/static/functions-conditional.html">http://www.postgresql.org/docs/8.4/static/functions-conditional.html</a></font></div><div><font class="Apple-style-span" color="#000000"><br></font></div><div><font class="Apple-style-span" color="#000000">Does this help?</font></div></blockquote><div><br></div><div>Well, sort of. Honestly, I was hoping you'd say "no, you don't have to crosstab your tables, you can do <i>xyz </i>instead" and then you'd explain <i>xyz</i> to me. </div><div><br></div><div><blockquote type="cite"><div><font class="Apple-style-span" color="#000000">Works if years/scenarios are limited/known.</font></div></blockquote><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>--</div><div>John </div><div><a href="mailto:jabraham@ucalgary.ca">jabraham@ucalgary.ca</a></div><div><br></div><div><br></div><blockquote type="cite"><div><font class="Apple-style-span" color="#000000">On Mon, Apr 19, 2010 at 1:24 PM, John Abraham <jabraham at <a href="http://ucalgary.ca">ucalgary.ca</a>> wrote:</font></div><div><font class="Apple-style-span" color="#000000"><br></font></div><div><font class="Apple-style-span" color="#000000">> We have a bunch of tables with columns like this:</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> *GeometryUniqueID</font></div><div><font class="Apple-style-span" color="#000000">> *Year</font></div><div><font class="Apple-style-span" color="#000000">> *Scenario</font></div><div><font class="Apple-style-span" color="#000000">> Value</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> where * indicates the primary key</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> (And, of course, a separate table for the Geometry</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> *GeometryUniqueID</font></div><div><font class="Apple-style-span" color="#000000">> TheGeometry)</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> To compare values across scenarios with MapServer, QGIS, ArcGIS etc. we</font></div><div><font class="Apple-style-span" color="#000000">> need it like this</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> *GeometryUniqueID</font></div><div><font class="Apple-style-span" color="#000000">> TheGeometry</font></div><div><font class="Apple-style-span" color="#000000">> Year1Scenario1Value</font></div><div><font class="Apple-style-span" color="#000000">> Year1Scenario2Value</font></div><div><font class="Apple-style-span" color="#000000">> Year1Scenario3Value</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> and to compare values across years we need it like this</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> *GeometryUniqueID</font></div><div><font class="Apple-style-span" color="#000000">> TheGeometry</font></div><div><font class="Apple-style-span" color="#000000">> Year1Scenario1Value</font></div><div><font class="Apple-style-span" color="#000000">> Year2Scenario1Value</font></div><div><font class="Apple-style-span" color="#000000">> Year3Scenario1Value</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> That is, we want to compare values across scenarios and across years for</font></div><div><font class="Apple-style-span" color="#000000">> the same geometry. But we've normalized all our tables so that there is a</font></div><div><font class="Apple-style-span" color="#000000">> row for each unique combination of scenario and year. And, unfortunately,</font></div><div><font class="Apple-style-span" color="#000000">> all of the GIS tools that will plot our data seem to expect the data to be</font></div><div><font class="Apple-style-span" color="#000000">> in different columns in the same row (i.e., not normalized, at least not to</font></div><div><font class="Apple-style-span" color="#000000">> my mind).</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> Is there a standard approach to appending each column to a view so that the</font></div><div><font class="Apple-style-span" color="#000000">> PostGIS layer contains comparable values in different columns?</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> Right now we're thinking of a fairly complex select that joins the table to</font></div><div><font class="Apple-style-span" color="#000000">> itself once for each year (or each scenario) but that doesn't feel right for</font></div><div><font class="Apple-style-span" color="#000000">> many reasons, not the least of which we need to know the number of years (or</font></div><div><font class="Apple-style-span" color="#000000">> scenarios) before we can write the view.</font></div><div><font class="Apple-style-span" color="#000000">></font></div><div><font class="Apple-style-span" color="#000000">> Thanks in advance. I don't want to reinvent the wheel here. Crosstabbing</font></div><div><font class="Apple-style-span" color="#000000">> in SQL is always hard but I bet someone on this list has solved this before.</font></div><div><font class="Apple-style-span" color="#000000">> --</font></div><div><font class="Apple-style-span" color="#000000">> John Abraham</font></div><div><font class="Apple-style-span" color="#000000">> jabraham at <a href="http://ucalgary.ca">ucalgary.ca</a></font></div><div><font class="Apple-style-span" color="#000000">> _______________________________________________</font></div><div><font class="Apple-style-span" color="#000000">> postgis-users mailing list</font></div><div><font class="Apple-style-span" color="#000000">> postgis-users at <a href="http://postgis.refractions.net">postgis.refractions.net</a></font></div><div><font class="Apple-style-span" color="#000000">> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a></font></div><div><font class="Apple-style-span" color="#000000">></font></div></blockquote><div><br></div></body></html>