I’ve wanted to be able to do proper road network analysis for a while now,  shortest routes, matrix routing, accessibility and so on. There are ready to go options available that’ll do this for you, but I wanted to build something myself, partly just for the fun and interest of it, and partly so I could run analysis without worrying about costs or set up requests to a service whenever I wanted to poke at it.

So the plan I made was to put together my own pgRouting setup, running locally, on real road data. 

I decided to start with creating a network of King Island, Tasmania. It’s small and the road network is compact enough that it should be able to load and query without any fuss, but still has enough branching and a complicated enough network to be interesting (and to make sure the routing requests are actually working correctly).

The basic idea for this first pass:

• Get a PostgreSQL/PostGIS instance running
• Pull the OSM road data for King Island and load it into a routing-ready network
• Run a basic shortest-path query to confirm the graph is actually usable

This would then allow me to move onto building more complex and larger networks to support some other projects I want to explore further.

I decided to use Postgres.app to run the database locally. I have found it to be super easy and friendly to get going and (so far) has been able to support everything I’ve needed in terms of PostGIS and pgrouting.

For getting the OSM data into a routing schema I used osm2pgrouting rather than writing my own import. There was a bit of mucking about, getting a version of the data downloaded, for it to then load into the table, but once I worked it out, it seems to work pretty simply.

So the next thing to do was start making some routing queries and check that they were working correctly. 

The simple Dijkstra operation in pgrouting supports array routing, so I selected a single origin node from the network (shown below in white) and a few different destination nodes (shown below in pink).

Then made the request:

SELECT 
    res.end_vid,      
    res.path_seq,     
    res.node,         
    res.edge,         
    res.cost,         
    res.agg_cost,     
    e.geom        
FROM pgr_dijkstra(
    'SELECT id as id, source, target, length_m as cost FROM ways',
    2066,                  -- Single Origin Node ID
    ARRAY[88, 875, 1464],  -- Multiple Destination Node IDs
    directed := false
) AS res
JOIN ways e ON res.edge = e.id
ORDER BY res.end_vid, res.path_seq;

Nothing fancy, just picked an origin node and destination nodes and checked the returned paths made sense when I plotted it. 

It did, which was a good sign the topology out of osm2pgrouting is clean and the network is properly connected end to end.

Now that I have a working workflow for getting data and building usable a network, I want to apply this to some of the larger cities in Australia and use it on some other ideas. More to come 🙂