The question
On The Gate opens on a map centred on you and pins the football matches nearby,
from the Premier League down to the non-league clubs where you pay cash at the turnstile. Behind that map is one
endpoint, /fixtures/nearby, which takes a latitude, a longitude and a radius (ten miles by default) and returns
upcoming fixtures sorted by distance.
The obvious first version loads every fixture, works out how far away each ground is, and throws away anything too far. That works fine with a few hundred clubs. But the English pyramid has thousands of clubs, each playing dozens of games a season, and the app needs this query on every map load. Doing the maths for every fixture in the country to show you the six nearest is wasted work.
Step one: a cheap box
The trick is to ask the database a dumber question first. Instead of a circle with a ten-mile radius, it asks for a square around you. A square is just two ranges, and databases are very good at ranges:
- Latitude is easy. One degree is about 111 km everywhere, so the box is
radius / 111degrees tall. - Longitude is the catch. Lines of longitude meet at the poles, so a degree of longitude shrinks as you move away
from the equator by a factor of
cos(latitude). In London, at about 51.5°N, a degree of longitude is only about 69 km. Without that correction the box would be too narrow and you’d miss games to the east and west.
In the API that becomes two BETWEEN filters in SQL:
lat_delta = radius_km / 111
lng_delta = radius_km / (111 × max(cos(lat), 0.01))
The max(…, 0.01) is a guard. Near the poles cos(lat) heads towards zero and the box would blow up
to infinity. Nobody is looking for a Step 7 game at the North Pole, but a guard costs nothing.
Step two: the exact circle
The box is deliberately a little generous. Its corners stick out past the circle, so about a fifth of its area (1 − π/4) is ground that’s actually too far away. That’s fine, because the box only has to be a superset of the right answer. Whatever survives it gets the precise treatment: the Haversine formula, which gives the great-circle distance between two points on a sphere. Anything further than the radius is dropped, and the rest is sorted by distance, then by kick-off time.
The same query also leaves out games that have already finished, but keeps any that kicked off in the last two hours. A match in its 70th minute is still worth knowing about if the ground is around the corner. Turning up for the second half on the gate is a time-honoured tradition.
Why not PostGIS?
PostGIS is the right tool for serious geospatial work: proper spatial indexes, real distance queries, polygons and more. But it’s another extension to install, host and keep in mind. At On The Gate’s current size, two numeric columns and a bounding box get almost all of the benefit at none of the cost.
The honest caveat: the box only saves real work once the database can use it efficiently. Right now that means adding
a composite index on (latitude, longitude), which is next on the list. When the dataset outgrows that, or
the app needs more than “within a radius of me”, PostGIS will be worth its keep.
The general lesson
Run a cheap, slightly-wrong filter first, then a precise, expensive check on what’s left. It shows up all over the place: spatial queries, search ranking, even my old physics dissertation, where you cut most of the noise before doing the careful analysis. It’s rarely the clever algorithm that makes something fast. It’s not running the clever algorithm on things that obviously don’t matter.