Aggregating (x,y) coordinate point clouds in PostgreSQL

by

Use the often overlooked built-in function width_bucket() in combination with your aggregation:

If your coordinates run from, say, 0 to 2000 and you want to consolidate everything within squares of 5 to single points, I would lay out a grid of 10 (5*2) like this:

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 minimize the error you could GROUP BY the grid as demonstrated, but save actual average coordinates:

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 demonstrating both alongside.

Well, this particular case could be simpler:

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

But that’s just because the demo grid size of 10 conveniently matches the decimal system. Try the same with a grid size of 17 or something …

Expand to timestamps

You can expand this approach to cover date and timestamp values by converting them to unix epoch (number of seconds since ‘1970-1-1’) with extract().

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

When you are done, convert the result back to timestamp with time zone:

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

Or simply to_timestamp():

SELECT to_timestamp(1349118398);

More Related Contents:

  1. Get values from first and last row per group
  2. Crosstab with a large or undefined number of categories
  3. Select first row in each GROUP BY group?
  4. How to use RETURNING with ON CONFLICT in PostgreSQL?
  5. Does PostgreSQL support “accent insensitive” collations?
  6. PostgreSQL: running count of rows for a query ‘by minute’
  7. Split comma separated column data into additional columns
  8. IN vs ANY operator in PostgreSQL
  9. How to delete duplicate entries?
  10. Escaping keyword-like column names in Postgres
  11. Delete duplicate rows (don’t delete all duplicate)
  12. GROUP BY + CASE statement
  13. Split column into multiple rows in Postgres
  14. Perform this hours of operation query in PostgreSQL
  15. Getting date list in a range in PostgreSQL
  16. Postgres Dynamic Query Function
  17. How do I delete a fixed number of rows with sorting in PostgreSQL?
  18. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  19. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  20. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  21. Error: Column does not exist
  22. DROP FUNCTION without knowing the number/type of parameters?
  23. Multiple array_agg() calls in a single query
  24. INSERT COMMAND :: ERROR: column “value” does not exist
  25. Postgres error updating column data
  26. Check whether string is a date Postgresql
  27. Counting the number of occurrences of a substring within a string in PostgreSQL
  28. Return rows from INSERT with ON CONFLICT without needing to update
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment