TransWikia.com

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

Geographic Information Systems Asked by Ivan M on February 7, 2021

enter image description hereIn 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?

enter image description here
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

enter image description here

One Answer

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

Answered by Taras on February 7, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP