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);
;
ST_Node
the collected (!) network, dumping the points and aggregating the intersections over them would be my choice.