Created
November 12, 2025 13:22
-
-
Save amandasaurus/ef012e3b68113e4eae0479747a6600f2 to your computer and use it in GitHub Desktop.
2025-11-12 English towns without a Wetherspoons
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {{data:sql,server=https://postpass.geofabrik.de/api/0.2/}} | |
| -- Source was a fedi post: “I dare you to find a town in England without a Wetherspoons” <https://cupoftea.social/@moof/115521240053010548> | |
| -- “Is location A inside “city B”?” is hard to answer for England. People don't | |
| -- Simplier rule: An OSM place location which is >20 km from the nearest Wetherspoons. | |
| -- This is written for Postpass <https://postpass.geofabrik.de/>, and takes about | |
| -- As of 2025-11-12 there are 62 towns or cities with no Wetherspoons in England. | |
| -- The output is a list of lines, from the town/city to the nearest 'Spoons. | |
| -- See below for changing that geo output. | |
| -- Here starts the code! We use a big ol' postgresql CTE. | |
| with | |
| -- First we get the boundary of England | |
| england AS materialized ( | |
| SELECT geom as eng_geom FROM postpass_polygon | |
| WHERE tags->>'boundary' = 'administrative' AND tags->> 'name' = 'England' | |
| ), | |
| -- Get all the Wetherspoons in England | |
| spoons AS materialized ( | |
| SELECT tags, geom | |
| FROM | |
| postpass_pointlinepolygon | |
| join england ON (geom && eng_geom) | |
| where | |
| tags->>'amenity' = 'pub' | |
| AND ( | |
| -- There are a few ways to tag Wetherspoons. | |
| -- This will miss those called `name=JD Weatherspoons` etc | |
| tags->>'name' = 'Wetherspoon' OR tags->>'brand' = 'Wetherspoon' OR tags->>'brand:wikidata' = 'Q6109362' | |
| ) | |
| AND ST_Intersects(eng_geom, geom) | |
| ), | |
| -- Now get all the towns & cities in England | |
| places AS materialized ( | |
| select tags, geom | |
| from | |
| postpass_point join england ON (geom && eng_geom) | |
| where | |
| tags->>'place' IN ('city', 'town') | |
| AND ST_Intersects(eng_geom, geom) | |
| ), | |
| -- This is is where the main geo processing works. | |
| -- For each town/city, we calculate the nearest wetherspoons, and annote each town/city with that info. | |
| places_spoons AS materialized ( | |
| select | |
| dist_m, | |
| places.tags as place_tags, | |
| places.geom as place_geom, | |
| spoons.tags as spoon_tags, | |
| spoons.geom as spoon_geom | |
| from | |
| places cross | |
| join lateral ( | |
| select *, ST_Distance(places.geom::geography, spoons.geom::geography) as dist_m | |
| from spoons | |
| order by places.geom <-> spoons.geom limit 1 | |
| ) as spoons | |
| ) | |
| SELECT | |
| place_tags->>'name' AS place_name, | |
| place_tags->>'population' AS place_pop, | |
| ROUND(dist_m/1000) AS dist_km, | |
| spoon_tags->>'name' AS nearest_wetherspoons_name, | |
| spoon_tags::text AS nearest_wetherspoons_tags, | |
| -- Create a line from the place to the wetherspoons | |
| ST_MakeLine(place_geom, ST_Centroid(spoon_geom)) AS geom | |
| -- Use this instead to just have the place point | |
| --place_geom AS geom | |
| FROM | |
| places_spoons | |
| WHERE dist_m > 20000 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment