Agregando nubes de puntos de coorderadas (x, y) en PostgreSQL

Tengo una tabla de database PostgreSQL con la siguiente estructura simplificada:

  • ID de dispositivo varchar
  • Pos_X (int)
  • Pos_Y (int)

Básicamente, esta tabla contiene una gran cantidad de datos bidimensionales de waypoints para dispositivos. Ahora quiero diseñar una consulta que networkinguzca el número de coorderadas en la salida. Debe agregar coorderadas cercanas (para un cierto umbral x, y) Un ejemplo:

fila 1: DISPOSITIVO1; 603; 1205

fila 2: DISPOSITIVO1; 604; 1204

Si el umbral es 5, estas dos filas se deben agregar dado que la varianza es menor que 5. ¿Alguna idea de cómo hacer esto en PostgreSQL o SQL en general?

Utilice la function width_bucket() menudo pasada por alto width_bucket() en combinación con su agregación:

Si sus coorderadas se ejecutan desde, digamos, 0 a 2000 y desea consolidar todo dentro de cuadrados de 5 a puntos únicos, tendré una cuadrícula de 10 (5 * 2) como esta:

 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; 

Para minimizar el error , puede GROUP BY la cuadrícula como se demostró, pero save coorderadas promedio reales:

 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 demostrando ambos al costado.

Bueno, este caso particular podría ser más simple:

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

Pero eso es solo porque el tamaño de la cuadrícula de demostración de 10 coincide convenientemente con el sistema decimal. Pruebe lo mismo con un tamaño de cuadrícula de 17 o algo …


Expandir a marcas de time

Puede ampliar este enfoque para cubrir los valores de la date y la timestamp convirtiéndolos a unix epoch (número de segundos desde '1970-1-1') con extract () .

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

Cuando termine, convierta el resultado a la timestamp with time zone de timestamp with time zone :

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

O simplemente to_timestamp() :

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

Donde en lugar de 5 puede tener cualquier número dependiendo de cuánta agregación necesita /