0

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?

  • have you tried any statements yet? do your datasets already contain a geometry column? – kttii Jun 17 '16 at 17:37
  • I have not tried any statement yet. I have been looking around documentation to try it out but so far nothing seems to work, therefore i have decided to post the question as i continue experimenting and post udpates as i get results. Both my datasets are associated to a geometry yes! – Alex Girard Jun 17 '16 at 17:43
  • Could you provide a diagram of what you are trying to do? I'm not understanding how the polygons layer is related? You want to know the average distance of the points inside each polygon? – kttii Jun 17 '16 at 17:59
  • To simplify, let's forget about the polygon for the first part of the query. Therefore, I am looking for a fonction that would give me the average distance from a point to all other points within a range of 2 km. (let's call this new column avg_dist) Than, the fonction would calculate the average of avg_dist for all the points with a geometry within the one of the polygon. Is this a bit more clear? (I am pretty new to the sql language, I am sorry if its not clear enough!) – Alex Girard Jun 17 '16 at 18:07
3

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;
| improve this answer | |
  • This does solve the problem of the range as it would limit the search list, but what would be the fonction to find the average distance between 1 point and the rest of the points within the index created by ST_DWithin? – Alex Girard Jun 17 '16 at 18:26
  • oh, sorry, I forgot about that part... one moment... – kttii Jun 17 '16 at 18:29
  • This fonction gives me a table with the r.id associated withe very s.id. As my table have more than 150 000 points, the returning table is enormous... Instead of a fonction that gives you the distance of 1 point to all the other point (and return a table like that) is there a possibility to have a fonction where the result for a row would be the id (column 1) and the avg distance for this point to all other points (column 2). The fonction would have to first calculate all the matches distances for that ID and than calculate the average. – Alex Girard Jun 21 '16 at 13:56
  • try it without the references to s.id... answer has been modified. – kttii Jun 21 '16 at 14:09
  • It's working! Tho I do have a concern. As i work in EPSG 2950, my reference unit is meter. Yet in the ST_DWithin, you used 2 as the reference to 2KM...Shouldn't I use 2000 instead? – Alex Girard Jun 22 '16 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.