I needed to quickly find out the distance between thousands of combinations of Lat/Long points so we could understand how far students were travelling to professional experience placements from their home address. I was planning to use the Google Maps API but when I worked out the possible number of combinations of addresses of Postcode/Suburb combinations in Australia (There are 16,080 by the way) multiplied by my other table which contained 2701 Suburb/Postcode locations – that equates to 4,343,080 possible combinations.
If I were to use the Google Maps API’s I could only run 40,000 driving directions lookups per month for free so it would take me 1085 months to retrieve all the data (or pay over $173,000 to get enough calls to the driving directions API!).
So with that in mind, we looked at whether we could get straight line “As the crow flies” distance using a formula that takes into account the curvature of the earth. We could use Lat/Long points (which we already have for all 16,080 Postcode/Suburb combinations in Australia), and we could link this to the secondary table’s suburb/postcode (which then linked to the same list of suburb/postcode lat long points) to get a start and end Lat/Long.
I found an Excel formula at http://bluemm.blogspot.com/2007/01/excel-formula-to-calculate-distance.html which contained exactly the formula I needed to provide distance (“as the crow flies”) between the points.
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371
Obviously replace the Lat1, Lat2, Long1 and Long2 with cell references in your own spreadsheet. The 6371 refers to the radius of the earth in KM. To get the distance between points in Miles you just replace 6371 with 3959 (according to a Google search).
Obviously this formula is not 100% perfect due to the rugged and mountainous terrain that is the earth, but it’s a ‘close enough is good enough’ formula to get some answers to distances really quickly!
Recent Comments