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

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

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