MapInfo Pro

 View Only
  • 1.  MapInfo Monday: Calculating Center of Locations

    Employee
    Posted 04-10-2023 05:24

    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.


    I also tried calculating the center using the average of the coordinates for the locations back in 2000. The center is shown as the red diamond. As you can see the few locations to the north are not able to move the center out of the south because the majority of locations are in the south.

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


  • 2.  RE: MapInfo Monday: Calculating Center of Locations

    Posted 04-13-2023 18:44

    Hi Peter, another consideration would be to use Centre of Minimum Distance, weighted by number of employees at each site (ie minimising total KMs travelled). This would require a fairly simple MapBasic program to achieve.



    ------------------------------
    Tim Mashford
    Knowledge Community Shared Account
    ------------------------------



  • 3.  RE: MapInfo Monday: Calculating Center of Locations

    Employee
    Posted 04-14-2023 05:31

    Yeah, using actual drive distances, or times, would be a better way to do it. Not as easy but better.

    How would you do that?

    I was considering creating a distance matrix between all the locations and using that in some way. But maybe there is a better way I can't see.



    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 4.  RE: MapInfo Monday: Calculating Center of Locations

    Posted 04-15-2023 22:10

    Centre of Minimum Distance can be estimated using a fine grid, for each cell calculate the sum of the distances from the cell centre to each point (weighted/multiplied by staff count if required). The cell with the lowest value represents the location where total travel is minimised. It will likely be close to the location you found using the average X and average Y coordinates, but just a bit more refined to minimise the total travel distance.

    And yep using DriveTime distances (or travel times) instead of straight lines would be even better.



    ------------------------------
    Tim Mashford
    Melbourne, Australia
    ------------------------------



  • 5.  RE: MapInfo Monday: Calculating Center of Locations

    Employee
    Posted 04-17-2023 03:55

    Thanks, Tim, I took your advice and implemented the process using a Select statement in MapInfo Pro.

    read the full post here: MapInfo Monday: Calculating Center of Locations - 2



    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------



  • 6.  RE: MapInfo Monday: Calculating Center of Locations

    Posted 04-18-2023 02:03

    All this is basically a center of mass calculation, which is done exactly as an average of coordinates, weighted by mass or number of employees as in this example.

    Using a grid is just a very complicated way of doing the same thing. The only advantage is with lat/long coordinates closer to the poles, where the simpler approach with averages starts failing.



    ------------------------------
    Uffe Kousgaard
    ROUTEWARE
    Roskilde
    ------------------------------



  • 7.  RE: MapInfo Monday: Calculating Center of Locations

    Posted 04-18-2023 23:22

    They're not the same though.

    Taking the average of the X coordinates, and the average of the Y coordinates, and using these averages to create a point location won't give you the minimum total distance to all points. It will be close, but the grid method can refine the answer.

    (Whether it's worth the extra effort is another question. But it's a different process.)

    I just ran it on a dataset of ~100 points, spread over ~30km. Using the average X and average Y coordinates, the total distance to all locations was 467km. Using the grid method (200m cells) to find the centre of minimum distance, the total distance to all locations was reduced to 448km.



    ------------------------------
    Tim Mashford
    Melbourne, Australia
    ------------------------------