Creating duplicate points as many times as it overlap with polygons in QGIS

Question

Let's assume there two layers, see image below

• a point layer 'random_points_test' with 10 features
• a polygon layer 'test' with three features

I am trying to modify the number of features in the point layer based on a number of overlaps with polygon features. So, a point feature that overlaps three polygon features will now exist three times in the attribute table and so on.

Of course, I am aware of the "Intersection" tool, however, it drops features where the overlap does not exist, see image below.

I can do it with a Virtual Layer, like this:

SELECT
    a.id AS "a_id",
    b.fid AS "b_id",
    a.geometry as geom
FROM
    "random_points_test" AS a,
    "test" AS b
WHERE
    ST_Intersects(a.geometry, b.geometry)
UNION
SELECT
    a.id AS "a_id",
    '' AS "b_id",
    a.geometry as geom
FROM
    "random_points_test" AS a
WHERE
    a.id NOT IN (
    SELECT
        a.id
    FROM
        "random_points_test" AS a,
        "test" AS b
    WHERE
        st_intersects(a.geometry, b.geometry)
    GROUP BY
        a.id)

So, this is my desired output:

Another solution might be to build a model in the Graphical Modeler, where I will need to (1) split features into two groups with overlap and without, then (2) proceed with the "Intersection" geoalgorithm for the first group, and finally (3) merge them back into one layer. So, I was wondering if there is a smarter native solution to achieve the desired output?

Answer 1

The "Join Attributes by Location" tool, with a 'one-to-many' join type, should give your desired result.