CREATE TABLE IF NOT EXISTS altitude ( time TIMESTAMPTZ NOT NULL, workout_id VARCHAR(24) NOT NULL, user_id VARCHAR(24) NOT NULL, altitude DOUBLE PRECISION NOT NULL, gain DOUBLE PRECISION NOT NULL, loss DOUBLE PRECISION NOT NULL, activity_type VARCHAR(32) NOT NULL, elapsed_distance DOUBLE PRECISION NOT NULL, absolute_distance DOUBLE PRECISION NOT NULL, elapsed_time DOUBLE PRECISION NOT NULL, absolute_time DOUBLE PRECISION NOT NULL );
- altitude This is the geographical altitude at the point the altitude point was recorded.
- absolute_time This is the absolute time since the start of the recording
- absolute_distance This is the absolute distance traveled since the start of the recording
- elapsed_distance This is the elapsed distance between two successive altitude points
- elapsed_time This is the elapsed time between two successive altitude points
- gain The altitude gain between two successive altitude points
- loss The altitude loss between two successive altitude points
I want to calculate the speed by distance, that is I want something like:
SELECT distance, speed FROM calculate_speed_by_distance(from_time, to_time, step_distance) ORDER BY distance
The step should specify the distance interval the resulting speed should be calculated beginning at the distance already traveled at from_time to the distance traveled at to_time. E.g. speed=200 (with a distance at from_time of 0m and a distance of to_time of 756m) sould mean:
0m 200m 400m 600m 800m 0km/h 5km/h 10km/h 11km/h 8km/h
The speed (absolute_distance/absolute_time) should be averaged among all successive altitude points in each interval.
If there are no altitude points in some intervals, the last calculated speed point can be used.
So I need something like the following:
CREATE OR REPLACE FUNCTION calculate_speed_by_distance(arg_time_from timestamp, arg_time_to timestamp, arg_distance_step bigint, arg_workout_id varchar(24), arg_user_id varchar(24)) RETURNS TABLE ( distance double precision, value double precision ) AS ' BEGIN RETURN query with ... END; ' LANGUAGE PLPGSQL;
So the question is: How to aggregate the data in the current format to calculate the speed by distance in the aforementioned way?
Example data for altitude points: See here