Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate Mileage from Lat Lon

Posted on 2012-04-04
9
Medium Priority
?
838 Views
Last Modified: 2012-04-04
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
0
Comment
Question by:JArndt42
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37807085
What are your expected results for your sample data, and what are you actually getting?
0
 
LVL 1

Author Comment

by:JArndt42
ID: 37807100
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37807158
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:JArndt42
ID: 37807169
so it is. I guess I will have to find a different way of finding my miles huh? Any recommendations?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37807198
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37807212
This picture should give you an idea of just how different some results can be...
http://www.distancefromto.net/
0
 
LVL 1

Author Comment

by:JArndt42
ID: 37807231
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37807283
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
 
LVL 1

Author Closing Comment

by:JArndt42
ID: 37807317
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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