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
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.