4 Replies Latest reply on Jan 10, 2007 12:25 PM by Newsgroup_User

    Longitude and Latitude to search by zip

    Level 7
      I found the formula that calculates distance with zip codes. Where do I get
      the 2nd long and lat for the formula? Do I add the distance (25 miles) to
      both long and lat to get the 2nd set of variables? Or do I use the base long
      and lat add and subtract to get the 2 sets?

      Has anyone done a search using zip codes to determine everything within a
      specific range (miles / kilometers)?

      And when I get the result of the formula, how does it apply to getting a
      range of zip codes? And how to I query using the result.


      Wally Kolcz
      Founder / Developer

        • 1. Re: Longitude and Latitude to search by zip
          Level 7
          I entered "Zip Code Radius" into google and I am finding many products
          and services that will do this for you. Haven't found a do it yourself
          version yet.
          • 2. Re: Longitude and Latitude to search by zip
            Level 7
            Ok that did not take too long.

            Building a Zip Code Proximity Search with ColdFusion
            [ColdFusion Developer's Journal]

            Which references this article:
            Proximity Searches for Fun and Profit [Webmonkey]
            • 3. Re: Longitude and Latitude to search by zip
              rmorgan Level 1
              see if this will work for you:

              SELECT b.zipcode, b.state,
              (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) *
              COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2))))) AS distance
              FROM zipcodes a, zipcodes b, zipcodes c
              a.zipcode = '02134' AND # <-- Your starting zipcode
              a.zipcode = c.zipcode AND (3956 * (2 * ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) + COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) * POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
              )))) <= 5 # <-- Your target radius
              GROUP BY distance
              • 4. Re: Longitude and Latitude to search by zip
                Level 7
                Took a lot of tweeting and work, but I got it to finally work, Thank you so

                I had to edit the cfc to work (the SQRT function was spelled out SQUARE and
                gave me fits) and I had to capture and convert a whole new set of zip codes,
                longitudes and latitudes and convert them into the radiuses. But all in all
                it finally worked. The database on the example is written for MS SQL and I
                am using MySQL. Each enough to do. All I had to do is take the text file,
                add headers to the data, save it as a CSV, take it using CFhttp and loop it
                into the MYSQL database while converting the longitude and latitude into
                'rlongitude' and 'rlatitude' which the CFC requires to make it work.

                Thanks again Ian. I still want to add you to my site with a linkback if you
                want it.

                If anyone wants the modified CFC, application.cfm, and a MYSQL script for
                the zipcodes of the US (and beyond). Let me know

                wkolcz (at) mynextpet (dot) org