Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Box query

Re: Box query

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sat, 22 Apr 2006 17:25:40 GMT
Message-ID: <oGt2g.6469$YI5.559@tornado.ohiordc.rr.com>


Mikito Harakiri wrote:
[snip]

> 
> To add more, there is a next question that I was going to ask: "find
> the intersection volume". And your query even have the correct "group
> by" clause in place. I'm really puzzled if you already knew the answer,
> or put "group by" incidentally instead of "distinct":-)

Wrapping a view declaration around Bob's excellent query

create view intersects as
select b1.id as box1
, b2.id as box2
from boxes b1
, boxes b2
where b1.id < b2.id
and not exists (

         select  1
         from    boxes b3

, boxes b4
where b3.id = b1.id and b4.id = b2.id and b4.dim = b3.dim and ( b4.high <= b3.low or b4.low >= b3.high ) )
group by 1, 2 ;

and creating one which hauls out the lengths of the intersected spaces

create view intersections as
select box1

,       box2
,       dim
, case  when one.low < two.low
         then two.low else one.low end as lo
, case  when one.high > two.high
         then two.high else one.high end as hi
from (  select  box1

, box2
, dim
, low
, high
from boxes join ( select box1 , box2 from intersects ) isects on (id = box1) ) one join ( select box1
, box2
, dim
, low
, high
from boxes join ( select box1 , box2 from intersects ) isects on (id = box2) ) two
using (box1, box2, dim) ;

select box1

,       box2
,       x.len * y.len * z.len as volume
from (  select  box1

, box2
, hi - lo as len
from intersections where 'x' = dim ) x join ( select box1
, box2
, hi - lo as len
from intersections where 'y' = dim ) y using (box1, box2) join ( select box1
, box2
, hi - lo as len
from intersections where 'z' = dim ) z using (box1, box2) ;

I believe this gives volumes: Received on Sat Apr 22 2006 - 12:25:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US