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

Comments on this post are now closed. If you have particular grounds for adding a late comment, comment on a more recent post quoting the URL of this one.

Tags 1920s 1930s 1940s 1950s 1960s 1970s 1980s 1990s 2000s 2010s 3d printing action advent of code aeronautics aikakirja anecdote animation anime army astronomy audio audio tech aviation base commerce battletech beer boardgaming book of the week bookmonth chain of command children chris chronicle church of no redeeming virtues cold war comedy computing contemporary cornish smuggler cosmic encounter coup covid-19 crime crystal cthulhu eternal cycling dead of winter doctor who documentary drama driving drone ecchi economics en garde espionage essen 2015 essen 2016 essen 2017 essen 2018 essen 2019 essen 2022 essen 2023 existential risk falklands war fandom fanfic fantasy feminism film firefly first world war flash point flight simulation food garmin drive gazebo genesys geocaching geodata gin gkp gurps gurps 101 gus harpoon historical history horror hugo 2014 hugo 2015 hugo 2016 hugo 2017 hugo 2018 hugo 2019 hugo 2020 hugo 2021 hugo 2022 hugo 2023 hugo 2024 hugo-nebula reread in brief avoid instrumented life javascript julian simpson julie enfield kickstarter kotlin learn to play leaving earth linux liquor lovecraftiana lua mecha men with beards mpd museum music mystery naval noir non-fiction one for the brow opera parody paul temple perl perl weekly challenge photography podcast politics postscript powers prediction privacy project woolsack pyracantha python quantum rail raku ranting raspberry pi reading reading boardgames social real life restaurant reviews romance rpg a day rpgs ruby rust scala science fiction scythe second world war security shipwreck simutrans smartphone south atlantic war squaddies stationery steampunk stuarts suburbia superheroes suspense television the resistance the weekly challenge thirsty meeples thriller tin soldier torg toys trailers travel type 26 type 31 type 45 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