postgresql - 使用计算的距离更新同一张表

Question

这个问题与this:有关

我可以用新条目计算距离并插入到名为距离的第 4 列中。但是现在我需要进行更新查询，使用 ST_Distance 来计算现有 GPS 坐标的渐进距离并存储到距离列中。知道我现有的表格设计如何实现吗？

Answer 1

在更新现有行的值时，您需要为每个 device_id 标识当前行之前的行.您可以使用窗口函数执行此操作，按 device_id 对行进行分区并通过 "timestamp" 订购，像这样:

UPDATE my_table SET distance = cum_dist
FROM (
  SELECT dev, t, sum(d) OVER (PARTITION BY dev ORDER BY t) AS cum_dist
  FROM (
    SELECT mt.device_id AS dev, mt."timestamp" AS t,
           coalesce(ST_Distance(mt.GPS_location, lag(mt.GPS_location) OVER (PARTITION BY mt.device_id ORDER BY mt."timestamp")), 0.) AS d
    FROM my_table mt
  ) sub2
) sub
WHERE my_table.device_id = sub.dev
  AND my_table."timestamp" = sub.t;

几句解释:

• 最内层查询 sub2计算到特定设备的先前 GPS 位置的距离。它使用 lag() function这样做和coalesce()函数以确保系列中的第一个点的距离为 0而不是 NULL .
• 在接下来的查询中sub计算累积距离，再次使用窗口函数，但这次使用众所周知的聚合函数 sum() .
• 然后通过链接查询 sub 将累积距离“更新到”表中使用 device_id 到表和 "timestamp"专栏。

与所有更新一样，首先运行查询以查看它是否生成正确的结果，然后执行 UPDATE .