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:

And here's an image of the two 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.)

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.

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.

---

---

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.

Output, showing attribute table for the output of the 'Aggregate' tool:

Answer 4

This may not help you, but conceptually, here's what a SQL spatial solution (using PostGIS) looks like for this problem:

select

p.schednum
, string_agg(full_address, ' || ') as concat_addresses

from
    ccd."CCD_Addresses" as a 
join ccd."CCD_Parcels" as p on
    ST_Intersects(a.geom , p.geom)
where
    1=1
    and p.owner_name like '%SCHOOL DISTRICT%'
    group by p.schednum

Which returns:

schednum     |concat_addresses                                                                 
-------------|---------------------------------------------------------------------------------
0606403001000|995 N Grape St                                                                   
0503608002000|951 N Elati St                                                                   
0115200019000|9154 E 56th Ave || 9204 E 56th Ave                                               
0517300003000|911 S Hazel Ct || 900 S Irving St || 938 S Irving St                             
0115200017000|9104 E 56th Ave                                                                  
0115200018000|9004 E 56th Ave || 8904 E 56th Ave                                               
0128414001000|8800 E 28th Ave || 8750 E 28th Ave || 2685 N Akron St || 2608 N Xanthia Ct       
0505330001000|880 N Hooker St                                                                  
0633400057000|8600 E Dartmouth Ave                                                             
0505331025000|860 N Hazel Ct                                                                   
0502226019000|860 E 13th Ave                                                                   
0505331005000|850 N Hazel Ct                                                                   
0508604001000|85 N Hooker St                                                                   
0502314002000|846 N Corona St                                                                  
0518422003000|845 S Lowell Blvd || 3615 W Kentucky Ave                                         
0502211001000|840 E 14th Ave Bldg 2 || 840 E 14th Ave                                          
0222131011000|84 E 45th Ave || 72 E 45th Ave || 82 E 45th Ave  

Basically, join all the address points that fall within parcels with owner_name value of SCHOOL DISTRICT, return the schednum of the parcels and a concatenated list of values in the 'full_address' column, grouping by the schednum where the points intersect.

Again, just posting this as a teaser more than anything...