1

I am using TimescaleDB (an extension to PostgreSQL) to store altitude data from a GPS sensor. The table looks like:

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?

EDIT:

Example data for altitude points: See here

Best,

Philipp

  • Can you clarify what is your question? What is your particular difficulty? – k_rus Jul 17 at 15:32
  • I have added the actual question, thanks. The difficulty comes from the kind the data needs to be aggregated - not by time but by a custom integer range. TimescaleDB offers only time_bucket_gapfill to do time bucketed aggregation. – Philipp Staudt Jul 17 at 15:44
  • Can you explain how step-distance relates to absolute-distance in OP? My understanding is that it might aggregate, but might split absolute-distance. May be example with data will help to understand. – k_rus Jul 17 at 16:47
  • I have added more information and example data. I can also add elapsed-distance and elapsed-time (between two successive altitude points) in the dataset if this is easier for the calculation. – Philipp Staudt Jul 17 at 17:18
  • Thank you for adding the example. Now it is much more clear about the data granularity and that aggregating is necessary. – k_rus Jul 17 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.