2

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.

  • what sort of grid cell do you have in mind? like a regular grid (e.g. coordinate grid) or some specific areas or dynamic/moving (e.g. clustering)? – ThingumaBob Mar 15 '18 at 22:27
  • Thingumabob, I don't have enough reputation to add a comment so I can only reply here. I'm looking at a regular/coordinate grid. – mmackenzie93 Mar 15 '18 at 22:42
  • @mmackenzie93, Welcome to Geographic Information Systems. Please have a look at merging your two accounts. Also, rather than try to add a comment as an answer, or providing more information as a comment, you can edit your post to include further details. Please visit the tour for more information. – Fezter Mar 15 '18 at 23:10
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).

0

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.

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.