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); ;