QGIS Spatial Join – Summing Attribute Values Along Line Layer

fields-attributeslineqgisspatial-joinsummarizing

I want to sum up the attribute values from a line layer along another line layer in QGIS in a direction:

The values from the orange line should be summed up along the red line, which is divided into segments between the points. The orange and red lines intersect in the blue points. The values written in red should be the result in each segment of the line.

How can I sum up the values from the orange lines along the red line?

Here you can see the dataset

Best Answer

Solution: the basic idea

Create the shortest path from each start/end of the side branch (blue houses in the screenshot below) to the last point on the main line (dark blue square) using the network/shortest path tool. Each segment of the main line then has a different number of overlapping lines: aggregate/sum the attribute values of these accumulated, overlapping lines separately for each segment.

For step-by-step implementation in QGIS, see below. Result with your dataset:

How to implement it in QGIS

Screenshot, showing the state after Step 5: the thickness of the lines represents the number of overlapping lines; the labels show the corresponding values that you sum up in Step 6:

See at the bottom for details about the general principles of the solution and the problem of your dataset that you should clean before starting.

Use Select by expression with the expression Hauptleitung = 1 and create a very small buffer (like 0.1 m) around the selected features only - we need this buffer layer in step 6
Merge selected features so that Hauptleitung is one single feature.
Create the start points for all Zuleitungen lines: Select Zuleitungen (with Hauptleitung <> 1 or Hauptleitung is NULL) and use Menu Processing > Toolbox > Geometry by expression. Check the box Selected features only and create a new point layer with the expression start_point ($geometry)
Menu Processing > Toolbox > Shortest Path (layer to point). As Vector layer with start points, select the points from Step 3. For the end-point, manually select the last point on the main pipe. To be sure to get a route for all points, set a small topology tolerance (e.g. 0.1).
Run Explode lines on the result of Step 4.
Run Menu Processing > Toolbox > Aggregate. For Group by expression, use the following expression, where Buffered is the name of the buffer layer from Step 1. For the attribute Heizlast, select Aggregate Function to sum and run the tool:
```
overlay_within('Buffered', $id)[0]
```

Details about the idea

For each of the branch lines (supply pipes, your "Zuleitungen"), create the start point (the green house icons in your screenshot, blue dots in my screenshot). From each of these points, create the shortest path along the pipes network to the end-point. This end-point should be at the end of the main pipe (your "Hauptleitung"): in your screenshot, somewhere on the red line on the right border of the image. Use the network "shortest path" tool for that. So each supply pipe after the other leads to the main pipe. Like this, along the main pipe, more and more supply lines are accumulated from left to right (towards the end-point).

Explode the resulting lines to get each segment of the main pipe between two supply pipes as a single line. Each of these segments consists of a different number of overlapping lines. Now aggregate these overlapping lines (see screenshot below), whereby you sum up the connected attribute values Heizlast to get the cumulative sum of all supply pipes: the result you're looking for.

Supply pipes dotted, main pipe in solid, thick black line; red: accumulated values of the supply line; blue: start points (step 3 below); the end-point for the shortest path tool (step 4) is on the main pipe to the right of the red 130:

Problem: get clean data

At least in an earlier version, your data I downloaded was inconsistent and messy. For the kind of task you want to undertake, it is critical to have clean data - data resulting from CAD might not be suitable for that. Be sure to have lines snapped to each other and avoid overlapping geometries. It seems data was updated meanwhile and works fine with this solution.

E.g. the feature with id=1 contained the whole length of Hauptleitung, whereas the other sections of Hauptleitung contained just the lines between two neighbouring Zuleitungen. Also, the point where Zuleitung with fid=8 met Hautpleitung did not correspond to the start/end-point of the corresponding Hauptleistungen.

Recommendation - what you need (maybe even as separate layers):

One single part line representing the main pipe. You can then explode this to get the individual segments of the main pipe.
Individual lines for supply pipes, with end-points snapped to vertices of the main pipe.

Related Solutions

[GIS] Creating points along line according to attribute data using QGIS

Here's a quick PyQGIS script which should do the trick

from qgis.core import QgsFeature, QgsVectorFileWriter, QgsGeometry

def create_points(feat,writer):
    geometry = feat.constGeometry()
    if not geometry:
        return
    length = geometry.length()
    # -----------------
    # change 'num_points' to match your field name for the number of points field
    # -----------------
    num_points = feat['num_points']
    delta = length / ( num_points + 1.0 )
    distance = 0.0
    for i in range(num_points):
        distance += delta
        output_feature = QgsFeature(feat)
        output_feature.setGeometry( geometry.interpolate(distance) )
        writer.addFeature(output_feature)

layer = iface.activeLayer()

# ---------------
# change 'd:/test_points.shp' to desired output file name
# ---------------

writer = QgsVectorFileWriter('d:/test_points.shp',None, layer.fields(), QGis.WKBPoint, layer.crs())

for f in layer.getFeatures():
    create_points(f,writer)

del writer

Just change the num_points field name and output file name to match your data, select the input layer, and run it in the python console.

[GIS] Merge adjacent parallel lines and add count as attribute in QGIS

First, you don't really want to make the lines to overlap, but only to reflect in the attributes the number of flights that are using the same path. This can be done following these steps:

Make sure you create a new column for your lines, call it "flights" and use the value 1 for all of them. I used the name "Count" which is not the best.

Then, perform a buffer operation with the desired proximity, you will have to play with the distance that you consider appropiate for your analysis: (my QGIS is in Spanish but I am sure you can figure it out)

Then, you have buffered lines that are most likely touching each other, like sausages. Your last step is to perform a spatial join, under vector, data management menu. Use the options intersect, as follows:

Make sure you add the summary option with the sum. The result will be a new column in every attribute, adding the number of other flights that are intersecting. If the lines have an ID you can join this new column to your original lines, or you can turn the buffered polygons back into lines.

If you want to simplify and remove the extra lines is a different problem, but I hope I could have helped you to start at least.