Naturalistic Driving Study: Development of the Roadway Information Database (2014)

Below is the uncorrected machine-read text of this chapter, intended to provide our own search engines and external engines with highly rich, chapter-representative searchable text of each book. Because it is UNCORRECTED material, please consider the following text as a useful but insufficient proxy for the authoritative book pages.

59 A p p e n d i x C This appendix documents the activities completed under SHRP 2 Project S04A for Task 5 (Database Design and Speci- fication), which is aimed at developing the technical specifi- cations and supporting management components for the SHRP 2 Roadway Information Database (RID). This task included developing data models to define the RID and to ensure its interoperability with the Naturalistic Driving Study (NDS) database. This appendix provides the draft database design for the nor- malized RID, the S04A database, which is used for storing and maintaining the base data while maintaining data integrity. C.1 database design and Specification The objectives of Task 5 were to develop the technical spec- ifications and supporting management components for the SHRP 2 RID, develop data models to define the RID, and to ensure its interoperability with the NDS database. Because the RID is the framework to which all other data in the SHRP 2 Safety program is attached, the ultimate success and useful- ness of the SHRP 2 Safety program depends on establishing data specifications that provide some consistency across the six study area sites while recognizing and accounting for inherent differences among the data between, and even within, the NDS sites. As such, the S04A database is designed to efficiently store all the data collected as part of the S04B project, along with additional data acquired from each of the six NDS sites. The design provides a high level of flexibility in the attributes that can be stored and in the method by which those attributes are located relative to the road network. C.1.1 Assumptions Because the base road networks for each of the six NDS sites were yet to be determined before development and because the list and sources of additional attributes were still being finalized, the following basic assumptions were made: 1. A standard road network framework would be established, either purchased or developed, for each of the six study area sites. This road network framework would be a topo- logically connected, geospatial database, and would use a standard link-node design to depict each road segment and intersection. 2. From this road network framework, a framework route system would be created where each route would be stored as a unique linear feature. Each route would be calibrated over its entire length (i.e., the start of the route has measure 0, the measures increase along the route, and the final mea- sure is equal to the total length of the route). 3. Location events (i.e., signs, intersections, extents of lanes, horizontal curves) would be located using a route/measure linear referencing method (LRM). C.1.2 Design Concept A spatially enabled database, or geodatabase, is a database designed to store, query, and manipulate geographic data, including points, lines, and polygons. Within the geodatabase are a number of classes that are used to store the attribute and spatial data, relationships between classes, and domains of valid attribute values. Table C.1 provides brief descriptions of the classes found in a typical geodatabase. The design of the S04A database is a simplified version of the revised Unified Network-Transportation (UNETRANS) net- work data model proposed by Butler (2008). This data model offers several advantages: • It allows route overlaps (i.e., when a single road segment has two or more route designations) to be handled by cre- ating the framework routes independent of preexisting route designations. Alternative route designations (e.g., Database Design Specifications

60 I-95, US-1) are then stored as linear events of the frame- work route. • It allows geographic positions to be referenced against mul- tiple routes, using different linear referencing methods. • It provides flexibility by storing all simple route events within a single table. These simple route events, called aspects, nor- mally only require a single attribute descriptor (e.g., func- tional class, pavement width, number of lanes). • Complex route events (e.g., intersections, signs, alignments), called elements, that require multiple attribute descriptors, are stored in custom tables. • It treats all route events, whether they are aspects or ele- ments, in a similar manner. Based on the requirements of the SHRP 2 NDS, the data contained within the S04A database was to be published into a denormalized data warehouse designed to improve data query and retrieval speeds at the cost of increased storage. C.2 S04A data Model The following sections describe the draft S04A data model. C.2.1 Road Network All the event data within the RID is referenced against a route system, based on the defined RouteID field. This route system is built from the underlying links and nodes. This relation- ship is shown Figure C.1. As stated in the assumptions, the RouteID is not be based on preexisting route designations (e.g., I-95, US-1), but uses an incrementing key. C.2.2 LRM Locations All positions used to locate point and linear events on the net- work are defined using an LRMPosition (see Figure C.2). For linear events, the FrMeasure, FrX, FrY, ToMeasure, ToX, and ToY are defined, while for point events only the FrMeasure, FrX, and FrY are required. C.2.3 Alignments The road alignment (i.e., horizontal and vertical curves) is defined using several related attributes and is stored sepa- rately as a linear event against the routes (see Figure C.3). C.2.4 Aspects Simple route events, called aspects, normally only require a single attribute descriptor (e.g., functional class, pavement width, rumble strip location). As such, they can be stored within the same table, Aspect, that provides the following (see Figure C.4): • The spatial location for the route event; • The type of the event (which infers the event value type); and • The event value. This structure is identical to the current data model devel- oped by FHWA for submission of Highway Performance Monitoring System (HPMS) data. The list of aspect route events to be stored within the Aspect table are defined within the AspectTypes Domain table (see Figure C.11). Table C.1. Geodatabase Classes Class Description Table The Table class is the basic data storage mechanism consisting of columns, referred to as fields, and an unordered collection of rows. Table LRMPosition All point and linear event data will be stored in tables. Feature The Feature class is similar to the Table class but it is spatially enabled, allowing it to store geometry. The spatial geometry field can be one of a number of different types, including points, lines, and polygons. Point feature class GeoPosition Examples of Feature classes include roads and routes. Relationship The Relationship class defines the links between fields in two Table/Feature classes. The Relationship can simply relate the two fields in a one-to-one or one-to-many cardinality, or it can be attributed to differentiate many-to-many cardinal relationships. Relationship class Many to many LRMPositionHasGeoPosition Domain The Domain classes provide valid values for the Table and Feature class fields. The Domain classes can be of two types: • Coded: The Domain defines the list of valid choices for the field. • Range: The valid values are defined using minimum and maximum values.

61 Simple feature class nodes Contains Z values Contains M values Geometry Point No No Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Nodes OBJECTID Object ID Shape Geometry Yes Long integer Yes 0 Unique Identifier Simple feature class Routes Contains Z values Contains M values Geometry Polyline Yes No Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Routes OBJECTID Object ID Shape Geometry Yes Long integer Yes 0 Unique identifier Long integer Yes 0 Parent Route identifier ROUTENAME String Yes 64 Route Name ROUTEBASE String Yes 64 Base Route Name LENGTH Double Yes 0 0 Length of the route FRMEASURE Double Yes 0 0 From Measure TOMEASURE Double Yes 0 0 To Measure ONEWAY Short integer Yes 0 Is the route one way? Simple feature class links Contains Z values Contains M values Geometry Polyline No No Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Links OBJECTID Object ID Shape Geometry Yes LINKID Double Yes 0 0 Unique Identifier Double Yes 0 0 From Node ID Double Yes 0 0 To Node ID NAME String Yes 64 Road Name DIR_TRAVEL String Yes 2 Travel Direction (F, T or B) FUNC_CLASS String Yes 1 Functional Class X Double Yes 0 0 X coordinate of midpoint Y Double Yes 0 0 Y coordinate of midpoint ROUTEBASE String Yes 64 Base Route Name ROUTENAME String Yes 64 Route Name ANGLE Float Yes 0 0 Angle of the link Double Yes 0 0 Parent Route identifier Double Yes 0 0 Route identifier RAMP String Yes 2 Is this link a ramp? Shape_Length Double Yes 0 0 Node point Route polyline GIS length of the link Link polyline NODEID ROUTEID PROUTEID FRNODEID TONODEID PROUTEID ROUTEID Figure C.1. S04A road network data model.

62 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 To measure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data locationmethod PositionID Polyline length Figure C.2. S04A LRMPosition table. Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 To measure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data locationmethod PositionID Polyline length Table Alignment Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Alignment OBJECTID Object ID AlignmentID GUID No 0 0 38 Unique identifier GUID No 0 0 38 Identifier for LRMPosition Tangent Short integer No 0 TrueFalse 0 Tangent Radius Long integer No 0 Curve radius CurveDirectionID Short integer No Direction 0 Curve direction SuperElevation Double Yes 0 0 Super elevation Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record LRMPositionID Figure C.3. S04A alignments data model.

63 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 To measure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data location method PositionID Polyline length Table AspectType Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Aspect Type OBJECTID Object ID Short integer No 1 0 Unique identifier Name String No 32 Name of the Aspect FieldTypeID Short integer No 1 FieldTypes 0 Aspect data field type GeometryTypeID Short integer No 1 GeometryTypeID 0 Aspect geometry type DomainTypeID Short integer Yes DomainTypes 0 Aspect domain RangeMin Double Yes 0 0 Minimum range value RangeMax Double Yes 0 0 Maximum range value CodedValueTable String Yes 128 Coded Value Domain table name UseOffset Short integer Yes TrueFalse 0 Use LRM offset? Table Aspect Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Aspect OBJECTID Object ID AspectID GUID No 0 0 38 Unique identifier GUID No 0 0 38 Identifier of the LRMPosition Short integer No AspectTypes 0 Aspect Type ID ValueNumeric Double No 0 0 Aspect numeric value ValueText String No 32 Aspect text value ValueDate Date No 0 0 8 Aspect date value Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record AspectTypeID LRMPositionID AspectTypeID Figure C.4. S04A aspects data model.

64 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 To measure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data location method PositionID Polyline length Table Barrier Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Barrier OBJECTID Object ID BarrierID No 0 0 38 Unique Identifier No 0 0 38 Identifier of the LRMPosition BarrierTypeID Short integer No 1 BarrierType 0 Barrier Type StartTreatmentTypeID Short integer Yes 1 BarrierTreatmentType 0 Treatment Type at the start of the Barrier EndTreatmentTypeID Short integer Yes 1 BarrierTreatmentType 0 Treatment Type at the end of the Barrier PostMaterialID Short integer No BarrierPostMaterial 0 Post Material RubrailPresent Short integer Yes 1 TrueFalse 0 Is a Rub Rail present? Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record LRMPositionID Figure C.5. S04A barriers data model. C.2.5 Barriers The barriers along the roads are defined using several related attributes and are stored as a linear event against the routes (see Figure C.5). C.2.6 Intersections Intersections are complex, as they need to be referenced against each route that intersects at the intersection. As such, the intersections are referenced as point events against each of the routes through the RouteIntersection table (see Figure C.6). C.2.7 Lanes Each segment of the road network can have multiple lanes of different types and widths. These lanes are linearly referenced against the routes as linear events (see Figure C.7). C.2.8 Locations Each section of the road network is reviewed to capture grade and cross slope. These locations are linearly referenced against the routes as linear events (see Figure C.8). C.2.9 Shoulders Each segment of the road network can have a defined shoul- der of various types. These shoulders are linearly referenced against the route system as linear events. Additional fields in the LRMPosition table identify which side of the road the shoulder is located on, as seen in Figure C.9. C.2.10 Signs Each sign is associated with a sign assembly that is linearly referenced against the route system as a point event. A sign assembly can have multiple signs (see Figure C.10).

65 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 Tomeasure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data location method Table Intersection Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Intersection OBJECTID Object ID GUID No 0 0 38 Unique identifier ControlTypeID Short integer No 1 IntersectionControlType 0 Intersection Control Type Approaches Short integer No 4 0 Number of approaches to the Intersection Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record Table RouteIntersection Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Route Intersection RouteIntersectionID GUID No 0 0 38 Unique identifier GUID No 0 0 38 Identifier for Intersection GUID No 0 0 38 Identifier for LRMPosition OBJECTID Object ID IntersectionID LRMPositionID PositionID IntersectionID Polyline length InterectionNumApproaches Figure C.6. S04A intersections data model.

66 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 To measure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data locationmethod PositionID Polyline length Table Lane Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Lanes OBJECTID Object ID LaneID GUID No 0 0 38 Unique identifier GUID No 0 0 38 Identifier for the LRMPosition Width Double No 0 0 Lane width ThroughLanes Short integer Yes 0 0 Number of Through Lanes LeftTurnLanes Short integer Yes 0 0 Number of Left Turn Lanes RightTurnLanes Short integer Yes 0 0 Number of Right Turn Lanes CenterTurnLane Short integer No 0 0 Number of Center Turn Lanes AccelerationLane Short integer No 0 0 Number of Acceleration Lanes DecelerationLane Short integer No 0 0 Number of Deceleration Lanes AccelDecelLane Short integer No 0 0 Number of Acceleration/Deceleration Lanes Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record BikeLane Short integer Yes 0 Number of Bike Lanes LRMPositionID Figure C.7. S04A lanes data model.

67 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 To measure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data locationmethod PositionID Polyline length Table Location Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Location OBJECTID Object ID LocationID No 0 0 38 Unique Identifier No 0 0 38 Identifier of the LRMPosition Grade Double Yes 0 0 Road grade CrossSlope Double Yes 0 0 Road cross slope Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record LRMPositionID Figure C.8. S04A locations data model.

68 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 Tomeasure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data location method PositionID Polyline length Table Shoulder Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Shoulder OBJECTID Object ID ShoulderID GUID No 0 0 38 Unique identifier GUID No 0 0 38 Identifier of the LRMPosition ShoulderTypeID Short integer No 1 ShoulderType 0 Shoulder Type Width Double No 0 0 ShoulderWidth Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record LRMPositionID Figure C.9. S04A shoulders data model. C.2.11 Domains The domains within the RID define the valid values for a sub- set of the fields within the S04A data model and are used for efficiency, as they allow numeric IDs to be stored within the tables rather than repeated strings. The domains fall into two categories: 1. Fixed domains: The values within the domain do not change between RID databases. 2. Dynamic domains: The values within the domain are dynamically populated based on the loaded data. C.2.11.1 Fixed Domains Fixed domains are shown in Figure C.11. C.2.11.2 Dynamic Domains The codes listed within these domains are dynamically inserted by scanning the S04B databases to determine unique values for the associated fields. Dynamic domains are shown in Figure C.12. C.3 S04B databases The SHRP 2 S04B Mobile Data Collection project is focused on the deployment of a roadway measurement system to col- lect, at highway speed, roadway-feature data on selected roads within the study sites. The data includes the following: • Tier 1 44 Horizontal curvature: Radius, length, point of curvature (PC), point of tangency (PT), and direction of curve (left or right based on driving direction). 44 Grade. 44 Cross slope/superelevation. 44 Lane: Number, width, and type (e.g., through, turn, pass- ing, acceleration, carpool). 44 Shoulder type/curb (and paved width if it exists). 44 Sign location. 44 MUTCD signs.

69 Table LRMPosition Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls LRM Positions on Routes OBJECTID Object ID GUID No 0 0 38 Unique identifier RouteID Long integer No 0 Route identifier FrMeasure Double No 0 0 From measure ToMeasure Double No 0 0 To measure SideOfRoad String No SideCode 1 Side of Road OffsetReferent Short integer No 0 Referents 0 Lateral Offset Referent OffsetDir Short integer No 0 0 Offset direction OffsetDistance Double No 0 0 0 Offset distance Length Double Yes 0 0 0 FrX Double Yes 0 0 Point or start of polyline x coordinate FrY Double Yes 0 0 Point or start of polyline y coordinate ToX Double Yes 0 0 End of polyline x coordinate ToY Double Yes 0 0 End of polyline y coordinate LocationMethod Short integer Yes LocationMethod 0 Data locationmethod PositionID Polyline length Table SignAssembly Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Sign Assembly OBJECTID Object ID GUID No 0 0 38 Unique identifier GUID No 0 0 38 Identifier of the LRMPosition NumSigns Short integer No 1 0 Number of Signs on the Assembly SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record LRMPositionID Table Sign Data typeField name Prec ision Scale LengthDomainDefault value Allow nulls Sign OBJECTID Object ID SignID GUID No 0 0 38 Unique Identifier GUID No 0 0 38 Identifier for Sign Assembly MUTCDCodeID Short integer No SignMUTCDCode 0 MUTCD Code MUTCDNameID Short integer No SignMUTCDName 0 MUTCD Name MUTCDCategoryID Short integer No SignMUTCDCategory 0 MUTCD Category Message String No 32 Message on the Sign Comment String Yes 255 Comment SourceID String Yes 32 Source of the data DataSourceID Short integer No 1 DataSource 0 ID of the source record DataDate Date Yes 0 0 8 Date of the source record SignAssemblyID SignAssemID Figure C.10. S04A signs data model.

70 Figure C.11. Fixed S04A domains. (Continued on next page.) Coded value domain AspectTypes Description Field type Split policy Merge policy Name Short integer Default value Default value DescriptionCode 1 Route Name 2 AADT 3 Functional Class 4 Lighting 5 Median Strip 6 Rumble Strip 7 IRI 8 Speed Limit Coded value domain Datums Description Field type Split policy Merge policy Short integer Duplicate Default value DescriptionCode 1 State Plane 2 WGS 84 0 Unknown Coded value domain Direction Description Field type Split policy Merge policy Short integer Duplicate Default value DescriptionCode 1 Left 0 None 1 Right Coded value domain DomainTypes Description Field type Split policy Merge policy Domain Types Short integer Default value Default value DescriptionCode 0 None 1 Range 2 Coded Value Coded value domain GeometryTypeID Description Field type Split policy Merge policy Short integer Default value Default value DescriptionCode 1 Point 2 Polyline Range domain InterectionNumApproaches Description Field type Split policy Merge policy Short integer Default value Default value Maximum valueMinimum value 0 10 Coded value domain LocationMethod Description Field type Split policy Merge policy Method used for locating the polyline Short integer Default value Default value DescriptionCode 1 End points on same route 2 End point on adjacent routes 3 Shortest path 1 Original and Conflated polyline length differ by more than20% 2 End point(s) failed to snap tonetwork within tolerance 3 Failed to find shortest path betweenend points Coded value domain OffsetDirections Description Field type Split policy Merge policy Short integer Duplicate Default value DescriptionCode 0 None 1 In 2 Out 3 North 4 East 5 South 6 West 7 Toward 8 Away From

71 Coded value domain FieldTypes Description Field type Split policy Merge policy Short integer Duplicate Default value DescriptionCode 1 Short Integer 2 Integer 3 Long Integer 5 String 6 Float 7 Double 8 ObjectID 9 DateTime 10 GUID 11 XML 12 Blob Coded value domain Referents Description Field type Split policy Merge policy Short integer Duplicate Default value DescriptionCode 0 None 1 Edge of Pavement 2 Face of Curb 3 Back of Curb 4 Back of Sidewalk 5 Edge of Right of Way 6 Centerline of Road 7 Centerline of Ditch Coded value domain SideCode Description Field type Split policy Merge policy String Duplicate Default value DescriptionCode L Left Side Only R Right Side Only B Both Sides & Street S Street Only N Both Sides & Not Street U Unknown C Center Coded value domain TrueFalse Description Field type Split policy Merge policy Short integer Default value Default value DescriptionCode 1 True 0 False 1 True Figure C.11. Fixed S04A domains. (Continued from previous page.) 44 Barriers and guardrail. 44 Intersection location. • Tier 2 44 Intersections: Number of approaches, and control (uncon- trolled, all-way stop, two-way stop, yield, signalized, round- about). Ramp termini are considered intersections. 44 Median presence: Type (depressed, raised, flush, barrier). 44 Rumble strip presence: Location (centerline, edgeline, shoulder). 44 Lighting presence. These data are provided as a Microsoft Access database in a standard format and are processed directly from this database. C.3.1 ETL Routine Extract-transform-load (ETL) routines developed as part of this task are used to load the collected data from the Microsoft Access databases into the RID. These ETL routines include the following: • Spatial locations are referenced against the underlying route system and references loaded into the LRMPosition table. • The alignment, intersection, lane, sign, and shoulder events are loaded into the appropriate custom Element route event tables. • The remaining collected data (i.e., median type, grade, light- ing, rumble strip location) are loaded into the Aspect route event table. C.4 Hardware and Software The physical assembly of the new server infrastructure is complete. Some electrical system upgrades were needed to accommodate the universal power supply (UPS) unit.

72 Coded value domain BarrierPostMaterial Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain BarrierTreatmentType Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain BarrierType Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain IntersectionControlType Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain MedianType Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain ShoulderType Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain SignMUTCDCategory Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain SignMUTCDCode Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Coded value domain SignMUTCDName Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Figure C.12. Dynamic S04A domains.

73 Figure C.13. SHRP 2 SQL server. With the system physically in place and all power upgrades completed, the SHRP 2 S04A SQL server setup was com- pleted and is fully functional. The storage array system setup and implementation was completed in January 2011. Figure C.13 shows the server diagram. C.5 Reference Butler, J. A. 2008. Designing Geodatabases for Transportation. Esri Press, Redlands, Calif.