Some geometries don't work with ST_Union and gives a non-noded intersection

Question

I'm getting a lot of errors like this:

GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING (147.254 -42.8705, 147.254 -42.8705) and LINESTRING (147.254 -42.8705, 147.254 -42.8705) at 147.25355728849621 -42.870521684000678

I am calling ST_MakeValid(ST_Union(ST_SnapToGrid(mpoly))) on an aggregate of all rows that match my filter.

Answer 1

There is a similar question at How best to fix a non-noded intersection problem in PostGIS?. Some have had success by performing ST_MakeValid, ST_Buffer or ST_SnapToGrid to the geometry before to performing the union. I see you tried SnapToGrid. Have you tried something like:

ST_Union(ST_MakeValid(mpoly))

or

ST_Union(ST_Buffer(mpoly, 1e-5))

The presentation PostGIS: Tips for Power Users outlines some potential sources of trouble when unioning geometries.

Answer 2

Just change the order of functions, add the grid size to snap 0.0001 for degrees, collenct only polygons for query ( 3 stands for polygons):

Select
ST_Union(
  ST_CollectionExtract(
    ST_MakeValid(
      ST_SnapToGrid(geom,0.0001)
    ),3
  )
)
From
  mpoly