PyQGIS Problem adding certain vector layers to QGIS from postgres

Question

I've created a tool to find layers from our PostGIS database based on a string match and add them to the current QGIS project. In the example below I'm searching for 'landscape' in the dataset name.

Problem: it adds some datasets to the current project- but not all (2 out of 4 work).

The basic code that adds the layers in: ## I feed in all the parameters with 'P' on the end

uri = QgsDataSourceUri()
uri.setConnection(hostP, "5432", databaseP, userP, passP)
uri.setDataSource (schemaP, dataChoiceNameP, geometrycolP)   
vlayer=QgsVectorLayer (uri.uri(False),dataChoiceNameP,'postgres')
currentProject.addMapLayer(vlayer, False)
mygroup.addLayer(vlayer)

Note that non of these layers originally had the ID field at the beginning and even then the same 2 out of 4 worked. Then I did a drop and create (in FME using PostGIS writer) for all datasets and putting 'id' column at the front (see settings below) and still the same two datasets didn't want to load.

When adding the layer from the QGIS Browser window, PostGIS connections the two problem datasets are added successfully, also works if I use the DB manager.

All 4 datasets have the same attribute/column data types and column names, id, geom, autocad_layer, etc. So I can't see any difference in the data to explain the problem. All datasets had their geometry checked and fixed before going into the database. These are real tables, -not views- unlike a previous post.

Q1. Any ideas why those 2 datasets don't load in the python tool?

Q2. What the difference is between the QGIS GUI (Browser panel/DB Manager) add layer code and the python equivalent?

nielsgerrits · Accepted Answer

The issue is probably the geometrytype. PostGIS can have generic geometry tables, fme can write them but QGIS can't handle them without you specifying the geometrytype.

Check the geometrytype of the spatial column. Make sure you have the "Create Generic Spatial Columns" box unchecked, then you can choose point / polyline / polygon etc.

GIS_py · Answer

For any others reading I found some other circumstances or additional details when the postgis layers didn't load into QGIS:

When layers contain mixed geometries in a single dataset or complex geometries like collections or polyhedral surfaces. You can see multiple layers of the same name in the QGIS browser panel, one for point, line, polygon. You can use postgis SQL to force collections to multi polygons ( ST_CollectionExtract) CAD arcs/curves to lines (ST_CurveToLine).

When Geometry errors were present like self intersections and you can fix using postgis SQL ST_MakeValid.

When it was missing a coordinate system and the SRID is reported as 0. For us this was due to the use of a custom CRS that doesn't have a registered EPSG /SRID. You can use postgis SQL
UpdateGeometrySRID provided you added your custom CRS to the spatial_ref table and you know the number relating to it.

If you're unlucky the source data such as CAD has multiple issues like that and you may need to clean the data in a particular sequence.
What worked for me was:

collection to multi polygon,
curve to line
St make valid
Update geometry SRID (applies for the layer)
Add and serial int 'id' field as the first attribute field (if not already done) and set as primary key since QGIS doesn't like not having it. You may find it loads but you won't be able to edit the layer.

Bare in mind that St makevalid might fix things and put geoemtries into a collection again so you might need to run one or more of the steps again.
I implemented these steps in the FME workbench via a python caller for publishing to Postgis. But also created QGIS python tools to clean up data that's already in the database.

PyQGIS Problem adding certain vector layers to QGIS from postgres

2 Answers

Add your own answers!

Ask a Question