Contours– Structuring PostGIS data for viewing with GeoServer
Posted by smathermather on May 25, 2011
Naively structured data is my bane– the desire (and need) to get stuff done so often overtakes the time needed to do things the better way. So, we bootstrap.
A long time ago, we managed to load in a few tens of gigs of contour data into PostGIS, partitioned it into 2ft, 10ft, 20ft, 50ft, 100ft and 250ft tables using select queries with a modulus operator, e.g.
CREATE TABLE base.cuy_contours_10 AS SELECT elevation, the_geom FROM base.cuy_contours_2 WHERE base.cuy_contours_2.elevation % 10 = 0;
However, layer groups can often be replaced by properly structured PostGIS tables. For large (and homogenous) datasets like this, it’s the only way to go. In addition, properly structuring this allows us to take advantage of GeoServer’s ability to modify the legend based on zoom extent, which for representing contours at a range of scales is an almost “must have”.
To structure the table, we could be disciplined and build this out as a proper normalized relational dataset where our gid is used to determine if a contour is divisible by 10, 20, 50 etc., and while “I don’t wanna” isn’t a good enough reason not to do this, I think the computational overhead of a database view piecing these data back into a single table each time we need to access this would not be justified in the light of the static nature of the table. So database normalization be darned, disk space is cheap, full speed ahead. Let’s add some boolean fields for flagging whether a contour is divisible by our numbers and calculate that out:
UPDATE base.contours_2 SET div_10 = CAST( contours_2.elevation % 10 AS BOOLEAN );
UPDATE base.contours_2 SET div_250 = CAST( contours_2.elevation % 250 AS BOOLEAN );
yields the following (with highlights added):
Sort of the opposite of what I intended, the “falses” maybe should be “trues” and vice versa, but hey, it’ll work anyway. BTW, we did test doing this calculation a few different ways, and while I can’t remember all the details, doing this as a calculation instead of doing an update query with a while statement testing the modulus was much faster (3x faster).
Ok, so now we style an sld for it, and sit back and enjoy (pics later…).