I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called "great circle" calculation. I need perform this calculation on a huge amount of coordinates.
The data looks like that:
| A B C D E F G
--|-------------------------------------------
0 | LAT LON rLAT dLAT dLON a DIST
1 | 52.39964 13.04729 ... ... ... ...
2 | 52.39985 13.04802 ... ... ... ... ???
3 | 52.40116 13.04744 ... ... ... ... ???
4 | 52.40147 13.04722 ... ... ... ... ???
5 | 52.40163 13.04685 ... ... ... ... ???
6 | ... ... ... ... ... ... ...
Now, what I have tried is the following for the field G2
in LibreOffice:
C2=RADIANS(A2)
D2=RADIANS(A2-A1)
E2=RADIANS(B2-B1)
F2=SIN(D2/2)*SIN(D2/2)+SIN(E2/2)*SIN(E2/2)*COS(C1)*COS(C2)
G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371
The result for G2
is20015
which is quite... wrong.
How do I calculate the distance between two points specified by latitude and longitude in Microsoft Excel or LibreOffice Calc? What's wrong with my formula?