# Re: Box query

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sun, 23 Apr 2006 13:22:29 GMT

```> J M Davitt wrote:
>
```

>> Mikito Harakiri wrote:
>> [snip]
>>
>>>
>>> To add more, there is a next question that I was going to ask: "find
>>> the intersection volume". And your query even have the correct "group
>>> by" clause in place. I'm really puzzled if you already knew the answer,
>>> or put "group by" incidentally instead of "distinct":-)
>>
>>
>>
>> Wrapping a view declaration around Bob's excellent query
>>
>> create view intersects as
>> select b1.id as box1
>> , b2.id as box2
>> from boxes b1
>> , boxes b2
>> where 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 1, 2 ;
>>
>> and creating one which hauls out the lengths of the intersected spaces
>>
>> create view intersections as
>> select box1
>> , box2
>> , dim
>> , case when one.low < two.low
>> then two.low else one.low end as lo
>> , case when one.high > two.high
>> then two.high else one.high end as hi
>> from ( select box1
>> , box2
>> , dim
>> , low
>> , high
>> from boxes
>> join ( select box1
>> , box2
>> from intersects ) isects on (id = box1) ) one
>> join ( select box1
>> , box2
>> , dim
>> , low
>> , high
>> from boxes
>> join ( select box1
>> , box2
>> from intersects ) isects on (id = box2) ) two
>> using (box1, box2, dim) ;
>>
>> select box1
>> , box2
>> , x.len * y.len * z.len as volume
>> from ( select box1
>> , box2
>> , hi - lo as len
>> from intersections
>> where 'x' = dim ) x
>> join ( select box1
>> , box2
>> , hi - lo as len
>> from intersections
>> where 'y' = dim ) y using (box1, box2)
>> join ( select box1
>> , box2
>> , hi - lo as len
>> from intersections
>> where 'z' = dim ) z using (box1, box2) ;
>>
>> I believe this gives volumes:
```>
>
> I don't know whether it does, but it seems rather complex compared to
> using exp, ln and sum as Mikito's solution does. His solution makes the
> volume easier to express because he orders box1 and box2 by low instead
> of by id. That would seem to make my query somewhat less excellent vis a
> vis his.

```

I agree. We would be happier with something like this:

select box1

```  ,       box2
,       product(hi - lo) as volume
```

from intersections
group by 1, 2 ;

So I wrote one... an aggregate PRODUCT(), that is.

But, you know, a truck-load of other issues crop up.

```1. It ain't SQL any more
2. I can do this in Teradata or postgres; I'm not sure about others.
3. Even in the systems in which I can do this, I can see no way to
```

avoid using "magic numbers" - using the provided "high level"     languages - and "code-in" a potential flaw and maintain SQL-like     behavior. (It has to do with NULL wrangling.) 4. Being SQL-like, I see no way to return lengths, areas, and

volumes - like "inches", "square inches," "cubic inches," &c. -     from this UDF. Received on Sun Apr 23 2006 - 15:22:29 CEST

Original text of this message