Re: Box query

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 22 Apr 2006 18:33:03 GMT
Message-ID: <zFu2g.64284$VV4.1208052_at_ursa-nb00s0.nbnet.nb.ca>

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
; Received on Sat Apr 22 2006 - 20:33:03 CEST