From: | selkovjr(dot)mcs(dot)anl(dot)gov(at)mcs(dot)anl(dot)gov |
---|---|
To: | Jeff Hoffmann <jeff(at)remapcorp(dot)com>, pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Geometric operators |
Date: | 1999-06-18 18:15:53 |
Message-ID: | 199906181913.OAA22611@antares.mcs.anl.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Steffen Zimmert | 1999-06-18 18:48:28 | Geometric operators |
Previous Message | Jeff Hoffmann | 1999-06-18 16:03:20 | Re: [GENERAL] Geometric operators |