Once we have a set of intersections, we find the connections between these intersections. This should be the data in the TIGER_01 table, but we are going to create out own data set in case there are any errors in the TIGER data. We need these segments and intersections to calculate routes using graphs, nodes and edges. The link table can be created with this:
CREATE TABLE [dbo].[links](
[tlid] [int] NOT NULL,
[intersectionId1] [int] NULL,
[intersectionId2] [int] NULL,
[distance] [float] NULL,
[time] [int] NULL
) ON [PRIMARY]
tlid is a field in the TIGER files and is a unique identifier for a road. the intersectionIds are found in the intersections table. The distance and time will be calculate later.
The links can be calculate with this command:
INSERT INTO links ( tlid, intersectionId1, intersectionId2 )
SELECT tlid, id1.intersectionId, id2.intersectionId
FROM TIGER_01
JOIN intersections AS id1 ON ( TIGER_01.FRLAT = id1.lat AND TIGER_01.FRLONG = id1.long )
JOIN intersections AS id2 ON ( TIGER_01.TOLAT = id2.lat AND TIGER_01.TOLONG = id2.long )