Generating London Locality Data

Using the TfL Santander public bicycle hire scheme as a proxy for active travel presents several problems. There is, for example, uncertainty as to how representative public bicycle hire use might be of more general trends in active travel. Others relate to how best to process bicycle docking station data to produce meaningful measures of activity.

TfL release live data on the status of approximately 800 docking stations around London. Each station belongs to a ‘village’ describing its local neighbourhood (click on station to see others in the same village; double-click to see all stations):

Groupings vary considerably in size and there is some inconsistency in geographic placement and in naming, so to reduce the number of localities, the following changes were made:

Naming Consistency & Abbreviation

Old name New name
Kings Cross King’s Cross
Parsons Green Parson’s Green
Queen Elizabeth Olympic Park Olympic Park
St. James’s St James’s
St. John’s Wood St John’s Wood
St.John’s Wood St John’s Wood
St Lukes St Luke’s
St. Luke’s St Luke’s
St. Paul’s St Paul’s
The Borough Borough
The Regent’s Park Regent’s Park

Aggregation

id Station Old village New locality
5 Sedding Street Sloane Square Knightsbridge
8 Maida Vale Maida Vale St John’s Wood
25 Doric Way Somers Town Euston
27 Bouverie Street Temple Holborn
47 Warwick Avenue Station Maida Vale St John’s Wood
79 Arundel Street Temple Strand
80 Webber Street Southwark Elephant & Castle
104 Crosswall Tower Aldgate
107 Finsbury Leisure Centre St Luke’s Finsbury
116 Little Argyll Street West End Soho
120 The Guildhall Guildhall Bank
124 Eaton Square Belgravia Knightsbridge
127 Wood Street Guildhall Bank
130 Tower Gardens Tower Aldgate
140 Finsbury Square Moorgate Liverpool Street
149 Kennington Road Post Office Oval Kennington
160 Waterloo Place St James’s West End
181 Belgrave Square Belgravia Knightsbridge
183 Riverlight North Nine Elms Battersea Park
199 Great Tower Street Monument Bank
203 West Smithfield Rotunda Farringdon Barbican
207 Grosvenor Crescent Belgravia Knightsbridge
215 Moorfields Moorgate Barbican
224 Queensway Kensington Gardens Hyde Park
228 St. James’s Square St James’s West End
247 St. John’s Wood Church Regent’s Park St John’s Wood
255 Clifton Road Maida Vale St John’s Wood
259 Bourne Street Belgravia Pimlico
267 Regency Street Westminster Pimlico
276 Lower Thames Street Monument Bank
298 Curlew Street Shad Thames Bermondsey
302 Putney Pier Wandsworth Putney
307 Black Lion Gate Kensington Gardens Hyde Park
331 Bunhill Row Moorgate Barbican
350 Queen’s Gate Kensington Gardens Hyde Park
393 Snow Hill Farringdon Holborn
404 Palace Gate Kensington Gardens Hyde Park
423 Eaton Square (South) Belgravia Knightsbridge
440 Kennington Oval Oval Kennington
452 St. Katharine’s Way Tower Wapping
459 Gunmakers Lane Old Ford Victoria Park
467 Southern Grove Bow Mile End
487 Canton Street Poplar Limehouse
509 Fore Street Guildhall Bank
521 Driffield Road Old Ford Victoria Park
527 Hansard Mews Holland Park Shepherd’s Bush
559 Abbotsbury Road Holland Park Kensington
566 Westfield Ariel Way White City Avondale
587 Monument Street Monument Bank
601 BBC White City White City Avondale
606 Addison Road Holland Park Ladbroke Grove
611 Princedale Road Holland Park Ladbroke Grove
612 Wandsworth Rd Isley Court Wandsworth Road
631 Battersea Park Road Nine Elms Battersea Park
634 Brook Green South Brook Green Hammersmith
637 Spencer Park Wandsworth Common Clapham
648 Peterborough Road Sands End Parson’s Green
650 St. Mark’s Road North Kensington Portobello
653 Simpson Street Clapham Battersea
654 Ashmole Estate Oval Kennington
673 Hibbert Street Battersea Wandsworth
675 Usk Road Clapham Junction Wandsworth
689 Spanish Road Clapham Junction Wandsworth
704 Mexfield Road East Putney Wandsworth
706 Snowsfields London Bridge Bermondsey
707 Barons Court Station West Kensington Hammersmith
718 Ada Street Hackney Central Haggerston
719 Victoria Park Road Hackney Central Victoria Park
728 Putney Bridge Road East Putney Putney
732 Duke Street Hill London Bridge Borough
739 Hortensia Road West Brompton West Chelsea
748 Hertford Road De Beauvoir Town Haggerston
742 Blenheim Crescent Ladbroke Grove Portobello
757 Harcourt Terrace West Brompton Earl’s Court
768 Clapham Common North Side Clapham Common Clapham
769 Sandilands Road Walham Green Sands End
773 Tallis Street Temple St Paul’s
775 Little Brook Green Brook Green Hammersmith
783 Monier Road Hackney Wick Olympic Park
790 Stratford Station Olympic Park Olympic Park
793 Cromer Street Bloomsbury Clerkenwell
797 Ossulston Street Somers Town Euston
807 Bevington Road West North Kensington Portobello
817 Riverlight South Nine Elms Battersea Park
838 Fore Street Avenue Guildhall Bank

This reduces the number of localities to 84:

The table of modified station localities is stored in an SQLite database:

CREATE TABLE stations(id INTEGER NOT NULL, name TEXT, village TEXT, lon REAL, lat REAL);
.mode csv
.import tflBicycleStationsWithLocalities.csv stations

From these we can create a set of locality centroids that we use as the basis of activity mapping, where each locality is associated with the bicycle activity of all the docking stations within it (noting the names station_id and station_name are used for consistency with other city data even though each is a collection of docking stations):

CREATE TABLE villages AS
SELECT village AS station_id, village AS station_name, AVG(lat) AS lat, AVG(lon) AS lon
FROM stations
GROUP BY village;

Generating Locality Activity Values with SQLite

Bicycle activity is generated from the TfL feed that indicates the status of each docking station. We log this feed at 10 minute intervals storing the data in an SQL table:

CREATE TABLE usage(
  stationId INTEGER NOT NULL,
  availableBikes INTEGER,
  availableDocks INTEGER,
  installed INTEGER,
  locked INTEGER,
  temporary INTEGER,
  t TEXT);

To update with more recent data, ensure the most recent data are at the end of a day and then update from the following day onwards. For example

DELETE FROM usage WHERE t > '2020-05-05';

Download the current month from remote database, and add to usage:

.mode csv
.import myUpdatedData.csv usage

Join locality to usage

Because docking stations are referenced only by their id, we need to join the locality to each reading. We also create a new day_of_year column for temporal aggregation by truncating the timestamp of each reading:

CREATE TABLE usage2 AS
  SELECT stationId, village, availableBikes, t, substr(t,0,11) AS day_of_year
  FROM usage
  LEFT JOIN stations ON stationId = id;

Calculate activity index

For each station: accumulate the number of times number of docked bikes changes in consecutive readings on a given day. Can use the SQLite window operations and then sum with an aggregate function.

Additionally we group the results by village and day of year and create an id that combines village name with day of week.

Note also that to be consistent with other datasets, we rename ‘village’ as ‘station’ even though it is an aggregation of several stations in a locality.

CREATE TABLE station_daily_time_series AS
  SELECT
    day_of_year AS date,
    village AS station,
    village||'_'||strftime('%w',day_of_year) AS id,
    SUM(activity) AS count
  FROM
    ( SELECT
        stationID,
        village,
        day_of_year,
        abs(first_value(availableBikes) OVER win - last_value(availableBikes) OVER win) AS activity
      FROM usage2 WINDOW win AS
        (PARTITION BY stationId ORDER BY t ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    )
  GROUP BY village, day_of_year
  ORDER BY day_of_year,village;

Calculate benchmark data

To calculate expected activity for each locality we take the mean activity for the period Monday 6th January to Sunday 1st March grouped by day of week:

CREATE TABLE station_reference_bicycle AS
  SELECT id,AVG(count) AS value
  FROM station_daily_time_series
  WHERE date > '2020-01-06' AND date < '2020-03-02'
  GROUP BY id;

And then export:

.headers on
.mode csv
.output StationReference-Bicycle.csv
SELECT id,count FROM station_reference_bicycle ORDER BY id;
.quit

Export station daily time series

.headers on
.mode csv
.output StationDailyTimeSeries-Bicycle.csv
SELECT date,station,id,count FROM station_daily_time_series ORDER BY date,id;
.quit

Hourly Usage

We can perform a similar process of comparing temporally adjacent docking counts for deriving hourly usage, except this time don’t need to export an id to compare with reference data:

CREATE TABLE usageHourly AS
  SELECT stationId, village, availableBikes, t, substr(t,0,14) AS hour_of_year
  FROM usage
  LEFT JOIN stations ON stationId = id;
CREATE TABLE hourly_time_series AS
  SELECT
    hour_of_year AS date,
    village AS station,
    SUM (activity) AS count
  FROM
    ( SELECT
        stationID,
        village,
        hour_of_year,
        abs(first_value(availableBikes) OVER win - last_value(availableBikes) OVER win) AS activity
      FROM usageHourly WINDOW win AS
        (PARTITION BY stationId ORDER BY t ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    )
  GROUP BY village, hour_of_year
  ORDER BY hour_of_year,village;

To derive the average hourly activity across the whole scheme:

SELECT date, AVG(count) AS count
FROM hourly_time_series
GROUP BY date;

Visualizations

Differences from expectation

We can now use the generated files in the visualization specification just as we would for more direct sensor measurements.

Geographic Patterns

Drag slider to see change over time.