4

I am struggling to convert my sql query to a valid Postgres layer in QGIS with the help of Python. Here is what I tried:

SELECT * FROM test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71

When running the above code in pgAdmin4 I get 2 rows as a result as I except it. When I try to call the same statement inside my Qgis Plugin, I get an empty layer.

sql = "select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71"    
self.uri1.setDataSource("", "test_geodb", "geom",  sql)  
layer = QgsVectorLayer(self.uri1.uri(False), "testlayer", "postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)   

While doing this:

self.uri1.setDataSource("public", "test_geodb", "geom", "w_speed = 5.75")        
vlayer = QgsVectorLayer(self.uri1.uri(False) ,"layername2","postgres")
QgsMapLayerRegistry.instance().addMapLayer(vlayer)

results in a correct selection and this:

self.db.setDatabaseName(self.uri1.database())
self.db.setUserName(self.uri1.username())
self.db.setPassword(self.uri1.password())
self.db.open()
self.query = QSqlQuery(self.db)
sql = "select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71"
while self.query.next():
    print self.query.record().value("geom")
    #geom_query = query.record().value("geom")

gives me the geometries of my polygons as postgis geometries of the two rows. By reading here, here and here, I tried to solve my problem. I assume it has to do with the setting of the setDataSource but I don't know how to fix it.

I am using Postgres 9.6, postgis 2.3 on windows 10 with QGIS 2.14

1

I finaly figured it out but it feels like a huge detour so I would appreciate any better suggestions:

What I did was opening my DB with python, cretaing a query which creates a table and then load this newly created table as a layer into qgis

 def sql_querry(self):
        self.db = QSqlDatabase.addDatabase("QPSQL")
        self.db.setHostName(self.uri1.host())
        self.db.setPort(int(self.uri1.port()))
        self.db.setDatabaseName(self.uri1.database())
        self.db.setUserName(self.uri1.username())
        self.db.setPassword(self.uri1.password())
        self.db.open()
        self.query = QSqlQuery(self.db)
        if self.count_query == 0:
            print "Query execute:"
            sql = "DROP TABLE  IF EXISTS test_db;CREATE TABLE test_db AS select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 71;ALTER TABLE test_db   ADD COLUMN geoid SERIAL PRIMARY KEY;"
            self.count_query += 1
        else:
            sql = "DROP TABLE  IF EXISTS test_db;CREATE TABLE test_db AS select k.pot_field, k.geom, k.id from test_geodb  as k, vg250 as o WHERE ST_Within(ST_PointOnSurface(k.geom),o.geom) AND o.id = 68;ALTER TABLE test_db   ADD COLUMN geoid SERIAL PRIMARY KEY;"

        print self.query.exec_(sql)

The if-else statement was just for checking if could load different layers from the "same" table which also worked. Here is the code which I called to the create the layer:

    self.sql_querry()    
    self.uri1.setDataSource("public", "test_db", "geom")  
    layer = QgsVectorLayer(self.uri1.uri(False), "testlayer", "postgres")                
    if layer.isValid():
        QgsMapLayerRegistry.instance().addMapLayer(layer)  

As I said feel free to point me to a better solution!

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.