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?

Avertin

Have you looked at google maps api? – Nubble

@Amir Raminfar -When I last looked at Google maps api, the limitation was that any information retrieved had to be displayed on a map to satisfy the liscence requirements. I'm not looking to display on a map. – Avertin

Answer 1

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)

Stylography

Answer 2

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)

Ocam

Would you mind explaining the logic behind your Excel formula? I don't see it matching up with the formula on Chip Pearson's site that you link to. Also this assumes a spherical Earth rather than a more realistic ellipsoid, but I guess it's perfectly fine for this application, since the uncertainty will be much smaller than the size of the zip codes... except perhaps in large, compact cities where zip code areas can be pretty small. Maybe I'm being too anal. – Localism

@Jean-Francois Corbett Excels needs to be converted to Radians (I will supply another link in my answer). I wasn't aiming to break it down, and wasnt thinking like you when I wrote it. But you do make a good point. I guess it depends on how exact he will want it to be. Thats why I linked him to another option. – Ocam