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?

.

Relevant Pages

  • Re: Box query
    ... Mikito Harakiri wrote: ... Which naturally leads to one more intersection query: ... from boxes b1, boxes b2 ...
    (comp.databases.theory)
  • Re: Box query
    ... Mikito Harakiri wrote: ... Which naturally leads to one more intersection query: ... from boxes b1, boxes b2 ...
    (comp.databases.theory)
  • Re: DoCmd.OpenReport - WhereCondition
    ... I have three combo boxes so there are 8 possible where clauses. ... >>> Dim strWhere as String ... >>> The strWhere clause goes in the Where clause argument, ... > Show a message requesting an entry? ...
    (microsoft.public.access.reports)
  • Re: Trouble with a multiple parameter search
    ... the code will produce a SQL statement that only has 2 phrases in the ... WHERE clause instead of 14 like the one you posted. ... The criteria boxes go in the Form Header section, ... The final trailing " AND " is then chopped off, and the string is added to ...
    (microsoft.public.access.forms)
  • Re: EMails going AWOL
    ... the leads and found that the boxes you sent me to were unchecked, ... Packard Bell recovery disc to go back to zero to clear everything and start ... were in the boxes before the recovery are not there - everything I had sent ... You should see an option to leave copy on server for X days or until I ...
    (microsoft.public.windows.vista.mail)