Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 4 of 4

How to generate drive time matrix rathe than distance matrix in EXCEL

This is a discussion on How to generate drive time matrix rathe than distance matrix in EXCEL within the MapPoint Desktop Discussion forums, part of the Map Forums category; Dear all: Now, I am quite clear about how to generate distance matrix based on post code using EXCEL automatically. ...

  1. #1
    john453 is offline Junior Member White Belt
    Join Date
    Jul 2011
    Posts
    9

    How to generate drive time matrix rathe than distance matrix in EXCEL

    Dear all:

    Now, I am quite clear about how to generate distance matrix based on post code using EXCEL automatically. The code is in the follows and it turned out to be quite successfully. However, Now I would like to generate drive time between each post code rather distance. So, in this case. how to properly modify the code in order to make that? Moreover, if this can be done, is it possible to modify the settings of the speed?

    I cannot thank you enough if you can give me help!!

    "
    Sub DistanceMatrix_Generator_Click3()

    Set oApp = CreateObject("MapPoint.Application.EU.16")
    oApp.Visible = False
    Set objMap = oApp.NewMap
    Set objRoute = objMap.ActiveRoute
    oApp.ActiveMap.Saved = True

    For i = 3 To 11

    For j = i + 1 To 12


    szZip1 = Worksheets("Sheet1").Cells(i, 2)
    szZip2 = Worksheets("Sheet1").Cells(j, 2)

    objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
    objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)

    objRoute.Calculate

    Worksheets("Sheet1").Cells(i, j) = objRoute.Distance

    Next j

    Next i

    objRoute.Clear
    oApp.ActiveMap.Saved = True


    End Sub"
    Attached Files Attached Files

  2. #2
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: How to generate drive time matrix rathe than distance matrix in EXCEL

    Awesome!! A question I can answer and not involving DEP, Windows 7, Visual Studio 2010, or 64-bit OS's

    It's actually pretty simple, just use the DrivingTime property, here's the example from the help file --

    Code:
      Sub GetRouteDrivingTime()
    
      Dim objApp As New MapPoint.Application
      Dim objMap As MapPoint.Map
      Dim objRoute As MapPoint.Route
    
      'Set up the 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("Seattle, WA").Item(1)
      objRoute.Waypoints.Add objMap.FindResults("Redmond, WA").Item(1)
      objRoute.Calculate
    
      'Find the driving time of the route
      MsgBox "Driving time in minutes: " + CStr(objRoute.DrivingTime / geoOneMinute)
    
      End Sub
    Speed is set using the DriverProfile object --

    Code:
      Sub ChangeLimitedAccessSpeed()
     
      Dim objApp As New MapPoint.Application
      Dim objMap As MapPoint.Map
      Dim objRoute As MapPoint.Route
     
      'Set up the 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("Seattle, WA").Item(1)
      objRoute.Waypoints.Add objMap.FindResults("Spokane, WA").Item(1)
     
      'Change limited-access road speed
      objRoute.DriverProfile.Speed(geoRoadLimitedAccess) = 55
      objRoute.Calculate
     
      End Sub
    hope this helps!

    Eric
    Last edited by Eric Frost; 08-11-2011 at 01:23 PM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  3. #3
    john453 is offline Junior Member White Belt
    Join Date
    Jul 2011
    Posts
    9

    Re: How to generate drive time matrix rathe than distance matrix in EXCEL

    Dear Eric:

    Thank you so much for what you have done for me!



    However, now, I have a more complex question. since the distance is worked out and It is known that the road had five types of road which all called RoadInterstate, limited-access highway, RoadOtherHighway,RoadArterial,RoadStreet respectively. This distance, for example, is 50 miles. Then 50 miles cover the overall length of each type of road.

    So, I was wondering if there is any function or application in Mappoint to work out the distance for each type. As I am working on one project which requires manipulating the speed on each type of the road, so, I am quite desperate to find out how the overall distance can be split?


    I cannot thank you enough!


  4. #4
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: How to generate drive time matrix rathe than distance matrix in EXCEL

    Sure, glad to help!

    I'm not sure what you want is possible

    You can extract coordinates of a route as demonstrated in a recent newsletter -- Route Factory Spreadsheet, MapPoint Videos on Youtube, MapForums Downloads Section

    But I'm not sure you can actually get the speed or type of road at each point.

    Maybe if you have a real road dataset such as NAVTEQ's and a real GOS platform such as MapInfo Pro, but I don't think you can really extract the road types from MapPoint.

    Anyone have thoughts on this?

    Thanks!
    Eric
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculate driving distance matrix in Excel
    By DaveOKC in forum MapPoint Desktop Discussion
    Replies: 21
    Last Post: 04-13-2012, 07:20 AM
  2. Zip-to-Zip Driving Time Matrix
    By sasa in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 12-08-2008, 11:11 AM
  3. Calculating automatically with matrix
    By kikolino in forum Development
    Replies: 0
    Last Post: 04-28-2008, 01:40 AM
  4. Distance matrix question
    By routesmith in forum Bing Maps and MapPoint Web Service
    Replies: 1
    Last Post: 04-10-2006, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27