qgis - Calculating lat/lons from PostGIS table which has projected coordinate system?

I have a PostGIS table in a projected coordinate system (EPSG: 28355 or MGA94 Zone 55). 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?

Answer

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 lon = ST_X(ST_Transform(centroid(the_geom),4283));
UPDATE gisprod.buildings SET lat = ST_Y(ST_Transform(centroid(the_geom),4283));