Zip Code Radius 
--------------------------------------------------------------------------------
Ever wondered how sites are able to return stores that are within a certain radius of your own zip code? There are basically two steps that are required to handle this task.

The first step is going out on the internet and finding someone that already has a zipcode database available for download. There are tons of places that offer this for a fee. I did a search through google.com "zip code free database longitude latitude .mdb" and found about 235 returned records. I saw process for the database from 14.95 and up. If you are lucky you may be able to find a free outdated version to test with.

Once you have the database you are on to step 2. Step 2 can be broken down into 3 steps. Step A is to create a form that can be used to call your query on the database of zip codes. In the form below I have added two options to return the data: Table based and Comma delimited. The Table version can be used to confirm that there is data being returned and the Comma delimited version can be used to search your database.


<form action="zipcoderadiustest.cfm" method="post" name="getzipradius">
    Enter ZipCode<br>
    <input type="text" size="6" name="passedzipcode"><br>
    Enter Miles<br>
    <select name="passedradius">
        <option value=
"" selected>Select a maile range</option>
        <option value=
"5">5</option>
        <option value=
"10">10</option>
        <option value=
"15">15</option>
        <option value=
"20">20</option>
        <option value=
"25">25</option>
        <option value=
"30">30</option>
        <option value=
"35">35</option>
        <option value=
"40">40</option>
        <option value=
"45">45</option>
        <option value=
"50">50</option>
        <option value=
"55">55</option>
        <option value=
"60">60</option>
        <option value=
"65">65</option>
        <option value=
"70">70</option>
        <option value=
"75">75</option>
        <option value=
"80">80</option>
        <option value=
"85">85</option>
        <option value=
"90">90</option>
        <option value=
"95">95</option>
        <option value=
"100">100</option>
    </select>

    <br>
    <input type="radio" Checked name="passedreturndataformat" value="Table">Table
    <br>
    <input type="radio" name="passedreturndataformat" value="Comma">Comma delimited string
    <br>
    <input type="submit" name=
"submit" value="Submit">
</form>



Step B in step two is to take the information passed in the form above and to build your query to return the zipcodes within your selected radius. (Side note:When building queries I always use a dynamic variable for my datasources. This allows me to define my datasource once within the application.cfm. Then, if I ever need to change my datasource across the site all I have to do is change it in one place.)

In order to return the zip codes within the radius passed you must first find the longitude and latitude of the zip code passed. After this query I am checking to see if there are any records returned. If there are no records returned I pass back an error message.

The sql is quite complex and the static numbers in the query should not be changed because they are as stated, static values. 3963 is equal to the radius of the earth. 57.2958 is 180/pi and is used to convert latitude or longitude from degrees to radians.

The statement:
ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
(COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
* 3963) AS distance

retuns a variable named distance and allows you to show the distance from the zip code passed and the zip code found within the radius passed. So not only do you get the variables within the select statement returned you also get the variable named distance.

Please note that if you are using an ACCESS database that the ACOS is not valid. I do not have an answer for an access database at this time. If you find one please let me know and I will update this tutorial.

Please note that if you are using an MS SQL database that an additional parameter is required. After the 3963 you will need to add a ",3". So the last line would read:
* 3963, 3) AS Distance

<CFPARAM name="errormessage" type="string" default="">
<CFPARAM name=
"passedzipcode" type="string" default="78626">
<cfquery name=
"passedzip" datasource="#dsname#">
SELECT * FROM zipcodes
WHERE ZIPcode=#passedzipcode#
</cfquery>

<cfif passedzip.recordcount eq 0>
    <cfset errormessage =
"No records found for passed search #passedzipcode#">
    <cfabort>
</cfif>

<cfif errormessage eq ''>


<cfquery datasource="#dsname#" name="getlocs">
    SELECT zipcode, latitude, longitude, statename, city,
    ROUND((ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
    (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
    COS(longitude/57.2958 - #passedzip.longitude#/57.2958))))
    * 3963) AS distance
    FROM zipcodes
    WHERE (latitude >= #passedzip.latitude# - (#passedradius#/111))
    And (latitude <= #passedzip.latitude# + (#passedradius#/111))
    AND (longitude >= #passedzip.longitude# - (#passedradius#/111))
    AND (longitude <= #passedzip.longitude# + (#passedradius#/111))
    ORDER BY distance
</cfquery>

</cfif>


Step C in step two is to display the information returned from the query. Three colors are defined so that they can be passed to the function cf_bgcolortoggle so that the table row colors are changed as the rows more rows are added to the table. The following variables are also defined: dsname, passedzipcode, passedradius, passedreturnformatdata, and passedreturneddata. This will prevent any errors if one of the variables are not found during the processing of the code below.

After determining whether the information should be returned within a table or comma delimited the cfouput will loop through all of the records passed. After all of the data has been returned a second cfoutput is included to display the generated output.

<CFPARAM name="dsname" type="string" default="your datasource name">
<CFPARAM name=
"passedzipcode" type="string" default="78626">
<CFPARAM name=
"passedradius" type="string" default="20">
<CFPARAM name=
"passedreturndataformat" type="string" default="table">
<CFPARAM name=
"passedreturneddata" type="string" default="">

<CFPARAM name=
"rowonecolor" type="string" default="whitesmoke">
<CFPARAM name=
"rowtwocolor" type="string" default="white">
<CFPARAM name=
"rowthreecolor" type="string" default="000080">

<cfif passedreturndataformat eq 'table'>
    <cfset passedreturneddata = passedreturneddata & "<table width=55% border=0 cellpadding=0 cellspacing=0>    
    <!--- this is used to make the headers of the table colored --->

    <tr>
    <td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">City</font></td>
    <td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">State</font></td>
    <td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">ZipCode</font></td>
    <td valign=""center"" align=""center"" bgcolor=""#rowthreecolor#""><font color=""#rowtwocolor#"">Distance</font></td>
    </tr>"
>
    
    
    <cfoutput query="getlocs">
    <!--- this is used to make the headers of the table colored --->
    <!--- cf_bgcolortoggle can be downloaded from cfmsource.com --->

    <cf_bgcolortoggle
                rowonecolor =
"#rowonecolor#"
                rowtwocolor =
"#rowtwocolor#">
    
    <cfset passedreturneddata = passedreturneddata & "
    <tr>
    <td align=""center"" bgcolor=""#rowcolor#"">#city#</td>
    <td align=""center"" bgcolor=""#rowcolor#"">#statename#</td>
    <td align=""center"" bgcolor=""#rowcolor#"">#zipcode#</td>
    <td align=""center"" bgcolor=""#rowcolor#"">#round(distance)#</td>
    </tr>"
>
    
    
    </cfoutput>
    <cfset passedreturneddata = passedreturneddata & "
    </table>"
>
    <cfset errormessage = "">
<cfelse>
    <cfset passedreturneddata =
"">
    <cfoutput query=
"getlocs">
    <cfif passedreturneddata gt "">
        <cfset passedreturneddata = passedreturneddata &
"|#city#,#statename#,#zipcode#,#round(distance)#">
    <cfelse>
        <cfset passedreturneddata = passedreturneddata &
"#city#,#statename#,#zipcode#,#round(distance)#">
    </cfif>
    </cfoutput>
    <cfset errormessage =
"">
</cfif>



<cfoutput>
    <cfif errormessage gt ''>
       
#errormessage# 
    <cfelse>
       
#passedreturneddata#
    </cfif>
</cfoutput>


To see a working example of this please visit www.cfmsource.com.
Questions? Comments? Please e-mail me at the email address below.


About This Tutorial
Author: kaigler
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5,CFMX
Total Views: 133,894
Submission Date: June 29, 2003
Last Update Date: June 05, 2009
All Tutorials By This Autor: 1
Discuss This Tutorial
  • I spoke too soon. Forget 46, use 69 for both latitude and longitude.

  • I searched Google and found out that there are 46 miles per degree for latitude, and 69 miles per degree of longitude. Now my search works correctly! This is my new where clause: WHERE (zipcode_latitude >= #qLookup.zipcode_latitude# - (#radius#/46)) And (zipcode_latitude <= #qLookup.zipcode_latitude# + (#radius#/46)) AND (zipcode_longitude >= #qLookup.zipcode_longitude# - (#radius#/69)) AND (zipcode_longitude <= #qLookup.zipcode_longitude# + (#radius#/69))

  • Also, 111 works as a static divisor for degrees of latitude, however the kilometers to degrees longitude relationship is not static. Degrees of longitude are closer together at the poles. Therefore, shouldn't the divisor for the longitude parameters be a variable value? Thanks! AndyEye

  • The select clause is returning a distance in terms of miles is it not? Shouldn't the where clause and the select cause be using the same units?

  • 111 is used because that is the number of kilometers in a degree of latitude.

  • Where does the constant static value of 111 in your where clause come from? In my own usage it seems that your where clause is excluding some records which should be returned. For example, if I input a radius of 3000 and a zip of 21122 (Maryland) I would expect to have the zip 90210 (California) to be among the returned records. It is not. In fact, I have to raise the radius to at least 5000 before the 90210 code is returned. When the record is returned, the distance returned is only 2330. Therefore, this record should have been returned given the 3000 radius. Any ideas? Thanks, AndyEye

  • missing comma.

  • I got this error running the query on MSSQL: Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'ACOS'. SQL = "SELECT zipcode, state, city, latitude, longitude ROUND((ACOS((SIN(40.624499999999998/57.2958) * SIN(latitude/57.2958)) + (COS(40.624499999999998/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - -111.8245/57.2958)))) * 3963, 3) AS distance FROM zipcodes WHERE (latitude >= 40.624499999999998 - (50/111)) And (latitude <= 40.624499999999998 + (50/111)) AND (longitude >= -111.8245 - (50/111)) AND (longitude <= -111.8245 + (50/111)) ORDER BY distance" Data Source = "RCU" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (53:3) to (53:45) in the template file D:\rcudev\market\radius.cfm. Wondering what happened? Michael

  • I still can't figure out the query in access. I'm trying to use the code you had in your website code, and access doesn't like it. How do I do #passedzip.latitude and #passedzip.longitude with a parameter query? Can I assign a variable?? What do I do. Here is my code: SELECT * ROUND((ACOSH((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) + (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) * COS(longitude/57.2958 - #passedzip.longitude#/57.2958)))) * 3963) AS distance FROM tblAll_Zips_Lat_Long, tblCustomer2 WHERE [ZipCode]=#passedzipcode# (latitude >= #passedzip.latitude# - (#passedradius#/111)) And (latitude <= #passedzip.latitude# + (#passedradius#/111)) AND (longitude >= #passedzip.longitude# - (#passedradius#/111)) AND (longitude <= #passedzip.longitude# + (#passedradius#/111)) ORDER BY distance; my field names are ZipCode, Latitude, Longitude Thanks again!

  • The query above will not work in access. The acos sign is not a valid function in access. Once you figure out the query for access, you would do a simple join between the your database and the database of zipcodes. Ex: select * from zipcodetable a, yourclients b where a.zipcode = b.zipcode Make sure that the data type of the zipcode is the same in both tables. I could not figure out the query for access so I switched databases to MySQL. It looks like cfmsource.com has a sample for sale in access.

Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.