Grouping points per grid and summing their value on PostGIS?

Question

I'm fairly new to PostgreSQL and PostGIS so don't expect someone to answer my question for me of course, I was just wondering if someone could point me in the right direction on where to look it up and if it is actually possible.

I have a table with a value, longitude and latitude column. I have made a geometry column using

UPDATE table SET geom = ST_Transform(
  ST_GeomFromText('POINT('||longitude||' '||latitude||')', 4326), 4326);

Would it be possible to group by a specific sized grid cell and sum the value column for each point and then order it highest value to lowest? I'm using the terminal in Linux.

I am hoping to create a grid which covers every point stored in the table and then total the value per grid cell.

Answer 1

Have a look at the function st_snaptogrid. With this function, you snap the points to an underlying grid. You can either specify the origin x and origin y of the grid and the size x and size y, or simple say how large each grid cell should be. The example below generates a 1000 points in a small area, snaps them to a grid of 0.1 (comparable to your WGS84 coordinate system) and groups the snapped points.

WITH points AS
(
-- Generate a set of points (1000 points)
    SELECT (ST_dump(ST_GeneratePoints(
    ST_Buffer(
        ST_GeomFromText(
        'LINESTRING(52 5.5,52 6)'
        ), 1, 'endcap=round join=round'), 1000))).geom,
       random() rand
),snapped_points as
(
-- Snap to points to a grid (0.1 size)
    SELECT 
    st_astext(geom) generated_point,
    st_astext(st_snaptogrid(geom,0.1) ) snapped_point,
    rand
    FROM points
)
-- Group the points and see how many there are per cell
SELECT snapped_point,count(*),sum(rand)
FROM snapped_points 
GROUP BY snapped_point;

You could transform your points to a projected coordinate system for the grid size to make a bit more sense with st_transform; then you could use a grid size of 100 (for 100 meters) or 1000 (for 1km).

Answer 2

You will be able to do this, but you'll need to be a bit more specific about what your grouping with... If it's a vector grid, where there are polygon squares, you should be able to group the points by the ID of the polygon that they intersect with, and sum the values.

Pseudocode:

select sum(p.value), a.geom
from points as p, polygons as a
where ST_Intersects(p.geom, a.geom)
group by g.key;

I haven't tested, but that's the core of it.