Topology exception on geometry intersection?

Question

I am using the following PostGIS functions in a Postgres query:

ST_AsGeoJSON(ST_Intersection(p.geom,c.geom))

Where p.geom & c.geom are type multipolygon. The query works most of the time but with some layers I recieve the following error:

ERROR: Error performing intersection: TopologyException: Input geom 0 
is invalid: Ring Self-intersection at or near point... 

I tried applying ST_Makevalid() on the individual geometries in the ST_Intersection call but it doesn't seem to resolve the issue.

I also tried adding where conditions using ST_IsValid() on both table geometries and the intersection of the two. I can get the query working when I add where ST_IsValid(p.geom) but it excludes a large number of the features I'm trying to display in the application.

After running ST_IsValidReason(p.geom) I can see that several of the results have Ring Self-intersection[{long} {lat}].

How can I resolve this?

Answer 1

The input of st_intersection needs to be cleaned.

You can check whether geometries are clean by using:

WHERE ST_IsValid(mySuspectTable.geom)

You can try and fix these with:

set geom = ST_MakeValid(geom)

You can also use:

ST_Buffer(ST_Buffer(geom, -0.001), 0.001)

To remove slivers.