2

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.

6
  • If you export your layer to a new one, then run the QGIS delete duplicate geometries tool on the new layer does that work?
    – johns
    Jul 5 at 22:35
  • Looks like the join conditions are always coming up equal. What's the data source - shapefiles, postgis, etc?
    – jbalk
    Jul 6 at 4:55
  • @johns When I export the file, it is the same.
    – mdgcrks
    Jul 6 at 6:23
  • @jbalk they are geojson files.
    – mdgcrks
    Jul 6 at 6:23
  • 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) ?
    – jbalk
    Jul 6 at 15:26

1 Answer 1

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.

1
  • it works perfectly, thank you very much!
    – mdgcrks
    Jul 6 at 6:26

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.