# Group by equidistant integer intervals

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 '19 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 '19 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 '19 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 '19 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 '19 at 19:41
{"ft_lang_label":"__label__en","ft_lang_prob":0.7995516,"math_prob":0.97304213,"size":2374,"snap":"2020-34-2020-40","text_gpt3_token_len":514,"char_repetition_ratio":0.18354431,"word_repetition_ratio":0.046242774,"special_character_ratio":0.21229991,"punctuation_ratio":0.10632911,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.98798,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-08-12T21:31:58Z\",\"WARC-Record-ID\":\"<urn:uuid:d37bd609-0e9e-4af1-a05c-2a7c12e955e3>\",\"Content-Length\":\"145504\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:e1ec2fe8-0566-41b7-8075-d22d9490c1fa>\",\"WARC-Concurrent-To\":\"<urn:uuid:1fac2684-333f-4ab9-b340-bb4946d5612d>\",\"WARC-IP-Address\":\"151.101.65.69\",\"WARC-Target-URI\":\"https://dba.stackexchange.com/questions/243098/group-by-equidistant-integer-intervals\",\"WARC-Payload-Digest\":\"sha1:Q44WVD56AELOCFPQRV2BPI47XPYS7D4T\",\"WARC-Block-Digest\":\"sha1:G2U2UIL4N5V4LI2QLOONKYPBOGN3OSTB\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-34/CC-MAIN-2020-34_segments_1596439738944.95_warc_CC-MAIN-20200812200445-20200812230445-00446.warc.gz\"}"}