Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

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

ST_Transform is your friend.

/Nicklas

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

Your Answer

 
discard

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.