Counting attributes from line intersections with polygons on QGIS?

Question

I have a line layer with 1300 features and a polygon layer with 19,000 features that represents buffers of points.

I need to determine how many polygons does each individual line intersect. And have a column in attributes with "buffer intersections" and another one with sum of attribute X

Like in the below example

The line in the right intersects 3 circles so it would have a value of 3 while the one on the left would have a higher value due to intersecting a lot of circles. It was possible to get this count by using the Combine tool in MMQGIS.

Let's say each circle has a value between 10-100 of attribute X, how to get the sum of this value in each line?

Is there any tool that does this or some other way by using QGIS?

Answer 1

This would be extremely simple in PostGIS:

SELECT line.unique_name, line.geom, COUNT(circle.geom), SUM(circle.attribute_name)
FROM line
JOIN circle ON ST_Intersects(line.geom, circle.geom)
GROUP BY line.unique_name, line.geom;

In QGIS 2.14 there is the option of creating a "Virtual Layer" which might work for this. But the performance is not as good with larger layers.

To create a table:

CREATE TABLE table_name AS
SELECT line.unique_name, line.geom, COUNT(circle.geom), SUM(circle.attribute_name)
FROM line
JOIN circle ON ST_Intersects(line.geom, circle.geom)
GROUP BY line.unique_name, line.geom;