Calculate Mileage from Lat Lon

I have the following snippet of code that gets the lat and lon from a starting point and ending point. It works like a charm. However the mileages are off when compared to any map program, web based or not, such as PC miler. And when I say off I mean by as much as 100 miles off, usually less than what PC miler calculates. Any ideas would be very helpful.
Function GetMiles() As Long
Dim db As Database
Dim rsFrom As Recordset
Dim rsTo As Recordset
Dim DistanceLat As Double
Dim DistanceLong As Double
Dim Distance As Long

Set db = CurrentDb
Set rsFrom = db.OpenRecordset("select zipcode,latitude,longitude from ZipCodes where zipcode= """ & txtStartZip & """")
Set rsTo = db.OpenRecordset("select zipcode,latitude,longitude from ZipCodes where zipcode= """ & txtZip & """")
If rsFrom.RecordCount > 0 Then
    DistanceLat = (rsTo.Fields("Latitude") - rsFrom.Fields("Latitude")) * 69.1
    DistanceLong = (69.1 * (rsTo.Fields("Longitude") - rsFrom.Fields("Longitude")) * (Cos(rsFrom.Fields("Longitude") / 57.3)))
    Distance = (((DistanceLat ^ 2) + (DistanceLong ^ 2)) ^ 0.5)
    GetMiles = Distance
Else
    MsgBox "Not Working"
End If

End Function

Open in new window


for an testing purposes here are the beginning lat and lon for:
Start: Lat: 44.062231  Lon:  -123.168041

End: Lat  37.671778   Lon:  -121.012493
LVL 1
JArndt42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
What are your expected results for your sample data, and what are you actually getting?
0
JArndt42Author Commented:
the result is 448 miles. If you go to google or pc miler and enter 97402 to 95350 zip codes you get 541 miles. those are the lats and lons for both those zip locations.
0
mbizupCommented:
Those map programs take driving distances into account, don't they?

The algorithm you have is pure distance "as the crow flies",  so a shorter distance is to be expected...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JArndt42Author Commented:
so it is. I guess I will have to find a different way of finding my miles huh? Any recommendations?
0
mbizupCommented:
What exactly are you interested in for your results?

 Road mileage (maps)?

Or a more pure distance taking into account things like earth curvature as opposed to a straight line, but not road-based limitations?
0
mbizupCommented:
This picture should give you an idea of just how different some results can be...
http://www.distancefromto.net/
0
JArndt42Author Commented:
I am more interested in road miles as this will be used to calculate fuel usage and cost per mile driven. I have a function i have used in the past that would open Google Maps run the route and then another function that would look at the source code of the results page and key on the mileage. However Google changes their source code ever so slightly every once in a while and I have to go find the difference and change the string my function is looking for. So although that works it is a pain in the butt because I have to send the updated function to all the users after I spend all the time to find what the difference is.
0
mbizupCommented:
Okay - I don't think this is really an Access question so much as an algorithms/math/science/etc question.

You made your issue very clear to me, but I don't have the specific topic expertise to help with it - and I think you would have better results by posting a question with the explanation in your last comment, including the following topics (zones):

Algorithms
Miscellaneous
Math/Science

Maybe include the Access Zone for help with the VBA if needed.  Or post in the more academic zones first to nail down the process/algorithm and then repost here for help with the code.
0
JArndt42Author Commented:
Well I really do not have the time or resources to pursue this issue any further. Thank you for bringing the issue to my attention.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.