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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.