0

I have a table of polygons T1 and a second table of points P1

Each point has a value

I want to sum the values of P1 for each T1, with P1 being the closest set of points to T1

At the moment the only way I can see to do this is compute a nearest neighbour for each P1 from the T1 table. Then having done this compute the sum. It seems inefficient as P1 contains millions of rows compared to T1 which has a few hundred thousand.

Is there a better approach? It is critical that points in P1 are not allocated to multiple polygons in T1. This is current approach

SELECT distinct on(B.id) B.id,   
   sum("count") OVER (PARTITION BY B.id) as point_count   
FROM P1
INNER JOIN LATERAL (SELECT id FROM T1 ORDER BY P1.wkb_geometry <-> 
T1.wkb_geometry LIMIT 1) B 
ON TRUE
  • 1
    if the respective points are not spatially within those polygons, or within (unambiguous) proximity of them, a (K) Nearest Neighbor (just like you outlined) search with a LATERAL join and the <-> KNN operator on properly indexed geom columns would be the proper (and I dare say only) way; assign polygon ids to each point and sum over those ids. – ThingumaBob Jan 17 at 14:46
  • Yep, that is basically what I am doing now – CitizenFish Jan 17 at 16:20
  • maybe try with SELECT B.id, SUM(P1.id) AS point_count FROM ... GROUP BY B.id for better performance on large tables. – ThingumaBob Jan 17 at 17:44

Your Answer

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

Browse other questions tagged or ask your own question.