MapInfo Pro

Happy #MapInfoMonday!

I received excellent feedback on last week's post MapInfo Monday: Calculating Center of Locations from @Tim Mashford.@Tim Mashford

I wanted to try Tim's suggestion, which ended up as this follow-up article.

Tim wrote: "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."

Tim also suggested creating a small Mapbasic application to do this. I wanted to see if this could be done using the SQL Select statement.

I used the GridMaker tool to create a 10 by 10 km grid covering my area of interest. I have earlier written an article about using the GridMaker tool.

To calculate the distance between all grids and all offices, I need to use the Select statement with the Cross Join keywords as you can see below:

Select Sum(ObjectDistance(o.Obj, Centroid(g.Obj), "km")) As "Distance_km"
, AggregateCombine(g.Obj)
From German_Offices_2020 As "o", Grid_Ajax_Germany As "g" Cross Join
Where o.Place_Name <> g.Description
Group By g.Description
Into Center_Minimum_Distance_Wgt

Let me walk you through the statement.

As input tables, I specify the offices - German_Offices_2020 - and the grid cells - Grid_Ajax_German. Notice that I use aliases for the tables which allow me to use this instead of the actual table name in the statement. I notice the Cross Join keyword after the two tables. That is also quite important.

I also have to specify a join condition. As I'm building a Cross Join statement, I'm more flexible here than I have been used to. As I want to combine all the offices with all the grid cells, I need to come up with a join condition that ensures this. I could have an integer column in both tables with the same value for all records, say 1. But I can also specify a condition where the values can't be the same. This gives me more flexibility. So I specified that the name of the office should be different from the grid description: o.Place_Name <> f.Description.

I group the result by the description for the vector grid as this will group all the distances for each grid cell.

And finally, I need to specify what columns/values to return. I need to do some distance calculations and return the value from this calculation per grid cell. I use the ObjectDistance() function to calculate the nearest distance from each office to the grid cell. Instead of using the grid cell, I specify the distance should be calculated to the centroid of the grid cell: Centroid(g.OBJ). And finally, I specify to aggregate these values using the Sum() aggregate and I give the result a column alias: Sum(ObjectDistance(o.Obj, Centroid(g.Obj), "km")) As "Distance_km" 

Normally, MapInfo Pro will remove the spatial objects when you use Group By in your query. Since MapInfo Pro v2019, you can however specify a spatial aggregate to keep the spatial objects - or create new spatial objects for the result set. I use AggregateCombine() to combine the grid cells for each row: AggregateCombine(g.Obj).

Below you can see the query and the result. I'm using 10 by 10 km grid cells resulting in a total of approximately 7000 grid cells. Joining these to the 275 offices results in almost 2 million distance calculations. This takes a bit more than 2 minutes on my laptop.

To highlight the location with minimum distance for all offices, I add a theme to my map. The light area in the center is the best location considering the location of all offices and a straight line distance.

Now the calculation above doesn't take into account the number of employees at each location. We can of course also do this in our calculation.

Select Sum(ObjectDistance(o.Obj, Centroid(g.Obj), "km") * o.Employees) As "Distance_km"
, AggregateCombine(g.Obj)
From German_Offices_2020 As "o", Grid_Ajax_Germany As "g" Cross Join
Where o.Place_Name <> g.Description
Group By g.Description
Into Center_Minimum_Distance_Wgt

This statement is very similar to the one above. This only difference lies in the calculation for the Distance_km where I have multiplied the result by the number of employees and then summarized the result: Sum(ObjectDistance(o.Obj, Centroid(g.Obj), "km") * o.Employees) As "Distance_km".

 This does however have an effect on the result. As you may recall from the article last week, the older office locations in the south of Germany have typically more employees compared to the newer offices in the center and to the north. The center has been dragged around 100 km to the south.

You don't have to use such a fine grid. You can achieve a similar result using a larger grid size. Below you can see the result if I use a 50 km grid size. Note that I have used the Layer List to turn off the cells with the largest distances. The benefit of using larger grids is that the calculation takes far less time. less than 4 seconds compared to approximately 140 seconds with the 10 km grid cells.

The result of the weighted Center of Minimum Distance is very similar to the result I got last week when using the weighted method too. The resulting center has Würzburg in the top right corner.

Thanks, @Tim Mashford for bringing this method to my attention.