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.

I'll try to do some research in aggregate, any more suggestion? thanks — Cookie, Jun 18 '21 at 10:07
Suggestions... be prepared for frustration. I find aggregate hard to master. — Erik, Jun 18 '21 at 10:42
On the german fossgis conference 2020 I've made a workshop on the aggregate function, there is still an animated gif showing how to proceed: https://files.wheregroup.com/index.php/s/ysPjBt74pmD5qQp may be that helps... — eurojam, Jun 18 '21 at 10:51
IMHO the most intuitive approach is the "Join attributes by location (summary)", here is a tutorial: https://www.qgistutorials.com/en/docs/3/performing_spatial_joins.html#performing-spatial-joins-qgis3. Also you may check https://gis.stackexchange.com/questions/305165/calculating-point-layer-values-within-polygon-features-in-qgis-2 with GROUP_CONCAT, more details here: https://gis.stackexchange.com/questions/354187/one-to-many-spatial-join-with-results-in-one-row — Taras, Jun 18 '21 at 11:01

score 4 · Answer 1 · edited Jun 22 '21 at 05:36

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

Oh no I fr$aking did it! but in my case I've changed the code into concatenate_unique for a cleaner result. Very, very appriciated! — Cookie, Jun 19 '21 at 07:23

score 4 · Answer 2 · edited Jun 22 '21 at 05:37

4

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

edited Jun 22 '21 at 05:37

Taras

32,823
4
66
137

answered Jun 18 '21 at 11:46

pigreco

4,460
1
15
32

Hi, thanks for the answer, but how can I add a "unique" to the code since most the point names are the same. – Cookie Jun 19 '21 at 07:28
@Cookie array_to_string (array_distinct (overlay_contains('punti',"id"))), using array_distinct – pigreco Jun 19 '21 at 07:35

score 1 · Answer 3 · edited Jun 22 '21 at 05:39

I would take a little bit different approach.

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).
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:=' ,')
```
Delete additional fields and rows to get the desired kind of table

This probably is the closest answer for what I desired, but it came up with multiple identical IDs in step 2.1, is there anyway to fix this? Here's a quick sample — Cookie, Jun 19 '21 at 07:14
Just create new IDs e.g. with attribute table's Quick field calculation bar by setting ID = @row_number? — Pauliina, Aug 20 '21 at 09:06

Counting points in polygon and printing out each placing

3 Answers3