MapInfo Pro

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 Desc
Into _to_update

Update _to_update
  Set NameNew = Name,
       DistanceNew = value

It does calculate the Proportional Overlap, but we only use it to sort the result. In an improved query, we also add this to the conditions and compare it to a reasonable overlap. In my example below, I expect the area to be covered by 50% or more (And ProportionOverlap(sa.Obj, dt.Obj) >= 0.5) before I assign the area to that service engineer. In your case, the overlap percentage may differ. Maybe 25% (0.25) or 75% (0.75) works better for your situation. Sometimes, you may have to try a couple of values to see which makes the most sense.

I have added a PropOverlap column to my Service_Area table. This column is updated with the overlap percentage in the Update statement. This helps me verify the result. I check these values to ensure they are above the specified tolerance. I also checked the temporary table _to_update after I ran the script. This is especially worth checking at higher distance values, where more drivetime polygons overlap the areas simultaneously.

Here is the adjusted query:

Select sa.postsect, sa.NameNew, sa.DistanceNew
, sa.PropOverlap, dt.NAME, dt.value
, ProportionOverlap(sa.Obj, dt.Obj) As "PropOverlapNew"
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
And ProportionOverlap(sa.Obj, dt.Obj) >= 0.5
Order by sa.postsect, PropOverlapNew Desc
Into _to_update

Update _to_update
  Set NameNew = Name,
       DistanceNew = value,
       PropOverlap = PropOverlapNew

The nValue is still a variable that the user enters when the script runs. This allows the script to be used for various drivetime distances.

Let's run it for the 15-minute drivetime polygons.

As I mentioned above, the user will be prompted to enter the drivetime distance

And for 45-minute drivetime polygons.

And finally, for 60-minute drivetime polygons.

This shows the difference between using an overlap tolerance and not.

You'll notice that quite a few areas have shifted from one service engineer to another

You'll probably also see that more areas have not automatically been assigned to a service engineer. That's because our 60-minute drivetime polygons don't cover at least 50% of these areas. We could consider running the query with a lower overlap tolerance to get these assigned too.

I have zoomed into an area around Bristol to compare the differences with and without the overlap tolerance. The maps also show the 15- and 30-minute drivetime polygons, and it shows the time assigned to the areas based on the two methods

This is how it looked before we used the overlap tolerance.

Notice how areas are assigned a lower time just because a very small overlap exists between the small drivetime and the area.

And here it is with the overlap tolerance.

Notice how many of the areas are now assigned a higher time, as they aren't overlapping the drivetime polygons by enough.

I think this is a great improvement over last week's solution.

"Are we done yet?" you may ask. Well, maybe not quite.

You may notice on the map above that several areas are assigned to one service engineer, while other areas, a bit farther to the north on the map, are assigned to another service engineer.

This happens when an area overlaps equally with two engineers' drivetime polygons of the same distance - the query can't distinguish which engineer is closer. In an upcoming article, I will show how you can assign the area to the closest service engineer in those situations. Stay tuned!

Happy #MapInfoMonday!