Thread: Geometric operators
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? -- Best Regards, Steffen Zimmert Steffen Zimmert Fraunhofer Center for Research in Computer Graphics Providence, Rhode Island, USA email: szimmert@crcg.edu
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. If you read my question from earlier today, you'll notice i'm having problems with it in 6.5. I don't know if that's just me or something that's changed since 6.4.2.
selkovjr.mcs.anl.gov@mcs.anl.gov wrote:
> 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.
you are, of course, correct. there are probably more operators in there
than anybody would actually use. i noticed the docs on 6.5 have a lot
of "?" by the descriptions of geometric operators. does this mean that
nobody actually knows how this stuff works?
>
> 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.
i think it's a bit more readable to put all of that extra stuff in
there, but then again, i don't like putting the opening { on its own
line in C code, for similar reasons. i guess i'm just a rebel.
btw, does anyone have any hints as to why the r-tree indexes aren't
working for me in 6.5? i sent a message about it earlier today and
i've been poking around when i've had time, but i haven't figured it out
yet.
jeff
> 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
Hi, Jeff Hoffmann wrote: > > selkovjr.mcs.anl.gov@mcs.anl.gov wrote: > > 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. Thank you very much, I tried your examples and suggestions using the '@' operator and PostgreSQL does exactly what I want it do! ;)) Besides, does anybody have some experience with the JDBC driver? My application should connect to the database and store an object in together with a box object. Is it possible? Sorry about my silly questions but I am really new to the database stuff. -- Best Regards, Steffen Zimmert Steffen Zimmert Fraunhofer Center for Research in Computer Graphics Providence, Rhode Island, USA email: szimmert@crcg.edu
Jeff Hoffmann wrote:
> selkovjr.mcs.anl.gov@mcs.anl.gov wrote:
> > 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.
>
> you are, of course, correct. there are probably more operators in there
> than anybody would actually use. i noticed the docs on 6.5 have a lot
> of "?" by the descriptions of geometric operators.
If you mean those question marks you see at the end of some
descriptions, it appears to me that these are part of description
indicating the boolean type of the return value
> does this mean that
> nobody actually knows how this stuff works?
Let's say, few people do. Geometric applications are relatively rare,
so that part of the documentation does not usually get enough
attention. But postgres can speak for itself. One can find everything
about operators by:
(1) reading the sources
(2) making queries to the system catalogs
I wouldn't start from from the sources, though, because it's easy to
get lost there without knowing what to look for. System tables are
more transparent. Everything you need to know about operators is in
pg_operator. Types are in pg_type. Human language descriptions of
everything -- not only operators -- go to pg_description (which you
can view as a brief documentation). For example,
SELECT o.oid, o.oprname, l.typname, r.typname, d.description
FROM pg_operator o, pg_type l, pg_type r, pg_type result, pg_description d, pg_proc p
WHERE o.oprkind = 'b'
AND o.oprleft = l.oid
AND o.oprright = r.oid
AND o.oprresult = result.oid
AND l.typname ~ 'box|point|polygon|path|circle'
AND result.typname = 'bool'
AND regproctooid(o.oprcode) = p.oid
AND p.oid = d.objoid
ORDER BY o.oprname;
Now if that's not enough and you want to know how exactly it works,
look in the sources. If I wanted to know how this operator worked:
501|>= |box |box |greater-than-or-equal
I would first find out what function it is associated with:
test=> select oprcode from pg_operator where oid = 501;
oprcode
-------
box_ge
(1 row)
That gives me something to look for in the sources:
find /usr/src/pgsql/src/backend/ -name "*.[ch]" -exec grep -l box_ge {} \;
which gives
/usr/src/pgsql/src/backend/utils/adt/geo_ops.c
/usr/src/pgsql/src/backend/utils/fmgrtab.c
Go figure!
> btw, does anyone have any hints as to why the r-tree indexes aren't
> working for me in 6.5?
Any examples?
--Gene
Hi, I've been using postgresql 6.3.2 with Apache Modperl Eperl for quite some time without any problems whatesoever. These few days however, the postmaster seems to die and the web site is unaccessible until I manually restart it. I would like to know why and when it died. Can anyone tell me any known problems? Buggy codes to look out for etc? And where does postgresql keep its logs Any remedies? Please help as it's urgent. Thanks Martin