3

Is there's a way to reproject all geometries columns of a postgis schema given the same source SRID to the same destination SRID?
Exemple :
schema.table1 has 2 geometries columns geom and geom2 with the same SRID 900913
schema.table2 has 2 geometries columns geom and geom2 with the same SRID 900913
etc..
I want to project all those columns in SRID 2154. I tought there's could be a simple way by using the public.geometry_columns view but I don't know how.

  • Interested to know if you have tried out my answer, have an issue with it, etc? – John Powell Mar 13 '15 at 12:23
6

You can do this using a Postgres DO block, essentially an anonymous function that you just run once from a psql prompt. Something along these lines:

DO $$declare r record; 
  BEGIN 
    FOR r in SELECT srid as srs, f_table_name as name, f_geometry_column as geom
         FROM geometry_columns WHERE f_table_schema = 'schema_name'
    LOOP
       RAISE NOTICE 'updating table: %, geom column: %, with srid: %', r.name, r.geom, r.srs;
       PERFORM UpdateGeometrySRID(r.name, r.geom, 2154); 
       EXECUTE FORMAT ('UPDATE %I set %I = ST_Transform(%I, 2154)', r.name,  r.geom, r.geom);
    END LOOP;
END$$;

This uses UpdateGeometrySRID to update the meta data and ST_Transform to transform the geometries. You have to use Perform within an anonymous block like this, as you are not returning anything from the select and execute format as you have dynamic table names.

Naturally, you can add more to the WHERE clause, as you might not want to exclude certain tables and you will need to replace the schema_name with the actual schema name. Clearly, you could put this in a function too, but it sounds like a one off exercise.

There may well be a simpler way that I am unaware of.

|improve this answer|||||
0

Thanks John, perfect workaround with slight update.

DO $$Declare r record; 
  BEGIN 
    FOR r in SELECT srid as srs, f_table_name as name, f_geometry_column as geom
     FROM geometry_columns WHERE f_table_schema = 'Schema_name'
    LOOP
       RAISE NOTICE 'updating table: %, geom column: %, with srid: %', r.name, r.geom, r.srs;
       PERFORM UpdateGeometrySRID(quote_ident(r.name), quote_ident(r.geom), 2154); 
       EXECUTE FORMAT ('UPDATE %I set %I = ST_Transform(%I, 2154)', r.name, r.geom, r.geom);
   END LOOP;
END $$;
|improve this answer|||||

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.