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.

Fig44

In this case, the attribute field we are interested in is a column named f. We used f to represent the term frequency (it’s what statisticians do).  Calculating the weighted mean center is slightly more complicated than yesterday’s example of the mean center, but is still rather easy to accomplish with SQL.

SELECT    sum(x*f)/(SELECT sum(f) FROM Points),
                  sum(y*f)/(SELECT sum(f) FROM Points)
FROM Points

Notice that the use of the sum function will sum the product of the coordinate value and the frequency field.  This must be divided by the sum of all the frequencies, therefore we are required to issue a second SQL statement to obtain the sum of the frequencies (please note, some SQL engines like that in SQLServer or Oracle allow the user to store variables, so you could obtain the sum of the frequencies and then use that later in the SQL statement – but this is fairly straightforward so we did it all in a single SQL statement).

And once again, we can convert the x,y coordinates into a geometry by using the NewPoint function in our SQL engine:

SELECT    NewPoint(num(x*f)/(SELECT sum(f) FROM Points),
                   sum(y*f)/(SELECT sum(f)) FROM Points)
FROM Points

Our next post will examine how to calculate the central feature in a dataset using SQL.

2 thoughts on “Spatial is Not Special – Weighted Mean Center

  1. Thanks for the post. Just a quick question, why do you use Manifold which is owned by very few people and costs a bucket instead of a free alternative like QGIS – that would surely make it much more accessible! Interesting tutuorial in any case and great to see reproducibility of findings. Robin (Leeds, UK)

  2. Hi Robin,
    Thanks for your comment. I am using Manifold because it has a really good spatial engine with SQL. Also, a lot of my work is done in Manifold, and I have probably a thousand people who have used my previous training products for Manifold. Also, QGIS does not have a spatial SQL engine – for that, you have to use PostGIS. PostGIS is great, but it will require the users to install it. I suppose a PostGIS server on AWS might make things easier in that regard, but for now, my focus are on the Manifold users who have been so supportive of my work.

    Now, that being said, you should get out of the GIS field and move into a field that predicts the future (ha, ha)!!! I say that because after I am done with these posts, I am going to also recreate the SQL in PostGIS, spatialite, and SQLServer. So, you were spot on in your comment.

    I really like spatialite, but unfortunately, you have to add the spatial indexes directly in the SQL, so that makes the code a little more cumbersome. PostGIS is good because it, like Manifold and SQLServer (and others), takes care of the spatial indexes natively.

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