Re: [GENERAL] Geometric operators

> Steffen Zimmert wrote:
> >
> > Hello everybody,
> >
> > I am wondering if the geometric datatypes of the PostgreSQL system allow
> > the following queries.
> > The database should contain the box datatype which is used as the index.
> > The system should allow queries like "Retrieve all boxes that are
> > contained in the query box". Is that possible with the standard types of
> > the system?
>
> It should be working (at least it worked fine in 6.4.2).  The operator
> you are looking for is "&&" which is a box overlap. For example, if you
> create a table with a box field (we'll call it "box_field"), you could
> create an index on it (if you have a lot of records):
>
> create index mytable_index on my_table using rtree (box_field box_ops);
>
> and then a select would be
>
> select * from mytable where box_field && '(100,100),(200,200)'::box;
>
> where the '(100,100),(200,200)'::box would be the bounding query box.

That is not exactly so, if I may. '&&' is, like Steffen has already
mentioned, an operator for overlap. What the original posting inquired
about was containment. There are two operators for that, '~' and
'@', with the meanings of 'contains' and 'contained', respectively.

As a side comment, you don't need type-casting for the box
constants -- they are coerced -- and you might as well omit
parentheses:

select * from mytable where box_field && '100,100,200,200';

unless you want to stay consistent with the way boxes represent
themselves on the output.


--Gene