Export of PostGIS Layer with multiple geometry columns fails

[EZUSoft]

What is the bug or the crash?

If a PostGIS table has multiple geometry columns and if this layer is exported including a geometry column, the export will fail. Shape and Geopackage were tested.

Export to vector file failed.
Error: Unsupported type for field <name of geom column>

The export of geometry columns has been running without any problems for more than 10 years. Last successfully tested in 3.18.2

Steps to reproduce the issue

Add a postgis table (or query) with 2 geometry columns to QGIS.

Layer = iface.activeLayer()
print (Layer.isValid())


options = QgsVectorFileWriter.SaveVectorOptions()
options.driverName = 'ESRI Shapefile'
context = QgsProject.instance().transformContext()
print (QgsVectorFileWriter.writeAsVectorFormat(Layer,'.....r.shp','',Layer.crs(),"ESRI Shapefile"))
# since QGIS 3.20 you should use writeAsVectorFormatV3
print(QgsVectorFileWriter.writeAsVectorFormatV3(Layer, '....r.shp', context, options))

Versions

3.28.3, 3.30.0

Supported QGIS version

• [X] I'm running a supported QGIS version according to the roadmap.

New profile

• [x] I tried with a new QGIS profile

Additional context

No response

[weca-theo]

Sounds similar to: https://github.com/qgis/QGIS/issues/53546 Not an issue on 3.22.8 FYI.

[Tonio0774]

when is there a fix?

[nyalldawson]

I don't think this IS a bug -- multiple geometry columns can't be stored in a shape file, so it's correct to abort out and advise the user.

If you want to export this table to shapefile you have some options, such as

• deselect the secondary geometry column from the list of fields to export
• use a processing tool such as refactor fields to export to shapefile, converting the secondary column to a text field via the geom_to_wkt function

[Tonio0774]

it's a bug because it prevents the normal functioning of processing... it's impossible to make buffer zones for example. the previous behavior was fine in 3.22 and earlier.

in previous versions the second geomtry column was recognized as text in the settings and it was perfect...

[nyalldawson]

it's a bug because it prevents the normal functioning of processing... it's impossible to make buffer zones for example. the previous behavior was fine in 3.22 and earlier.

It doesn't prevent this -- it's just that your model needs to handle your data structures correctly. And in this case it's NOT possible to store a layer with multiple geometry columns in the output format you've selected.

in previous versions the second geomtry column was recognized as text in the settings and it was perfect...

Actually it wasn't perfect -- it was incorrectly dropping information from these columns silently, resulting in transparent data loss. That's why the handling of secondary geometry columns was completely reworked to ensure that they are handled in a completely lossless way. It's unfortunate that fixing this bug has impacted your workflow, but transparent data loss is a critical, high priority bug and I believe fixing that is more important than this workflow.

[Tonio0774]

in this case, it is necessary to plan to be able to choose in the processing and exports which geometry column must be used when it is a postgis layer or by default use the one that does not appear in the settings. I agree that only one column can be used at a time but your consideration in the corrections has not been done correctly I find.

[davidmurray]

I have the same problem after upgrading to QGIS 3.34. Used to be able to export PostGIS layers to CSV without a problem but now it fails ("unsupported type for column XYZ")

[Djedouas]

Hi,

Vector layers in QGIS are managed everywhere with their"main" geometry column, the geometry used for symbology, processings, etc., the column specified as the geometry when the layer is first loaded.

Because it is the way that QGIS works, what you are referring to in https://github.com/qgis/QGIS/issues/52225#issuecomment-1619174255 about being able to choose the geometry column for a processing or an export is then a feature request.

For broken workflows now I would also recommend to adapt the processings and models with fields refactoring, or loading the "same" layer multiple times, each time with a different "main" geometry column, to be able to choose the correct layer.

I would also recommend having a look at the proposed use-cases for handling multi-geometry layers https://www.geopackage.org/guidance/modeling.html