Distance between coordinates, correct formula

    Hello,


    I needed to work out the combinations of 15 locations in groups of 3 as explained here:


    http://www.ozgrid.com/forum/sh…46301&p=522971#post522971


    This gives a large number of spherical triangles. Next I need to use the Haversine formula to work out the distances between these coordinates, i.e. the lengths of the sides of each spherical triangle. I think this formula will work:


    3963.0 * arccos[sin(lat1/57.2958) * sin(lat2/57.2958) + cos(lat1/57.2958) * cos(lat2/57.2958) * cos(lon2/57.2958 -lon1/57.2958)]


    This works from the coordinates in decimal degrees directly rather than converting them to radians. Is it possible to use this formula in Excel? Lat1 is E, Long1 is F, Lat2 is G and Long2 is H. I may have to convert the coordinates into radians first and use this formula:


    3963.0 * arccos[sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1)]


    I'm not sure how these formulas work with north and south latitudes, north is usually written as positive and south as negative but perhaps they need to be written as 180 degrees. For example the south pole would be 0 degrees and the north pole 180 degrees.

    Re: Distance between coordinates, correct formula


    This is more of mathematical problem than strictly an Excel problem. However...

    Excel trignometric functions use Radians so you will need to convert using Lat or Lon * (PI()/180) - which is similar to your /57.2958 but to the precision of Excel's calculation.
    I'm fairly sure you don't need to convert them to true polar co-ordinate angles, they will work as +/- for N/S & E/W

    So your formula becomes:
    =3963*ACOS(SIN(RadCon*$E2)*SIN(RadCon*$G2)+COS(RadCon*$E2)*COS(RadCon*$G2)*COS(RadCon*($F2-$H2)))

    Where RadCon is either a named cell containing =PI()/180 or you can enter it directly as a named constant RadCon using Insert > Name > Define and type =PI()/180 into the Refers to: box.

    Re: Distance between coordinates, correct formula


    Hi zzgorme,
    Nice to meet you.
    I add degrees to Rob's suggestion.
    =3963*DEGREES(ACOS(SIN(RADIANS(lat1/57.2958))*SIN(RADIANS(lat2/57.2958))+COS(RADIANS(lat1/57.2958))*COS(RADIANS(lat2/57.2958))*COS(RADIANS(lon1/57.2958-lon2/57.2958))))
    Also I suggest to have a look at excel files about Vincenty's formulae below link, too.
    http://www.ga.gov.au/geodesy/datums/calcs.jsp
    Regards, junho

    Re: Distance between coordinates, correct formula


    I write Cells K21.
    =3963*degrees(acos(sin(radians(e21/57.2958))*sin(radians(g21/57.2958))+cos(radians(e21/57.2958))*cos(radians(g21/57.2958))*cos(radians(f21/57.2958-h21/57.2958))))
    =6880.185

    sorry,swap f21,h21.
    =3963*DEGREES(ACOS(SIN(RADIANS(E21/57.2958))*SIN(RADIANS(G21/57.2958))+COS(RADIANS(E21/57.2958))*COS(RADIANS(G21/57.2958))*COS(RADIANS(H21/57.2958-F21/57.2958))))

    but,1/57.2958 is pi()/180,try this. i think this function is radians unit. Am I right? Rob is right....
    =3963*ACOS(SIN(E21/57.2958)*SIN(G21/57.2958)+COS(E21/57.2958)*COS(G21/57.2958)*COS(H21/57.2958-F21/57.2958))

    Re: Distance between coordinates, correct formula


    I believe the formula I posted is the correct Excel conversion of the formula you supplied. Checking it against the link supplied by Junho it gives the same results (but in miles, rather than km) allowing for computational accuracy based on the radius of the Earth you supplied (in miles). Also, the formula you are using is technically not a haversine formula but a spherical law of cosines formula. See: http://www.movable-type.co.uk/scripts/latlong.html

    Re: Distance between coordinates, correct formula


    I think it's right with the second formula you give. I plugged in some coordinates that I have on Google Earth and it was within about 60 miles of the right answer. They probably have a different number for the radius of the Earth. I was wondering how I can use this for each set of coordinates, there are 455 in all. This works for the 21ist row, is there a way to generalize it for each row without having to adjust the row numbers manually? Thanks.

    Re: Distance between coordinates, correct formula


    Rob, it was a bit easier because I was worried about converting to radians with north and south latitudes not getting confused. This seems to work properly as well, I tried it with one set with a south latitude and the second set of coordinates with a north latitude. How can I generalize this for all 455 combinations, this seems to work for the 21st row only.

    Re: Distance between coordinates, correct formula


    Its a 'generalised formula' you just need to enter it correctly in the right cell.

    Taking your sample spreadsheet from above:
    In K21: =3963*ACOS(SIN(RadCon*$E21)*SIN(RadCon*$G21)+COS(RadCon*$E21)*COS(RadCon*$G21)*COS(RadCon*($F21-$H21)))

    This is calculating the distance between Lat1, Lon1 and Lat2, Lon2. You should be able to work out how to modify it to work for the other two pairs of co-ordinates in your row and then you can Autofill copy your three formula down all 455 rows.

    Re: Distance between coordinates, correct formula


    Thanks I worked out how to autofill the rows but it is giving some wrong answers as well. It may be when the second longitude is larger than the first longitude. For example on Google Earth I plotted


    2 -13.9865 107.831 3 -48.0027 55.505

    That's the 2nd and 3rd set of coordinates in the spreadsheet. Both in the Southern hemisphere hence the minus sign and both West longitude. In the spreadsheet I get a distance of 5304 miles which is 8535 kilometers. On Google Earth I use GEPath to calculate the distance between these as 6172 kilometers. However the first one was right.

    Re: Distance between coordinates, correct formula


    Yes I found the error, but it there is still a divergence from Google Earth. Maybe they are calculating with the Earth not being a perfect sphere.

    Re: Distance between coordinates, correct formula


    There are a few posibilities:

    Your radius of Earth is probably not the same as theirs (converted to kilometres)
    They are almost certainly allowing for the oblate spherical shape of the Earth.
    It is just possible that it is calculational innacuracies because the formula you are using can build up innaccuracy in calculation for small distances. See the link I provided with more detail.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login