1

I have 700,000 grids generated to cover an administrative area. I want to calculate the sum of an attribute in its neighboring grids till 2nd order and assign it to each grid. To do so I have generated a buffer on each grid and identified which subsequent grids fall under a parent grid and summed their attributes, but it is taking too long.

Is there a quicker way around this issue?

0
0

It can be solved by a simple update like this:

update your_grid 
  set (sum_field1, sum_field2) = 
    (select sum(a.field1), sum(a.field2) 
       from your_grid a 
       where st_touches(a.geometry, geometry))

This will update your grid fields with values that are calculated as a sum of fields from neighboring grids (grids touches that one which is updated). For best performance, there should be a spatial index on grid geometry field.

OR if your grid is regular and can be accessed by a row and column indices, then you can use something like this:

update your_grid 
  set (sum_field1, sum_field2) = 
    (select sum(a.field1), sum(a.field2) 
       from your_grid a 
       where a.row >= row -1 and a.row <= row + 1 
         and a.col >= col -1 and a.col <= col + 1
         and a.row <> row and a.col <> col)

Again, for best performance, there should be index on a row and col fields.

5
  • The solution provided solved the issue for the 1st order (3x3 neighbourhood around the first grid). For the 2nd order (5x5 neighbourhood) i used the st_crosses function and that greatly decreased the query run time. Your solution helped design the solution to the 2nd order problem. Thank you for your time. Nov 15 '19 at 5:50
  • @SiddharthSoni how would you solve the 2nd order window summary using ST_Crosses? I suspect the 'greatly decreased [...] query run time' is due to it not working as expected...
    – geozelot
    Nov 18 '19 at 14:06
  • I generated a buffer and used the st_exteriorring function and st_crosses function to identify the grids that intersect the 2nd order buffer. The cumulative sum of the grid itself, 1st order neighbours and 2nd order neighbours gave the value I wanted. Nov 20 '19 at 5:15
  • @SiddharthSoni if that works for your grid, better use ST_DWithin with the same radius as you used for the buffer! Same result, orders of magnitude more efficient (using the index).
    – geozelot
    Nov 20 '19 at 8:37
  • @SiddharthSoni better yet, if your grid is regular, use a.geom && ST_Expand(b.geom, <sidelength_of_gridcells>)
    – geozelot
    Nov 20 '19 at 8:42

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.