Concatenating names of the districts and the numbers of the sections in the polygonal layer of streets based on intersection

Question

In QGIS 3.14 I want for a layer with street contours (polygons) to record the names of the areas through which the street passes and the numbers of cadastral plots through which it passes.

If a street intersects several objects, their names must be written in one field after a comma. How do I solve this problem?

if the area of the area falling into the boundaries of the street is less than the specified value, then its name is not taken into account

Answer 1

To extend the query in this answer with an "area"-rule, i.e. consider value only if the intersection is more than a certain value, then consider a slightly adjusted query:

SELECT a.*, GROUP_CONCAT(b.info) AS concat_b_info
FROM "Layer_B" AS b, "Layer_A" As a
WHERE ST_INTERSECTS(a.geometry, b.geometry)
      AND ST_AREA(ST_INTERSECTION(a.geometry, b.geometry)) > 80000000
GROUP BY a.id

Or consider in the Field Calculator the following expression:

aggregate(
  layer:='Layer_B',
  aggregate:='concatenate',
  expression:="info",
  concatenator:=',',
  filter:=(intersects($geometry,geometry(@parent)) AND area(intersection($geometry,geometry(@parent))) > 80000000)
)

Keep in mind that 'area'-function

Returns the area of a geometry polygon object. Calculations are always planimetric in the Spatial Reference System (SRS) of this geometry, and the units of the returned area will match the units for the SRS. This differs from the calculations performed by the $area function, which will perform ellipsoidal calculations based on the project's ellipsoid and area unit settings.

When areas of the overlapping features have to be additionally calculated, use this

SELECT a.*, GROUP_CONCAT(b.info) AS concat_b_info,
            GROUP_CONCAT(round(ST_AREA(ST_INTERSECTION(a.geometry, b.geometry)), 4)) AS a_b_areas
            -- GROUP_CONCAT(round(ST_AREA(b.geometry), 4)) AS b_areas
FROM "Layer_B" AS b, "Layer_A" As a
WHERE ST_INTERSECTS(a.geometry, b.geometry)
      AND ST_AREA(ST_INTERSECTION(a.geometry, b.geometry)) > 80000000
GROUP BY a.id

or in the Field Calculator with a new field for area values:

aggregate(
  layer:='Layer_B',
  aggregate:='concatenate',
  expression:=to_string(round(area(intersection($geometry,geometry(@parent))),4)),
  # expression:=to_string(round(area($geometry),4)),
  concatenator:=',',
  filter:=(intersects($geometry,geometry(@parent)) AND area(intersection($geometry,geometry(@parent))) > 80000000)
)