Reprojecting in PostGIS not working as expected

Question

I just reprojected with PostGIS tree layers using this function:

select UpdateGeometrySRID('schema', 'table', 'geom', 3857)

These tree layers are contiguous. The problem is, when I open it on QGIS to check if they are reprojected well, just one of them is in the correct ubication. I have another layer to intersect and obviously, it doesn't return the expected result as just one of the layers to intersect with is in the correct ubication.

I tried to reproject them directly on QGIS exporting in local with the correct projection, and with this way the reprojection was done well. But I keep asking why the reprojection with PostGIS didn't work.

Answer 1

UpdateGeometrySRID only ever sets the SRID identifier (and changes the typemod accoridngly), it does not reproject!

This scenario is covered by the docs:

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.

with the respective (generic) command:

ALTER TABLE <table>
  ALTER COLUMN geom TYPE geometry(<GEOMETRY_TYPE>, <new_SRID>)
    USING ST_Transform(ST_SetSRID(geom, <old_SRID>), <new_SRID>)
;