Re: Box query
Date: Sat, 22 Apr 2006 17:25:40 GMT
Message-ID: <oGt2g.6469$YI5.559_at_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
and creating one which hauls out the lengths of the intersected spaces
create view intersections as
select box1
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
group by 1, 2 ;
, 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 ) )
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
using (box1, box2, dim) ;
, 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
, box2
, x.len * y.len * z.len as volume
from ( select box1
where 'x' = dim ) x
join ( select box1
, box2
, hi - lo as len
from intersections
, 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 - 19:25:40 CEST