Geographic Information Systems Asked by James B on August 1, 2021
(QGIS 3.4) The image below will help explain my goal:
I’m working out how to coalesce the attributes of the field “ID” if the field “X, Y” is a duplicate.
I have highlighted some duplicate coordinates in red. My expression will create a new text field and chain all attributes in the “ID” field, delimited by a pipe (|
).
For example, where I have labelled features with blue text 1,2,3, the new field will write
Special Protection Areas | Scheudled Monuments | SSSI | AONB | Special Areas of Conservtion | Scheduled monuments | Scheduled Monuments | SSSI | AONB
I will omit duplicate values later in excel.
Here’s what I have so far…if( "X, Y" = "X, Y" , coalesce( "ID" ),' ')
but this just duplicates the “ID” field without any coalescing.
With credit to Vince, the correct expression to use in this case was:
concatenate( to_string( "ID" ),group_by:="X, Y", concatenator:='|')
Correct answer by James B on August 1, 2021
In QGIS I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...
Let's assume there is a point layer with it's corresponding attribute table, see image below.
With the following query, it is possible to achieve the result.
SELECT "X, Y", GROUP_CONCAT(info, ' | ') AS info_concat
FROM "points"
GROUP BY "X, Y"
The output Virtual Layer will look like as following
Note: Geometry is not included in the final output, otherwise extend the query with geometry
parameter and to check how many points were grouped insert COUNT()
, i.e.
SELECT "X, Y", GROUP_CONCAT(info, ' | ') AS info_concat, geometry, COUNT("X, Y") AS pperloc
FROM "points"
GROUP BY "X, Y"
References:
Answered by Taras on August 1, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP