Spatial is not special – Weighted 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 extend the computation of the standard distance to that of the weighted standard distance. Following our example from the book, we have an attribute column called f, which represents the frequency.  The computation is presented as:

table4_6

The SQL for computing the weighted standard distance is:

SELECT sqr(
                       SUM(f*x^2)/(SELECT sum(f)
                                            FROM Points)(SUM(x*f)/(SELECT sum(f)
                                                                                 FROM Points))^2
+ SUM(f*y^2)/(SELECT SUM(f)
                                         FROM Points)(SUM(y*f)/(SELECT sum(f)
                                                                                       FROM Points))^2
                           )
FROM Points

This is almost identical to the standard distance computation, but instead of dividing by the number of features, we divide by the total frequency. We also subtract the weighted mean center coordinates, instead of simply the mean center.  And finally, we multiply each coordinate by the frequency. Once again, 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.  This is what is so nice about having a worked example – putting this into SQL took me a little time because of the parenthesis issue.

So, this gets us the weighted standard distance. LIke the previous example of weighted mean center, we can wrap the query in the buffer function to create a geometry of the weighted standard distance centered on the weighted mean center.

SELECT buffer(newpoint((SUM(x*f)/(SELECT sum(f)
FROM Points))^2,
(SUM(y*f)/(SELECT sum(f)
FROM Points))^2),
                                          sqr
                                             (SUM(f*x^2)/(SELECT sum(f)
                                             FROM Points) – (SUM(x*f)/(SELECT sum(f)
                                                                                          FROM Points))^2
                                             + SUM(f*y^2)/(SELECT SUM(f)
                                                                     FROM Points) – (SUM(y*f)/(SELECT sum(f)
                                                                                                  FROM Points))^2))
        FROM Points

Remember from earlier posts that 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. Again, the hardest thing about this is getting the parentheses correct!!!  Always work with a small example so you can make sure your answers are correct – this will scale just fine once you have it working.

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.

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

Continue reading

Spatial is Not Special – Central Feature

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.

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

Spatial is Not Special – Weighted Mean Center

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 bookStatistical 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.

In our previous post we saw how easy it was to compute the mean center of a geographic dataset with SQL.  Today’s post examines the SQL code necessary to generate the weighted mean center for a geographic data set.  Recall from Statistical Problem Solving in Geography (third edition),  the formula and computation of weighted mean center and the 7 point data set used. Continue reading

Spatial is not Special – Mean Center

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.

Today’s post examines the SQL code necessary to generate the mean center for a geographic data set.  Recall from Statistical Problem Solving in Geography (third edition),  the formula for mean center and the 7 point data set used. Continue reading

SQL Examples for Statistical Problem Solving in Geography

I have spent the last few years advocating the benefits of SQL, and in particular spatial constructs in SQL for solving geographic problems.  Why all the fuss?  Quite simply, I think the use of spatial constructs in SQL is one of the most powerful tools available to geographers.  Last year, I taught a couple of workshops entitled Spatial SQL: A Language for Geographers.  These workshops were well received, and most of those in attendance were unaware of the power that spatial SQL has to offer – and why would they know, the GIS industry does not really talk about this. Continue reading