2

I have seen a similar question to Counting points in polygon using weight and class simultaneously, but I was unable to operationalize the answer that I found so far.

I have a point layer with attribute values by groups that I would like to assign to the cells of a grid layer as sums of the different groups.

Ideally, I would receive one column for each group with the summed values of the group that are contained within the grid cells. How can achieve that?

I was going to try a suggestion involving virtual layers, but didn't fully understand which specific parts of the suggested code would need to be replaced with my layer names.

Taras
  • 32,823
  • 4
  • 66
  • 137
NiBau
  • 23
  • 4
  • 1
    First things first: Please don't post follow-up questions as answers. Instead do as you did right now, ask a new question, reference the original one and state, why that solutions doesn't work for you. So, please delete your answer in the linked question, thank you. Secondly: Please include relevant code in your question, since links may become obsolete. – Erik Jun 22 '20 at 12:00
  • Thanks for the quick responses. I deleted the follow-up question from the answers for the thread that I had referred to. Unfortunately, I can't get the code to work, which I would blame on my lack of understanding of the query.

    My point layer "PupilNo" has one column for each year (e.g. "1894") with values (>=0 ) for groups that are defined in a different column ("Society") of the point layer while my grid cell layer "1894SocietySums" only has an "id" column that I would want to link the sums by group and year to.

     – NiBau Jun 22 '20 at 12:31
  • Oh, and so far I tried:

    SELECT st_union(B.geometry), B."id", SUM(D."1894") FROM "1894SocietySums" AS B JOIN "PupilNo" AS D ON contains(B.geometry, D.geometry) GROUP BY D."Society"

    but it doesn't seem to work.

     – NiBau Jun 22 '20 at 12:36

2 Answers2

2

Let's assume there two layers "cells" and "points" with its corresponding attribute tables, see image below.

input

With the following query, it is possible to receive one column for each group with the summed values of the group that are contained within the grid cells.

SELECT c.id AS "cell_id",
       p."Groups" AS "point_gr",
       SUM(p.Data) AS "value",
       COUNT(p.id) AS "points_count"
FROM "cells" AS c
JOIN "points" AS p ON st_intersects(c.geometry, p.geometry)
GROUP BY c.id, p."Groups"

The output Attribute table will look as following

output

Note: that the output layer does not contain any geometry since it was specified.

References:

Taras
  • 32,823
  • 4
  • 66
  • 137
  • Thanks a lot, very clear and understandable!

    I think that I managed to modify the code accordingly.

    Unfortunately, however, it seems like the number of my grid cells (~30,000) slows down the process significantly to the extent that it becomes unusable. I receive a "No error" notification after around 5 minutes, but opening the attribute table or saving the virtual layer consistently crashes my pc or qgis. I am assuming that your query works, but that my dataset isnt suitable for the solution?

     – NiBau Jun 22 '20 at 14:37
1

Another solution using only native QGIS 3 tools.

Let's assume there two layers "cells" and "points" with its corresponding attribute tables, see image below.

input

Step 1. Use a 'Join attributes by location' geoalgorithm

step_1

Step 2. Apply a Filter (RMC > Filter...) to the output of the Step 1, because there can be points with no overlaps with cells. Simply where "id_2" != 'NULL'

step_2

Step 3. By means of 'Statistics by Categories' perform a grouping by two parameters of a single value.

step_3

And finally get an output in a table format.

result

Taras
  • 32,823
  • 4
  • 66
  • 137