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