Attribute data in QGIS Atlas

Question

I am trying to pull together data from the attribute table based on which the atlas is created. I want the query to display the information if it occurs within the Atlas feature. In my project I am trying to produce maps of various parks and then pull details of various features in it. For example, a park may have playgrounds, barbecues, water fountains etc. and want information displayed just for those features. There are many more attributes. Just to clarify, all this information is present in the parks layer.

I am so far tried the following query and it works as long as the features exist in the atlas feature. If they don't the attribute query fails which is not what I want. Here's an example of the query:

[%'ASSET KEY'  ||  '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||   '\t' ||  'ASSET NAME' || '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||   '\t' ||'AREA (SQ. M.)' ||  '\n'  ||  '\n'  || 
title("pg_key")||  '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||   '\t' || title("pg_nam") || '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||   '\t' || "pg_area"  ||  '\n'  ||  '\n'  || 
title( "skt_key") ||  '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||   '\t' || title("skt_nam") || '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||'\t' ||"skt_area"  ||'\n'  ||  '\n'  || 
title( "bmx_key") ||  '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||   '\t' || title("bmx_nam") || '\t' ||   '\t' ||   '\t'  ||  '\t' ||   '\t' ||   '\t' ||    '\t'  ||   '\t' ||   '\t' ||   '\t'   || '\t' ||'\t' ||'\t' ||"bmx_area"%]

In the above example, the query works as long as the features pg, skt and bmx occur within the park. Even if one doesn't the query fails. I want to create this query for all the possible scenarios so that the atlas puts the correct information in each of the maps.

---

Example image below:

With this one, I would like to information associated with green, red and blue hatching and the football icon. However, there are many other features within parks that are not present in this park here. So I want to set up the query for all the possible scenarios so that I don't have to manually change it for the 400 odd maps that I'm trying to produce. In the example below, if I try and put down a query for all the possible scenarios, it is failing as soon as one of the features that is not a part of the park is mentioned in the query.

This is how I would like the information to appear below the map. I tried to use the attribute table directly but the manner in which the data is organised in the atlas layer does not let me know populate the data row-wise. In other words, each park is a row and all the features whether present in the park are linked to the single row in separate columns.

This screen dump below shows how the features are linked to the different parks as separate columns.

---

I have since morning tried to tackle this problem differently. Rather than linking all the features to each and every row in the park, I have tried to keep the features in separate layers and then use a query to pull the desired information. Even this approach does not seem to work. The query I'm using is as follows:

"BMX_NAME" =attribute(  $atlasfeature ,'bmx_nam')

BMX_NAME is a column name in a separate layer called BMX whilst the parks layer has the name of the BMX track under the column bmx_nam. This query doesn't work even though my current atlas feature has a BMX track. So I'm back to square one now. If this works then it would solve the problem I think...

Any suggestions?

---

I changed the query to the following and it still does not work.

 "BMX_NAME"=attribute(@atlas_feature , 'bmx_nam')

Answer 1

Summarized:

1. You can create a virtual_layer that combines every feature contained by a park. Or - when dealing with a lot of layers - it is preferrable to make use of a database, for example SpatiaLite where you create a view.
2. Then you add a relation park-virtual_layer/view.
3. In the print composer you can add a table and select as source: 'relation children'.

Only the 'linked' features will show up in the table.

In detail:

Suppose you have a polygon layer (parks), line layer and points layer.

Create a SpatiaLite database (How to save multiple layers from QGIS to a Spatialite db?) and load all your layers into it.

Make a view with the following code:

create or replace view parks_contains as

select a.id, a.in_park
from 

(
select parklayer.id, layer1.attributeA as in_park
from parklayer
join layer1 on st_intersects(parklayer.geometry, layer1.geometry)

union

select parklayer.id, layer2.attributeB as in_park
from parklayer
join layer2 on st_contains(parklayer.geometry, layer2.geometry)

union

select parklayer.id, layer3.attributeC as in_park
from parklayer
join layer3 on st_contains(parklayer.geometry, layer3.geometry)

union

etc...

) as a

where in_park is not null

You can union as many layers as you want. Just be aware to name all outputfields the same. How it works:

In the main codeblock you make a join between the parkslayer and each other layer. In the join you define how the other features will be 'linked' to each parkattribute, using a command like st_within() or st_intersects() (for more detail look at http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-sql-2.4-4.html#p12)

For each feature that lies in a park there will be a row in the resulting table :

Load that new table into QGIS and add a relation project > project properties

In the printcomposer you can add a map, set it to be controlled by atlas. Add a table and choose 'Relation children' as Source.