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.

21 C h a p t e r 4 4.1 Database Design This section discusses the general design approach without reference to specific details. Refer to Appendix C for the details of the database design. Also discussed in this section are the development details as they relate to the final database design. The RID database design was completed by Cam- bridge Systematics, Inc., which served as a subcontractor to the research team. The objectives of the database design and development component of this project were to develop the technical spec- ifications and supporting management components for the RID, develop data models to define the RID, and ensure its interoperability with the NDS data. As such, the RID is designed to efficiently store the entire set of roadway data collected as part of the mobile data col- lection 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. The following were considered during the design of the RID: 1. A standard road network framework was established for each of the six study area sites. This road network frame- work is a topologically connected geospatial database and uses a standard link-node design to depict each road segment and intersection. Note that the road networks were extracted from the Esri StreetMap Premium road network. 2. From this road network framework, a framework route system was created where each route is stored as a unique linear feature. Each route was calibrated over its entire length (i.e., the start of the route has measure 0, the mea- sures increase along the route, and the final measure 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). A spatially enabled database, or geodatabase, is a database designed to store, query, and manipulate geographic data, including points, lines, and polygons. The geodatabase includes a number of classes used to store attribute and spa- tial data, relationships between classes, and domains of valid attribute values. Table 4.1 provides brief descriptions of the classes found in the RID geodatabase. The design of the RID database is a simplified version of the revised Unified Network-Transportation (UNETRANS) network data model proposed by Butler (2008). This data model offered several advantages: • It allows route overlaps (i.e., when a single road segment has two or more route designations) to be handled by creating the framework routes independent of preexisting route des- ignations. Alternative route designations (e.g., I-95, US-1) are then stored as linear events of the framework route. • It allows geographic positions to be referenced against multiple 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, normally only require a single attribute descrip- tor (e.g., functional class, pavement width, number of lanes). • Complex route events (e.g., intersections, signs, align- ments), called elements, that require multiple attribute descriptors are stored in custom tables. • It treats all route events, whether they are aspects or elements, in a similar manner. Based on the requirements of the project, the data contained within the RID database were published into a Database Design and Development

22 denormalized data warehouse (storing individual roadway attributes in their native extents) designed to improve data query and retrieval speeds. 4.2 rID Data Model examples The following sections describe the RID data model and provide examples, which include alignment, aspects, and domains. A complete description of the data model is included in Appendix C. 4.2.1 Alignment The road alignment (horizontal curves) is defined using sev- eral related attributes and is stored separately as a linear event against the routes (Figure 4.1). 4.2.2 Aspects Simple route events, called aspects, normally only require a sin- gle 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 4.2): • 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 the 2012 Highway Perfor- mance Monitoring System (HPMS) data. 4.2.3 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 because 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. 4.2.3.1 Fixed Domains Fixed domains are shown in Figure 4.3. 4.2.3.2 Dynamic Domains The codes listed within dynamic domains are dynamically inserted by scanning the S04B databases to determine unique values for the associated fields. Dynamic domains are shown in Figure 4.4. Table 4.1. RID 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 are 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.

23 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 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 4.1. S04A alignment data model. 4.3 Ongoing/Long-term rID Management The RID is static (reflecting what the roadway looked like during the NDS study period), and there is no need to maintain the database in the sense of updating or refresh- ing content. Long-term management of the RID may take the form of migrating data to newer and more efficient data- base software, which will probably emerge over the next 10 to 30 years. Additional maintenance tasks are to ensure the physical functioning of data storage and to protect the system from hazards. Assuming that the data are hosted by a commercial service provider, state DOT, or research institution as con- templated above, these are routine aspects of hosting. 4.4 rID Operation procedures The RID was built on an underlying, route-measure-based lin- ear referencing system (LRS). Other roadway features, such as those identified in the mobile data collection project, have been conflated to this LRS and are represented as individual features within an Esri ArcGIS geodatabase. Each feature also possesses corresponding LRS-based route and measure values. Therefore, the RID supports both attribute-based and spatial-based que- ries, as well as dynamic segmentation (examples of such queries are provided in Appendix F). Dynamic segmentation may be used to overlay multiple, independently maintained, and dispa- rate data sets to identify locations satisfying specific conditions. Appendix F provides a step-by-step example of how the process can be used to answer a research question.

24 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 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 4.2. S04A aspects data model.

25 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 Figure 4.3. Fixed S04A domains. (Continued on next page.)

26 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 4.3. Fixed S04A domains. (Continued from previous page.)

27 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 SignMUTCDName 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 SignMUTCDCategory Description Field type Split policy Merge policy BarrierTreatmentType Short integer Default value Default value DescriptionCode 1 Unknown Figure 4.4. Dynamic S04A domains.