Classic ARC/INFO Commands in PostGIS: Weighted Mean Center

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

We'll solve this one in steps, as well, so you know how the SQL functions can build on one another.

In the formula above, you'll see that we have to multiply each coordinate by an attribute value (in this case, the f value, representing frequency. And, we have to sum the product of those values and divide by the total sum of the frequencies:

the above query returns the X and Y values from the geometry just like we did last week, but it also multiplies that value by the frequency. That's actually the numerator in the equation. The denominator of the equation represents the sum of the frequencies. So what we have are the weighted averages for both the X and Y coordinates.

And just like we did last week, we have to turn those weighted averages for X and Y into geometries:

and just like last week, we now have a geometry, but not an assigned coordinate system. So, you can return to our post of DEFINE PROJECTION and assign a projection for the result.