coordinate system - Adding additional geometry column in PostGIS?

I'm importing many sets of geodata into PostGIS, and they have different SRID's. (Some have EPSG:3857, some EPSG:4326, some something else).

I'd like to create an additional geometry column, eg. the_geom_mercator with SRID EPSG:3857, and also keep the original geom column in whatever SRID it came in.

How can I do this with a PostGIS function?

Answer

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