Zip code distance calculator

Question

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?

Answer 1

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)

Answer 2

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)