2

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

enter image description here

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?

3

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;
  • Do you know if it would be possible to get a sum of an attribute present in each polygon by using Postgis? I was able to get a count by using MMQGIS > combine tool but it doesnt get the individual attributes – Luffydude Jul 19 '16 at 13:04
  • 1
    Easy. SUM(circle.attribute_name) – HeikkiVesanto Jul 19 '16 at 13:21
  • Also, you don't even need PostGIS to do this, you could do it in SpatialLite as well, it might be a bit slower but if you don't have PostGIS set up. You can check out the following for how to get your data in: gisforthought.com/centroid-within-selection-in-qgis – HeikkiVesanto Jul 19 '16 at 13:25
  • Okay I'll try out your pg solution but how would I use the result back on qgis? – Luffydude Jul 19 '16 at 13:37
  • 1
    Two options. Either use the DB Manager from QGIS which can load the results of queries as layers. Or use a create table query and load the resulting table. You need to select the geometry from the line as well if you want to load it into QGIS. I have modified the query. – HeikkiVesanto Jul 19 '16 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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