Excel formula to calculate the distance between multiple points using lat/lon coordinates

Question

I'm currently drawing up a mock database schema with two tables: Booking and Waypoint.

• Booking stores the taxi booking information.

• Waypoint stores the pickup and drop off points during the journey, along with the lat lon position. Each sequence is a stop in the journey.

How would I calculate the distance between the different stops in each journey (using the lat/lon data) in Excel?

Is there a way to programmatically define this in Excel, i.e. so that a formula can be placed into the mileage column (Booking table), lookup the matching sequence (via bookingId) for that journey in the Waypoint table and return a result?

Example 1:

A journey with 2 stops:

1   1   1   MK4 4FL, 2, Levens Hall Drive, Westcroft, Milton Keynes 52.002529   -0.797623
2   1   2   MK2 2RD, 55, Westfield Road, Bletchley, Milton Keynes   51.992571   -0.72753

4.1 miles according to Google, entry made in mileage column in Booking table where id = 1

Example 2:

A journey with 3 stops:

6   3   1   MK7 7DT, 2, Spearmint Close, Walnut Tree, Milton Keynes 52.017486   -0.690113
7   3   2   MK18 1JL, H S B C, Market Hill, Buckingham              52.000674   -0.987062
8   3   1   MK17 0FE, 1, Maids Close, Mursley, Milton Keynes        52.040622   -0.759417

27.7 miles according to Google, entry made in mileage column in Booking table where id = 3

Answer 1

If you want to find the distance between two points just use this formula and you will get the result in Km, just convert to miles if needed.

Point A: LAT1, LONG1 Point B: LAT2, LONG2

ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-lat2)) *COS(RADIANS(long1-long2)))*6371

Regards

Answer 2

Until quite recently, accurate maps were constructed by triangulation, which in essence is the application of Pythagoras’s Theorem. For the distance between any pair of co-ordinates take the square root of the sum of the square of the difference in x co-ordinates and the square of the difference in y co-ordinates. The x and y co-ordinates must however be in the same units (eg miles) which involves factoring the latitude and longitude values. This can be complicated because the factor for longitude depends upon latitude (walking all round the North Pole is less far than walking around the Equator) but in your case a factor for 52^o North should serve. From this the results (which might be checked here) are around 20% different from the examples you give (in the second case, with pairing IDs 6 and 7 and adding that result to the result from pairing IDs 7 and 8).

Answer 3

Since you say accuracy is not important, and assuming distances are small (say less than 1000 miles) you can use the loxodromic distance.

For this, compute the difference of latitutes (dlat) and difference of longitudes (dlon). If there were any chance (unlikely) that you're crossing meridian 180º, take modulo 360º to ensure the difference of longitudes is between -180º and 180º. Also compute average latitude (alat).

Then compute:

distance= 60*sqrt(dlat^2 + (dlon*cos(alat))^2)

This distance is in nautical miles. Apply conversions as needed.

EXPLANATION: This takes advantage of the fact that one nautical mile is, by definition, always equal to one minute-arc of latitude. The cosine corresponds to the fact that meridians get closer to each other as they approach the poles. The rest is just application of Pythagoras theorem -- which requires that the relevant portion of the globe be flat, which is of course only a good approximation for small distances.

Answer 4

It all depends on what the distance is and what accuracy you require. Calculations based on "Earth locally flat" model will not provide great results for long distances but for short distance they may be ok. Models assuming Earth is a perfect sphere (e.g. Haversine formula) give better accuracy but they still do not produce geodesic grade results. See Geodesics on an ellipsoid for more details. One of the high accuracy (fraction of a mm) solutions is known as Vincenty's formulae. For my Excel VBA implementation look here https://github.com/tdjastrzebski/Vincenty-Excel