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