Search code examples
excelformula

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

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

  • Booking stores the taxi booking information.

enter image description here

  • 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.

enter image description here

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

Solution

  • 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 52o 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).