Calculating distance with radians

[David McKnight]

acos(cos($a1)*cos($b1)*cos($a2)*cos($b2) +
cos($a1)*sin($b1)*cos($a2)*sin($b2) + sin($a1)*sin($a2)) * $r;

I would like to convert the above excel formula to us in an access query to
calulcate distance between two cites. a1,b1,a2,b2 are lat & long and are in
degrees so I must first convert to radians. R= radius of earth. Any way to do
this in Access?

 

[Tom Ellison]

Dear David:

Except for the references in your formula to specific spreadsheet cells,
this formula will work in Access, either in a query calculated column, or as
a VBA function. It appears to be Napier's spherical trigonometry.

The complexities will be that, if you are doing this in a query, and given
the coordinates in your cell references, this must operate between the
values in two different rows. If so, the query would need to reference two
different rows using some techniques that are not entirely simple if they
are new to you. Are you needing these differences between all combinations
of rows in your table, or only a selected pair? There is much along these
lines that is not clear from your question.

But as to the formula, other than supplying the variable data (including the
"constant" variable R) to it, there is no problem.

Tom Ellison

 

[David McKnight]

When I try the following Query I get an invalid syntax error.

Home Distance Travel :acos(cos( [Location.Lat-radians] )*cos(
[Location.Long-radians] )*cos( [Location_2.Lat-radians] )*cos(
[Location_2.Long-radians] ) +

cos( [Location.Lat-radians] )*sin( [Location_2.Lat-radians] )*cos( [Location.Long-radians] )*sin( [Location_2.Long-radians] ) + sin( [Location.Lat-radians] )*sin( [Location.Long-radians] )) * 3600

 

[David McKnight]

I think I immproved things with this (not sure what difference is but I had
some weird charactors displayed in edit mode and press delete a couple of
times and "changes" below resulted), but my Access (2003) is not
understanding "ACOS".

Home Distance Travel:
ACOS(Cos([Location.Lat-radians])*Cos([Location.Long-radians])*Cos([Location_2.Lat-radians])*Cos([Location_2.Long-radians])+Cos([Location.Lat-radians])*Sin([Location_2.Lat-radians])*Cos([Location.Long-radians])*Sin([Location_2.Long-radians])+Sin([Location.Lat-radians])*Sin([Location.Long-radians]))*3600
--
David McKnight

When I try the following Query I get an invalid syntax error.

Home Distance Travel :acos(cos( [Location.Lat-radians] )*cos(
[Location.Long-radians] )*cos( [Location_2.Lat-radians] )*cos(
[Location_2.Long-radians] ) +

cos( [Location.Lat-radians] )*sin( [Location_2.Lat-radians] )*cos( [Location.Long-radians] )*sin( [Location_2.Long-radians] ) + sin( [Location.Lat-radians] )*sin( [Location.Long-radians] )) * 3600

--
David McKnight

Dear David:

Except for the references in your formula to specific spreadsheet cells,
this formula will work in Access, either in a query calculated column, or as
a VBA function. It appears to be Napier's spherical trigonometry.

The complexities will be that, if you are doing this in a query, and given
the coordinates in your cell references, this must operate between the
values in two different rows. If so, the query would need to reference two
different rows using some techniques that are not entirely simple if they
are new to you. Are you needing these differences between all combinations
of rows in your table, or only a selected pair? There is much along these
lines that is not clear from your question.

But as to the formula, other than supplying the variable data (including the
"constant" variable R) to it, there is no problem.

Tom Ellison

 

[David McKnight]

I found this reference in th VB derived functions:

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

Not sure how to translate to work with code below. What do I use as "X"? Do
I place the entire (minus Acos) where there is "X" in formula above? I
assume Access understands Atan but not Acos? ie,

Atn(-(Cos([Location.Lat-radians])*Cos([Location.Long-radians])*Cos([Location_2.Lat-radians])*Cos([Location_2.Long-radians])+Cos([Location.Lat-radians])*Sin([Location_2.Lat-radians])*Cos([Location.Long-radians])*Sin([Location_2.Long-radians])+Sin([Location.Lat-radians])*Sin([Location.Long-radians]))*3600
/
Sqr(-(Cos([Location.Lat-radians])*Cos([Location.Long-radians])*Cos([Location_2.Lat-radians])*Cos([Location_2.Long-radians])+Cos([Location.Lat-radians])*Sin([Location_2.Lat-radians])*Cos([Location.Long-radians])*Sin([Location_2.Long-radians])+Sin([Location.Lat-radians])*Sin([Location.Long-radians]))*3600
*
(Cos([Location.Lat-radians])*Cos([Location.Long-radians])*Cos([Location_2.Lat-radians])*Cos([Location_2.Long-radians])+Cos([Location.Lat-radians])*Sin([Location_2.Lat-radians])*Cos([Location.Long-radians])*Sin([Location_2.Long-radians])+Sin([Location.Lat-radians])*Sin([Location.Long-radians]))*3600
+ 1)) + 2 * Atn(1)
--
David McKnight

I think I immproved things with this (not sure what difference is but I had
some weird charactors displayed in edit mode and press delete a couple of
times and "changes" below resulted), but my Access (2003) is not
understanding "ACOS".

Home Distance Travel:
ACOS(Cos([Location.Lat-radians])*Cos([Location.Long-radians])*Cos([Location_2.Lat-radians])*Cos([Location_2.Long-radians])+Cos([Location.Lat-radians])*Sin([Location_2.Lat-radians])*Cos([Location.Long-radians])*Sin([Location_2.Long-radians])+Sin([Location.Lat-radians])*Sin([Location.Long-radians]))*3600
--
David McKnight

When I try the following Query I get an invalid syntax error.

Home Distance Travel :acos(cos( [Location.Lat-radians] )*cos(
[Location.Long-radians] )*cos( [Location_2.Lat-radians] )*cos(
[Location_2.Long-radians] ) +

cos( [Location.Lat-radians] )*sin( [Location_2.Lat-radians] )*cos( [Location.Long-radians] )*sin( [Location_2.Long-radians] ) + sin( [Location.Lat-radians] )*sin( [Location.Long-radians] )) * 3600

--
David McKnight

Dear David:

Except for the references in your formula to specific spreadsheet cells,
this formula will work in Access, either in a query calculated column, or as
a VBA function. It appears to be Napier's spherical trigonometry.

The complexities will be that, if you are doing this in a query, and given
the coordinates in your cell references, this must operate between the
values in two different rows. If so, the query would need to reference two
different rows using some techniques that are not entirely simple if they
are new to you. Are you needing these differences between all combinations
of rows in your table, or only a selected pair? There is much along these
lines that is not clear from your question.

But as to the formula, other than supplying the variable data (including the
"constant" variable R) to it, there is no problem.

Tom Ellison


acos(cos($a1)*cos($b1)*cos($a2)*cos($b2) +
cos($a1)*sin($b1)*cos($a2)*sin($b2) + sin($a1)*sin($a2)) * $r;

I would like to convert the above excel formula to us in an access query
to
calulcate distance between two cites. a1,b1,a2,b2 are lat & long and are
in
degrees so I must first convert to radians. R= radius of earth. Any way to
do
this in Access?

 

[Tom Lake]

I found this reference in th VB derived functions:

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

Not sure how to translate to work with code below. What do I use as "X"?
Do
I place the entire (minus Acos) where there is "X" in formula above? I
assume Access understands Atan but not Acos?

Exactly. There is no Acos in Access. The entire expression (without the
Acos())
goes in there. It might be easier to create a function for it:

Function Acos(X)
Acos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

Then you can use it just as you originally had it.

Tom Lake

 

[Tom Ellison]

Dear Tom:

You're rigtht. I knew that at one time. Access does not have a complete
set of inverse trig functions. I don't understand why.

Sorry I missed this, David! Tom's recent explanation is one way to do it.
I would personally prefer to use the Acos function he proposed. Make sure
it is a Public function. I think that may be the defaule, but I don't keep
track of that either, I just always make it explicit. Terribly odd of me, I
expect. But it doesn't hurt.

Tom Ellison

 

[David McKnight]

How do I do this in a query? How do I make it public? Do I make a module and
then the query will understand what acos means - never done this before.

 

[Tom Ellison]

Dear David:

A function is made public either by default, or explicitly with the key word
Public in front of the first line:

Public Function Acos(X)

You may then use your custom function in any Jet query just like any of the
built-in functions, like Cos(x).

The query will then automatically resolve the reference and use your
function. Do make sure the function doesn't duplicate the name of any of
the built-in functions.

Not too difficult, really. Try it, you'll like it!

Tom Ellison

 

[Tom Lake]

How do I do this in a query? How do I make it public? Do I make a module
and
then the query will understand what acos means - never done this before.

Yes, you put the function in a module. I've never had to prefix
a function name with the word Public before but it couldn't hurt.

Tom Lake

 

[Tom Ellison]

Dear Tom:

My point is that the function MUST be Public. I expect that is the default
if you don't make it so explicitly. I have always done so explicitly. So,
it probably doesn't matter, and you've been entirely correct all along.

Tom Ellison

 

[TheNovice]

David,

I have simular problem with my solution, which got more complex then
expected. I have not been able to get this resolved and found your question.

Q: how did you resolve the and if you can Please tell me the steps