"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/