Skip to content

Instantly share code, notes, and snippets.

@mbforr
Last active Jul 29, 2021
Embed
What would you like to do?
SELECT
start_station_id,
start_station_name,
start_station_latitude,
start_station_longitude,
# calculate the average distance for all trips from each start station
AVG(st_distance(st_geogpoint(start_station_latitude,
start_station_longitude),
st_geogpoint(end_station_latitude,
end_station_longitude)))
FROM
`bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
starttime BETWEEN '2017-06-01T00:00:00'
AND '2017-06-30T00:00:00'
GROUP BY
start_station_id,
start_station_name,
start_station_longitude,
start_station_latitude
ORDER BY
AVG(st_distance(st_geogpoint(start_station_latitude,
start_station_longitude),
st_geogpoint(end_station_latitude,
end_station_longitude))) DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment