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 Get the mailserver that powers this list at http://www.coolfusion.com
