435,463 Members | 3,115 Online
Need help? Post your question and get tips & solutions from a community of 435,463 IT Pros & Developers. It's quick & easy.

# zip distance in a square, not radius

 P: n/a I have successfully created a zip code radius search, but the performance is unacceptable. I have two tables. The first is 52K zip codes w/lat and long. The second is 3K national business addresses. Using the zip code 10010, it takes almost 60 seconds to return 122 businesses within a 25 mile radius. This slowdown is almost entirely due to the query radius distance formula. I wonder if someone knows the formula to calculate the distance within a given square. Would this increase performance? I presume the results would include zips in the "corners", but performance boost might be worth it. Thanks in advance. Jul 17 '05 #1
4 Replies

 P: n/a "Xenophobe" wrote in message news:sSAZb.91266\$jk2.439590@attbi_s53... Using the zip code 10010, it takes almost 60 seconds to return 122 businesses within a 25 mile radius. This slowdown is almost entirely due to the query radius distance formula. I wonder if someone knows the formula to calculate the distance within a given square. Would this increase performance? I presume the results would include zips in the "corners", but performance boost might be worth it. I've got a system where I do something like this, but I precalculate the distances. Whenever I update the ZIP database, I do something similar to the following: 1) Calculate the distance (straight-line according to the particular formula I use) between every pair of ZIP codes. In my case, I'm only using ZIP codes for Long Island and the five boroughs, so I've only got a few thousand, resulting in a manageable table. If you did this for the entire national database, you're talking about LOTS of rows. The table looks like this: CREATE TABLE ZIPDistance ( ZIPCode1 DECIMAL(5), ZIPCode2 DECIMAL(5), Distance DECIMAL(3) ) By convention, I always store the "lower" of the two ZIP codes in ZIPCode1. 2) Delete everything in the table where the distance is over my maximum lookup distance. You can now quickly get all of the businesses within a certain distance with a query along these lines: SELECT * FROM Business WHERE ZIPCode IN ( SELECT ZIPCode2 FROM ZIPDistance WHERE ZIPCode1 = 11787 AND Distance <= 10 UNION SELECT ZIPCode1 FROM ZIPDistance WHERE ZIPCode2 = 11787 AND Distance <= 10 UNION SELECT 11787 ) Note: I'm running on SQL Server for this project, so I can write these sorts of queries. You'll need to come up with a working method for MySQL. That is left as an exercise for the interested student. :) Steve -- Steven C. Gallafent - The Computer Guy st***@compguy.com - http://www.compguy.com/ Jul 17 '05 #3

 P: n/a Hi, On Sat, 21 Feb 2004 04:10:00 GMT, "Xenophobe" wrote: I have successfully created a zip code radius search, but the performance isunacceptable.I have two tables. The first is 52K zip codes w/lat and long. The second is3K national business addresses.Using the zip code 10010, it takes almost 60 seconds to return 122businesses within a 25 mile radius. This slowdown is almost entirely due tothe query radius distance formula. What about the suggestion I made last time you asked? It would be heaps faster, because it doesn't do any calculation HTH, Jochen -- Jochen Daum - Cabletalk Group Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourceforge.net/projects/phpdbedittk/ Jul 17 '05 #5

### This discussion thread is closed

Replies have been disabled for this discussion.