2

I have a layer on ESPG 4326 and I want to reproject it to ESPG 3857. I'm using this SQL query:

ALTER TABLE table
  ALTER COLUMN geom TYPE geometry(Geometry, 3857)
    USING ST_Transform(ST_SetSRID(geom, 4326), 3857)
;

But I'm getting this error:

ERROR: transform: couldn't project point (271600 5.1817e+006 0): latitude or longitude exceeded limits (-14) SQL state: XX000

I read somewhere to switch the lat and long, but when I open the layer on QGIS (from DDBB) with the expected projection (ESPG 3857) seems all right. I can't export from QGIS and reimport to PostGIS because the layer is to big, so I need to change the projection in PostGIS directly.

Any suggestions?

Improve this question
6
  • 2
    it looks like your points are already in 3857 so you don't need to transform them
    – Ian Turton ♦
    Sep 3, 2021 at 8:42
  • 2
    And if, for whatever reason, the column specifies EPSG:4326, just call the ALTER TABLE with USING ST_SetSRID(geom, 3857);
    – geozelot
    Sep 3, 2021 at 8:45
  • @geozelot okey, now its running, its taking a while becouse it's a big table, but let's see if it works.
    – J.Patordi
    Sep 3, 2021 at 8:59
  • @IanTurton but if I look to the sql strucutre, the geom column is on (geometry, 4326).
    – J.Patordi
    Sep 3, 2021 at 8:59
  • 2
    It's generally unwise to ignore the error message when planning your response to that message. Despite the metadata, at least some of your features are not in decimal degrees. You need to identify the correct coordinate systems before performing overlay.
    – Vince
    Sep 3, 2021 at 11:31

1 Answer 1

Reset to default
1

Your coordinates are already in srid 3857. To test this, I created a new table with a linestring in srid 4326. I then altered the geometry type to 3857:

alter table temp_delete alter column geom type geometry(linestringm, 3857) using st_transform(st_setsrid(geom,4326),3857);

I then attempted to re-alter the table geometry type to 3857 (same code as above), and I get the 'exceeded limits (-14)' error.

If your data is already in 3857, the following code should execute without errors:

alter table temp_delete alter column geom type geometry(linestringm, 3857) using st_setsrid(geom,3857);
Improve this answer

Your Answer

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

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