Excel – Zip code distance calculator

distanceexcelvbazipcode

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?

Best Answer

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)

Related Solutions

Excel – Zip Code distance in excel

If you have the latitude and longitude for each zip code, you can use trigonometry to calculate the distance as the crow flies.

There is a free database here that you can download.

To do the calculation, see the info here. And some actual Excel-specific calc examples are here.

Calculate distance between Zip Codes… AND users.

Ok, for starters, you don't really need to use the Haversine formula here. For large distances where a less accurate formula produces a larger error, your users don't care if the match is plus or minus a few miles, and for closer distances, the error is very small. There are easier (to calculate) formulas listed on the Geographical Distance Wikipedia article.

Since zip codes are nothing like evenly spaced, any process that partitions them evenly is going to suffer mightily in areas where they are clustered tightly (east coast near DC being a good example). If you want a visual comparison, check out http://benfry.com/zipdecode and compare the zipcode prefix 89 with 07.

A far better way to deal with indexing this space is to use a data structure like a Quadtree or an R-tree. This structure allows you to do spatial and distance searches over data which is not evenly spaced.

Here's what an Quadtree looks like:

Quadtree

To search over it, you drill down through each larger cell using the index of smaller cells that are within it. Wikipedia explains it more thoroughly.

Of course, since this is a fairly common thing to do, someone else has already done the hard part for you. Since you haven't specified what database you're using, the PostgreSQL extension PostGIS will serve as an example. PostGIS includes the ability to do R-tree spatial indexes which allow you to do efficient spatial querying.

Once you've imported your data and built the spatial index, querying for distance is a query like:

SELECT zip
FROM zipcode
WHERE
geom && expand(transform(PointFromText('POINT(-116.768347 33.911404)', 4269),32661), 16093)
AND
distance(
   transform(PointFromText('POINT(-116.768347 33.911404)', 4269),32661),
   geom) < 16093

I'll let you work through the rest of the tutorial yourself.

http://unserializableone.blogspot.com/2007/02/using-postgis-to-find-points-of.html

Here are some other references to get you started.

Best Answer

Related Solutions

Excel – Zip Code distance in excel

Calculate distance between Zip Codes… AND users.

Related Topic