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
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)
)
