A rider opens the app at a dock with no bikes. The app is supposed to say 'nearest bike: 130 m, that way'. The current implementation computes the distance from the empty dock to every other dock and sorts; it takes four seconds on the live feed and will not survive the expansion. The ops team wants the same answer as an indexed nearest-neighbour query it can run once a minute.
For every station with no bikes (bikes = 0), return the nearest station that has at least one.
| column | meaning |
|---|---|
empty_dock | the dock_id of the empty station |
nearest_stocked | the dock_id of the closest station with bikes > 0 |
bikes | how many bikes it has |
walk_m | great-circle distance between the two in metres, rounded to the nearest metre |
geom | the empty station's point, so the result draws on the map |
One row per empty station. The table is cycle_docks, SRID 4326 with a GiST index on geom; its columns are below.
The query has to be the index-assisted form: a LATERAL subquery ordered by the KNN operator with LIMIT 1. The brute-force version gives the same rows and is the reason the app is slow.
cycle_docks — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
dock_id | text | Station identifier, BP-1 … BP-878 (gaps where stations were removed) |
name | text | Station name and neighbourhood |
terminal | text | TfL terminal code |
docks | integer | Docking points at the station (docks) |
bikes | integer | Bikes available at the snapshot, all kinds (bikes) |
empty_docks | integer | Free docking points at the snapshot (docks) |
standard_bikes | integer | Pedal bikes available (bikes) |
ebikes | integer | E-bikes available (bikes) |
temporary | boolean | A temporary event station |
snapshot_at | text | When the counts were read |
geom | geometry(Geometry, 4326) | Point |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
cycle_docksCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| dock_id | text |
| name | text |
| terminal | text |
| docks | integer |
| bikes | integer |
| empty_docks | integer |
| standard_bikes | integer |
| ebikes | integer |
| temporary | boolean |
| snapshot_at | text |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
Nearest-available-resource is the query under every 'closest bike / charger / cab' feature, and the LATERAL + KNN form is the only one that survives the fleet growing.
5 scored, 0 informational
Returns empty_dock, nearest_stocked, bikes, walk_m
14%One row per empty station
21%Uses the KNN operator inside a LATERAL
29%No brute-force distance matrix
14%Distance is in metres
21%