7 Replies Latest reply on Jan 11, 2007 8:28 AM by Newsgroup_User

    Search by zipcode

    Level 7
      Anyone do a 'search by zip code within a certain distance' with a variable
      of distance (i.e. 25 miles)?
      I downloaded a zip code database with a ton of zip codes in it. I can get
      the longitude and latitude of each zip code when querying it with the
      entered zip code.

      How do I then query the database to search within 25 miles of the longitude
      and latitude? I would like the function to query the database and grab a
      bunch of zip codes within the distance and then query that against the
      possible zip codes in the rescues database to match only those within the 25
      miles.

      Any tutorials? Anyone have an example finished (code)? Anyone know the
      correct way to do this? I am only guessing on how it is actually done.

      Thanks!
      --
      Wally Kolcz
      MyNextPet.org
      Founder / Developer
      586.871.4126


        • 1. Re: Search by zipcode
          Level 7
          Any tutorials? Anyone have an example finished (code)? Anyone know the
          correct way to do this? I am only guessing on how it is actually done.

          Thanks!

          I do not have the exact code, but Google does.

          When you go searching you are going to get formulas that you use to
          determine the min and max long and lat that contain your 25 mile radius
          area. Which one you use depends on how you want to balance performance
          with accuracy and whether you OK with looking at the world as a flat
          grid or need to take the curvature of the global sphere into account.

          Once you have chosen your algorithm, you just plug in the numbers and
          you will get your min and max long and lat numbers. You then uses these
          in your query to get all zip codes between these four values.




          • 3. Re: Search by zipcode
            Level 7
            Thanks. I looked using google, but I dont know which to find.
            Most show me 2 longitudes and 2 latitudes and gives me the difference in
            miles.

            (a^2) + (b^2) = (c^2)
            If a = latitudinal distance, b = longitudinal distance, then c = distance
            between the two points.
            For each ZIP code, you'll have to do
            a = x1 - x2
            b = y1 - y2
            c = Sqrt((a^2) + (b^2))
            Where x1 is latitude of the "from" ZIP, x2 is lat. of the "to" ZIP,
            y1 is long. of the "from" ZIP and y2 is long. of the "to" ZIP.

            Someone supplied me with this CF function, but it also appears to do the
            same:

            Here is the code from cflib.org that calculates the distance.


            <cfscript>
            function LatLonDist(lat1,lon1,lat2,lon2,units)
            {
            // Check to make sure latitutdes and longitudes are valid
            if(lat1 GT 90 OR lat1 LT -90 OR
            lon1 GT 180 OR lon1 LT -180 OR
            lat2 GT 90 OR lat2 LT -90 OR
            lon2 GT 280 OR lon2 LT -280) {
            Return ("Incorrect parameters");
            }

            lat1 = lat1 * pi()/180;
            lon1 = lon1 * pi()/180;
            lat2 = lat2 * pi()/180;
            lon2 = lon2 * pi()/180;
            UnitConverter = 1.150779448; //standard is statute miles
            if(units eq 'nm') {
            UnitConverter = 1.0;
            }

            if(units eq 'km') {
            UnitConverter = 1.852;
            }

            distance = 2*asin(sqr((sin((lat1-lat2)/2))^2 +
            cos(lat1)*cos(lat2)*(sin((lon1-lon2)/2))^2)); //radians

            if(units neq 'radians'){
            distance = UnitConverter * 60 * distance * 180/pi();
            }

            Return (distance) ;
            }
            </cfscript>


            I need one that shows the range of longitude and latitudes to use in my
            query to find all the zips that fall in the range.

            Any ideas on what I should be looking for.

            Sorry.


            • 4. Re: Search by zipcode
              Level 7
              Ok, I am retarded. Each I find asks for 2 longitudes and 2 latitudes. What
              am I missing. I can grab the longtitude and latitude from the user's zip
              code from the database of zipcodes I have. I can ask for the max distance
              (25 miles, 50 miles, etc.) But then I need to plug those values into a
              formula to generate a range of zipcodes that fall within, right?

              I found this while searching for the formula:

              What is my second long and lat when I am supplying the first long and lat
              and distance from the user's information.

              <!---UDF distance Formula||--->
              <CFSCRIPT>
              //calculate the Deltas
              function distance(lat1,lat2,lon1,lon2)
              { km=6367;
              deltaLon=twoRadians(lon1)-twoRadians(lon2);
              deltaLat=twoRadians(lat1)-twoRadians(lat2);
              //intermediate values
              intMedVal = sin2(deltaLat/2) + cos(twoRadians(lat1)) *
              cos(twoRadians(lat2)) * sin2(deltaLon/2);
              //the great circle distance in radians gcd = 2 *
              arcSin(getMin(1,Sqr(intMedVal)));
              //multiply the the radians by the radius to get the distance in specified
              units d = km * gcd;
              //this only applies to us (ANDRONICS.CO.UK)
              //it relevant to the vendor that is displaying the map data
              //the map is coming back in rectangular format they use the length as the
              zoom distance
              //we multiply it by 2 two get an equal zoom distance from top to bottom
              d = round(d * 2); }
              //Convert to Radians
              function twoRadians(currDegree){
              (currDegree * (Pi()/180));
              }
              function sin2(currVal){
              (1 - cos(2*currVal))/2;
              } function arcSin(x){
              atn(x/Sqr(-x * x + 1));
              } function getMin(y,z)
              { if(y lte z){ getMin=y;
              }else{
              getMin=z;
              }
              }
              </CFSCRIPT>
              <CFSET
              ATTRIBUTES.D=distance(maxLatitude,minLatitude,maxLongitude,minLongitude)>

              I appreciate the help and the patients. This one really confuses me... :)


              • 5. Re: Search by zipcode
                Level 7
                Would the second longitute and latitude be the base plus the milage
                difference?

                Say the base long. was 25, would the second be 50? And the same for the
                latitude?


                • 6. Re: Search by zipcode
                  philh Level 1
                  The basic idea is to query the table for the ZIP codes whose lat/longs, when plugged into the formula, fall within the desired range of the "home" ZIP code. Hint: have the database, not ColdFusion, do the math.
                  • 7. Re: Search by zipcode
                    Level 7
                    Thanks. I got the thing all finished and it works great!