Learn how to load and analyze Strava runs with PostgreSQL JSON database by creating a JSON-ready PostgreSQL table, importing Strava API, and querying Strava.
In my last post, I showed how to map Strava runs using data that I’ d extracted from their /activities API, but the API returns a lot of other data that I discarded because I wasn’ t sure what I should keep.
The API returns a nested JSON structure, so the easiest solution would be to save each run as an individual file. But I’ ve always wanted to try out PostgreSQL’s JSON data type and this seemed like a good opportunity.
First up, we need to create a database in which we’ ll store our Strava data. Let’s name it appropriately:
Now, we can now create a table with one field with the JSON data type:
Easy enough. Now we’ re ready to populate the table.
We can partially reuse the script from the last post except rather than saving to CSV file we’ ll save to PostgreSQL using the psycopg2 library.
The script relies on a TOKEN environment variable. If you want to try this on your own Strava account you’ ll need to create an application, which will give you a key.
We can now write some queries against our newly imported data.
That’s all for now. Next, I’ m going to learn how to query segments, which are stored inside a nested array inside the JSON document. Stay tuned for that in a future post.
