Re: Box query
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Apr 2006 19:39:06 GMT
Message-ID: <uDv2g.64311$VV4.1208721_at_ursa-nb00s0.nbnet.nb.ca>
>> Bob Badour wrote:
>>
>>> 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?
Date: Sat, 22 Apr 2006 19:39:06 GMT
Message-ID: <uDv2g.64311$VV4.1208721_at_ursa-nb00s0.nbnet.nb.ca>
J M Davitt wrote:
> Bob Badour wrote: >
>> Bob Badour wrote:
>>
>>> 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?
> > Well, I don't like it very much, either; the approach seems rather > Celkoesque to me -- but it's a solution consistent with the problem > as stated. > > Regarding the "What if the box has 6 dimensions? Or only 2 dimensions?" > Well, six dimensions would imply something other than "volume" in the > three-dimensional space we're familiar with.
The original problem statement said n-dimensional boxes. I assumed that "volume" was a shorthand meaning area or length for n < 3 and some sort of hyper-volume for n > 3.
In fact, I took the relation to represent a system of inequalities. Otherwise, boxes of different degree could never intersect, and of course, the queries we were giving would allow them to. Received on Sat Apr 22 2006 - 21:39:06 CEST