Warnock Inc. Technology Consultation for the Future

Once we have the road segment data added, we can move on to processing the data. While road segments are important, we also need to know where these segments intersect. To find this, we take all the start and end points and assume these are intersections.

The intersection table can be created with this command:

CREATE TABLE [dbo].[intersections](
[intersectionId] [int] IDENTITY(1,1) NOT NULL,
[lat] [int] NULL,
[long] [int] NULL
) ON [PRIMARY]

and import the intersections from the TIGER_01 table and put them in the intersections table. We are only interested in roads. Roads are indicated by the CFCC field when the entry starts with an "A". We will learn more about types of roads later.

INSERT INTO intersections ([lat],[long])
SELECT DISTINCT FRLAT, FRLONG FROM TIGER_01 WHERE [CFCC] LIKE "A%"
INSERT INTO intersections ([lat],[long])
SELECT DISTINCT TOLAT, TOLONG FROM TIGER_01 WHERE [CFCC] LIKE "A%"

It is run twice, once w/ the start points and once with the end points.