This is a simple set of scripts that allow you to load all of your Strava activities into a Postgres database.
gomes=> select sport_type, count(*) from activities group by sport_type order by count desc;
sport_type | count
----------------+-------
Run | 711
Ride | 154
VirtualRide | 126
Swim | 91
WeightTraining | 65
Workout | 30
Walk | 29
AlpineSki | 19
Elliptical | 8
Hike | 7
Yoga | 1
Kayaking | 1
StairStepper | 1
Windsurf | 1
Tennis | 1
Rowing | 1
Surfing | 1
(17 rows)
gomes=> \d activities
Table "public.activities"
Column | Type | Collation | Nullable | Default
-------------------------------+------------------+-----------+----------+---------
resource_state | double precision | | |
athlete.id | double precision | | |
athlete.resource_state | double precision | | |
name | text | | |
distance | double precision | | |
moving_time | double precision | | |
elapsed_time | double precision | | |
total_elevation_gain | double precision | | |
type | text | | |
sport_type | text | | |
workout_type | double precision | | |
id | double precision | | not null |
start_date | text | | |
start_date_local | text | | |
timezone | text | | |
utc_offset | double precision | | |
location_city | text | | |
location_state | text | | |
location_country | text | | |
achievement_count | double precision | | |
kudos_count | double precision | | |
comment_count | double precision | | |
athlete_count | double precision | | |
photo_count | double precision | | |
map.id | text | | |
map.summary_polyline | text | | |
map.resource_state | double precision | | |
trainer | boolean | | |
commute | boolean | | |
manual | boolean | | |
private | boolean | | |
visibility | text | | |
flagged | boolean | | |
gear_id | text | | |
start_latlng | json | | |
end_latlng | json | | |
average_speed | double precision | | |
max_speed | double precision | | |
average_cadence | double precision | | |
average_temp | double precision | | |
has_heartrate | boolean | | |
average_heartrate | double precision | | |
max_heartrate | double precision | | |
heartrate_opt_out | boolean | | |
display_hide_heartrate_option | boolean | | |
elev_high | double precision | | |
elev_low | double precision | | |
upload_id | double precision | | |
upload_id_str | text | | |
external_id | text | | |
from_accepted_tag | boolean | | |
pr_count | double precision | | |
total_photo_count | double precision | | |
has_kudoed | boolean | | |
Indexes:
"activities_pkey" PRIMARY KEY, btree (id)- Register a Strava app through https://www.strava.com/settings/api.
- Take note of the Client ID and Client Secret.
- Clone this repository.
- Install dependencies with
bun install. - Set up the
STRAVA_CLIENT_IDandSTRAVA_CLIENT_SECRETenvironment variables (e.g., using a.envrcfile). - Run
bun get-access-token.tsand follow the instructions. Make sure to copy the access token this script outputs at the end:
$ bun get-access-token.ts
Go to URL https://www.strava.com/oauth/authorize?client_id=123456&redirect_uri=http%3A%2F%2Flocalhost%2Fexchange_token&response_type=code&scope=activity%3Awrite%2Cactivity%3Aread%2Cactivity%3Aread_all and authorize application
Once you have authorized, you will be redirected to a 'localhost' address (don't worry if you see a 'This site can’t be reached' message)
✔ Copy the whole URL of the page from the browser and paste it here : … http://localhost/exchange_token?state=&code=3ef024236b8c48891a23d318b9256fdf571210e8&scope=read,activity:write,activity:read,activity:read_all
Successfully retrieved access token: f6a7d88c02a3429fb62cc9f97fc54fb1cc868912- Spin up a Postgres instance and apply the
./schema.sqlfile in this repository. This file does not create a database. - Set up a
PG_CONNECTION_STRINGenvironment variable that looks likepostgres://[user]:[password]@[hostname][:port]/[dbname] - Run
bun load-activities.ts <access-token>
(If you've made edits to existing activities and want to force them to get updated, you can run bun load-activities.ts --update-existing <access-token>)
- Connect to your Postgres instance, and run
select * from <database_name>.activities. - Additionally, if you would also like to add outdoor temperature data to your activities, you can run two additional scripts:
- Run
bun add-outdoor-temp-col.tsin order to add an additional column to the activities table; - Run
bun update-outdoor-temp.ts- This script retrieves outdoor temperature data for your Strava activities based on their time and location by using Open-Meteo, a free (for non-commercial purposes) open-source weather API. It then updates your activities table with this additional information.
With this additional data, we can, for example, identify the activities that occurred at the highest and lowest temperatures.
ls=> SELECT name, distance, outdoor_temp FROM activities WHERE outdoor_temp IS NOT NULL ORDER BY outdoor_temp DESC;
name | distance | outdoor_temp
----------------+----------+-------------
Morning Run | 8310.8 | 34.25
Morning Run | 6016.2 | 33.2
Evening Run | 5017.7 | 33
Morning Run | 2557.2 | 32.4
Morning Run | 12687.5 | 31.8
Evening Run | 7013.9 | 31.5
Lunch Run | 8523.6 | 31
Evening Run | 7511.6 | 30.8
Afternoon Run | 7502.8 | 30.7
Morning Run | 24019.1 | 30.55
...
ls=> SELECT name, distance, outdoor_temp FROM activities WHERE outdoor_temp IS NOT NULL ORDER BY outdoor_temp;
name | distance | outdoor_temp
----------------+----------+-------------
Evening Run | 4446.9 | -5.9
Morning Run | 3214.5 | -5.1
Morning Run | 11021.3 | -5
Morning Run | 17010.9 | -4.4
Evening Run | 5087.4 | -4.1
Morning Run | 6015.4 | -2
Evening Run | 8379.6 | -1.8
Evening Run | 4604.9 | -1.5
Morning Run | 5067.7 | -1.2
Morning Run | 5041.2 | -1
...I have ~1250 activities on my Strava profile. This is how long it takes to update activities:
$ time bun load-activities.ts <access-token>
Page 1 (200 activities found)
Page 2 (200 activities found)
Page 3 (200 activities found)
Page 4 (200 activities found)
Page 5 (200 activities found)
Page 6 (200 activities found)
Page 7 (47 activities found)
bun load-activities.ts 518ccfd5859027a07577302ee8729a7fde462517 146.06s user 54.10s system 84% cpu 3:57.22 totalThat's ~4 minutes. And this is how long it will take to load activities for the first time:
$ time bun load-activities.ts <access-token>
Page 1 (200 activities found)
[Activity 1] Inserting brand new activity.
[Activity 2] Inserting brand new activity.
[Activity 3] Inserting brand new activity.
[Activity 4] Inserting brand new activity.
[Activity 5] Inserting brand new activity.
...
Page 7 (47 activities found)
...
[Activity 1246] Inserting brand new activity.
[Activity 1247] Inserting brand new activity.
bun load-activities.ts 518ccfd5859027a07577302ee8729a7fde462517 263.20s user 94.43s system 90% cpu 6:33.19 totalThat's 6min30secs.
- This script could be made much faster by accepting a "Last Sync At" parameter that makes it search the Strava API for activities that have only occurred since a certain date.
- We could merge both the
get-access-token.tsand theload-activities.tsscripts for ease of use. - This project could be further automated by having a script that automatically starts Postgres in a local Docker container and loads data there.
- More data besides activities could be collected.
- We should have more examples in this README of what could be done with Strava data in Postgres
- This script could allow custom sleeps so it doesn't reach Strava's API limits for users with more data.