I have two Postgres tables with different SRIDs: 4326 and 900913. I want to convert the latter to 4326. I first changed the SRID using:

Select UpdateGeometrySRID('table', 'geomcolumn', 4326).  

I then transformed the reference data using:

update table set geomcolumn = ST_Transform (geomcolumn, 4326). 

This seemed to work. But when I changed the SRS setting of the relevant layer in TileMill to WGS84, I get this error:

Detected out of bounds geographic extent.

Presumably the transformation didn't work but it's not clear why.

  • I guess you want WGS84, which is EPSG:4326, not 4236. – AndreJ Sep 22 '13 at 19:14
  • Quick question: is Tilemill faster if the geometry is WGS84? So is it worth converting each layer that uses a 900913 Projection to WGS84? Sorry.. I am a GIS noob ;) – Georg Jun 15 '15 at 8:56

The transformation failed for your case since the UpdateGeometrySRID command just changes the metadata, but does not transform coordinates. And when you attempt a transform from 4326->4326, no transform is done since the SRIDs are equal.

If you have PostGIS 2.x with a table like this:

CREATE TABLE my_table (
  gid serial primary key,
  geom geometry(Point,900913),
  name text not null

the correct way to transform and change a geometry column's spatial reference system is to use the ALTER TABLE DDL:

ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(Point,4326) USING ST_Transform(geom,4326);
| improve this answer | |
  • I am trying to do the same thing but I cannot seem to get this ALTER TABLE command to work. ALTER TABLE Detail_Building_buildinglayer ALTER COLUMN Geometry TYPE geometry(Polygon,900913) USING ST_Transform(geom,2954); The error I get is relation "detail_building_buildinglayer" does not exist. I don't get this because that is the name of my table not a relation. – Tyler Veinot Jan 22 '16 at 13:51
  • @TylerVeinot see stackoverflow.com/q/21796446/327026 – Mike T Jan 22 '16 at 21:09
  • Thanks, that was the issue; the program that made the geometry table named it Geometry and not geom. After reading that post I tried quotes so it would look for "Geometry" but it still failed. So, I changed the geometry column name to geom, ran the query and it worked; after I just changed the name back to "Geometry". I think the name and the case were causing problems. – Tyler Veinot Jan 23 '16 at 15:45
  • 1
    Have you changed the database column name instead the column name in the SQL script??? Jesus! – Magno C May 20 '16 at 17:26

It worked for me with this sequence:

CREATE TABLE tabla_900913 AS (SELECT ST_TRANSFORM(geom, 4326) FROM "tabla_4326")
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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