5

I'm trying to load data from different tables using left outer joins into a QgsVectorLayer. The approach I'm following so far is the one from the developers cookbook here. The underlying database is postgres. The query returns correct data, including a geometry column, however I can't figure out how to get the join inside the query to work with QgsDataSourceUri.setDataSource(). As the table the data come from needs to be specified, is it even possible to do so?

Would approaching the problem using psycopg2 be more suited, and if so, is there a way to create a layer from the result set? The only way I can think of is creating an empty layer and adding each feature individually by looping over the result set. But possibly there is an easier way?

Sadly, using the builtin DB Manager is not an option. The layer has to be added programmatically.

2
  • 1
    Are your query(s) static enough that you can setup views on the database side?
    – cokrzys
    Jun 7, 2016 at 17:55
  • The queries are pretty static, but I cannot setup views as I have read-only access, the idea is aweseome though. I have asked the "DBA" about it, he told me that setting up views is not going to happen...
    – werkuh
    Jun 8, 2016 at 7:26

1 Answer 1

10

You have to set up the QgsDataSourceURI like in this post:

uri = QgsDataSourceURI()
uri.setConnection('host', 'port', 'database', 'username', 'password')
sql = 'SELECT * FROM myschema.mytable JOIN othertable USING(id)'
uri.setDataSource('', '(' + sql + ')', 'geom', '', 'id')

vlayer = QgsVectorLayer(uri.uri(), 'LayerName','postgres')
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

print('Is layer valid?', vlayer.isValid())

whereby, it's crucial that the SQL query has to be surrounded by brackets and at least geom and id columns are selected! Another important thing is, to leave the schema string in setDataSource empty and instead use it in the query.

Update for PyQGIS 3:

uri = QgsDataSourceUri()
uri.setConnection('host', 'port', 'database', 'username', 'password')
sql = 'SELECT * FROM myschema.mytable JOIN othertable USING(id)'
uri.setDataSource('', '(' + sql + ')', 'geom', '', 'id')

vlayer = QgsVectorLayer(uri.uri(), 'LayerName','postgres')
QgsProject.instance().addMapLayer(vlayer)

print('Is layer valid?', vlayer.isValid())

Your Answer

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

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