Concatenate string field in spatial join in QGIS

Question

I'm doing a spatial join between a point layer and a polygon layer. The point layer has a field called "type" which has string values. When I join this to the polygon layer, I would like to get a field concatenating all the "types", in addition to the count of points in each polygon.

Here's an image of the point attribute table: attribute table point layer

And here's an image of the two layers: points and polygon layers

I know this is possible in ArcGIS with "Output field properties" and "Merge rules", but I can't figure out how to do this in QGIS.

Answer 1

Do a spatial join between the points layer and the polygon layer. The result should be that the points layer gets one additional attribute, "Polygon_ID" which tells you which polygon each point belongs to. (If your points already have polygon IDs you can skip this step.)

enter image description here

Use the Field Calculator to add a new field to the point layer with an expression like this:

 array_to_string( array_distinct( array_agg("type","Polygon_ID")))

Make the appropriate substitutions with your actual field names and layer names.

The field will have comma-separated values, like this: 'real estate,politics,food,opinion'. If you want spaces or other characters separating the words, you can add an optional concatenator parameter to the end of the array_to_string() function. For example, if you want the words separated by a space, then a hyphen, then another space (like ' - '), use this function:

 array_to_string( array_distinct( array_agg("type","Polygon_ID")), ' - ')

All of the points with the same polygon ID will have the same value in this new field.

Now do another spatial join. This time join the polygon layer to the points, and choose 'take attributes of the first located feature only (one-to-one)' as the join type.

enter image description here

Answer 2

Assuming that the joined layer has a polygon_id field.

---

If you want to count how many points of each type are joined to each polygon, you can use the following expression:

array_to_string(
 array_distinct(
 array_agg(
 count(
 "id",
 "type"||"polygon_id") || ' ' || "type",
 "polygon_id"))
 , ' , ')

There we are counting how many points are by type and polygon, aggregating them by polygon in an array and transforming the array to a string.

---

If you want to have a list of types inside a polygon and the total number of points inside a polygon, you can use the following expression:

concat(
 'Types: ',
 array_to_string(
 array_distinct(
 array_agg(
 "type",
 "polygon_id")),
 ' , '),
 ' . Points: ',
 count(
 "id",
 "polygon_id"))

There we are doing something similar but just for the types, and then counting all the points.

---

1

---

Note:

If you are joining the polygons to the points or the points to the polygons, is no difference here. The joined layer must be similar (but in one case you have points, in the other polygons). Anyway, you will have a polygon id, a point id and a point type. Just use the field names of your joined layer in the expression.

Answer 3

Simple solution using existing tools (completed in QGIS 3.22).

1. run a normal 'join attributes by location' tool, setting the 'base' layer as the polygon layer and the 'join layer' as the point layer. Select the join layer field prefix (if desired) and select the fields to join from the point layer (unless you want all of them). Run tool.
2. Check output of step 1. You'll notice if multiple points fall within a single polygon, the polygon feature will be duplicated for each join. The next step will clean this up.
3. Open the 'Aggregate' tool. Set it up like the image below. You're essentially telling the tool to dissolve all polygons into each other but keeping features separate on a unique polygon field name. Where a polygon was duplicated (because it found multiple points during the join process), the Aggregate tool will concatenate the unique values found (make sure to increase the field length to hold a long concatenated string). It can also create a new count field (click the 'Add new field' button in the Aggregate tool).

I've highlighted in red the important settings in the image.

enter image description here

Output, showing attribute table for the output of the 'Aggregate' tool: enter image description here