Creating virtual layer with extent of features from multiple layers in QGIS

Question

I am trying to create an atlas that shows a series of plots (polygons) along with the access routes (line features). I need to vary the extent to show the entirety of each site along with the access route(s) for each plot.

The atlas feature in QGIS works well to iterate through a single coverage layer and will vary its scale to fit the coverage feature. But there isn't a reasonable buffer around either the plots or the routes layers that works to show the entire extent of the combination of the two without either excluding portions of some features or having the site only occupy a small portion of the map.

I am currently using a hand-built extent layer drawn to include the features for both layers but would like a more elegant solution that doesn't need to be re-drawn to account for changes in the underlying features.

Is there a way to create a virtual layer that merges selected features from two layers based on common attributes?

My source layers are: Plots (polygon layer) Routes (line layer)

Both share a common text field "Sites" with values: 'Name', 'Other Name', 'Third Site' Some of the sites have multiple access routes.

I think I should be able to do something along the lines of (SQL novice, but this doesn't work):

FROM Plots, Routes
SELECT *
WHERE Sites matches
BBOX

I'm looking for an output layer that has one feature for each unique value of "Sites" that encompasses the extent of the features from "Plots" and "Routes" with matching "Sites" attributes. The output feature must have an attribute named "Sites" with the appropriate value.

Answer 1

Use this query, where in the last line, replace id with the attribute name that should have the same value and replace poly and lines with the names of your layers:

SELECT
 bounds(st_union(g1.geometry, g2.geometry)) 
FROM
 poly AS g1,
 lines AS g2
WHERE
 g1.id = g2.id

enter image description here

---

Edit: As @BrianFisher, the OP of this question, added in the comments, the query looks like this to merge several lines with the same value:

SELECT
 bounds(st_union(g1.geometry, g2.geometry)) 
FROM
 poly AS g1,
 (SELECT st_union(l.geometry) AS geometry, l.id
 FROM lines AS l
 GROUP BY l.id) AS g2
WHERE
 g1.id = g2.id

• Brian Fisher: Is there a way to merge or filter so I only get one feature for each unique id? Several of my sites have more than one line associated with a single id. I tried using SELECT DISTINCT, but and that took care of cases when the bounding boxes matched, but not when the bounding box of the unions were different
• Brian Fisher: In order to account for multiple lines associated with a single site I added a subquery to merge the lines `(SELECT st_union(l.geometry) AS geometry, l.id; from lines as l; GROUP BY l.id) AS g2
• Brian Fisher: SELECT bounds(st_union(g1.geometry, g2.geometry)), g1.id FROM (SELECT st_union(l.geometry) AS geometry, l.id FROM lines AS l GROUP BY l.Diversion) AS g2, poly AS g1 where g1.id = g2.id
• Babel: OK, thanks. Added this to the answer, hope this is OK for you?