Zip code distance calculator
I have a spreadsheet of addresses and I need to calculate the distance between all of their zip codes and my zip code. I'm fairly flexible on the method used, but I'm hoping for some sort of webservice or mathematic algorithm. US addresses only. Basically I need to feed in 2 开发者_开发百科zip codes and get out the distance between them.
I'm willing to use Excel formulas or VBA, and I can even code something in C#.net if needed.
How would you go about calculating these distances?
You could use Latitude and Longitude.
Excel:
=IF(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1 - Long2) > 1,
RadiusofEarth * ACOS(1), RadiusofEarth *
ACOS(SIN(Lat1) * SIN(Lat2) + COS(Lat1) * COS(Lat2) * COS(Long1-Long2)))
VB.net imports System.Math
Private Function Distance(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double, ByVal unit As String) As Double
Dim theta As Double = lon1 - lon2
Dim dist = System.Math.Sin(deg2rad(lat1)) * System.Math.Sin(deg2rad(lat2)) + System.Math.Cos(deg2rad(lat1)) * System.Math.Cos(deg2rad(lat2)) * System.Math.Cos(deg2rad(theta))
dist = System.Math.Acos(dist)
dist = rad2deg(dist)
dist = dist * 60 * 1.1515
Select Case unit
Case "K"
dist = dist * 1.609344
Case "N"
dist = dist * 0.8684
End Select
Return dist
End Function
Other Useful Links(First one also mentions VBA alternatives)
ExcelLatLong (Also mentions VBA alternatives)
Zips by Lat Long Lookup
VBA discussion
EDIT: Link added due to comment discussion
More Info(Excel Formula)
It's pretty simple actually. Download a database of zip codes' GPS coordinates, there's plenty of sites that have this data available for download. They would list the coordinates for the center of the zip code.
Use a formula to calculate shortest distance: (ex: http://www.movable-type.co.uk/scripts/latlong.html)
精彩评论