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

1 Answer 1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.