Re: Box query
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Apr 2006 23:48:24 GMT
Message-ID: <chz2g.64422$VV4.1211731_at_ursa-nb00s0.nbnet.nb.ca>
>>Mikito Harakiri wrote:
>>
>>
>>>Mikito Harakiri wrote:
>>>
>>>
>>>>The next question that I was going to ask: "find the intersection volume".
>>>
>>>
>>>Which naturally leads to one more intersection query:
>>>
>>>select b1.id as box1, b2.id as box2
>>>from boxes b1, boxes b2
>>>where b1.dim = b2.dim
>>>group by b1.id, b2.id
>>>having 0.1 < exp(sum(ln(
>>> case when b2.low between b1.low and b1.high then b1.high-b2.low
>>>else 0.00000001 end
>>>)))
>>>;
>>>
>>>This doesn't look like relational division at all...
>>>
>>
>>Mikito,
>>
>>I don't think that query will give the volumes even if one moves the
>>volume expression into the select list:
>>
>>select b1.1d as box1, b2.id as box2, exp(sum(ln(...)))
>>...
>>
>>
>>Let boxes = { (id, dim, low, high ) |
>> (1,x,0,3), (1,y,1,3),
>> (2,x,1,3), (2,y,0,3),
>>}
>>
>>The result before grouping will be:
>>
>>Let tmp = { (box1,low1,high1,box2,low2,high2, ln(...)) |
>> (1,0,3,1,0,3,ln(3)), (1,0,3,2,1,3,ln(3)),
>> (1,1,3,1,1,3,ln(2)), (1,1,3,2,0,3,ln(1e-8)),
>> (2,1,3,1,0,3,ln(1e-8)), (2,1,3,2,1,3,ln(2)),
>> (2,0,3,1,1,3,ln(3)), (2,0,3,2,0,3,ln(3))
>>}
>>
>>The result after grouping and before having will be:
>>
>>Let tmp1 = { (box1,box2,exp(sum(ln(...)))) |
>> (1,1,6), (1,2,3e-8),
>> (2,1,3e-8), (2,2,6)
>>}
>>
>>The final result will be:
>>
>>Let rslt = { (box1,box2,exp(sum(ln(...)))) |
>> (1,1,6), (2,2,6)
>>}
>>
>>I would have thought from your original description that you would want
>>something like:
>>
>>{ (box1,box2,size_isect) |
>> (1,2,4)
>>}
>>
>>Combining our queries, wouldn't it have to be something like the following?
>>
>>select b1.id as box1, b2.id as box2
>>, case when min( b2.high-b2.low ) = 0 or min(b1.high-b2.low) = 0
>> then
>> 0
>> else
>> exp(sum(ln(
>> case when b2.high < b1.high
>> then
>> b2.high-b2.low
>> else
>> b1.high-b2.low
>> end
>> )))
>> end as size_isect
>>from boxes b1 join boxes b2 (using dim)
>>where b2.low between b1.low and b1.high
>>and (b2.low > b1.low or 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 b1.id, b2.id
>>;
Date: Sat, 22 Apr 2006 23:48:24 GMT
Message-ID: <chz2g.64422$VV4.1211731_at_ursa-nb00s0.nbnet.nb.ca>
Mikito Harakiri wrote:
> Bob Badour wrote: >
>>Mikito Harakiri wrote:
>>
>>
>>>Mikito Harakiri wrote:
>>>
>>>
>>>>The next question that I was going to ask: "find the intersection volume".
>>>
>>>
>>>Which naturally leads to one more intersection query:
>>>
>>>select b1.id as box1, b2.id as box2
>>>from boxes b1, boxes b2
>>>where b1.dim = b2.dim
>>>group by b1.id, b2.id
>>>having 0.1 < exp(sum(ln(
>>> case when b2.low between b1.low and b1.high then b1.high-b2.low
>>>else 0.00000001 end
>>>)))
>>>;
>>>
>>>This doesn't look like relational division at all...
>>>
>>
>>Mikito,
>>
>>I don't think that query will give the volumes even if one moves the
>>volume expression into the select list:
>>
>>select b1.1d as box1, b2.id as box2, exp(sum(ln(...)))
>>...
>>
>>
>>Let boxes = { (id, dim, low, high ) |
>> (1,x,0,3), (1,y,1,3),
>> (2,x,1,3), (2,y,0,3),
>>}
>>
>>The result before grouping will be:
>>
>>Let tmp = { (box1,low1,high1,box2,low2,high2, ln(...)) |
>> (1,0,3,1,0,3,ln(3)), (1,0,3,2,1,3,ln(3)),
>> (1,1,3,1,1,3,ln(2)), (1,1,3,2,0,3,ln(1e-8)),
>> (2,1,3,1,0,3,ln(1e-8)), (2,1,3,2,1,3,ln(2)),
>> (2,0,3,1,1,3,ln(3)), (2,0,3,2,0,3,ln(3))
>>}
>>
>>The result after grouping and before having will be:
>>
>>Let tmp1 = { (box1,box2,exp(sum(ln(...)))) |
>> (1,1,6), (1,2,3e-8),
>> (2,1,3e-8), (2,2,6)
>>}
>>
>>The final result will be:
>>
>>Let rslt = { (box1,box2,exp(sum(ln(...)))) |
>> (1,1,6), (2,2,6)
>>}
>>
>>I would have thought from your original description that you would want
>>something like:
>>
>>{ (box1,box2,size_isect) |
>> (1,2,4)
>>}
>>
>>Combining our queries, wouldn't it have to be something like the following?
>>
>>select b1.id as box1, b2.id as box2
>>, case when min( b2.high-b2.low ) = 0 or min(b1.high-b2.low) = 0
>> then
>> 0
>> else
>> exp(sum(ln(
>> case when b2.high < b1.high
>> then
>> b2.high-b2.low
>> else
>> b1.high-b2.low
>> end
>> )))
>> end as size_isect
>>from boxes b1 join boxes b2 (using dim)
>>where b2.low between b1.low and b1.high
>>and (b2.low > b1.low or 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 b1.id, b2.id
>>;
> > > Yes there were several gaps with the volume solution. First, let's > clarify some things that I thought were obvious for the reader, but > from the parallel discussions I noticed they are not. > > 1. All the boxes are assumed to be of the same dimension. They have to > be called hyperboxes to avoid confusion. > 2. The intersection volume is hypervolume, then. > > Next, exp(sum(ln(...)))) is a poor man's substitution for the product > aggregate. It doesn't work zero and negative numbers. Hence the > workaround with creepy constants like 0.00001. Negative values can be > avoided with abs() or with another case operator. Finally, we don't > really need to know the volume in the having clause, just multiplying > 0s and s1 would do. Therefore, the amended having clause is: > > having 0.00001 < exp(sum(ln( > case when b2.low between b1.low and b1.high then 1 > else 0.00001 end > ))) > > But based on your reply, perhaps > > having > case when min( b2.high-b2.low ) = 0 or min(b1.high-b2.low) = 0 > then 0 else 1 end = 1 > > is even simpler condition?
It doesn't have the correct intersection volume of (1,2,4), which is what I thought the query was supposed to return. And if one has two intersecting hyperboxes that abutt, one could argue that it should return a row indicating a zero-volume intersection. Your having clause will simply remove the result. Received on Sun Apr 23 2006 - 01:48:24 CEST