Hi Jim I'm the one who asked for other ways. It is an interesting idea but I'm not sure how you would use it since I doubt if USPS has standard distances between zip codes. If they did, it would be great, but I'm pretty sure they base zip codes on population density.
If I get over this cold I will have a good weekend! :) Thanks Tom -----Original Message----- From: Jim Curran [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 3:51 PM To: CF-Talk Subject: RE: List of Cities Tom, Someone asked if there was a faster way, so I bounced that idea out there. Your methods are correct, and I did not dispute them. I was merely bringing out another option to explore. Obviously the lack of overhead gain does not make this solution worthwhile. Have a good weekend. - j -----Original Message----- From: Tom Nunamaker [mailto:[EMAIL PROTECTED]] Sent: Friday, February 22, 2002 4:38 PM To: CF-Talk Subject: RE: List of Cities Jim I find it hard to use the zipcode numbers as a filter due to the lack of information on how they are derived. How do I know what the correct amount is for your second possibility? Is it 2000? 1500? 2500? How do I know for sure? You don't. The only way you do know is by calculating the distances. How do I know instate is fine? You said is should be...but do you know for a fact or is that speculation? You said they should run faster since there is no math calculations involved. That's misleading since we are both using SQL between statements to create a recordset. I fail to see how I know the distance between two zipcodes with your method. Don't you have to calculate that the same way I do....with lat/longs? My method only does that calculation for a small number of wasted computations but I guarantee my results are within the correct distance desired. The method I'm using is VERY fast. The only distance calculations you do are within the rectangle of results you get by filtering out the vast majority of data. You're basically throwing out the corners of the box to make a circle if that makes sense. For instance, if I do a query looking for something within 120 NM of 0N 0W, I do this: SELECT * FROM mydata WHERE lat BETWEEN -2 AND 2 AND lon BETWEEN -2 AND 2 The farthest distance I could possibly have (assuming the rectangle is nearly a perfect square) would be 2^(1/2) = 1.4142 * 120 or 169.7 NM All other data would have been filtered out at this point...which is about 98.6% of the planet. You're going to have to do the distance calculations on this dataset and end up throwing out a maximum of about 29% of these remaining records. NOTE: As your go father North, the square is more rectangular and you're excess records will slowly increase, but not dramatically. Perhaps I'm still missing something about just using zipcode numbers as my initial filter. If I want to search within 50 miles, what zipcode amount do I vary my target from? +/- 50? What's the key? Do you have a working example? 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 3: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 ______________________________________________________________________ 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=coldfusiona 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
