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.

Table4_2

Using our example dataset, and some basic SQL, we can easily generate the code necessary to compute the mean center,

SELECT avg(x),avg(y)
FROM Points

The SQL function avg allows us to compute the average of both the x, and y data columns.  However, adding the NewPoint function takes both the average x and y coordinates and turns them into a geometry object, and is easily added to the existing SQL syntax:

SELECT NewPoint(avg(x),avg(y))
FROM Points

The next post will examine how to calculate the weighted mean center using SQL.  And don’t worry, these examples are going to get more complicated as we go along.

 

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