Re: Box query

Bob Badour wrote:
> Mikito Harakiri wrote:
> > Anyway, the condition if the two intervals [b1.low,b1.high] and
> > [b2.low,b2.high] intersect (for each dimension) is a simple expression
> > that depends on 4 numbers. Kind of obvious, isn't it? All what is
> > needed is aggregating it with the product aggregate -- which
> > corresponds to informal idea of the intersection volume. Alternatively,
> > we operate with 1s and 0s only, and could use boolean conjunction,
> > which is arithmetic min aggregate.
>
> How do those 1's get you your volume? For instance, the volume of box 1
> intersected with itself is 6 not 1. The volume of box 1 intersected with
> box 2 is 4 not 1.

The discussion was whether we can write intersection query without any subquery at all. The chain of reasoning was:

"Find intersections" -> "Generalize the query and find intersection volume" ->"But we really dont need volume and can use a surrogate product of 1s and 0s instead in the having clause".

For volume query we have to return to the original idea, but fix the symmetry bug! The amended volume query doesn't need any restriction at all (except matching dimensions):

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

0.00000001 is supposed to be the smallest number supported in the domain of reals. Received on Sun Apr 23 2006 - 18:17:44 CEST