Query to calculate distance

I did find this to help you do the actual calculations. To do this in a query all you would do is add the equation into a new field of the query like this:

Distance: ([tableName].[Lat]*[tableName].[Long].......)

Of course this is not the actual equations but that is how it is done.

Using Longitude and Latitude to Determine Distance

Date: 17 Apr 1995 17:47:06 -0400
From: Clarence Warren
Subject: (none)

I've been looking for the equation for finding the distance between two
cities, given the latitude and longitude of both cities. For example: what
is the formula one would use to find the distance between San Francisco
(N37 37' 00" latitude, W122 22' 00" longitude) and Paris (N48 44' 00"
latitude, E02 23' 00" longitude.)?

I'm trying to write a program in Visual Basic that will include this distance
calculation in it.

Any help that members of "The Swat Team" can offer would be greatly
appreciated.

--------------------------------------------------------------------------------


Date: 17 Apr 1995 21:38:43 -0400
From: Dr. Ken
Subject: Re: distance on a sphere

Hello there!

This problem can be most easily solved by using spherical coordinates on the
earth.  Have you dealt with those before?  Here's the transformation from
spherical coordinates to normal rectangular coordinates, where a=latitude
and b=longitude, and r is the radius of the earth:

x = r Cos[a] Cos[b]
y = r Cos[a] Sin[b]
z = r Sin[a]

Then we'll use the following property of the dot product (notated [p,q]):

[p,q] = Length[p] * Length[q] * Cos[angle between p & q]

Now, any vector that points to a point on the surface of the earth will have
length r.  So the right side we have r^2 * Cos[angle between p & q].  On the
left side, we can pull the r's out of the dot product, and cancel them with
the r's on the right side.  Let t represent the angle between p and q.  Then
if the latitude and longitude of our two cities, p and q, are (a1,b1) and
(a2,b2), we have

Cos[a1] Cos[b1] Cos[a2] Cos[b2] + Cos[a1] Sin[b1] Cos[a2] Sin[b2]
    + Sin[a1] Sin[a2]  =  Cos[t]

So you can compute the angle t as a function of a1, b1, a2, b2, which are
the latitudes and longitudes of our cities p and q.  Then visualize what
you've got:  draw a great circle through the points p and q.  This is just a
plain old Joe-Schmoe circle of radius r, and the angle t is the angle of the
arc that subtends p and q.  The problem from here on out is just figuring
out what the arc length between p and q is.  The relevant formula is
Arc length = t/360 * 2Pi* r.  So that's your formula.  By substitution, we
have

Arccos[Cos[a1] Cos[b1] Cos[a2] Cos[b2] + Cos[a1] Sin[b1] Cos[a2] Sin[b2]
    + Sin[a1] Sin[a2]]/360 * 2Pi * r

Oh, by the way, West longitude means negative values of b, and South
latitude means negative values of a.

Enjoy, and let us know if I've made mistakes, or something's not clear!

-Ken "Dr." Math

--------------------------------------------------------------------------------


From: Dr. Ken
Date: October 30, 1997

I thought I'd add a couple of remarks that may help some people use the above
formula.  First, of all, you need to make sure your calculator or computer
is using degrees, not radians, to figure out the Sine, Cosine, and ArcCosine
functions.  If you're using a calculator or computer that uses radians, then
use a different version of the formula:

Arccos[Cos[a1] Cos[b1] Cos[a2] Cos[b2] + Cos[a1] Sin[b1] Cos[a2] Sin[b2]
    + Sin[a1] Sin[a2]] * r

Also, keep in mind that these formulae don't take into account the squashed
nature of the earth.  As you may know, the earth is kind of fat around the
equator, as a result of the centrifugal force it gets from spinning on its
axis.  So that will throw a little error into these calculations.  I've
never tried to come up with a formula that takes the squashing into account,
and I suspect it might be hard.

-Dr. Ken
 The Math Forum
   
 
HTH
berg

I have used this simplified Formula and it seems to work well.


DistanceLat = (Latitude_To - Latitude_From) * 69.1

DistanceLong = (69.1 * (Latitude_To - Longitude_From) * (Cos(Latitude_From / 57.3)))

Distance = (((DistanceLat ^ 2) + (DistanceLong ^ 2)) ^ 0.5)
       
Hope this helps

George

If I understand you correctly, you want to calculate the distance between New York and Calcutta but NOT between Calcutta and New York - correct?

I don't think you can devise a query which would do this.  You could however incrementally move through a recordset, doing the calculation against every record AFTER the current record, then move next.

I think the easiest way to do this is to use  a two dimensional array.
Say you have ten locations then I think I'm right in saying that you want to end up with  9!  distances. (i.e. 9! = 9+8+7+....+1).
The code would be:
Sub CalcDist()

Dim I as integer, J as integer
Dim MyArray(10,10) as double, DISTANCE as Double

'Start with the first location I = 1
For I =1, 10
    'Loop through all locations
    For J = 1, 10
        'If location I and J are the same then the distance is zero
        If J = I then
            MyArray( I,J) = 0
        Else
            Work out distance between location I and J put result equal to DISTANCE
             MyArray(I,J) = DISTANCE
        End if
    Next
Next

End Sub

The nice thing about using this is that the distance between location 5 and 3 for example is the array element MyArray(5,3) etc. This should make it simple to place it in a logical table.

If the number of location was varing and user defined you can use the ReDim function to adjust the size of the array you use.
I hope this is alright

Doobs

qcoval
Just fo my own interest: In your simplifed formula where do the numbers 69.1 and 57.3 come from?
Doobs

One degree of latitude is equal to 69.1 miles. One degree of longitude is equal One degree of latitude is equal to 69.1 miles. One degree of longitude is equal to 69.1 miles at
the equator. North or south of the equator, one degree of longitude is a smaller distance. It's
reduced by the cosine of the latitude. Dividing the latitude number by 57.3 converts it to radians.
DistLat = 69.1 * (Lat2-Lat1)
DistLong = 69.1 * (Lg2-Lg1) * cos(Lat1 / 57.3)
Dist = (DlstLat2 (squared) + DlstLong2(squared)^ 0.5

If you don't want to use the COS function, then a good approximate solution is:
DistLat = 69 1 * (Lat2 - Lat1)
DistLg = 53 * (Lg2 - Lg1)
Dist = (DistLat2 (squared) + DistLong2(squared))^ 0.5

This is from info I downloaded concerning calculation of distances.

This can be used and called from a function or can be used in a query depending on how you want to calculate distances. If you give me a little more info on what you want to achieve, I may be able to help you.

Thanks qcoval, I wasn't trying to acheive anything i was just interested. Whenever i see "magic" numbers like that in equations I just like to know where they came from.
Many thanks
Doobs

Sorry, the code I originally posted needs a slight change.
The start of the second FOR loop read:
For J = 1, 10
It should read:
For J = I, 10
which was the whole point as you don't want to do the calculation twice.

One other thing. When starting the FOR loop, the comma should be replaced by the word "To".

Doobs

Sorry, the code I originally posted needs a slight change.
The start of the second FOR loop read:
For J = 1, 10
It should read:
For J = I, 10
which was the whole point as you don't want to do the calculation twice.

One other thing. When starting the FOR loop, the comma should be replaced by the word "To".

Doobs

thank you experts.
some body has asked for some more info. on my question. I am having 1000 location on my access table. I want to calculate distance of each location from every other location and place the results on another table. If I use 2 loop stmt, it is taking nearly 15-20 min. to perform calculation for 1,000,000 records. Then I used a loop like this
Do While Not rsctract.EOF
rtract = rsctract("censustract")
rlongitude = rsctract("longitude")
rlatitude = rsctract("latitude")
mycommand.CommandText = "insert into distance (reftract,centract,dist,mp) select '" & rtract & _
           "', a.censustract ,(((( " & rlatitude & " - a.latitude) ^ 2) + (( " & rlongitude & " - a.longitude) ^ 2)) ^ 0.5) * 62 , a.marketpotential from tracts a "
mycommand.Execute
rsctract.MoveNext
Loop

which is taking 10-15 min. Is there any other way to minimize time for calculation(optimize the query)?

You could try using a Transaction Process. This will buffer data to memory speeding uo the process
Doobs
ps Are you reaching the limitations of your PC?

doobs,
     Can you pl. explain me how to do this transaction process. I can't get yr question. My PC is having 64 mb ram & 233MHz of speed.

thank you doobs. This helped me a lot.