[GIS] PostGIS – Calculate of the average distance between points for a delimited area

distance matrixpostgispostgresql

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?

Best Answer

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;
Related Question