8 Replies Latest reply on Jun 29, 2007 5:28 AM by rmorgan

    zipcodes within milage

    Nick201 Level 1
      Hi ,
      For example I would like to find TACO BELL with in 20 mile radius. I can i build the program search on zipcode and radius.
      Thanx
        • 1. zipcodes within milage
          jdeline Level 1
          > For example I would like to find TACO BELL with in 20 mile radius.

          Are you only interested in Taco Bell? What about Burger King?

          There are two setups you need to do:
          - Get the zip codes for every Taco Bell in the country and put them in a database.
          - Then download the latitude and longitude zip code directory from http://www.zipcoderesearch.com/geo.html ($49.99).

          I presume the user would enter an address, including a zip code, and the number of miles for the radius, correct? Use the zip code directory to obtain the latitude and longitude of the user's address.

          Do some trigonometry on the user's lat/long and use the directory to create a list of acceptable zip codes within the desired radius. Then query the database using the list of acceptable zips.

          (There's a lot of assuming goin' on here. :-) )
          • 2. Re: zipcodes within milage
            rmorgan Level 1
            Here is one that works pretty well for me. Just make sure you have all the longs and lats to the corresponding zip.

            SELECT a.zipcode, b.zipcode, 3963.0 * acos(sin(a.latitude/57.2958) * sin(b.latitude/57.2958) + cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * cos(b.longitude/57.2958 - a.longitude/57.2958)) AS distance
            FROM zipcode a, zipcode b
            WHERE a.zipcode = '90210' <==Enter zip here
            AND 3963.0 * acos(sin(a.latitude/57.2958) * sin(b.latitude/57.2958) + cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * cos(b.longitude/57.2958 - a.longitude/57.2958)) <= 20 <==Enter miles here
            GROUP BY distance;
            • 3. Re: zipcodes within milage
              Nick201 Level 1
              Thanx. Do you have any sample code I can test. I have no idea how does that work.
              • 4. Re: zipcodes within milage
                rmorgan Level 1
                Not anything that is publicly accessable.
                • 5. Re: zipcodes within milage
                  Nick201 Level 1
                  could you please let me know what do you have in you db.

                  I copy one zip db. It looks like that.

                  "00501","+40.922326","-072.637078","HOLTSVILLE","NY","SUFFOLK","UNIQUE"

                  I have my db. I have zip code column in it. So I have to match my query to zip file. Is that right.? Thanx
                  • 6. Re: zipcodes within milage
                    rmorgan Level 1
                    With the example query I posted, all you will need is a column for the zipcodes, latitudes and longitudes. Anything else is up to you.
                    • 7. Re: zipcodes within milage
                      Nick201 Level 1
                      so you have two different zipcode table right?
                      OR just you joining one table two times.

                      • 8. Re: zipcodes within milage
                        rmorgan Level 1
                        Its just one table.