How to manipulate coordinates with PostGIS?

August 12, 2015

Postgis is a very powerful extension of PostgresSQL for working with spatial data.

This post will help you get started to manipulate geographical data with PostGIS.

First of all, you need to have a PostgresSQL database with PostGIS running on your instance. If you are new to DSS you can check out the Connecting DSS and PostgreSQL post.

What is a geometry in PostGIS?

A geometry is a representation of a geographical object (possibly complex) used by Postgis to make fast and efficient data manipulations. Most of PostGis functions take geometries as arguments and that's why we need to understand how to convert classic coordinates into such a representation.


The most usual geographical data are points. So, how do we go from a standard (longitude,latitude) tuple to a Postgis geometry of type point? You need to use the ST_MakePoint function. It takes the longitude, the latitude (and eventualy the altitude) and it returns a geometry of type point.

Here is an example of such a request:

SELECT ST_MakePoint(longitude,latitude) as geom FROM list_points

But it's not enough if you really want to use your new geometries with other Postgis functions. Why? Because you need to specify the coordinate system (SRID) from wich your points come from with the ST_SetSRID function. The most common system (and widely used) is SRID=4326 (ie, GPS coordinates). If you have no idea of where your data comes from it's probably this one.

So our request becomes:

SELECT ST_SetSRID(ST_MakePoint(longitude,latitude),4326) as geom
         FROM list_points

Sometimes you may want to convert your data in a specific coordinate system. It is possible with the function ST_Transform wich moves the coordinates of a geometry from its current system to another one.

For example if we want to project our data into the French 'Lambert Nord standard' system (SRID=27561) we can modify our request as this:

SELECT ST_Transform(ST_SetSRID(ST_MakePoint(longitude,latitude),4326),27561) as geom
        FROM list_points

Now, you may want to get back from your geometry to the classic tuple (longitude,latitude). You can do it by using the ST_AsText function which takes a geometry and returns a text (known as "WKT" format).

For example:

SELECT ST_AsText(geom) as points FROM list_geom

You can also use the ST_X and ST_Y functions to directly recover the longitude an latitude of your points.

SELECT ST_X(geom) as longitude, ST_Y(geom) as latitude FROM list_geom


Once we know how to make points it is very simple to build lines. You just have to use the function ST_MakeLine which can take two points (see previous part) and build the line between them.

SELECT ST_MakeLine(point_geom1,point_geom2) as linestring FROM geom_points

You can also give an array of points and the function will draw the line wich interpolates those points.

SELECT ST_MakeLine(ARRAY[point_geom1,point_geom2,point_geom3,point_geom4])
                    as linestring FROM geom_points

And then there are several interesting functions to characterize this line. You can: - get the the length with ST_Length - get the starting point with ST_StartPoint - get the endding point with ST_EndPoint - get the number of points in the line with ST_NPoints


The last main geometry you have to know is the polygon. As you can guess it delimitates an area. There are many ways to build a polygon from points, lines, other polygons... Here we only give two methods.

The first one is the simplest since it only creates a polygon from a closed line. You have to use the function ST_MakePolygon with a closed line geometry (it means that first and last points are the same).

Here is an example:

SELECT ST_MakePolygon(
        ST_MakeLine(point_geom1,point_geom2,point_geom3,point_geom1)) as poly
        FROM geom_points

The other method is quite more complicated but also useful. The aim is to build the smallest convex polygon containing a collection of points. In order to do that, you need first to build a geometry from a collection of points with the function ST_Collect and then use the ST_ConvexHull function which will make the job.

Let see an example:

SELECT ST_ConvexHull(ST_collect(point_geom1,point_geom2,point_geom3)) as poly
        FROM geom_points

Manipulating all those geometries

This is not the subject of this post but here is a non-exhaustive list of useful functions you can use with the geometries we just learnt to build:

  • ST_Within checks if a geometry (for example a point) is contained in another one (for example a polygon)
  • ST_DWithin checks if a geometry is within a specified distance from another one (be carefeul to the units! If you want to speak in meters you have to cast your geometry into a geography)
  • ST_Distance calculates the distance between two geometries (same remark for the units)
  • ST_Area calculates the area of a polygon (same remark for the units)
  • ST_Centroid returns a point which is the centroid of your geometry