SQL advanced -> Graph example: Road network. Part 3

After showing basic facts about road network of Hatsfield city in Part 1, we want to analize our graph conditions and found out if it includes pseudo junctions ? Pseudo junction is unnecessary for graph definition. For example it can be a meeting point of two connected road links. This point can be removed, two links merged, and our road network will continue to function properly as before.

Find pseudo junctions:
SELECT a.juncno, count(*) AS cn
FROM
  (SELECT TJunc AS juncno
   FROM roads
   UNION ALL
   SELECT FJunc AS juncno
   FROM roads) AS a
GROUP BY a.juncno
HAVING count(*) = 2;


Results:
juncnocn
132
182



Ok. Junction 13 and 18 are unnecessary and can be removed.



sqlexamples.info