howto

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.

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:

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:

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:

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:

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

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.

You can also give an array of points and the function will draw the line wich interpolates those 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:

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:

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