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 1 of 3 123 LastLast
Results 1 to 10 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; I am new to Mappoint. I need to access Mappoint from Excel, calculate a large driving distance matrix (50 x ...

  1. #1
    DaveOKC is offline Junior Member White Belt
    Join Date
    Jul 2007
    Posts
    4

    Calculate driving distance matrix in Excel

    I am new to Mappoint. I need to access Mappoint from Excel, calculate a large driving distance matrix (50 x 200) from stores to customers using lat/longs stored in the Excel program and end up with the distance matrix (in driving miles) in Excel. Preferably without the user even knowing about or seeing mappoint.
    Is this doable in mappoint? How do I go about it if it is?
    THanks!
    DaveOKC

  2. #2
    Mattys Consulting's Avatar
    Mattys Consulting is offline Senior Member Black Belt
    Join Date
    Dec 2002
    Posts
    1,040

    Re: Calculate driving distance matrix in Excel

    Hi Dave,

    Mile-Charter might be a viable option for you ...
    MileCharter: Create Mileage Charts with Microsoft® MapPoint®

  3. #3
    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

    Yes it would do what you want - except it runs from within MapPoint rather than Excel.

    Use the Data Import Wizard in MapPoint to load your data into MapPoint. Load the source locs as one pushpin set, load the destination locs as a different pushpin set. Then run MileCharter, and it will create the matrix in Excel.

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

  4. #4
    DaveOKC is offline Junior Member White Belt
    Join Date
    Jul 2007
    Posts
    4

    Re: Calculate driving distance matrix in Excel

    Richard - thanks for the input. One question however - about how long would it take to generate a 50 x 100 matrix (table/chart) in Excel? I saw a comment on the Milecharter website that indicated it might take some time to do this. Thanks
    DaveOKC

  5. #5
    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

    It really depends on how far apart the locations are... but say it takes two seconds per route, then 2 x 50 x 200 = about 6 hours I think. As long as you precalculate I think it's fairly reasonable.
    Eric

  6. #6
    DaveOKC is offline Junior Member White Belt
    Join Date
    Jul 2007
    Posts
    4

    Re: Calculate driving distance matrix in Excel

    Good point on the route distances. Most of the measurements will be from 1/2 mile to 4 miles. If the average time is two seconds, or even one second, this will pose a problem for me. Can anyone verify this for me?
    DaveOKC

  7. #7
    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

    The route calculation time is due to MapPoint. MileCharter adds virtually no overhead (I made sure of that!).

    My rule of thumb (on a P4 @2.4GHz) is 1 second per route. That is an average US route - ie. lots of AK-CA would take longer. Within one State is noticeably less (0.5sec perhaps). 4 miles should be even less.
    (The rule of thumb comes from some timing tests I did in MapPoint before MileCharter was written. The test data was spread across the eastern half of the US).

    There's also an option to hide MapPoint during processing. This can be inconvenient if you're using the PC for something else at the same time, but it removes the overhead of MapPoint drawing each and every route. I would expect this to be fairly constant per route length - so more significant for shorter routes. Also your PC is probably faster than my P4 example.

    Try it - there's a free 14 day trial.

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

  8. #8
    maddog 2 is offline Junior Member White Belt
    Join Date
    Jan 2004
    Posts
    5

    Wink Re: Calculate driving distance matrix in Excel

    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

  9. #9
    DaveOKC is offline Junior Member White Belt
    Join Date
    Jul 2007
    Posts
    4

    Re: Calculate driving distance matrix in Excel

    Adam - thanks for the offer - I sent you a PM. Let me know how to reach you.
    DaveOKC

  10. #10
    diku1 is offline Junior Member White Belt
    Join Date
    Dec 2008
    Posts
    1

    Re: Calculate driving distance matrix in Excel

    maddog,
    I am not sure if this thread is too old to post a message, but I am also interested in checking out your Excel Model.

    Regards,
    diku1

    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

Page 1 of 3 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