Milo Hyson <milo@cyberlifelabs.com> writes:
> 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.
[ some overlap here with previous answers, but some new stuff too ]
As some other people already pointed out, PostGIS probably has a direct
solution for this. However, you could solve it without PostGIS using
rtree indexes.
Here's an example that shows how to find all the points contained within
a given bounding box using an rtree index. For some reason there is not
an rtree opclass for "point"; but there is one for "box", so we promote
the points into boxes of width and height zero.
regression=# create table pts (f1 int, f2 point);
CREATE
regression=# create index ptsi on pts using rtree(box(f2,f2));
CREATE
regression=# insert into pts values (1, '0,0');
INSERT 147648 1
regression=# insert into pts values (2, '1,1');
INSERT 147649 1
regression=# insert into pts values (3, '2,1');
INSERT 147650 1
regression=# insert into pts values (4, '12,1');
INSERT 147651 1
-- now find f2 points contained in the bounding box (1,0),(2,2)
regression=# select * from pts where box(f2,f2) @ '1,0,2,2'::box;
f1 | f2
----+-------
2 | (1,1)
3 | (2,1)
(2 rows)
regression=# explain select * from pts where box(f2,f2) @ '1,0,2,2'::box;
NOTICE: QUERY PLAN:
Index Scan using ptsi on pts (cost=0.00..4.83 rows=1 width=20)
EXPLAIN
So, given an index constructed this way, you could compute the minimum
and maximum latitude and longitude that a point could have and still
fall within the desired distance of your start point. Then use the
index to pull out the points within that "box", and finally do the
expensive exact-distance calculation for just these points.
regards, tom lane
