Re: index on a box

Around 17:21 on Jun 21, 2001, Tom Lane said:

# Never mind, it wouldn't work anyway.
#
# The problem is that "point @ box" isn't an rtree-indexable operation.
# You could use "box @ box" instead, where the lefthand box is a
# zero-area box with all corners at the point of interest.  Crufty, but
# unless you want to go in and teach rtree about a new operator type...

select * from tmp where box(point(-121,37),point(-121,37)) @ b;

Index Scan using tmp_bybox on tmp  (cost=0.00..238.59 rows=61 width=92)

    Hmm...  That's interesting.  It seems that point @ box would be
more generally useful than box @ box.  Then again, I've only used this for
this one particular task I'm doing right now.  :)  It seems that rtree
already knows how to do what I'm trying to do if all I've got to do is
make a box containing the point twice to get the lookup to be fast.

    If anyone's interested in what I'm doing with this, you can see it
in action here:

http://bleu.west.spy.net/~dustin/geo/pointinfoform.jsp

    I've loaded about 60k polygons (consisting of a total of about
seven million points) describing the shape of various geographical areas
in the United States.  I've got a table with the descriptions of the
polygons and box boundaries, then another table with the actual ordered
polygon data.  I do a box match on the first table to get a list of
candidates, then examine them all in more detail with a point-in-polygon
algorithm in my application.  It's currently pretty slow because I'm don't
actually have a box column on my first table, just the boundaries, which I
cast to a box and use point @ box(point(),point()) with a table scan.  It
looks like, when the index works, it'll be as fast as it was when I had
very little data in the tables again.  :)

    Thanks for the help!

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________