PyQGIS – Loading PostGIS Layer Within Active Layer Extent Using PyQGIS

postgispyqgisqgis

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)

Best Answer

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')
Related Question