Re: [GENERAL] Geometric operators - Mailing list pgsql-general

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