Spatial is Not Special – Standard Distance

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:

fig4_5

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.

3 thoughts on “Spatial is Not Special – Standard Distance

  1. Art, your first SQL example “FROM Points) – avg(x)^2” uses a hyphen not a minus character. C&P the code into Manifold, it fails on the hyphen.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s