I have 3 layers ( 1 line and 2 points) as shown in image. The layer id,s are mixture of integers and text such as cable4389175, node1 , service_point5316. I connect the two point layers (node and service_point) with line by using the IDs. I got the connections but the attribute table is not showing the the data of starting(fromNode) and ending points (toNode) of connecting line. For instance, node1 is connected with service_point53168 by forming a line (cable4389175).

I have also attached a demo plot to demonstrate what I am trying to achieve.

  • Is there a plugin or solution that will connect the points with line and also save the starting and ending points in the attribute table?

enter image description here

enter image description here

  • It is unclear whether you already have got the red line layer, or are creating it. If second option, how did you create it?
    – Erik
    Dec 16 '20 at 13:10

This can be achieved using a virtual layer.

Go the the menu layer / add layer / add-edit virtual layer and enter the following query. You would have to change the layer name and field name(s) to suit your needs.

The output is a dynamic layer, which you can then export (Shapefile or else).

SELECT c.*, n.id as fromNode, s.id as toNode
FROM cableLayer c
  LEFT JOIN nodeLayer n ON ST_INTERSECTS(ST_STARTPOINT(c.geometry),n.geometry)
  LEFT JOIN serviceLayer s ON ST_INTERSECTS(ST_ENDPOINT(c.geometry),s.geometry)

If you want to get this information only for cables linking a node to a service (no node-node cables), replace the LEFT JOIN by regular JOIN

  • Interesting approach, thanks! I'm puzzled, however, about the use of the asterisk in the first line. Could you elaborate?
    – Stu Smith
    Dec 16 '20 at 19:22
  • @StuSmith c.* means "all fields from table alias c", which in turns point to the layer cableLayer
    – JGH
    Dec 16 '20 at 19:34
  • Got it. Also, I suspect that your approach requires that each line in the cable layer was correctly digitized in a from-to "downstream" sequence. Correct?
    – Stu Smith
    Dec 16 '20 at 19:42
  • @StuSmith yes. If not then 4 joins would be required, with a coalesce() on both end points to keep the not-null one
    – JGH
    Dec 16 '20 at 20:14
  • @JGH. I have 6 points in one layer as shown in the first image. I am trying to: (1) connect points with lines. (2) Save data of line connections (From_point and To_point) in the seperate layer. I tried to show manually here i.stack.imgur.com/25HKy.png
    – Case Msee
    Jan 14 '21 at 13:27

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.