Re: Box query
Date: Sun, 23 Apr 2006 18:58:00 GMT
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
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 - 20:58:00 CEST