Transforming from SRID 900913 to 4326 with PostGis

Question

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.

Answer 1

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

Answer 2

It worked for me with this sequence:

CREATE TABLE tabla_900913 AS (SELECT ST_TRANSFORM(geom, 4326) FROM "tabla_4326")