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)
uri.setDataSource("", query, ...
uri.setDataSource("", "SELECT * FROM ...", "lock_geom" ...
. Or create a view under PostGIS and load it.