如何在PostGIS中按日期分组统计ST_LengthSpheroid路径长度？

错误原因分析 #

你当前的查询存在两个核心问题：

1. 聚合逻辑错误：ST_MakeLine(a.geom)未作为聚合函数使用，而是直接引用了未被分组或聚合的geom列。GROUP BY规则要求，SELECT子句中的非分组列必须被聚合函数包裹，否则数据库无法确定如何处理多行的geom值。
2. 路径顺序未保障：子查询按geo_id排序，但分组后无法保证单日范围内的GPS点是按时间顺序连接的，乱序连接的线串会导致计算出的路径长度完全不符合真实行驶轨迹。

正确实现方案 #

要按天统计路径长度，需先按日期+车辆分组，对每组内的GPS点按接收时间排序后聚合为线串，再计算线串的球面长度。以下是修正后的SQL：

SELECT
  date_trunc('DAY', received_at)::date AS track_date,
  ROUND(
    ST_LengthSpheroid(
      ST_MakeLine(geom ORDER BY received_at),  -- 按时间顺序聚合点为轨迹线串
      'SPHEROID["WGS 84",6378137,298.257223563]'
    )::numeric,
    2
  ) AS track_len_meters
FROM geolocation
WHERE
  vehicle = 4
  AND received_at BETWEEN '2023-03-01 12:00:00-00' AND '2023-03-10 12:00:00-00'
GROUP BY date_trunc('DAY', received_at)
ORDER BY track_date;

关键细节说明 #

• 时间顺序聚合：ST_MakeLine(geom ORDER BY received_at)是PostGIS支持的聚合函数写法，确保每组内的GPS点按接收时间顺序连接，生成符合真实行驶轨迹的线串。
• 日期格式化：date_trunc('DAY', received_at)::date将时间戳转换为纯日期格式，让结果更直观易读。
• 单位说明：ST_LengthSpheroid基于WGS84椭球计算球面长度，结果单位为米，完全适配GPS坐标的使用场景。

内容的提问来源于stack exchange，提问作者Руслан Омалаев