Re: Box query
Date: Sat, 22 Apr 2006 19:22:29 GMT
Message-ID: <Vnv2g.11883$P2.5707_at_tornado.ohiordc.rr.com>
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.
Certainly, we could generalize the solution to provide for points on lines, lines intersecting lines and spaces, etc. But, before doing so, I would want to come up with better representations of the things to be stored and operated on than "three rows labeled with the same id and three dimensions define a cube." As presented, only one of the regular polyhedra had to be handled. If we want more than that, it's a different problem. Received on Sat Apr 22 2006 - 21:22:29 CEST