2

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:

sub_bas to_bas etc. only depending on fao_id.

Improve this question
4
  • 1
    It doesn't strike me as particularly non-parsimonious or error prone for what it does. Sure, not as simple as the GeoPandas version, but you are not comparing like for like. I would never use Postgis if I wanted to do some quick charting or generate a cholorpleth in Folium and I would never use Pandas if I wanted to do some vector/raster overlay functionality on 500 Gb of data. Apples and oranges.
    – John Powell
    Nov 24, 2017 at 8:34
  • Thank you John. Can you confirm that this is the proper way to do a dissolve in PostGIS? If so, my question is solved. Typically I use Geopandas for testing and creating a suitable workflow for a subset of the data and then use PostGIS for the complete dataset.
    – RutgerH
    Nov 24, 2017 at 10:10
  • 1
    Yes, ST_Union is the correct way to do dissolve, sometimes in conjunction with ST_Dump, if you don't want some giant multi geometry or collection. But, as you are already grouping by fao_id, I imagine this won't be an issue. yes, that's a good workflow. I have tended to do everything in Postgis in the past and then dump to shp to look at it (which isn't very agile :-)). I am increasingly using Pandas, folium, matplotlib, etc before pushing large scale processing to Postgres.
    – John Powell
    Nov 24, 2017 at 12:35
  • 1
    You might also find this answer helpful. It deals with some of the edge cases in larger unions.
    – John Powell
    Nov 24, 2017 at 12:41

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.