RogerBW's Blog

Finding Supermarket Fuel 28 April 2018

A reader (hi Dave!) suggested extending the supermarket-finder to locate supermarket fuel near motorway junctions.

The Food Standards Agency files aren't much use, but this can be done entirely with OpenStreetMap data. The first step is to locate junctions, as beforeā€¦ well, actually not as before. I got distracted.

The thing is, the motorway-only data set will fit easily into memory on a modern machine, but larger sets (such as "all roads in the UK") won't. And the Reader mode of LibXML is all very well for parsing files that won't fit, but not terribly convenient. How else do we handle data which don't fit into memory? A database! So I wrote a converter which takes the nodes, ways and relations of OpenStreetMap XML and turns them into SQLite. What used to be:

<node id="212531" lat="51.6024835" lon="-0.6885638" version="11" \
 timestamp="2013-10-18T13:16:23Z" changeset="18419340" uid="24119" user="Mauls">
  <tag k="ref" v="3"/>
  <tag k="name" v="Knaves Beech Interchange"/>
  <tag k="exit_to" v="High Wycombe (E)  A40"/>
  <tag k="highway" v="motorway_junction"/>


CREATE TABLE node (changeset INTEGER, lat NUM, lon NUM, user INTEGER, version INTEGER, timestamp TEXT, uid INTEGER, id INTEGER PRIMARY KEY);
CREATE TABLE node_tag (node_id INTEGER, v TEXT, k TEXT);
INSERT INTO "node" VALUES(18419340,51.6024835,-0.6885638,'Mauls',11,'2013-10-18T13:16:23Z',24119,212531);
INSERT INTO "node_tag" VALUES(212531,'3','ref');
INSERT INTO "node_tag" VALUES(212531,'Knaves Beech Interchange','name');
INSERT INTO "node_tag" VALUES(212531,'High Wycombe (E)  A40','exit_to');
INSERT INTO "node_tag" VALUES(212531,'motorway_junction','highway');

(the order of fields is unimportant, and random). The same thing is done for ways and relations.

This leads to fun with subqueries:

SELECT way_tag.v,node_tag.v,,lat,lon
FROM node_tag
  JOIN node ON
  JOIN way_nd ON
  JOIN way_tag ON way_nd.way_id=way_tag.way_id
WHERE node_id IN (
  SELECT node_id
  FROM node_tag
  WHERE k='highway'
    AND v='motorway_junction'
  AND node_tag.k='ref'
  AND way_tag.k='ref';

So a list of junctions can be generated much as before, though faster. Then it's time for another osmfilter pass:

osmfilter gb.o5m --keep=amenity=fuel >fuel.osm

That "amenity=fuel" tag can be on any of three different sorts of structure: an individual node, a way, or a relation (collection of ways). So for each of those things we do a query to pull out the "name", "brand" and "operator" tags (the use of these is horribly inconsistent), and see if any of them matches the names of the supermarkets that sell fuel in England: Sainsbury's, Tesco, Asda and Morrison's. If so, if that's a node we already have its latitude and longitude; for ways or relations, we do a second query to pull out all latitudes and longitudes of constituent ways and nodes, and average them.

At this point we use the algorithm as before to match motorway junctions with fuel locations; but I keep track of closest points separately for each brand of supermarket (and I set a hard limit of five straight-line miles from the junction, because driving further than that isn't going to produce much of a saving). So a single junction's records may end up as:

-0.7771407,51.6115432,"M40 J4 (0.4) Asda"
-0.754612975,51.63432275,"M40 J4 (1.6) Morrison"

It's limited like the supermarket finder in that it doesn't consider questions of accessibility, or actual driving time between junction and garage. I have the OSRM code, but their preferred way of building it is a Docker image, and that's rather more faff than I want.

As before, here are some Viking visualisations. (Triangles are the calculated junction positions; circles are the garages.)

High Wycombe (Asda near the motorway, Morrisons in town):


(Garages from west to east: Sainsbury's in Calcot, Asda in Tilehurst, two Tesco branches either side of the station, and an Asda in Lower Earley.)

The north-western part of London (with some concentration on the Sainsbury's and Asda that share a roundabout in northern Watford):

All of England:

See also:
Finding Tesco

Add A Comment

Your Name
Your Email
Your Comment

Your submission will be ignored if any field is left blank, but your email address will not be displayed. Comments will be processed through markdown.

Tags 1920s 1930s 1940s 1950s 1960s 1970s 1980s 1990s 2000s 2010s 3d printing action aeronautics aikakirja anecdote animation anime army astronomy audio tech base commerce battletech beer boardgaming bookmonth chain of command children chronicle church of no redeeming virtues cold war comedy computing contemporary cornish smuggler cosmic encounter coup cycling dead of winter doctor who documentary drama driving drone ecchi espionage essen 2015 essen 2016 essen 2017 existential risk falklands war fandom fantasy film firefly first world war flash point food garmin drive gazebo geodata gin gurps gurps 101 harpoon historical history horror hugo 2014 hugo 2015 hugo 2016 hugo 2017 hugo-nebula reread humour in brief avoid instrumented life kickstarter learn to play leaving earth linux mecha museum mystery naval non-fiction one for the brow opera perl photography podcast politics powers prediction privacy project woolsack pyracantha quantum rail ranting raspberry pi reading reading boardgames social real life restaurant reviews romance rpg a day rpgs science fiction scythe second world war security shipwreck simutrans smartphone south atlantic war squaddies stationery steampunk stuarts suburbia superheroes suspense television the resistance thirsty meeples thriller tin soldier torg toys trailers travel vietnam war war wargaming weather wives and sweethearts writing about writing x-wing young adult
Special All book reviews, All film reviews
Produced by aikakirja v0.1