# Wouldn't it be useful if...



## 3LeggedDog (Mar 3, 2006)

Wouldn't it be good if Microsoft had a deal worked out with mapquest which would allow them to create a function that would take in  two zip codes, or two addresses even, and calculate the driving distance between them, based on whatever is in Mapquest's databases?

Note: I don't need anyone to work on anything that would do that, since it doesn't relate to any problem that I'm working on, I was just throwing it out there.

-3LD


----------



## RalphA (Mar 3, 2006)

Surely, there must  be thousands of possible functions that people could want, if one takes the time to think about it.  And, I'm sure that many of these already exist.  The main problem is to be able to find them in a timely manner!  It's the old problem of Information Retrieval, and it really is a very complicated problem.  One usually solves the problem of "how to do it" by

1. Asking people you know.
2. Asking Google
3. Asking MrExcel
4. Thinking about it and reinventing the wheel
5. Telling your emploee-genius to figure it out for you.
6. Other ways too numerous to list here.

After all, if we combine all the knowledge of the world, we have the terribly difficult problem of how do we get an answer back that fits our need?  Just think about it for a few minutes!


----------



## Greg Truby (Mar 3, 2006)

Mapquest already does that.  Just use their _Directions To_ or _Direction From_ feature.


----------



## RalphA (Mar 3, 2006)

Greg, I thought the 3LeggedDog wanted an Excel function to do the trick!  Otherwise, why in the world would he be posting his question here?


----------



## 3LeggedDog (Mar 4, 2006)

I don't need one to do it, I'm not working on anything that would use it.  The reason that I was thinking about it is that yesterday I was watching a group presentation where they talked about calculating a bunch of distances using Mapquest individually, for each pair of points, and I thought how much easier it would have been if they could have done that using Excel.  I have no need for such a function, and I don't expect for anyone to try to solve it.  I was just throwing out the idea that for some future version of Excel, it would be neat if they worked with Mapquest, or whatever Microsoft's equivalent software is, to make calculating driving distances simple.  This is the sort of thing that could help out small businesses that want to easily get an estimate of a whole bunch of shipping costs without having to enter queries in Mapquest over and over.  I worked on a class project in the fall that would have benefited from this.  When I'm on a different computer, I may post a part of one of the sheets that I used, to give a better idea of what I mean.

-3LD


----------



## Greg Truby (Mar 6, 2006)

Ah.  There is software for stuff like that all over the place in the logistics industry.  I'm not up-to-date on the latest route-planning software packages, but you can bet every carrier that's still solvent uses it.  I'm not familiar with any Excel addins for it.  I've only ever seen stand-alone packages -- but it's been a few years since I've thumbed through a logistics trade rag so who knows what's on the market now-a-days.


----------



## adam_neb (Mar 6, 2006)

Two mileage programs that spring to mind are Rand McNally MileMaker & ALK's PC Miler.  Both programs have excel addins that allow the user to calculate miles, etc.  I cannot imaging any logistics company using both, or at least one, of those packages.
There are less expensive programs, but Rand and PC Miler are the two standards (can there be more than one standard?).

Adam


----------



## Greg Truby (Mar 6, 2006)

I was going to mention _PC Miler_, since my old company had used that a bit.  But that was over ten years ago and I wasn't sure if it was still around.


----------



## Oaktree (Mar 6, 2006)

You can also get a mapping of zip code to latitude/longitudes (I believe the USPS can provide) and make a UDF (or use a series of calculations) to calculate the distances that way...if you choose to go that route, you can Google your way to the lat/lon --> dist calculation.


----------



## Barry Katcher (Mar 12, 2006)

What I would really like to see is the ability to enter the two zip codes, plus a third cell with a brief description of my urologists diagnosis of my kidney-bladder functions, and have Excel calculate how many pit stops will be needed on the journey.  Then, upload it to Mapquest and have it show little urinal icons at all the available Dunkin' Donuts along the route.


----------



## whiteghost (Mar 19, 2006)

google earth?    er no....sorry  routemaster... UK only  best solution buy a map


----------



## mnordeen (Jul 5, 2013)

I have been trying to do this and there is a couple ways to do it but they are way too complicated, does anyone have a simple solution?


----------



## Atroxell (Jul 5, 2013)

If you have 2 lat/lon points, you can use the formula below. Everything I have found shows that it is reliably accurate. Of course there is probably some variable that needs to be accounted for that makes it perfect, but this works well enough for my purposes:


```
=ACOS(COS(RADIANS(90-Lat1))    *COS(RADIANS(90-Lat2))   +SIN(RADIANS(90-Lat1))    *SIN(RADIANS(90-Lat2))   *COS(RADIANS(Long1-Long2)))*3958.756
```


----------



## 05newtothis (Jul 8, 2013)

Thats funny...not the kidney-bladder thing the Dunkin' Donuts!!  Got me vote!


----------



## TinaP (Jul 8, 2013)

Barry Katcher said:


> What I would really like to see is the ability to enter the two zip codes, plus a third cell with a brief description of my urologists diagnosis of my kidney-bladder functions, and have Excel calculate how many pit stops will be needed on the journey.  Then, upload it to Mapquest and have it show little urinal icons at all the available Dunkin' Donuts along the route.


Possibly the most useful function ever suggested.  Although urinals fail me due to anatomical differences.


----------



## RobMatthews (Jul 8, 2013)

TinaP said:


> Possibly the most useful function ever suggested. Although urinals fail me due to anatomical differences.



At the risk of being the one to "go there"; maybe you've just gotta ... Nah, on second thoughts, I'll just let that go.


----------



## diddi (Jul 11, 2013)

i wrote a function that does what you want using the googlemaps API.  its fast and accurate for actual road distance rather than lat/long triangulation


----------



## mnordeen (Jul 11, 2013)

Well I found this, I didn't write it but it works. its in KM but that is an easy conversion if you want Miles.

You need to enable the Reference; In VBA Tools/Reference Select "Microsoft XML,V6.0"

in Cell say C1 use 
	
	
	
	
	
	



```
=G_Distance(A1,B1)
```
 < type the address or zip to/from in A1 and B1.


```
Function G_DISTANCE(Origin As String, Destination As String) As DoubleDim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
    G_DISTANCE = 0
    On Error GoTo exitRoute
    Origin = Replace(Origin, " ", "%20")
    Destination = Replace(Destination, " ", "%20")
    Set myRequest = New XMLHTTP60
    myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & Origin & "&destination=" & Destination & "&sensor=false", False
    myRequest.send
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
    If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function
```

I believe this can be modified to find public bathrooms.


----------



## Gerald Higgins (Jul 24, 2013)

I for one would find such a function useful.

I'm happy to use Google maps to find the road distance between two places.

But from time to time I've needed to get data for many different journeys, and some function like this would save alot of time over looking them up individually on Google.

*Diddi* - would you care to share your method ?

*mnordeen* - I tried your solution but couldn't get it to work.
I'm using Excel 2007. 
I'm afraid I'm not hot on VBA or UDFs, but I copied your code exactly as is, into a standard module, and the very first line is highlighted in red, I think it's having a problem with the "...my request..." section.
Any ideas ?

If I can get mnordeen's proposal to work, I am confident that I could get it to work repetitively for a long list of journeys.


----------



## mnordeen (Jul 24, 2013)

Gerald,
Did you enable the reference?
Open excel, open VBA then from the tool bar at the top (Tools)/(Reference Select) "Microsoft XML,V6.0"
I have it working on 2003 and 2007, it works like a champ.. B>)


----------



## TinaP (Jul 24, 2013)

The first line should be two lines...

```
Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim myRequest As XMLHTTP60
```

As far as I know, there's no such thing as DoubleDim.


----------



## mnordeen (Jul 24, 2013)

Thanks Tina,
sorry everyone I guess I got in a hurry because for once I thought I had an answer.


----------



## TinaP (Jul 24, 2013)

I thought you were just trying to prove your signature.  


> If you do it right, No one will know you've done anything at all.


I was thoroughly confused until I re-read your post saying that the distances were in km.


----------



## diddi (Jul 24, 2013)

@Gerald
I have 2 comboboxes and a couple of labels on a udf.  when setting up the contents of the comboboxes, make sure that the place names match the syntax used by google maps.

this is straight from my project and works great in Australia.

```
Sub GetMapsDistances()
    Dim xhrRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim ixnlDistanceNodes As IXMLDOMNodeList
    Dim ixnNode As IXMLDOMNode
    Dim TtlDist As Long
    
    ' Donated to MrExcel users by diddi
    ' Read the data from the website
    
    Set xhrRequest = New XMLHTTP60
    xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & ComboBox2.Value & "&destination=" & ComboBox3.Value & "&sensor=false", False
    xhrRequest.send

    Set domDoc = New DOMDocument60
    domDoc.loadXML xhrRequest.responseText

    Set ixnlDistanceNodes = domDoc.selectNodes("//step/distance/value")

    '  Total up the distance from node to node
    TtlDist = 0
    For Each ixnNode In ixnlDistanceNodes
        TtlDist = TtlDist + Val(ixnNode.Text)
    Next ixnNode

    Label13.Caption = "One way distance is about " & Int(TtlDist / 1000) & "km"
    Label14.Caption = "Return trip is about " & Int(TtlDist * 2 / 1000) & "km"
    
    Set ixnNode = Nothing
    Set ixnlDistanceNodes = Nothing
    Set domDoc = Nothing
    Set xhrRequest = Nothing
End Sub
```


----------



## Gerald Higgins (Jul 25, 2013)

nmordeen and TinaP - thank you. I should have said, I had enabled the reference, I'm guessing it was the glitch that Tina spotted that was causing my problem. I'm not in a position to check right now, but it sounds likely.

diddi - thanks for sharing your method as well.

I will try both of these methods - it might be next week now before I can do it properly, so thanks in advance to all !


----------



## schielrn (Jul 26, 2013)

Both solutions I believe return kilometers, so in the US, you would want to multiply by 0.621371 to get the results in miles.

Or change the functions to return strings instead of doubles and use:

//leg/distance/text instead of //leg/distance/value

You can also return the travel time as well.


----------



## Gerald Higgins (Aug 5, 2013)

*nmordeen* - I got your solution to work perfectly (with help from *TinaP*) - this is fantastic ! Thanks very much !
*diddi* - thanks for posting your solution too. As nmordeen's works, I haven't rushed to test your's, but I will try and get round to it.
*schielrn* - you actually need to _divide_ kms by 0.621371 to get miles, not multiply 

Thanks again everyone !


----------



## Kyle123 (Aug 5, 2013)

The only thing to be aware of is that this breaches the terms of use for Google, because of this, I wrote an alternative using Bing maps which does not have the same stipulations on use: Getting Distances and DriveTimes


----------



## Gerald Higgins (Aug 5, 2013)

Kyle123 - thanks for that. 

Which part of the TOU do you think it breaches ?
Is it the bit about disallowing "mass downloads or bulk feeds" ? If yes, is there any word on what qauntity of downloads would qualify as "mass" or "bulk" ?


----------



## Kyle123 (Aug 5, 2013)

Hi Gerald, it's this:


			
				Google said:
			
		

> Use of the Distance Matrix API must relate to the display of information on a Google Map; for example, to determine origin-destination pairs that fall within a specific driving time from one another, before requesting and displaying those destinations on a map. Use of the service in an application that doesn't display a Google map is prohibited.



https://developers.google.com/maps/documentation/distancematrix/


----------



## Gerald Higgins (Aug 5, 2013)

OK thanks Kyle123 - I'll take a look at your Bing workaround.

I was using nmordeen's suggestion.
Although I haven't tested diddi's solution myself, I'm guessing this may also fall foul of Google's ToU ?


----------



## Kyle123 (Aug 5, 2013)

I'm afraid so, they both use the same API


----------



## Superstar31 (Aug 26, 2015)

Atroxell said:


> If you have 2 lat/lon points, you can use the formula below. Everything I have found shows that it is reliably accurate. Of course there is probably some variable that needs to be accounted for that makes it perfect, but this works well enough for my purposes:
> 
> 
> ```
> ...



How do I uses this? 



mnordeen said:


> Well I found this, I didn't write it but it works. its in KM but that is an easy conversion if you want Miles.
> 
> You need to enable the Reference; In VBA Tools/Reference Select "Microsoft XML,V6.0"
> 
> ...


What needs to be changed to make it so Miles and not KM?

And yes I realize this is an old thread, but it covers exactly what I need and hopefully the original people are still subscribed to it!

Well that's a lie.. exactly would be doing the above using long and latitude... as the crow drives!


----------



## shg (Aug 26, 2015)

> What needs to be changed to make it so Miles and not KM?


Multiply by 0.621371


----------



## Kyle123 (Aug 26, 2015)

You don't need a web service to get straight line distance between 2 coordinates, you can do that with a formula


----------



## Superstar31 (Aug 26, 2015)

shg said:


> Multiply by 0.621371


 I thought someone said divide? So I don't need to mess with the module, just take the answer and multiple it my .621371



Kyle123 said:


> You don't need a web service to get straight line distance between 2 coordinates, you can do that with a formula



As the grow drives (driving directions)


----------



## shg (Aug 26, 2015)

How many miles in a kilometer?


----------



## Superstar31 (Aug 26, 2015)

shg said:


> How many miles in a kilometer?



I understand what you're saying but this is the message i saw and nobody corrected it



> schielrn - you actually need to divide kms by 0.621371 to get miles, not multiply


 and it WAS NOT giving me the correct answer.  So I wanted to make sure everything I did was correct! Thanks!


----------



## shg (Aug 26, 2015)

You're welcome.


----------

