How to join attributes of points with several overlapping polygons?

Question

I am making a road atlas using QGIS, where the pages overlap:

I would like the intersection of the roads and the edge of each atlas page (for example, Point A and Point B) to tell which pages you should turn to to continue following that road:

That is, each intersection point needs to have a field telling which page(s) it is contained in, like this:

          contained_in
-----------------------
Point A   Page and 3
-----------------------
Point B   Page 2

Doing a "join attributes by location" with the pages creates several versions of the same point:

          contained_in
-----------------------
Point A   Page 2
-----------------------
Point A   Page 3
-----------------------
Point B   Page 2
-----------------------
Point B   Page 2

This leaves me with many duplicates, and I can't figure out how to combine them. How can I make a point tell all of the polygons which contain it?

Answer 1

I tried to recreate this situation using a polygon layer page with field page_id, and a points layer intersections with field id. This particular snippet of code should be more directly applicable than the answer I linked to earlier.

Create a new Virtual Layer.

Under Query, enter something like this, swapping in the relevant field/layer names as necessary:

SELECT c.geometry, c.id, group_concat(p.page_id) AS PAGE_ids
FROM page p, intersections c
WHERE st_contains(p.geometry, c.geometry)
GROUP by id;

This should give you a virtual layer with the field PAGE_ids, separated by commas. Should look like this:

Now open up the layer styling for the virtual layer, enable lables, and enter the following expression: concat('See page(s) ',"PAGE_ids"). Now you should have your points set up more or less how you describe in the question.

Getting them to display properly in your atlas is another matter, but you'll at least have the desired information in a single field, and can work from there.