2

I have two shape points as shown in image below.

  • Point 1 represents the connectivity nodes (hub)
  • Point 2 shows the address(spoke)

How to connect each hub (connectivity nodes) to nearest spoke(address) by a Virtual Layer approach? A hub can connect many nearest spokes at random distances say 50m.

The id,s in both point layers are combination of strings and integers as shown in image of question.For example the field " GlobalID" in both layers.

What I tried:

I achieve this by using MMQGIS as shown in image 2, but i am trying to do this with virtual layer approacch.

Practical example: Suppose an electrical pole (hub) is connecting many houses (spokes).

enter image description here

enter image description here

Case Msee
  • 855
  • 4
  • 11

1 Answers1

4

Let's assume there are two point layers 'points_test' (blue) and 'random_points_test' (orange) with its corresponding attribute tables, see image below.

input

One-to-One 1:1

It is an extension to this answer.

With the following query, it is possible to connect each feature in 'points_test' with one in 'random_points_test' within the distance of less than 200000m.

SELECT ST_ShortestLine(r.geometry, p.geometry) AS geom,
       p.id AS pid,
       r.id AS rid,
       ROUND(ST_Length(ST_ShortestLine(r.geometry, p.geometry)), 6) AS distance
FROM "points_test" AS p, "random_points_test" AS r
WHERE distance < 200000
GROUP BY p.id
ORDER BY MIN(distance)

The output polyline layer with its attribute table will look like:

result_1

One-to-Many 1:N

With the following query, it is possible to connect each feature in 'points_test' with many in 'random_points_test' within the distance of less than 200000m.

SELECT make_line(r.geometry, p.geometry) AS geom,
       p.id AS pid,
       r.id AS rid,
       ST_Length(make_line(r.geometry, p.geometry)) AS distance 
FROM "points_test" AS p
CROSS JOIN "random_points_test" AS r
WHERE distance < 200000

The output polyline layer with its attribute table will look like:

result_2

Taras
  • 32,823
  • 4
  • 66
  • 137
  • A blue dot (hub) is only conecting single dot (spoke). I am trying to connect blue dot with multiple orange dots bases on the distance radius as also shown in image 2 in question. – Case Msee Oct 06 '21 at 05:19
  • See my updates, I hope it is what you need. – Taras Oct 06 '21 at 05:19
  • Great. In my case, the id,s in both point layers are combination of strings and integers as shown in image of question. Will the above solution work for those ids? I belive the above solution is only for integer ids? – Case Msee Oct 06 '21 at 05:44
  • For example the field " GlobalID" in both layers in question. – Case Msee Oct 06 '21 at 05:44
  • Can you please extend your question with these details? – Taras Oct 06 '21 at 05:54
  • I have added the info of ids in question – Case Msee Oct 06 '21 at 05:57