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