Topic: Finding a location within X miles/km of an postal code

[rondog]

I have a client who wants to have their stores searchable my zip code. I have a database with 20,000+ stores already that include zip code. I dont even know where to begin to start this? I was looking on google and just found ones you can buy and they are a bit expensive. Any ideas on where to start with this? Thanks.

[Attila]

Here you go this should do it for you.   It is a form that will search the database and display the results of the zip code.



<?
     $top_form = "<form method=\"post\" action=\"{$_SERVER['PHP_SELF']}\">
     <table border=\"2\" cellpadding=\"2\" cellspacing=\"2\" bordercolor=\"#333333\" bgcolor=\"#CCCCCC\">
        <tr align=\"center\" valign=\"middle\">
          <td colspan=\"1\">";
   $bottom_form = "</td>
        </tr>
      <tr>
          <td>Enter the your zip code:/td>
          <td><input type=\"text\" value=\"{$_POST['zip']}\" name=\"zip\" /></td>   

        </tr>
        <tr align=\"center\" valign=\"middle\">
          <td colspan=\"2\">
        <input type=\"hidden\" name=\"op\" value=\"ds\">
        <input type=\"submit\" name=\"Submit\" value=\"Search\"></td>
        </tr>
      </table>
     </form>";
     
if ($_POST['op'] != "ds") 
   { 
      echo "$top_form"."$bottom_form";
   } 
   else
      {
   echo "$top_form"."$bottom_form";
   $zipcode = $_POST['zip'];
   $sql = "SELECT * FROM the tabel WHERE colum name LIKE '%$zip%' ORDER BY colum name ASC";  //this is a like search you might have to do an exaxt search
   database_connect()
   $result = mysql_query($sql) or die(mysql_error());
  

while($row = mysql_fetch_array($result)) {

//add formating in here to echo out the data
  }
?>

[rondog]

well the query you provided wont really work since zip codes are all 5 digits (US anyway) and the % wildcard will return nothing unless its an exact match to a zip code in the DB.

If I searched 92026 and nothing in the DB had a zip of 92026, then I would get nothing. If I was to search like '920%' then I would get some pretty decent results back. I am not sure how well that would work with other areas, but that might be a cheap solution...just knock off the users two trailing characters. Even increase/decrease the third numeral and just concatenate those results.

[Zanus]

I can tell where to start for this but I won't do the whole thing for you.

1. Lookup a zipcode database, there easy to find.
actually, here's a great one
http://code.google.com/p/codearachnid/downloads/detail?name=zipcodes.rar&can=2&q=
So you get the zipcodes SQL and you've got an entire table full of every US zipcode.

2. Then google a zipcode distance calculator.
Depending on how good you are a programmer, choose one that suits you.
you can find an OOP based one, with many features, or look for a single function out there.

all in all, you just need the longitude and latitude of two zipcodes and you can do whatever you want if you find or compose the right code.

3.  Well I guess you done.  just implement it.

enjoy

[Third_Degree]

I can tell where to start for this but I won't do the whole thing for you.

1. Lookup a zipcode database, there easy to find.
actually, here's a great one
http://code.google.com/p/codearachnid/downloads/detail?name=zipcodes.rar&can=2&q=
So you get the zipcodes SQL and you've got an entire table full of every US zipcode.

2. Then google a zipcode distance calculator.
Depending on how good you are a programmer, choose one that suits you.
you can find an OOP based one, with many features, or look for a single function out there.

all in all, you just need the longitude and latitude of two zipcodes and you can do whatever you want if you find or compose the right code.

3.  Well I guess you done.  just implement it.

enjoy

I don't think he was really asking for that, he already has the zip codes and mentioned nothing about distance.

Anyway just get all the zip codes out of the db and use php to compare the strings.

[Zanus]

I don't think he was really asking for that, he already has the zip codes and mentioned nothing about distance.

I believe he does, because he says he has 20,000 stores already with valid zipcodes.
his only problem is that that is only approximately 46.5% of all the zipcodes is the US.

How many ZIP Codes are there in the United States?

There are approximately 43,000 ZIP Codes in the United States.  This number can fluctuate by a few thousand ZIP Codes annually, depending on the number of changes made

So if he has a table will all of them, and another table with all the stores, each store with its own foreign key (it's zipcode) relating to the zipcode table, he can perform radius searches to find the closest store to the customer instead of the customer searching for his/her OWN zipcode or various ones by trial and error......which is what the op is trying to do.....which I am trying to persuade him not to do.

[Third_Degree]

I guess I misunderstood the OP then

[rondog]

Yes I actually did need this table of all the zip codes. Thanks zanus.

Its taking me forever to import the data! If I do the whole file at once, my connection to the DB times out after about an hour.

Right now I am doing it state by state and about half way done so far. As soon as thats done, Ill begin my google search for this distance calculator. I have actually seen a few examples already. They were using things such as SIN, COS and TAN. I didnt even know those existed in mysql.

On a side note, I am noticing that some of these zip codes have no state and its set to null. How could a zip code have no state ???

[Zanus]

I don't know....maybe puerto rico has no states and guam.

[rondog]

Ok finally done importing. Alright I am looking at this post on the mysql forums: http://forums.mysql.com/read.php?23,3868,73506#msg-73506

I am pretty sure that will do what I need it to do, but I am so lost. Is their any easier resource that you know of?

This zip radius search function is pretty much what I need:
http://www.web4future.com/products.php?p=zip

This is A LOT more complicated than I thought 

[Zanus]

http://www.phpro.org/tutorials/Geo-Targetting-With-PHP-And-MySQL.html



The next set of posts begins the UK version of this topic

[haku]

One of the specs for a real-estate site I'm doing a quote on, is finding a property within X km of a postal code. The user enters in the postal code, and then the database is searched for properties near that.

I have to admit, I'm somewhat at a loss on where to begin with this - how does this work? The site is UK based, which I'm sure will play into that. Has anyone ever seen a database of UK postal addresses and their locations, or something along that line? Any ideas?

[phparray]

Add a hundred bucks to your quote and buy this.

http://www.milldo.com/en16_uk_post_codes_database.htm


Problem solved.

[haku]

Thanks mate, that's exactly what I need. I was kind of stressing about that part of the system, but it just got a lot easier with that!

[redarrow]

cheek this out then there all free.

http://www.freemaptools.com/download-uk-postcode-lat-lng.htm