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(at)spy(dot)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. ____________