Joining each grid cells with many records in QGIS

Question

I have

a grid (10 x 10 km) where each cell is identified by a code
a shapefile, a point layer, with the year when each point was sampled

as shown in the figure below.

As you can see from the image below, the year is identified by a color and in each cell I have many points of different years.

What I want to obtain is an intersection between the two layers, specifically, I want the same grid with a new column showing the year(s) of the points within each cell. I show you below an example

Cell_ID       Year_of_sampling
10kmE275N271  2014-2015
10kmE276N271  2015
10kmE277N271  2013-2016
10kmE278N271  2013-2015-2016

It seems an easy task, but with Vector -> Geoprocessing -> Intersection is not possible. Any ideas?

I think you are trying to perform this One-to-many spatial join with results in one row, p.s. in the query change ST_INTERSECTS() into ST_WITHIN(). Alternatively, you may be interested in this Assigning each point name of polygon in which it lies using QGIS?. — Taras, Apr 04 '20 at 14:03
In case if you want to have only unique year than you DISTINCT inside of the query GROUP_CONCAT(DISTINCT year) — Taras, Apr 04 '20 at 14:17
Yes, it works. I don't know why but using ST_WITHIN() it doesn't work, whereas ST_ITERSECTS() yes (and it was suitable for what I needed) — Franza, Apr 04 '20 at 16:25
For ST_ITERSECTS() both are acceptable either ST_ITERSECTS(points, grid) or ST_ITERSECTS(grid, points). When you use ST_WITHIN() it should be ST_WITHIN(point, grid) and not ST_WITHIN(grid, point) By point and grid I mean their geoemtries — Taras, Apr 04 '20 at 16:31

Val P · Answer 1 · 2020-04-04T14:41:16.117

1

You can use the Field Calculator and the array variable to create a new field in the grid layer attribute table.

This is the expression that you should use

array_to_string(
array_sort(aggregate(
 layer:= 'point_layer',
 aggregate:='array_agg',
 expression:=field_name,
 filter:=contains(geometry(@parent), $geometry))),
 delimiter:='-'
 )

changing your point_layer name and your field_name according with your data.

in the image below an example of the result using a layer called point and a field called id.

NB The array_sort variable is not available in all the QGIS versions. To use it you should install before the plugin ArrayPlus.

edited Apr 04 '20 at 14:41

answered Apr 04 '20 at 14:34

Val P

3,858
1
8
33

Looking at your results it is what I wanted, unfortunately I hadn't the array_sort, and also I was not able to install the plugin (I use QGIS 2.18 for Mac) – Franza Apr 04 '20 at 16:30
Highly suggested updating it to the most recent LTD, i.e. QGIS 3.10 – Taras Apr 04 '20 at 16:35
I agree with Taras suggestion. Anyway, array_sort order your attribute. You can always apply the expression removing the array_sort and sort the result in another way. – Val P Apr 04 '20 at 16:42
Shame on me should be LTR not LTD – Taras Apr 04 '20 at 16:44

Joining each grid cells with many records in QGIS

1 Answers1