Zipcode Radius Search
Want to find all zipcodes and corresponding distances within a certain mile/kilometer radius from another zipcode or point? This problems require latitude and longitude coordinates to solve. Geocoding the address gives you latitude/longitude coordinates from an address. Both Yahoo and Google offer free geocoding services. I wrote a post about geocoding with a code example for both Google and Yahoo geocode services.
First you will need a database of all zipcodes and their corresponding latitude and longitude coordinates. If you are reading this post, you probably don't have this so let me help you out. Download Zipcode Database for a zip file of both an SQL data dump and CSV format. Here is the basic table structure I use in my example code:
`zipcode` varchar(5) NOT NULL DEFAULT '',
`city` varchar(100) NOT NULL DEFAULT '',
`state` char(2) NOT NULL DEFAULT '',
`latitude` varchar(20) NOT NULL DEFAULT '',
`longitude` varchar(20) NOT NULL DEFAULT '',
KEY `zipcode` (`zipcode`),
KEY `state` (`state`)
)
So once you have the database you want to find all zipcodes within a certain mile radius of a central point. If the central point is another zipcode, simply query the database for the latitude and longitude coordinates of that zipcode. Then the code is as follows:
$coords = array('latitude' => "32.8", 'longitude' => "-117.17");
//RADIUS
$radius = 30;
// SQL FOR KILOMETERS
$sql = "SELECT zipcode, ( 6371 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";
// SQL FOR MILES
$sql = "SELECT zipcode, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";
// OUTPUT THE ZIPCODES AND DISTANCES
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query)){
echo "{$row['zipcode']} ({$row['distance']})<br>\n";
}
I get the following error with this:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in
I tried to modify your code for a store loactor and I am getting an SQL syntax error in the following
SELECT *, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) – radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM clinics HAVING distance <= {$radius} ORDER BY distance,Clinic_Preferred
With the code change as above I get an unidentified field error, and when I try to change any of the latitude, longitude or ‘latitude’,'longitude’ with my table fields, I get the syntax error stating it’s around this area.
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘[ 'Clinic_Geocode_Latitude' ] } ) ) * cos( radians( latitude ) ) * cos( radians(’ at line 1
I cannot for the life of me figure out what’s wrong… might you be able to assist?
If not, thank you anyways your code has gotten me about 98% where I wanted to be.
Cheerz!
Matt
Figures once I post a problem I figure it out (kind of)
SELECT *, ( 3959 * acos( cos( radians( “37.5579760″ ) ) * cos( radians( ‘Clinic_Geocode_Latitude’ ) ) * cos( radians( ‘Clinic_Geocode_Longitude’ ) – radians( “-122.0411430″ ) ) + sin( radians( “37.5579760″ ) ) * sin( radians( ‘Clinic_Geocode_Latitude’ ) ) ) ) AS distance FROM clinics HAVING distance <= “250″ ORDER BY distance,Clinic_Preferred ASC
This code query’s with no errors but returns no data…
Basically what I am trying to do is enter a zipcode and a Mileage radius ( 5-50 miles) then have a MySQL query look at all my clinic locations Geocodes and do a distance search based on teh geocode of the zipcode entered on my form… What am I missing?
Thank you once again
Cheerz!
Thank you so much for this. The SQL formula you wrote works so much better than all those bloated queries I have spent hours goggling.
BTW, you can speed it up by using a BETWEEN clause also…
hi,
i have user table and zip code table. i need to search a zip codes with in given miles. for example i enter 75070 zip and 50 miles range users i need to get from the user table, please provide the solution.
thank you.
This is a very cool code, and I got it to work on my page.
But… how do you use the result array in a new search that have records that match the resulting zip codes? I gather you have to convert the result from an associative array to a string? Then maybe us an “IN” Select query? Or maby use a SELECT in a while loop?
I don’t understand your question. The result is the zipcode records. What would you want to gain from a new search? Email me so I can help you.
Hi,
thanks for superb information.Can you explain the calculation you have done in the query to calculate distance?