1

Using a win 7 machine, I have been revising tables in postGIS, then looking at the results in qGIS: with following script and run it in the qGIS python console.

import random
uri = QgsDataSourceURI()
myLayer = "layer_name"
myPrimaryKey = "the_key_name"
uri.setConnection("localhost","5432","database_name","user","password")
uri.setDataSource("public",myLayer,"geom","",myPrimaryKey)
vlayer= QgsVectorLayer(uri.uri(),myLayer+str(random.randrange(0,600)),"user")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

The problems I am running into are twofold:

  1. Sometimes if I do not change the name of the table, the above script does nothing. (The random # is there so that I can see if the script has added a layer and with a new name.)
  2. More importantly, the above script rarely works with postgis "views," meaning that each time I want to revise a table, I must first drop it. I'd rather be replacing views. The problem relates to this line:
uri.setDataSource("public",myLayer,"geom","","apn")

It let's me specify a schema, "public;" but I cannot figure out a way to say whether a table falls under the next level, namely postGIS tables or postGIS views. If I could do this, I would then be able to specify the name of the view. Yes, DBManager is "aware" of postGIS views. But I would like to know if the qGIS python console can work with postGIS views.

  • When you say rarely works, are you saying it sometimes works with views? The only difference I can think of with a table or view is that with a table, QGIS knows what to use for primary key. I assume you are saying apn should be the primary key. If apn is duplicated in your view, I think qGIS throws up an error cause it can't deal with duplicate keys. – LR1234567 Apr 23 '17 at 17:47
  • Thank you! My views do not have keys (and I don't know it they can have can). However, "apn" was the incorrect name for table's key. I updated the above script for a key name and it now works with postgreSQL views! As far as "rarely works," I will have to test further. I have experienced instances where the above script would not add a table, when I had repeatedly dropped a given table, re-created it using a select (but with using same name), and then used the above script to send it to qGIS. – user3659172 Apr 23 '17 at 18:33
  • Okay I'll upgrade my comment to an answer – LR1234567 Apr 23 '17 at 18:35
2

The only difference I can think of with a table or view is that with a table, QGIS knows what to use for primary key because it can infer it from the table primary key constraints. With a view, since you can't specify a primary key, you have to give it a name of a field to use.

I assume you are saying apn should be the primary key. If apn is duplicated in your view (multiple records have same apn) or does not exist, I think qGIS throws up an error cause it can't deal with duplicate keys and has to have a key.

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.