[GIS] Transformation of geometry on select Postgresql

coordinate systempostgispostgis-2.2postgresql

I am trying to run a transformation of a geom via a select query, the layer has been digitised in WGS 4326 and when I run my select to filter the table by my criteria, I would like to transform the CRS from WGS84 (4326) to UTM36S (32736)

My final Query is designed make a new table of attributes from the first changing the CRS 4326 – 32736

I have two steps I cannot seem to join

SELECT ST_AsText(geom) FROM foo  which provides the list of geoms as text
MULTIPOLYGON(((32.841274 -25.151649,.............32.841274 -25.151649)))

And with the resultant Text entered in: 

SELECT ST_AsText(ST_Transform(ST_GeomFromText('MULTIPOLYGON(((32.841274 -25.151649,.............32.841274 -25.151649)))',4326),32736)) As wgs_geom;

I get the result:

MULTIPOLYGON(((484002.707664207 ............... 7218250.78539813)))

But:

SELECT ST_AsText(ST_Transform(ST_GeomFromText FROM (SELECT ST_AsText(geom) FROM foo) ,4326),32736)) As wgs_geom;

or 

SELECT ST_AsText FROM a (ST_Transform(ST_GeomFromText FROM (SELECT ST_AsText(geom) FROM foo) as a ,4326),32736)) As wgs_geom;

doesn't run I've spent a bit of time looking at the syntax of the SQL but can't get it to run…

Any Ideas?

Note: the MULTIPOLYGON are quite complex hence ……….

Best Answer

First off:

  • You can chain most functions directly if their return types are compatible, so no need to subselect within a function
  • Geometries in textual representation are merely a tool for readability, so no need to have them translated in intermediate steps


  • Your understanding of the SQL syntax is...problematic, as is the notation ,). Get a good read on general SQL query structure, the w3schools for example offer a quite comprehensive introduction



But, to help you out here; if I assume correctly that you simply want all geometries in table foo transformed into the given projection, and the original geometries are correctly referenced to a CRS, try

SELECT ST_Transform(geom, 32736) AS utm_geom
FROM foo;

to get the binary representation of your transformed geometries (standard format used for storage and geometric analysis within PostGIS) for further tasks, or

SELECT ST_AsText(ST_Transform(geom, 32736)) AS utm_geom
FROM foo;

to get a simple textual representation of your geometries.

If you´d like to create a new table with those geometries, use

CREATE TABLE bar AS
  SELECT ST_Transform(geom, 32736) AS utm_geom
  FROM foo;

(I need to add here: creating tables like above is trivial, maintaining performant database/table structures is not. Among a lot of other things, for one of the next steps in learning PostgreSQL/PostGIS I recommend to read about indexes/table statistics and performance in general)

Filters are most commonly added in the WHERE block, like

SELECT ST_Transform(geom, 32736) AS utm_geom
FROM foo
WHERE ST_IsValid(geom);       --Note: this is equal to 'WHERE ST_IsValid(geom) = TRUE'

to select only those rows/geometries of table foo that are well formed.

Note: in all cases, these queries will only return your geometries, no other column will be added to either the output or the table. To add more columns, name them in the SELECT command, possibly before the geometry, like

SELECT <column_a> AS <new_name_for_a_if_needed>,    --repeat for other columns if necessary
       ST_Transform(geom, 32736) AS utm_geom
FROM foo;
Related Question