How do I calculate distance from one location to many?

January 29, 2009 8:52 PM

How can I figure out the distance from multiple locations to one location?

I have a list of 200 locations and 1 starting point. I would like to know how far it is from each of the locations to the starting point with out entering each separately. The 200 points are in an excel sheet. I have google earth pro if that helps.

posted by jestonb to Travel & Transportation (6 answers total)

If the Earth were flat (or if our distances are close enough that curvature makes negligible difference), the distance between two points is the square root of ( the square of (the points' difference in X) + the square of (the points' difference in Y ) ).

Of course, this is the length of the straight line connecting the two points; it's "as the crow flies", not "as the crow drives".

If you need to take curvature into account, there's are several formula using trigonometry. I've used one (in a database view, naturally), but I don't recall it. Ah: googling tells me you want the Haversine or Vincenty formulas.
posted by orthogonality at 9:04 PM on January 29, 2009

Oh: in Excel, plug the formula in for the first of your 200 points, referring to the origin point using the Excel dollar-sign absolute reference syntax (and the first point using normal cell column-row syntax), then copy it down to the other 199.
posted by orthogonality at 9:07 PM on January 29, 2009

Response by poster: Sorry the locations are places (cities to be extact) and my starting points is an address.
posted by jestonb at 9:37 PM on January 29, 2009

Best answer: Use this web site.

1) Paste in excel file
2) Check "Calculate (straight line) distance from first address in Mi/Km" option in step 4
3) Enjoy the fruits of your labor*

*You may have to click an additional button or two.
posted by niles at 9:50 PM on January 29, 2009 [1 favorite]

Best answer: I just found batchgeocode.com yesterday and can attest to its awesomeness. That said, I've been finding that the yahoo maps geocoding api (which batchgeocode uses) is frequently up to a hundred feet off. Not a big deal when calculating distances between cities, but annoying when you're trying to see if 702 E. Main is a house or the parking lot next to it.
posted by ChrisHartley at 10:38 PM on January 29, 2009

Yeah, that's true. I recently used it to get about 100 addresses placed on a KML file, and they were all off by about a house (so, 100-200 feet maybe?). However, if jestonb is just calculating from given cities to one address, she/he should be able to just punch in the coordinates for that address, and then just trust the location for the city. Google's API just grabs about the middle of the city, so I wouldn't care if it was off by a bit. But that's just me.
posted by niles at 10:48 PM on January 29, 2009