I have a big layer with lines, and a view that needs to calculate the length of these lines without counting their overlaps

A working query that does half the job (but does not account for the overlap, so overestimates the number)

select name, sum(st_length(t.geom)) from mytable t where st_isvalid(t.geom) group by name

The intended query that returns SQL Error [XX000]: ERROR: GEOSUnaryUnion: TopologyException: found non-noded intersection between LINESTRING (446659 422287, 446661 422289) and LINESTRING (446659 422288, 446660 422288) at 446659.27944086661 422288.0015405959

select name,st_length(st_union(t.geom)) from mytable t where st_isvalid(t.geom) group by name

The thing is that the later works fine for the first 200 rows, it's only when I try to export the entire view that I get the error

Would there be a way to use the preferred query first, and if it returns an error on a row use the other one? Something like:

case when st_length(st_union(t.geom)) = error then sum(st_length(t.geom))
else st_length(st_union(t.geom)) end

CodePudding user response：

1. Make sure your geometries are valid before union by wrapping them in ST_MakeValid(). You can also query their individual validity using select id, ST_IsValid(t.geom) from mytable; to maybe filter out or correct the affected ones. In cases where one of you geometries is itself invalid in this way, it'll help. This will still leave cases where the invalidity appears after combining multiple valid geometries together.
2. See if ST_UnaryUnion(ST_Collect(ST_MakeValid(t.geom))) changes anything. It will try to dissolve and node the component linestrings.
3. When really desperate, you can make a PL/pgSQL wrapper around both of your functions and switch to the backup one in the exception block.
4. At the expense of some precision and with the benefit of a bit higher performance, you could try snapping them to grid ST_Union(ST_SnapToGrid(t.geom,1e-7)), gradually increasing the grid size to 1e-6, 1e-5. Some geometries could be not actually intersecting, but be so close, PostGIS can't tell at the precision it operates at. You can also try applying this only to your problematic geometries, if you can pinpoint them.

Here's a related thread.