5

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). Invalid PostgreSQL layer

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.

FME Writer attribute settings for 'id'

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?

Improve this question
0
4

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.

enter image description here

Improve this answer
4
  • Wow that works! quick solution- many thanks! Will I just have to filter features by geometry and have a separate writer for each type? Or is there a more element solution? – GIS_py Aug 27 '19 at 13:48
  • This (one geometry type per layer) is the classic way desktop GIS works. Well, at least QGIS and ArcMap. So yes, you should split them if you want it to work with QGIS. – nielsgerrits Aug 27 '19 at 17:52
  • In order to keep the question short I didn't explain everything, for example the data is from a CAD file with multiple geometries, some points, line, polygons. The data is merged with attributes from a spreadsheet using the Joiner transformer and in the attributes I have the table name, schema name (for the writer to take), and I could additionally add a geometry attribute from a transformer and try to match the valid PostGIS geometry type if the writer would accept a feature attribute, but it doesn't look like it does. Can you confirm that? Perhaps using a dynamic schema definition... – GIS_py Aug 28 '19 at 15:14
  • @GIS_py Hard to say because I do not know the exact details of what you need but my current best practice for generic CAD-GIS conversions is to just map the CAD data based on geometrytype. points, texts, blocks to a point table, lines, curves, etc to a polyline table, hatches, closed polygons to a polygon table. Multiple ways to do this. Simple is GeometryFilter, advanced way is to expose autocad_originaly_entity and map specific types. Does this help? – nielsgerrits Aug 30 '19 at 7:15
0

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

  1. 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).

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

  3. 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:

  1. collection to multi polygon,
  2. curve to line
  3. St make valid
  4. Update geometry SRID (applies for the layer)
  5. 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.