MapPoint: Distances traveled within a state

Question

I would like a process to take two points and build a route between them, and be able to tally miles by state. would a process like this be possible within Access VBA or VB.net?

So far I am able to make a route and get the distance, but I am not sure what would be next to find states traveled and the tally of the miles in each state.

Here is what I have so far:

Public Sub route()


  Dim objApp As New MapPoint.Application
  Dim objMap As MapPoint.Map
  Dim objRoute As MapPoint.route


  'Set up application
  Set objMap = objApp.ActiveMap
  Set objRoute = objMap.ActiveRoute
  objApp.Visible = True
  objApp.UserControl = True


  'Add route stops and calculate the route
  objRoute.Waypoints.Add objMap.FindResults("Iron Mountain, MI").Item(1)
  objRoute.Waypoints.Add objMap.FindResults("Chicago, IL").Item(1)
  objRoute.Calculate

    For Each Item In objRoute.Directions
        MsgBox Item.Instruction & " " & Item.ElapsedDistance
    Next

End Sub

An Issue I am still having is I have tried to use Item.Location.StreetAddress.Region but I get: "Object Variable or With block variable not set." Can anyone tell me how to find the state of a particular direction point? If so I beleive I can find elapsed miles by state by adding elapsed miles - previous elapsed miles to the state it belongs to.

thanks!

Answer 1

I have been tinkering with the following Access VBA code. It is an extension of the code in the question, but I used GetObject() so I could attach to an existing instance of MapPoint. That way I could create my route in MapPoint (and tweak as necessary), then [Alt-Tab] into Access and run the code.

The code is by no means complete, but it does seem to cover the basics. As a test, I planned a route from "New York, NY" to "Los Angeles, CA" and when I run my VBA code the following appears in the Immediate Window of the VBA editor:

"Origin_State: New York",0
"Entering New Jersey",1.57828283309937
"Entering Pennsylvania",76.8766632080078
"Entering Ohio",387.730041503906
"Entering Indiana",624.323974609375
"Entering Illinois",776.259155273438
"Entering Iowa",939.418151855469
"Entering Nebraska",1245.23413085938
"Entering Colorado",1599.96252441406
"Entering Utah",2054.32885742188
"Entering Arizona",2418.78686523438
"Entering Nevada",2448.091796875
"Entering California",2572.029296875
"End_of_route",2798.63793945313

The code is as follows:

Option Compare Database
Option Explicit

Dim objApp As MapPoint.Application
Dim objMap As MapPoint.Map

Const DebugMode = True  '' controls how error messages are displayed

Public Sub RouteTest()
    Dim objRoute As MapPoint.Route
    Dim objDirection As MapPoint.Direction
    Dim StateOfOrigin As String

    On Error GoTo RouteTest_Error
    '' attach to existing instance of MapPoint
    Set objApp = GetObject(, "MapPoint.Application")
    On Error GoTo 0  '' for debugging
    Set objMap = objApp.ActiveMap
    Set objRoute = objMap.ActiveRoute

    If objRoute.Directions Is Nothing Then
        DisplayErrorMessage "No route.", vbExclamation
        Exit Sub
    End If

    StateOfOrigin = GetState(objRoute.Directions(1).Location)
    Debug.Print """Origin_State: " & StateOfOrigin & """,0"

    For Each objDirection In objRoute.Directions
        If objDirection.Instruction Like "Entering *" Then
            Debug.Print """" & Replace(objDirection.Instruction, """", """""", 1, -1, vbBinaryCompare) & """," & objDirection.ElapsedDistance
        End If
    Next
    Set objDirection = objRoute.Directions(objRoute.Directions.Count)
    Debug.Print """End_of_route""," & objDirection.ElapsedDistance
    Set objDirection = Nothing

    Set objRoute = Nothing
    Set objMap = Nothing
    Set objApp = Nothing
    Exit Sub

    RouteTest_Error:
    If Err.Number = 429 Then
        DisplayErrorMessage "Unable to attach to existing instance of MapPoint.", vbCritical
    Else
        Err.Raise Err.Number
    End If
End Sub

Public Function GetState(loc As MapPoint.Location) As String
    '' adapted from code at http://www.mp2kmag.com/articles.asp?ArticleID=47
    Dim objResults As MapPoint.FindResults
    Dim objTempLoc As MapPoint.Location
    Dim rgn As String
    rgn = ""
    loc.Goto
    objMap.Altitude = 1
    Set objResults = objMap.ObjectsFromPoint(objMap.LocationToX(loc), objMap.LocationToY(loc))
    If objResults.ResultsQuality = geoAllResultsValid Then
        For Each objTempLoc In objResults
            If objTempLoc.Type = geoShowByRegion1 Then
                rgn = objTempLoc.Name
                Exit For
            End If
        Next
        Set objTempLoc = Nothing
    End If
    Set objResults = Nothing
    GetState = rgn
End Function

Private Sub DisplayErrorMessage(ErrorMessage As String, MessageBoxStyle As Long)
    If DebugMode Then
        Debug.Print ErrorMessage
    Else
        MsgBox ErrorMessage, MessageBoxStyle
    End If
End Sub

Answer 2

The actual error you are seeing is due to an abuse of the FindResults collection. You are blindly assuming that element 1 is set. This is not necessarily the case, and you should check the ResultsQuality property first. This is a very common problem that often appears on the various online forums - so common, that I wrote a quick article about it here:

http://www.mapping-tools.com/howto/programming/using-the-findresults-collection/

For City,State locations, you will probably also do better with FindAddressResults. This will result "Ambiguous Results" in most cases, but you can loop through the results and choose the first city (typically there are two results: City and State).

---

The above was in answer to the second part of the question. The follow was my response to the general strategy. From the comments, it looks like the OP has worked it out, but I'm keeping it here so that the comments make sense.

I don't think you can do what you want unless you internally had a list of shape definitions and did a point-in-polygon test (yes a lot of programming).

The Location's StreetAddress property is only set if the Location object was explicitly created from a street address (eg. At import).

Note also that your method could have some large errors because there is no guarantee that a Direction lies close to a State boundary. Also, the Direction Location objects do not lie on the route - they are intended for viewing only. Yes sometimes they do lie on the route segment, but there is no guarantee whatsoever. The idea is that you can zoom to the Location, and get an ideal map view for that Direction (Turn Left, etc).

Answer 3

I thought it actually adds a Direction when it changes state, so it shouldn't be that inaccurate? I know for accounting purposes tallying miles +/- a few bucks doesn't make much of a difference, and it's likely to be more accurate than relying on drivers to remember to look down and properly record mileage when crossing state lines. In any case, you could add rest stops to force additional directions to be added to the route, this might make it slightly more accurate -- http://www.mp2kmag.com/update/mappoint.newsletter/2011-05-05/
Eric
m: 312-399-1586