Zip code radius search question

[eluser]Nicholas Bello[/eluser]
Hello fellow CI'ers, I'm developing an application with a hopeful turn out of at least 4 million users (hey why not aim high). A major part of this application will be locating items by zip code radius. Now i know the calculations to perform between two zip codes to get a distance, and i know where to get a zipcode database with latitude and longitude, but i'm not sure how to do a fast and efficient zipcode radius search that searches every zip code in the continental United States that will be repeated thousands of times a day, without killing the app or the mysql database.

Basically i need to program a tool that returns the nearest zipcodes from an inputed zip code. I'm not sure if I should do this via a mysql stored procedure http://www.goondocks.com/blog/08-01-22/z...mysql.aspx or do it via the php side. My only concern is slowing down the application. I know there is a way to do this efficently as many sites already do it. Any help would be greatly appreciated.

Thank you,
Nick

[eluser]Dam1an[/eluser]
I'm not sure how zip codes work, but I know post codes work in such a way you know where to start looking for something close

The first 2 characters tell you the town/city
The following number is a region within the town/city
The the next number gets more specific, followed by 2 more letters which get even more specific

So you know that LU2 7AE and LU2 7AF are right next to each other, and LU3 3HE would be reasonably close, but further then the previous 2 where from each other

This means you know where to start searching (so given the first one you'd scan all the LU2's, then LU's etc

There's probably some similar logic with zip codes

[eluser]Jondolar[/eluser]
I did a zip code radius search for an application and the app does call the function for every search (for now). The visitor can select a distance from the zip code to perform the search. The site is not that busy yet so doing the calc for each search is not a problem.

One approach might be to gather all of the zip codes from a certain distance of each zip code in the database and create a related table. Basically, write a script that goes through each zip code and creates entries in a related table for each other zip code that falls within 25 miles. Then, when someone searches, just look up all the zip codes in the related table instead of calculating the radius and looking up all the zip codes that fall within a certain longitude/latitude.

The problem with this approach is that you must hard-code the distance. If you had a finite number of distances, like from a drop-down list, you could build the related table for each of those distances.

Good luck with your project.

[eluser]pixel888[/eluser]
Here is MySQL query I used for my ZIP Code Locator

Code:

SELECT s.*
, z.lattitude
, z.longitude
, (3956 * 2 * ATAN2(SQRT(POW(SIN((RADIANS(lattitude) - RADIANS(40.6813))/2),2) +
COS(RADIANS(40.6813)) * COS(RADIANS(lattitude)) * POW(SIN((RADIANS(longitude) - RADIANS(-73.981))/2),2)),SQRT(1-(POW(SIN((RADIANS(lattitude) - RADIANS(40.6813))/2),2) + COS(RADIANS(40.6813)) * COS(RADIANS(lattitude)) * POW(SIN((RADIANS(longitude) - RADIANS(-73.981))/2),2))))) as distance

FROM `locator_stores` s, `locator_zip_code` z
WHERE s.zip = z.zip_code HAVING distance <= 10

where
40.6813 is Lat
-73.981 is Lon
10 is miles

It works fast

[eluser]Mutsop[/eluser]
Why not use the API of google maps?
With this you don't need to store anything through your database.

Here is an example of retrieving the longitude and latitude using an adress.

Here is some information from google geocoding

[eluser]pixel888[/eluser]
This way the script not dependent on the any third party services like google