As an open source alternative to ESRI’s Network Analyst, PG routing provides a very solid platform to perform routing queries. This post, in tandem with posts on shortest distance algorithms and driving distance calculations, will highlight certain basic functionalities within pg routing that I have been playing around with over the past few weeks. I should add that most of this would not have been possible without the help of Anita Graser’s blog, which contains many wonderful posts on the seemingly unlimited capabilities of QGIS!
Pg routing can be downloaded here. Copy the bin, lib and share folders over to your postgres installation folder. Test that it is been installed properly by creating a pg routing enabled database using
CREATE extension pg_routing
and then running pgr_version() which should result in details of the pg routing you installed.
Now PG routing is setup we need a routable network to start querying! This is a network which has been split into nodes and vertices and uses the distance between the two to calculate the distance of the overall route. Dependent on the data you use, this can be achieved in two ways.
If using OSM data, the network is not routable by default and a tool is required. I used OSM2pgrouting with download and documentation information provided. The following command changes your .osm file to a routable netwrok.
osm2pgrouting -file 'path to .osm file' -conf 'path to config file' -dbname pgrouting -user postgres -host localhost -port 5632 -password XXXXXXX -clean
Note: a config file is required and can be downloaded from here and you have to change directory to location where osm2pgrouting application file is installed e.g. C:\Program Files\PostgreSQL\9.3\bin.
More tables are generated than required with ‘ways’ and ‘nodes’ being the most important.
This command is avaiable within pg_routing and I used it on the OS open roads dataset which comes in a layer of ways and nodes. Firstly, source and target columns need to be created.
ALTER TABLE ways ADD COLUMN "source" integer; ALTER TABLE ways ADD COLUMN "target" integer;
We then run the topology function
SELECT pgr_createTopology('ways', 0.00001, 'the_geom', 'gid');
Then create indexes for queries to run faster.
CREATE INDEX ways_source_idx ON ways("source"); CREATE INDEX ways_target_idx ON ways("target");
Finally analyse your network for any errors – this website provides some indepth information on how to achieve this.
Once you have a routable network, you can begin querying.