4

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)

4

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')
13
  • Hi ThomasG77, thank you very much for this! perfect answer for my question.
    – ColtonM
    Sep 25 at 15:14
  • Hi again, the suggested solution worked perfectly as it seems, however it has given me somewhat unexpected results...the sql worked and land-use polygons within the bounding box is loaded in QGIS. However, all rows seems to have been unioned into only 1 row...when I opened the attribute table, it says 'Features total: 11500, Filtered: 1' - how can I show all features? why it says it is filtered...?
    – ColtonM
    Sep 29 at 22:33
  • I've tested again (already done before posting the solution) and I'm unable to reproduce. It's also not possible: SELECT * FROM {table_name} WHERE ST_WITHIN(geom, {geom_bound}) is only doing a filtering on records. It does not unioned geometries. I thing you are mixing things using QGIS attributes table e.g docs.qgis.org/3.16/en/docs/user_manual/working_with_vector/… as you seem unfamiliar with it. Close the attribute table, then try to open attribute table again and it should be solved.
    – ThomasG77
    Sep 29 at 23:17
  • Hi I appriciate you've been trying to reproduce what happening to me. It seems really odd to me and I am familiar with QGIS, but this has never happended to me and really confused..yes, I agree with you that it does not union geometries. In fact, when I simply run the same sql on postgres, all records within the bbox returns. when I load the polygons, I can see all geometries, but when I tried to select single feature, it selects everything. When I open the attribute table, I can see only 1 row though it says total row is 11500. I even re-start QGIS, but still...
    – ColtonM
    Sep 29 at 23:31
  • table_name = 'landuse' geom_bound = f'ST_MakeEnvelope({xmin}, {ymin}, {xmax}, {ymax}, 27700)' sql = f'SELECT * FROM {table_name} WHERE ST_WITHIN(geom, {geom_bound})'
    – ColtonM
    Sep 29 at 23:33

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.