PostGIS - Calculate of the average distance between points for a delimited area

Question

I am working with PostGreSQL

I have 2 data sets: 1) A points layer where each point represents a building. 2) A Polygons layer created from the points layer using convexhull and clusterwithin fonctions to aggregate points in areas.

Now what I am trying to do is calculate the average distance between all points (by area) within a radius of 2 km (my projection is EPSG2950 so i work with meters).

The result should be a new column in my polygon layer with a number that indicates what i stated above.

Any idea how such a request would look like?

Answer 1

Sounds like you are looking for ST_DWithin():

SELECT * 
FROM points as r
LEFT JOIN points as s ON ST_DWithin(s.geom, r.geom, 2);

and this to get the average:

SELECT r.id, s.id, AVG(ST_Distance(r.geom, s.geom)) FROM points as r LEFT JOIN points as s ON ST_DWithin(s.geom, r.geom, 2) where r.id <> s.id GROUP BY r.id,s.id;

SELECT r.id, AVG(ST_Distance(r.geom, s.geom))
FROM points as r
LEFT JOIN points as s ON ST_DWithin(s.geom, r.geom, 2) where r.id <> s.id
GROUP BY r.id;