From the archive · 2016
Distance Analysis Projects
Road-network distances from every home in Ireland to nearby health services, used to draw GP and hospital catchments.
I built a spatial analysis toolset that measured road distances between Irish households and services such as GP practices, hospitals and health centres, or any location with a known grid reference. It calculated the distance along the Ordnance Survey Ireland road network from each residential address in An Post’s GeoDirectory to the chosen services. The results showed how well a service covered an area, for example GP catchments within 5, 10 and 15km of Waterford City.
Method
PostgreSQL with the pgRouting extension can route between points with algorithms such as A* and Dijkstra. I converted the OSI road network from vector data into a routable graph, removing thousands of small laneways, bog trails and fragments that didn’t connect to the main network. I then wrote SQL to translate grid coordinates into network nodes, plus wrappers around the routing functions.
One query between two points took about four seconds. Ireland has about 2.3 million residential addresses, or 1.7 million unique points once apartments in the same building are merged. Routing every one of them to a single destination, one at a time, would take more than 20 days even at one query a second, and real projects had thousands of destinations.
Scaling with Node.js
The routing application takes a CSV of destination grid points and returns, for every address, the road distance to its five closest destinations. It counts the CPUs on the host, splits the destinations between them and forks one worker per CPU. Each worker writes its own CSV and the files are merged at the end, which avoids syncing results back to a parent process.
Two shortcuts cut the work. A cheap straight-line distance query picks the 20 nearest destinations first, so only those get a road-distance query. Each route is then tried inside a bounding box four times the straight-line distance, and rerun without the box if that fails. Workers reported progress and errors to Slack. On a 24-CPU machine with 32GB of RAM, a full analysis of 2,200 destinations finished within 12 hours.
Problems along the way
Every worker shared one PostgreSQL server, which hit its client limit within moments on the default settings. It took a week of tuning to find a configuration that held.
A small number of queries failed. They turned out to be on islands off the west coast, close to the mainland in a straight line but with no road connection. Western headlands caused a similar problem, with the nearest destination by straight line sitting across a bay and the real road route falling outside a small bounding box.
GP catchments
The first project estimated how many people lived within set distances of GP practices in urban areas. That gave each practice a catchment whose demographics could guide the next year’s funding, since practices serving more disadvantaged patients may need more support.
Hospital catchments
A second analysis routed from each small area to every general acute hospital, then assigned each small area to a hospital. Grouping the areas drew a catchment around each hospital for further analysis.
