Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to calculate distance?

Posted on 2011-09-29
5
Medium Priority
?
244 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:SerinaStar
  • 2
  • 2
5 Comments
 

Expert Comment

by:RaoAnil
ID: 36890039
a = sin²(¿lat/2) + cos(lat1).cos(lat2).sin²(¿long/2)
c = 2.atan2(va, v(1-a))
d = R.c
        where R is earth’s radius (mean radius = 6,371km);
note that angles need to be in radians to pass to trig functions!
 javaScript :
var R = 6371; // km
var dLat = (lat2-lat1).toRad();
var dLon = (lon2-lon1).toRad();
var lat1 = lat1.toRad();
var lat2 = lat2.toRad();

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
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 1500 total points
ID: 36892119
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
'
    y = Radius * Sin(Deg2Rad(Lat))
    x = Radius * Sin(Deg2Rad(Lon)) * Cos(Deg2Rad(Lat))
    z = -Radius * Cos(Deg2Rad(Lon)) * Cos(Deg2Rad(Lat))

End Sub

Function Deg2Rad(x As Double) As Double
' Degrees to radians
    Deg2Rad = x / 180 * PI()
End Function

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

Open in new window


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
 
LVL 11

Assisted Solution

by:datAdrenaline
datAdrenaline earned 1500 total points
ID: 36892135
I just noticed the Debug.Print lines -- you can delete them.
0
 

Author Comment

by:SerinaStar
ID: 36901207
Wow your right it does look pretty cool. Do you have any suggestions on how to call the function.
0
 

Author Closing Comment

by:SerinaStar
ID: 36914805
Thanks it is a very interesting piece of code.....it's going to work brillantly    :)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question