Integrate strava with PostgreSQL

strava

Strava is a social application that allows its users to create their profile, track their activities and share it with other users. Users can also view the activity of other users. Strava is used by a lot of runners, cyclists and other sports athletes.

PostgreSQL

PostgreSQL is an open source object relational database system (ORDBMS. It’s also known as Postgres. PostgreSQL has a number of advanced features which make it different from MySQL. PostgreSQL supports various types of data types, data storage and data manipulation languages. The users can change their database schema without having to rewrite any code or restart the server. This software is available for many platforms like Windows, Linux, Mac etc.

Integration of strava and PostgreSQL

It would be very interesting to have strava data in our PostgreSQL database. We will use the pg_extras extension to connect to our MySQL database and retrieve the data from there. We also need to load all the related tables. We will use the fplowing command to load all the tables related to our database:

SELECT * FROM pg_tables;

Now we need to add the stv_athlete table to our database so that we can store the athlete information. We will execute the fplowing query:

CREATE TABLE IF NOT EXISTS stv_athlete( id int8 NOT NULL PRIMARY KEY, lastname varchar(20. NOT NULL );

Let us also add the feat_activity table which will hpd the activity information. We will execute the fplowing query:

CREATE TABLE IF NOT EXISTS feat_activity( id int8 NOT NULL PRIMARY KEY, name varchar(50. NOT NULL, fid int8 NOT NULL REFERENCES stv_feats(id), time datetime NOT NULL, duration float8 NOT NULL DEFAULT '0', notes text, place int8 NOT NULL REFERENCES stv_places(id), grade int4 NOT NULL DEFAULT '1', segment_duration float8 NOT NULL DEFAULT '0', segment_distance float8 NOT NULL DEFAULT '0', segment_elevation float8 NOT NULL DEFAULT '0', segment_gain float8 NOT NULL DEFAULT '0', segment_lapse float8 NOT NULL DEFAULT '0', segment_speed float8 NOT NULL DEFAULT '0', segment_temp float8 NOT NULL DEFAULT '0', segment_time float8 NOT NULL DEFAULT '0' );

Let us also add the feat_status table which will hpd the status information. We will execute the fplowing query:

CREATE TABLE IF NOT EXISTS feat_status( id int8 NOT NULL PRIMARY KEY, name varchar(50. NOT NULL, fid int8 NOT NULL REFERENCES stv_feats(id), time datetime NOT NULL, note text );

Let us also add the strava_activities table which will hpd all our activities. We will execute the fplowing query:

CREATE TABLE IF NOT EXISTS strava_activities( id int8 NOT NULL PRIMARY KEY, name varchar(255. NOT NULL, date date NOT NULL, time datetime NOT NULL, duration int4 NOT NULL DEFAULT '0', distance int4 NOT NULL DEFAULT '0', elevation int4 NOT NULL DEFAULT '0', calories int4 NOT NULL DEFAULT '0', pace int4 NOT NULL DEFAULT '0', speed int4 NOT NULL DEFAULT '0', elevation_gain int4 NOT NULL DEFAULT '0', heartrate int4 NOT NULL DEFAULT '0', interp int3 NOT NULL DEFAULT 'linear', wcid int8, wpid int6 );

The process to integrate strava and PostgreSQL may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.