We’ve been working closely with a marketing agency recently. Cohesive have been helping us discover and tell our company story a lot better than we have been to date. Expect big things in the near future.
Andy Williams is our main man and I share a common love of cycling with him. As a result I follow @andy_tyfelin on twitter to see what he is up to. Last week he tweeted me off the sofa with this challenge:-
Firstly, @headwaycardiff is a great cause so well done Andy for getting off your arse and trying to raise some cash.
But secondly, the challenge, “Can’t think of any +500m UK hills you can summit via 3 unique climbs?”. That got me thinking. I’ve ridden my bike up shed loads of UK hills but suffer from a fading memory. I’m sure there’s more than just the Bwlch? So seeing as it’s Friday let’s have a little hack with some GIS tools and see what we can discover. Non-techies may want to scroll down to the bottom!
Firstly we need to find roads that go over 500m high. I decided to use Ordnance Survey’s Openroads data set to do this. It comes with two types of features:-
- road line strings
- road nodes that join these linestrings
A quick way to find these high roads is to filter out all nodes that are higher than 500m high. Sadly Ordnance Survey do not attach height data to these nodes so I’ll have to do it myself. This data is freely available at http://dwtkns.com/srtm/ so I downloaded the UK tiles required and loaded it into a local PostgreSQL database:-
raster2pgsql -t 10x10 -a -C *.tif data_dem.srtm_50_dem | psql -d nautoguide
It’s worth noting that I split it up into tiles of size 10x10, trust me that this speeds up queries later on which are going to use a spatial index I created.
CREATE INDEX srtm_dem_50_st_convexhull_idx ON data_dem.srtm_50_dem USING gist (st_convexhull(rast))
That index allows me to do super speedy searches through this height data using a point as a reference.
Hopefully, you are still with me as now I’m going to filter out my nodes by height:-
SELECT * INTO ng_research.high_nodes FROM mapping_os_opendata_openroads.roadnodes OS_OR INNER JOIN data_dem.srtm_50_dem DEM ON ST_Intersects(rast, ST_TRANSFORM(OS_OR.wkb_geometry, 4326)) WHERE ST_VALUE(rast,ST_TRANSFORM(OS_OR.wkb_geometry, 4326)) > 500
This creates me a nice little table of road nodes that are all higher than 500 metres. It does it by looking for the height raster that contains our node and picking the height data from it. Now you can see why I picked 10x10 as a tile size as my spatial index will select the right tile quickly, and PostgreSQL can get the value quickly from the small tile.
Onwards! We need to answer the next bit of the challenge: “via three unique climbs”. Hmmmm a bit trickier. Now, seeing as it’s Friday am and all of our customers send in support requests in the afternoon (why?) I did not have much time, so decided upon a slightly hacky approach.
The road nodes tell me where road lines intersect. So let’s find all road nodes from our selected list that have 3 or more intersections. This should give us a good indication of high roads with 3 paths to the top.
Hold onto your hat as things are going to get a little more complicated:-
SELECT * FROM( SELECT distinct on (HN.identifier) HN.identifier as node, ST_TRANSFORM(HN.wkb_geometry, 27700) as node_geom, count(R.ogc_fid) OVER (partition by HN.identifier) as node_count, array_agg(R.ogc_fid) OVER (partition by HN.identifier) as roads FROM ng_research.high_nodes HN INNER JOIN mapping_os_opendata_openroads.roads R ON HN.identifier = R.startnode OR HN.identifier = R.endnode ) FOO WHERE node_count >= 3 ) SELECT node, node_geom, ST_UNION(wkb_geometry) OVER (partition by node) as road_geom INTO ng_research.high_roads FROM ROAD_COUNT INNER JOIN mapping_os_opendata_openroads.roads ON ogc_fid = ANY(roads)
We’re doing quite a lot in this little query:-
firstly in the ROAD_COUNT section we’re connecting our high_nodes to the road lines using the node identifier. Ordnance Survey have linked the start and end of each road line to a specific node. Our OR clause “HN.identifier = R.startnode OR HN.identifier = R.endnode” finds all the road lines for each node.
Next we group them by node with distinct on(HN.identifier) and use a window function to count the number of road lines and find the id (ogc_fid) for each one. array_agg gives me an array of these road line ids that I’ll use later to display on the map
Now we have our list of high road lines and we need to group them together on the map. This is done in the final part of the query by pulling the road line ids out of the array, joining them to the roads table and grouping the geometries together using ST_UNION
Hooray! We have our list of roads sat in the table ng_research.high_roads let’s fire up QGIS and have a look. Firstly, we’ll nip over to Wales and check that we’ve found the Bwlch which is Andy’s challenge climb:-
Phew! There it is. I’ve marked the route I’ve ridden before in red and we can see the blue lines of my high roads. Interestingly we can also see some other potential routes going up to 500m.
Zooming out to the whole of the UK we get this:-
Andy may have to eat his words as it looks like there are plenty of opportunities outside of Wales. But on closer inspection it seems that my algorithm may need a little more work:-
In the picture above we can see the Bealach Na Ba probably the most iconic road climb in the UK. But my algorithm has been tricked by a tiny little side road near the top. If I had more time I’d recurse further down my connected roads to ensure that there’s enough tarmac to satisfy the unique climb criteria of Andy’s challenge.
But I also found the segment as shown above. This is in the Yorkshire Dales and looks to me like the classic climb of Fleet Moss. It appears to fit the criteria of Andy’s challenge and I now throw the gauntlet back down at his feet (wheels) to go and find out.
If you’ve read this far you’ll agree with me that GIS is a powerful tool for settling an argument or making an informed decision. I didn’t reply to Andy but I’m going to now as I have the data and evidence in front of me.
We do this a lot in the office for our customers and are always keen to have new challenges thrown our way, if you’ve got one just give me a shout.