Duplicated attributes problem while adding attributes of points to attribute table of lines in QGIS

Question

I am trying to add the id numbers of my points to the attribute table of my lines.

I used this post [https://gis.stackexchange.com/a/355320], which solves the problem. However, the resultant layer has way more line features than the original file. The attribute table consists of all possible combinations of starting and ending ids, e.g., N1-N1, N1-N2, N1-N3,…N1, Nk, N2-N1,….). In Image 2 below, you can see the generated attribute table.

I tried to delete duplicated features using MMQGIS and Grass v.clean tools, but QGIS crashed every time.

Is there another way to delete these duplicates?

Would it be possible to add additional query lines to this query that deletes duplicates at the end? If yes, would you share any examples? I am quite new to SQL queries.

Answer 1

Open the field calculator in the lines layer and calculate the fields with this expression.

For the start of the line:

aggregate(
    'Points', -- replace Points with the points layer name/id
    'array_agg',
    "ID", -- put inside the quotes the points id field
    with_variable(
        'parent_geom',
        start_point(
            geometry(
                @parent
            )
        ),
        x(@parent_geom) = $x and y(@parent_geom) = $y
    )
)[0]

For the end of the line:

aggregate(
    'Points', -- replace Points with the points layer name/id
    'array_agg',
    "ID", -- put inside the quotes the points id field
    with_variable(
        'parent_geom',
        end_point(
            geometry(
                @parent
            )
        ),
        x(@parent_geom) = $x and y(@parent_geom) = $y
    )
)[0]

Remember that if there is more than one point in the start/end of a line only one id will be taken from the points layer. If you want to handle this you can create a field of list type and remove from the above expression the [0] at the end.