如何在PostgreSQL中利用窗口函数计算几何图形的移动轨迹

PostgreSQL窗口函数需与PostGIS配合处理轨迹：LAG()获取时序相邻几何以计算位移，ROW_NUMBER()用于分段聚合；关键在先用ORDER BY ts组织时序，再用ST_Distance或ST_MakeLine处理geometry。

如何在postgresql中利用窗口函数计算几何图形的移动轨迹_结合postgis

直接说结论：PostgreSQL 的窗口函数本身不处理几何图形，但可以和 PostGIS 函数配合，对按时间排序的轨迹点序列做有序编号、差分计算、分段聚合等操作——关键在于「先用窗口函数组织时序，再用 PostGIS 函数处理几何」。

因为 ROW_NUMBER() 只给行打序号，不提供前一行的坐标；而计算两点间位移（如速度、方向）必须拿到当前点和上一个点的 geom。常见错误是写成：

SELECT id, geom, ROW_NUMBER() OVER (ORDER BY ts) AS rn FROM trajectory;

这只能编号，没法算距离。真正需要的是 LAG() 或 LEAD() 拉取相邻行的几何值。

LAG(geom) 必须配合 ORDER BY ts 才有意义，否则时序错乱，位移结果全错
如果 ts 有重复值，需加二级排序（如 ORDER BY ts, id），否则 LAG() 行为不可预测
geom 是 geometry(Point, 4326) 类型，LAG() 返回的仍是 geometry，可直接传给 ST_Distance() 或 ST_MakeLine()

这是最常用场景：求每一段轨迹线段的长度（单位米）。注意必须转成 geography 类型才能得到真实米制距离：

SELECT
  id,
  ts,
  ST_Distance(
    geom::geography,
    LAG(geom)::geography
  ) AS dist_m
FROM trajectory
ORDER BY ts;

当需要把连续 N 个点合并为一条折线（比如每 5 秒合成一段），可用 ROW_NUMBER() 构造分组键：

SELECT
  (ROW_NUMBER() OVER (ORDER BY ts) - 1) / 5 AS seg_id,
  ST_MakeLine(ARRAY_AGG(geom ORDER BY ts)) AS seg_line
FROM trajectory
GROUP BY seg_id;

轨迹分析慢或结果不准，往往不是函数写错，而是底层配置或类型没对齐：

没建空间索引：CREATE INDEX idx_trajectory_geom ON trajectory USING GIST (geom); —— 即使只查点，LAG() 本身不依赖索引，但后续加条件（如 WHERE ST_Within(geom, ...)）会崩
时间字段没索引：CREATE INDEX idx_trajectory_ts ON trajectory (ts); —— ORDER BY ts 在百万级轨迹点上可能全表扫描
误用 geometry 算大范围距离：跨经纬度大的轨迹（如航班），必须用 geography，否则 ST_Distance 误差可达 10% 以上
ST_MakeLine() 输入点数超 10000 时可能内存溢出，应提前用 WHERE 截断或分批处理