TfL's operations dashboard shows bikes available per borough, refreshed every minute from the same feed this snapshot came from. The current query was written against a borough list that only had the twelve boroughs inside the scheme; the expansion team needs all thirty-three on the board, with zeros where the scheme is not. Rewrite it.
Return one row per borough with the stations, docking points and bikes inside it at the snapshot.
| column | meaning |
|---|---|
borough | the borough's name |
stations | number of docking stations inside it |
docks | sum of docks |
bikes_available | sum of bikes |
geom | the borough polygon, so the result draws on the map |
The tables are london_boroughs and cycle_docks, both SRID 4326 and both with a GiST index on geom. Their columns are below.
A borough with no station is a row with zeros, not a missing row. Order by docks descending, then borough.
london_boroughs — 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 |
borough_id | text | Stable identifier of the form LB-01 |
name | text | Borough name; the City of London is included as one unit |
inner_london | boolean | True for the 12 statutory Inner London boroughs and the City |
geom | geometry(Geometry, 4326) | Polygon |
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.
london_boroughsCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| borough_id | text |
| name | text |
| inner_london | boolean |
| geom | geometry(Geometry,4326) |
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.
The per-area aggregate is the most common spatial query in any operations dashboard, and the outer-join mistake is the most common reason those dashboards under-report.
4 scored, 0 informational
Returns borough, stations, docks, bikes_available
20%One row per borough
40%Uses a spatial predicate for the join
20%Uses an outer join
20%