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

Here we have some data that describes road network in imaginary city Hatsfield. The streets (roads) are represented by lines (links) that intersects in junctions. Streets links are numbered and uniquely identified by link_id number. Junctions identifier is JuncNo. It is referenced by fjunc (from-junction) and tjunc (to-junction) columns from the Roads table.


Roads table
link_idfjunctjuncroadnamelink_len...
112188Archibald St29.39...
113819Archibald St34.56...
1193012Factory Yard157.48...
12278High Amplitude Avenue31.30...
..................


Junctions table
JuncNoTrafficLight
81
100
110
120
......


Junctions table structure:
CREATE TABLE junctions (
[JuncNo] INTEGER,
[TrafficLight] INTEGER,
[JuncName] TEXT(25));


Roads table structure:
CREATE TABLE roads (
[link_id] INTEGER,
[FJunc] INTEGER,
[TJunc] INTEGER,
[roadname] TEXT(60),
[link_len] DOUBLE,
[cityname] TEXT(60),
[flnum] INTEGER,
[tlnum] INTEGER,
[frnum] INTEGER,
[trnum] INTEGER,
[zipcode] TEXT(12));


This piece of Road network of Hatsfield supposed to be a Flat Graph. That means it is build as an integrated network, and you can create route from any given junction (begin point) to any other (destination) junction by chosing an apropriate sequence of connected links.



sqlexamples.info