基于PostGIS与Timescale在物化视图中计算船舶累计航行距离

你的trail超表结构如下：

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);

-- 转换为按时间分区的超表
SELECT create_hypertable('trail', 'time');

要统计船舶过去24小时的累计航行距离，推荐使用Timescale的连续聚合视图（比普通物化视图更适配时序数据的增量更新），结合PostGIS的ST_Distance和SQL的LAG函数实现，具体步骤如下：

1. 创建连续聚合视图 #

CREATE MATERIALIZED VIEW trail_daily_distance
WITH (timescaledb.continuous) AS
SELECT
    user_id,
    SUM(ST_Distance(position, prev_position)) AS total_distance_meters,
    time_bucket('1 hour', time) AS bucket
FROM (
    SELECT
        user_id,
        time,
        position,
        -- 获取同一船舶的上一条位置记录
        LAG(position) OVER (PARTITION BY user_id ORDER BY time) AS prev_position
    FROM trail
    -- 仅统计过去24小时的数据
    WHERE time >= NOW() - INTERVAL '24 hours'
) AS position_with_prev
-- 排除第一条无前置位置的记录
WHERE prev_position IS NOT NULL
GROUP BY user_id, bucket
WITH DATA;

2. 优化查询性能 #

为了让LAG函数和过滤条件更快执行，建议创建复合索引：

CREATE INDEX trail_user_time_idx ON trail (user_id, time DESC) INCLUDE (position);

这个索引可以让数据库快速按user_id分组并按time排序，同时直接获取position值，避免回表查询。

3. 设置自动刷新策略 #

配置连续聚合视图自动刷新，确保数据始终是最近24小时的统计结果：

-- 设置每分钟刷新一次
SELECT add_continuous_aggregate_policy('trail_daily_distance',
    start_offset => INTERVAL '24 hours',
    end_offset => INTERVAL '0 minutes',
    schedule_interval => INTERVAL '1 minute');

补充说明 #

• ST_Distance针对geography类型返回的单位是米，如果需要公里，可将SUM结果除以1000：SUM(ST_Distance(...)) / 1000 AS total_distance_km
• 连续聚合的bucket维度可以根据需求调整，比如改为'15 minutes'更细的粒度；如果只需要每个船舶的总距离，也可以去掉bucket（但Timescale连续聚合建议保留时间桶以优化存储）
• 如果不需要自动刷新，也可以手动刷新：REFRESH MATERIALIZED VIEW trail_daily_distance;

内容的提问来源于stack exchange，提问作者nilsmagnus