Calculating distances

[Mark A. Sam]

Hello,

I need a report filtered by the distances of cities based on a starting city
(radius search). For example, if the starting point is Buffalo NY, I want
the records within a specified radius of that city. My method would be to
produce a temporary table with distance field then use that as the criteria
for selecting the report results.

What I am looking for is a program which will allow me to calculate that
field from Access and to return the result to the Access table. An Access
program would be preferrable, but unlikely.

Thanks for any help.

God Bless,

Mark A. Sam

 

[Allen Browne]

Hi Mark

One way to do this would be to store the precise latitude and longditude of
each city, and then use great-circle trig. to calculate thd distance between
different places:
http://en.wikipedia.org/wiki/Great-circle_distance

Microsoft did release a Neatcode.mdb that contains the VBA functions to
handle great-circle calculations. I can't see a more recent one, but this
was for Access 95, so you should still be able to convert it into your
version:
http://support.microsoft.com/kb/148402/en-us

 

[Mark A. Sam]

Hello Allen,

This is really excellent. I tried the function called, GreatArcCircle which
promised to return the shorted distance between two points on the globe, but
it is returning me a number I don't understand which isn't the distance in
miles. Maybe you can help me to understand.

Here is the function:

Function GreatArcDistance(Lat1 As Double, Lon1 As Double, Lat2 As Double,
Lon2 As Double, Radius As Double) As Double
'
' Calculates the Great Arc (shortest) distance between 2 locations on the
globe.
'
' Uses functions from Trigonometry
'
Dim X1 As Double, Y1 As Double, Z1 As Double, X2 As Double, Y2 As Double, Z2
As Double
Dim CosX As Double, ChordLen As Double
LatLongToXYZ Lat1, Lon1, Radius, X1, Y1, Z1
LatLongToXYZ Lat2, Lon2, Radius, X2, Y2, Z2
ChordLen = Sqr((X1 - X2) * (X1 - X2) + (Y1 - Y2) * (Y1 - Y2) + (Z1 - Z2) *
(Z1 - Z2))
CosX = 1 - ChordLen * ChordLen / (2 * Radius * Radius)
Debug.Print X1, Y1, Z1
Debug.Print X2, Y2, Z2
Debug.Print ChordLen, CosX
If CosX = 1 Or CosX = -1 Then
GreatArcDistance = 0
Else
GreatArcDistance = Sqr(1 - CosX * CosX) * Radius * PI() / 2
End If
End Function

I tried the longitude/latitude decimal values from Albany NY (42.75,
73.80)to Buffalo NY (42.93, 78.73). I plugged in a radius of 500 and the
value returned was 69.40. Then I tried a Radus of 500 miles and it returned
49.57.

The road miles from Albany to Buffalo are 292.

So I'm not sure if this is the wrong function or I am implementing it in the
wrong way.

If you or anyone else can help me to understand, I would appreciate it. I
haven't had trig since I was in 11th grade in 1968.

God Bless,

Mark

 

[Mark A. Sam]

Never mind. I discovered that the radius is the radius in miles or metres,
depending on the result that you want.

 

[efandango]

Allen,

How can I use that Formula in a query, where I want the query to find all
the Lat/Lon geocodes in a table for a given reference Lat/Lon on the form?.
I'm not a seasoned VBA coder, but know my way around a little.

 

[Allen Browne]

It's been years since I tried applying that in a query.

Hopefully you can find your way through those resources, or perhaps someone
else will comment.

 

[Mark A. Sam]

That is what I did. If you send me your email address I will tell you how I
did it. Send it to

msam
at
TruckloadsDotNet

You need to contruct the email address.

My table has an origin city and state and a destination city and state. I
used queries to calculate the distance to the Origin City and state from a
starting point on a form and the distance to the destination city and state
from a second starting point on the same form. Then it filters a subform
accordingly.

God Bless,

Mark A. Sam