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.Image 1 - my lines and points

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. In the image below

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.

• John: If you export your layer to a new one, then run the QGIS delete duplicate geometries tool on the new layer does that work?
• jbalk: Looks like the join conditions are always coming up equal. What's the data source - shapefiles, postgis, etc?
• mdgcrks: @johns When I export the file, it is the same.
• mdgcrks: @jbalk they are geojson files.
• jbalk: I know the field calculator solved your problem, but out of curiosity, I'm wondering if the virtual layer will work if you change start_point(l.geometry) and end_point(l.geometry) to st_startpoint(l.geometry) and st_endpoint(l.geometry) ?

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.