I have some points stored as WGS84 (EPSG:4326) data. I want to write a SELECT statement to return the projected values, in EPSG:2811, and unprojected values.

Here is my table SQL:

CREATE TABLE points (name varchar, point geometry);

Here is the SQL used to inserted the points:

INSERT INTO points(name, point) VALUES(2, ST_GeomFromText('POINT(<lon> <lat>)', 4326));

This gives me a readable view of the imported data for a quick QA check:

SELECT name, ST_AsText(point) FROM points;

How do I add the transformed coordinates to my SELECT statement? I think I need to use ST_TRANSFORM but beyond that I'm lost.

Install details:

POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER

The ST_Transform function has quite simple parameters: a geometry object and a target SRID. This should do it.

SELECT name, ST_AsText(ST_Transform(point,2811)) FROM points;
  • For future readers, you need to set the source SRID before the above works. SELECT UpdateGeometrySRID(TableName, ColumnName, EPSG_ID); – DenaliHardtail Apr 1 '15 at 23:09
  • Right. Ideally that happens when the table is created. – alphabetasoup Apr 1 '15 at 23:44

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.