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 9 of 9

Driving Distance between 2 postcodes

This is a discussion on Driving Distance between 2 postcodes within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi! I hope you are all well. My problem is the following: I have 2 data columns A Postcode1 and ...

  1. #1
    jdavid21 is offline Junior Member White Belt
    Join Date
    Apr 2009
    Posts
    2

    Exclamation Driving Distance between 2 postcodes

    Hi!

    I hope you are all well.

    My problem is the following:

    I have 2 data columns A Postcode1 and B Postcode 2. I have a couple of hundreds of rows. I need to get the driving distance from A to B, for each line.

    I've read some previous threads and other forums but due to low VBA skills and new to map point, I'm struggling to get anywhere.

    I'd really appreciate your comments, links towards code and instructions as to how to get this done in excel interacting with map point.

    Thank you in advance.
    Kind regards

    David

  2. #2
    davidb is offline Senior Member Blue Belt
    Join Date
    Oct 2003
    Location
    London
    Posts
    290

    Re: Driving Distance between 2 postcodes

    There is a very helpful set of tutorials, starting from zero knowledge, that you can find at

    MP09NA, MP09EU, MapPoint VBA Tutorial, Company and Product Directory, Functional Programming F#

    Scroll down the page to “Automating MapPoint with Excel VBA - Tutorial 6” and there’s a link to the previous 5. Tutorial 3 does exactly what you want for a pair of zips. Using that exact code and then adding a do-loop like in tutorial 4 you have a very basic program for doing what you want....

    Code:
    Dim oApp As MapPoint.Application
    
    Private Sub CommandButton1_Click()
    
      Set oApp = CreateObject("MapPoint.Application.EU.16")
      oApp.Visible = True
      Set objMap = oApp.NewMap
      Set objRoute = objMap.ActiveRoute
      
      nReadRow = 2
       Do While Worksheets("Sheet3").Cells(nReadRow, 1) <> ""
     
          szZip1 = Worksheets("Sheet3").Cells(nReadRow, 1)
          szZip2 = Worksheets("Sheet3").Cells(nReadRow, 2)
       
          '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
      
          Worksheets("Sheet3").Cells(nReadRow, 3) = objRoute.Distance
      
          objRoute.Clear
          nReadRow = nReadRow + 1
       Loop
    End Sub
    You’ll certainly need to refine it (eg. dealing with unlocated postcodes etc.) and using the search on this forum you’ll find a lot of code examples.
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  3. #3
    Profem is offline Junior Member White Belt
    Join Date
    Apr 2009
    Location
    Montréal, QC, Canada
    Posts
    3

    Re: Driving Distance between 2 postcodes

    Hi !

    I have a fairly similar problem to David's one.

    I have 2 data columns in Excel (2007 edition)
    column A : Postcode1
    column B : Postcode2

    I also have a couple of hundreds of rows and also need to get the driving distance (and time) from A to B, for each line (to use it afterwards in a vehicle routing problem).

    I already have the code (which I will try to paste here...). The problem is that Excel doesn't seem to link to MapPoint (2004 edition) because it says "Compilation error : Project or librairy unfound".

    As I don't have any knowledge in programing... I don't know how to help Excel find MapPoint to calculate my distance (and time) matrix.

    I would really appreciate any of your comments, advices or help.

    Thank you really much in advance !

    Kind regards

    Emilie

    Code part 1 : Hebergement gratuit d'images, heberger vos photos pour forum, msn et site sur casimages

    Code part 2 : Hebergement gratuit d'images, heberger vos photos pour forum, msn et site sur casimages

  4. #4
    davidb is offline Senior Member Blue Belt
    Join Date
    Oct 2003
    Location
    London
    Posts
    290

    Re: Driving Distance between 2 postcodes

    One suggestion, in the visual basic editor on the Tools menu click References. Make sure the check box for Microsoft MapPoint Europe is ticked. Also look at tutorial 1 (link above) for more details on this.
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  5. #5
    jdavid21 is offline Junior Member White Belt
    Join Date
    Apr 2009
    Posts
    2

    Thumbs up Re: Driving Distance between 2 postcodes

    Davidb! that was very very useful!

    I jiggle about some of the code and now it works!

    The only thing though is that it works for 10 - 20 pairs... more than that it gets stuck! I already checked that all postcodes are valid by importing the pairs and mapping them in map point... the ones that were not valid were changed...

    Am I missing something? Is there any options to deselect in order to maker faster? For the 266 pairs that I have, I left it running for more than 15 min without success... so had to stop it...

    Also, I was able to change the calculations for distance from km to miles, but can't find the one for "time"... my driving times results are coming in "days"!!!!

    Thank you and kind regards,

    David

    Quote Originally Posted by davidb View Post
    There is a very helpful set of tutorials, starting from zero knowledge, that you can find at

    MP09NA, MP09EU, MapPoint VBA Tutorial, Company and Product Directory, Functional Programming F#

    Scroll down the page to “Automating MapPoint with Excel VBA - Tutorial 6” and there’s a link to the previous 5. Tutorial 3 does exactly what you want for a pair of zips. Using that exact code and then adding a do-loop like in tutorial 4 you have a very basic program for doing what you want....

    Code:
    Dim oApp As MapPoint.Application
     
    Private Sub CommandButton1_Click()
     
      Set oApp = CreateObject("MapPoint.Application.EU.16")
      oApp.Visible = True
      Set objMap = oApp.NewMap
      Set objRoute = objMap.ActiveRoute
     
      nReadRow = 2
       Do While Worksheets("Sheet3").Cells(nReadRow, 1) <> ""
     
          szZip1 = Worksheets("Sheet3").Cells(nReadRow, 1)
          szZip2 = Worksheets("Sheet3").Cells(nReadRow, 2)
     
          '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
     
          Worksheets("Sheet3").Cells(nReadRow, 3) = objRoute.Distance
     
          objRoute.Clear
          nReadRow = nReadRow + 1
       Loop
    End Sub
    You’ll certainly need to refine it (eg. dealing with unlocated postcodes etc.) and using the search on this forum you’ll find a lot of code examples.

  6. #6
    davidb is offline Senior Member Blue Belt
    Join Date
    Oct 2003
    Location
    London
    Posts
    290

    Re: Driving Distance between 2 postcodes

    When you get issues like reporting drivetime in days then it’s always a good idea to check out the appropriate msdn library. Here’e the DrivingTime property...

    DrivingTime property

    Basically to report in minutes the line of code should be

    Worksheets("Sheet1").Cells(NReadRow, 4) = objRoute.DrivingTime / geoOneMinute

    or simply multiply by 1440.

    I can see no reason why your program should exit without any error message. It’s been written so that it will exit when it finds an empty cell in the first column. If it’s exiting when the cell isn’t empty then all I can suggest is you post the code and some postcode examples where the problem happens.

    Also add in this line inside the do loop
    Worksheets("Sheet1").Cells(2, 5)= NReadRow-1
    so that you can monitor the number of postcode pairs it has analysed.
    Last edited by davidb; 04-22-2009 at 03:18 AM.
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  7. #7
    Profem is offline Junior Member White Belt
    Join Date
    Apr 2009
    Location
    Montréal, QC, Canada
    Posts
    3

    Re: Driving Distance between 2 postcodes

    Thank you very much davidb ! You were right, the box for Microsoft MapPoint North America wasn't checked.

    There is another problem now. "Execution error '-2147181454 (80049c72)'. The requested member of the collection does not exist. Use a valid name or index number." and it highlights this line of the code :

    objRoute.Waypoints.Add objMap.FindAddressResults(, , , , CodePostal1).Item(1)

    It doesn't seem to be finding the postal code in MapPoint... if anyone has an idea to help, I would really appreciate !

    Here is the code :


    Sub ComputeDurations()

    Dim objApp As MapPoint.Application
    Dim objMap As MapPoint.Map
    Dim objRoute As MapPoint.Route
    Dim objFindResults As MapPoint.FindResults
    Dim n As Integer
    Dim CodePostal1 As String
    Dim CodePostal2 As String

    Set objApp = CreateObject("mappoint.application")
    Set objMap = objApp.ActiveMap
    Set objRoute = objMap.ActiveRoute

    objApp.Visible = False
    objApp.UserControl = False

    objApp.Units = geoKm

    objRoute.DriverProfile.Speed(geoRoadInterstate) = 70
    objRoute.DriverProfile.Speed(geoRoadLimitedAccess) = 70
    objRoute.DriverProfile.Speed(geoRoadOtherHighway) = 70
    objRoute.DriverProfile.Speed(geoRoadStreet) = 30
    objRoute.DriverProfile.Speed(geoRoadArterial) = 42

    n = 1

    Do

    CodePostal1 = Excel.Cells(n, 1)
    objRoute.Waypoints.Add objMap.FindAddressResults(, , , , CodePostal1).Item(1)

    CodePostal2 = Excel.Cells(n, 2)
    objRoute.Waypoints.Add objMap.FindAddressResults(, , , , CodePostal2).Item(1)
    objRoute.Calculate

    Excel.Cells(n, 3) = 1440 * objRoute.DrivingTime
    Excel.Cells(n, 4) = objRoute.Distance

    objRoute.Clear

    n = n + 1

    Loop Until n > 10

    ActiveWorkbook.Save

    objApp.ActiveMap.Saved = True
    objApp.Quit

    Set objApp = Nothing
    Set objMap = Nothing
    Set objRoute = Nothing
    Set objFindResults = Nothing

    End Sub

  8. #8
    Profem is offline Junior Member White Belt
    Join Date
    Apr 2009
    Location
    Montréal, QC, Canada
    Posts
    3

    Re: Driving Distance between 2 postcodes

    I think that it works... it ends with an error message but it still seems to be working as a my rows C and D are full of numbers which make sens !

    Thanks again davidb... .

    Emilie

  9. #9
    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: Driving Distance between 2 postcodes

    The ".Item(1)" will fail with the collection message if the FindAddressResults could not find any results.

    You should check the quality of the results first before calling .Item(1), anyway. As well as the possibility of no results, it can also return ambiguous results (multiple possible options). There's no guarantee that the first is anywhere near correct!

    The collection's ResultsQuality property is a GeoFindResultsQuality value:

    geoAllResultsValid 0
    All returned results match the method criteria. Used only for results obtained from the FindNearby and ObjectsFromPoint methods, where all results match the criteria.
    geoAmbiguousResults 2
    At least the first two results are good matches, but it is not clear which one was intended.
    geoFirstResultGood 1
    The first result is a good match to the method criteria.
    geoNoGoodResult 3
    None of the results is a good match to the method criteria, but the results are the best available.
    geoNoResults 4
    No results are returned.

    Eg. one of your waypoint lines will be:

    Code:
    dim objResults as FindResults
    set FindResults = objMap.FindAddressResults(, , , , CodePostal1)
    if (FindResults.ResulktsQuality = ResultsQuality.geoAllResultsValid or 
       FindResults.ResultsQuality = ResultsQuality.geoFirstResultGood) then
    
         ' Result is good we can use it
        objRoute.Waypoints.Add FindResults.Item(1)
    endif

    Note that if the first waypoint is bad, then you don't need to look for the second one. Also you only need to calculate the route if both are good.

    This kind of thing quickly gets complicated - that is why there are commercial products around that do it! (yes I sell one, it is called MPMileage )


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

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. Driving distance between two postcodes
    By zippy in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 10-24-2004, 11:56 PM
  4. Replies: 6
    Last Post: 08-19-2004, 07:10 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