PostGIS
Name
UpdateGeometrySRID — Updates the SRID of all features in a geometry column, and the table metadata.
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. If the column was enforced by a type definition, the type definition will be changed. 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
Insert geometries into roads table with a SRID set already using EWKT format:
COPY roads (geom) FROM STDIN;
SRID=4326;LINESTRING(0 0, 10 10)
SRID=4326;LINESTRING(10 10, 15 0)
\.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) ;