sfcgal ST_3DIntersects hangs on this query

[robe]

I built a 3d building table using ST_Extrude and slapped on a 3D index.

CREATE INDEX idx_sfo_3dbuilds_geom
   ON data.sfo_3dbuildings USING gist(geom gist_geometry_ops_nd);

When I do a query like this:

set postgis.backend=geos;
WITH drone_path AS (SELECT ST_GeomFromText('LINESTRING Z (6011567 2117852 12,6011567 2117860 50, 
    6011765 2117429 20, 6011765 2117500 100,  6011765 2118500 300, 6011800 2119500 30, 6012800 2129500 150)',2227) As geom)
SELECT b.gid
FROM data.sfo_3dbuildings As b INNER JOIN drone_path As d ON ST_3DIntersects(b.geom, d.geom);

I get this answer:

 gid
------
 4354
 4367
 4379
 4429
 4449
 4451
 4509
 4612
 4836
 4842

Which seems like a fairly respectible answer when I view it in my x3dviewer - see attached image. Granted its a very messed up drone path I have going on.

set postgis.backend=sfcgal;

When I switch to use sfcgal my postgres server goes into memory death and I have to kill the connection.

Dataset and image is attached (buildings in red are those the native ST_3DIntersects marks as collision targets)

[robe]

Where are my manners - forgot to output version details:

POSTGIS="2.2.0dev r13298" GEOS="3.5.0dev-CAPI-1.9.0 r4048" SFCGAL="1.0.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="0.12" RASTER PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit

[robe]

Sadly this issue still exists with:

POSTGIS="2.2.0dev r13966" GEOS="3.5.0-CAPI-1.9.0 r4088" SFCGAL="1.1.0" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.0, released 2015/06/14" LIBXML="2.7.8" LIBJSON="0.12" RASTER

I built with boost 1.59.0 and CGAL 4.6.1

[robe]

As I recall I think this is still an issue even in SFCGAL 1.2. This I feel is probably an SFCGAL issue and not how it connects to PostGIS so changing to SFCGAL.

[hmercier]

Tested with:

POSTGIS="3.0.0dev r16844" [EXTENSION] PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" SFCGAL="1.3.5" PROJ="Rel. 5.1.0, June 1st, 2018" LIBXML="2.9.4" LIBJSON="0.12.1"

No crash. It gives me the same result than with the GEOS backend (after more time though …)

[nicklas]

Bara för att det inte skall bli förvirring. ST_3DIntersects finns inte i GEOS. Det är native PostGIS, eller SFCgal som är de 2 alternativen.

Är PostGIS native snabbare? Kul i sådana fall

[hmercier]

@nicklas, please use english here.

(Google translation) Just because there will not be confusion. ST_3DIntersects are not available in GEOS. It is native PostGIS, or SFCgal which are the 2 options. Is PostGIS native faster? Fun in such cases

Yes, the "GEOS" backend implements intersects3d by lwgeom_mindistance3d_tolerance()

[nicklas]

Ouch, sorry. Brain fart here. I had to recheck if I really had written it in Swedish, couldn't believe it. Good Google translate made a decent job here.

Yes, lwgeom_mindistance3d_tolerance is found in the PostGIS code base here ​https://trac.osgeo.org/postgis/browser/trunk/liblwgeom/measures3d.c

[komzpa]

SFCGAL 3DIntersects removed from PostGIS 3.