# Joining hub and spoke points by lines using Virtual Layer

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

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:

• 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. Commented Oct 6, 2021 at 5:19
• See my updates, I hope it is what you need. Commented Oct 6, 2021 at 5: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? Commented Oct 6, 2021 at 5:44
• For example the field " GlobalID" in both layers in question. Commented Oct 6, 2021 at 5:44
• Can you please extend your question with these details? Commented Oct 6, 2021 at 5:54