More

# 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``

OK so here is the much improved query thanks to the useful pointer given by Jakub

``WITH POINTS AS ( SELECT point_id, polygon_id, point_geometry as wkb_geometry FROM boundary LEFT JOIN points ), DISTANCES AS ( SELECT polygon_id, point_geometry <-> other_point_geometry as distance FROM ( SELECT distinct on(point_id) point_id, polygon_id, point_geometry, (SELECT wkb_geometry FROM other_points ORDER BY wkb_geometry<->B.wkb_geometry LIMIT 1) AS other_point_geometry FROM points B ) AS FOO ) 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``