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;