This post is part of a series entitled Spatial is not Special, where I will illustrate how spatial constructs in SQL provide us with a rich toolset for geographers doing spatial analysis. To illustrate these concepts, I will be showing examples from my book Statistical Problem Solving in Geography. Even though PostGRES, SQLServer, MySQL, spatialite, and Oracle all support spatial SQL, these examples will be presented using Manifold GIS. The example dataset is a Manifold 8.0 .map file and can be found here.
As we continue to move through my book Statistical Problem Solving in Geography, we come to another important descriptive spatial statistic called the standard distance. Following our example from the book, we see the computation presented as:
The SQL for computing standard distance is:
SELECT sqr(SUM(x^2)/(SELECT COUNT(*) FROM Points) – avg(x)^2 + SUM(y^2)/(SELECT COUNT(*) FROM Points) – avg(y)^2) FROM Points
The inner portion of the query is computing the sum of the squared X coordinates and dividing it by the number of points by using the COUNT(*) function. We also subtract the average squared X coordinate. Also, we do the same thing for the Y coordinates. The proper placement of parentheses allow us to wrap that calculation inside the square root function. I’ve colored the parentheses to help understand which ones correspond to one another – hopefully the colors are more helpful than distracting.
So, this gets us the standard distance. But, you might be interested in creating an area feature to place on a map. This brings us back to our very first post in this series – the mean center. This is going to look messy, but it’s really quite easy.
SELECT buffer(newpoint(avg(x),avg(y)), (SELECT sqr(sum(x^2)/(SELECT COUNT(*) FROM Points) – avg(x)^2 + SUM(y^2)/(SELECT COUNT(*) FROM Points) – avg(y)^2) FROM Points)) FROM Points
Our previous query for standard distance returns a value, right? So, we are going to use that query as a sub-query which I’ve italicized and made in medium gray for clarity. We can then take our mean center query and wrap that into the BUFFER function. This is another one of those spatial constructs we’ve talked about. The BUFFER function requires a geometry and a distance [buffer([geometry],distance)]. So, for the [geometry] field, we are passing our mean center, and for our distance, we are passing our standard distance. Probably the hardest thing about this is getting the parentheses correct!!!
Hopefully, with the gray text, you can see how easy it is to take the results from the standard distance query along with the mean center geometry, and just insert it into the buffer function.