Loading PostGIS layer within active layer extent using PyQGIS

Question

I think what I am trying to do is quite simple..however, I couldn't figure out how to do this. I am fairly new to PostGIS and PyQGIS.

I've created a spatial table with PostGIS that contains vector land-use data (polygon) and I want to perform a partial data load using a python script on QGIS (eventually, I want to create a plug-in)

On QGIS desktop, prior to data load, I add a single local authority boundary polygon layer. From the PostGIS table 'land-use', I only want to load 'what is inside the boundary layer' - so for example, if I have a boundary layer of 'Glasgow', then I only want to load land-use data for Glasgow - so I guess this is like a clipping feature layer by another layer and I know it's easy to do when both layers are already loaded on QGIS.

To get an extent of data I require from my data table, I wrote the following script:

uri = "C:/data/glasgow.shp"
iface.addVectorLayer(uri, 'glasgow', 'ogr')
    
layer = iface.activeLayer() 

ext = layer.extent()
xmin = ext.xMinimum()
xmax = ext.xMaximum()
ymin = ext.yMinimum()
ymax = ext.yMaximum()

rect = QgsRectangle(xmin, ymin, xmax, ymax)

I am struggling to work out what to do after this. I assume that I need to write a spatial query using ST_intersect or ST_Within (or something else, maybe I am completely wrong!) using rect? (select land use geometry only within rect bounding box)

Answer 1

You can look at the following recipe. It completes the beginning of the example you provided.

table_name = 'your_table_name'
# We use hardcoded EPSG code 4326. You may also need to do a ST_Transform
# if your layer from shp do not have same projection as the PostGIS layer
geom_bound = f'ST_MakeEnvelope({xmin}, {ymin}, {xmax}, {ymax}, 4326)'
sql = f'SELECT * FROM {table_name} WHERE ST_WITHIN(geom, {geom_bound})'

# Before going further, you may try the resulting sql query
# from above sql variable using PgAdmin to see "it works"
# Then proceed to below steps
uri = QgsDataSourceUri()
# You may need to change localhost to your server host, port 5432
# to your own and also database_name, user and password with
# the one you need
uri.setConnection ("localhost", "5432", "database_name", "user", "password")
# First arg empty because no need for schema here, geom is
# geometry column name and id the record identifier/primary key
uri.setDataSource('', f'({sql})', 'geom', '', 'id')
vlayer = iface.addVectorLayer(uri.uri(), 'my specific query','postgres')