如何在PostGIS表中筛选间距至少10公里的设备轨迹点？

首先明确需求：你需要从设备轨迹点中筛选出间隔至少10公里的关键位置，同时先合并完全重复的点位。以下提供两种符合PostGIS特性的实现方案，根据实际需求选择：

方案一：轨迹简化（保留间隔≥10公里的连续点） #

这种方案适合简化设备移动轨迹，保留第一个点以及与前一个保留点距离超过10公里的点位，符合大多数轨迹去重精简的场景：

WITH aggregated_points AS (
    -- 先合并完全相同的点位，同时转换为PostGIS地理对象用于距离计算
    SELECT 
        MIN(ts) AS arrivalTime,
        MAX(ts) AS departureTime,
        lat,
        lon,
        ST_SetSRID(ST_MakePoint(lon, lat), 4326)::GEOGRAPHY AS geom
    FROM logsData
    WHERE lat IS NOT NULL AND lon IS NOT NULL
    GROUP BY lat, lon
    ORDER BY arrivalTime
),
points_with_prev_distance AS (
    -- 计算每个点与前一个点的球面距离（单位：米）
    SELECT 
        *,
        ST_Distance(geom, LAG(geom) OVER (ORDER BY arrivalTime)) AS distance_to_prev
    FROM aggregated_points
)
-- 保留第一个点，以及与前一个点距离≥10公里的点
SELECT 
    arrivalTime,
    departureTime,
    lat,
    lon
FROM points_with_prev_distance
WHERE distance_to_prev IS NULL 
   OR distance_to_prev >= 10000;

说明： #

• ST_SetSRID(ST_MakePoint(lon, lat), 4326) 将经纬度转换为WGS84坐标系的空间点，转换为GEOGRAPHY类型后，ST_Distance会计算球面距离，更符合真实地理场景的公里数计算。
• LAG()窗口函数用于获取按时间排序后的前一个点位，确保只保留移动超过10公里的关键节点。

方案二：保留与所有其他点位距离≥10公里的点 #

如果你的需求是严格保留那些与所有其他途经点位距离都不小于10公里的点（这种场景在连续移动的轨迹中很少见），可以使用以下查询：

WITH aggregated_points AS (
    SELECT 
        MIN(ts) AS arrivalTime,
        MAX(ts) AS departureTime,
        lat,
        lon,
        ST_SetSRID(ST_MakePoint(lon, lat), 4326)::GEOGRAPHY AS geom
    FROM logsData
    WHERE lat IS NOT NULL AND lon IS NOT NULL
    GROUP BY lat, lon
)
SELECT 
    ap1.arrivalTime,
    ap1.departureTime,
    ap1.lat,
    ap1.lon
FROM aggregated_points ap1
-- 排除存在其他点位与当前点距离小于10公里的情况
WHERE NOT EXISTS (
    SELECT 1
    FROM aggregated_points ap2
    WHERE (ap1.lat <> ap2.lat OR ap1.lon <> ap2.lon)
      AND ST_Distance(ap1.geom, ap2.geom) < 10000
);

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