Spatial is Not Special – Nearest Neighbor Continued

The last post illustrated how to create a Nearest Neighbor Index (NNI) for a set of points using SQL.  However, make any inferential observations, we need to compare the actual nearest neighbor distance to a theoretical random distribution.  Chapter 14 in our book shows that the theoretical distribution is 1 divided by the 2 times the square root of the density:

1/2*sqr(Density)

The Density is simply the number of points divided by the area.  So, we can compute the density as:

SELECT count(*)/Area(BoundingBox(AllCoords(points.[Geom (I)]))) FROM points

In the above query, AllCoords takes the individual points and converts them to a multipoint object. Wrapping the multipoints into the BoundingBox function creates a bounded area of all the points.  We then use the Area function to determine the area of the bounding box.  Finally, the Count(*) returns the number of points in the dataset.  So, we divide the number of points by the area of the features.

That gets us part of the way there.  We then have to take the square root of the result, multiply it by two, and then take the reciprocal (divide it into 1) as:

SELECT 1/(2*sqr(count(*)/Area(BoundingBox(AllCoords(points.[Geom (I)]))))) FROM points

Now, that gets us the theoretical result.  We then divide the actual NNI into the theoretical to get the R value.  The code below isn’t formatted real well, but it is basically taking what we did today, and dividing it into what we did in the last point to get the R value:

SELECT AVG(dist) /(SELECT 1/(2*sqr(count(*)/Area(BoundingBox(AllCoords(points.[Geom (I)]))))) FROM points) FROM ( SELECT min(dist) AS dist, a.pt AS PT, first(b.pt) AS NN FROM ( SELECT a.pt, b.pt,distance(a.[Geom (I)],b.[Geom (I)]) AS dist FROM points AS a, points AS b WHERE a.pt <> b.pt ORDER BY dist ) GROUP BY a.pt )