2

I have two tables.

Table1 is having many point objects (Sample given below) Table2 shows the connectivity between the point object(using a link) and link details

My requirement is to have a new Line layer connecting point objects from Table1 as per the Link in Table2.

example : I need to have one line connecting A1 to A3 with a name of "A1_A3", another Line connecting A5 to A4 name of the line to be A5_A4)

I tried with few plugins like connectpoints & points2one... But no luck.

Points

Links

  • @GIS_Esri: Do you want cross join? or not? describe more plz. – Taras May 22 at 9:14
  • Have you had a look at connect by lines (search for connect in the tool box)? Feature names may be added afterwards using the field calculator. Could this suit your needs? – Erik May 22 at 9:25
2

In QGIS, you can create a Virtual layer (Edit Menu > Create Layer > New Virtual Layer) with the code below :

SELECT t3."SI No",
       t3."Link ID",
       t3."Link Desc",
       make_line(ST_GeomFromText(t3.ptfrom), ST_GeomFromText(t3.ptto)) AS geom
FROM (SELECT "Table2"."SI No",
             "Table2"."Link ID",
             "Table2"."Link Desc",
              ST_AsText(t1from.geometry) AS ptfrom,
              ST_AsText(t1to.geometry) AS ptto
      FROM "Table2"
      INNER JOIN "Table1" t1from ON "Table2"."From" = t1from."Object name"
      INNER JOIN "Table1" t1to ON "Table2"."To" = t1to."Object name") AS t3

Usually, the SQL query is more simple but here, I've tried to reproduce your case with two memory layers (Table1 point, Table2 no geometry) and I had to transform point geometry into text (WKT) to have both Table1 fields.

  • Does it make any difference to use an alias instead of a nested query? – Taras May 22 at 10:03
  • you can remove the outer query and build the line directly using the geometries make_line(t1from.geometry,t1to.geometry) – JGH May 22 at 11:07
  • @JGH : I know, but it doesn't work with my two memory layers. Virtual Layers didn't want to show the two geometry fields from Table1, I don't know why and this is why I translate geom in WKT and reverse operation then. – J. Monticolo May 22 at 11:16
  • @Taras : are you referring to t3? – J. Monticolo May 22 at 11:17
  • @J.Monticolo, yes – Taras May 22 at 11:18

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.