Distance calculation for lat/lon's?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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"
 
AgelessWonder said:
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"
Click to expand...

There is no Acos function in Access. If you look up "Derived Math
Functions" in Access help, it tells you what to use instead.
 
Was wondering if anyone came up with an answer...I tried to look up "derived
Math Functions in access help, but turned up nothing...
 
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)
 
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)
 
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;

[email protected] said:
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)


John Spencer (MVP) said:
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)
Click to expand...
Click to expand...
 
Back
Top