postgresql - PostGIS:找到过去 n 天内交叉路径的用户的最佳方法？

Question

我有一个表，用于存储用户在运行、骑自行车、步行等时的位置数据。该表的设计如下:

CREATE TABLE public.user_location (
    app_user_id int4 NOT NULL,
    location_uuid uuid NOT NULL,
    .....
    location_timestamp timestamptz NOT NULL,
    app_user_location geometry(POINT, 4326) NOT NULL,
    coordinate_accuracy float8 NULL,
);

location_timestamp字段包含记录的时间位置和 app_user_location字段包含 GPS 坐标。如果用户正在移动(例如骑自行车/慢跑等)，则每 100 米接收一次位置数据。
在几天、几周和几个月的时间里，我收集了数百万个位置点。我想要做的是找出哪些用户在过去 n 天(或在任何一天)经过了路径，即他们在同一时间点彼此靠近。一个简单的例子是，如果用户慢跑 5 公里。我需要找到他在慢跑时“遇到”的其他用户。

我在 app_user_location 上创建了一个索引柱子。接下来，我需要在给定的日期(或在给定的时间点)为用户的一组点做一个最近的邻居。我可以循环执行，但想知道是否有更好的 SQL 方法来执行此操作？

提前致谢。

Answer 1

这是第一次尝试，我还没有测试过!

除非您的所有用户都居住在赤道上，否则我建议使用地理而不是几何来确定用户位置——这将简化距离计算。如果您使用的是支持生成列的 postgres 版本，则可以执行

ALTER TABLE user_location ADD COLUMN app_user_location_geog GEOGRAPHY(POINT, 4326)
    GENERATED ALWAYS AS (app_user_location) STORED;

然后您可以根据匹配时间戳和 ST_DWithin 找到 id。这可能需要在 geography 列和时间戳上建立索引。

SELECT
  user1.app_user_id AS user1_id,
  user2.app_user_id AS user2_id,
  user1.app_user_location_geog AS user1_location,
  user2.app_user_location_geog AS user2_location,
  user1.location_timestamp AS crossing_time
FROM
  user_location user1
  JOIN user_location user2 
    ON user1.app_user_id != user2.app_user_id
    -- geo timestamps occurred within a minute of each other
    AND
      user1.location_timestamp >= user2.location_timestamp - INTERVAL '30 seconds'
    AND
      user2.location_timestamp <= user2.location_timestamp + INTERVAL '30 seconds'
    AND
      -- geographies were within 10 meters of each other
      ST_DWithin(user1.app_user_location_geog, user2.app_user_location_geog, 10)