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
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Calculate driving distance matrix in Excel

This is a discussion on Calculate driving distance matrix in Excel within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi Maddog, I read your post from a couple of years back. For my graduation project I have to research ...

  1. #11
    Rowany is offline Junior Member White Belt
    Join Date
    Apr 2011
    Posts
    1

    Re: Calculate driving distance matrix in Excel

    Hi Maddog,

    I read your post from a couple of years back. For my graduation project I have to research the Vehicle Routing Problem for a company. The thing is that I need to create a distance matrix for all their customers, which is quite time consuming. Do you still have the Excel model? This would really help me out a lot!

    Thanks in advance!

    Quote Originally Posted by maddog 2 View Post
    Dave, I've built an Excel model that does just the thing

    You input the Lat/longs and it outputs the following:

    - a map of all the points
    - a road distance matrix
    - a straight-line matrix
    - a speed matrix
    - a time matrix
    - and a 'wiggle' matrix (road/straight-line)

    You can also select whether you want miles/kms, shortest/quickest, mins/hours and so on, plus drivetime zones for the map itself.

    Send me an email and I'll send you the model. If it's for personal use then I won't charge...

    Adam

  2. #12
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60

    Re: Calculate driving distance matrix in Excel

    MileCharter continues to be available. Free trial available from MileCharter: Home Page

    Richard
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

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

    Re: Calculate driving distance matrix in Excel

    Quote Originally Posted by maddog 2 View Post
    Dave, I've built an Excel model that does just the thing

    You input the Lat/longs and it outputs the following:

    - a map of all the points
    - a road distance matrix
    - a straight-line matrix
    - a speed matrix
    - a time matrix
    - and a 'wiggle' matrix (road/straight-line)

    You can also select whether you want miles/kms, shortest/quickest, mins/hours and so on, plus drivetime zones for the map itself.

    Send me an email and I'll send you the model. If it's for personal use then I won't charge...

    Adam
    My name is John and I am currently studying at Warwick Universityfor postgraduate. I am working on the final project which required calculating the distance matrix and time matrix based between UK post code. The supervisor asked me to do this using Mappoint.

    By chance, I noticed your posting on this forum which is obviously 7 years back. I hope my reply is not too late. Your excel model is definitely what I am after. If you can kindly share your model with me, you cannot image how much time I will save and I cannot thank you enough!

    So, dear sir, Can you send me a copy of your model to me. I made a solemn commitment that this model is only for personal and academic use and I will remove this model as soon as I completed my project. You can send the model to john453@live.cn Thanks again!


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

    Re: Calculate driving distance matrix in Excel

    Also note that MileCharter has a free trial
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  5. #15
    ashokeroy is offline Junior Member White Belt
    Join Date
    Aug 2011
    Posts
    1

    Re: Calculate driving distance matrix in Excel

    Hi There - It seems an old discussion thread. If this message reaches you, can I take look at your model? Thanks

    Quote Originally Posted by maddog 2 View Post
    Dave, I've built an Excel model that does just the thing

    You input the Lat/longs and it outputs the following:

    - a map of all the points
    - a road distance matrix
    - a straight-line matrix
    - a speed matrix
    - a time matrix
    - and a 'wiggle' matrix (road/straight-line)

    You can also select whether you want miles/kms, shortest/quickest, mins/hours and so on, plus drivetime zones for the map itself.

    Send me an email and I'll send you the model. If it's for personal use then I won't charge...

    Adam

  6. #16
    reliantrobin is offline Junior Member White Belt
    Join Date
    Jan 2012
    Posts
    1

    Re: Calculate driving distance matrix in Excel

    Hi Maddog,

    I too would like a copy of your program if that was possible?...Its not for commercial use and not for a college project either )...just out of interest to see how these thing are done.

    If you want to PM me that would be great.

    Thanks

  7. #17
    PROFIT100 Consulting is offline Member Green Belt
    Join Date
    Sep 2009
    Location
    Darmstadt, Germany
    Posts
    94

    Re: Calculate driving distance matrix in Excel

    Hi,

    try our MapPoint-AddIn: IC-DistanceAnalysis
    We suggest: download and install IC-GeoAnalysis-Suite

    I you register, you will get a 3 month free license.

    Kind regards
    Manfred

  8. #18
    Binkus is offline Junior Member White Belt
    Join Date
    Nov 2011
    Posts
    5

    Re: Calculate driving distance matrix in Excel

    OP

    how are you with VBA in excel?

    i have a bit of code which will run travel distance to a location from a location

    (in my case i was using postcodes, but can be used with lat long with a minor code tweak)

    i used it to calculate for a business model i was working on the distance between every postcode district to every postcode district, take a long time but worth it.

    sounds like your problem would be a doddle compared to that

    here is the code if you are familiar with VBA shoudlnt be too hard to work around, note map type and my i which was my 110 locations to work out distance to, hope it helps someone. note also szzip1 and 2 pretty sure you can adapt this for lat long

    Code:
    Sub distance()
    Dim objApp As MapPoint.Application
    Dim objmap As Map
    Dim objloc As Location
    Dim szzip1 As String
    Dim szzip2 As String
    Dim objroute As Route
    Dim i As Integer
    Dim x As Integer
    i = 3
    x = 3
    Set objApp = CreateObject("MapPoint.Application")
    objApp.Visible = True
    Set objmap = objApp.NewMap
     Set objroute = objmap.ActiveRoute
     
    Do While x < 2894 And i < 110
    
      szzip1 = Sheets("sheet1").Cells(2, i).Value
      szzip2 = Sheets("sheet1").Cells(x, 1).Value
    On Error Resume Next
    
      'Add route stops and calculate the route
      objroute.Waypoints.Add objmap.FindResults(szzip1).Item(1)
      objroute.Waypoints.Add objmap.FindResults(szzip2).Item(1)
      objroute.Calculate
    
      Sheets("sheet1").Select
      Cells(x, i).Value = objroute.distance
    'Range("B" & x).Value = objroute.distance
    
    objroute.Clear
    objroute = Nothing
    x = x + 1
    
    If x = 2894 Then
    x = 3
    i = i + 1
    
    End If
    
    Loop
    
    End Sub
    Last edited by Eric Frost; 01-06-2012 at 08:41 AM.

  9. #19
    Winwaed's Avatar
    Winwaed is offline Mapping-Tools.com Black Belt
    Join Date
    Feb 2004
    Location
    Irving,Texas
    Posts
    1,859
    Blog Entries
    60

    Re: Calculate driving distance matrix in Excel

    FindResults returns an object and not a collection - you should check the status of this collection before blindly calling Item(1). There might not be an item 1, and the above code will throw an exception (fatal error).
    Winwaed Software Technology LLC
    http://www.winwaed.com
    See http://www.mapping-tools.com for MapPoint Tools

  10. #20
    Binkus is offline Junior Member White Belt
    Join Date
    Nov 2011
    Posts
    5

    Re: Calculate driving distance matrix in Excel

    Quote Originally Posted by Winwaed View Post
    FindResults returns an object and not a collection - you should check the status of this collection before blindly calling Item(1). There might not be an item 1, and the above code will throw an exception (fatal error).
    i use the above code without any problems,

    the error handler you could write in more detail however in this case it is a simple if its not there move onto the next one

    in the OP question he has lat long details since i am assuming most locations will have a lat long data you can blindly call the data.

    i dont think you are reading this code how i use it a top list(i) of postcodes along an excel column and x is a row list of postcodes checking distances between each one

    the code is fine its quick and dirty

Page 2 of 3 FirstFirst 123 LastLast

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do I get the driving distance of two addresses?
    By dawa in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 04-18-2008, 02:23 AM
  2. Driving Distance from a Point
    By BC1968 in forum MapPoint Desktop Discussion
    Replies: 4
    Last Post: 07-21-2007, 09:04 AM
  3. Distance matrix question
    By routesmith in forum Bing Maps and MapPoint Web Service
    Replies: 1
    Last Post: 04-10-2006, 03:58 PM
  4. Driving distance between two postcodes
    By zippy in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 10-24-2004, 11:56 PM
  5. SHORTEST DRIVING DISTANCE
    By Anonymous in forum Bing Maps and MapPoint Web Service
    Replies: 1
    Last Post: 07-29-2004, 08:51 PM

Tags for this Thread

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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127