PostGIS查询用户轨迹与检查点线相交时间的实现方法

问题原因 #

你当前的写法是将单个用户的所有上报点聚合为整条轨迹线，聚合操作已经丢失了每个原始点的时间戳信息，因此直接在SELECT中添加located_at字段会触发GROUP BY语法约束，无法直接获取相交时间。

解决思路 #

用户穿越检查点的行为一定发生在连续两次位置上报的时间区间内，因此我们可以按以下逻辑实现：

• 对每个用户的上报点按时间排序，通过窗口函数获取每个点对应的下一个上报点和时间
• 将相邻两个上报点拼接为移动线段
• 判断移动线段和检查点线是否相交
• 对相交的线段通过线性插值计算出精确的穿越时间

实现代码 #

WITH user_location_segments AS (
  -- 为每个用户的位置点匹配相邻的下一个上报点和对应时间
  SELECT
    user_uuid,
    location AS prev_point,
    located_at AS prev_time,
    LEAD(location) OVER (PARTITION BY user_uuid ORDER BY located_at) AS next_point,
    LEAD(located_at) OVER (PARTITION BY user_uuid ORDER BY located_at) AS next_time
  FROM locations
)
SELECT
  c.uuid AS checkpoint_uuid,
  uls.user_uuid,
  -- 线性插值计算穿越检查点的精确时间
  prev_time + (next_time - prev_time) * ST_LineLocatePoint(
    ST_MakeLine(prev_point, next_point),
    ST_Intersection(ST_MakeLine(prev_point, next_point), c.line)
  ) AS located_at
FROM user_location_segments uls
JOIN checkpoints c
  -- 过滤出和检查点线相交的用户移动线段
  ON ST_Intersects(ST_MakeLine(prev_point, next_point), c.line)
-- 排除每个用户最后一个没有下一跳的上报点
WHERE uls.next_point IS NOT NULL
ORDER BY checkpoint_uuid, located_at;

注意事项 #

• 如果你的数据使用WGS84地理坐标系（SRID=4326），可以将ST_MakeLine的结果强转为geography类型，距离计算会更准确，只需将代码中对应的ST_MakeLine(prev_point, next_point)替换为ST_MakeLine(prev_point, next_point)::geography、c.line替换为c.line::geography即可
• 如果业务不需要精确的插值时间，也可以直接返回prev_time（相交区间的起始上报时间）或next_time（相交区间的结束上报时间），省略插值计算逻辑
• 该方案支持同一个用户移动线段和多个检查点相交的场景，会自动返回多条对应记录

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