QGIS – Creating Virtual Layer with Extent of Features from Multiple Layers

dissolveextentsqgissqlvirtual-layer

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.

Best Answer

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