How do you calculate lat/lons from a PostGIS table which has a projected coordinate system?

Question

I have a PostGIS table in a projected coordinate system (EPSG: 28355 or MGA94 Zone55). In QGIS (with the PostGIS manager plugin) I calculate a coordinate by performing the following SQL "UPDATE gisprod.buildings SET easting = x(centroid(the_geom))".

Now I want to add a "lat" and "lon" field to this table and calculate the values using the appropriate coordinate system, in this case that would be (EPSG:4283 or GDA94). Is there a way I can specify in the SQL statement to calculate the x/y coord using a different coordinate system?

Thanks

Andrew

Answer 1

Try this, it should work:

ALTER TABLE gisprod.buildings ADD COLUMN lat double precision;
ALTER TABLE gisprod.buildings ADD COLUMN lon double precision;

UPDATE gisprod.buildings SET lat = ST_X(ST_Transform(centroid(the_geom),4283));
UPDATE gisprod.buildings SET lon = ST_Y(ST_Transform(centroid(the_geom),4283));

Answer 2

ST_Transform is your friend.

/Nicklas