Happy #MapInfoMonday!
In today's article, we will discuss some options for calculating the center of several locations.
Imagine this story: Ajax Germany has since back in 2000 had a yearly meeting for its employees. This meeting has always taken place in the city of Schwabisch Hall in the southern part of Germany. Back then, Ajax Germany mostly had offices in the southern part of Germany with just a handful of locations in the north too. But over the last 20 years, they have been opening offices in the central region as well.
In this map, you can see the location of the current meeting marked with a red diamond, the current office locations marked with a blue dot, and the original offices marked with a blue circle.
Ajax Germany is now reconsidering the location for the yearly meeting. I'll give you three ways to calculate the center of the locations. I'm keeping this simple so I'm not taking drivetimes into consideration and I'll just be using straight-line distances.
I'll be using a combination of SQL and MapBasic to find and show the result of these calculations.
Centroid of the Locations
The first method is using a very simple calculation. I'm using the minimum and maximum coordinates and finding the center, or centroid, of these.
Here is the small script I have used to do this calculation:
Set Coordsys Table German_Offices_2020
Select Min(CentroidX(OBJ)) As "MinX", Min(CentroidY(OBJ)) As "MinY"
, Max(CentroidX(OBJ)) As "MaxX", Max(CentroidY(OBJ)) As "MaxY"
From German_Offices_2020
Into q1 NoSelect
Fetch First From q1
Dim fMinX As Float
Dim fMinY As Float
Dim fMaxX As Float
Dim fMaxY As Float
fMinX = q1.MinX
fMinY = q1.MinY
fMaxX = q1.MaxX
fMaxY = q1.MaxY
Insert Into WindowInfo(FrontWindow(), WIN_INFO_TABLE)
(OBJ)
Values (CreateLine(fMinX, fMinY, fMaxX, fMaxY))
Insert Into WindowInfo(FrontWindow(), WIN_INFO_TABLE)
(OBJ)
Values (Centroid(CreateLine(fMinX, fMinY, fMaxX, fMaxY)))
Initially, I set MapInfo Pro to use the coordinate system of the input table. I then use a query to calculate the minimum and maximum coordinates of the locations. Notice that I use the functions CentroidX() and CentroidY() to extract the values from the points.
After running the SQL Select statement, I read the coordinates into four variables so that I can use the coordinates in my final statements.
Finally, I insert two objects into the cosmetic layer of my map window:
I insert a line drawn between the minimum coordinate and the maximum coordinate using the CreateLine() function.
And I insert a point representing the Centroid of this line. I used the function Centroid() to extract the point from the line.
Notice that I use this expression to get to the name of the cosmetic layer of the active map window: WindowInfo(FrontWindow(), WIN_INFO_TABLE). This will fail if you don't have a map window as the active window.
This point represents the geographical center of the locations. But as it only considers the minimum and maximum coordinates, it doesn't handle outliers very well as they get weighted just as much and an area with many locations.
Average of the Locations
The second method calculates the average coordinates from the locations. I have kept the line between the minimum and maximum coordinates for reference as that helps you see the difference between the three methods.
Here's the script I used:
Set Coordsys Table German_Offices_2020
Select Min(CentroidX(OBJ)) As "MinX", Min(CentroidY(OBJ)) As "MinY"
, Max(CentroidX(OBJ)) As "MaxX", Max(CentroidY(OBJ)) As "MaxY"
, Avg(CentroidX(OBJ)) As "AvgX", Avg(CentroidY(OBJ)) As "AvgY"
From German_Offices_2020
Into q1 NoSelect
Fetch First From q1
Dim fMinX As Float
Dim fMinY As Float
Dim fMaxX As Float
Dim fMaxY As Float
fMinX = q1.MinX
fMinY = q1.MinY
fMaxX = q1.MaxX
fMaxY = q1.MaxY
Insert Into WindowInfo(FrontWindow(), WIN_INFO_TABLE)
(OBJ)
Values (CreateLine(fMinX, fMinY, fMaxX, fMaxY))
Dim fAvgX As Float
Dim fAvgY As Float
fAvgX = q1.AvgX
fAvgY = q1.AvgY
Insert Into WindowInfo(FrontWindow(), WIN_INFO_TABLE)
(OBJ)
Values (CreatePoint(fAvgX, fAvgY))
As you can see a lot of the script is similar to the first. This is mainly because I want to draw the line between the minimum and maximum coordinates.
The important difference lies in the SQL Select statement where I now also calculate the Average of the coordinates for the locations using the Avg() aggregate.
And at the end of the script where I insert a new point into the cosmetic layer. I create the point using the CreatePoint() function.
In the map below, you can see the center of the locations calculated using the average of the coordinates. This will often be a better result as single outliers won't skew the result as much. Also, notice that this location has been placed more to the southwest compared to the first approach.
Weighted Average of the Locations
In the second approach, I used the average of the coordinates. This is best if the locations I compare are of the same size, ie. there is the same amount of employees. For Ajax Germany, this is not the case. The older offices in the south have more employees than the newer ones. And some of the offices in the major cities have even more employees.
To take the number of employees into account, I can use the Weighted Average, or WtAvg() as the name of the aggregate is.
Below you can see the script:
Set Coordsys Table German_Offices_2020
Select Min(CentroidX(OBJ)) As "MinX", Min(CentroidY(OBJ)) As "MinY"
, Max(CentroidX(OBJ)) As "MaxX", Max(CentroidY(OBJ)) As "MaxY"
, WtAvg(CentroidX(OBJ), Employees) As "AvgX"
, WtAvg(CentroidY(OBJ), Employees) As "AvgY"
From German_Offices_2020
Into q1 NoSelect
Fetch First From q1
Dim fMinX As Float
Dim fMinY As Float
Dim fMaxX As Float
Dim fMaxY As Float
fMinX = q1.MinX
fMinY = q1.MinY
fMaxX = q1.MaxX
fMaxY = q1.MaxY
Insert Into WindowInfo(FrontWindow(), WIN_INFO_TABLE)
(OBJ)
Values (CreateLine(fMinX, fMinY, fMaxX, fMaxY))
Dim fAvgX As Float
Dim fAvgY As Float
fAvgX = q1.AvgX
fAvgY = q1.AvgY
Insert Into WindowInfo(FrontWindow(), WIN_INFO_TABLE)
(OBJ)
Values (CreatePoint(fAvgX, fAvgY))
This is script is almost identical to the second example. The only difference is that I used the WtAvg() aggregate in the SQL Select statement. The WtAvg() aggregate also needs a second condition: weight. In my example, I have used the Employees column.
Below, you can see the center of the locations marked with a red diamond using a weighted average calculation.
And it may become even clearer if the map also shows the number of employees at each location.
Using this last calculation, Ajax Germany decided to move the location of their yearly meeting around 80 kilometers to the north - to Würzburg.
As I mentioned at the beginning these 3 methods all use basic calculations to determine the center. If you want to include drive time information, you should have a look at MapInfo RouteFinder.
Ajax Germany is a company invented for this use case.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------