I have three series (or tables) of points in PostGIS which I want to compare. I would like to know how "close" each series is when compared to the other two. In other words I would need some measure such as the average of the average distance of each point with all the others (that is, the average of the distance matrix)... Sounds complicated, but I am sure some common statistic exist to compare series of points.

How my query would look like?

  • 2
    If you select as your measure the root mean squared distance among the points then the complexity of the calculation reduces from O(N^2) to O(N), because the RMSE distance equals sqrt(2N/(N-1)) times the RMSE distance between the N points and their centroid. – whuber Aug 7 '14 at 1:18
  • If you do it your way you'll end up in a situation where comparing a set to itself will end up with non-zero distance. That is you will compare how close the points in the set are not only between two sets. – Jakub Kania Aug 7 '14 at 12:28
  • @Jakub A common and effective way to measure the degree of clustering within a set of points that has been partitioned into "clusters" is to use some measure of average distance within any point set. Comparing the measure for the entire set to the sum (or some other appropriate combination) of the measures of the clusters assesses the amount of clustering. Thus your comment, which might be read by some as an objection to Francesco's procedure, actually points out why it is an interesting and useful one. – whuber Aug 7 '14 at 14:40
  • I dont know what is the point of this calculations, but if you only want to know whether or not points in set A are closer to each-other then points in set B maybe the solution will be to count area of their hull (ST_Area + ST_ConcaveHull) – Jendrusk Aug 8 '14 at 8:20
  • Is there a reason you want the average of the average of ALL the distances between points? Each average calculation will be distorted by the maximum distance between features, when all sets of features may be rather close together. – raphael Dec 23 '15 at 15:24

As I mentioned in a comment, the average distance will be distorted by the greatest distances between points. For example, you could have a point cloud that is 100 km across, that you then shift laterally by 1 m to form a new table. The average distance for any point on table A to table B will be closer to 100 km than 1 m. The nearest distance for any point on table A to table B will be 1 m, and the average nearest distance will be 1 m.

With that aside, I'll show how to calculate the average of the nearest distance, which you can then extend as you please.

a.id AS a_id, b.id AS b_id, ST_DISTANCE(a.geom, b.geom) as distance  
INTO a_b_distances  
FROM a, b  
ORDER BY a.id, a.geom <-> b.geom

And then to calculate the average nearest distance:

SELECT avg(distance)  
FROM a_b_distances
| improve this answer | |
  • This is great ! How could this be extended to find the maximum nearest distance ? – DaynaJuliana Apr 19 '16 at 7:39
  • Swap out avg for max – raphael Apr 19 '16 at 15:21
  • Awesome ! Could this be written to return to return the max directly? I'm doing this on all my lines – DaynaJuliana Apr 19 '16 at 18:51
  • 1
    At this point you may want to ask your own question and I'll be happy to try answering it, we'll get better detail than this back and forth in the comments section – raphael Apr 19 '16 at 22:07
  • Added here (also edited) gis.stackexchange.com/questions/190237/… – DaynaJuliana Apr 19 '16 at 22:25

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.