Identification of Utility Conflicts and Solutions: Pilot Implementation of the SHRP 2 R15B Products at the Maryland State Highway Administration (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.

CHAPTER 3 Utility Conflict Data Model and Database Introduction The research team updated the UCM data model and Access database to reflect suggestions from MDSHA district officials on the usability of the UCM approach as well as lessons learned by members of the research team as part of other research initiatives, in particular related to the development of generalized inventories of utility facilities within the highway right-of-way. Part of this effort involved developing data entry forms in Access to manage information about projects, utility owners, utility facilities, and utility conflicts. This chapter describes the various components of the UCM data model and database, including the following: • Business process model. This model describes the process to identify and manage utility conflicts. • Conceptual model. This model is a high-level representation of groups of objects or entities that are needed to manage utility conflicts. This model also provides a high-level representation of relationships between those objects or entities. • Logical data model. This model is a representation of data characteristics and relationships at a level that is independent of any physical implementation. • Physical data model. This model is a representation of data characteristics and relationships that depends on the specific physical platform chosen for its implementation. For this project, the research team prepared physical data models in Access and Oracle. • Data entry forms. These Microsoft Access forms enable the management of information about projects, utility owners, utility facilities, and utility conflicts. Stand-alone versions of these model components have been prepared (delivered separately). Business Process Model Figure 3.1 provides a graphical representation of the traditional design-bid-build project development and delivery process. The diagram depicts both phases (planning, preliminary design, and so on) as well as major functional areas (environmental, real property, utilities, and so on). The model corresponds to the general case in which a project goes through all phases of development and delivery. In reality, the type of project drives what phases and activities are involved. Figure 3.1 also shows a zoomed-in view of the project development and delivery process that focuses on utility activities. To function properly, the utility process needs utility data input, which occurs at different times of the process. Typically, as time progresses, utility information becomes more detailed and precise. Other elements of the utility process include 31

coordination of utility relocation activities, preparation and execution of utility agreements, preparation of utility certifications, and monitoring of utility relocations and reimbursement of utility owners. Figure 3.1. Utility process within the project development and delivery process (PS&E = plan, specifications, and estimate). Alternative Analysis and Preliminary Plans Environmental Process Utility Conflict Analysis, Permits, Relocation, and Reimbursement Property Acquisition and Relocation Assistance Design and PS&E Assembly Letting Construction Planning Preliminary Design Detailed Design Letting Construction Post Construction Property Management Planning linkages Definition, Selection, Financing, Sched. Environmental Commitments Preliminary Utility Conflict Analysis Right-of-Way Map Development Agreements, Scope Update Construction authorization Environmental reevaluation Environmental approval Right-of-way authorization Project Management 30% design 60% design 90% design 15-20% design 0% design 32

Utility conflict management is a multistage activity within the utility process. Although every project is different, a generalized description of the major utility process stages follows. Utility Process: Stage 1 Stage 1 corresponds to the beginning of the process when potential utility conflicts are identified for the first time. It involves the following activities: • Conduct preliminary investigation based on existing records. This corresponds to a quality level D (QLD) investigation. • Assess potential impact. For each potential conflict, determine whether the utility is in conflict or whether more accurate data are needed to make a determination. Depending on project specifics, this assessment can occur with input from utility owners or can be performed by an internal DOT review team. An initial utility coordination meeting is advisable at this point. Results of the assessment should be communicated with utility owners. Utility Process: Stage 2 Stage 2 corresponds to the part of the process (typically at the end of the preliminary design phase or beginning of the design phase) when the agency collects detailed survey data, including visible utility appurtenances. It includes the following activities: • Survey visible utility appurtenances. The survey should include all aboveground utility facilities, such as poles, guy wires, manholes, and valves. This corresponds to a quality level C (QLC) investigation when correlated to belowground utility facilities. • Assess potential impacts. For each potential conflict, determine whether the utility is in conflict or whether more accurate data are needed to make a determination. For belowground installations, QLB data might be needed to make that determination. In that case, the assessment should list the locations where the QLB data are needed. • Analyze and review utility conflict resolution strategies, including the option to make changes to the highway design. Depending on project specifics, this assessment can occur with input from utility owners or can be performed by an internal agency review team. This stage should include a utility coordination meeting to discuss conflicts and potential strategies. Results of the assessment should be communicated with utility owners. Utility Process: Stage 3 Stage 3 corresponds to the part of the process, around 30% design, when the agency collects detailed information about belowground utility installations and uses the resulting data to identify or confirm utility conflicts as well as analyze and review utility conflict resolution strategies. It includes the following activities: 33

• Conduct detailed utility investigations with appropriate geophysical methods at QLB for the location and soil conditions of the project to produce a map of horizontal locations of belowground utility installations. QLB investigations often turn up a significant number of previously “unknown” utility facilities, raising the question as to the benefit of limiting investigations to only existing records (i.e., QLD data) or QLC data. • Assess potential impact. For each potential conflict, determine whether the utility is in conflict or whether QLA test hole data are needed. • Analyze and review utility conflict resolution strategies, including the option to make changes to the highway design. Depending on project specifics, this assessment can occur with input from utility owners or can be performed by an internal agency review team. This stage should include a utility coordination meeting to discuss utility conflict resolution strategies. Results of the assessment should be communicated with utility owners. • If a utility installation needs to be relocated, coordinate utility relocation design with utility owners. Coordination with utility owners involves all aspects leading to the identification and design of utility conflict resolution measures and setting dates by which critical milestones must be complete. • Prepare and execute utility agreements. Preparation and execution of utility agreements is typically required for utilities that will seek reimbursement for their utility relocation costs. These agreements outline the conditions of the utility accommodation, responsibilities of the parties involved, important timelines, and procedures for the relocation. Utility Process: Stage 4 Stage 4 corresponds to the part of the process, around 60% design (or earlier if possible), when the agency exposes belowground utility installations at specific locations to gather accurate depth data and other critical facility information. At 60% design, critical elements of the project design are in place, including the horizontal and vertical alignments and the drainage design. Stage 4 includes the following activities: • Conduct detailed utility investigations at QLA at specific locations to gather accurate depth data and other critical facility information. • Assess potential impact. For each potential conflict, determine whether the utility is in conflict or not. • Analyze and review utility conflict resolution strategies, including the option to make changes to the highway design. Depending on project specifics, this assessment can occur with input from utility owners or can be performed by an internal DOT review team. This stage should include a utility coordination meeting to discuss utility conflict resolution strategies. Results of the assessment should be communicated with utility owners. 34

• If a utility installation needs to be relocated, coordinate utility relocation design with utility owners. Coordination with utility owners involves all aspects leading to the identification and design of utility conflict resolution measures and setting dates by which critical milestones must be complete. • Prepare and execute utility agreements. Preparation and execution of utility agreements is typically required for utilities that will seek reimbursement for their utility relocation costs. These agreements outline the conditions of the utility accommodation, responsibilities of the parties involved, important timelines, and procedures for the relocation. Utility Process: Stage 5 Stage 5 corresponds to the part of the process, around 90% design, when the agency begins to prepare utility certifications for plan, specifications, and estimate (PS&E) documents. This stage also involves monitoring utility relocations and reimbursing utility owners, as applicable. It includes the following activities: • Prepare utility certifications. Many agencies include a listing of pending utility relocations in the letting documents to alert potential bidders about utilities that will need to be adjusted during the construction phase of the project. • Monitor utility relocations and reimburse utility owners. Monitoring utility relocations includes ensuring that (a) relocated utilities are built and surveyed in accordance with standard project survey accuracy requirements and (b) as-changes are made to the design plans to reflect as-built conditions. Utility Process: Stage 6 Stage 6 corresponds to the part of the process, normally at the beginning of the construction phase, when utilities finish relocating facilities on the ground. Depending on the situation, utility relocations might also be included in the highway contract. This phase also involves managing new utility conflicts that are identified, including conflicts that were missed earlier in the project. It includes the following activities: • Monitor utility relocations and reimburse utility owners. Monitoring utility relocations includes ensuring that (a) relocated utilities are built and surveyed in accordance with standard project survey accuracy requirements and (b) as-changes are made to the design plans to reflect as-built conditions. Ideally, utility relocations should be completed before the beginning of construction. In reality, some utility installations may need to take place during the construction phase. This part of the process also involves reimbursing utility owners for eligible relocation expenses. • Analyze, review, and implement utility conflict resolution strategies for conflicts that are identified during construction. 35

UCM Updates Effective utility conflict management involves preparing and using UCMs systematically throughout the entire utility process. Using the six-stage process described earlier, UCMs could be updated as follows (or at other critical milestones as needed): • UCM 1: During preliminary design. • UCM 2: End of preliminary design or beginning of detailed design. • UCM 3: Around 30% detailed design. • UCM 4: Around 60% detailed design. • UCM 5: Around 90% detailed design. • UCM 6: During construction. Conceptual Model Managing utility conflicts involves managing data about those conflicts as well as all kinds of related data. Conceptually, it is possible to identify six first-level (or core) topics or data objects that pertain to utility conflicts: utility conflict, utility facility, utility agreement, document, project, and user (Figure 3.2). Each of these data objects represents a real-world object that can be characterized by using a set of relevant tables and attributes. The arrows represent high-level relationships between real-world objects. Notice that the real-world objects could be data systems or modules. In this case, the corresponding oval is a placeholder for that data system or module. For example, the project oval would be the placeholder for a project database or system, the utility facility oval would be the placeholder for a computerized inventory of utility facilities, and the utility conflict oval would be the placeholder for a module for managing utility conflicts. Figure 3.2. Conceptual model for the management of utility conflicts. Project User Utility Conflict Document Utility Facility Utility Agreement 36

Logical Data Model The research team developed a logical data model using AllFusion ERwin Data Modeler software. To facilitate implementation, the logical data model complied with the following requirements and standards: • Use of information engineering notation to model entity relationships. • “Third normal form” normalization level. • Entity names use alphanumeric (no special) characters, have fewer than six words, and are derived from the data description. • Attribute names use alphanumeric (no special) characters, have less than six words, and consist of one or more prime words, zero or more qualifier words, and end with one class word. Prime words represent the subject or entity name (e.g., UTILITY CONFLICT). A qualifier word is a descriptive word that further qualifies the prime word (e.g., TYPE). A class word indicates the type of attribute and is chosen from a standardized list of 21 words (e.g., ID, NAME, TEXT) (3). • Attributes use standardized data types. Using standardized data types in the logical data model simplifies compliance with requirements of data types for the physical data model. These requirements can be satisfied by providing a mapping between logical and physical data types. The logical data model was built around core entities that were identified in the conceptual model. The research team accomplished this objective by using subject areas that provide a coherent view of all the entities associated with their corresponding core entity. Appendix A provides a list of all the entities in the data model. The data model includes eight subject areas, that is, one for each core data object in the conceptual model, as well as one subject area for spatial entities and one subject area for application support entities, as follows: • Utility conflict, • Utility facility, • Utility agreement, • Project, • Document, • User, • Application support, and • Spatial entity. The following sections provide a description of the utility conflict and utility facility subject areas. Other subject areas are not described in more detail in this report, but the data dictionary and other documentation provide additional information as needed. The utility conflict subject area includes all the entities needed to manage utility conflict information as well as 37

information needed to relate utility conflict data to other components of the data model. The utility facility subject area includes a data model to build comprehensive inventories of utility facilities along transportation corridors. Agencies could use this data model not just to provide support to the utility conflict management function but also to develop stand-alone utility inventories. The design of the data model took into consideration that agencies may already have database systems in place to manage business processes that are related to utility agreements, projects, documents, utility inventories, and system users. During implementation, it may be possible to develop a linking entity between any of the core entities in the data model and a corresponding entity in an existing information system. For example, a linking entity could be developed to link the PROJECT entity in the data model with a corresponding entity in an enterprise system that manages project-related data. It might even be possible to replace an entire subject area in the data model with an existing information system at the agency. In this scenario, each of the subject areas in the logical data model actually becomes a placeholder for an information system. Alternatively, if the agency does not currently have a specific database system in place, the data model can be used to implement a basic tool for the management of these business processes. Utility Conflict Subject Area The Utility Conflict subject area consists of the main entity UTILITY CONFLICT, related lookup tables, and linkages to other subject areas (Figure 3.3). Table 3.1 provides a listing of the subject area entities and their definitions. A list of all data model entities and definitions is included in Appendix A. At the lowest level, the primary key for a utility conflict is the UTILITY CONFLICT ID attribute. This ID is unique within the database and should be automatically assigned by the database system to ensure uniqueness. UTILITY CONFLICT also includes an attribute (UTILITY CONFLICT NUMBER), which can be edited by users as needed. The UTILITY CONFLICT entity has 28 attributes to describe a utility conflict, most of which are optional attributes. Mandatory attributes are DOT PROJECT ID, UTILITY FACILITY ID, UTILITY CONFLICT ID, and UTILITY CONFLICT DESCRIPTION. In addition, a utility conflict requires at least one design plan sheet number indicating where the utility conflict was found. This attribute is stored in table PLAN DOCUMENT (within the DOCUMENT subject area). UTILITY CONFLICT links to UTILITY CONFLICT EVENT, which stores data about changes to utility conflicts, including the ID of the user who made that change. Examples of events include utility conflict identified, utility conflict revised, permit application, permit approved, and many others that are stored in UTILITY CONFLICT EVENT TYPE. The Utility Conflict Subject Area also contains several linkages to other core tables and/or subject areas. These linkages resolve many-to-many relationships. Linkages include UTILITY CONFLICT ASSIGNMENT, which links utility conflicts to the system user subject 38

area using SYSTEM USER ID; TEST HOLE UTILITY FACILITY, which links utility investigation test holes to utility facility records; and UTILITY CONFLICT EVENT DOCUMENT, which links utility conflict events to records of documents in the system. Figure 3.3. Utility conflict subject area (UAP = utility accommodation policy). UTILITY CONFLICT COMMENTUTILITY CONFLICT EVENT TYPE UTILITY CONFLICT TYPE UTILITY CONFLICT EVENT UAP EXCEPTION UAP EXCEPTION TYPE UTILITY CONFLICT ADJUSTMENT COST UTILITY INVESTIGATION TEST HOLE UTILITY CONFLICT LOCATION TYPE RIGHT OF WAY PARCEL MEETING UTILITY CONFLICT ADJUSTMENT COST TYPE UTILITY CONFLICT ASSIGNMENT SURFACE TYPE TEST HOLE UTILITY FACILITY UTILITY CONFLICT PARCEL UTILITY CONFLICT RESOLUTION STATUS UTILITY CONFLICT SUBTYPE UTILITY CONFLICT RESOLUTION ALTERNATIVE RESPONSIBILITYUTILITY CONFLICT RESOLUTION ALTERNATIVE 39

Table 3.1. Utility Conflict Subject Area Entities and Definitions Entity Name Definition COMMENT A COMMENT is miscellaneous information that provides extra detail or description for an event. MEETING A MEETING is a gathering of people for the purpose of discussing a typically predetermined topic. RIGHT-OF-WAY PARCEL A RIGHT-OF-WAY PARCEL is a parcel that must be acquired as part of a DOT project. SURFACE TYPE A SURFACE TYPE is a category that describes a kind of manmade or natural ground surface. Examples of a SURFACE TYPE are asphalt, concrete, or natural ground. TEST HOLE UTILITY FACILITY A TEST HOLE UTILITY FACILITY is a mapping that represents the many-to-many relationships between a TEST HOLE and a UTILITY FACILITY. TEST HOLE UTILITY FACILITY enables the identification of UTILITY FACILITIES associated with a TEST HOLE and the identification of TEST HOLES associated with a UTILITY FACILITY. UAP EXCEPTION A UAP EXCEPTION is an exemption to the state’s utility accommodation policy. UAP EXCEPTION TYPE A UAP EXCEPTION TYPE is a category that describes a certain kind of UAP EXCEPTION. UTILITY CONFLICT A UTILITY CONFLICT is an instance in which a utility facility is noncompliant with the DOT’s utility accommodation policies, is noncompliant with safety regulations, is in conflict with a proposed transportation project feature, or is in conflict with another utility facility. A UTILITY CONFLICT can be resolved by using an appropriate measure such as modifying the proposed transportation design, relocating the utility facility, abandoning the facility in place, protecting the facility in place, or granting an exception to the state’s utility accommodation polices or safety regulations. UTILITY CONFLICT ADJUSTMENT COST A UTILITY CONFLICT ADJUSTMENT COST is the amount that a utility owner estimates to expend on the removal of a utility conflict by adjusting the utility facility. UTILITY CONFLICT ADJUSTMENT COST TYPE A UTILITY CONFLICT ADJUSTMENT COST TYPE is a characterization of a UTILITY CONFLICT ADJUSTMENT COST. UTILITY CONFLICT ASSIGNMENT A UTILITY CONFLICT ASSIGNMENT is a designation of a person to a UTILITY CONFLICT for a specific purpose, such as responsibility to manage and resolve the conflict. UTILITY CONFLICT EVENT A UTILITY CONFLICT EVENT is the occurrence of a change to a UTILITY CONFLICT. UTILITY CONFLICT EVENT TYPE A UTILITY CONFLICT EVENT TYPE is a category that describes a certain kind of UTILITY CONFLICT EVENT. UTILITY CONFLICT LOCATION TYPE A UTILITY CONFLICT LOCATION TYPE is a characterization of the location of a utility conflict relative to the surface of the earth. Valid values for a UTILITY CONFLICT LOCATION TYPE are “overhead (aboveground)” and “belowground.” 40

Entity Name Definition UTILITY CONFLICT PARCEL A UTILITY CONFLICT PARCEL is a mapping between a UTILITY CONFLICT and a RIGHT-OF-WAY PARCEL. It identifies all UTILITY CONFLICTS associated with a RIGHT-OF-WAY PARCEL, and all RIGHT-OF-WAY PARCLELS associated with a UTILITY CONFLICT. As such, the table identifies all RIGHT-OF-WAY PARCELS that are affected by a UTILITY CONFLICT. UTILITY CONFLICT RESOLUTION ALTERNATIVE A UTILITY CONFLICT RESOLUTION ALTERNATIVE is an option to resolve a utility conflict. Typically, there are multiple resolution alternatives for each utility conflict, which may or may not be feasible. UTILITY CONFLICT RESOLUTION ALTERNATIVE DECISION A UTILITY CONFLICT RESOLUTION ALTERNATIVE DECISION is an option for a determination on how to proceed with one of multiple alternatives for the resolution of a utility conflict. Examples of a UTILITY CONFLICT RESOLUTION ALTERNATIVE DECISION are “Rejected,” “Under Review,” and “Selected.” UTILITY CONFLICT RESOLUTION ALTERNATIVE RESPONSIBILITY A UTILITY CONFLICT RESOLUTION ALTERNATIVE RESPONSIBILITY is a description of the party that is responsible for resolving a utility conflict. Examples of a UTILITY CONFLICT RESOLUTION ALTERNATIVE RESPONSIBILITY are “DOT,” “Utility Owner,” “Utility Owner and DOT,” and “undetermined.” UTILITY CONFLICT RESOLUTION STATUS A UTILITY CONFLICT RESOLUTION STATUS is a definition of the status that a UTILITY CONFLICT can have in the process of resolving the conflict. For example, a UTILITY CONFLICT RESOLUTION STATUS can be “Utility conflict created,” “Utility owner informed of utility conflict,” “Utility conflict resolution strategy selected,” or “Utility conflict resolved.” UTILITY CONFLICT SUBTYPE A UTILITY CONFLICT SUBTYPE is a characterization that further describes a kind of UTILITY CONFLICT TYPE. Examples of a UTILITY CONFLICT SUBTYPE are “Finish Grade,” “Pathway,” and “Excavation.” UTILITY CONFLICT TYPE A UTILITY CONFLICT TYPE is a characterization that describes a kind of UTILITY CONFLICT. Examples of a UTILITY CONFLICT TYPE are “project feature conflict” and “utility regulation conflict.” UTILITY INVESTIGATION TEST HOLE A UTILITY INVESTIGATION TEST HOLE is a small opening in the ground, typically made with a vacuum excavation technique, for the purpose of determining the exact vertical and horizontal position of a buried utility facility. Utility Facility Subject Area The utility facility subject area in the original SHRP 2 R15B deliverables only included the minimum number of utility inventory entities needed to manage utility conflicts. As such, those entities were placeholders for a more comprehensive treatment of utility facility-related data within the highway right-of-way. At the time, there were other ongoing utility research initiatives, including SHRP 2 R01A, R01B, and R01C. SHRP 2 R01A, Technologies to Support Storage, Retrieval, and Use of 3D Utility Location Data, was charged with the development of a 3-D platform for developing utility facility inventories. As of this writing, the SHRP 2 R01A products have not been delivered yet. On the basis of preliminary presentations, the products will include a software prototype for building 3-D utility data models with a highly aggregated structure to handle utility data. This structure uses only two feature classes per type of utility 41

(e.g., one point feature class and one linear feature class for water installations, as well as one point feature class and one linear feature class for wastewater installations) using the Spatial Data Standards for Facilities, Infrastructure, and Environment (SDSFIE) (4). Differentiation among facilities within a specific utility class is by attribute. Members of the research team have been involved in a number of other initiatives related to the development of strategies for preparing comprehensive inventories of utility facilities within the right-of-way. Two of those initiatives are a recent study completed for the Florida DOT (FDOT), which involved the use of Bentley MicroStation and GEOPAK files and protocols in conjunction with an Esri ArcGIS platform (5), and an ongoing study for FHWA on the use of 3-D platforms for developing utility inventories within the right-of-way. This section provides a summary of a generalized platform for developing utility inventories that can be used both for managing utility facility information needed to manage utility conflicts (i.e., the main purpose of this pilot implementation) and for developing long-term repositories of utility facility information. The data model is generic and can be used both for 2-D and 3-D applications because the model is based on real-world objects. At the same, the model is platform independent and can be implemented in a variety of platforms such as MicroStation, AutoCAD, and ArcGIS. The Utility Facility subject area consists of the main entity UTILITY FACILITY, related lookup tables, and linkages to other subject areas (Figure 3.4, Table 3.2). Each record in UTILITY FACILITY represents a utility facility on the ground (or in development), which has several attributes, including utility type (e.g., communication, electric, gas), utility subtype (e.g., communication manhole, communication line), a brief description, whether it is a public or private utility, whether it is an aboveground or belowground facility, and utility owner. UTILITY FACILITY stores some basic information. Additional attributes (depending on the type of information) are stored in UTILITY FACILITY DETAIL and UTILITY FACILITY FEATURE CLASS. The utility type is stored in FEATURE CLASS SHAPE, while the subtype is stored in UTILITY FACILITY FEATURE CLASS. The latter contains a list of 46 common utility facilities subtypes (e.g., communication pole, electric line) that could be expanded as needed. Although all attributes could have been included in just one entity, UTILITY FACILITY, the researchers used a split-table design because there are many utility facility attributes, and many attributes are specific to a type of utility (e.g., barrel diameter is specific to a manhole). Using just one large entity would have produced a wide, sparsely populated table. The split-table design is intended to reduce the overhead of managing UTILITY FACILITY, which is being used by many tables in the database, making the system more efficient. Appendix B includes a list of attributes associated with all feature classes defined in the database. Although UTILITY FACILITY DETAIL contains most of the descriptive data about a utility facility, such as length, width, height, and material, another table is used to define which of the attributes in UTILITY FACILITY DETAIL apply to which UTILITY FACILITY FEATURE CLASS. For example, a communication line would be expected to have a depth or a material, but not a barrel diameter. The definition of applicable attributes for each utility facility 42

feature class is provided by FEATURE CLASS ATTRIBUTE. This linking table allows the management of utility facility feature attributes by using a data table instead of hardcoding values. If an agency decides to track and display additional attributes for a particular utility facility feature class, it can do so simply by editing the table instead of rewriting the system code. Figure 3.4. Utility facility subject area. UTILITY FACILITY FEATURE CLASS TYPE UTILITY FACILITY OPERATION TYPE UTILITY FACILITY MATERIAL UTILITY FACILITY OFFSET UTILITY FACILITY LOCATION TYPE ALIGNMENT REFERENCE HORIZONTAL SPATIAL REFERENCE UTILITY FACILITY DETAIL FEATURE CLASS SHAPE DESIGN LIBRARY LINE STYLE LINE COLORLINE WEIGHT UTILITY FACILITY FEATURE CLASS FEATURE CLASS ATTRIBUTE ATTRIBUTE VERTICAL SPATIAL REFERENCE UTILITY INVESTIGATION QUALITY LEVEL 43

Table 3.2. Utility Facility Subject Area Entities and Definitions Entity Name Definition ALIGNMENT REFERENCE An ALIGNMENT REFERENCE is a point or line that can be used to define a location in reference to the point or a position on the line. Examples of an ALIGNMENT REFERENCE are “Edge of Pavement,” “Baseline,” “Right- of-Way Line,” “Centerline,” “Back of Curb,” “Survey Hub,” and “Reference Point in Driveway.” ATTRIBUTE An ATTRIBUTE is a property or characteristic of a UTILITY FACILITY serving to describe a UTILITY FACILITY. DESIGN LIBRARY A DESIGN LIBRARY is a set of style definitions and resources for a MicroStation file. DOTs use design libraries within MicroStation to define standards for cells, levels, level filters, line styles, multiline styles, text styles, dimensions, and several others. DOTs might have different design libraries for different engineering disciplines, including roadway, geotechnical, photogrammetry, and surveying. FEATURE CLASS ATTRIBUTE A FEATURE CLASS ATTRIBUTE is a mapping between a UTILITY FACILITY FEATURE CLASS and an ATTRIBUTE. It identifies all UTILITY FACILITY FEATURE CLASSES associated with an ATTRIBUTE, and all ATTRIBUTES associated with a UTILITY FACILITY FEATURE CLASS. As such, the table identifies all ATTRIBUTES that a UTILITY FACILITY FEATURE CLASS can have. FEATURE CLASS SHAPE A FEATURE CLASS SHAPE is the form of a FEATURE CLASS in a GIS. For example, a FEATURE CLASS can have the shape of line, point, polygon, or multipoint. The FEATURE CLASS SHAPE is used to define the default or preferred shape of a FEATURE CLASS. UTILITY FACILITY TYPE A UTILITY FACILITY TYPE is a characterization of a kind of UTILITY FACILITY. Examples include water utility, gas utility, and communication. HORIZONTAL SPATIAL REFERENCE A HORIZONTAL SPATIAL REFERENCE is a coordinate system that describes the horizontal location of a feature. Examples include NAD 1983, UTM Zone 12N, NAVD 1988, and GCS WGS 1984. LINE COLOR A LINE COLOR is the appearance of a line in a GIS or CAD environment based on a red, green, and blue value. LINE STYLE A LINE STYLE is a part of the symbology of graphic elements in MicroStation that defines a line’s appearance as being continuous, continuous dashes, dots and dashes, and many others. LINE WEIGHT A LINE WEIGHT is a number within the range of 0 to 30 that designates the stroke width or thickness of a line in MicroStation that is being used to draw and plot a graphic element. UTILITY FACILITY A UTILITY FACILITY is a fixed structure or installation used by a utility owner for the purpose of transporting or delivering a utility. UTILITY FACILITY DETAIL A UTILITY FACILITY DETAIL is a record of information about a UTILITY FACILITY. Records in the table FEATURE CLASS ATTRIBUTE define which attributes a utility facility has, and as a result, which columns in UTILITY FACILITY DETAIL can be populated. UTILITY FACILITY FEATURE CLASS A UTILITY FACILITY FEATURE CLASS is a grouping of FEATURES of the same kind that have the same set of attributes. Examples of a FEATURE CLASS are “Communication Line,” “Water Manhole,” and “Electric Pedestal.” 44

Entity Name Definition UTILITY FACILITY LOCATION TYPE A UTILITY FACILITY LOCATION TYPE is a characterization of the site where a UTILITY FACILITY is located. Examples of UTILITY FACILITY LOCATION TYPE include “State Right-of-Way (Permit),” “Private Easement,” and “Franchise.” UTILITY FACILITY MATERIAL A UTILITY FACILITY MATERIAL is the matter or substance that a UTILITY FACILITY is composed of. UTILITY FACILITY OFFSET A UTILITY FACILITY OFFSET is a description of the distance between a UTILITY FACILITY and a reference line such as edge of pavement or centerline. UTILITY FACILITY OPERATION TYPE A UTILITY FACILITY OPERATION TYPE is a characterization of whether the utility company provides services for the public or for a private entity. UTILITY INVESTIGATION QUALITY LEVEL A UTILITY INVESTIGATION QUALITY LEVEL is a characterization of the quality and reliability of utility information. Valid values of a UTILITY INVESTIGATION QUALITY LEVEL are “QLD,” “QLC,” “QLB,” and “QLA.” VERTICAL SPATIAL REFERENCE A VERTICAL SPATIAL REFERENCE is a coordinate system that describes the vertical location of a feature. Examples include NAD 1983, UTM Zone 12N, NAVD 1988, and GCS WGS 1984. Physical Data Model The research team used the AllFusion ERwin Data Modeler to produce a physical data model based on the logical data model described in the previous section. Two versions of the physical data model were produced: Access 2010 and Oracle. As mentioned later, the research team used the Access 2010 version to prepare UCM data entry forms. The research team produced the Oracle version of the database model to prepare an Oracle SQL script and test the functionality of the database in an Oracle environment. To ensure a consistent conversion of logical data types (i.e., data types in the logical data model) to Microsoft Access physical data types, the research team used a data type conversion standard. The research team also used an extensive glossary of engineering terms to standardize table and column names as well as a name mapping standard to ensure a consistent conversion from logical entity and attribute names to physical tables and columns. The naming conversion process included replacing spaces between logical name words with underscores in the physical model to avoid future implementation issues. Implementation Using Microsoft Access The research team used the physical data model to generate a script to build a version of the UCM database in Access 2010 format. The research team then designed queries and forms for data entry using custom VBA code. The main goal of developing the data entry forms was to illustrate the use of the UCM approach in a stand-alone database environment to users who are not information technology (IT) professionals. The data entry forms are sufficiently polished and user friendly so that they can be used for actual data entry in a stand-alone environment. From 45

this perspective, they provide a unique opportunity for users to become familiar with some of the typical protocols that would take place when managing utility conflicts in a database environment. Although the Access forms are not compatible with an enterprise-level environment, they provide a preliminary design based on which enterprise-level forms could be developed. Initially, the research team designed one physical Access database for the VBA code and the data. However, to facilitate debugging and testing, the research team decided to develop the data entry forms and the VBA code application in one database file (i.e., the front end), while storing all data in a separate database file (i.e., the back end). As a result, the back-end database only includes tables and queries that are required to add, edit, or delete data. By comparison, the front end only includes forms, reports, and queries that are required to execute the forms and reports. Linking the application and the data is maintained by using the Access Linked Table Manager. If either database is moved to a new location, or if the drive letter of a local computer is different from the drive letter stored in Access, the application will not work until all tables have been relinked by using the Linked Table Manager. The research team prepared several data entry forms in Access 2010. The application includes a main user interface (Figure 3.5), four main forms (to manage projects, utility companies, utility facilities, and utility conflicts), and several subforms within each of the main forms. Figures 3.6 through 3.8 show the three subforms to manage projects, while Figures 3.9 through 3.11 show one example subform of the other main forms. This version of the data entry forms does not include the subsheet to analyze utility conflict resolution alternatives. Figure 3.5. Main user interface. 46

Figure 3.6. Form to manage projects: Add new project. 47

Figure 3.7. Form to manage projects: Edit existing project. 48

Figure 3.8. Form to manage projects: Delete existing project. 49

Figure 3.9. Form to manage utility companies: Edit utility company. 50

Figure 3.10. Form to manage utility facilities: Edit utility facility. 51

Figure 3.11. Form to manage utility conflicts: Edit utility conflict. The forms that display utility feature attribute data (e.g., the form to edit features in Figure 3.10) show a different set of attributes depending on the feature class. After considering several options, the research team decided to store a basic set of attributes in the UTILITY FACILITY table and all additional attributes in the UTILITY FACILITY DETAIL table. Basic attributes include utility facility class, company ID, company type, and horizontal and vertical spatial reference and positional accuracy. Mandatory basic attributes include utility facility class, company ID, and company type. The rest of the attributes, as well as all attributes in the UTILITY FACILITY DETAIL table, are optional. 52

Because the number of possible optional attributes can vary drastically among utility facility classes, the research team wrote code that only displays the attributes that pertain to each facility class. Unfortunately, VBA in Access does not support dynamic controls, which made it necessary to use static controls to simulate this functionality. A table called ATTRIBUTE contains a listing of optional attributes (26 in total). The current version of the code has static controls for 21 optional attributes (five attributes were added to the ATTRIBUTE table after the code had been finalized). When a user opens the form, the static controls are hidden by default. After the user selects a specific feature class (e.g., water line), the corresponding static controls representing each of the optional attributes associated with that feature class become visible. All other static controls remain hidden. For future implementations, the forms could be rewritten in a different language that supports dynamic controls such as Visual C#. With VBA in Access, the process to add any of the five unused attributes is as follows: • Add records to table FEATURE CLASS ATTRIBUTE to link the attribute and its display order to each feature class of interest. • Add a static control for the attribute in the Add Utility Facility, Edit Utility Facility, and Delete Utility Facility forms. • Modify the code to manage these controls in the Add Utility Facility, Edit Utility Facility, Delete Utility Facility forms. To add new optional attributes that are not currently listed in the ATTRIBUTE table, the process would be as follows: • Add new attribute to the logical and physical data models. • Add new attribute to the ATTRIBUTE table. • Add column for the new attribute in the UTILITY FACILITY DETAIL table. • Follow the process above for adding static controls. The research team conducted some limited testing of the Access data entry forms by entering data that MDSHA districts gathered when they were populating their stand-alone UCMs for the six pilot implementation projects. For the testing, the research team sequentially entered data about projects, utility owners, utility facilities, and utility conflicts. General observations on the use of the Access database approach to complete these activities are as follows: • Data entry is time-consuming but cost-effective. Mirroring what MDSHA officials noted while populating the stand-alone UCMs, the research team noticed that entering data into the database required time and effort. An informal estimate was that it took about 8 hours of effort to enter data for some 60 utility conflicts. While significant, managing utility conflicts is really about making an up-front investment with the 53

expectation of a significant return on that investment during the life of the project. The rate of return of that investment can be huge, possibly by a factor of 100 or more. For example, in the case of the MD 32 project, MDSHA project staff probably spent less than 36 hours, or approximately $3,600 (using an estimated hourly rate of $100), populating the UCM. The estimated economic benefit of using a UCM approach for that project was $500,000, which would translate to a net benefit of about $139 saved for each dollar invested. On the basis of an estimated delay savings of six months (or 960 hours), this would result in a benefit of about 27 project hours saved for each hour invested populating the UCM. • Microsoft Access has limitations for managing utility data. Although Access provides a convenient database platform for managing utility conflicts, Access is really designed for stand-alone implementations. The research team noted that both the query structure and the VBA code pushed the limits of what can be reasonably expected with this kind of database environment. During testing, the research team noted that sometimes it took a few seconds for forms to open or commands to execute. The research team expects these issues to increase in magnitude as the database grows in size, particularly in a multiuser environment. • Enterprise, centralized database implementation might be more beneficial in the long term. By design, Microsoft Access uses a decentralized implementation concept consisting of unlimited copies of the same database to be used at several locations. Using copies of the same data can improve efficiency in the short term. However, there is a risk of data redundancy and loss of quality control. The current application requires access to the physical database either on a local or network drive. A more user-friendly approach would be to develop an enterprise-level application that is accessible via the Internet and uses a database platform such as Oracle or SQL Server. That would also facilitate access and contributions by stakeholders outside of the DOT, including utility companies and consultants. • The conflict resolution alternative analysis subsheet should be further evaluated. MDSHA did not use the conflict resolution alternative analysis subsheet during the pilot implementation. However, comments from stakeholders indicated that this feature might be useful under certain circumstances involving high-impact utility conflict locations. • Utility conflict event tracking should be further evaluated. A major benefit of a database approach is the automated tracking of events associated with utility conflicts. An evaluation of this feature by MDSHA staff was not possible because the application was completed near the end of the pilot implementation. 54