1

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);
;
Improve this question
5
  • 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? – Zoltan May 5 '20 at 8:06
  • 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 May 5 '20 at 8:21
  • Realised I didn't make clear that I want to remove duplicate geometries, whilst keeping/merging all the road_id information at each intersection. – MAC May 5 '20 at 8:38
  • 2
    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. – geozelot May 5 '20 at 10:59
  • 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. – JGH May 5 '20 at 12:27
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);
Improve this answer
2
  • 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. – MAC May 5 '20 at 15:59
  • Please accept my answer clicking on the checkmark :) – Zoltan May 5 '20 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.