Request features within a polygon from PostGIS or GeoPackage with PyQGIS

Question

With the PyQGIS API, I have a QgsVectorLayer object that is sometimes a GeoPackage/SHP layer, sometimes a PostGIS layer. I have a working code to get all features within a given polygon "frame" when the layer is from a GeoPackage, but when it is from PostGIS so spatial filtering is server-side (the layer has a spatial index), QgsFeatureRequest functions setFilterRect or setDistanceWithin don't work anymore.

My code:

request = QgsFeatureRequest().setFilterRect(frame.boundingBox())
for feature in vectorLayer.getFeatures(request):
    if feature.geometry().intersects(frame):
        # feature stuff here
        id = str(feature.attribute(idFieldId))

For PostGIS case, I tried to use an spatial filter in setFilterExpression function but this doesn't get any feature:

spatialFilter = f"ST_Within({layer.dataProvider().uri().geometryColumn()}, ST_GeomFromText('{frame.asWkt()}', {frameSrid}))"
request.setFilterExpression(spatialFilter)

So, how to make a working and efficient spatial request with QgsFeatureRequest with a PostGIS layer?

Anyway, I can switch code depending on layer source provider with layer.dataProvider().name() which is ogr or postgres.

Answer 1

As an alternative to "request" to get intersecting features, you can use Qgis processing algorithm "selectbylocation":

alg_params = {
    'INPUT': vectorLayer,
    'PREDICATE': 0,
    'INTERSECT': frameLayer,
    'METHOD': 0
}
processing.run("native:selectbylocation", alg_params)

features = vectorLayer.getSelectedFeatures()

frameLayer should contain the geometry you are checking for the intersection with.

For algorithm help you can use:

processing.algorithmHelp("native:selectbylocation")

Answer 2

Actually, QgsFeatureRequest spatial filtering works the same way with ogr and postgres layers. There is no need to separate the building of the request for each layer provider, and no need to use PostGIS SQL functions.

If the frame is oriented from the North (my case), so it is a polygon, spatial filter can be built using setDistanceWithin with a distance of zero:

QgsFeatureRequest().setSubsetOfAttributes(attributeSubset).setDistanceWithin(frame, 0)

Also, using an attribute subset optimize the request.