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
This is a 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:
