6

We are currently investigating PostGIS as an option to look up three-dimensional blocks which contain a point (and later also lines). At this point in time I cannot see any functionality that operates on the volume of a three dimensional shape, all functions PostGIS offers seem to operate on the surface.

Here are a number of functions we tried:

SELECT 
    ST_Contains(p , mpoly),      ST_Contains(p , ph),      ST_Contains(p , box),
    ST_3DIntersects(p , mpoly),  ST_3DIntersects(p , ph),  ST_3DIntersects(p , box),
    ST_3DDWithin(p , mpoly, 0),  ST_3DDWithin(p , ph, 0),  ST_3DDWithin(p , box, 0)
FROM
  (SELECT 
    'POINT Z (0.5 0.5 0.5)'::geometry AS p,
    'MULTIPOLYGON Z (
        ((0 0 0, 0 1 0, 1 1 0, 0 1 0, 0 0 0)),
        ((0 0 1, 0 1 1, 1 1 1, 0 1 1, 0 0 1)),
        ((0 0 0, 0 0 1, 0 1 1, 0 0 1, 0 0 0)),
        ((1 0 0, 1 0 1, 1 1 1, 1 0 1, 1 0 0)),
        ((0 0 0, 0 0 1, 1 0 1, 1 0 0, 0 0 0)),
        ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0))
    )'::geometry AS mpoly,
    'POLYHEDRALSURFACE (
        ((0 0 0, 0 1 0, 1 1 0, 0 1 0, 0 0 0)),
        ((0 0 1, 0 1 1, 1 1 1, 0 1 1, 0 0 1)),
        ((0 0 0, 0 0 1, 0 1 1, 0 0 1, 0 0 0)),
        ((1 0 0, 1 0 1, 1 1 1, 1 0 1, 1 0 0)),
        ((0 0 0, 0 0 1, 1 0 1, 1 0 0, 0 0 0)),
        ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0))
    )'::geometry AS ph,
    ST_3DMakeBox('POINT Z (0 0 0)'::geometry, 'POINT Z (1 1 1)'::geometry) AS box
  ) AS foo
;

All of these return false. Given that we are using polygons and polyhedral surfaces this seems correct, but is there a way to have PostGIS apply volumetric calculations?

  • Have you seen this question? Do you have sfcgal enabled? – John Powell May 19 '15 at 18:02
  • @JohnBarça: I had not seen that, thanks. And no: I do not have sfcgal enabled - it seems not part of the stable binaries. I'll investigate. – Peter Becker May 19 '15 at 23:14
  • I installed postgis-pg94-binaries-2.2.0devw64gcc48.zip from winnie.postgis.net/download/windows/pg94/buildbot which seems to have sfcgal support. At least I could install the extension and set the backend without error. But the query above still returns all false. – Peter Becker May 19 '15 at 23:36
  • And trying to use ST_3DIntersection runs into trac.osgeo.org/postgis/ticket/3072 – Peter Becker May 19 '15 at 23:48
  • Oh dear. Well at least it is a known bug. – John Powell May 20 '15 at 5:42
3

You could use the &&& operator. It returns true if two n-d bounding boxes intersect:

SELECT 'POINT Z (0.5 0.5 0.5)' &&& 'LINESTRING Z(0 0 0, 1 1 1)' AS intersects;

intersects 
------------
 t
(1 row)

SELECT 'POINT Z (1.5 0.5 0.5)' &&& 'LINESTRING Z(0 0 0, 1 1 1)' AS intersects;
intersects 
------------
 f
(1 row)

For both POINT Z and LINESTRING Z their bounding box is used. So this would only work without further tests for points. For arbitrary lines you could get false positives (the cases were only the bounding box of a line intersects, not the line itself). For this query you should use a n-d index, SFCGAL is not needed.

Alternatively, you could go with ST_3DDWithin and choose a query plane geometry along with a distance that would include your complete query bounding box. This, however, would also mean you would have to throw away the results that are outside of your original bounding box. For this query you should use a 2-d index and also don't need SFCGAL.

Edit: After some more testing and consulting the PostGIS mailing list, it seems best to actually use both methods described above together (using AND). This way &&& reduce the number of distance tests that ST_3DDWithin has to do.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.