With the exception of 13 features that were not identified with the Tiger/Lines, Hazmat routes were created by extracting the TIGER/Line segments that corresponded to each individual route. Hazmat routes in the NTAD, are organized into 3 database files, hazmat.shp, hmroutes.dbf, and hmstcnty.dbf. Each record in each database represents a unique Tiger/Line segment. These Tiger/Line segments are grouped into routes identified as character strings in the ROUTE_ID field in the hmroutes.dbf table. The route name appearing in the ROUTE_ID is assigned by FMCSA and is unique for each State [this sentence could be deleted - it doesn't add a lot to it]. The hmstcnty.dbf table allows the user to select routes by State and County. A single shapefile, called hazmat.shp, represents geometry for all routes in the United States.
First the primary key field (FID) was duplicated within hazmat table. The field was named PK and now acts as the primary key for the table. Then the table was run through a program that identified which records were duplicates. For each duplicate record the appropriate Primary Key was assigned.
Next the hmroutes table was created. The RouteID and RouteType fields were placed in this table along with FK field. The FK field was populated with the PK values of the hazmat table and will serve as the foreign key field. The ROUTEID field was a concatenation of the State Abbreviation, the Route Type and the Route Number. The table was run through a program that parsed and stored the state abbreviation, the route type and route number into separate fields.
Then the hmstcnty table was created. The STCNTY field was placed in this table along with FK field. The FK field was populated with the PK values of the hazmat table and will serve as the foreign key field. The STCNTY field was a concatenation of the State FIPS and the County FIPS codes. The table was run through a program that parsed and stored the state FIPS and county FIPS into separate fields.
Finally the duplicate records in the hazmat table were deleted.
The hazmat table stores the basic spatial data for the Hazardous Materials information. The hmroutes tables stores the routing information and the hmstcnty table holds the state and county information. All of the data are not stored in a single table because it is inefficient.
To integrate the hmroutes table with the hazmat table you must create a relationship between the two. This relationship is defined by hazmat's PK field and the hmroute's FK field. More specifically for each PK value in the hazmat table there are one or more FK values in the hmroutes table.
To integrate the hmstcnty table with the hazmat table you must create a relationship between the two. This relationship is defined by hazmat's PK field and the hmstcnty's FK field. More specifically for each PK value in the hazmat table there are one or more FK values in the hmstcnty table. For information on how to create a relationship between tables search for the term "relate" in the help file of your GIS application.