Re: Box query

Bob Badour wrote:
> Mikito Harakiri wrote:
>
> > Mikito Harakiri wrote:
> >
> >>The next question that I was going to ask: "find the intersection volume".
> >
> >
> > Which naturally leads to one more intersection query:
> >
> > select b1.id as box1, b2.id as box2
> > from boxes b1, boxes b2
> > where b1.dim = b2.dim
> > group by b1.id, b2.id
> > having 0.1 < exp(sum(ln(
> > case when b2.low between b1.low and b1.high then b1.high-b2.low
> > else 0.00000001 end
> > )))
> > ;
> >
> > This doesn't look like relational division at all...
> >
>
> Mikito,
>
> I don't think that query will give the volumes even if one moves the
> volume expression into the select list:
>
> select b1.1d as box1, b2.id as box2, exp(sum(ln(...)))
> ...
>
>
> Let boxes = { (id, dim, low, high ) |
> (1,x,0,3), (1,y,1,3),
> (2,x,1,3), (2,y,0,3),
> }
>
> The result before grouping will be:
>
> Let tmp = { (box1,low1,high1,box2,low2,high2, ln(...)) |
> (1,0,3,1,0,3,ln(3)), (1,0,3,2,1,3,ln(3)),
> (1,1,3,1,1,3,ln(2)), (1,1,3,2,0,3,ln(1e-8)),
> (2,1,3,1,0,3,ln(1e-8)), (2,1,3,2,1,3,ln(2)),
> (2,0,3,1,1,3,ln(3)), (2,0,3,2,0,3,ln(3))
> }
>
> The result after grouping and before having will be:
>
> Let tmp1 = { (box1,box2,exp(sum(ln(...)))) |
> (1,1,6), (1,2,3e-8),
> (2,1,3e-8), (2,2,6)
> }
>
> The final result will be:
>
> Let rslt = { (box1,box2,exp(sum(ln(...)))) |
> (1,1,6), (2,2,6)
> }
>
> I would have thought from your original description that you would want
> something like:
>
> { (box1,box2,size_isect) |
> (1,2,4)
> }
>
> Combining our queries, wouldn't it have to be something like the following?
>
> select b1.id as box1, b2.id as box2
> , case when min( b2.high-b2.low ) = 0 or min(b1.high-b2.low) = 0
> then
> 0
> else
> exp(sum(ln(
> case when b2.high < b1.high
> then
> b2.high-b2.low
> else
> b1.high-b2.low
> end
> )))
> end as size_isect
> from boxes b1 join boxes b2 (using dim)
> where b2.low between b1.low and b1.high
> and (b2.low > b1.low or b1.id < b2.id)
> and not exists (
> select 1
> from boxes b3, boxes b4
> where b3.id = b1.id
> and b4.id = b2.id
> and b4.dim = b3.dim
> and ( b4.high < b3.low or b4.low > b3.high )
> )
> group by b1.id, b2.id
> ;

Yes there were several gaps with the volume solution. First, let's clarify some things that I thought were obvious for the reader, but from the parallel discussions I noticed they are not.

1. All the boxes are assumed to be of the same dimension. They have to be called hyperboxes to avoid confusion.
2. The intersection volume is hypervolume, then.

Next, exp(sum(ln(...)))) is a poor man's substitution for the product aggregate. It doesn't work zero and negative numbers. Hence the workaround with creepy constants like 0.00001. Negative values can be avoided with abs() or with another case operator. Finally, we don't really need to know the volume in the having clause, just multiplying 0s and s1 would do. Therefore, the amended having clause is:

having 0.00001 < exp(sum(ln(

       case when b2.low between b1.low and b1.high then 1 else 0.00001 end
)))

But based on your reply, perhaps

having

   case when min( b2.high-b2.low ) = 0 or min(b1.high-b2.low) = 0    then 0 else 1 end = 1

is even simpler condition? Received on Sun Apr 23 2006 - 01:33:31 CEST