Thread

1. Re: [GENERAL] Geometric operators

   selkovjr.mcs.anl.gov@mcs.anl.gov — 1999-06-18T18:15:53Z

   > 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
   
   

2. Re: [GENERAL] Geometric operators

   Jeff Hoffmann <jeff@remapcorp.com> — 1999-06-18T19:35:28Z

   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
   
   

3. Re: [GENERAL] Geometric operators

   Steffen Zimmert <szimmert@crcg.edu> — 1999-06-19T01:17:37Z

   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