I am using the uri to load the layer from the postgres to qgis canvas but it is not loading the selected features without where condition it is loading the lAYER.

    canvas = qgis.utils.iface.mapCanvas()
    csql='ST_Intersects("p1".geom, "p2".geom)'
    uri = QgsDataSourceURI()
    uri.setConnection("localhost", "5432", "ZBE", "postgres", "postgres")
    uri.setDataSource("public","Sample", "geom",csql,"dn")
    vlayer = QgsVectorLayer(uri.uri(), "test", "postgres") 
    if not vlayer.isValid():
        print "not valid"

Not tested, but it looks to me that your csql should be something like:

csql='SELECT field1,field2,etc 
  FROM "Trainingset" t, "Polygon" p 
  WHERE ST_Intersects(t.geom, p.geom)'

If you don't find another solution for this, consider creating a VIEW in PostgreSQL and then add that as a layer. This has the added benefit that you can change the query in the database without touching your Python code. (So, for example, if you have several users using QGIS with this code installed, you can update all of them immediately by updating the server.)

The syntax for the view is something like,

CREATE VIEW boo AS SELECT * FROM p1, p2 WHERE ST_Intersects(p1.geom, p2.geom)

Then just use boo as the table name in the Python script, and leave out the SQL.


There is a little trick here. You are in a WHERE clause. To query more than one table, you can use a subquery. Suppose you have the tables p1 and p2 from the same PostGIS database, both with the field id as a primary key. You want to filter p1 in QGIS to show only p1 features that intersect p2 features. Try:

csql = "p1.id IN (SELECT p1.id FROM p1, p2 WHERE ST_Intersects(p1.geom, p2.geom))"

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.