QGIS 3.28.4. I have 3 shapefiles:

  1. polyline, street information (streetId, + streetInfos...)
  2. point, stake information (StakeId,streetId, + Stakeinfos.....)
  3. polyline, pannels information (PannelId, StakeId, + PannelInfos...)

There can be n pannels on one stake, m stakes in one street.

I have to produce a street based report that gives a multilevel view on this by saying:

The report should contain a map of the street, different infos on that street (No problem with that) but also the stakeID woth their own pannels, expressed, for exemple as

  • For street1, I have StakeId1 (with Pannel1, Pannel2), StakeId2 (with Pannel3)

  • For street2, I have StakeId3 (with Pannnel4)

  • For street3, I have StakeId4 (with Pannel5, Pannel6, Pannel7), StakeId5 (with pannel8)


I tested with aggregate fonctions, but do not know how to use the result of the StakeId aggregate list as an input for the second aggregate level on the pannels. Any ideas how to handle this?


1 Answer 1


Solution: quick description

First join the three layers, than aggregate them.

How to do

1. Joining

Use virtual layers: one to join street and stakes layers:

select * from street as a, stakes as b
where a.streetId = b.streetId

Then join this layer virtual1 to the panels layer to create a second virtual layer virtual2:

select * from virtual1 as v, panels as p
where v.StakeId = p.StakeId
2. Aggregating

Now you have as many features as you have panels with duplicate features for street and stakes (see layer panel in first screenshot).

To reduce them, use Aggregate algorithm on virtual2 using StakeId for Group by expression and make the necessary settings for all attributes: concatenate for PanelID (change Source Expression to to_string(PannelId) to be able to concatenate), probably first value for other attributes and adapt field Type and Length (see first screenshot below).

Then repeat with the resulting layer and StreetId for Group by expression. See the result for both Aggregate steps on the 2nd screenshot.

3. Screenshots

enter image description here

Result: enter image description here

  • seems good for a SQL database, but all data are as shapefiles. Can we transpose this to shapefiles?
    – ClaudeVer
    Nov 8, 2023 at 7:13
  • 2
    This works without any SQL database, this works with any vector layer, including shapefiles. QGIS can use SQL queries to create virtual layers based on your shapefiles. Be aware, however, of the limitations of shapefiles regarding field length etc. Consider Geopackage as an alternative.
    – Babel
    Nov 8, 2023 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.