Adding additional geometry column in PostGIS?
To add a column to an existing table, use the ALTER TABLE DDL, e.g.:
ALTER TABLE my_table
ADD COLUMN the_geom_mercator
geometry(Geometry,3857);
which can be populated from another column (the_geom) using:
UPDATE my_table SET
the_geom_mercator = ST_Transform(the_geom, 3857)
FROM spatial_ref_sys
WHERE ST_SRID(the_geom) = srid;
(the third line FROM spatial_ref_sys ...
isn't necessary, but it guards transform attempts with unknown or invalid projections, which raise errors).
And if this table is to be maintained (added/updated), you can use a trigger function to update the_geom_mercator, e.g.:
CREATE OR REPLACE FUNCTION my_table_tg_fn() RETURNS trigger AS
$BODY$BEGIN
IF TG_OP = 'INSERT' AND NEW.the_geom ISNULL THEN
RETURN NEW; -- no new geometry
ELSIF TG_OP = 'UPDATE' THEN
IF NEW.the_geom IS NOT DISTINCT FROM OLD.the_geom THEN
RETURN NEW; -- same old geometry
END IF;
END IF;
-- Attempt to transform a geometry
BEGIN
NEW.the_geom_mercator := ST_Transform(NEW.the_geom, 3857);
EXCEPTION WHEN SQLSTATE 'XX000' THEN
RAISE WARNING 'the_geom_mercator not updated: %', SQLERRM;
END;
RETURN NEW;
END;$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER my_table_tg BEFORE INSERT OR UPDATE
ON my_table FOR EACH ROW
EXECUTE PROCEDURE my_table_tg_fn();
Note that ST_Transform should trap errors and show a warning, e.g.:
postgis=# INSERT INTO my_table(the_geom)
postgis-# VALUES (ST_SetSRID(ST_MakePoint(0,1), 123))
postgis-# RETURNING the_geom, the_geom_mercator;
WARNING: the_geom_mercator not updated: GetProj4StringSPI: Cannot find SRID (123) in spatial_ref_sys
-[ RECORD 1 ]-----+---------------------------------------------------
the_geom | 01010000207B0000000000000000000000000000000000F03F
the_geom_mercator |
INSERT 0 1
First create a normal non-spatial table, which you already have. Second add a spatial column to the table using the OpenGIS "AddGeometryColumn" function.
Example:
CREATE TABLE terrain_points (
ogc_fid serial NOT NULL,
elevation double precision,
);
SELECT AddGeometryColumn('terrain_points', 'wkb_geometry', 3725, 'POINT', 3 );