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