The third in an occasional series of posts dabbling in PgRouting

Once you’ve got your PgRouting database configured in PostgreSQL (see here and here for more information) you might want to use the routing data in an online map. There are a number of tutorials around for doing this using geoserver, and some information on using mapserver, but also a distressing number of posts pleading for assistance!

So, in the spirit of sharing, this is my attempt to pull the various bits of advice together into something a bit more comprehensive.

The key point to remember is that the PgRouting algorithms don’t return geometry, so you can’t automatically display them on a map. You need to join the query results to your network table to get your geometries and give you mappable results.

The most useful links about displaying routing data in mapserver are here and here, and you also need to know a little bit about mapserver runtime substitution, which you will find here.

The approach that worked for me was to take a working shortest_path query based on the examples given above, and test it in psql/pgadmin3. This gives you something like this (go back to my previous post to see the routing table structure for my Ordnance Survey ITN data):

select * from shortest_path('select uid as id, source, target, length as cost from  ways', 45, 65, false, false)

We need to join this to some geometry so we can use it in mapserver, so we alter it to this:

select wkb_geometry from ways join (select * from shortest_path('select uid as id, source, target, length as cost from  ways', 45, 65, false, false)) as route on ways.uid = route.edge_id

In this case, our source node is 45 and our target node is 65, but we want to be able to call these dynamically in our URL (This was the stage that seemed a little hard to find in the online instructions). Our mapserver layer needs to look like this:

    DATA "wkb_geometry from ways join (select * from shortest_path('select uid as id, source, target, length as cost from  ways', %source%, %target%, false, false)) as route on ways.uid = route.edge_id using unique uid using srid=27700'"
        "source_validation_pattern" "."
        "target_validation_pattern" "."
    NAME "shortest_path"
            COLOR 255 0 0
            WIDTH 3

Note the layer-level metadata which tells mapserver to convert the parameters you give it in the URL into the parameters required in the data string.

We can then call this from the mapserver URL like this:


Which in my case returns something like: this

There are a couple of caveats here:

  • Firstly, in this very basic example, you need to know the ID of the source and target node, which is unlikely in a real-use scenario. You’d need to join your node data to some sort of alternative positioning data (Grid Reference, Address, etc) in order to use more intuitive start and end points, or allow people to click the map to generate them.

  • Secondly, you are calculating the routing on the fly each time the map is generated, which is fine with a small sample dataset but not so good in real life. Ideally you’d save the query results into a temporary table for re-use, but this is a good starting point.