postgresqlpostgistimescaledb

Calculating the summed distance in a materialized view with postgis and timescale

I am logging ship-positions at minute-interval in a postgresql database and i am using timescale and postgis.

My table of trail with indexes:

CREATE TABLE trail
(
    time     timestamptz NOT NULL default now(),
    user_id  int         not null,
    position geography(POINT, 4326)
);

create index trail_userid_idx on trail (user_id);
create index trail_time_idx on trail (time);

-- Then we convert it into a hypertable that is partitioned by time
SELECT create_hypertable('trail', 'time');

I am having a hard time finding out how i can create a materialized view that uses the ST_Distance function to aggregate how far the ships has travelled for instance the past 24 hours. The samples in the timescale documentation are rather simple when it comes to postgis.

Has anyone done this before?

Solution

  • I don't know about any restriction in timescale but in pure Postgres this could look as follows.

    (Expecting the user_id is the identifier for your ships...)

    step-by-step demo:db<>fiddle

    CREATE MATERIALIZED VIEW distance_24 AS                                 -- 6
  
SELECT DISTINCT ON (user_id)                                            -- 5
    *,
    SUM(distance_to_prev_position)                                      -- 4
        OVER (PARTITION BY user_id ORDER BY time) as cum_distance
FROM (
    SELECT
        *,
        ST_Distance(                                                    -- 3
            position, 
            lag(position) OVER (PARTITION BY user_id ORDER BY time)     -- 2
        ) as distance_to_prev_position
    FROM trail
    WHERE time > now() - interval '24 hours'                     -- 1
) s
ORDER BY user_id, time DESC;                                            -- 5
    1. Filter your range. (in the demo I used a fixed value due to demo input data)
    2. use lag() window function to get the previous position (ordered by the timestamp and user_id) into your current row
    3. Calculate the distance between current position and previous one
    4. use cumulative SUM() window function to cumulatively sum up the distances calculated in the previous step
    5. If you only wanted to get one single value per ship you could use the DISTINCT ON clause (combined with the specific ORDER BY)
    6. From that query, create the materialized view.

    Notes: