ENH: support multiple geometry columns in read_postgis

[Gijs-Koot]

Is your feature request related to a problem?

I'm loading the results of a PostGIS query with multiple geometry columns, and I wish geopandas would convert all of them. The problem is that now, only one of the columns is converted to a real geometry type and the other geometries I have to convert myself using shapely.wkb.

Describe the solution you'd like

geopandas.read_postgis should have an argument named geom_cols: list[str]. When this argument is set, all these columns are parsed into a geometry type.

API breaking implications

This will add an additional optional parameter. Behaviour wouldn't change if the parameter is not included, so it would be backwards compatible if I'm not mistaken.

Describe alternatives you've considered

Current solution is to use shapely to do the conversion after loading the data.

[nicholas-ys-tan]

I'd like to have a go at this one if no one else is currently working on this.

[nicholas-ys-tan]

Probably a few different ways to cut this, the changes would need to be made to io.sql._df_to_geodf where geom_col accepts the string of the geometry column name.

Would we want to extend read_postgis to accept string and lists of string? Perhaps with the first one in the list being used as the geometry column for the GeoDataFrame?

Or would we want a new kwarg to accept additional geometry columns that would not subsequently become the geodataframe's geometry column?

[m-richards]

I'd like to have a go at this one if no one else is currently working on this.

Feel free! There are a few rough edges like this in our postgis support.

Or would we want a new kwarg to accept additional geometry columns that would not subsequently become the geodataframe's geometry column?

I would lean toward this option - I prefer that we're still explicit about which column is the active geometry column, rather than doing something implicit based on ordering (and also that might get confused with trying to control the order of the columns in the returned geodataframe)

Another option is to accept a string or list of strings as geom col, with a new kwarg to accept the primary geometry column, but I think that's not as clean to work with.

[jorisvandenbossche]

Would it actually be possible to infer from the postgres result schema which columns are geometry columns? (although I don't know if we have access to that through the pandas.read_sql we are using)

[Gijs-Koot]

The coordindate system is an interesting sidenote here, that is in geopandas a property of the frame itself, while the separate columns could use different coordinate systems.

[m-richards]

The coordindate system is an interesting sidenote here, that is in geopandas a property of the frame itself, while the separate columns could use different coordinate systems.

Geopandas also defines column level crs, gdf.crs is the crs of the "active" geometry column, which is the frame column used as the basis for spatial joins and other geometry operations on the frame level. So we should be fine in this aspect