基于GPS轨迹数据筛选符合条件的5米长路段

需求说明 #

给定包含track_seg_point_id、time、elevation、geometry字段的GPS轨迹点数据，需筛选出同时满足以下条件的轨迹路段：

• 路段总长度≥5米
• 路段总海拔变化≤0.5米（取绝对值）

示例验证：

• 0到2号点路段：长度超6米，但海拔变化超0.5米，不符合要求
• 2到4号点路段：长度约5.64米，海拔变化约0.4米，符合要求

SQL实现代码 #

以下是基于PostGIS的初始分析代码，用于计算并筛选符合条件的路段：

WITH RECURSIVE trajectory_segments AS (
    -- 基础情况：单个相邻点对组成的初始路段
    SELECT
        track_seg_point_id AS seg_start,
        LEAD(track_seg_point_id) OVER (ORDER BY track_seg_point_id) AS seg_end,
        ST_Distance(geometry, LEAD(geometry) OVER (ORDER BY track_seg_point_id)) AS distance,
        ABS(elevation - LEAD(elevation) OVER (ORDER BY track_seg_point_id)) AS elevation_chng
    FROM gps_points
    WHERE LEAD(track_seg_point_id) OVER (ORDER BY track_seg_point_id) IS NOT NULL

    UNION ALL

    -- 递归扩展：将现有路段向后延伸一个点
    SELECT
        ts.seg_start,
        g.track_seg_point_id AS seg_end,
        ts.distance + ST_Distance(prev_geom.geometry, g.geometry),
        ABS(gps_start.elevation - g.elevation)
    FROM trajectory_segments ts
    JOIN gps_points g ON g.track_seg_point_id = ts.seg_end + 1
    JOIN gps_points prev_geom ON prev_geom.track_seg_point_id = ts.seg_end
    JOIN gps_points gps_start ON gps_start.track_seg_point_id = ts.seg_start
    -- 海拔变化超过0.5米时停止扩展
    WHERE ABS(gps_start.elevation - g.elevation) <= 0.5
)
-- 筛选出长度达标且海拔变化符合要求的路段
SELECT
    seg_start,
    seg_end,
    ROUND(distance::numeric, 2) AS distance,
    ROUND(elevation_chng::numeric, 2) AS elevation_chng
FROM trajectory_segments
WHERE distance >= 5
ORDER BY seg_start, seg_end;

输出结果说明 #

最终结果表包含以下字段：

• seg_start：路段起始点的track_seg_point_id
• seg_end：路段结束点的track_seg_point_id
• distance：路段总长度（保留两位小数，单位：米）
• elevation_chng：路段总海拔变化的绝对值（保留两位小数，单位：米）

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