Speeding up pgRouting

pgRouting and accessibility
pgRouting and accessibility

I have been using pgRouting for some accessibility analysis to various facilities on the network and experimenting with different ways of making the process faster.

My initial network had 28000 edges and to solve a catchment area problem for one location on the network to all other nodes on the network was taking 40 minutes on a 2.93GHz quad core processor with 4GB RAM (Windows 7 PostgreSQL 9.2 PostGIS 2.0.3 and pgRouting 1.0.7).  I put the query into a looping function that processed the facilities in order but any more than 4 and the machine would run out of memory as the complete solution is stored in RAM until the loop finishes.

First step, reduce the number of edges in the network to 23000 and number of nodes to 17000 by removing pedestrian walkways, alleys, private and restricted roads.  Now the query is solved in about 12-14 minutes using about 200MB RAM per facility. Continue reading Speeding up pgRouting

pgRouting on Windows 7

pgrouting-networkDrive time isochrones with pgRouting and PostGIS on Windows 7 Pro.  I am using Postgresql 9.0, 9.1 and 9.2, PostGIS 2.0.3 and pgRouting 1.0.7-dev.  Anita Graser has some excellent pgRouting resources on her blog  and I used them in conjunction with Jo Cook’s pgRouting with ITN guide to load and build my network dataset.

One of the problems I had while following this post was that I had more than the three airports she used in her example and to manually update the table 116 times for each play park in the county was not an option.

So, how to write a looping function in Postgresql to loop through all the play parks and calculate all the drive times for each facility in one go?

Postgresql docs are a good place to go and this is what worked for me in the end.  Once the table is complete finish up using Anita’s how to.

-- 1. create calculate playpark costs function
-- NOTE: function returns an integer representing the next play park nearest node id
-- NOTE: loops through every nearest_node, calculating drive times and inserting records into the table
FOR nn IN SELECT nearest_node FROM osplayparks_pts LOOP
 INSERT INTO catchment_pp (
 (SELECT sum(cost) FROM (
 SELECT * FROM shortest_path('
 SELECT objectid AS id,
 source::int4 AS source,
 target::int4 AS target,
 traveltime::float8 AS cost
 FROM angus_routes',
 nn, -- this updates each time with a new value for nearest_node
 false)) AS foo ) AS cost
 FROM vertices_tmp);
$$ LANGUAGE 'plpgsql';

-- 2. run calculate playpark cost function
SELECT * FROM insertPlayParkCosts();

Installing pgRouting on Windows 7


This quick guide outlines the steps I took to get Postgresql, PostGIS and pgRouting installed on Windows 7 Pro.

1. Install Postgresql

Download the installer from EnterpriseDB.com, double click to start the installer and accept most of the defaults except for the port to run the server on.  I installed 9.0.13, 9.1.9 and 9.2.4 running each server on a different port.  So 9.0.13 is running on 5432, 9.1.9 on 5433 and 9.2.4 on 5434.

The installers install the correct version of pgAdmin III for each version of the database and it is recommended to use the right pgAdmin III with the right Postgresql.  This ensures database integrity and interactive functionality suitable for each version.  For Postgresql 9.2 use pgAdmin 1.16.1, for Postgresql 9.1 use 1.14.3 and for Postgresql 9.0 use 1.12.3.

2. Install PostGIS

Download the installers from OSGEo.

For Postgresql 9.0 install http://download.osgeo.org/postgis/windows/pg90/postgis-pg90-setup-1.5.5-1.exe and select the correct instance of Postgresql on port 5432

For Postgresql 9.1 install http://download.osgeo.org/postgis/windows/pg91/postgis-pg91-setup-1.5.5-1.exe and select the correct instance of Postgresql on port 5433. I could have installed a later version of PostGIS on 9.1 but I needed 1.5.5 to work with AstunTechnology’s PortableGIS Loader! (See archaeogeek’s blog)

For Postgresql 9.2 install http://download.osgeo.org/postgis/windows/pg92/postgis-pg92-setup-2.0.3-2.exe and select the correct instance of Postgresql on port 5434

3. Install pgRouting

For the development versions of pgRouting visit Boston GIS and get 1.0.7dev for Windows 32-bit and PostgreSQL 9.2 (I am running 32-bit Windows).  pgRouting contains the following features:

  • Dijkstra algorithm – Shortest path algorithm, which named in honor of Prof. Dr. Edsger Wybe Dijkstra who has invented it
  • A-star (A*) algorithm – Shortest path algorithm using heuristical function
  • Driving distance – area person can cover in certain time from start point using road network
  • TSP Travelling Salesman Problem solution
  • Shooting star (Shooting*) algorithm – Shortest path algorithm for real road networks with turn restrictions, traffic lights and one way streets.

To install and enable pgRouting (read the README) copy the contents of /lib (.dlls required for problem solving) and /share/extension (.sql files to enable pgrouting in the database) to the C:\Program Files\PostgreSQL\9.2\lib\ and C:\Program Files\PostgreSQL\9.2\share\extension folders respectively.

Then, using the psql prompt or pgAdmin III, do:


Now you should have a pgRouting-enabled PostGIS database.

There are older version of pgRouting available to download from Github.  You’ll need to extract the zip archive and run the various SQL files to install the required routing functionality as well as copying the .DLL files to the correct folder as above.  Boston GIS have also release dev versions of pgRouting 2.0.0 which I have not tried.