0

I try to query two spatial table using st_within function, then I want to display the query result as a QGIS Layer. But the layer did not add to the QGIS Canvas. Here my code :

conn = psycopg2.connect("dbname='db_pbb' host=localhost port=5433 user='postgres' password='septin'")
    cur = conn.cursor()
    cur.execute("""SELECT wa FROM gis.kecamatan""")
    rows = cur.fetchall()
    row_list = []
    for row in rows:
        #row_list.append(row.)
        self.dlg.cboKecamatan.addItems([row[0]])
    self.dlg.show()
    # Run the dialog event loop
    result = self.dlg.exec_()
    text = str(self.dlg.cboKecamatan.currentText())
    txtnop = self.dlg.lineNOP.text()

    # See if OK was pressed
    if result == 1:
        #Do something useful here - delete the line containing pass and
        # substitute with your code.
        #Versi satu - sudah berhasil, yeay, you know the key right ?

        sql = "(SELECT gis.tm_bidang.numb, st_within(gis.tm_bidang.geom,gis.kec_palu_utm.geom) as within_geom, gis.tm_bidang.d_nop,gis.tm_bidang.d_luas, gis.tm_bidang.aktor from gis.tm_bidang,gis.kec_palu_utm where gis.kec_palu_utm.wa = '"+text+"' and st_within(gis.tm_bidang.geom,gis.kec_palu_utm.geom))"
        uri = QgsDataSourceURI()
        uri.setConnection("localhost", "5433", "db_pbb", "postgres", "septin")
        uri.setDataSource("", sql, "within_geom", "", "numb")
        tmadminkec = QgsVectorLayer(uri.uri(), "tm_bidang", "postgres")
        QgsMapLayerRegistry.instance().addMapLayer(tmadminkec)

Any ideas ?,

1

I'm trying something similar and want to be able to send a query. Somehow that does not seem to be working even though the documentation for setDataSource() provides several parameters.

I had to change my code to the below to display a simple WHERE clause but still haven't figured out how to display a custom query without only specifying the where clause.

uri.setDataSource(*schema*, *table*, *geom colum*, *where clause*")    

EDIT: Think I just solved it. Hopefully it works for you. I was testing this using the global flow visualisation from here with airports and routes, https://anitagraser.com/2011/08/20/visualizing-global-connections/

Based on trial/error/research, it seems like for the setDataSource function the you dont pass the schema and pass the sql string as the parameter for the table.

Check here as well, https://georepublic.info/en/blog/2013/joining-tables-with-sql-using-pyqgis/

The code I used is below,

sql = "(select * from routes where source_airport in ('BKK', 'DXB', 'KUL'))"
canvas = iface.mapCanvas()
uri = QgsDataSourceURI()
uri.setConnection('localhost', '5432', 'postgis_test', 'postgres', 'xxx')
uri.setDataSource("", sql, "geom", "", "gid")
print uri.uri()
vlayer = QgsVectorLayer(uri.uri(False), "test", "postgres")

if not vlayer.isValid():
    print "not valid"

QgsMapLayerRegistry.instance().addMapLayer(vlayer)

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.