在 PostgreSQL 中聚合 (x,y) 坐标点云

我有一个 PostgreSQL 数据库表，其简化结构如下：

• 设备 ID varchar
• Pos_X（整数）
• Pos_Y（整数）

基本上，该表包含设备的大量二维航路点数据。现在我想设计一个查询来减少输出中的坐标数量。它应该聚合附近的坐标（对于某个 x,y 阈值） 一个例子：

第 1 行：设备 1；603；1205

第 2 行：设备 1；604；1204

如果阈值为 5，则应聚合这两行，因为方差小于 5。 知道如何在 PostgreSQL 或 SQL 中执行此操作吗？

使用经常被忽视的内置函数width_bucket() http://www.postgresql.org/docs/current/interactive/functions-math.html结合您的聚合：

如果你的坐标从 0 到 2000，并且你想将 5 个正方形内的所有内容合并为单个点，我会布局一个 10 (5*2) 的网格，如下所示：

SELECT device_id
     , width_bucket(pos_x, 0, 2000, 2000/10) * 10 AS pos_x
     , width_bucket(pos_y, 0, 2000, 2000/10) * 10 AS pos_y
     , count(*) AS ct -- or any other aggregate
FROM   tbl
GROUP  BY 1,2,3
ORDER  BY 1,2,3;

To 最小化误差你可以GROUP BY如图所示的网格，但保存实际的平均坐标：

SELECT device_id
     , avg(pos_x)::int AS pos_x   -- save actual averages to minimize error
     , avg(pos_y)::int AS pos_y   -- cast if you need to
     , count(*)        AS ct      -- or any other aggregate
FROM   tbl
GROUP  BY
       device_id
     , width_bucket(pos_x, 0, 2000, 2000/10) * 10  -- aggregate by grid
     , width_bucket(pos_y, 0, 2000, 2000/10) * 10
ORDER  BY 1,2,3;

sqlfiddle 并排演示了两者。 http://www.sqlfiddle.com/#!12/047cd/7

嗯，这个特殊情况可以更简单：

...
GROUP  BY
       device_id
     , (pos_x / 10) * 10          -- truncates last digit of an integer
     , (pos_y / 10) * 10
...

但这只是因为演示网格大小为10方便地匹配十进制。尝试使用相同的网格大小17或者其他的东西 ...

扩展到时间戳

您可以扩展此方法以涵盖date and timestamp通过将它们转换为unix纪元（自“1970-1-1”以来的秒数）来获取值提炼（） http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT.

SELECT extract(epoch FROM '2012-10-01 21:06:38+02'::timestamptz);

完成后，将结果转换回timestamp with time zone:

SELECT timestamptz 'epoch' + 1349118398 * interval '1s';

或者简单地to_timestamp() http://www.postgresql.org/docs/current/interactive/functions-formatting.html:

SELECT to_timestamp(1349118398);