How to transform geometries with PostGIS

By Dillon Smart · · · 0 Comments

ST_Transform in PostGIS

PostGIS is a power extention for PostgreSQL databases. It provides a suite of tools to handle and manipulate geographic data. At times, you may need to transform geometries with PostGIS for different base maps or to perform more accurate calculations, such as calculating the area of a polygon.

Transform geometry with PostGIS

PostGIS has a helpful function ST_Transfrom(geometry, SRID) which takes a geometry as its first parameter, and transforms the geometry to a different spatial reference system, which is the second parameter. See the offial documentation.

For example, if your geometry had an SRID of 27700, and you needed to transform it to 3857, you would use the following, where the value of the SRID parameter is the SRID you want the geometry transfromed to.

ST_Transform(geometry_field, 3857)

Geometry has an unknown SRID

ST_Transform not working? Often, when working with geometries which are poorly documented, you can find youself unaware of the SRID for a given geometry. Typically, if the data is sourced online, the creators will give a couple of options for the geometries projection. However, this isn’t always the case.

When executing the ST_Transform function, you may be presented with the “ST_Transform: Input geometry has unknown (0) SRID” error. This error message indicates that the geometries SRID cannot be changed because the geometry is of an unknown SRID.

To overcome this issue, PostGIS has a helpful function to determing the SRID of a given geomtery. You can find the SRID of a geometry using the ST_SRID function.

ST_SRID(geometry) // outputs the SRID of the geometry e.g. 27700

Once you know the SRID of the geometry, you can use the same ST_Transform method. If passing 3 parameters, the second parameter becomes the from_srid and the third parameter becomes the to_srid, like so:

ST_Transform(geometry_field, 27700, 3857)

Using ST_SetSRID to set the SRID of geometry

The same can be achieved by wrpping the geometry in the ST_SetSRID function.

ST_Transform(ST_SetSRID(geometry_fiels, 27700), 3857)


0 Comment

Was this helpful? Leave a comment!

This site uses Akismet to reduce spam. Learn how your comment data is processed.

How to install PostGIS

Updated 2nd October 2023

PostGIS is a plugin which adds support for storing, indexing and querying geographic data in PostgreSQL databases. To get started with PostGIS, you will need to install the extension on your PostgreSQL database. If you are using PostgreSQL as a service from a cloud provider, you may already have PostGIS installed. How to check if

SQL Union – What is a Union and how to combine two or more SQL Select queries

Updated 1st December 2023

The SQL UNION Operator is used to combine two or more SELECT statements together into one set of results. Things to know about SQL Union Operator To execute a SQL UNION statement successfully, you need to be away of the requirements of a UNION statement. Example using SQL UNION In our example we have 2