As a young lad I lived in Padstow and went to school a stone's throw from the sea. I'm sure that you can imagine that these were fun filled formative days with evenings spent exploring beaches and messing about in sand dunes. My blissful existance was rudly interupted by my father's career as an R.A.F pilot. He came home one night and told us we'd been posted, R.A.F speak for "you're going to have to move". I asked "where?" and Dad got out a map, he then pointed to Swindon about as far from the sea as you can possibly be.
Consequently the sea has nagged away at me all my adult life and in a few weeks time I'll be moving back to it as I've done the exact opposite to my family and transfered them down to Brixham. A change of address form triggered that early memory of Swindon being so far from the coastline and it got me thinking.
Who really does live the furthest away from the sea?
So I broke out the GIS tools and decided to find out. Strap yourself in as we're about to dive straight into the endless possibilities of open data and open source.
First we need to define what we mean by distance from the sea. I decided that it needs to represent a car journey, where you can get out after a long drive and smell the ozone in the air. This requires a list of road end points right next to the seaside. For that we'll need some open data from Ordnance Survey.
The Openmap Local Tidal Water Line data set is a good fit for the coastline of the UK. If I then take the Openroads Road Node data set of road points I can come up with the closest set of road points to the sea. I loaded them into PostgreSQL and ran a reasonably simple query:-
SELECT ogc_fid,wkb_geometry INTO ng_research.seanodes FROM mapping_os_opendata_openroads.roadnodes RN WHERE EXISTS ( SELECT 1 FROM mapping_os_opendata_openmap.tidalboundary TB WHERE ST_DWITHIN(RN.wkb_geometry, TB.wkb_geometry, 50) )
This gets me every road node that is within 50 metres of a tidalboundary and seemed to do a pretty good job as you can see below. I will admit to editing out some points along estuaries, but it's a pretty good first stab.
Next on to the addresses. I decided to do my analysis at postcode level. There are 1.68 million of them in the UK and computing distances between each one and the sea nodes would take quite a lot of time. So let's eliminate a shed load of them by buffering out our road nodes to 80 kilometres. This can be done dead easily in QGIS using the buffer process followed by a dissolve to give me this:-
I end up with a small area that is greater than 50km from any road point near the tidal zones. This gave me 3907 postcodes, a much easier number to manage.
Now let's find out which postcodes this area represents:-
SELECT * INTO ng_research.distant_postcode FROM data_os_opendata.opennames OPN WHERE local_type='Postcode' AND NOT EXISTS ( SELECT 1 FROM ng_research.seanodes_buffer SN WHERE ST_INTERSECTS(SN.wkb_geometry,OPN.wkb_geometry))
That query gives me every point in the Ordnance Survey Opennames data set that is a postcode which does not intersect with my buffer region.
The next bit gets harder as I have to compute my drive times from these postcodes to the sea. Luckily I am a big fan of OSRM the open source routing engine. It is super fast and will compute driving routes for me in milliseconds. I've written an interface to it from PostgreSQL, I won't bore you with the code (ask if you want it) but it does the following:-
- takes a postcode geometry
- finds the five nearest coastal road points (as the crow flys)
- computes the driving routes to each
- picks the shortest
(The reasoning behind "five" is that the closest crow flys point may not necessarily be the closest driving route.) Running my algorithm took 10 minutes on a smallish PC and I ended up with a table of routes and distances to the sea.
And there we have it, our poor old West Midlanders are either heading to Birkenhead or Kings Lynn to get a glimpse of the sea.
Ready for the big reveal? The longest distance is from the postcode CV9 2JT a whopping 104 miles car journey to the seaside. This happens to be a farm in Atherstone and the resident has to drive all the way to Kings Lynn. On arrival the view is pretty grim
Hopefully this few hours of GIS tinkering has provided some mild entertainment for you whilst showcasing the potential for location based analysis. It's a passion of ours at Nautoguide unlocked by the fantastic open data and open source ecosystem we have in the UK.
***** STOP PRESS *****
Walking home last night I had a hunch that my approach may not be foolproof in using fixed distances from the coast to limit postcodes. So I reduced the buffer size by a considerable amount and left the algorithm running all night.
I found that the unfortunate residents of Cookley in Kidderminster have to travel over 120 miles to reach the Welsh coast. I think I need to leave it running for the whole of the UK with no restrictions to be sure that I really have found the most coast-o-phobic residence.