"Xenophobe" <xe*******@planetx.com> 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/