Joining hub and spoke points by lines using Virtual Layer

Question

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).

Answer 1

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

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:

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: