2

I'm trying to combine a bunch of geometry boundaries to get the linework of their borders, but I'm getting an error with the default, GEOS-based ST_Union:

SELECT ST_Union(ST_Boundary(geom)) FROM my_table;

gives

ERROR: GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING (391229 4.79279e+06, 391444 4.79279e+06) and LINESTRING (391444 4.79279e+06, 391229 4.79279e+06) at 391437.43387050979 4792789.729486906

According to the PostGIS documentation, SFCGAL has a version of ST_Union that I can use:

Some SFCGAL functions replace standard ones (ST_Intersects, ST_Intersection, ST_Difference, ST_Union, ST_Area and ST_Distance), to switch between standard functions and SFCGAL function use:

SET postgis.backend = sfcgal;

However, this has no effect:

SET postgis.backend = sfcgal;
SHOW postgis.backend;
SELECT ST_Union(ST_Boundary(geom)) FROM my_table;
0 Rows Affected

postgis_backend
----------------
sfcgal

ERROR:  GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING (391229 4.79279e+06, 391444 4.79279e+06) and LINESTRING (391444 4.79279e+06, 391229 4.79279e+06) at 391437.43387050979 4792789.729486906

Based on the fact I'm still getting a GEOS error, I'm presuming it's not actually using the SFCGAL function.

The array version of ST_Union produces the same result:

SELECT ST_Union(ARRAY_AGG(ST_Boundary(geom))) FROM my_table;

How can I get PostGIS to invoke the SFCGAL version of ST_Union?


Note that I do have SFCGAL installed:

SELECT postgis_full_version();
POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="100" GEOS="3.7.0-CAPI-1.11.0 673b9939" SFCGAL="1.2.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" RASTER
SELECT * FROM pg_catalog.pg_extension WHERE extname LIKE '%sfcgal%';
extname        | extversion | extconfig
---------------+------------+-----------
postgis_sfcgal | 2.5.1      | NULL

PostgreSQL Version:

SELECT version();
PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

Aside: I don't know if the SFCGAL function will help with my error, and that's not really the topic of this question. I just want to try using to see if it does or not.

  • This is very odd. I understood that the sfcgal versions were to add the 3D versions of these functions, however, having looked at the source code, I can see that both 2D and 3D versions are supported. Irrespective, such errors are usually caused by spurious decimal places and can often be fixed by using ST_SnapToGrid on the geometry before ST_Union is called. – John Powell Dec 17 '18 at 18:21
  • @JohnPowell Yeah, I've tried all manner of things to fix these errors. Snapping, grid snapping, buffer/reverse buffer to remove tiny protrusions/insets. (I was told that my data is a mess and should be cleaned up, but unfortunately, neither my company nor our client has the resources to do that.) Even e-mailed the PostGIS mailing list some years back. Never could get a reliable answer. I have some really nasty code that dumps out all the line segments and tries to stitch them together one at a time, but it's not reliably producing the correct result. – jpmc26 Dec 17 '18 at 18:35
  • How much data do you have? I don't want to sounds preachy, but, perhaps it might be a good idea to figure out what the problem with your data are, garbage in/ garbage out, and all that :D. I would be happy to have a quick look, if you could share enough data to replicate the problem? – John Powell Dec 17 '18 at 18:42

Your Answer

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

Browse other questions tagged or ask your own question.