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 2 12 LastLast
Results 1 to 10 of 17

Postcode Distances

This is a discussion on Postcode Distances within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hi there. New to the forum. I come in the search of help.I have a list of postcodes around a ...

  1. #1
    PaulB82 is offline Junior Member White Belt
    Join Date
    Jan 2010
    Posts
    2

    Postcode Distances

    Hi there. New to the forum. I come in the search of help.I have a list of postcodes around a 1,000 and im looking to see their distance from each other. Its working fine. However my problem is that my excel will freeze if the list is anything over 100 or so. If i try and get this for all 1,000 it just slows everything up to a grinding halt. I am using excel 2003 and mappoint 16. Any advice or help that i can use within these pieces of software would be of huge help!!!</p>

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

    Re: Postcode Distances

    How are you getting the distances Paul? Are you using code? How many distances are you trying to calculate? Are all the postcodes valid?

    Rgds
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  3. #3
    PaulB82 is offline Junior Member White Belt
    Join Date
    Jan 2010
    Posts
    2

    Re: Postcode Distances

    I have all valid postcodes and have ran them through MapPoint to make sure they are recognized. Im using the following code Private Sub CommandButton1_Click()Dim objApp As New MapPoint.ApplicationDim objMap As MapPoint.MapDim objRoute As MapPoint.RouteDim objLoc1 As MapPoint.LocationDim objLoc2 As MapPoint.LocationSet objApp = CreateObject("MapPoint.Application.EU.16")objApp.V isible = FalseSet objMap = objApp.NewMapSet objRoute = objMap.ActiveRouteWorksheets("Sheet2").Cells(1, 3).Value = "Drive Distance (kms)"Worksheets("Sheet2").Cells(1, 4).Value = "Drive Time (mins)"Worksheets("Sheet2").Cells(1, 5).Value = "Straight Line Distance (kms)"NReadRow = 2Do While Worksheets("Sheet2").Cells(NReadRow, 2) ""'Locate the 2 pointsSet objLoc1 = objMap.FindResults(Worksheets("Sheet2").Cells(NRea dRow, 1)).Item(1)Set objLoc2 = objMap.FindResults(Worksheets("Sheet2").Cells(NRea dRow, 2)).Item(1)'Calculate the routeobjRoute.Waypoints.Add objLoc1objRoute.Waypoints.Add objLoc2objRoute.Calculate'Drive Distance in kmsWorksheets("Sheet2").Cells(NReadRow, 3) = objRoute.Distance'Drive Time in minutesWorksheets("Sheet2").Cells(NReadRow, 4) = objRoute.DrivingTime'Straight Line Distance in kms (as a check)Worksheets("Sheet2").Cells(NReadRow, 5) = objMap.Distance(objLoc1, objLoc2)objRoute.ClearNReadRow = NReadRow + 1LoopEnd Sub

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

    Re: Postcode Distances

    Hey give us a break Paul, can you put your code into a more user friendly format!! Just post it as the separate lines preferably between code markers -square bracket CODE close square bracket, square bracket /CODE close square bracket.
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

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

    Re: Postcode Distances

    Haha, I pasted into Visual Studio and cleaned it up --

    Code:
        Private Sub CommandButton1_Click()
            Dim objApp As New MapPoint.Application
            Dim objMap As MapPoint.Map
            Dim objRoute As MapPoint.Route
            Dim objLoc1 As MapPoint.Location
            Dim objLoc2 As MapPoint.Location
            objApp = CreateObject("MapPoint.Application.EU.16")
            objApp.Visible = False
            objMap = objApp.NewMap
            objRoute = objMap.ActiveRoute
            Worksheets("Sheet2").Cells(1, 3).Value = "Drive Distance (kms)"
            Worksheets("Sheet2").Cells(1, 4).Value = "Drive Time (mins)"
            Worksheets("Sheet2").Cells(1, 5).Value = "Straight Line Distance (kms)"
            NReadRow = 2
            Do While Worksheets("Sheet2").Cells(NReadRow, 2) <> ""
                'Locate the 2 points
                objLoc1 = objMap.FindResults(Worksheets("Sheet2").Cells(NReadRow, 1)).Item(1)
                objLoc2 = objMap.FindResults(Worksheets("Sheet2").Cells(NReadRow, 2)).Item(1)
                'Calculate the route
                objRoute.Waypoints.Add()
                objLoc1objRoute.Waypoints.Add(objLoc2objRoute.Calculate)
                'Drive Distance in kms
                Worksheets("Sheet2").Cells(NReadRow, 3) = objRoute.Distance
                'Drive Time in minutes
                Worksheets("Sheet2").Cells(NReadRow, 4) = objRoute.DrivingTime
                'Straight Line Distance in kms (as a check)
                Worksheets("Sheet2").Cells(NReadRow, 5) = objMap.Distance(objLoc1, objLoc2)
                objRoute.Clear()
                NReadRow = NReadRow + 1
            Loop
        End Sub
    In a past project, sometime I would see MapPoint freeze and eventually give up (however no error) when it would try to Optimize something that wasn't possible (one-way dead end streets, etc.), but you're not doing Optimize, just routing.

    You are only ever calculating between two points, right? I wonder if memory is leaking and things slow down. Can you check in task manager if MapPoint or Excel is consuming a greater and greater amount of memory as things slow down?

    If that is the case, then in your loop, you might re-instantiate MapPoint every time or say every 20th time. Or set the objects to Nothing.

    Eric
    Last edited by Eric Frost; 01-27-2010 at 01:56 PM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

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

    Re: Postcode Distances

    Thanks for that Eric. I don't think that code would actually run because there are a couple of "Set" statements missing. Also I think Visual Studio may have chewed up the lines in the 'Calculate the route' bit. Anyway I put the code into a workable format and ran it on 200 postcode pairs. It worked fine until the very end where it left a MapPoint process hanging so basically froze excel till I stopped that. So I added the necessary code to do this with vba and here's the revised...

    Code:
     Private Sub FindDistances_Click()
            Dim objApp As New MapPoint.Application
            Dim objMap As MapPoint.Map
            Dim objRoute As MapPoint.Route
            Dim objLoc1 As MapPoint.Location
            Dim objLoc2 As MapPoint.Location
            Set objApp = CreateObject("MapPoint.Application.EU.16")
            objApp.Visible = False
            Set objMap = objApp.NewMap
            Set objRoute = objMap.ActiveRoute
            Worksheets("Sheet2").Cells(1, 3).Value = "Drive Distance (kms)"
            Worksheets("Sheet2").Cells(1, 4).Value = "Drive Time (mins)"
            Worksheets("Sheet2").Cells(1, 5).Value = "Straight Line Distance (kms)"
            NReadRow = 2
            Do While Worksheets("Sheet2").Cells(NReadRow, 2) <> ""
                'Locate the 2 points
                Set objLoc1 = objMap.FindResults(Worksheets("Sheet2").Cells(NReadRow, 1)).Item(1)
                Set objLoc2 = objMap.FindResults(Worksheets("Sheet2").Cells(NReadRow, 2)).Item(1)
                'Calculate the route
                objRoute.Waypoints.Add objLoc1
                objRoute.Waypoints.Add objLoc2
                objRoute.Calculate
                
                'Drive Distance in kms
                Worksheets("Sheet2").Cells(NReadRow, 3) = objRoute.Distance
                'Drive Time in minutes
                Worksheets("Sheet2").Cells(NReadRow, 4) = objRoute.DrivingTime
                'Straight Line Distance in kms (as a check)
                Worksheets("Sheet2").Cells(NReadRow, 5) = objMap.Distance(objLoc1, objLoc2)
                objRoute.Clear
                NReadRow = NReadRow + 1
            Loop
            
            
         objMap.Saved = True
         Set objApp = Nothing
         Set objMap = Nothing
         Set objLoc1 = Nothing
         Set objLoc2 = Nothing
         Set objRoute = Nothing
        End Sub
    Paul if this routine still slows down for you after 100 or so pairs then please re-post and we can try something else.

    rgds
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  7. #7
    SCudd is offline Junior Member White Belt
    Join Date
    Jan 2010
    Posts
    2

    Re: Postcode Distances

    Hi there, I am very new to all this but managed to use this code to calculate postcodes in two adjacent columns. I was trying to alter the code however to look at only one column, take the first postcode in row 2 and calculate the distance etc. to the second postcode in row 3, then take the second postcode in row 3 and calculate the distance to the third postcode in row 4 and so on - unfortunately I am getting nowhere. Can anyone please help
    Last edited by SCudd; 01-28-2010 at 05:09 PM. Reason: clarify info

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

    Re: Postcode Distances

    You need to play around with the cell references. Try something like....

    Code:
    Private Sub FindDistances_Click()
            Dim objApp As New MapPoint.Application
            Dim objMap As MapPoint.Map
            Dim objRoute As MapPoint.Route
            Dim objLoc1 As MapPoint.Location
            Dim objLoc2 As MapPoint.Location
            Dim Ws As Excel.Worksheet
            Set objApp = CreateObject("MapPoint.Application.EU.16")
            objApp.Units = geoKm
            objApp.Visible = False
            Set objMap = objApp.NewMap
            Set objRoute = objMap.ActiveRoute
            Set Ws = Worksheets("Sheet2")
            Ws.Cells(1, 3).Value = "Origin Postcode"
            Ws.Cells(1, 4).Value = "Destination Postcode"
            Ws.Cells(1, 5).Value = "Drive Distance (kms)"
            Ws.Cells(1, 6).Value = "Drive Time (mins)"
            Ws.Cells(1, 7).Value = "Straight Line Distance (kms)"
            NReadRow = 2
            Do While Ws.Cells(NReadRow + 1, 1) <> ""
                'Locate the 2 points
                 Set objLoc1 = objMap.FindResults(Ws.Cells(NReadRow, 1)).Item(1)
                 Set objLoc2 = objMap.FindResults(Ws.Cells(NReadRow + 1, 1)).Item(1)
                'Calculate the route
                 objRoute.Waypoints.Add objLoc1
                 objRoute.Waypoints.Add objLoc2
                 objRoute.Calculate
                 Ws.Cells(NReadRow + 1, 3) = Ws.Cells(NReadRow, 1).Value
                 Ws.Cells(NReadRow + 1, 4) = Ws.Cells(NReadRow + 1, 1).Value
                'Drive Distance in kms
                 Ws.Cells(NReadRow + 1, 5) = objRoute.Distance
                'Drive Time in minutes
                 Ws.Cells(NReadRow + 1, 6) = objRoute.DrivingTime / geoOneMinute
                'Straight line distance for checking
                 Ws.Cells(NReadRow + 1, 7) = objMap.Distance(objLoc1, objLoc2)
                 objRoute.Clear
                 NReadRow = NReadRow + 1
            Loop
               
         objMap.Saved = True
         Set objApp = Nothing
         Set objMap = Nothing
         Set objLoc1 = Nothing
         Set objLoc2 = Nothing
         Set objRoute = Nothing
         Set Ws = Nothing
        End Sub

    By the way I'm no programmer so always check the results thoroughly before trusting the code!!!
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

  9. #9
    SCudd is offline Junior Member White Belt
    Join Date
    Jan 2010
    Posts
    2

    Re: Postcode Distances

    Superb - that worked perfectly - thank you very much. Can I trouble you to ask one more question please. The list I have has some blank spaces in it, is there any way to make this program carry on for maybe 5 attempts to find data before it stops? So for instance it finds an origin position, then when it looks at the cell below it is blank, so it looks to the cell below that etc. five times. If it finds data during its 5 attempts it uses that as the destination and carries on as normal using the last one it found as the origin. If no data is found after 5 attempts, it stops. Any helpl would be greatly appreciated and I promise this is the last request.

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

    Re: Postcode Distances

    This would be a slightly more general approach in that it would carry on looking for a destination postcode in the column until it found one. If then you wanted to end the routine after finding 5 blank cells it would be easy to add that in with a counter. The ‘strategy’ would be
    - Identify the last cell in column A that isn’t blank
    - Identify the first cell in column A that isn’t blank, ignoring the header in A1
    - Assign the first filled cell as the origin postcode and the one below that as the destination postcode
    - Check to see if the destination postcode cell is blank and carry on checking until a filled cell is found
    - Calculate drive distance etc
    - Re-assign that cell as the origin postcode
    - Then repeat the process until the last destination cell used was the last filled cell in column A
    Anyway you’ll need to play around with this but the code snippets would be something along the lines of.....
    Code:
    Private Sub FindDistances_Click()
      
     {set up pretty well as before} 
         
           'Identify row number of last filled cell in column A
            nLastRow = Ws.Range("A65000").End(xlUp).Row
           'Identify row number of first filled cell in column A ignoring header
            nFirstRow = Ws.Range("A1").End(xlDown).Row 'think that works, needs checking
    
     {as before}
    
          'set up the loop and if statement
            nOrigRow = nFirstRow
            nDestRow = nFirstRow + 1
            Do While nDestRow <= nLastRow
           'check if there’s a value in the destination cell
                If Ws.Cells(nDestRow, 1) <> "" Then
           'if yes then calculate the distance, drive time etc as before – but you need to change _
           '     ...indices for printing out the results
     
                Else
           'if the cell is empty move on to the next cell in column A
                 nDestRow = nDestRow + 1
                End If
            Loop
         MsgBox "calculations complete!!"
       
      {end as before}
    Something like that anyway!!! Also as these routines get more complicated it’s always a good idea to set up Dim statements for all the variables used (Option Explicit).
    David
    UK mapping and map analysis services at www.broomanalysis.plus.com

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Distances 'as the crow flies'
    By TRK in forum MapPoint Desktop Discussion
    Replies: 4
    Last Post: 01-20-2007, 04:25 AM
  2. Getting Distances
    By cmarquez in forum MapPoint Desktop Discussion
    Replies: 13
    Last Post: 10-17-2006, 10:34 AM
  3. Calculate distances
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 11-18-2004, 11:58 AM
  4. Batch Distances?!
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 01-21-2004, 01:40 PM
  5. hi, we are using mappoint to get distances for ou....
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 07-05-2002, 11:01 AM

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