2

I am trying to write a piece of code to query and then import a subset of a PostgreSQL as a vector layer onto the map.

I am able to connect and add the whole SQL table into QGIS. But I am not sure how to query the table; for a subset using ST_Intersect() to a bounding box before adding it as a layer into the map.

This is where I am at

import psycopg2 
from psycopg2 import sql
from qgis.core import *
from PyQt5.QtCore import *
from PyQt5.QtCore import QSettings
from PyQt5.QtCore import QSettings
from qgis.core import QgsVectorLayer, QgsDataSourceUri
uri = QgsDataSourceUri()
try:
    uri.setConnection('host', 'port', 'name', 'user', 'pass')
except:
    print ("I am unable to connect to the database")

query = sql.SQL("SELECT * FROM {} Where st_intersects(lockgeom, st_transform((ST_MakeEnvelope(-121.860102, 37.387439, -121.860510,
37.385896, 4326)), 2163))").format(sql.Identifier("cuebiq", "usca_trip_points7_201905"))

uri.setDataSource("schema","table name","lock_geom",a.Sql='query', aKeyColumn='id')
uri.setKeyColumn('id')
vlayer = QgsVectorLayer(uri.uri(),"mylayernamep","postgres")
#vlayer=ST_Intersects(lockgeom, st_transform((ST_MakeEnvelope(-121.860102, 37.387439, -121.860510,
37.385896, 4326)), 2163))
if not vlayer.isValid():
    print ("invalid")
QgsProject.instance().addMapLayer(vlayer, True)
6
  • 1
    Try : uri.setDataSource("", query, ... Jun 14, 2021 at 9:02
  • I tried uri.setDataSource("cuebiq","usca_trip_points7_201905","lock_geom","query", aKeyColumn='id') it still creates an empty layer , Is that what you meant ? Jun 14, 2021 at 13:28
  • Not exactly, just let an empty schema and replace table name by the query text : uri.setDataSource("", "SELECT * FROM ...", "lock_geom" .... Or create a view under PostGIS and load it. Jun 14, 2021 at 13:37
  • I tried : query= sql.SQL("SELECT * FROM {} Where st_intersects(lock_geom, st_transform((ST_MakeEnvelope(-121.860102, 37.387439, -121.860510, 37.385896, 4326)), 2163))").format(sql.Identifier("cuebiq", "usca_trip_points7_201905")) cur.execute(query) myresult = cur.fetchone() print(myresult) uri.setDataSource("","query", "lock_geom" , aKeyColumn='id') Jun 14, 2021 at 14:28
  • And also uri.setDataSource("","SELECT * FROM {} Where st_intersects(lock_geom, st_transform((ST_MakeEnvelope(-121.860102, 37.387439, -121.860510, 37.385896, 4326)), 2163)).format(sql.Identifier("cuebiq", "usca_trip_points7_201905"))", "lock_geom" , aKeyColumn='id') Jun 14, 2021 at 14:30

0

Your Answer

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