Re: Box query

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

(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