Extract intersections of 2 shapefiles with field subset in QGIS

Question

I have 2 polygon shapefiles: A (with integer field "a_id") and B (integer field "b_id").

My goal is to extract intersections of polygons in layer A with polygons in layer B where attribute "a_id" is equal to attribute "b_id" + 55.

I'm trying to implement it with the QGIS graphical modeler, but "Select by expression" algorithm supports only one layer for running expressions, and "Extract by location" has no attribute filter.

Answer 1

The best solution for me was to open directory containing both layers as a single GDALDataset object, make an OGR SQL query to it and save the result to a new layer. It allows doing spatial and attribute queries simultaneously and can be done with the QGIS console:

from osgeo import ogr
ogr.UseExceptions()
ogr_ds = ogr.Open('/path/to/data', True)  # Windows: r'C:\path\to\data'
SQL = """\
    SELECT ST_Intersection(A.geometry, B.geometry) AS geometry, A.a_id, B.b_id
    FROM A, B
    WHERE ST_Intersects(A.geometry, B.geometry)
    AND CAST(A.a_id as INTEGER) = CAST(B.b_id as INTEGER) + 55;"""
layer = ogr_ds.ExecuteSQL(SQL, dialect='SQLITE')
# copy result back to datasource as a new shapefile
layer2 = ogr_ds.CopyLayer(layer, 'result_shapefile')
# save, close
layer = layer2 = ogr_ds = None

The idea was picked here