Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 44 votes, 5.00 average. Display Modes
Old 04-26-2012, 09:04 PM   #1
crowegreg
Newly Registered User
 
Join Date: Feb 2011
Posts: 108
Thanks: 14
Thanked 1 Time in 1 Post
crowegreg is on a distinguished road
calculate the driving distance between 2 zip codes

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=imperia l&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 by crowegreg; 04-27-2012 at 10:57 AM.
crowegreg is offline   Reply With Quote
Old 04-29-2012, 08:56 AM   #2
crowegreg
Newly Registered User
 
Join Date: Feb 2011
Posts: 108
Thanks: 14
Thanked 1 Time in 1 Post
crowegreg is on a distinguished road
Re: calculate the driving distance between 2 zip codes

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!!
crowegreg is offline   Reply With Quote
Old 04-29-2012, 05:30 PM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,037
Thanks: 78
Thanked 1,995 Times in 1,943 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: calculate the driving distance between 2 zip codes

Is your question just to use 2 zip codes, or is it really for 2 full addresses?

For those following the thread, this is cross posted at

http://www.accessforums.net/showthre...en-2-zip-codes


Last edited by jdraw; 04-30-2012 at 05:42 AM.
jdraw is offline   Reply With Quote
Old 04-29-2012, 05:38 PM   #4
crowegreg
Newly Registered User
 
Join Date: Feb 2011
Posts: 108
Thanks: 14
Thanked 1 Time in 1 Post
crowegreg is on a distinguished road
Re: calculate the driving distance between 2 zip codes

Only 2 zip codes.
crowegreg is offline   Reply With Quote
Old 04-30-2012, 03:16 PM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,037
Thanks: 78
Thanked 1,995 Times in 1,943 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: calculate the driving distance between 2 zip codes

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.
Attached Files
File Type: zip DistanceBetweenZips_0.zip (26.6 KB, 2640 views)
jdraw is offline   Reply With Quote
The Following 7 Users Say Thank You to jdraw For This Useful Post:
BoBaxter (03-29-2017), Duckman (09-30-2014), marvinhagen (05-05-2013), Privateer (08-19-2014), Royce (11-09-2012), sarmoni (03-11-2013), SP2ewe (03-13-2014)
Old 04-30-2012, 03:35 PM   #6
crowegreg
Newly Registered User
 
Join Date: Feb 2011
Posts: 108
Thanks: 14
Thanked 1 Time in 1 Post
crowegreg is on a distinguished road
Re: calculate the driving distance between 2 zip codes

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 is offline   Reply With Quote
Old 04-30-2012, 04:04 PM   #7
crowegreg
Newly Registered User
 
Join Date: Feb 2011
Posts: 108
Thanks: 14
Thanked 1 Time in 1 Post
crowegreg is on a distinguished road
Re: calculate the driving distance between 2 zip codes

That worked perfectly!! Thank you very much!

crowegreg is offline   Reply With Quote
Old 05-01-2012, 04:51 AM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,037
Thanks: 78
Thanked 1,995 Times in 1,943 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: calculate the driving distance between 2 zip codes

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.
jdraw is offline   Reply With Quote
Old 05-01-2012, 05:14 AM   #9
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: calculate the driving distance between 2 zip codes

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 (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-01-2012, 05:56 AM   #10
crowegreg
Newly Registered User
 
Join Date: Feb 2011
Posts: 108
Thanks: 14
Thanked 1 Time in 1 Post
crowegreg is on a distinguished road
Re: calculate the driving distance between 2 zip codes

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!!
crowegreg is offline   Reply With Quote
Old 05-01-2012, 06:28 AM   #11
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,037
Thanks: 78
Thanked 1,995 Times in 1,943 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: calculate the driving distance between 2 zip codes

Quote:
Originally Posted by crowegreg View Post
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 is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
SP2ewe (03-13-2014)
Old 05-01-2012, 06:48 AM   #12
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,037
Thanks: 78
Thanked 1,995 Times in 1,943 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: calculate the driving distance between 2 zip codes

Quote:
Originally Posted by gemma-the-husky View Post
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 by jdraw; 05-01-2012 at 06:58 AM. Reason: formatting
jdraw is offline   Reply With Quote
Old 05-01-2012, 07:47 AM   #13
crowegreg
Newly Registered User
 
Join Date: Feb 2011
Posts: 108
Thanks: 14
Thanked 1 Time in 1 Post
crowegreg is on a distinguished road
Re: calculate the driving distance between 2 zip codes

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.
crowegreg is offline   Reply With Quote
Old 05-07-2012, 05:44 PM   #14
VIC B
Newly Registered User
 
Join Date: May 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
VIC B is on a distinguished road
Re: calculate the driving distance between 2 zip codes

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 B is offline   Reply With Quote
Old 05-08-2012, 12:27 AM   #15
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: calculate the driving distance between 2 zip codes

Quote:
Originally Posted by jdraw View Post
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

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help Calculating Distance cmcquain Modules & VBA 2 04-13-2012 04:33 AM
Calculate distance from coordinates Sweetnuff38 General 6 05-08-2011 06:35 PM
Finding Distance Using Post Codes Dannyboy11 Queries 11 11-17-2010 12:50 PM
Calculate Distance From Postcode to Base dvent Queries 1 10-19-2009 03:39 PM
Post codes/ Zip codes input masks angelrichardson General 3 02-11-2003 07:46 AM




All times are GMT -8. The time now is 12:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World