Classic ARC/INFO Commands in PostGIS: Central Feature

Our previous post showed how to extend the mean center of a geographic dataset to incorporate the weighted mean center in PostGIS.  Today’s post examines the SQL code necessary to generate the central feature for a geographic data set.  Recall from my textbook An Introduction to Statistical Problem Solving in Geography (third edition), the formula and computation of the Central Point.

This is where the power of spatial SQL gets used.  As you can see, finding the central point requires us to compute a symmetrical matrix of the distance between each point and every other point. For our 7 point example, that’s 49 computations!  With spatial SQL, the ST_Distance function allows us to determine the distance between each of the points such as:

this is the base of our query. We'll now add to that by getting the sum of all the distances for each point:

This is the same query we entered earlier, except that we've included the function SUM. SUM is what is known as an aggregate query - we've used it before. But, instead of one value like we did with the mean center, we want to aggregate those sums for each unique point. That is what the GROUP BY function does - for each point, it obtains the sum of the distances to every other point. So how do we get the shortest distance? We simply sort the distance (ORDER) in ascending order, and grab the first value using the LIMIT clause. The results are: