1

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.

example1

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.

example2

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?

4
  • 1
    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 4, 2020 at 14:03
  • 1
    In case if you want to have only unique year than you DISTINCT inside of the query GROUP_CONCAT(DISTINCT year)
    – Taras
    Apr 4, 2020 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 4, 2020 at 16:25
  • 1
    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 4, 2020 at 16:31

1 Answer 1

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.

enter image description here

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

4
  • 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 4, 2020 at 16:30
  • Highly suggested updating it to the most recent LTD, i.e. QGIS 3.10
    – Taras
    Apr 4, 2020 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 4, 2020 at 16:42
  • Shame on me should be LTR not LTD
    – Taras
    Apr 4, 2020 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.