A year back, you assigned areas to the service engineers you had. Then time went by, and things changed. One of the engineers resigned, and a new one was hired. One moved to a different side of the city. The company received more service assignments in certain areas but lost traction in others. A new major road was finished in the central part of your area.
Things rarely remain static for long, and your planning should reflect these changes.
You are looking at your map and wondering whether some areas should be assigned to a different service engineer.
To start, you create drive-time polygons around your service engineers to understand their reach.
You add these to the map and color them with a light color for the 15-minute drivetime polygons, and with darker colors for the longer drivetime polygons up to 60-minutes.
The thick red line illustrates the districts, or the area assigned to each service engineer.
Just by looking at the map, you can see smaller drivetime zones (30 minutes) for a service engineer that stretch into the neighboring district. This illustrates that areas have been assigned to an engineer who has a longer drive to these areas. A few examples:
So, there is no doubt that the distribution of areas can be improved to reduce drive times.
But how can you do that? Or how can you do that in a way that is at least to some degree automated?
Happy #MapInfoMonday!
The idea here is to use the spatial relationship between the areas and the drivetime polygons.
Below, you can see the result of a query that finds the relationships between all the areas and all the drivetime polygons. Many areas will overlap several drive-time polygons. In this case, we will take the drivetime zone with the shortest distance, and if several service engineers can reach an area in the same time, we will use the service engineer whose drivetime polygon has the greatest overlap with the area.
The above query will find the relationship for all the various drivetime polygons. We need, however, to do this for a single distance at a time. As we have four distances - 15, 30, 45, and 60 - we need to run the query 4 times. By starting with the shortest distance, we ensure that service engineers are assigned based on distance.
So, the query needs to be extended to include a distance condition, and we need to ensure that a service engineer hasn't already been assigned to the area.
To fulfill the last condition, we ensure that the columns NameNew and DistanceNew are reset to "" and 0, respectively. This is in the query used to check if the area has been assigned to a service engineer.
Here's what that looks like in practise. The query we will use and the update statement look like this:
Select sa.postsect, sa.NameNew, sa.DistanceNew, dt.NAME
, dt.value, ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlap"
From Service_Areas As "sa", Service_Engineers_overlap_DT As "dt"
Where sa.Obj intersects dt.Obj
And dt.value= nValue
And sa.DistanceNew = 0
Order by sa.postsect, PropOverlap
Into _to_update
Update _to_update
Set NameNew = Name,
DistanceNew = value
From the areas, we query out the postal sector, and two columns to hold the new values for the service engineer name NameNew, and the drivetime distance DistanceNew. Since we don't overwrite the current values directly, we can perform analysis afterward to identify the changes. We also include the Name column from the drivetime polygons and the value column that holds the distance. And finally, we include the expression ProportionalOverlap, which is a built-in MapInfo function that returns a decimal between 0 and 1 representing how much of the first polygon is covered by the second.
For conditions, we make sure the two polygons intersect, that the drivetime distance, the value column, matches the value for the current statement: 15, 30, 45, or 60. This is implemented as a variable that asks the user for the current value. And finally, we make sure that this area hasn't already been assigned to a service engineer. We do this by making sure the DistanceNew column equals 0.
We sort the result by the postsect column and by the proportional overlap value. By sorting by the proportional overlap, we ensure that the smallest values are at the top of the result and the largest values are at the end. This also means that when we run the update statement, the smallest overlaps in an area are updated first, and if there is also a relation with a larger overlap, it is updated later and overwrites the smaller overlaps.
With this query ready, we can run it four times.
When you run the query, the variable will prompt MapInfo Pro to show a dialog to the end user.
The user will enter the current drive time value here: 15.
When I ran each of these, a small dialog appeared asking me which distance I wanted to use. I skipped inserting each of those dialogs.
There are some areas that aren't within a 60-minute drive time of any of the engineers. For these, I assigned the value "Not Assigned" rather than leaving the field empty. Empty values would disrupt the theme and shift the colors unexpectedly. The choice of wording was also deliberate: "Not Assigned" sorts alphabetically after the engineer names, all of which begin with M, keeping the theme legend in a predictable order.
To visualize the changes, we can switch the theme from using the column Name to use the column NameNew, which has just been updated in our process.
You can see where a color spills into another district across one of the thick red polylines; there has been a change in the assignment. Also, compare this map to one of the first maps in this article.
You can also run a query to highlight all the areas where the Name <> NameNew and NameNew <> "". That will result in a map like this, highlighting the changes.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------