This only sets a "bounding box" of zips to consider when doing the actual queries and calcs. If you try to do a brute force match over your entire database, it becomes extremely slow. So, this query draws (selects) a square with sides equal to the desired radius, then passes those values to the queries and calcs that do the real meat of the work. It doesn't return the zips in the desired radius, radii being round and all that. The distance calcs drop the corners, so to speak, later in the tag.
It's extremely fast in operation - check out http://www.dbactive.com/tags/radtest.cfm - it output the calc time for each radius lookup. Keep in mind that this demo is running on a very bloated MSAccess DB on a shared hosting server. As an example: 84 Zip Codes within 10 miles of 60601 (CHICAGO, IL): Processed in: 281 MS This includes sort time as well (sorted by the distance in miles in this case). Based on your example of Alaska, here is an example. Using 10 miles as the radius, the system returns: 25 Zip Codes within 10 miles of 99501 (ANCHORAGE, AK): Processed in: 78 MS The highest distance returned from the center zip was 8.73 miles. So, to check and see if the tag is even fairly accurate, I ran a search at 99 miles: 52 Zip Codes within 99 miles of 99501 (ANCHORAGE, AK): Processed in: 141 MS I'll show the three pertinent entries - the aforementioned 8.73 mile zip and the next two: 99516 ANCHORAGE AK ANCHORAGE 907 8.73 99687 WASILLA AK MATANUSKA SUSITNA 907 12.66 99577 EAGLE RIVER AK ANCHORAGE 907 13.65 So, no other zips within the orignal radius of 10 miles. The tag seems fairly accurate. I'm sure it could be better, but for most locator type systems it's definately close enough. It's not doing driving distances, obviously. -------------------------------------- jon roig wrote: > If you're using the JCSM database, here's a simple query to pull out > all the zips within #radius# number of miles. > <CFQUERY NAME="findLocal" DATASOURCE="Zip_Search"> > SELECT jcszip.Zip, > jcszip.LastLineCity, > jcszip.State, > jcszip.Lat, > jcszip.Lon > FROM jcszip > WHERE (((jcszip.lat)>= (#geoCode.Lat# - (#Radius# * 0.0005)) > And (jcszip.lat)<= (#geoCode.Lat# + (#Radius# * 0.0005)) > ) AND ((jcszip.lon)>= (#geoCode.Lon# - (#Radius# * 0.0005)) > And (jcszip.lon)<= (#geoCode.Lon# + (#Radius# * 0.0005)) > )) > ORDER BY jcszip.Zip > </cfquery> I don't know what area the JCSM database covers, but from the looks of it I wouldn't count on this code unless it only covers the equator because the distance between meridians differs with the lattitude. And if you think the difference is insignificant: chances are your search area is only 50% of what it should be when doing a search from Anchorage, Alaska. Just think of the difference in distance between meridians on the equator and on the poles. The difference between the 0-meridian and the 1-meridian (either east or west) on the equator is 60 miles (n), and on the poles they actually overlap. My trigo is a bit rusty, but I will give it a shot to get the code correct: distance = acos(cos(latA)cos(latB)cos(lonB-lonA)+sin(latA)sin(latB))/360 * 2 * pi * R R = radius earth in the appropriate units lon from -180 to 180 degrees lat from -90 to 90 degrees If you are dealing with small distances and don't actually overlap one of the poles a reasonable approximation would be to compensate only the longitude by division with cos(#geoCode.Lat#): SELECT * FROM jcszip WHERE (((jcszip.lat)>= (#geoCode.Lat# - (#Radius# * 0.0005)) And (jcszip.lat)<= (#geoCode.Lat# + (#Radius# * 0.0005)) ) AND ((jcszip.lon)>= (#geoCode.Lon# - (#Radius# * 0.0005)/cos(#geoCode.Lat#)) And (jcszip.lon)<= (#geoCode.Lon# + (#Radius# * 0.0005)/cos(#geoCode.Lat#)) )) ORDER BY jcszip.Zip (I assume your factor 0.0005 is correct for miles (s).) Naturally you need to apply the full calculation on the resultset to get the actual distance, but this first approximation should reduce its size sufficiently to make that possible. And for the PostgreSQL users: check /contrib/, this functionality is built-in to the database. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
