Contour data and table management in PostGIS
Posted by smathermather on February 23, 2010
My contour dataset is almost ready to go live. Just a few more tweaks. I was manipulating it in the public schema. I really want it in my base map schema “base”.
Want to change schemas on a table in Postgre? No problem:
ALTER TABLE public.cuy_contours_2_clip SET SCHEMA base;
Rename a table? No problem:
ALTER TABLE base.cuy_contours_2_clip RENAME TO base.cuy_contours_2;
Save space by not storing higher precision data than you need to (e.g. NUMERIC data for what should be an integer elevation):
ALTER TABLE base.cuy_contours_2 ALTER COLUMN elevation TYPE smallint;
Now I want pre-built queries that store the two foot contour data as only the 10 foot, 20 foot, 50 foot, etc.. These different versions of the dataset will then have zoom factors applied to them when I serve them up in GeoServer. To achieve this, we use Postgre’s modulus operator with WHERE statement, e.g.:
CREATE TABLE base.cuy_contours_250 AS SELECT elevation, the_geom FROM base.cuy_contours_2 WHERE base.cuy_contours_2.elevation % 250 = 0;
And the rest:
CREATE TABLE base.cuy_contours_10 AS SELECT elevation, the_geom FROM base.cuy_contours_2 WHERE base.cuy_contours_2.elevation % 10 = 0;
CREATE TABLE base.cuy_contours_20 AS SELECT elevation, the_geom FROM base.cuy_contours_2 WHERE base.cuy_contours_2.elevation % 20 = 0;
CREATE TABLE base.cuy_contours_50 AS SELECT elevation, the_geom FROM base.cuy_contours_2 WHERE base.cuy_contours_2.elevation % 50 = 0;
CREATE TABLE base.cuy_contours_100 AS SELECT elevation, the_geom FROM base.cuy_contours_2 WHERE base.cuy_contours_2.elevation % 100 = 0;
Contours– Structuring PostGIS data for viewing with GeoServer « Smathermather's Weblog said
[…] contour data into PostGIS, partitioned it into 2ft, 10ft, 20ft, 50ft, 100ft and 250ft tables using select queries with a modulus operator, […]