I have a stored proc I wrote for SQLServer that returns the number of cities within a defined radius of a zip code based on lat/lon. If anyone wants that they are welcome to it. Its at www.cfexperts.com/downloads/locater.zip
Duane -----Original Message----- From: Jim Curran [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 4:03 PM To: CF-Talk Subject: RE: List of Cities After some further research, Tom, I think you are correct. It does not seem to matter for north-south states. Zipcodes start in the north-westand seem to zigzag across the country ending up in Washington State. So three possibilities: 1) Instate should be fine. 2) for out of state comparisons... use a larger range, like maybe 2000? 3) north-south comparisons can be done using zip code range and east west can be done using longitudinal range. These should both run faster since there is no math calculations involved. I think that made sense. What do you think? - j -----Original Message----- From: Tom Nunamaker [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 3:32 PM To: CF-Talk Subject: RE: List of Cities Jim Does that also work for zip codes across state lines? For instance, chicago is not far from Indiana. Would the zip codes still be numerically and physically close or does the state matter at all? 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 1:47 PM To: CF-Talk Subject: RE: List of Cities 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 ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
