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 https://www.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.

Latitude and longitude of Earth

By Djexplo (Own work) CC0via Wikimedia Commons

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:

square vs circle overlap

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://www.dougv.com/demo/php_zip_code_distance/

Downloadable code: Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via PHP And MySQL demo code

I distribute code under the GNU GPL. See Copyright & Attribution for details.

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.

All links in this post on delicious: http://delicious.com/dhvrm/getting-all-zip-codes-in-a-given-radius-from-a-known-point-zip-code-via-php-and-mysql

**: 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.

37 thoughts on “Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via PHP And MySQL

  1. Fred

    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. Doug Vanderweide

    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. Doug Vanderweide

    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. Pingback: dougv.com | The Web home of Doug Vanderweide » Blog Archive » Using AJAX To Data Bind A Child Drop Down List Based On The Selected Option Of A Parent Select Control

  5. Pingback: dougv.com | The Web home of Doug Vanderweide » Blog Archive » Using Cookies To Require Users To Visit An Introduction Page On Your Web Site

  6. Naved

    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

  7. Doug Vanderweide

    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.

  8. Doug Vanderweide

    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.

  9. Pingback: Parent – Child Select Lists Revisited: Multiple Parent – Child Select Lists Via PHP, MySQL And jQuery » dougv.com « The Web home of Doug Vanderweide

  10. Pingback: How I Code On This Blog: Elegance Vs. Transparency » dougv.com « The Web home of Doug Vanderweide

  11. mike

    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 )

  12. Johnathan Morlock

    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

  13. Doug Vanderweide

    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.

  14. Johnathan Morlock

    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

  15. Matt

    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

  16. Pingback: Calculating The Bearing And Compass Rose Direction Between Two Latitude / Longitude Coordinates In PHP » dougv.com « The Web home of Doug Vanderweide

  17. Fred

    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.

  18. Doug Vanderweide

    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.

  19. Fred

    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?

  20. Doug Vanderweide

    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.

  21. Johnathan

    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

  22. neel

    Hi,
    I tried the demo at mentioned link:
    http://www.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
    ==============================

  23. Doug Vanderweide

    @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.

  24. rick

    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…

  25. rick

    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…

  26. Rick

    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

  27. Doug Vanderweide

    @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";
  28. Emman

    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

  29. Doug Vanderweide Post author

    @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.

  30. Evan Grob

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

Leave a Reply