Geek Answers Handbook

Postal Code Distance Calculator

I have a table of addresses, and I need to calculate the distance between all their zip codes and my zip code. I am quite flexible in the method used, but I hope for some kind of web service or mathematical algorithm. US addresses only. Basically I need to file 2 zip codes and choose the distance between them.

I am ready to use Excel or VBA formulas, and I can even code something in C # .net if necessary.

How would you rate these distances?

+5
source share
2 answers

It is pretty simple. Download the database of GPS coordinates of zip codes, there will be many sites on which this data is available for download. They will list the coordinates for the zip code center.

Use the formula to calculate the shortest distance: (for example: http://www.movable-type.co.uk/scripts/latlong.html )

+2
source

You can 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 (The first also mentions alternatives to VBA)

ExcelLatLong (Also mentions alternatives to VBA)

Zips by Lat Long Lookup

Discussion VBA

EDIT: link added due to discussion of comments

Additional Information (Excel Formula)

+3
source

More articles:


All Articles