6

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?

5
  • 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, 2014 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. Aug 7, 2014 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, 2014 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, 2014 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, 2015 at 15:24

1 Answer 1

1

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.

SELECT DISTINCT ON(a.id)  
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
5
  • This is great ! How could this be extended to find the maximum nearest distance ? Apr 19, 2016 at 7:39
  • Swap out avg for max
    – raphael
    Apr 19, 2016 at 15:21
  • Awesome ! Could this be written to return to return the max directly? I'm doing this on all my lines Apr 19, 2016 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, 2016 at 22:07
  • Added here (also edited) gis.stackexchange.com/questions/190237/… Apr 19, 2016 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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