Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

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;

One Response to “Contour data and table management in PostGIS”

  1. […] contour data into PostGIS, partitioned it into 2ft, 10ft, 20ft, 50ft, 100ft and 250ft tables using select queries with a modulus operator, […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: