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

Access VBA Programming Part II - Calculating a Distance Matrix

This is a discussion on Access VBA Programming Part II - Calculating a Distance Matrix within the MP2K Magazine Articles forums, part of the Map Forums category; In the last article we geocoded a set of Al's Beef locations , all but one of which are in ...

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

    Access VBA Programming Part II - Calculating a Distance Matrix

    In the last article we geocoded a set of Al's Beef locations, all but one of which are in Chicago or nearby suburbs, which makes sense as Chicago is mentioned in the very first sentence of the Wikipedia entry for "Italian beef". Indeed, Al's Beef is mentioned in the very first sentence of the section on Italian beef's origins.

    We are now going to use the results of the geocoding, i.e. the Latitude Longitude values, and calculate the driving distance between each of the stores and put the results in a new table. There are 14 locations, so by my rough estimate we have 14 * 13 or 182 distances to calculate. If they each take 1 second, the program should complete in just a few minutes.

    Conceivably you could get away with half that number as you don't necessarily need the distance from the Park Ridge store to the Niles location AND the distance from the Niles store to the Park Ridge location, but because of one way streets and they layout of On / Off ramps on interstates there will be differences.



    From a practical perspective, a distance matrix like this might be useful for logistics or supplies, for instance, if one store runs out of hot giardiniera (which would be a disaster) you could quickly see what are the three closest stores and call to see which one can provide speedy pepper reinforcements.

    Here is the code --

    Code:
    Sub CalculateDistances()
      Dim APP As MapPoint.Application
      Dim MAP As MapPoint.MAP
      Dim RTE As MapPoint.Route
      Dim LOC1, LOC2 As MapPoint.Location
      
      Set APP = CreateObject("MapPoint.Application")
      APP.Visible = True
      Set MAP = APP.ActiveMap
      Set RTE = MAP.ActiveRoute
      
      Dim rs1, rs2 As Recordset
      Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM [Al's Beef]")
      Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Al's Beef]")
      
      Dim sql As String
      sql = "CREATE TABLE AB_Distances (ID1 INTEGER, ID2 INTEGER, Distance Float)"
      CurrentDb.Execute sql
    
      Do Until rs1.EOF = True
        Set LOC1 = MAP.GetLocation(rs1("MP_Latitude"), rs1("MP_Longitude"))
        rs2.MoveFirst 'reset
        Do Until rs2.EOF = True
          If rs1("ID") <> rs2("ID") Then 'don't bother to calculate a store's distance to itself
            Set LOC2 = MAP.GetLocation(rs2("MP_Latitude"), rs2("MP_Longitude"))
            RTE.Waypoints.Add LOC1
            RTE.Waypoints.Add LOC2
            RTE.Calculate
            sql = "INSERT INTO AB_Distances (ID1, ID2, Distance) VALUES (" & rs1("ID") & ", " & rs2("ID") & ", " & RTE.Distance & ")"
            CurrentDb.Execute sql
          End If
          rs2.MoveNext
          RTE.Clear
        Loop
        rs1.MoveNext
      Loop
      MAP.Saved = True
      Debug.Print "finished"
    End Sub
    You can see there's two recordsets, both of the same table, and two loops, one inside the other. I'm just using CurrentDb.Execute to fire off SQL Statements, because that's much more straightforward to me than DAO TableDefs and what not.

    A screenshot of the resulting table is above and it has just three columns: ID1, ID2, and the distance. If you need help transforming this into a traditional distance matrix e.g. with 14 rows and 14 columns, let me know by commenting below and I can help you figure this out.

    So, most of the MapPoint bits in here should be very familiar to MapPoint programmers, and between the last article and this one we've shown programmatically how perform the following Access operations --

    • loop over a table
    • update existing records
    • create a new table
    • insert records into a new table


    What else would you like to see, either on the MapPoint side or with operations in Access? If there are any requests, we can certainly publish a Part III or Part IV and make "Access VBA Programming" a regular feature.
    Last edited by Eric Frost; 09-11-2013 at 05:37 PM.
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  2. #2
    sublimer is offline Junior Member White Belt
    Join Date
    Jul 2014
    Posts
    1

    Re: Access VBA Programming Part II - Calculating a Distance Matrix

    Hi Eric,

    I am using a modified version of your code tailored to my tables, but it seems like the LOC1 and LOC2 variables are not being assigned the coordinates (or anything) - it always states in debug mode that they are set to "Nothing".

    I'm using Access 2003 and Mappoint 2004 - any idea what may be causing this? I've pasted my code below, any help would be greatly appreciated.

    Code:
    Dim APP As MapPoint.Application
    Dim MAP As MapPoint.MAP
    Dim RTE As MapPoint.Route
    Dim LOC1 As MapPoint.Location
    Dim LOC2 As MapPoint.Location
    
    
    Set APP = CreateObject("MapPoint.Application")
      APP.Visible = False
    Set MAP = APP.ActiveMap
    Set RTE = MAP.ActiveRoute
    
    
    Dim rs1, rs2 As Recordset
    Set rs1 = CurrentDb.OpenRecordset("Tech Points", dbOpenDynaset, dbSeeChanges)
    Set rs2 = CurrentDb.OpenRecordset("Service Review Available Techs", dbOpenDynaset, dbSeeChanges)
      
    DoCmd.RunSQL "DROP TABLE SvcRev_Distances"
    Dim sql As String
      sql = "CREATE TABLE SvcRev_Distances (ID1 INTEGER, ID2 INTEGER, Distance Float)"
      CurrentDb.Execute sql
    
    
    
    
    Do Until rs1.EOF = True
        Set LOC1 = MAP.GetLocation(rs1("Latitude"), rs1("Longitude"))
            rs2.MoveFirst
        Do Until rs2.EOF = True
          If rs1("resource_id") = rs2("resource_id") Then
            Set LOC2 = MAP.GetLocation(rs2("Latitude"), rs2("Longitude"))
            RTE.Waypoints.Add LOC1
            RTE.Waypoints.Add LOC2
            RTE.Calculate
            sql = "INSERT INTO SvcRev_Distances (ID1, ID2, Distance) VALUES (" & rs1("resource_id") & ", " & rs2("workorder_activity_id") & ", " & RTE.Distance & ")"
            CurrentDb.Execute sql
          End If
          rs2.MoveNext
          RTE.Clear
        Loop
        rs1.MoveNext
    Loop
    MAP.Saved = False
    Last edited by Eric Frost; 07-27-2014 at 09:24 AM.

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

    Re: Access VBA Programming Part II - Calculating a Distance Matrix

    Sublimer,

    Welcome to the forums!

    I know the answer to this!

    The Latitude and Longitude properties were not added to MapPoint until version 2006. I'm surprised it does not throw an error.

    In any case, the code as is will not work with any version prior to MapPoint 2006.

    Hope this helps!

    Eric
    Last edited by Eric Frost; 07-29-2014 at 09:16 PM.
    ~ 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. Access VBA Programming Part I - Geocoding with MapPoint
    By Eric Frost in forum MP2K Magazine Articles
    Replies: 8
    Last Post: 09-30-2016, 07:37 AM
  2. How to generate drive time matrix rathe than distance matrix in EXCEL
    By john453 in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 08-17-2011, 04:08 PM
  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 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