RogerBW's Blog

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"/>
</node>

becomes:

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,node.id,lat,lon
FROM node_tag
  JOIN node ON node_tag.node_id=node.id
  JOIN way_nd ON node.id=way_nd.ref
  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):

Reading:

(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: