# How to calculate distance?

Hi,

I how can I calculate distance between 2 points. I have created a table with the name of towns, post codes and coordinates.

The table look like this:
Town/Site     Post Code    Longitude     Latitude
Moranbah     4740           148.04577   -22.00091

I would like to use the table to give me the distance between the 2 points in Km's.
So If I created 2 combo box's To: and From: a Message Box would appear and say the distance between i.e Moranbah and Dysart is 60.271Km.

Is this possible to do using MS Access 07?
If so where do I begin?

Many Thanks
Serina
###### Who is Participating?

Commented:
This is an Office/VBA question, so the .Net code provided by RaoAnil is definatly not copy/pastable! :)

If you have MapPoint available to you, your task can utilize automation of MapPoint.  Using MapPoint from Word, Access and Excel

----

If you do not have MapPoint available to you, then something like this should do the trick.  I did not write this, as far as I know it came from here (note the version, Access 95) -- I just pasted it in a module if I ever needed it again :)

``````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

Sub LatLongToXYZ(Lat As Double, Lon As Double, Radius As Double, x As Double, y As Double, z As Double)
'
' Converts Latitude, Longitude, Radius to 3d-Cartesian coordinates
'
' Assumes:
'   X axis runs through 270 (-X) and 90 (+X) Latitude
'   Y axis runs North (+Y) to South (-Y)
'   Z axis runs through 0 (-Z) and 180 (+Z) Latitude
'

End Sub

Function Deg2Rad(x As Double) As Double
Deg2Rad = x / 180 * PI()
End Function

Function PI() As Double
PI = Atn(1) * 4
End Function
``````

You will need to know the radius of the Earth -- unfortunately I have never used this code, so I cannot atest to its usability or accuracy -- but it looked cool, so I kept it :)
0

Commented:
a = sin²(¿lat/2) + cos(lat1).cos(lat2).sin²(¿long/2)
c = 2.atan2(va, v(1-a))
d = R.c
note that angles need to be in radians to pass to trig functions!
javaScript :
var R = 6371; // km

var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
Math.sin(dLon/2) * Math.sin(dLon/2) * Math.cos(lat1) * Math.cos(lat2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var d = R * c;
0

Commented:
I just noticed the Debug.Print lines -- you can delete them.
0

Author Commented:
Wow your right it does look pretty cool. Do you have any suggestions on how to call the function.
0

Author Commented:
Thanks it is a very interesting piece of code.....it's going to work brillantly    :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.