One of least liked things about PostGIS is that spatial analysis (like union, intersection, difference) can fail due to participating geometries that don’t comply with OGC validity rules. The new version of GEOS, the engine that powers PostGIS geoprocessing functions, provided a rewritten “OverlayNG” set of functions that promised a better robustness of geoprocessing. Finally, GEOS 3.9 and PostGIS 3.1 were released, and I decided to test those new versions to check if TopologyException is really gone.
Installation
As for now (January 2021), the PostGIS in pgdg repository is still based on GEOS 3.7, so it can’t use the OverlayNG features. If you don’t want to compile PostGIS from source, for Ubuntu you can use the ubuntugis-unstable repo. The PostGIS 3.1 and GEOS 3.9 packages are provided for Ubuntu 20.04 (Focal) only.
On the other hand, PostgreSQL 13 is not mandatory: you can pair the newest PostGIS with Postgres 12 as well.
Data
I decided to test the new PostGIS with following Polish public datasets known to contain invalid geometries:
- Użytek ekologiczny (the lowest form of nature conservation) downloaded from the GDOŚ WFS server,
- cadastral parcels for the city of Katowice, downloaded from municipal WFS server
and intersect them with OpenStreetMap data downloaded from Geofabrik website, and imported using osm2pgsql.
Test
I ran a “sum of lines within polygons” queries against the data sets. The nature conservation data was first:
SELECT sum(st_length(st_intersection(a.geom, b.way))), a.nazwa FROM uzytki_ekologiczne a, planet_osm_line b WHERE st_intersects(a.geom, b.way) GROUP BY a.nazwa;And it returned an error:
ERROR: lwgeom_intersection_prec: GEOS Error: TopologyException: Input geom 0 is invalid: Self-intersection at 473688.49685412901 307009.51074238349So, maybe reducing the precision (intoduced in PostGIS 3.1) will help?
SELECT sum(st_length(st_intersection(a.geom, b.way, 0.1))), a.nazwa FROM uzytki_ekologiczne a, planet_osm_line b WHERE st_intersects(a.geom, b.way) GROUP BY a.nazwa;Nope – it just changed one error message to another:
ERROR: GEOSIntersects: TopologyException: side location conflict at 506560.04918080079 266363.2467461843Further reducing the precision caused the same error to appear.
Maybe the cadastral dataset will perform better?
SELECT a."identyfikator_działki", sum(st_length(st_intersection(a.wkb_geometry, b.way))) FROM cadastre a, planet_osm_line b WHERE st_intersects(a.wkb_geometry, b.way) GROUP BY a."identyfikator_działki";
Now even the more robust ST_Intersects function fails:
ERROR: GEOSIntersects: TopologyException: side location conflict at 506560.04918080079 266363.2467461843Conclusion
As you can see, the new GEOS’ OverlayNG can have better performance, but still it can’t stand self-intersecting polygons (the “Ring Self-Intersection” error is fine, but so it was with GEOS 3.7). This means that you probably shouldn’t hurry to update PostGIS and GEOS if there are no packages for your OS yet, and you definitely can’t dream of smoothly running spatial analysis on all kinds of real world data after the upgrade. And this also means that the infamous GIS magic wand, the zero-width buffer, isn’t going anywhere… but efforts are made to make its magic more predictable.