Average distances between two point sets bound by polygon

[المشرف العام]

I have a table of boundary polygons, a table of points and a table of other_points

I want to work out the average distance between points contained within and other_points for each polygon.

So far I have this query which works but is not very efficient, I limit distances to within 10,000 meters (data is in SRID 27700). I am using POSTGIS to do this, I ultimately need to get the data into Postgresql.

I am sure it can be done better as this will take many hours to compute. Any ideas how?

WITH POINTS AS ( SELECT point_id, polygon_id, point_geometry as wkb_geometry FROM boundary LEFT JOIN points ), DISTANCES AS ( SELECT distinct ON (ogc_fid) ogc_fid, polygon_id, round(ST_Distance(B.wkb_geometry, D.wkb_geometry)::NUMERIC,1) as distance FROM POINTS B, other_points D WHERE ST_DWithin(B.wkb_geometry, D.wkb_geometry,10000) ORDER BY ogc_fid,polygon_id,ST_Distance(B.wkb_geometry, D.wkb_geometry))SELECT distinct polygon_id, count(polygon_id) over(partition by polygon_id), round(avg(distance) over(partition by polygon_id)::NUMERIC, 1), FROM DISTANCES WHERE DISTANCES IS NOT NULL

أكثر...