# Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via PHP And MySQL

Update, 11 April 2012: I have created an ASP.NET Web Forms / SQL Server version of this script at http://dougv.com/2012/04/11/getting-all-zip-codes-in-a-given-radius-from-a-known-point-zip-code-via-asp-net/.

Recently asked on Yahoo! Answers:

Does anyone know a good source for programing language for zip codes/distances/database?
Language can be in Java or PHP. Need to find how to write the programing for doing a simple search (5 miles, 10 miles, etc.) from my registered location, when performing a search for the specific distances identified, so the results will be presented. (The way a search is performed on any dating site, when your searching for people near you.) Thanks for your help.

This question actually received a well-informed answer:

Firstly, you will need a listing of all the zip codes you wish to search, and their latitude and longitudes. Without this there is no way to calculate the distance between them!

Take a look at this excellent article:
http://www.pjenkins.co.uk/blog/index.php/2007/04/04/uk_post_code_distance_calculation/

It uses UK postcodes but it explaints everything you need, and it’s in PHP.

The questioner then contacted me, saying the link provided “isn’t what I’m looking for,” by which I think he means, he doesn’t understand how to convert the code at Paul Jenkins’ blog into code that does what he wants.

Paul’s code calculates the distance between two known postal codes; the questioner wants to determine all the postal codes within a given distance. The problem is similar but not the same, so with an appreciative nod to Paul’s work, I’ll mash up an adaptation.

### Step 1: Obtain Geocoded ZIP Codes

The first answerer is absolutely correct; whether we’re using British postal codes or American ZIP Codes, it doesn’t matter: We need those postal codes in a database, including their decimal coordinates.

Thanks to the ZIP Code Database Project at SourceForge.net, we can obtain this data for US Postal Codes in a free CSV file. (Paul is offering the British codes and coordinates through his article.)

Next, we’ll import this data into MySQL, via phpMyAdmin’s Import tab. (Everyone in the free world uses phpMyAdmin to manage his online MySQL database, so I’m not going to go into detail on how to do this.)

While some phpMyAdmin installs support creating tables directly from a CSV file, mine only supports importing SQL scripts. So I first had to extract the CSV file from its ZIP archive, then strip the double-quote delimiters and extra spaces from each column by opening the CSV file in Microsoft Excel. (For those who don’t have Excel, you can import, clean up and export the CSV file with Google Docs.)

I then used the MySQL Data Import tool located at sqldbu.com to convert the CSV file into SQL; it let me upload the cleaned-up CSV file I made in Excel and prompted me to download a new SQL text file that contained the proper statements.

I then uploaded that SQL text file via phpMyAdmin. Finally, I renamed the automatically generated primary key column. It was about 15 minutes from start to finish to get a table of geocoded U.S. ZIP Codes.

### Aside: How Accurate Is Accurate Enough?

Executive summary: The longer your radii, the more reliant your solution needs to be on trigonometry, so the less relevant this solution is to your needs. But for small distances, my linear-math-based squaring method is probably good enough.

This problem confronts us with a series of mathematical and practical dilemmas, all due to the fact that distances over the Earth’s surface aren’t straight lines, they’re arcs, because the Earth isn’t flat.

Thankfully, we can do a fair amount of fudging and hedging and still get accurate-enough results, so long as the distances involved are small. The curvature of Earth is not significant enough, over distances of 5 or 10 (or even up to 100) miles, to justify using trigonometry to find true distance to nearby points. This is especially true because our geocodes are arbitrary in the first place. Even if our coordinates represent the dead center of each town, the chances the places we’re really trying to find within those towns are at the dead center are about zero.

And even though the Earth is not a perfect sphere, once again, the errors introduced by treating it as such in our trigonometric equations are not significant.

On the other hand, the distance between degrees of longitude differs significantly, depending on where you live. Here in Maine, there are about 47 miles between degrees of longitude; in South Florida, there are about 62 miles between degrees. Clearly, we have to take into consideration the longitude of the starting location.

Ideally, we would extract all coordinates from the database table that are likely to lie within our radius — say, by converting the area of a circle represented by 2Πr into the area of a square represented by 2r2 — use that to get some “rough guess” records from the database, then use trigonometry to see whether each point’s coordinates indeed fall within the area of a circular projection of our desired radius.

We, however, are going to cheat in order to keep this simple and efficient. Again, because we are working with what are relatively small distances, and fairly inaccurate coordinates to begin with, we can afford a fudge factor. Ours is going to be to just use a square.

We’re going to get four coordinates, bearing 0, 90, 180 and 270 degrees from our known point (that is, points however many miles North, East, South and West of the known point). We’re then going to query the database for all points that exist within the area of a square formed by those points.

That is, we query for all ZIP Codes that have a latitude less than or equal to the North value and greater than or equal to the South value; plus a longitude greater than or equal to our West coordinate and less than or equal to our East coordinate.

If we were dealing with a radius of hundreds or thousands of miles, we would have to abandon this square technique and stick with trig. **

Consider this:

As you can see, the percentage of extra area remains constant, but becomes significantly more problematic the larger the radius we choose. The chances of a new ZIP code falling inside the 21½ miles of “extra” area in our “5-mile-radius square” is about zero. But at 100 miles, we’ve got a very good chance of catching unintended ZIP Codes within the 8,600 extra miles of area.

The problem with using a square with a long radius is further compounded by the fact that the longer our radius, the more variance there is in the longitudinal coordinates that ought to make up the top and bottom corners of the square.

For example, north of the equator, if we projected the area of a 100-mile-long rectangle that takes up exactly one degree of longitude onto the Earth’s surface above the equator, the top of the box would be narrower than the bottom of the box; the distance between degrees of longitude eventually becomes 0 at the North and South poles.

Again, for small distances that aren’t very far north or south, acting as though the area between degrees of longitude is fixed doesn’t introduce significant errors; but the greater the area covered, the more error that “fudge factor” creates.

In the case of this code, I’m assuming all ZIP Codes exist north of the Equator, and as such, I use the northernmost latitude coordinate to determine which longitudinal coordinate should set the east and west boundaries of the square. That means “extra” ZIP Codes that lie outside the radius selected by the user, will tend to be to the southwest and southeast of the known point.

### Step 2: The Formula

I was fortunate enough to find this article on another UK-based Web site, which provided a formula for finding the latitude and longitude of a point at a known distance and bearing from a given point. And that provides us with pretty much all we need to know to get this script up and running.

Because we have a known distance, d; a known radius of the sphere over which the distance is to be calculated, r; a known bearing, b; and a known geographic point expressed as lat1 and lon1, we can find the North, South, East and West points for our query square, expressed as lat2 and lon2, with this formula:

```lat2 = asin(sin(lat1) * cos(d/r) + cos(lat1) * sin(d/r) * cos(b))
lon2 = lon1 + atan2(sin(b) * sin(d/r) * cos(lat1), cos(d/R) - sin(lat1)*sin(lat2))```

### Step 3: The HTML

We need to know the latitude and longitude of our user’s ZIP Code. We’ll get that by having the user enter his five-digit ZIP Code in a form, then extracting the coordinates via a database query. We also need to know the distance for the radius of our search square-nee-circle. We’ll get that from a select list.

```<form action="<?php echo htmlentities(\$_SERVER['PHP_SELF']); ?>" method="post">
<label>Enter your ZIP Code:
<input maxlength="5" name="zipcode" size="6" type="text" /></label>

<label>Select a distance in miles from this point:</label>
<select name="distance">
<option>5</option>
<option>10</option>
<option>25</option>
<option>50</option>
<option>100</option>
</select>

<input name="submit" type="submit" value="Submit" />
</form>```

### Step 4: The PHP Code

Now for the PHP code that does the actual work:

• We find the latitude and longitude for the supplied ZIP Code via a database query. If we encounter an error or can’t find a match for the supplied ZIP Code, we note that.
• We next get the latitude for the distance provided, at bearings of 0 and 180 from the coordinates we retrieve; and the longitude for bearings of 90 and 270 degrees at the provided distance.
• The database is then queried for all points that fall within the four coordinates. Because of the way they work, northernmost latitudes and easternmost longitudes are always maximum values, anywhere in the world.
• If there are no results or we encounter an error, that fact is reported to the screen. Otherwise, we output the results to two tables: One that shows details for the original ZIP Code, another that shows details for all points within the given radius.

Note in the code below that we have to convert between degrees (in which latitude and longitude are expressed) and radians (in which trigonometry operates). I could have been more elegant and consolidated much of the casting of degrees to radians (and vice-versa); however, I wanted to be explicit about the points at which a conversion is taking place.

```<?php
if(isset(\$_POST['submit'])) {
if(!preg_match('/^[0-9]{5}\$/', \$_POST['zipcode'])) {
echo "<strong>You did not enter a properly formatted ZIP Code.</strong> Please try again.\n";
}
elseif(!preg_match('/^[0-9]{1,3}\$/', \$_POST['distance'])) {
echo "<strong>You did not enter a properly formatted distance.</strong> Please try again.\n";
}
else {
//connect to db server; select database
\$link = mysql_connect('host_name', 'user_name', 'password') or die('Cannot connect to database server');
mysql_select_db('database_name') or die('Cannot select database');

//query for coordinates of provided ZIP Code
if(!\$rs = mysql_query("SELECT * FROM php_zip_code_distance WHERE zip_code = '\$_POST[zipcode]'")) {
echo "<strong>There was a database error attempting to retrieve your ZIP Code.</strong> Please try again.\n";
}
else {
if(mysql_num_rows(\$rs) == 0) {
echo "<strong>No database match for provided ZIP Code.</strong> Please enter a new ZIP Code.\n";
}
else {
//if found, set variables
\$row = mysql_fetch_array(\$rs);
\$lat1 = \$row['latitude'];
\$lon1 = \$row['longitude'];
\$d = \$_POST['distance'];
//earth's radius in miles
\$r = 3959;

//compute max and min latitudes / longitudes for search square
\$latN = rad2deg(asin(sin(deg2rad(\$lat1)) * cos(\$d / \$r) + cos(deg2rad(\$lat1)) * sin(\$d / \$r) * cos(deg2rad(0))));
\$latS = rad2deg(asin(sin(deg2rad(\$lat1)) * cos(\$d / \$r) + cos(deg2rad(\$lat1)) * sin(\$d / \$r) * cos(deg2rad(180))));
\$lonE = rad2deg(deg2rad(\$lon1) + atan2(sin(deg2rad(90)) * sin(\$d / \$r) * cos(deg2rad(\$lat1)), cos(\$d / \$r) - sin(deg2rad(\$lat1)) * sin(deg2rad(\$latN))));
\$lonW = rad2deg(deg2rad(\$lon1) + atan2(sin(deg2rad(270)) * sin(\$d / \$r) * cos(deg2rad(\$lat1)), cos(\$d / \$r) - sin(deg2rad(\$lat1)) * sin(deg2rad(\$latN))));

//display information about starting point
//provide max and min latitudes / longitudes
echo "<table class="\"bordered\"" cellspacing="\"0\"">\n";
echo "<tbody><tr><th>City</th><th>State</th><th>Lat</th><th>Lon</th><th>Max Lat (N)</th><th>Min Lat (S)</th><th>Max Lon (E)</th><th>Min Lon (W)</th></tr>\n";
echo "<tr><td>\$row[city]</td><td>\$row[state]</td><td>\$lat1</td><td>\$lon1</td><td>\$latN</td><td>\$latS</td><td>\$lonE</td><td>\$lonW</td></tr>\n";
echo "</tbody></table>\n\n";

//find all coordinates within the search square's area
//exclude the starting point and any empty city values
\$query = "SELECT * FROM php_zip_code_distance WHERE (latitude <= \$latN AND latitude >= \$latS AND longitude <= \$lonE AND longitude >= \$lonW) AND (latitude != \$lat1 AND longitude != \$lon1) AND city != '' ORDER BY state, city, latitude, longitude";
if(!\$rs = mysql_query(\$query)) {
echo "<strong>There was an error selecting nearby ZIP Codes from the database.</strong>\n";
}
elseif(mysql_num_rows(\$rs) == 0) {
echo "<strong>No nearby ZIP Codes located within the distance specified.</strong> Please try a different distance.\n";
}
else {
//output all matches to screen
echo "<table class="\"bordered\"" cellspacing="\"0\"">\n";
echo "<tbody><tr><th>City</th><th>State</th><th>ZIP Code</th><th>Latitude</th><th>Longitude</th><th>Miles, Point A To B</th></tr>\n";
while(\$row = mysql_fetch_array(\$rs)) {
echo "<tr><td>\$row[city]</td><td>\$row[state]</td><td>\$row[zip_code]</td><td>\$row[latitude]</td><td>\$row[longitude]</td><td>";
echo acos(sin(deg2rad(\$lat1)) * sin(deg2rad(\$row['latitude'])) + cos(deg2rad(\$lat1)) * cos(deg2rad(\$row['latitude'])) * cos(deg2rad(\$row['longitude']) - deg2rad(\$lon1))) * \$r;
echo "</td></tr>\n";
}
echo "</tbody></table>\n\n";
}
}
}
}
}
?>```

You can see a working demo here: http://demo.dougv.com/php_zip_code_distance/

Code on github: https://github.com/dougvdotcom/php_zip_code_distance

Note that I am only including the HTML form and PHP code this time around. I don’t want to have people leeching the ZIP Codes from me, so you’ll have to go through the process in Step 1 to get your data.

**: If you want, you can directly query the MySQL database for all records that lie within range, without using the square method I describe here.

This is a bit more resource-intensive on the MySQL server and you may notice a performance hit versus using the squaring method, but I want to include this method for completeness, especially for those applications that require greater accuracy.

## 65 Comments

1. Hi Doug,

Nice bit of programming! I have a thought. That extra 27% is an annoyance since the user (not having read your blog) will see zipcodes outside of the limit chosen, i.e. "I set a 25 mile limit and I’m getting results obove 33 miles – therefore I can’t trust any of the results"

As a way to remove the overages, why not do a final comparison of the distances calculated – e.g. see your echo equation

` acos(sin(deg2rad(\$lat1)) * sin(deg2rad(\$row['latitude'])) + cos(deg2rad(\$lat1)) * cos(deg2rad(\$row['latitude'])) * cos(deg2rad(\$row['longitude']) - deg2rad(\$lon1))) * \$r`

– against the selected distance \$d and remove anything >= \$d
then you’ll be right on!

Is there any easy way to incorporate this as a final (and therefore fast) filter? I’m eager to add this feature to my website – thanks.

Again, great piece of programming.

2. Fred, you describe what I was talking about at the start of my aside: Using a square to get “rough guess” records, then using trigonometry to filter out those records which don’t actually fit the supplied radius.

We can accomplish that by modifying the while block that fetches the records, at line 55. All we do is add an if statement that wraps the output, checking if the distance calculation is less than or equal to the distance supplied by the user. (We also move around the table cell tags, to accommodate the new conditional statement.)

```while(\$row = mysql_fetch_array(\$rs)) {
\$truedistance = acos(sin(deg2rad(\$lat1)) * sin(deg2rad(\$row['latitude'])) + cos(deg2rad(\$lat1)) * cos(deg2rad(\$row['latitude'])) * cos(deg2rad(\$row['longitude']) - deg2rad(\$lon1))) * \$r;
if(\$truedistance < \$d) {
echo "<tr><td>\$row[city]</td><td>\$row[state]</td><td>\$row[zip_code]</td><td>\$row[latitude]</td><td>\$row[longitude]</td>";
echo "<td>\$truedistance</td></tr>\n";
}
}
```

Again, so long as you are working with short distances, this shouldn’t be necessary. The coordinates for the ZIP Codes themselves are arbitrary; there’s a better-than-even chance that, even with a distance of 50 to 100 miles, some part of the town that seems outside the selected radius actually does lie within the radius.

For example, if a town is laid out as a 10-mile square, its ZIP Code coordinate in the database will actually be about 5 miles away from each border. Therefore, if our radius reaches 4.5 miles into the town’s territory, it won’t show up in our results set; the center of town is too far away, but a significant portion of the town is within range.

However, if you want that precision, again, use the square as a starting point, then test each record in the result set for true distance.

3. Fred, XSS is a nonissue with this script.

You always want to sanitize your inputs on the server side, which I have done with the regular expressions at the start of the script. People can append any nonsense they want to this script. It isn’t going to work if it doesn’t get a five-digit POST variable named zipcode and a one- to three-digit POST variable named distance.

If you want to use htmlentities(), go ahead. For the purpose of this demo, I’ll add it to the script as well, but again, this code is already impervious to XSS due to the regular expressions checking the inputs for range and format.

4. Naved says:

Hey Doug..nice work man..,,
i have a problem here,as i am making the script for finding the lat and long at pert distance,and more important thing is that i dont have any database which is already have these long and lat value instead i just have the city name,state name,country name with zipcode in the database,so as i am entering the value of distance from a pert point it is giving me four values north south east and west respectively but i want to make it only two that is lat and long..

for ex:=>(fake values)suppose my zip is 61353(lat=>41.0023, long=>-88.2561) and i want to find the lat and long of location which is 100 miles away from here…then how??b,coz i am getting 4 values instead of 2?

can you tell me the formula to find these values by using north south east and west values ….Thanks in advance

Thanks and Regards
Naved Mohammad

5. Naved, I don’t quite understand what you are driving at. If you have no starting coordinates, nor the coordinates of any other points, you cannot compute the distance between points. In other words, you have to have the latitude and longitude of the points involved.

Download the geocoded ZIP Codes as described above into a new table, simply left-join the coordinates in a query, and use the techniques noted here.

6. Received from Naved via e-mail:

Hi Doug..
I think you didn’t got the point what i was trying to make ..The point is that i have one location whose zip and lat-long i already have..ok
Now from this perticular position i want to find the another location which is exactly 100 miles away but i dont have the lat and long of that location…ok
as i use your script i put lat1 long1 distance radius…fine
what i am getting is north south east west instead of this i want the lat and long..ok
Now you tell me how to find that lat and long..

Thanks & Regards
Naved Mohammad

My response:

Naved: Once again, I am having difficulty understanding exactly what you mean.

As the article explains, in order to get all the points that lie within a given radius, a convenient way to do that is to find the north, south, east and west coordinates from a given point, then use those coordinates to create the boundaries of a square, then query the database for all points less than north, greater than south, less than east and greater than west.

You can’t ask, “I know the latitude and longitude of ZIP Code 12345. I want to know the latitude and longitude of a ZIP Code that is 100 miles away.” It begs the question.

If you don’t have the latitude and longitude of all other ZIP Codes, then you can’t determine which ones are 100 miles away. It would be like putting on a blindfold and then trying to say how far away things are. You don’t know because you can’t seen them, you don’t know which direction you are pointing, etc. It is the same thing here. Unless you know where the other ZIP Codes are located, and the bearing from your starting point at which that ZIP Code is located, you cannot tell how far away other ZIP Codes are.

I am sorry the language barrier between us is causing difficulty. I would be willing to assist you further but I will need to charge you money to do so. Let me know if you would like to pay for my help.

7. Kim says:

Tnx so much for posting this on the internet, it helped me a lot 😉

8. mike says:

great post, very concise and to the point unlike many others….
the square method is quite accurate enough considering the fact that when you are pulling zip codes that are in a radius of your point, the lat/long of that zip code is just the center point of that zip, and is not the actual boundary. So if you really wanted ultimate accuracy you would have to do some intersect checks to see if your circle radius touches the boundaries of the zip code….. or if the zip code entirely falls within the radius depending on needs for your app, etc etc ( insert lots of trig here )

9. Awesome, awesome post.

Doug you’ve done a great job. It is AMAZING how difficult it was to find something like this… searched Google for days. After searching various terms and such I finally found this on page 8 of “php zip radius search”. Anywho…

This is working out great, however I am getting an error. You can test it for yourself here:
http://www.tsgcomputers.net/webapps/zipradius/default.php

I’ve worked on the code a bit… had to to match my DB… and I keep getting this: {code snipped}

`There was an error selecting nearby ZIP Codes from the database.`

It does seem to gather the information from the DB since it gathers the ZIP code information that I enter… but nothing more.

If you don’t mind can you look this over and offer assistance?

Thank you so much for your code and hard work… it is of great help just to get this far without having to spend \$100 or more.

Thank you,
Johnathan

10. Johnathan: Your code is throwing an error because there’s something wrong with the query. The easiest way to find out what might be the problem would be to find line 47:

```echo "<p><strong>There was an error selecting nearby ZIP Codes from the database.</strong></p>\n";
```

And replace that with this:

```echo "<p>" . mysql_error() . " | \$query</p>\n";
```

This will give you the exact error message MySQL is returning, along with the query that caused the error. You should be able to determine from the nature of the error message and the query you are submitting what, specifically, is causing the error. Most likely, you are sending malformed, mistyped or null data in your query.

Unfortunately, the code you provided was mangled beyond recognition by WordPress. If you post a text file showing your code and a link to your code, that would ensure it can more easily be read.

If you need further assistance, I’d be happy to help you in exchange for a purchase from my Amazon wish list, which is linked under Copyright, Attributions & Donations.

11. Doug, thanks for the response.

Actually I found MY mistake…

```//find all coordinates within the search square's area
//exclude the starting point and any empty city values
\$query = "SELECT * FROM zip_codes WHERE (latitude = \$latS AND longitude = \$lonW) AND (latitude != \$lat1 AND longitude != \$lon1) AND city != '' ORDER BY state, city, latitude, longitude, zipcode";
```

I didn’t define the correct table to get the ZIP code information pertaining to the multiple ZIP Codes search.

Once I defined that correctly it worked perfectly… now to make each ZIP into a link… that’ll be no problem.

Thank you so much and God bless you and your family and work,
Johnathan

12. Does any one know how to work out the bearing from one set of coordinates to another.

For example, if the calculated bearing was 90 (degrees) the using if statements to see if a coordinate was between two parameters I could return North, North East, East and so on?

Thanks in advance.

Matt

13. Fred says:

hello again Doug,

Well, I’m finally getting back to incorporating your very handy codework. But (being a newbie) I’m banging my head against the wall over what should be a very simple addition.

I’ve got another table of data (in the same MySQL database) that I want to cross reference in the php coding you’ve made.

The other table “agent_info”, has names and addresses including “zip” of real estate agents. Essentially, what I’m trying to accomplish is that once someone enters the zipcode and wants other zipcodes within whatever distance, I then want to tell them how many agents (from “agent_info”) are in each zip_code in the range. So, it’s another column of info with “number of agents” as a heading and the value (e.g. SELECT COUNT(*)…) taken from “agent_info” for the total number of agents in each zip_code that is returned. And of course a total sum of all agents as well.

No matter how I try, all I can get is “Resource id #4” returned for each field.

Do you have suggestions, or a quick solution?

I appreciate your help.

1. Fred: What you describe requires one of two equally unappealing options.

Option 1 is to requery your database for every ZIP Code retrieved, running your COUNT query for each ZIP Code.

The benefit of that approach is, you always get an accurate count of agents. It’s also easiest to write.

The drawback is, you’ll tax the living Hell out of your MySQL server; you’ll definitely see a performance hit if you are dealing with 100+ ZIP Code records, and you’ll probably see a performance hit if your Web application is popular, even for small sets of ZIP Codes.

Option 2 is to create a new table — call it agent_count_by_zip_code — that contains a primary key of zip_code, and a field named agent_count that stores the count of agents for each ZIP Code. You can then use a simple LEFT JOIN to add the counts from that table to the query for ZIP Codes at Line 46.

The benefit to that approach is, it’s far more efficient and won’t cause any performance problems in your application. The drawback is, you have to manage the agent_count_by_zip_code table, which probably means writing new queries (or stored procedures and triggers) to keep the counts accurate.

Were I writing what you are doing, I would use Option 2; create a stored procedure that inserts or updates the counts; and create triggers that fire on any update, insert or delete on the agents table, correcting the counts in the agent_count_by_zip_code table.

I recognize that stored procedures and triggers are probably outside your repertoire, but you can fake it with standard, follow-up queries attached to any other database queries you use to populate or edit data in your agents table.

So my best advice is, use Option 2, because it’s most elegant and efficient; use Option 1 if you need to get this done right now, and work on integrating Option 2 as quickly as you can.

I would be glad to assist you further if you need it; I would request something from my Amazon wish list in compensation, If you’re interested, drop me a line. Best of luck with your project.

14. Fred says:

thanks again Doug,

i followed your advice and was able to put the agent_count in the same table as the zip codes with lon/lat data. It’s speedy and clean.

You’re right, the stored procedures and triggers are beyond my current ability/need – so I’ll just update manually as warranted.

Thanks again,

Fred

PS – so what does your Amazon wish list look like?

15. Fred: My wish list is listed under Copyright, Attribution and Donations. Nothing necessary for what I’ve done so far, but anything is appreciated.

16. Doug, awesome job on the code… it works great.

How can you sort the search results by distance?

Thank you,
Johnathan

1. Johnathan: There are two ways of accomplishing what you want. One would be to calculate the distance in the query itself, then use that as a sorting variable. This approach is propably the most elegant. However, you may find a small MySQL performance hit.

Another would be to query, put the records returned into a two-dimensional array, calculating the distance as you do so and adding it as a cell of the array. Then, you sort your array on the distance and output records that way. The benefit of this approach is that, if you don’t know much about MySQL or are seeing performance problems in MySQL, it’s a bit easier for most Web servers and new programmers to handle.

Best of luck with your project. If you’d like any further assistance, I’d ask that you purchase something from my Amazon wish list, which is linked under Copyright, Attribution and Donations.

17. Doug,

I was actually thinking on purchasing something since you have been very helpful.

I will try to work it out, but I may need more assistance in doing that.

Thank you and God bless,
Johnathan

1. Johnathan: No obligation, but I do appreciate anything you might send. As a rule, I’ll do a follow-up or two for free.

18. neel says:

Hi,
I tried the demo at mentioned link:
http://dougv.com/demo/php_zip_code_distance/

Entered zip code 94087 (sunnyvale,ca) ( 37°22?7.56?N 122°2?13.4?W? / ?37.3687667°N 122.037056°W? / 37.3687667; -122.037056.)

But it is returning incorrect result. Does Db incorrectly contain sunnyvale long as -99.99 or it is script issue.
Thanks,
Neel

=============
Sunnyvale California 37.350091 -99.999999 37.711898145195 36.988283854805 -99.542662827632 -100.45733517237

City State ZIP Code Latitude Longitude Miles, Point A To B
Ashland Kansas 67831 37.196075 -99.776620 16.251589044657
Bloom Kansas 67865 37.394383 -99.967230 3.550269445576
Bucklin Kansas 67834 37.523175 -99.634350 23.355813537495
Englewood Kansas 67840 37.040749 -99.989030 21.383310186967
Ford Kansas 67842 37.549041 -99.748690 19.468577369373
==============================

1. @neel: When I imported the ZIP code database, I mistakenly set the size and precision of the longitude column in my MySQL database to (8,6). Therefore, any values -100 and below were automatically converted to -99.999999. (Hey, I’m from New England. Sometimes I forget that things exist west of the Ohio River.)

I have corrected the size of the latitude and longitude columns, re-imported the data and the demo should now be returning proper values. Apologies and thanks for catching my error.

19. rick says:

Great article (and the one on two-level state/city lookup).

Might I suggest that, since you are not providing the zip code database in your download, that you describe the structure of the zipcode table.

Thanks…Rick…

1. @rick: It’s self-explanatory from the code: columns for ZIP Code, latitude, longitude, city and state. Oh, and a primary key, or course.

20. rick says:

Field names I figured out. Field types (numeric, float, text, length, etc) are not so obvious.

For instance, the ‘radius search’ program requires numeric fields of some sort for lat/long, but the two-level state/city article doesn’t need numeric lat/long. And I’m having difficulty with the import of the zip.csv file into the lat/long fields.

So the field types/lengths is important information, IMHO.

Thanks…Rick…

21. I found your script and I have integrated your plus the ability to find “stores” based on the list of initially found zip codes. Work well, your script was really nice. SO instead of displaying a list of zip codes it now display a list of “places” found within my database with those zip.

While testing yesterday I found an interesting issue with your base code. When searching for a radius of zips, it does not return the zip of the initially search zip and perhaps more. So I went back to your demo and it exhibits the same issue. Since I am in Connecticut, I tried to use a zip code 06896 and it find the zip, then display all the zips except for those in Redding, (the zip code I used) Actually there are 4 zipcodes tied to Redding so I’m guessing that the searched zip code and any other the same town are not being returned. It is not just this zip code that manifests the issue. Try 10010 a NYC zip.

I’ll need to figure out what your zip list routine is doing and adjust it.
thanks

1. @Rick: Change the SQL statement to not exclude the initial ZIP Code.

This:

`\$query = "SELECT * FROM php_zip_code_distance WHERE (latitude <= \$latN AND latitude >= \$latS AND longitude <= \$lonE AND longitude >= \$lonW) AND (latitude != \$lat1 AND longitude != \$lon1) AND city != '' ORDER BY state, city, latitude, longitude";`

Becomes this:

`\$query = "SELECT * FROM php_zip_code_distance WHERE (latitude <= \$latN AND latitude >= \$latS AND longitude <= \$lonE AND longitude >= \$lonW) AND city != '' ORDER BY state, city, latitude, longitude";`
22. Emman says:

Hello, Love your code dude, but i can’t convert the zipcode.csv to sql can you provide me an sql file the zip codes. I would be much appreciated. Thank you

1. @Emman: For the reasons stated in the post (I don’t want leeches), and because I have no interest in maintaining such a resource, no, I will not provide a SQL file of the ZIP Codes. The article describes one way to convert the database project file. The Web is full of examples and tools. I may, at a future date, create a generic CSV to SQL script. Or you could pay me to convert it for you. If you’re interested in the latter option, please email me directly.

23. Evan Grob says:

Thanks for the information on how to create a search distance via zip code. This site was extremely helpful.

24. balu says:

Thank “U” very much…..

Its working fine. Get one keep it good….

25. Shafi says:

Hi Doug,
I am trying to convert your PHP code into java and I am getting longitude (E&W) and latitude(N&S) same as my current location Longitude and latitude, following is my code, please let me know what mistake I am doing, thanks in advance and this is very good article:
—————————————————————–

```import java.lang.Math;

public class FetchRadius {

public static void main(String[] args) {
//Sample City : Herndon and Zipcode: 22092 Lat - "38.96" Long - "-77.38"
double Lat = 38.96;
double Long = -77.38;
int d = 10;
int er = 3959;

double Lat_radian = deg2rad(Lat);
double Long_radian = deg2rad(Long);

System.out.println("Lat_radian: "+Lat_radian);
System.out.println("Long_radian: "+Long_radian);

double latN = rad2deg(Math.asin(Math.sin(Lat_radian) * Math.cos(d / er) + Math.cos(Lat_radian) * Math.sin(d / er) * Math.cos(deg2rad(0))));
double latN_radian = deg2rad(latN);
double latS = rad2deg(Math.asin(Math.sin(Lat_radian) * Math.cos(d / er) + Math.cos(Lat_radian) * Math.sin(d / er) * Math.cos(deg2rad(180))));
double latS_radian = deg2rad(latS);

System.out.println("latN : "+latN);
System.out.println("latS : "+latS);

double lonE = rad2deg(Long_radian + Math.atan2(Math.sin(deg2rad(90)) * Math.sin(d / er) * Math.cos(Lat_radian), Math.cos(d / er) - Math.sin(Lat_radian) * Math.sin(latN_radian)));
System.out.println("lonE : "+lonE);
double lonW = rad2deg(Long_radian + Math.atan2(Math.sin(deg2rad(270)) * Math.sin(d / er) * Math.cos(Lat_radian), Math.cos(d / er) - Math.sin(Lat_radian) * Math.sin(latN_radian)));
System.out.println("lonW : "+lonW);
}

/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*::  This function converts decimal degrees to radians             :*/
/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
private static double deg2rad(double deg) {
return (deg * Math.PI / 180.0);
}

/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
/*::  This function converts radians to decimal degrees             :*/
/*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*/
private static double rad2deg(double rad) {
return (rad * 180.0 / Math.PI);
}

}
```

—————————————————————–

26. Shafi says:

Hi Doug,
I figured out the issue with my code, the issue is I have to define my distance and radius variables as double instead of int.

Thank you for your article and it is very helpful article.

27. Fredrick says:

Hi Doug,

Thanks a lot for this, it is a great resource. Is there any chance you could update it do MySQLi?

1. @Fredrick: I may at some point, but as this is a single query iterating through a results set, it shouldn’t be too much of a challenge to simply follow along with the example at mysqli_stmt::fetch to make the upgrade.

28. Hello Doug,

I managed to import a dutch zipcode database into your coding. The script works perfect and thanks for this!
You can have a view at: http://www.kilometerafstanden.nl/postcode-in-cirkel.php ( use for example zipcode 6523 )

However it would be nice to round up all the KM distances in colomn 6. .
Can you help me out in this???

Again thanks a lot

Fred Wielakker
Nijmegen, Netherlands

1. @Fred: Simply wrap Line 59 with the PHP functions round(), ceil() or floor(). For example:

```echo ceil(acos(sin(deg2rad(\$lat1)) * sin(deg2rad(\$row['latitude'])) + cos(deg2rad(\$lat1)) * cos(deg2rad(\$row['latitude'])) * cos(deg2rad(\$row['longitude']) - deg2rad(\$lon1))) * \$r);
```

The code above will round up all fractions to the next highest integer.

29. Thanks Doug for your answer.

One last question however. Can”t find the answer myself.
Is it possible to give the CEIL and other functions an extra 3 decimals? Example: a number notation like 3.456 looks better than only the number 3 with the CEIL function

Thanks for helping out.

Fred Wielakker
Nijmegen, Netherlands

30. Efraim says:

Great post! Just found it via Google to figure this zip code problem.

Do you have a math background?

I’m beginning to learn some programming languages,and I’m realising that the fact that I sucked on math during school will soon start to hinder my development.

Any tips or resources or books you suggest to solve this problem?

Thanks

1. @Efraim: I’m pretty much in the same boat as you. In my travels, I have seldom had to do mathematical heavy lifting; being able to solve single-variable equations generally will carry you for most programming problems. On those occasions when I have had to do hard math, I found someone else’s answer (as it was here) or got someone else to do the math.

That said, depending on where you live, I’d consider doing a college math prep course or two. Generally speaking, most colleges will allow you to audit an introductory course (that is, take it without getting a grade, or credit). You pay full price, but if what you want is a resource you can question, that’s a good start. You public school district’s adult education office might also be an option.

By the same token, I bet you can find a boffin on craigslist who would be willing to help you. (Try the labor and computer categories under “gigs”; you can also post your own ad there.)

MIT also offers open courseware in a number of math disciplines. I have found that when I need help with math, I tend to understand it and retain it longer if someone shows me how he came to an answer, rather than reading the answer and its explanation. That’s me, though, so an online course may work for you.

31. Chirag says:

hi,

Please help me to get difference between these two.

```(lat = '\$latS' AND lng &gt;= '\$lonE' AND lng //Most nearer locations are not showing, far are showing
(lat = '\$latS' AND lng = '\$lonW'    =&gt; //Most nearer locations are showing but far location are not showing```

I want to have all location nearby me (lat,long) withing some specific radius.

Please let me know your thoughts.

1. @Chirag: What you describe makes no sense. If you use the solution as coded, by default all ZIP Codes with coordinates that exist within the area of a square of 2*r the distance provided will be found, regardless of their location within the area of the square.

In other words, using this solution as coded, if you request all ZIP Codes within 50 miles of a known ZIP Code’s coordinates, the solution will draw a square with an area of 10,000 square miles, centered on the known ZIP Code. Then it will find all the ZIP Codes that fall within that 10,000 square mile box. It will do so regardless of the proximity of the found ZIP Codes from the known ZIP Code.

This is clearly shown in the demo.

Therefore, whatever adjustments you have made to the code are at issue.

32. Chirag says:

Thank you Doug !

I found the root cause.

if my longitude is negative, following will works,

`( (lat = '\$latS') AND (lng >= '\$lonE') AND (lng <= '\$lonW'))`

else,

`( (lat = '\$latS') AND (lng = '\$lonW'))`

Does it also depends on sign of latitude ?

Please let me know.

1. @Chirag: If the sign of the longitudes are affecting your outcome, then so will the latitudes. However, as far as ZIP Codes go, all latitudes are North latitudes, and thus will always be positive.

It is not clear to me at all what you are trying to accomplish with your tinkering, but it is clear that you are new to PHP / databases and have not considered the importance of using the correct data types in your queries.

It appears you brought your latitude and longitude coordinates over into your database as strings, since you are single-quoting the latitude and longitude variables in your queries. Undoubtedly much of your trouble is caused by the fact that you are not actually working with decimals, but strings, on the MySQL server, given the way your queries are structured.

While MySQL / PHP will implicitly cast those values as floats in order to compare them mathematically, that’s sloppy and expensive. Assuming you are using the ZIP Code database data, you should reimport your data, ensuring the latitude and longitude columns are decimals with a precision of (9,6). This will reduce the overhead of your queries and ensure the results you expect.

If your data is, in fact, numeric in MySQL, then there is no need to quote the PHP variables in your queries. In fact, doing so is doubly wasteful, because MySQL sees those variables as strings, and converts them into decimals, when if they were sent over as decimals in the first place, by not quoting them, you would get the results you expect.

33. Chirag says:

Thank you !
Correct observation.
So aftr making it as decimal, old queries will be enough? Negative longitude wont affect?

34. Hi Doug,

I’m still using your script Getting All ZIP Codes In A Given Radius, it works fine. Thanks for that again!
However I would like to improve the outcome in KM by using your filter for a more accurate distance calculation.
But when I insert the next code in the script I’m using I get a 500 from my server. I tried manytimes but it simply doesn’t work.

{code removed}

Can you help me out by implementing your \$truedistance solution code into my existing script without disturbing it functionality.

I would appriciate your help. Again!

Fred Wielakker
Nijmegen
The Netherlands

1. @Fred: I’d be happy to assist you, for pay. Please email me directly for details.

35. Just wanted to say thank you, Doug, for sharing and explain this so well.. It took me nearly an entire day to understand and implement this to a search engine, but I finally did it. Have a nice day!

36. Chase says:

Thank you for this great post Doug, you have been a tremendous help!

What would be the best method to go about sorting the results by \$truedistance?

Thanks again,

Chase

1. @Chase: If you want to sort by \$truedistance specifically, first create an object that has as its properties the values you are populating from your database, e.g.:

```class MyLocation {
public \$city;
public \$state;
public \$zip_code
public \$latitude;
public \$longitude;
public \$truedistance;

//\$lat1, \$lon1: Starting ZIP lat / lon
//\$lat2, \$lon2: This ZIP's lat / lon
//\$r: Radius of Earth
function __construct(\$city, \$state, \$zip, \$lat1, \$lon1, \$lat2, \$lon2, \$r) {
\$this->city = \$city;
\$this->state = \$state;
\$this->zip_code = \$zip;
\$this->latitude = \$lat2;
\$this->longitude = \$lon2;
\$this->truedistance = acos(sin(deg2rad(\$lat1)) * sin(deg2rad(\$lat2)) + cos(deg2rad(\$lat1)) * cos(deg2rad(\$lat2)) * cos(deg2rad(\$lon2) - deg2rad(\$lon1))) * \$r;
}
}
```

Next, add a usort function so you can sort that object array by the truedistance property:

```function distanceSort(\$a, \$b) {
if(\$a->truedistance == \$b->truedistance) {
return 0;
}
if(\$a->truedistance > \$b->truedistance) {
return 1;
}
return -1;
}
```

Next, rather than directly looping your MySQL query result to the table output, loop it to an array of these objects:

```\$resultsArray = array();
while(\$row = mysql_fetch_array(\$rs)) {
\$resultsArray[] = new MyLocation(\$row['city'], \$row['state'], \$row['zip_code'], \$lat1, \$lon1, \$row['latitude'], \$row['longitude'], \$r);
}
```

Next, sort your object array:

```\$results = usort(\$resultsArray, "distanceSort");
```

You now have a sorted array of objects that you can output to screen:

```foreach(\$results as \$location) {
echo "<tr><td>\$location->city</td><td>\$location->state</td><td>\$location->zip_code</td><td>\$location->latitude</td><td>\$location->longitude</td><td>\$location->truedistance</td></tr>\n";
}
```
37. Chase says:

After about 6 hours last night I ended up using mysqli to create a temporary table and then used a while loop to pull the data from the orginal query into the temp table and then queried it directly from temp table using ORDER BY sort the data by distance.

Zip code radius is the main data filter on my project so I will be using it to filter almost all of my queries.

This evening I am going to try to implement your suggestion as it appears more secure and probably more stable for multiple users.

Thanks again for all of your help!

1. @Chase: Your solution works fine, too. If I were to go this route, I would probably compute the true distance as a dynamic column in the query response / results set, rather than via a temporary table.

Temporary tables are volatile (don’t live outside the current session) and are memory intensive, so they’re inefficient; especially in shared hosting environments, where one MySQL server is handling multiple databases and client connections. Since MySQL tends to be the most valuable, and therefore the most limited, computational resource in a solution, I find it’s best to offload work from the database server to the HTTP or application server wherever possible.

So yes, I think you will find my suggestion, while not improving security per se, does make a difference in performance.

38. Robin says:

That is a really comprehensive answer. However, I have a couple of questions.

What if I use PostgreSQL instead of MySQL? Will it change anything or I can import the database using the same steps you describe?

I am interested because I am making a similar app for my team and have to know all the details.

You say that you could have been more elegant and consolidated much of the casting of degrees to radians – it would be nice if you could show that version as well.

1. @Robin:

What if I use PostgreSQL instead of MySQL? Will it change anything or I can import the database using the same steps you describe?

The backend data store makes no difference if you use the solution as designed, and do any record filtering in your business logic rather than your query.

It would make some difference if you were filtering records via the query itself, since the DB server would need to do some casting and more computationally expensive record inspections; but again, that’s only if you modify the solution to filter record selection via the query, rather than the business logic.

Also, PostGRE is pretty capable, so I would expect to see similar performance from it as one would get from MySQL.

… it would be nice if you could show that (elegant) version as well.

http://dougv.com/2009/06/how-i-code-on-this-blog-elegance-vs-transparency/

39. Midhun says:

Thanks for the solution Doug 🙂 It really saved me.

40. Gille says:

Thank you for this article for those who want to remove the double quote on Excel via a macro this is the formula

```Sub Macro1()
'
' Macro1 Macro
'
Cells.Replace What:=Chr(34), Replacement:="", LookAt:=xlPart, MatchCase:=False
'
End Sub
```

This site uses Akismet to reduce spam. Learn how your comment data is processed.

• Check out the Commenting Guidelines before commenting, please!
• Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
• Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!