在PostgreSQL中聚合（x，y）坐标点云

Question

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

• 设备ID varchar
• Pos_X（int）
• Pos_Y（int）

基本上，这个表格包含了许多关于设备的二维航点数据。现在我要设计一个查询，它可以减少输出中坐标的数量。它应该聚合附近的坐标（对于特定的x，y阈值）。以下是一个例子：

行1：DEVICE1;603;1205

行2：DEVICE1;604;1204

如果阈值为5，则这两行应该被聚合在一起，因为方差小于5。 您有什么想法如何在PostgreSQL或SQL中实现？

- hoetz

Answer 1

使用经常被忽视的内置函数width_bucket()与聚合函数结合使用：

如果您的坐标从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;

为了减少误差，您可以按照以下示例对网格进行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示例，可以同时演示两种情况。

嗯，这个特定的情况可能会更简单：

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

但这只是因为演示的网格大小为10方便地匹配了十进制系统。尝试使用网格大小为17等其他大小...

---

扩展到时间戳

您可以通过使用extract()将date和timestamp值转换为Unix时期(自1970年1月1日以来的秒数)来扩展此方法。

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

完成后，将结果转换回带时区的时间戳 (timestamp with time zone)：

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

或者简单地使用 to_timestamp() 函数：

SELECT to_timestamp(1349118398);

- Erwin Brandstetter

---

太棒了，谢谢。现在我需要想办法在使用时间戳列并且只聚合相隔最多5分钟的点时如何扩展这个功能... - hoetz

Answer 2

select [some aggregates] group by (pos_x/5, pos_y/5); 

在这里，您可以根据需要进行聚合的数量来替换数字5。

- sega_sai