From: | Milo Hyson <milo(at)cyberlifelabs(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Postal code radius searches |
Date: | 2002-02-06 18:36:18 |
Message-ID: | 200202061024.5796@cyberlifelabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
--
Milo Hyson
CyberLife Labs, LLC
From | Date | Subject | |
---|---|---|---|
Next Message | wsheldah | 2002-02-06 19:18:59 | Re: Postal code radius searches |
Previous Message | Justin Clift | 2002-02-06 17:47:21 | Re: ipc-daemon error |