Strava Activity Processing

strava_process.sql

--Only needs to be run if it already exists.
drop table strava.strava_pts_map;

--Moves the points to start from 0,0
create table strava.strava_pts_map as
with
min_time_geom
as
(
select distinct on (aid)
aid, st_x(geom) * -1 as  x_mov, st_y(geom)  * -1 as  y_mov, ptime
from strava.strava_pts
order by aid, uid asc nulls last
)
select
ST_Translate(p1.geom, ij.x_mov, ij.y_mov) as geom_null,
--the date below 2010,01,01, doesn't matter, it just has to be before any of your actual activities.
make_timestamp(2010,01,01,extract(hour from p1.ptime - ij.ptime)::int, extract(minute from p1.ptime - ij.ptime)::int, extract(second from p1.ptime - ij.ptime)) as time_show,
p1.*
from strava.strava_pts p1
left join min_time_geom ij on p1.aid = ij.aid;

create index idx_strava_pts_map_uid
on strava.strava_pts_map (uid);

create index idx_strava_pts_map_time_show
on strava.strava_pts_map (time_show);

vacuum analyze strava.strava_pts_map;

--Only needs to be run if it already exists.
drop table strava.driver;

--We need to create the time series table to drive the animation.
--Intialize the table with a 00:00:00 entry.
--The 2010,01,01 date may need to be changed if you changed it above. Or if you have activites that are over 24 hours.
--I have a different interval the more the animation proceeds, so starting with 15 second intervals for the first hour, ending with 5 minute intervals for the last hours.
create table strava.driver as
select
0::int8 as row_number,
'2010-01-01 00:00:00.000'::timestamp as dd_start,
'2010-01-01 00:00:00.000'::timestamp as dd_end,
0::float as hour,
0::float as minute,
0::float as second,
'00:00:00'::text as time,
'0 seconds'::text as interval_text;

--For each frame I show twice the duration of the data. So this is another 00:00:00 entry, but also shows 15 seconds of data.
insert into strava.driver
select
1::int8 as row_number,
'2010-01-01 00:00:00.000'::timestamp as dd_start,
'2010-01-01 00:00:15.000'::timestamp as dd_end,
0::float as hour,
0::float as minute,
0::float as second,
'00:00:00'::text as time,
'15 seconds'::text as interval_text;

--First set of intervals.
--I show 30 seconds of movement at 15 second intervals for the first hour.
insert into strava.driver 
select row_number() over(), dd as dd_start, dd +
'30 seconds' as dd_end,
extract(hour from dd) as hour, extract(minute from dd) as minute, extract(second from dd) as second,
lpad(extract(hour from dd +
'15 seconds')::text, 2, '0') || ':' || lpad(extract(minute from dd +
'15 seconds')::text, 2, '0') || ':' || lpad(extract(second from dd +
'15 seconds')::text, 2, '0') as time,
'15 seconds'::text as interval_text
FROM generate_series
        ( '2010-01-01 00:00:00'::timestamp 
        , '2010-01-01 00:59:45'::timestamp
        , '15 seconds'::interval) dd;

--Second set of intervals.
--I show 60 seconds of movement at 30 second intervals for the second hour.
insert into strava.driver 
select row_number() over(), dd as dd_start, dd +
'60 seconds' as dd_end,
extract(hour from dd) as hour, extract(minute from dd) as minute, extract(second from dd) as second,
lpad(extract(hour from dd +
'30 seconds')::text, 2, '0') || ':' || lpad(extract(minute from dd +
'30 seconds')::text, 2, '0') || ':' || lpad(extract(second from dd +
'30 seconds')::text, 2, '0') as time,
'30 seconds'::text as interval_text
FROM generate_series
        ( '2010-01-01 01:00:00'::timestamp 
        , '2010-01-01 01:59:30'::timestamp
        , '30 seconds'::interval) dd;
        
--Third set.
insert into strava.driver 
select row_number() over(), dd as dd_start, dd +
'2 minute' as dd_end,
extract(hour from dd) as hour, extract(minute from dd) as minute, extract(second from dd) as second,
lpad(extract(hour from dd +
'1 minute')::text, 2, '0') || ':' || lpad(extract(minute from dd +
'1 minute')::text, 2, '0') || ':' || lpad(extract(second from dd +
'1 minute')::text, 2, '0') as time,
'1 minute'::text as interval_text
FROM generate_series
        ( '2010-01-01 02:00:00'::timestamp 
        , '2010-01-01 03:59:00'::timestamp
        , '1 minute'::interval) dd;

--Fourth set.
insert into strava.driver 
select row_number() over(), dd  as dd_start, dd +
'10 minute' as dd_end,
extract(hour from dd) as hour, extract(minute from dd) as minute, extract(second from dd) as second,
lpad(extract(hour from dd +
'5 minute')::text, 2, '0') || ':' || lpad(extract(minute from dd +
'5 minute')::text, 2, '0') || ':' || lpad(extract(second from dd +
'5 minute')::text, 2, '0') as time,
'5 minute'::text as interval_text
FROM generate_series
        ( '2010-01-01 04:00:00'::timestamp 
        , '2010-01-01 11:55:00'::timestamp
        , '5 minute'::interval) dd;

--Check it looks ok.
select * from strava.driver;

--Sets the UID, since we created it in batches the row number is no good.
UPDATE strava.driver m
SET    row_number = sub.rn
FROM  (SELECT time, interval_text,  row_number() OVER (order by hour, minute, second, time, interval_text) - 1 AS rn FROM strava.driver) sub
WHERE  m.time = sub.time and m.interval_text = sub.interval_text;
       
create index idx_driver_dd_start
on strava.driver (dd_start);

create index idx_driver_dd_end
on strava.driver (dd_end);

create index idx_driver_row_number
on strava.driver (row_number);

--Only run if table exists.
drop table strava.strava_int_lines;

--Create the line datasets. Easier doing it here than in the QGIS geometry generator.
create table strava.strava_int_lines as
select dd."row_number", dd.dd_start, dd.dd_end , dd."hour", dd."minute", dd."second", dd.time, pp.aid, count(*),
st_makeline(pp.geom_null order by uid) as geom
from strava.driver dd
join strava.strava_pts_map pp on pp.time_show >= dd.dd_start and pp.time_show <= dd.dd_end
group by dd."row_number", dd.dd_start, dd.dd_end, dd."hour", dd."minute", dd."second", dd.time, pp.aid
order by pp.aid;

create index idx_strava_int_lines_geom
on strava.strava_int_lines
using gist (geom);

create index idx_strava_int_lines_row_number
on strava.strava_int_lines
(row_number);

--Strava at least in the past doesn't save the crops in activities. So you may want to delete some activities.

--delete from strava.strava_int_lines
--where aid
--in
--(271,
--654,
--269);

vacuum analyze strava.strava_int_lines;