3

Are there strategies for dealing with TopologyException errors in PostGIS 2? Nearly every function I run against two geometries results in a TopologyException (e.g. ST_Intersection, ST_SymDifference).

I always try ST_Buffer(the_geom, 0) first, then ST_MakeValid(), then (ST_Dump(the_geom).geom and repeat ST_Buffer. Sometimes a combination of one or more fixes it, sometimes not.

Perhaps my data is just not clean enough for PostGIS 2? I feel like I rarely had this issue on Postgres 9.0 with PostGIS 1.5, but now it is almost every dataset.

underdark
  • 84,148
  • 21
  • 231
  • 413
Dylan Hettinger
  • 520
  • 1
  • 4
  • 10

1 Answers1

3

As you suspect, this is from invalid geometries.

First, find them and identify the issue, e.g.:

SELECT gid, geom, ST_IsValidReason(geom)
FROM my_table
WHERE NOT ST_IsValid(geom);

Do this for each geometry column/table, and fix the geometries (this might be another question). Use QGIS or some tool to get a visual of the geometry to see what is going on.

Once you have them cleaned up, keep them valid with a check constraint:

ALTER TABLE my_table
  ADD CONSTRAINT enforce_valid_geom CHECK (ST_IsValid(geom));
Mike T
  • 42,095
  • 10
  • 126
  • 187
  • I'm able to correct the input geometries and get PostGIS to believe they are valid (WHERE NOT ST_IsValid(geom) returns 0), but most often running ST_Intersection or similar still errors with a TopologyException. – Dylan Hettinger Apr 29 '13 at 16:14
  • @DylanHettinger It is possible that this is a bug, however it is difficult to isolate the offending geometry(ies). If you can, post the WKT/WKB in your question. – Mike T Apr 29 '13 at 19:59