Thanks, I'll give it a shot. I don't have CF so I'll improvise. Rob p.s. LotR The Two Towers is Awesome!!! Can't wait till the "Return of the King" tip time <tip type="Session Variables Aren't Perfect" author="Rob.Smith"> I've done some intense web apps that deal with multiple session variables. In one particular instance I used 13 different variables. Well besides the fact that these become difficult to manage quite quickly, time seems to take over. Thus, session variables die (default 20 minutes). Its always a good idea to store the variables locally just in case your lose one or two. Things get messy after that. Once you've stored them locally on your pages, feel free to leave the project on while you do other stuff if you need to. </tip> -----Original Message----- From: Joshua Olson [mailto:joshua at waetech.com] Sent: Thursday, December 19, 2002 1:54 PM To: thelist at lists.evolt.org Subject: Re: [thelist] Zip/Postal Code Radius ----- Original Message ----- From: "Rob Smith" <rob.smith at THERMON.com> Sent: Thursday, December 19, 2002 2:34 PM > Howdy, > > This is something I've always been curious about. How do Zip/Postal Code > finders work where you enter your zip and it brings you the closest > something sorted by distance? Do you have to have a listing of all known zip > codes, distances to and fro or is there a website/formula that will > calculate this for you? Hi Rob, I find myself doing this quite a bit. Most zip code databases include the latitude and longitude of the Post Office. If you know the latitude and longitude of the two points, then finding the distance can be done a couple of ways. The hardest (mathematically) is to find the Great Circle distance. While it is a more complex equation that the other method (I'll cover that next) it provides the best results. The technique I use is MUCH simpler, though. In this instance, I'll assume I want to find all businesses within 10 miles of a certain zip code. And, assume that each business already has a zip code field, a attitude field, and a longitude field. All of which are prefilled. Here is some code I use, in CF with SQL Server 2k, to do the lookup: <cfquery name="get_zip"> SELECT latitude, longitude FROM zipcode WHERE zip = '12345' </cfquery> <cfset radius = "10"> <cfset miles_per_lat = 69.09> <cfset miles_per_lon = Abs(69.09 * Cos(Val(get_zip.latitude) * Pi() / 180))> <cfset deg_latitude = radius / miles_per_lat> <cfset deg_longitude = radius / miles_per_lon> <cfquery name="get_businesses"> SELECT *, ROUND(SQRT(SQUARE(#miles_per_lat# * (latitude - #Val(get_zip.latitude)#)) + square(#miles_per_lon# * (longitude - #Val(get_zip.longitude)#))), 1) AS distance FROM business WHERE latitude BETWEEN #Val(Val(get_zip.latitude) - deg_latitude)# AND #Val(Val(get_zip.latitude) + deg_latitude)# AND longitude BETWEEN #Val(Val(get_zip.longitude) - deg_longitude)# AND #Val(Val(get_zip.longitude) + deg_longitude)# </cfquery> ~~~~~~~~~~~~~~~~~~~~~~~~ For small radiuses this should work great. Far larger distances, the system breaks down. Here's a basic idea of what it's doing. 1. It does a lookup of the zip codes lat and lon from the database. 2. It records how many miles there are per degree latitude (69.09 globally) 3. It determines how many miles there are per degree longitude. This depends on the latitude. The number of miles shrinks as we approach the north and south pole. 4. It uses 2 and 3 to determine how many degrees to look north-south and east-west. This designates a rectangle on the ground to search. 5. Use a simple lookup on the businesses table to find businesses within the rectangle. This may return businesses that are outside of the designated radius... those businesses at the corners of the rectangle may actually be farther away than the basic radius... but you can always filter the list on output. Good Luck, -joshua -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !