I have a geodataframe for which I want to dissolve the geometry based on an attribute (fao_id). In geopandas this is easy as
gdf.dissolve(by="fao_id", aggfunc="first") but unfortunately this fails due to some invalid geometries.
I ingested the geodataframe in PostGIS to make use of the st_make_valid functionality that geopandas does not have. This method works like a charm but now I want to dissolve my table using postGIS similar to the gdf.dissolve function.
I found this article but the result is a less parsimonious SQL statement:
CREATE TABLE newtable AS
SELECT MIN(fao_id) as fao_id,
MIN(attribute1) as attribute1,
MIN(attribute2) as attribute2,
ST_Multi(ST_Union(t.geom)) as geom
FROM inputtable As t GROUP BY fao_id
some comments: 1) I have to manually type all my columns in the statement which can be tedious with larger tables. 2) there is no native FIRST() aggregate function but using MIN() our MAX() works since all values per fao_id are identical.
I was wondering if there was a better way. My attributes2 3 etc are only dependent on the composite key fao_id. see below: