# Re: Box query

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sat, 22 Apr 2006 17:25:40 GMT

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":-)

```

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 - 19:25:40 CEST

Original text of this message