Help Calculating Distance

[cmcquain]

I am trying to code an access database that will allow a user to input two zip codes and return the distance in miles between the two. I have a table that lists the zip codes with the latitudes and longitudes. Below is the code that I have so far. Any help would be great.

DB= zipcodes
Table = ZIP Codes
Fields = ZIP Code, Latitude, Longitude
On the form I have two text boxes to enter info called txtZipCode1, txtZipCode2



Private Sub cmdCalculateDistance_Click()
Dim dbsZipCodes As Database
Dim rstTable As Recordset
Dim rstZip1 As Recordset
Dim rstZip2 As Recordset
Dim rstLatitude1 As Recordset
Dim rstLatitude2 As Recordset
Dim rstLongitude1 As Recordset
Dim rstLongitude2 As Recordset
Set dbsZipCodes = CurrentDb
With dbsZipCodes

'Open the Recordset objects
Set rstTable = .OpenRecordset("Zip Codes", dbOpenTable)
Set rstZip1 = dbsZipCodes.OpenRecordset("ZIP Codes")
Set rstZip2 = dbsZipCodes.OpenRecordset("ZIP Codes")

rstZip1.Index = "PrimaryKey"
rstZip1.Seek "=", txtZipCode1
rstLatitude1.Index = "PrimaryKey"
rstLatitude1.Seek "=", Latitude


MsgBox rstLatitude1




If rstZip1.NoMatch Or rstZip2.NoMatch Then
MsgBox "No Match"
Else

End If

rstZip1.Close
rstZip2.Close
dbsZipCodes.Close

Set rstZip1 = Nothing
Set dbsZipCodes = Nothing

End With
End Sub

 

[gemma-the-husky]

http://www.movable-type.co.uk/scripts/gis-faq-5.1.html

this sort of formulas and explanations may help. for small distances you can ignore the spherical nature of the earth. for larger distances you have to take it into account. what you are actually calculating is the number of seconds/minutes/degrees of arc between the locations represented by the two lats/longs

what can be confusing, is that these calculations do not return any distance (apparently)

however, it happens that a nautical mile is taken to represent 1 minute of arc - there are 60*360 = 21600 minutes in a circle, which means that the earth's circumference is a nominal 21600 nautical miles. A nautical mile being 6080 feet. The article dicusses various distortions
so eg a calculation returning 100 minutes of arc, indicates a distance of 100 nautical miles

this just gives crows-flies distances. these calcs are very quick to do.

road distances are more complex. i am pretty sure sat-navs use direct distances for things like objects of interest, but when you pick one, they then have to compute a detailed route. you would not be able to quickly compute detailed routes for a number of points.

 

[jdraw]

You may get some ideas from Chip Pearson's discussion and samples at
http://www.cpearson.com/Excel/latlong.aspx

Great Circle distance.

I did something similar a few years ago. I was trying to find Businesses within x miles of a specific Lat Long. I had Canadian Postal Codes and Business addresses.

There are a couple of issues.

First zip codes/postal codes are area locators, to reduce a postal/zip code to a point is an approximation. You are basically saying -- I know this zip code covers an area, but I'll assume it is represented by this point (Lat/Long coords).

Second as Dave has pointed out is distance over a curved surface.

If you are looking for distance in miles, the materials at the site noted can be helpful.



There are other sites with some info
http://www.meridianworlddata.com/Distance-Calculation.asp
http://en.wikipedia.org/wiki/Haversine_formula
Good luck.