[GIS] Transforming from SRID 900913 to 4326 with PostGis

coordinate systempostgispostgresql

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.

Best Answer

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);
Related Question