Hello,
I would like to test the containment of a point against many boxes.
I did not find a way to express "box @> point" in straightforward way such that the GiST index on the boxes is exploited.
The only way to use a point directly is to turn the box into a polygon.
Is it a missing feature?
The way I currently represent a point p is as: box(p, p). In this case, the GiST index use kicks in.
Regards,
Ralf
--
drop table if exists boxes cascade;
create table boxes (
b box
);
-- Some random data
insert into boxes
select box(point((random()*100)::int, (random()*100)::int),
point((random()*100)::int, (random()*100)::int))
from (select * from generate_series(1,1000)) as t;
create index i on boxes using gist (b);
vacuum analyze boxes;
explain select * from boxes where b @> '((0,0),(0,0))'::box;
explain select * from boxes where b::polygon @> '(0,0)'::point;
RESULT:
QUERY PLAN
----------------------------------------------------------------
Index Scan using i on boxes (cost=0.00..8.27 rows=1 width=32)
Index Cond: (b @> '(0,0),(0,0)'::box)
(2 rows)
QUERY PLAN
---------------------------------------------------------
Seq Scan on boxes (cost=0.00..23.00 rows=500 width=32)
Filter: ((b)::polygon @> '(0,0)'::point)
(2 rows)
