Name
UpdateGeometrySRID — Updates the SRID of all features in a geometry column, geometry_columns metadata and srid. If it was enforced with constraints, the constraints will be updated with new srid constraint. If the old was enforced by type definition, the type definition will be changed.
Synopsis
text UpdateGeometrySRID(varchar
table_name, varchar
column_name, integer
srid);
text UpdateGeometrySRID(varchar
schema_name, varchar
table_name, varchar
column_name, integer
srid);
text UpdateGeometrySRID(varchar
catalog_name, varchar
schema_name, varchar
table_name, varchar
column_name, integer
srid);
Description
Updates the SRID of all features in a geometry column, updating constraints and reference in geometry_columns. Note: uses current_schema() on schema-aware pgsql installations if schema is not provided.
This function supports 3d and will not drop the z-index.
This method supports Circular Strings and Curves
Examples
This will change the srid of the roads table to 4326 from whatever it was before
SELECT UpdateGeometrySRID('roads','geom',4326);The prior example is equivalent to this DDL statement
ALTER TABLE roads
ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326)
USING ST_SetSRID(geom,4326);If you got the projection wrong (or brought it in as unknown) in load and you wanted to transform to web mercator all in one shot You can do this with DDL but there is no equivalent PostGIS management function to do so in one go.
ALTER TABLE roads ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 3857) USING ST_Transform(ST_SetSRID(geom,4326),3857) ;