Re: Box query

From: Mikito Harakiri <>
Date: 23 Apr 2006 11:36:17 -0700
Message-ID: <>

Mikito Harakiri wrote:
> select as box1, 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,

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). Received on Sun Apr 23 2006 - 20:36:17 CEST

Original text of this message