• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

calculate the driving distance between 2 zip codes (1 Viewer)

crowegreg

Registered User.
Local time
Today, 03:18
Joined
Feb 28, 2011
Messages
108
I import information pertaining to a shipment. I have to calculate the driving distance between 2 zip codes. Currently I go to mapquest, enter the two zip codes, then paste the distance within my DB. I've looked at applications that calculate the distance, but they do not calculate driving distance, which is what I need.

I'd like to calculate the distance when the data is being imported in . I've looked at Google Maps, and here is what I have now.
I've completed the beginning and ending of this process, I've created the http line and I've imported the xml data into my table

Here's what I need help with

This http request works:

http://maps.googleapis.com/maps/api/distancematrix/xml?origins=77074&destinations=24112&units=imperial&sensor=false

How do I execute this http command within VBA, then save the results as an xml file?


Thank you in advance!!
 
Last edited:

crowegreg

Registered User.
Local time
Today, 03:18
Joined
Feb 28, 2011
Messages
108
Here's what I have at this time:

strhttp = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins="
strhttp = strhttp & strOZip & "&destinations="
strhttp = strhttp & strDZip & "&units=imperial&sensor=false"


Application.FollowHyperlink (strhttp)

MsgBox "Determine the distance"

'import xml file
Application.ImportXML _
DataSource:="c:\scat\zipxml.xml", _
ImportOptions:=acStructureAndData

This works, but it requires keyboard entries. When the Google API is executed, a Microsoft Office window pops up, it is asking if you want to open the file, I select ok. A new IE window opens with xml data that I have requested. I have to manually save the file. After saving the file, I close that IE window. You'll see that I have a msgbox come up so it halts the execution of the VBA so I can get that file saved. I select ok to my message, and the process is completed.

So what I'm needing is:
How do I prevent or automatically answer the Microsoft Office message?
How do I automatically save the the XML file?
I can live with the msgbox being displayed, but if their is something better, I'm all for it.

Thanks in advance!!
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
13,369
I am attaching a zip file containing an Access mdb database. The database has a table of 27 random US zips and a form -frmZipSample. Using the form you can select 2 zips and at the click of a button get a message showing
a) the distance between the zips, and
b) the estimated driving time.

There is an option to show the route in a separate browser window. I use Firefox 12.

This is for illustration only. Google Maps is the property of Google.
 

Attachments

  • DistanceBetweenZips_0.zip
    26.6 KB · Views: 2,731

crowegreg

Registered User.
Local time
Today, 03:18
Joined
Feb 28, 2011
Messages
108
Thanks for the DB. To make sure I understand how this works, the 2 zip codes need to be within the table randomzips, then the code will go out to google and retrieve the distance?
 

crowegreg

Registered User.
Local time
Today, 03:18
Joined
Feb 28, 2011
Messages
108
That worked perfectly!! Thank you very much!
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
13,369
Glad it does what you want. You can type in new zipcode into the zip combo, since the zips are not limited to the list.

If you look at the vba behind form and in module, you could intercept the values retruned from Google Maps and put it directly in a table etc.

I just chose some random zips and created the form to make a sample of HOW it could be done.

Post back if you have questions.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Sep 12, 2006
Messages
13,892
just for the benefit of anyone in the UK, you will/may have difficulty adapting this to the UK usage.

The reason is that the UK Post office does not freely provide post code (zip code) information, and therefore the exact geo-location of a UK post code cannot generally be determined from google (or any free service) with sufficient accuracy. You do get an approx location, but it is generally not accurate enough. You have to pay for the proper service.
 

crowegreg

Registered User.
Local time
Today, 03:18
Joined
Feb 28, 2011
Messages
108
It works perfectly. What I did was once I import the record, I parse out the 2 zip codes, then place them within your variables zip1 & zip2. I use the distance to calculate a charge for potential customers. This saves me about 30 seconds per import record.

Thanks again!!
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
13,369
It works perfectly. What I did was once I import the record, I parse out the 2 zip codes, then place them within your variables zip1 & zip2. I use the distance to calculate a charge for potential customers. This saves me about 30 seconds per import record.

Thanks again!!

As I mentioned, you could intercept the output and get - for example -
the mileage, multiply by a charge factor and get total charge, and store that in a table/record etc.
You could remove the manual intervention if needed.
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
13,369
just for the benefit of anyone in the UK, you will/may have difficulty adapting this to the UK usage.

The reason is that the UK Post office does not freely provide post code (zip code) information, and therefore the exact geo-location of a UK post code cannot generally be determined from google (or any free service) with sufficient accuracy. You do get an approx location, but it is generally not accurate enough. You have to pay for the proper service.

Dave,

A couple of points.
1. Postcode/zip code availability:
I agree totally that the general availability of postal codes is quite limited. In Canada, the post office controls all updates and it is a for fee service to have the latest valid, authoritative of codes. Same seems true for the US and the UK.

2. Zip codes (represent an area, not a point)
As I mention within the database start up page, Postal codes(zip codes) represent an area, not a point. Ideally, you would measure distance between 2 points. Zip codes are Area locators and represent some geographic area. The post office (or those in charge of area locators) may identify a centroid that represents some central point within the area represented by the Postal/zio code; and make measurements based on that central point. They may not, but some approximation must be made in order to represent an area by a point.

3. Accuracy of Google service
Based on 2, it follows that the distance between 2 area locators is always an approximation. Depending on the intended usage of the calculated result, the user/developer must determine if the accuracy of the approximated value is sufficient for the application. This is the user/developer decision, and must be considered in all cases.

I have included a sample of the accuracy based on 2 UK addresses and Postal Codes for illustration only.

I took 2 UK addresses.
The first calculated distance is based only on the Postal codes.
The second calculated distance is based on Full Address

And I do recognize that another address within the Postal Code could give a different result. Similarly, the calculated distance between other Postal Codes could vary more as well.

Test 1:
The distance between TN1 2BZ and RH1 1RT is 32.7 mi
The estimated time to get there is 45 mins.

Test 2:
The distance between 1 Calverley Street,Tunbridge Wells, Kent, TN1 2BZ and 3 Cromwell Road,Redhill, Surrey RH1 1RT is 31.8 mi
The estimated time to get there is 44 mins


Whether the accuracy (difference) in these 2 results is sufficient(acceptable or not) is entirely up to the user/developer or application representatives.
 
Last edited:

crowegreg

Registered User.
Local time
Today, 03:18
Joined
Feb 28, 2011
Messages
108
Unfortunately, my charge factor is a judgement call based on information I view on a web site. I have a quote form that I review the data that has been imported, then enter the charge factor. It then creates a contract, and emails it to the email address within the record. Your piece, was the last part of the puzzle for automation.
 

VIC B

New member
Local time
Today, 02:18
Joined
May 7, 2012
Messages
3
Thanks but that is Totally No Good for me...

I'm looking for addresses that are very Close to each other,
like 1/20th of a mile...

Victor
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Sep 12, 2006
Messages
13,892
Dave,

A couple of points.
1. Postcode/zip code availability:
I agree totally that the general availability of postal codes is quite limited. In Canada, the post office controls all updates and it is a for fee service to have the latest valid, authoritative of codes. Same seems true for the US and the UK.

2. Zip codes (represent an area, not a point)
As I mention within the database start up page, Postal codes(zip codes) represent an area, not a point. Ideally, you would measure distance between 2 points. Zip codes are Area locators and represent some geographic area. The post office (or those in charge of area locators) may identify a centroid that represents some central point within the area represented by the Postal/zio code; and make measurements based on that central point. They may not, but some approximation must be made in order to represent an area by a point.

3. Accuracy of Google service
Based on 2, it follows that the distance between 2 area locators is always an approximation. Depending on the intended usage of the calculated result, the user/developer must determine if the accuracy of the approximated value is sufficient for the application. This is the user/developer decision, and must be considered in all cases.

I have included a sample of the accuracy based on 2 UK addresses and Postal Codes for illustration only.

I took 2 UK addresses.
The first calculated distance is based only on the Postal codes.
The second calculated distance is based on Full Address

And I do recognize that another address within the Postal Code could give a different result. Similarly, the calculated distance between other Postal Codes could vary more as well.

Test 1:
The distance between TN1 2BZ and RH1 1RT is 32.7 mi
The estimated time to get there is 45 mins.

Test 2:
The distance between 1 Calverley Street,Tunbridge Wells, Kent, TN1 2BZ and 3 Cromwell Road,Redhill, Surrey RH1 1RT is 31.8 mi
The estimated time to get there is 44 mins


Whether the accuracy (difference) in these 2 results is sufficient(acceptable or not) is entirely up to the user/developer or application representatives.

yeah

you are relatively ok in densely populated post codes. try picking a trip using peterborough, norfolk, or lincoln codes, when the post code zone is much much wider.

i thought in the US, the information is available free, which is why google maps works. UK lat longs can only be gathered from UK post codes with a limited precision (which is returned in google KML/csv), which may make their use in navigational packages less satisfactory
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
13,369
Thanks but that is Totally No Good for me...

I'm looking for addresses that are very Close to each other,
like 1/20th of a mile...

Victor

Vic,
What exactly do you need?
I have looked a Chip Pearson's GreatCircle code at
http://www.cpearson.com/Excel/latlong.aspx

and some formulas from ZipcodeWorld at
http://www.zipcodeworld.com/samples/distance.vb.html

and have set these up as vba functions in Acc2003. These functions deal with direct distance between 2 points given their Lat/Long. This is not related to driving distance.

In measuring the distance from the Washington Monument to the US Capitol Building using the very same lat/long (decimal degrees)

Code:
startName = "Washington Monument"
endName = "Capitol Building, Washington,DC"
startLat = 38.8894515
startLong = -77.0348573
endLat = 38.8899352
endLong = -77.0091304


I found the following:

Miles using Distance 1.38390935013792
Kilometers using Distance 2.22718620918837

Miles using GreatCircle 1.38397013082645
Kilometers using GreatCircle 2.22728402622381

These seem pretty much the same to me. How accurate do you have to be?
 
Last edited:

duganc

New member
Local time
Today, 05:18
Joined
May 31, 2012
Messages
2
Glad it does what you want. You can type in new zipcode into the zip combo, since the zips are not limited to the list.

If you look at the vba behind form and in module, you could intercept the values retruned from Google Maps and put it directly in a table etc.

I just chose some random zips and created the form to make a sample of HOW it could be done.

Post back if you have questions.

Is there a way to perform a bulk lookup without using the form?

Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
13,369
I'm sure there is. What exactly is the issue you're trying to solve?
 

duganc

New member
Local time
Today, 05:18
Joined
May 31, 2012
Messages
2
I have sales territories with a workload center zip code and employees with their home zip codes. As we consider realigning our geographies we want to ensure we place folks as close as possible to the new workload centers and not make them drive too far to get their work done.

Right now I have SAS code to do that, but "as-the-crow-flies" miles doesn't really cut it.

Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
13,369
The database I provided in post#5 deals with Google maps and the distance between 2 zip codes.
Have you looked at it? It does distance, not minimal distance.
You could run a loop with your employee zips and get the distance from a work center.
Try it and post back if you have questions.
 
Last edited:

Users who are viewing this thread

Top Bottom