Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?



share|improve this question
up vote 5 down vote accepted

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));
share|improve this answer
This works perfectly! Much appreciated. – Ando Jul 10 '11 at 22:15
glad to hear it. no problem. :) – Mario Miler Jul 11 '11 at 7:49

ST_Transform is your friend.


share|improve this answer
It certainly is, cheers. – Ando Jul 10 '11 at 22:15

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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