1

I have a table of devices, each of them has a field 'location' that is a postGis geometry(Point,4326)

I need to operate a GROUP BY based of the placement on a grid, I'd use ST_SnapToGrid and then I need to get the ST_Centroid on each set of points in the cell.

I can correctly group by grid:

SELECT count(*) as count, 
   st_astext(ST_SnapToGrid(location, 50)) as grid 
FROM device_device GROUP BY grid;
 count |       grid       
-------+------------------
  3864 | POINT(0 0)
  1462 | POINT(0 -100)
  1377 | POINT(0 100)

and I can get the centroid out of many points:

testgis=# SELECT ST_AsText(ST_Centroid(
            ST_Collect( ARRAY(SELECT location FROM device_device) )));
                   st_astext                   
-----------------------------------------------
 POINT(-0.281948442456614 0.00443529565569083)
(1 riga)

I cannot understand how to mix the 2 and calculate the centroid for each cell:

testgis=# select count(*) as count, 
  st_astext(ST_SnapToGrid(location, 50)) as grid, 
  st_astext(ST_Centroid(ST_Collect(ARRAY(location)))) 
FROM device_device group by grid;
ERRORE:  errore di sintassi a o presso "location"
RIGA 1: ...) as grid, st_astext(ST_Centroid(ST_Collect(ARRAY(location))...                                                             
3

Just like with the COUNT(*), the SELECT can utilize (almost) every other aggregate function, keeping the reference to the grouped rows defined by the GROUP BY.

Not directly obvious: in your first query you implicitly GROUP BY ST_SnapToGrid(...) already, by using the column expression alias grid.

With that in mind, just run this very similar query:

SELECT  ST_Centroid( ST_Collect( location ) ) AS geom
FROM    device_device
GROUP BY
        ST_SnapToGrid( location, 50 )
;
  • 1
    Thanks! so my mistake was to add ARRAY, that I used w/o really understanding... – Alessandro Dentella Jul 4 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.