Zip code radius calculation algorithm
May 7th, 2006Here’s a neat little method I figured out for finding geographic points (waypoints) located within a certain radius of a “master point.” To make this a bit more concrete, let’s say you want to find all the zip codes within a 10 mile radius of your zip code, 27701 (Durham, NC). Most good zip code databases will have the latitude and longitude coordinates (waypoints) of each zip code, but this is alot of data (my database has over 79,000 zip code entries). It is far too much data to calculate a distance between the master point and each zip code so you need to find a way to quickly zoom in on the points that are most likely to be within your radius.
I’ve included the image above to help in this illustration. In this image X marks my master point, Durham, NC. The various green stars represent other zip codes around Durham. The first step is to determine the boundaries of our radius. A good rule of thumb to use is to assume 1 degree of longitude is about 70 miles wide while a degree of latitude is about 50 miles (this isn’t exact but we’ll improve this estimate when we calculate the actual radius).
In this case, our target zip codes will be between longitudes ‘a’ and ‘b’ while the latitudes will be between ‘c’ and ‘d.’ These can be calculated easily by adding and subtracting our radius ‘r’ from our center point X to get each limit. We can now query our database to return just those zipcodes where the latitudes are between ‘c’ and ‘d’ and longitudes are between ‘a’ and ‘b’ (example: SELECT * FROM zip WHERE latitude >= ‘c’ AND latitude <= ‘d’ AND longitude >= ‘a’ AND longitude <= ‘b’)This query returns 8 points but a few of these are outside our 10 mile radius we originally set. But now we’ve narrowed our consideration set down from 79,000 to just 10 points. We can calculate the distance between X and each of these points to see if they lie within the radius ‘r,’ leaving those points within the shaded circle as our result.The equation I use to calculate distances between two waypoints is:
$radius = 3959 * acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($long2 - $long1)); where $radius is in miles
There are a number of ways to calculate these distances that can be as complicated as you like but again, this should be close enough for most applications. Realize, however, that if you try to make this more precise by using a more complicated equation, you’ll severely increase the processing time required to calculate distances. Also note that increasing the radius under consideration can exponentially increase processing times depending on the nature and size of your dataset.
Many companies offer scripts for sale that calculate zip code radiuses and distances but with a little work and using this framework, you can create your own application. Take a look at an example of my implementation at: singletracks.
June 3rd, 2008 at 6:43 am
[...] a zip code search on singletracks.com several years ago and we even posted the pseudo-code for building your own zip code radius function on this blog. The singletracks zip code search function is fairly unique because our users [...]
December 4th, 2008 at 10:08 am
I’m not clear on how you calculate points a, b, c, and d. Maybe I’m missing something from your zip code radius calculation.
Thanks in advance for your help.
December 4th, 2008 at 10:51 am
@ Terry - good question, sorry that wasn’t made more clear.
A good place to start is by assuming that 1 degree of longitude is about 70 miles wide. So, for example, if you’re looking at a 10 mile radius you’ll want points a and b to be 1/7 degree of longitude to the left and right of your center point.
Degrees of latitude vary as you move away from the equator but a good estimate for these is about 50 miles apart. So, take you starting point and move up and down 1/5 degree to get c and d.
December 4th, 2008 at 7:26 pm
Thanks admin, that helps a lot.
February 7th, 2009 at 10:48 pm
ok, say my longitude is -73.845 and the latitude is 40.681 and this is in some database, and I want to get the other zip codes in 10 mile radius. How do you calculate degrees by 1/7 ? to get points a and b?. Whats the formula for something like that?. Any examples?
Thanks, John
February 8th, 2009 at 6:18 am
@ John C, it’s very simple: -73.845 ± 1/7 (or 0.143) and 40.681 ± 1/5.
February 8th, 2009 at 8:54 pm
Thanks for the fast reply, sorry I have no idea what the ± symbol is? is that a plus-minus sign?. or I guess I can just use 0.143?. When doing this calculation?. Finally, is it on the SQL side?, because I notice you do a select statement like so.
SELECT * FROM zip WHERE latitude >= ‘c’ AND latitude = ‘a’ AND longitude <= ‘b’).
Or is it like, get the zip codes lat & long where zip_code=’somezip’ in a query, then calculate the c,d,a,b on this result set of the lat & long returned. Then do another query using the calculation off of that?. Thanks so much for sharing and taking the time to reply.
Best, John C
February 9th, 2009 at 7:33 am
@ John C, my intent was to keep this article as general as possible so it would be applicable to any scripting/database language but perhaps a specific example will be helpful to others.
So - The SQL statement you would use to get the initial set of records within 10 miles of your target coordinates would be:
SELECT * FROM zip WHERE latitude >= '40.681 - 0.20' AND latitude < = '40.681 + 0.20' AND longitude >= ‘-73.845 - 0.143′ AND longitude < = '-73.845 + 0.143';
To eliminate the coordinates outside your radius (remember, the set returned by SQL is approximate and is a square, see the image in the post) you’ll need to do some calculations to find exact distances between your target and EACH point. You probably won’t want to do this in SQL since the calculations can be complicated (again, see above) - I choose to do this in PHP instead.
February 9th, 2009 at 3:40 pm
Got it,… thanks so much for taking the time to give a concrete example.
Best, John C
May 7th, 2009 at 4:20 pm
I used the between clause instead of the whole < =… makes more sense if someone looks at the sql later on at least to me
May 26th, 2009 at 11:14 am
Can you please explain where does the number 3959 come from?
Thank you
May 26th, 2009 at 1:08 pm
@Iryna, that’s just part of *one* formula for calculating the distance between two waypoints. Like I said, there are many ways to calculate the distance, depending on how accurate you want to measure it. For most applications this formula will work just fine.
August 3rd, 2009 at 8:37 pm
Is it possible to do this in VBA? I’ve been trying this calc for some time now and I’m hitting a wall. I’m calculating the distance in VBA and wrote a FUNCTION that should calculate for each possible record. I am then using db.Execute to call a SQL statement that will show the records that fall within the radius. The SQL statement is dependant on the FUNCTION created in VBA, and that’s where I think the problem is. I am very new to programming in both SQL and VBA, any help would be greatly appreciated.
August 3rd, 2009 at 8:56 pm
Here’s the code I keep having an issue with. I get runtime error 3085 “Undefined function ‘GetDistance’ in expression…
Function GetDistance(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
GetDistance = Round(0.621371 * (6371 * ((2 * ASin(Sqr((Sin((Deg2Rad([lat1]) - Deg2Rad([lat2])) / 2) ^ 2 + Cos(Deg2Rad([lat1])) * Cos(Deg2Rad([lat2])) * (Sin((Deg2Rad([long1]) - Deg2Rad([long2])) / 2) ^ 2))))))), 4)
End Function
Private Sub cmdRadCalc_Click()
Dim db As Database
Dim rangeFactor As Double
Dim SQLCalc As String
rangeFactor = 0.014457
Set db = CurrentDb
SQLCalc = “SELECT Zip1 INTO tblRadResults FROM tblZipcodes WHERE tblZipcodes.[Lat]” & _
“BETWEEN txtRadLat-(txtRadDist*rangeFactor) AND txtRadLat+(txtRadDist*rangeFactor)” & _
“AND tblZipcodes.[long] BETWEEN txtRadLong-(txtRadDist*rangeFactor) AND txtRadLong+” & _
“(txtRadDist*rangeFactor)AND GetDistance(txtRadLat, txtRadLong, tblZipcodes.[lat], tblZipcodes.[long]) <= txtRadDist”
db.Execute SQLCalc
DoCmd.OpenTable “tblRadResults”, acViewNormal, acReadOnly
End Sub