Every once in a while a Web developer gets the opportunity to use those math skills he honed in college. (I suppose this is only theoretical, since I didn't go to college.)
We're working on a project that calls for restricting search results to those occurring within a fixed distance from a user-supplied zip code. The premise is simple enough: discover the geographical location of two zip codes and calculate the distance between them. Of course, there are a few prerequisites:
- a database of geocoded zip codes
- a formula for calculating the distance between them
I thought the first resource would be difficult to find, especially considering my budget for this project is exactly $0. But lo and behold, much to my surprise and delight, the folks over at PopularData.com provide such a resource, and it's free! (As well it should be, since it's probably based on the U.S. Census data collected in 2000 or earlier.) They claim that the database is mostly complete, except for a few dropouts (most of which, they say, are military stations, and not too useful to our application.)
I had built an application similar to this one in the past, so I knew somewhere someone had written down the formula for calculating the distance. Remember that it's not just a simple point to point calculation. No, that would be too easy. You see, the Earth is round. (No, really.) And the curvature of the Earth increases the distance between two points. Oh, if only Columbus had been wrong: think of how much gas we'd save!
In fact, the formula for calculating this distance is known as the Haversine formula. You can read more about it, if you wanna; but it basically goes like this (in JavaScript):
var R = 6371; // circumference of the Earth, in kilometers
var dLat = (lat2-lat1).toRad();
var dLon = (lon2-lon1).toRad();
var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
Math.cos(lat1.toRad()) * Math.cos(lat2.toRad()) *
Math.sin(dLon/2) * Math.sin(dLon/2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var d = R * c;
For our application, we basically want a view of our zip code data that represents the set of zip codes within X miles (or kilometers) of a given zip code origin. We'll use that view to filter our other records before returning those to the end-user. To achieve this, I've written three MySQL procedures:
- function km, which performs the calculation between two sets of latitude and longitude coordinates
- function miles, which relies on km to do the calculation, and then converts the result to miles
- procedure inside, which accepts two parameters: a zip code origin, and a maximum distance with units (km for kilometers, and mi for miles)
So, to query those zip codes within a ten mile radius of my home, the SQL looks something like this:
CALL inside('22601', '10mi');
And the results are a bit like this:
"22601",39.1697,-78.1686,"WINCHESTER","VA",0
"22604",39.1676,-78.1686,"WINCHESTER","VA",0.142347455024719
"22655",39.1634,-78.2462,"STEPHENS CITY","VA",4.17041921615601
"22656",39.2137,-78.0901,"STEPHENSON","VA",5.17398071289062
"22602",39.1501,-78.269,"WINCHESTER","VA",5.53605270385742
"22603",39.264,-78.1989,"WINCHESTER","VA",6.70094060897827
"22638",39.2369,-78.2885,"WINCHESTER","VA",7.90875339508057
"22624",39.2719,-78.0998,"CLEAR BROOK","VA",7.94622468948364
"22622",39.2543,-78.0664,"BRUCETOWN","VA",7.98962259292603
"22611",39.1357,-77.9919,"BERRYVILLE","VA",9.72859001159668
To increase the usefulness of the result set, we sort the results of the stored procedure by their distance from the origin: least to greatest.
I hope this turns out to be as useful for you as it is for us. If you'd like to get a head start, feel free to download our source files:
- the data archive from PopularData.com
- the MySQL schema, including a table for storing the zip codes, and the functions and stored procedure
- and a PHP script for mounting the data into MySQL
