Re: Box query
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Apr 2006 17:50:30 GMT
Message-ID: <G1u2g.64253$VV4.1207653_at_ursa-nb00s0.nbnet.nb.ca>
>
>
> 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:
Date: Sat, 22 Apr 2006 17:50:30 GMT
Message-ID: <G1u2g.64253$VV4.1207653_at_ursa-nb00s0.nbnet.nb.ca>
> 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:
I don't know whether it does, but it seems rather complex compared to using exp, ln and sum as Mikito's solution does. His solution makes the volume easier to express because he orders box1 and box2 by low instead of by id. That would seem to make my query somewhat less excellent vis a vis his. Received on Sat Apr 22 2006 - 19:50:30 CEST