I have two tables A and B, both have valid geometries when I check with
ST_IsValid. In my query, I intersect the two and the query stops in the middle because of this error:
ERROR: lwgeom_intersection: GEOS Error: TopologyException: Input geom 0 is invalid: Self-intersection at or near point
I read ST_Union fails with TopologyException despite valid polygons and using ST_SnapToGrid which seems very similar, and tried the
ST_CollectionExtract as mentioned in the accepted answer, but I still the same error.
When I run the query on a subset of the data I notice some records are ST_MultiPolygon while others are ST_Polygon, is this causing the issue and, if so, how can I fix it?
The original query has other fields which I've left out here but it generally looks like this:
SELECT ST_CollectionExtract(ST_Intersection(ST_Transform(r.geom, 5070), ST_Transform(c.geom, 5070)),3) as geom, a.place, b.name FROM table1 as a JOIN table2 as b WHERE a.geom && b.geom AND ST_Intersects(ST_Transform(r.geom, 5070), ST_Transform(c.geom, 5070))
I am projecting it because I also calculate areas in the full query.