[postgis-users] 3d box intersection queries

[Matthias]

Am 16.04.2011, 06:12 Uhr, schrieb Matthias <nitro at dr-code.org>:

> Hello,
>
> My entities are defined as boxes in 3d. Now I want to be able to query  
> all entities within a certain (bigger) 3d bounding box. What's the  
> recommended way to do this with PostGIS? Usually I'd just use an r-tree  
> for this task, but I want to use it within the context of a database.
>
> For a start I downloaded the postgis 2.0 windows experimental binaries  
> and performed a query like
>
> SELECT ST_3DIntersects(box1, box2) FROM (SELECT
>
> ST_GeomFromEWKT('POLYHEDRALSURFACE(
> ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
> ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
> ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
> ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
> ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),
> ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )') AS box1,
>
> ST_GeomFromEWKT('POLYHEDRALSURFACE(
> ((2 0 0, 2 0 1, 2 1 1, 2 1 0, 2 0 0)),
> ((2 0 0, 2 1 0, 3 1 0, 3 0 0, 2 0 0)),
> ((2 0 0, 3 0 0, 3 0 1, 2 0 1, 2 0 0)),
> ((3 1 0, 3 1 1, 3 0 1, 3 0 0, 3 1 0)),
> ((2 1 0, 2 1 1, 3 1 1, 3 1 0, 2 1 0)),
> ((2 0 1, 3 0 1, 3 1 1, 2 1 1, 2 0 1)) )') AS box2
>
> ) as foo;
>
> which test if two cubes intersect.
>
> Now the polyhedrastuff looks a bit like overkill for simple 3d boxes. Is  
> there a better/faster/memory saving way to do this?

As a followup, I found the box3d type. However, this query

SELECT ST_3DIntersects('BOX3D(0 0 0, 1 1 1)'::box3d, 'BOX3D(0 0 2, 1 1  
3)'::box3d)

returns "true" while the result should really be false. I suspect the  
box3d is implicitly converted to a 2d polygon and then the intersection  
test is performed. This way it will return "true".

Any idea how to do a simple 3d bounding box intersection test?

-Matthias