PostGIS - Add Column of Lines that Intersect Point

Question

New to PostGIS. I have a road network which I have self-intersected/inner joined to generate nodes at each intersection.

I would like to add a column to the junctions table which lists all of the lines that intersect the generated node. I'm removing duplicate geometries, so at currently pulling the road_id's from the input data ends up with two identifiers, even when junctions have 3+ roads.

Is it best to not remove duplicates upon initial creation, and then alter table to remove duplicates and concatenate the road_ids into a single column? Or can the code be adjusted to do this on creation?

Have searched but can't find anything.

EDIT: Code used to create point geomteries below.

CREATE TABLE junctions as
SELECT 
 ROW_NUMBER() OVER () AS junction_id,
 ST_Intersection(a.geom, b.geom) AS geom
FROM roads a
INNER JOIN roads b
 ON a.geom && b.geom
 AND ST_Intersects(a.geom, b.geom)
 AND a.road_id != b.road_id
GROUP BY
 ST_Intersection(a.geom, b.geom);
;

• Zoltan: If you change "AND a.road_id != b.road_id" to "AND a.road_id > b.road_id" you can avoid the group by clause. It is not clear for me why do you have only line identifiers in the intersection of 3+ roads?
• MAC: Thanks for your repsonse and advice on the code. That section of the question is potentially poorly worded - currently my attempts to create columns with all road_id's for each junction by calling a.road_id and b.road_id results in two id's per geometry - fine if there are only two roads, but if there are more then some id's are "lost" by removing the duplicates.
• MAC: Realised I didn't make clear that I want to remove duplicate geometries, whilst keeping/merging all the road_id information at each intersection.
• geozelot: The core issue here is that always only two geometries are compared; it is impossible to aggregate all possible intersections per intersection of two! You will need to first extract a distinct list of intersection points, and collect all intersecting lines per point; and a heads up on that: you will likely encounter floating point precision issues. ST_Node the collected (!) network, dumping the points and aggregating the intersections over them would be my choice.
• JGH: On a side note, beware of strange roads: self intersections, multiple intersections between 2 roads etc. Removing duplicates by road ID may loose quite a bit of them, you may need to consider the locations instead of the IDs.

Answer 1

You can use array_agg to collect the line IDs at the intersection point. The number of lines can be more then two, so they can be collected in an array ba array_agg function:

CREATE TABLE junctions as
SELECT 
 ROW_NUMBER() OVER () AS junction_id, 
 array_agg(b.road_id) AS road_ids,
 ST_Intersection(a.geom, b.geom) AS geom
FROM roads a
INNER JOIN roads b
 ON a.geom && b.geom
 AND ST_Intersects(a.geom, b.geom)
 AND a.road_id != b.road_id
GROUP BY
 ST_Intersection(a.geom, b.geom);

• MAC: Brilliant, updated it to ARRAY_AGG(DISTINCT(b.road_id)) for neatness, but this was exactly what I was looking for. Thanks for your help.
• Zoltan: Please accept my answer clicking on the checkmark :)