2

To reproject a table into different CRS is a very common practice in geospatial analysis. However, I am still struggling to reproject my table in PostGIS, after reading loads of Q&A related to this topic.

Here I got a table with a set of points. The original SRID is 28356

SELECT Find_SRID('public', 'pts', 'geom');

enter image description here

I'd like to reproject the table to 4326 for further analysis.

Firstly, I used ST_Transform, but failed with this error message: ERROR: Geometry SRID (4326) does not match column SRID (28356)

UPDATE pts SET geom = ST_Transform(geom,4326);


Then I tried ALTER table but got the same error: ERROR: Geometry SRID (4326) does not match column SRID (28356)

ALTER TABLE pts 
  ALTER COLUMN geom 
  TYPE Geometry(Point, 4326) 


Finally, the UpdateGeometrySRID function seems running correctly and the Find_SRID query return 4326 as expected.

However, when loading the modified layer to the map canvas, they are not in the correct location.

SELECT UpdateGeometrySRID('public', 'pts', 'geom', 4326);
SELECT Find_SRID('public', 'pts', 'geom');

Is there anything wrong with my query?

2
  • It's also common practice in geospatial analysis to project from one data object to another. Your attempt at in-situ change is the issue here. I would suggest creating a new table for the new SRID.
    – Vince
    May 25, 2021 at 0:47
  • or just a new column - there is no problem having multiple geometry columns
    – Ian Turton
    May 25, 2021 at 10:45

2 Answers 2

5

You need to alter the table column and transform the data in one step:

ALTER TABLE pts 
  ALTER COLUMN geom 
  TYPE Geometry(Point, 4326) 
USING ST_Transform(geom, 4326);
1

What if you try specifying a source SRID and target SRID, one of the options in the [documentation][1], in the query used to load the table?

SELECT ST_Transform(geom, 28356, 4326);

Another option would be, as @Scorpio mentioned, to create a new table with the desired SRID

CREATE TABLE new_table
  AS (SELECT ST_Transform(geom, 28356, 4326)  FROM old_table);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.