Distance calculation for lat/lon's?

[Guest]

What is the formula for calculating distance between latitude and longitude.
I use the following formula in Excel but doesn't work in Access.
=3963*acos(Sin("lat/57.2958")*Sin("ycoord/57.2958")+Cos("lat/57.2958")*Cos("ycoord/57.2958")*Cos(("xcoord/57.2958")-("lon/57.2958")))
Where "lat/lon" is the first set of coordinates and "ycoord" and "xcoord" is
the second set of coordinates. Coordinate format is +/- DD.DDDD.
Access did not recognize "Acos"

 

[Brian]

What is the formula for calculating distance between latitude and longitude.
I use the following formula in Excel but doesn't work in Access..
=3963*acos(Sin("lat/57.2958")*Sin("ycoord/57.2958")+Cos("lat/57.2958")*Cos("
ycoord/57.2958")*Cos(("xcoord/57.2958")-("lon/57.2958")))
Where "lat/lon" is the first set of coordinates and "ycoord" and "xcoord" is
the second set of coordinates. Coordinate format is +/- DD.DDDD.
Access did not recognize "Acos"

There is no Acos function in Access. If you look up "Derived Math
Functions" in Access help, it tells you what to use instead.

 

[Guest]

Was wondering if anyone came up with an answer...I tried to look up "derived
Math Functions in access help, but turned up nothing...

 

[John Spencer (MVP)]

From the Online help for Access 97. Still the best help for Access that I can find.

Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)

 

[Guest]

I also found a dervied equation..ARCOS X=- ATN(X / SQR(1-X*X)) + PI/2...
so here is some VB code that will do the trick!
dim ee as double
ee = Cos ( Abs ( [Start_Long] *.0174532- [End_Long] *.0174532))
dim a as double
a = Sin( ( [Start_Lat] * .0174532))
dim b as double
b = Sin (( [End_Lat] * .0174532))
dim c as double
c = Cos( ( [Start_Lat] *.0174532) )
dim d as double
d = Cos ( ( [End_Lat] *.0174532) )
dim f as double
f = a * b
dim gg as double
gg = c * d * ee
dim hh as double
hh = f + gg
'- ATN(X / SQR(1-X*X)) + PI/2
dim jj as double
jj = - Atn ( hh/ Sqr ( 1-hh * hh ) ) + 3.1416/2
k =(jj) * 57.29577951308 * 69.11
output = Int( k)

To find the Bearing, try this!
Round(Atn ( 1/(Cos ( [Start_Lat] *.0174532)* Tan ([End_Lat]*.0174532 ) *1 /
Sin ( Abs ( [Start_Long]*.0174532- [End_Long]*.0174532 ) ) - Sin (
[Start_Lat] * .0174532 )*1/Tan ( Abs ( [Start_Long]*.0174532-
[End_Long]*.0174532 ) )))*180/3.1416,2)

 

[Guest]

the Access query would look like this:
UPDATE Caps SET Caps.DISTANCE =
(-ATN((((Sin([Start_Lat]*0.017453292519943299251994329))*(Sin([End_Lat]*0.017453292519943299251994329)))+((Cos([Start_Lat]*0.017453292519943299251994329))*(Cos([End_Lat]*0.017453292519943299251994329))*(Cos(Abs([Start_Long]*0.017453292519943299251994329-[End_Long]*0.017453292519943299251994329)))))/SQR(1-(((Sin([Start_Lat]*0.017453292519943299251994329))*(Sin([End_Lat]*0.017453292519943299251994329)))+((Cos([Start_Lat]*0.017453292519943299251994329))*(Cos([End_Lat]*0.017453292519943299251994329))*(Cos(Abs([Start_Long]*0.017453292519943299251994329-[End_Long]*0.017453292519943299251994329)))))*(((Sin([Start_Lat]*0.017453292519943299251994329))*(Sin([End_Lat]*0.017453292519943299251994329)))+((Cos([Start_Lat]*0.017453292519943299251994329))*(Cos([End_Lat]*0.017453292519943299251994329))*(Cos(Abs([Start_Long]*0.017453292519943299251994329-[End_Long]*0.017453292519943299251994329)))))))+3.141592653589793238/2)*3959.7113221489588;

I also found a dervied equation..ARCOS X=- ATN(X / SQR(1-X*X)) + PI/2...
so here is some VB code that will do the trick!
dim ee as double
ee = Cos ( Abs ( [Start_Long] *.0174532- [End_Long] *.0174532))
dim a as double
a = Sin( ( [Start_Lat] * .0174532))
dim b as double
b = Sin (( [End_Lat] * .0174532))
dim c as double
c = Cos( ( [Start_Lat] *.0174532) )
dim d as double
d = Cos ( ( [End_Lat] *.0174532) )
dim f as double
f = a * b
dim gg as double
gg = c * d * ee
dim hh as double
hh = f + gg
'- ATN(X / SQR(1-X*X)) + PI/2
dim jj as double
jj = - Atn ( hh/ Sqr ( 1-hh * hh ) ) + 3.1416/2
k =(jj) * 57.29577951308 * 69.11
output = Int( k)

To find the Bearing, try this!
Round(Atn ( 1/(Cos ( [Start_Lat] *.0174532)* Tan ([End_Lat]*.0174532 ) *1 /
Sin ( Abs ( [Start_Long]*.0174532- [End_Long]*.0174532 ) ) - Sin (
[Start_Lat] * .0174532 )*1/Tan ( Abs ( [Start_Long]*.0174532-
[End_Long]*.0174532 ) )))*180/3.1416,2)

From the Online help for Access 97. Still the best help for Access that I can find.

Arccos(X) = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)