Because zipcodes that are numerically close are physically close, you can speed up query time by limiting zip searches to within a +- 500 - 1000 range of the originating zip code.
EG. if you are in zip code 50000, a distance search between 45000 an 55000 should suffice. - j jim.curran technical.director nylon.technology 212.691.1134 [EMAIL PROTECTED] -----Original Message----- From: Tom Nunamaker [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 2:08 PM To: CF-Talk Subject: RE: List of Cities The US Census agency has a free listing of lat/lon for zip codes at http://ftp.census.gov/geo/www/gazetteer/places.html I wrote a bunch of UDF's for latlon calculations and posted them at cflib.org about two weeks ago. I see they are still sitting in the submission queue. <Why does it take two weeks to get to it guys?> If anyone needs the UDF's sooner, I can dig them out of the code I'm using at www.morervs.com. Look at http://www.morervs.com/fb3/index.cfm?fuseaction=search.home and try the distance search. I think that's the kind of thing you're after. I haven't seen how others do this but my solution was based on the fact that one degree of latitude is always equal to 60 nautical miles. You take the distance for the radius of the search and convert it to nautical miles. Then add that many degrees in a box around the starting point. If my search was 138 statute miles (about 120 Nautical miles), the 120 nautical miles is 2 degrees of arc at the earth's surface (Assuming the earth is a perfect sphere). Do an SQL query to filter out the vast majority of records that fall outside of the box surrounding your origin... Something like Select * >From myzipcodedata Where lat between (targetlat-dist/(60*1.15)) and (targetlat+dist/(60*1.15)) AND lon between (targetlon-dist/(60*1.15)) and (targetlon+dist/(60*1.15)) This leaves you with a rectangle that's CLOSE to a radius search. All that's left to do is to loop through these records and actually calculate the distance and throw out the ones that exceed your search distance. One degree of longitude is only equal to 60 NM at the equator. The farther North you go, the smaller the distance gets until you get to the poles where it's zero. Your initial box to filter records out is actually somewhat larger than your search distance but it will never be smaller than your search distance. If someone has a better way, I'd love to see it but this method works fairly quickly. Tom Nunamaker Paladin Computers Macromedia Certified Advanced ColdFusion 5.0 Developer http://www.toshop.com/ [EMAIL PROTECTED] -----Original Message----- From: Jim Curran [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 12:29 PM To: CF-Talk Subject: RE: List of Cities Hi, There are zipcode lists with LAT and LONG coordinates for each zipcode. http://www.zipinfo.com/products/products.htm You can calculate your route's slope, distance and direction using the codes, and then look up intersection paths in the DB. Hope that helps. I've done this before, and it is actually pretty exciting when it starts working ;) - j jim.curran technical.director nylon.technology 212.691.1134 [EMAIL PROTECTED] -----Original Message----- From: Duane Boudreau [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 9:57 AM To: CF-Talk Subject: List of Cities I am faced with a rather interesting challenge. I will be working on an application that will help a company increase efficiency in shipping goods around the company. The company has its own fleet of shipping vehicles but occasional will use independents for small loads. What I need to figure out is if independent x from zip code z1 is driving to zip code z2 what cities/towns does the route pass through or close to (within 2 miles). TIA, Duane ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
