-- These queries are here just for reference.
-- They were used to extract the data from Google BigQuery.
-- The tables with these data can be found there in the
-- public datasets database.

-- flows
SELECT
start_station_id as origin,
end_station_id as dest,
COUNT(*) as count
FROM
`bigquery-public-data.london_bicycles.cycle_hire`
WHERE
EXTRACT(year from start_date)=2017 AND
EXISTS(
SELECT id
FROM `bigquery-public-data.london_bicycles.cycle_stations`
WHERE id = start_station_id
)
AND EXISTS(
SELECT id
FROM `bigquery-public-data.london_bicycles.cycle_stations`
WHERE id = end_station_id
)
GROUP BY
start_station_id, end_station_id
ORDER BY count DESC
LIMIT 10000

-- locations
SELECT
id, name, latitude as lat, longitude as lon
FROM `bigquery-public-data.london_bicycles.cycle_stations`
ORDER BY id