I've been doing something similar with a database with about
2 million aircraft positions. Create an index with latitude,
longitude, and zipcode for starters.
Simpler math helps -- for example all zip codes within a
certain (whole or fractional) degrees lat and long of
another zip is an easy and fast calculation, as it's just
addition/subtraction. You get a cell or box around the
center zip.
Distance on the surface of the earth (the radius about the zip)
takes trig functions and mult/division, and usually takes longer.
For some purposes the simpler solution may work, for others you
may have to do the math.
Milo Hyson wrote:
> I've been struggling with this problem for a while now and I can't seem to
> find a solution. I have a postal-code database, currently populated with over
> 76,000 United States ZIP codes. Each record contains, among other things, the
> latitude and longitude for the postal code. I have a stored procedure that
> calculates the distance between any two points on the globe. I'm trying to
> figure out a fast way to locate all of the postal codes within an arbitrary
> radius of another postal code.
>
> The brute force method requires a sequential scan of all 76,000 records
> looking for those that fall within the specified area. A more
> high-performance method would be to pre-calculate the distances between all
> postal codes (possibly limiting the distance to save space). However, this
> requires more than 76,000 ^ 2 database operations. On a 1 GHz box, I
> calculated this would take nearly one year complete. It would take twice as
> long if I wanted to create a second cache for city/state searches.
>
> Does anybody have and tips on solving this issue? Is there any sort of
> complex index I could create based on the results of an arbitrary stored
> procedure call? Maybe some custom C code?
>
>
--
P. J. "Josh" Rovero Sonalysts, Inc.
Email: rovero@sonalysts.com www.sonalysts.com 215 Parkway North
Work: (860)326-3671 or 442-4355 Waterford CT 06385
***********************************************************************
