Re: Box query
From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sat, 22 Apr 2006 19:50:40 GMT
Message-ID: <kOv2g.11887$P2.3440_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.
Date: Sat, 22 Apr 2006 19:50:40 GMT
Message-ID: <kOv2g.11887$P2.3440_at_tornado.ohiordc.rr.com>
Bob Badour wrote:
> 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.
Yes, I noted that, too, but everything else described a world where 'n = 3.' Just as we pondered, "Does share a point mean intersect?" I imagine we could also worry about 2D boxes without volume -- but it all seemed off-point to me.
> 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.
Well, there you go.
Regarding your "not exists (no overlap)" approach to this: I thought that was inspired and it got me to thinking: isn't that a universal quantifier? (It's been a long time since I've putzed with logic, and I'm feeling the urge to brush up.) Received on Sat Apr 22 2006 - 21:50:40 CEST