I have this query that returns the_geom as text. However how could I also apply a transform to the same query. I can do this separately with the second example. Though I would like to do this to all the rows in my table, and in that example I copied the st_astext output into the transform query.

select sitename, st_astext(the_geom) from sites_tbl;

sitename | st_astext

Belfast A | POINT(145592 530021)

ST_AsText(ST_Transform(ST_SetSRID('POINT(145592 530021)'::geometry,27700), 4326));


You can use ST_Transform in your first query too.

select sitename, st_astext( ST_Transform( the_geom, 4326 ) ) from sites_tbl;

This is a query I use to write out the LAT/LON of points from data stored in State Plane (feet):

, st_x(st_centroid(st_transform(residential_block_points.geom, 4326))) as X
, st_y(st_centroid(st_transform(residential_block_points.geom, 4326))) as Y

and you could add this data as new fields in the existing table:

ALTER TABLE "residential_block_points" ADD x double precision;
ALTER TABLE "residential_block_points" ADD y double precision;
UPDATE "residential_block_points" SET x = st_x(st_centroid(st_transform(residential_block_points.geom, 4326)));
UPDATE "residential_block_points" SET y = st_y(st_centroid(st_transform(residential_block_points.geom, 4326)));

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.