Classic ARC/INFO Commands in PostGIS: Nearest Neighbor

Our previous post showed how to identify the central feature in a geographic layer using PostGIS.  Today’s post will show how to calculate the nearest neighbor index. Recall from An Introduction to Statistical Problem Solving in Geography (third edition), the formula and computation of the nearest neighbor index.

This isn't rocket science, but you'll start to see that things will start to get a little more complex as we build this one out. Again, let's do this in stages.

this query simply uses ST_Distance to obtain the distances from each feature to every other feature. But, we don't want to calculate the distance from a feature to itself, so we put in a the little clause where a.point <> b.point. Then, we order the data by the feature name and the distance.

The next thing we'll do is take the previous query, and wrap it in a another query where we select the minimum of those distances and group them by each unique point value. So, what this gives us are N values representing the minimum distance of each point to its closest point. To obtain the minimum value, we use the MIN function, and GROUP BY function return the minimum distance for each unique point.

Now that we have the minimum distance to each point, we simply wrap that query inside another query and return the average of the N distances: