Re: Box query

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sun, 23 Apr 2006 18:58:00 GMT
Message-ID: <Y6Q2g.64720$VV4.1225841@ursa-nb00s0.nbnet.nb.ca>

Mikito Harakiri wrote:

> Mikito Harakiri wrote:
>

>>select b1.id as box1, b2.id as box2
>>exp(sum(ln(
>>       case when b2.low between b1.low and b1.high then
>>b1.high-b2.low+0.00000001
>>else case when b1.low between b2.low and b2.high then
>>b2.high-b1.low+0.00000001
>>else 0.00000001 end end))) product
>>from boxes b1, boxes b2
>>where b1.dim = b2.dim
>>group by b1.id, b2.id

>
>
> Still buggy. What if b2 is enclosed in b1? Clearly there are 4
> possibilities, not just 2:
>
> 1. b1.high-b2.low
> 2. b2.high-b1.low
> 3. b2.high-b2.low
> 4. b1.high-b1.low
>
> I hate case analysis...
>
> (This two more cases matter for the correct calculation of the
> intersection volume. For surrogate case of 0s and 1s where we just
> evaluate if the two intervals intersect the 2 cases don't have to be
> refined).

I eliminated two of the cases by restricting to the higher of the two lows. But then, I had to disambiguate the case when the lows equal. So, I would say it was about a washout.

One could hide the case analysis entirely if one had two "min" and "max" operators. One as a summary operation and the other simply as a comparison operator. Then the interval is the min of the highs minus the max of the lows. Received on Sun Apr 23 2006 - 13:58:00 CDT