PDA

View Full Version : Reverse route stop times



Doc203
06-17-2012, 09:51 PM
Is there anyway to reverse a route and maintain the specific stop times? I have code that will reverse the route and put the same stop time on every stop, but what I need to do is maintain the original, unique stop times.

Thanks,

Jim

Mattys Consulting
06-17-2012, 11:35 PM
Please correct me if I'm wrong here:
Do you mean that you'll start at 9:00, go to stop 1 at 9:10 then on to stop 2 at 9:20 and return to the same origin at 9:30?
This route is presumably optimized. After all, it is a database of road segments with associated driving times.
If your stop 2 is further away, it will be difficult to achieve the same stop times as you may not get to stop 2 til 9:15, stop 1 at 9:25 and return at 9:35.
It depends upon conditions such as road speeds, man-made and natural obstacles, traffic, left turns vs right turns / stop lights, time of day and traffic, etc.

Doc203
06-18-2012, 06:15 AM
Sorry, I worded the question poorly. I should have said "stop duration's".

What happens is before the route is imported to mappoint, my worksheet puts the stop duration time into each waypoint (10 mins generally). It looks to see if there are more than one delivery to that stop, and if there is only puts a stop time on one of those stops. Otherwise, if you have 3 deliveries at one stop it would make the stop time 30 mins and skew the rest of the waypoint arrival times.

It works fine when you optimize as usual, but I have another bit of code that optimizes the route, then reverses it and adds the stop duration times back in. The problem I have is I cannot find a way to maintain the original stoptime that the worksheet calculated uniquely for each stop.

I probably just made this more confusing :)

Thanks,
Jim

Mattys Consulting
06-18-2012, 07:24 AM
Well, that didn't really clarify the problem for me and I'll likely need to see for myself.
Your last statement is that if there is more than one delivery at a stop, use only one stop time.
So add 10 minutes for each delivery; 2 deliveries = 20 minutes, 3 = 30 minutes.
Optimizing forward uses the stop times (duration) correctly, but the arrival times (which, apparently, are already set?) then receive late warnings.
I have to say that it does not work correctly at the point where you add more than one delivery to a stop and you'll have to shave some time off each duration.
Whether the route is reversed or not, the duration of multiple deliveries at one stop will not change in order for you to reach the set arrival times.
No?

Doc203
06-18-2012, 07:50 AM
Ok, let me try a different approach. I want to able to reverse a route and have the stop duration times imported from my spreadsheet - that match each stop. You can see my try at it in the last few lines below...


If objFindResults.ResultsQuality = geoNoResults Then ' <> geoFirstResultGood Then MsgBox ("Address incorrect on line " & nCurrentRow)
Exit Sub
End If

Set objPushpin = objmap.AddPushpin(objLoc, szName)

szStopTime = Cells(nCurrentRow, 15)

Dim wyp As MapPoint.Waypoint
Set wyp = objRoute.Waypoints.Add(objPushpin, objPushpin.Name)
wyp.StopTime = szStopTime * geoOneMinute
'wyp.StopTime = Range("Truck" & nTruck & "stop") * geoOneMinute

Dim objShape As MapPoint.Shape
objPushpin.BalloonState = geoDisplayName


nCurrentRow = nCurrentRow + 1
szAddress = Cells(nCurrentRow, 3)

Loop

Set oDS = objmap.DataSets("My Pushpins")
oDS.Name = Application.ActiveSheet.Name

objmap.Saved = True
oApp.Visible = True
objmap.DataSets.ZoomTo
' change all pushpins to small red circle
objmap.DataSets(1).Symbol = 25
objmap.Altitude = objmap.Altitude * 0.9
oApp.PaneState = geoPaneRoutePlanner

objRoute.Waypoints.Add objmap.FindAddressResults(ThisWorkbook.Worksheets("Settings").Range("EndAddress"), _
ThisWorkbook.Worksheets("Settings").Range("EndCity"), , _
ThisWorkbook.Worksheets("Settings").Range("EndState"), _
ThisWorkbook.Worksheets("Settings").Range("EndZip"))(1), "Start"


objRoute.Waypoints.Optimize
objRoute.Reverse

'Adds stop time to reversed route


Dim nCount As Integer
nCount = objmap.ActiveRoute.Waypoints.Count


For i = 1 To nCount - 1
objmap.ActiveRoute.Waypoints.Item(i).StopTime = szStopTime * geoOneMinute
Next

objRoute.Calculate

Mattys Consulting
06-18-2012, 09:07 AM
Hi Jim,

I don't see this code as modifying what I've stated so far.
However, I think that if the route is reversed, you might want to iterate the stop times in reverse as well.
That is,
For i = nCount - 1 To 1
'You'll need to work out nCurrentRow from bottom to top
szStopTime = Cells(nCurrentRow, 15)
objmap.ActiveRoute.Waypoints.Item(i).StopTime = szStopTime * geoOneMinute
Next

Doc203
06-18-2012, 10:24 AM
The problem is there is no reference to the stop time for each particular stop. When you are importing a route, you can reference each individual stop as you import it, thus you have the ability to have specific stop duration's for each delivery. When you reverse a route though, all of the information is already in mappoint and it does not reference excel again for it, and if it did it wouldn't know which stop is which.

An example would be:

3 deliveries

Bob's body shop
Bob's body shop
Jim's repair

Since Bob's is the same location, just 2 packages, my excel sheet inputs 10 minutes for one "Bob's" and 0 for the next one. The reason for this is our sheet also exports a map to a web server for our salespeople to look at and be able to tell our customers when they can expect to see their product. When you have 20 or so packages a day for each route, and many of those are multiple, it skews the amount of time spent at each location. As you can see in the example picture attached... in the route planner pane stops that have more than one invoice only have one stop duration listed. I would like to be able to reverse the route and keep those stop durations the same. Currently it will add the same stop duration to every delivery.

If I remove the duplicate locations in MapPoint, then the time works out fine obviously, but I also have a feature that prints a manifest for the driver and I want him to be able to have the invoice information etc on that manifest.

I may not be able to do what I want to do, and I may have to change how our manifest for the driver is printed.

715

Thanks for the help Michael

Mattys Consulting
06-18-2012, 11:16 AM
It can be done if you have your dataset set up for the desired operation.
Good Luck!

Doc203
06-19-2012, 10:06 PM
Michael - thanks to your help I think I have it worked out, need to test it on a few routes to be sure. Below is how I worked it out (just the section of code that performs the specific operation)... I am sure could be better, but it does work. :) It puts correct stop duration times on each stop and duplicates only have one stop time - thus maintaining the integrity of the route times overall.


objRoute.Waypoints.Optimize

'Export stops and stop times to use for refernce for rev route stoptimes

Sheets("Revrt").Select
Range("B1").Select
For p = 1 To objRoute.Waypoints.Count
ActiveCell.Offset(1, 0).Value = (objRoute.Waypoints.Item(p).StopTime / geoOneMinute)
ActiveCell.Offset(1, 0).Select

Next

Range("C1").Select
For p = 1 To objRoute.Waypoints.Count
ActiveCell.Offset(1, 0).Value = (objRoute.Waypoints.Item(p).Name)
ActiveCell.Offset(1, 0).Select


Next

objRoute.Reverse


'Adds stop time to reversed route
Sheets("Revrt").Select
Dim nCount As Integer
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
nCount = objmap.ActiveRoute.Waypoints.Count



For i = 1 To nCount - 1
szjStopTime = Cells(LastRow, 2)
objmap.ActiveRoute.Waypoints.Item(i).StopTime = szjStopTime * geoOneMinute
LastRow = LastRow - 1
Next

objRoute.Calculate