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?


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

I think ST_Transform is your friend.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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