10

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?

18

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
  • Thanks for a great answer. It's real neat to use triggers, I will start doing so. Could I add that trigger to the database instead, so that i dont have to add this trigger for every new table? – knutole Jul 21 '15 at 12:45
  • I'm adding data to postgis with shp2psql and table is created when piped through psql. So I can't really add a trigger before table exists? – knutole Jul 21 '15 at 14:08
  • 1
    If you are using shp2pgsql, use an update statement, see above. A trigger is useful if you need to maintain a table, but not for loading. – Mike T Jul 21 '15 at 21:11
2

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 );
1

You can create an unconstrained SRID geometry column to hold the native form and then transform to existing. Here is a contrived example assuming you have polygons that you are copying from a staging table (if you have mixed, you can set type to geometry e.g geometry(Geometry,3857):

CREATE TABLE poi(gid serial primary key, 
   geom_native geometry(POLYGON),  
   geom_mercator geometry(POLYGON,3857) );

INSERT INTO TABLE poi(geom_native, geom_mercator)
SELECT geom, ST_Transform(geom, 3857)
   FROM staging.imported_poly;
  • Thanks for your answer. Is there any way to do this on already existing tables (ie. without using staging tables)? Let's say I already have a table, with a geom column, and I simply want to add another the_geom_webmercator column. How would I do that? – knutole Jul 18 '15 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.