PostgreSQL/PostGIS中基于startflag分组生成Linestring路径的SQL方案

核心思路是先给每个路径分段分配唯一组ID，再按组聚合生成Linestring。以下是具体SQL实现：

WITH segment_groups AS (
    -- 第一步：为每个路径分段分配组ID
    SELECT
        car,
        timestamp,
        geom,
        -- 遇到startflag=4时组ID加1，否则延续当前组ID
        SUM(CASE WHEN startflag = 4 THEN 1 ELSE 0 END) OVER (
            PARTITION BY car  -- 按车辆分组，避免不同车辆路径混同
            ORDER BY timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS segment_id
    FROM your_table_name  -- 替换为你的实际表名
)
-- 第二步：按车辆和分段ID聚合生成路径
SELECT
    car,
    segment_id,
    MIN(timestamp) AS start_time,  -- 分段起始时间
    MAX(timestamp) AS end_time,    -- 分段结束时间
    ST_MakeLine(geom ORDER BY timestamp) AS path_line  -- 生成有序Linestring路径
FROM segment_groups
GROUP BY car, segment_id
ORDER BY car, start_time;

关键逻辑说明 #

• PARTITION BY car：必须按车辆分组，确保不同车辆的路径分段不会被错误合并。
• SUM(CASE...) OVER (...)：通过累加起始标记（startflag=4）的计数，自动为每个分段分配唯一segment_id，每个起始标记会开启一个新分段。
• ST_MakeLine(geom ORDER BY timestamp)：聚合时强制按时间戳排序，保证路径点的顺序符合行驶轨迹，生成的Linestring才是正确路径。

如果需要保留分段起始记录的ID，可扩展如下：

WITH segment_groups AS (
    SELECT
        id,
        car,
        timestamp,
        geom,
        startflag,
        SUM(CASE WHEN startflag = 4 THEN 1 ELSE 0 END) OVER (
            PARTITION BY car
            ORDER BY timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS segment_id
    FROM your_table_name
)
SELECT
    s.car,
    s.segment_id,
    start.id AS start_record_id,  -- 分段起始记录的ID
    MIN(s.timestamp) AS start_time,
    MAX(s.timestamp) AS end_time,
    ST_MakeLine(s.geom ORDER BY s.timestamp) AS path_line
FROM segment_groups s
LEFT JOIN segment_groups start
    ON s.car = start.car
    AND s.segment_id = start.segment_id
    AND start.startflag = 4
GROUP BY s.car, s.segment_id, start.id
ORDER BY s.car, start_time;

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