5

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.

Improve this question

1 Answer 1

Reset to default
7

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
Improve this answer
4
  • 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 Commented Mar 14, 2023 at 19:03
  • 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 Commented Mar 14, 2023 at 20:18
  • 1
    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 Commented Mar 14, 2023 at 20:54
  • 1
    OK, thanks. Added this to the answer, hope this is OK for you? Commented Mar 14, 2023 at 21:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.