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?