Get Distance

Screen shot

On the left is earth. It is round and has a radius from the center to each point on the surface. The radius of the earth is not the same everywhere, so an approximation is used.

On the right, coordinates for 2 cities are labeled. Unlike standard math where a coordinate number pair has the distance along the X-Axis (horizontal) first and second is the distance along the Y-axis, geological coordinates are usually reported with Latitude first, and then Longitude. Coordinates are in degrees with +/-90° being the extremity for Latitude, and +/- 180° for Longitude.

approximate Latitude/Longitude of a few major cities:

London, UK = 51.509865°, -0.118092°
Cairo, Egypt = 30.06263°, 31.24967°
Paris, France= 48.864716°, 2.349014°
Madrid, Spain = 40.416775°, -3.703790°
Beijing, China = 39.913818°, 116.363625°
Bangkok,Thailand = 13.736717°, 100.523186°
Sydney, Australia = -33.865143°, 151.209900°
New York, New York = 40.750422°, -73.996328°
Chicago, Illinois = 41.811929°, -87.687320°
Denver, Colorado= 39.726303°, -104.856808°
Houston, Texas = 29.813142°, -095.309789°
Los Angeles, California = 33.973951°, -118.248405°
Honolulu, Hawaii = 21.315603°, -157.858093°
Anchorage, Alaska = 61.217381°, -149.863129°

Examples

"Distance from Denver to Chicago is " & Format(GetDistance(39.726303, -104.856808, 41.811929, -87.68732) , "#,##0") & " miles"

Distance from Denver to Chicago is 909 miles

"Distance from Denver to Houston is " & Format(GetDistance(39.726303, -104.856808, 29.813142, -95.309789) , "#,##0") & " miles"

Distance from Denver to Houston is 873 miles

"Distance from Los Angeles to New York is " & Format(GetDistance(33.973951, -118.248405, 40.750422, -73.996328) , "#,##0") & " miles"

Distance from Los Angeles to New York is 2,451 miles

"Distance from London to Cairo is " _ & Format(GetDistance(33.973951, -118.248405, 40.750422, -73.996328) , "#,##0") & " mi = " & Format(GetDistance(33.973951, -118.248405, 40.750422, -73.996328, 2) , "#,##0") & " km"

Distance from London to Cairo is 2,451 mi = 3,945 km

Logic

To convert degrees to radians, divide by 180/pi, which is 57.2958 (since there are pi radians in a half circle, which is 180°).

Sin (sine) of an angle is is opposite/hypotenuse.

Cos (cosine) of an angle is adjacent/hypotenuse.

Atn (arc tangent) = the inverse of tangent (opposite/adjacent); the arc tan is the angle in radians for a given tangent.

A circle has 2(pi) radians (360°). The distance all around it is the circumference, which is 2(pi)R ... or (pi)D -- remember that from school? Therefore: the length of an arc is the angle in radians times the radius.

Parameters

pLat1 is Latitude of the first point in decimal degrees
pLng1 is Longitude of the first point in decimal degrees
pLat2 is Latitude of the second point in decimal degrees
pLng2 is Longitude of the second point in decimal degrees

Optional:

pWhich: 1=statute miles (default), 2=kilometers, 3=nautical miles

Code

'*************** Code Start *****************************************************
' Purpose  : Get distance between 2 points of Latitude and Longitude
' Author   : crystal (strive4peace)
' Return   : Double
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'-------------------------------------------------------------------------------
'                              GetDistance'-------------------------------------------------------------------------------
' 
Public Function GetDistance( _
   pLat1  As Double _
   , pLng1  As Double _
   , pLat2  As Double _   
   , pLng2  As Double _   
   , Optional pWhich As Integer _     
   ) As Double
'strive4peace 12-13-08, 12-22   

   On Error Resume Next
   Dim X As Double

   Dim EarthRadius As Double   
   
   Select Case pWhich
   Case 2:
      EarthRadius = 6378.7 'kilometers 
   Case 3:
      EarthRadius = 3437.74677 'nautical miles 
   Case Else
      EarthRadius = 3963 'statute miles 
   End Select   
   
    X = (Sin(pLat1 / 57.2958) * Sin(pLat2 / 57.2958)) _
      + (Cos(pLat1 / 57.2958) * Cos(pLat2 / 57.2958) * Cos(pLng2 / 57.2958 - pLng1 / 57.2958)) 

   GetDistance = ABS ( EarthRadius * Atn(Sqr(1 - X ^ 2) / X)  )      
  
End Function
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

VBA > Function > Get Distance