ST_Union returning Null with Spatialite, even after ST_MakeValid

Question

I am attempting to run ST_Union on some features in a GROUP BY Spatialite query, with the aim of counting the number of features, and the total area of those features, which may overlap so should not be summed individually e.g.

SELECT COUNT() as tot_count, ST_Area(ST_Union(geom)) AS tot_area
FROM features

However, with various methods, ST_Union always returns Null, as does ST_UnaryUnion. I can use ST_Collect fine, and get the total area from that, but I believe this will not account for the possible overlap in the features.

There are initially four invalid geometries in the features, as identified by ST_IsValid. Fixing these with ST_MakeValid appears successful (confirmed by ST_IsValid), but does not fix the issue, nor does ST_Buffer(geom, 0). However, if I specifically exclude those four invalid geometries with a where clause, the Union produces a result. Any ideas on the problem? Are the geometries still invalid, despite running ST_MakeValid, and ST_IsValid confirming this was successful? Is there anything else I can try?

Answer 1

One possible solution that seems to work is to union the valid and invalid geometries separately, make those valid, and then union the result e.g.

SELECT ST_Union(ST_MakeValid(geom))
FROM (SELECT ST_IsValid(geom) AS Valid, ST_Union(geom) AS geom
    FROM features
    GROUP BY Valid)

Perhaps an inefficient way to do it though?