We have been looking at assigning service areas a few times by now. I think this is the last in the series - unless I learn otherwise.
Until now, we have done the assignment one step at a time. Mostly so that I, and you as well, could see what happens at each step.
In this article, I'll put all the steps into a single script so that it's easy to run again and again.
I'll add a few comments at the end.
Let's get to it.
Combining All Steps into a Single Script
Instead of running the process once for each drivetime distance, we will run them sequentially in a single script.
I have also added a few additional steps to try to capture all the service areas.
Here is the script that can be run from the SQL window in MapInfo Pro. As you can see, the code repeats itself. We run the same query and update 5 times. The difference lies in the value we assign to the variables nValue and fOverlapPct.
Dim nValue As Integer
Dim fOverlapPct As Float
Set Coordsys Table Service_Areas
Print "Resetting New columns"
Update Service_Areas Set NameNew = "", DistanceNew = 0, PropOverlapNew = 0
nValue = 15
fOverlapPct = 0.5
Print "Drivetime: " + nValue + " min, Overlap %: " + Format$((fOverlapPct * 100), "#.###")
Select sa.postsect, sa.NameNew, sa.DistanceNew
, sa.PropOverlapNew, dt.NAME, dt.value
, ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlap"
From Service_Areas As "sa", Service_Engineers_DT As "dt"
Where sa.Obj intersects dt.Obj
And dt.value= nValue
And sa.DistanceNew = 0
And ProportionOverlap(sa.Obj, dt.Obj) >= fOverlapPct
Order by sa.postsect, PropOverlap Asc
Into _to_update NoSelect
Update _to_update
Set NameNew = Name,
DistanceNew = value,
PropOverlapNew = PropOverlap
nValue = 30
fOverlapPct = 0.5
Print "Drivetime: " + nValue + " min, Overlap %: " + Format$((fOverlapPct * 100), "#.###")
Select sa.postsect, sa.NameNew, sa.DistanceNew
, sa.PropOverlapNew, dt.NAME, dt.value
, ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlap"
From Service_Areas As "sa", Service_Engineers_DT As "dt"
Where sa.Obj intersects dt.Obj
And dt.value= nValue
And sa.DistanceNew = 0
And ProportionOverlap(sa.Obj, dt.Obj) >= fOverlapPct
Order by sa.postsect, PropOverlap Asc
Into _to_update NoSelect
Update _to_update
Set NameNew = Name,
DistanceNew = value,
PropOverlapNew = PropOverlap
nValue = 45
fOverlapPct = 0.5
Print "Drivetime: " + nValue + " min, Overlap %: " + Format$((fOverlapPct * 100), "#.###")
Select sa.postsect, sa.NameNew, sa.DistanceNew
, sa.PropOverlapNew, dt.NAME, dt.value
, ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlap"
From Service_Areas As "sa", Service_Engineers_DT As "dt"
Where sa.Obj intersects dt.Obj
And dt.value= nValue
And sa.DistanceNew = 0
And ProportionOverlap(sa.Obj, dt.Obj) >= fOverlapPct
Order by sa.postsect, PropOverlap Asc
Into _to_update NoSelect
Update _to_update
Set NameNew = Name,
DistanceNew = value,
PropOverlapNew = PropOverlap
nValue = 45
fOverlapPct = 0.25
Print "Drivetime: " + nValue + " min, Overlap %: " + Format$((fOverlapPct * 100), "#.###")
Select sa.postsect, sa.NameNew, sa.DistanceNew
, sa.PropOverlapNew, dt.NAME, dt.value
, ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlap"
From Service_Areas As "sa", Service_Engineers_DT As "dt"
Where sa.Obj intersects dt.Obj
And dt.value= nValue
And sa.DistanceNew = 0
And ProportionOverlap(sa.Obj, dt.Obj) >= fOverlapPct
Order by sa.postsect, PropOverlap Asc
Into _to_update NoSelect
Update _to_update
Set NameNew = Name,
DistanceNew = value,
PropOverlapNew = PropOverlap
nValue = 45
fOverlapPct = 0.05
Print "Drivetime: " + nValue + " min, Overlap %: " + Format$((fOverlapPct * 100), "#.###")
Select sa.postsect, sa.NameNew, sa.DistanceNew
, sa.PropOverlapNew, dt.NAME, dt.value
, ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlap"
From Service_Areas As "sa", Service_Engineers_DT As "dt"
Where sa.Obj intersects dt.Obj
And dt.value= nValue
And sa.DistanceNew = 0
And ProportionOverlap(sa.Obj, dt.Obj) >= fOverlapPct
Order by sa.postsect, PropOverlap Asc
Into _to_update NoSelect
Update _to_update
Set NameNew = Name,
DistanceNew = value,
PropOverlapNew = PropOverlap
Print "Nearest Engineer"
Select sa.postsect, sa.NameNew, sa.DistanceNew,
se.NAME,
ObjectDistance(Centroid(sa.Obj), se.Obj, "km") As "NearestDist"
From Service_Areas As "sa", Service_Engineers As "se" Cross Join
Where ObjectDistance(Centroid(sa.Obj), se.Obj, "km") < 150
And sa.DistanceNew = 0
Order By sa.postsect, NearestDist Desc
Into _nearest NoSelect
Update _nearest
Set NameNew = Name,
DistanceNew = -1 * NearestDist
I start with an overlap percentage of 50, and then for the 45-minute drivetime, I also use 25% and 5% to assign more service areas to the engineers.
Looking at the data, a better solution would be to create a 60-minute drivetime and include it in the script. You would then use the 25% and 5% in combination with a 60-minute drivetime zone.
Final Approach and some Issues
At the end, I use a Find The Nearest engineer approach. This is to get the last service areas assigned. But it does come at a cost.
As you can see in this map, using Find My Nearest Engineer with as-the-crow-flies distance can cause service areas to be assigned across water. For now, I need to check this and fix it manually. Using larger drivetime zones will avoid many of these issues.
Another issue you may run into is the presence of enclaves. Below is a good example of this.
The problem above occurs when the drivetime zones of the two neighboring service engineers overlap in the same area with almost the same amount. In the example above, Swindon may overlap those enclaved polygons with 94%, and Bristol with "only" 93%. This causes the polygons to be assigned to Swindon, even though it would be most obvious to assign them to Bristol. Again, this would require a bit of manual editing.
If you wanted to, you could convert this into a small MapBasic application. That would make it possible to create a function for running the individual steps and just pass the parameters - the drivetime and the overlap percentage - to this function. In this way, you can use the same code and wouldn't have to have it there multiple times. Let me get back to you on this in a future article - I guess the series doesn't end here after all.
Have you found this series useful?
Happy #MapInfoMonday
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------