# 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
``````
• Please use `alias.column` everywhere, having to guess what table comes from which table is no fun. For now all I can do is refer you to this answer: gis.stackexchange.com/a/71607/16594 that contains the most important part you need to fix. Aug 13 '15 at 18:04

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
``````
• You are still missing the `ON` clause of the `LEFT JOIN` between `boundary` and `points`. Aug 16 '15 at 10:33