PDA

View Full Version : Excel tools - Distance between 2 sets of geocoordinates?



ccm
12-31-2010, 09:33 AM
Would need help with calculating the distance (crow's fly and/or on-the-road) between two sets of coordinates, for Canadian locations. I have a dataset of 608 locations and I need to use the distance calculator to validate the mapping. Much appreciated any help in this direction.

Note: one of Eric's tools does have a Postal code - to - Postal Code distance calculation, which requires the Postal Codes (which in my case are not available). Thank you.

Eric Frost
01-02-2011, 09:10 AM
Check out MileCharter, I think there's a trial version. See ad in our most recent newsletter --
What's New at MP2K Magazine (http://www.mp2kmag.com/update/mappoint.newsletter/2010-12-30/)

Richard -- MileCharter is the right product, correct?

Otherwise I could modify the existing tool if you could send me some sample data.

Eric

Winwaed
01-03-2011, 08:31 AM
Yes MileCharter can calculate all combinations between two groups of pushpins (ie. pushpin sets):

MileCharter: Home Page (http://www.milecharter.com)

Free 14 day trial. Licenses are $60 or $100 depending on license level. CD-ROM version available for about $15 more.

It can also do things like "only find the closest destinations".

Richard

PROFIT100 Consulting
01-03-2011, 03:08 PM
:yes: Hi,
it´s quite easy.

Here the example: FLASH (http://www.flash.mappoint-tools.info/Toronto)

1. We export to excel the addresses of all hotels within a radius of 5 miles of the centre of Toronto. Name, street address, longitude/altitude
Using the ´NearBy-Macro´.
2. In the same way we export the addresses of all nightclubs and tavern.
3. We calculate all distances and driving time between hotels and nightclubs.
Using ´IC-DistanceAnalysis´.

With kind regards
Manfred

PROFIT1oo™ Consulting - Microsoft® MapPoint ISV (Independent Software Vendor)
Seitersweg 55, D-64287 Darmstadt, Germany, Tel.: +49 (6151) 9815-56, GM: Jürgen Käsemann, Manfred Sündorf,
Email: info@profit100.eu (info@profit100.eu) - Internet: http://www.mappoint2010.info (http://www.mappoint2010.info/)

ccm
01-04-2011, 07:45 AM
Thank you.
The MileCharter and the IC-DistanceAnalysis are indeed solutions for high volume exercises. For my one-time validation I would need to use something closer to the shared MapPoint Tools.

Eric Frost
01-05-2011, 03:10 PM
Take a look, it's a very simple spreadsheet & macro, but it does what it says --

http://www.mapforums.com/calculating-route-straight-line-distances-between-lat-lon-coordinates-14991.html

Let me know if anyone would need it adjusted to work with addresses or something else. Also, the driving distance adds significant time, if all you needed was the straight line distance, it would run a lot faster.

Eric

ccm
01-06-2011, 07:37 AM
Eric,

The tool works wonderfully!
It really helps to double check the geocoordinates returned from MapPoint for a given street address and when the mileage is known.

You are right saying that the driving distance calculation is dependant on the default unit of measurement. By the way: can this be controlled via VBA code from Excel? (i.e. to set the units of measurement = miles, so that it would override the default)

On a separate note, thanks to this tool run over a set of 390 locations, I would say 25% of the locations defined at the street address were not properly geocoded - which is quite of an error...
Thank you.
Colin

Eric Frost
01-06-2011, 10:18 AM
Glad to help!


By the way: can this be controlled via VBA code from Excel? (i.e. to set the units of measurement = miles, so that it would override the default)

Yes, I took a look in the Help File. I thought it would be a property of the Map object, but it's not. :detective: It's a property of the Application object. Here's the sample code --


Sub ToggleUnits()
Dim objApp As MapPoint.Application

'Create the Application object
Set objApp = New MapPoint.Application

'Toggle the units
If objApp.Units = geoMiles Then
objApp.Units = geoKm
Else
objApp.Units = geoMiles
End If

End Sub

ccm
01-06-2011, 10:39 AM
I have added the code and adjusted it to always show miles.
It works like a charm!
Many thanks,
Colin

Eric Frost
01-06-2011, 10:46 AM
Thanks, I added a note to the post in the Downloads section so people are aware of it.

Eric