Counting points in polygon and printing out each placing

Question

Here is my sample table for further description:

I have a original point layer:

ID	Name	X(latitude)	Y(longtitude)
ID1	Dog	A	1
ID2	Cat	A	1
ID3	Bird	B	1
ID4	Cat	A	2
ID5	Cat	B	1
ID6	Dog	A	1

Those points are inside a grid layer, lets say 2x2 for instance, and the points are inside the grid. I'll try my best to demonstrate it in the follow table:

-	A	B
1	. . . (Three points, which are ID1, ID2, ID6)	. . (ID3, ID5)
2	. (ID4)	(NULL)

Next, I use the count_points_in_polygon function and create a new layer, which by default adds a column "NUM_POINT".

Grid_ID	NUM_POINT
A1	3
A2	2
B1	1
B2	0

But what I actually want is a table that shows which "Name"s are in each polygon, like the following table shows. How to implement it?

The closest method I found is a guide from https://cornell-gis.github.io/gis-cookbook/qgis/count-multivalues, but it's still slightly different.

Answer 1

You can create a virtual field in the Field calculator for the grid layer and fill it with

aggregate(layer:='Your pointlayername',
          aggregate:='concatenate',
          expression:=to_string(attributes()['attributenameofpointlayer']),
          filter:=intersects($geometry, geometry(@parent)),
          concatenator:=','
          )

You may need to transform coordinates with function transform() if grid (polygon) and coordinates points do not match due to difference for the projection

You can find a link to a minimum QGIS project demo https://labs.webgeodatavore.com/partage/demo-aggregate-expression-points-in-grid.zip

Answer 2

Starting from QGIS 3.16 it is possible to use the overlay_* functions and therefore the expression to be used:

array_to_string(overlay_contains('Your pointlayername',"name"))

Answer 3

I would take a little bit different approach.

1. Vector -> Data management tools -> Join attributes by location

   • set your polygon layer (grid layer) as a Base layer and point layer as a Join layer
   • set Geometric predicate to Intersects
   • For Fields to add section choose Name field (from your point layer)

   As a result you will get a layer with as many rows as you have intersecting points with your polygons. At one row you see the information regarding your polygon object + the name of the point object it intersects with (e.g. if grid A1 contains 3 points, grid A1's field info will show up for three rows each time associated with different point name).

2. Create new fields for this result layer.

   2.1 The numerical field telling you the number of points in each polygon with

   count("grid_id", group_by:="grid_id")
   

   2.2 The list of the point layer names including in each grid with

   concatenate_unique("point_name", group_by:="grid_id", concatenator:=' ,')
   

3. Delete additional fields and rows to get the desired kind of table