Re: Box query
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Apr 2006 18:50:17 GMT
Message-ID: <JVu2g.64295$VV4.1208215_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:
Date: Sat, 22 Apr 2006 18:50:17 GMT
Message-ID: <JVu2g.64295$VV4.1208215_at_ursa-nb00s0.nbnet.nb.ca>
> J M Davitt wrote: >
>> 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.
Then again, I think I needed to combine mine and Mikito's to get it to work.
Looking a little closer at your solution, I really don't like the 'x'=dim stuff. What if the box has 6 dimensions? Or only 2 dimensions? Received on Sat Apr 22 2006 - 20:50:17 CEST