Identification of Utility Conflicts and Solutions (2012)

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.

24 C h a p t e r 4 Introduction This chapter describes the development of a data architecture to support the use of a variety of UCMs in a database environ- ment. The following sections describe data architecture com- ponents developed as part of this research, a utility conflict database prototype, and the results of testing the prototype with two sample UCMs and the prototype UCM described in Chapter 3. Data architecture Definitions A data architecture can be defined as the manner in which data components are organized and integrated. It usually includes one or more of the following data model components: • Business process model. A business process model is a rep- resentation of processes. A variety of tools and tech- niques may be used depending on the specific need, including flow charts, Gantt charts, project evaluation and review technique (PERT) charts, integration defini- tion (IDEF) methods, and business process modeling notation (BPMN). • Conceptual model. A conceptual data model is a high-level representation of basic objects or concepts and their rela- tionships. Conceptual data models usually provide a map- ping of relationships and rules (e.g., a utility owner can own many utility facilities) that facilitate the understanding and implementation of logical and physical data models. • Logical data model. A logical data model is a representation of data characteristics and relationships at a level that is independent of any physical implementation. • Physical data model. A physical data model is a representa- tion of data characteristics and relationships that depends on the specific physical platform chosen for its implemen- tation (e.g., Microsoft Access, Oracle, Microsoft SQL Server, or Sybase). • Data dictionary. A data dictionary is a list that contains definitions, characteristics, and other properties of entities, attributes, and other data elements. A metadata document is an alternative (or complement) to a data dictionary. The four models are discussed in the following sections. Business process Model Transportation project development processes around the country share many similarities. As a reference, Figure 4.1 shows a typical project development process diagram with six high-level sets of activities from planning and programming to letting and construction, with an emphasis on right-of- way and utilities. Readers should note that the actual transportation project development process can deviate from the general framework shown in Figure 4.1 depending on specific project character- istics and requirements. For example, different types of proj- ects, such as resurfacing or restoration (2R), nonfreeway rehabilitation (3R), new location and reconstruction (4R), and special facilities, have different design criteria that involve different groups of project development tasks and, therefore, different project scopes, durations, and sequencing. Similarly, project delivery methods such as design–build methods can accelerate task durations and alter the sequencing of certain project development tasks. In the case of utility relocations, there might be a substantial amount of relocation work dur- ing construction, as well as reimbursement activities that extend into the postconstruction phase. Figure 4.2 shows a more detailed view of utility activities in the project development process. To function properly, the utility coordination process needs utility data input, which can occur at different times of the process. Typically, as time progresses, utility data become more detailed and precise. Although any type of utility data can be collected at any time during the project development process, it is common to Prototype UCM Database Design and Testing

25 conflicts resolved by the end of the design phase. Appendix B describes the utility coordination process for several of the states contacted. Conceptual Model Managing utility conflicts involves managing data about a variety of topics. For example, the review of sample docu- mentation provided by the states revealed that, in the process of managing utility conflicts, states manage data about topics collect QLD and QLC data during preliminary design, and QLB and QLA data during the detailed design phase. Simi- larly, although utility conflict resolution can take place at any time, most utility conflict resolution activities usually occur around or after 60% design. Although many states follow a utility coordination process similar to that shown in Figure 4.2, there are differences from state to state. For example, the Georgia DOT (GDOT) has a process that includes formalized stages for utility impact analyses and field plan reviews, with the goal of having utility Alternative Analysis and Preliminary Plans Environmental Process Utility Conflict Analysis, Permits, Relocation, and Reimbursement Right-of-Way Map, Authorization to Acquire Property, Property Acquisition, and Relocation Assistance Design and PS&E Assembly Letting Construction Planning and Programming Preliminary Design Detailed Design Letting Construction Post Constrn. Property Management Preliminary input to planning and programming Scoping, Selection, and Scheduling Environmental Approval 30% 60% 90% Right-of-Way Authorization Environmental Re-evaluation Figure 4.1. Major transportation project development process activities. Figure 4.2. Utility coordination in the project development process.

26 • Attribute names use alphanumeric (no special) characters; have fewer 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) (15). • Attributes use standardized data types. In practice, develop- ing a list of standardized data types was an iterative process because requirements for data items changed as more information about the entity or attribute became known. For example, the company identifier initially had a number data type, but later it became necessary to change the data type to support combinations of numbers and letters. 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 map- ping between the logical and physical data types. The logical data model was built around the core data objects identified in the conceptual model. Each core data object defined a key entity, which was the most important entity within its corresponding subject area. The logical data model used the following key entities: • UTILITY CONFLICT. A UTILITY CONFLICT occurs when 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. • 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 AGREEMENT. A UTILITY AGREEMENT is a contract between a DOT and other agencies in connection such as projects, project contacts, utility facilities, utility con- flicts, right-of-way, utility investigations, utility relocation, utility coordination dates, agreements, costs, and billings. Some of these topics can be grouped into higher-order categories. Conceptually, it is possible to identify six first-level (or core) topics or data objects: utility conflict, utility facility, utility agreement, document, project, and user (Figure 4.3). Each of these data objects represents a real-world object or concept that can be characterized using a set of relevant tables and attributes. It is also possible to define relationships between those objects. As Figure 4.3 shows, several types of relationships exist between core data objects. Some of those relationships are many-to-many (depicted by double arrows in the figure). For example, a document can be associated with many projects, utility conflicts, and users. Similarly, a project can be associ- ated with many users, documents, and utility conflicts. Other relationships are one-to-many (depicted by single arrows). For example, a utility agreement can be associated with many utility conflicts, but a utility conflict can only be associated with one utility agreement. Similarly, a utility facility can be associated with many utility conflicts, but a utility conflict can only be associated with one utility facility. Similarly, a project can have many utility conflicts, but a utility conflict can only be associated with one project. This characteristic of the model resulted from observations of state DOT business practices, which typically recommend utility conflicts to be defined and managed within the context of a project. If utility conflicts span more than one project, the solution is to break up the utility conflict at the project boundary to enable track- ing and association of utility costs with a project. Logical Data Model The research team developed a logical data model using All- Fusion ERwin Data Modeler software. For simplicity, the pro- totype application was called the utility conflict database. The logical data model included the following elements: • Entities, attributes, and relationships; • Primary, foreign, and alternate keys; and • Entity and attribute definitions. To facilitate implementation, the logical data model com- plied with the following requirements and standards: • Information engineering notation is used to model entity relationships. • Third normal form normalization level is used. • All entity names use alphanumeric (no special) characters, have fewer than six words, and are derived from the data description. Figure 4.3. Conceptual model for the management of utility conflicts.

27 subject areas, one for each core data object in the conceptual model, as follows: • Utility conflict subject area (Figure 4.4); • Utility facility subject area (Figure 4.5); • Utility agreement subject area (Figure 4.6); • Project subject area (Figure 4.7); • Document subject area (Figure 4.8); and • User subject area (Figure 4.9). Appendix C includes a detailed description of each subject area. physical Data Model The research team used AllFusion ERwin Data Modeler to produce a physical data model (exported to Microsoft Access 2007) based on the logical data model described in the previous section. The data modeling process involved several iterations between the logical and physical data models. As detailed in the following section, the research team used data from the sample documents provided by the states to populate the prototype Access database. Dur- ing the process of entering data, it became necessary to make modifications to the model (either logical or physi- cal) to address specific situations, such as a data type that was not previously considered, a look-up table that had not been defined, or a many-to-many relationship that had to be optimized. The physical data model included the following elements: • Tables, columns, and relationships; • Primary, foreign, and alternate keys; and • Table and column definitions. 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 team also used an extensive glossary of engineering terms to standardize table and col- umn 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 conver- sion process included replacing spaces between logical name words with underscores in the physical model to avoid future implementation issues. The research team also added foreign key constraints in the physical model according to the following syntax: FK_<Child Table Physical Name> _REF_<Parent Table Physical Name> with a utility adjustment. A UTILITY AGREEMENT usually consists of a UTILITY AGREEMENT contract form and several attachments, such as the engineering estimate, design drawings, and special provisions. • DOT PROJECT. A DOT PROJECT is a transportation project managed by a state DOT. • DOCUMENT. A DOCUMENT is a tangible product in printed or electronic form produced from, resulting from, or documenting a DOT project development process activ- ity. Examples include stand-alone forms, agreement assem- blies, chapters, technical memoranda, invoices, and reports. In general, documents represented by multiple files are handled as document sets. • SYSTEM USER. A SYSTEM USER is someone who has an account and the authority to use the system. The prototype allows two types of users: DOT USERs and COMPANY USERs. (The scope of the research did not include devel- oping a system with user interfaces to create, manage, vali- date, or authenticate user accounts. However, a SYSTEM USER entity was necessary in the data model to support tracking of utility conflict events, e.g., to associate a UCM version with the name[s] of the individual[s] who pre- pared, revised, or reviewed the UCM.) State DOTs have long used database systems to manage project-related data. The use of enterprise-level electronic doc- ument management systems is also increasing. Because the level of investment in these applications is substantial, it is safe to assume that most state DOTs would be interested in a data model for managing utility conflict data that supports, or can be integrated with, their already-existing systems. The challenge during the research was to design a logical data model that could minimize data redundancy while simultaneously maxi- mizing compatibility with existing systems in order to increase the chances of implementation of the research prototype. 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. During imple- mentation, it may be possible to develop a linking entity between any of the core entities in the data model and a cor- responding 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. If the state DOT so chooses, it might even be possible to replace an entire subject area in the data model with an existing information system at the state DOT. In this scenario, each of the subject areas in the logical data model actually becomes a placeholder for an information system. The logical data model consists of approximately 115 enti- ties and numerous relationships. The data model includes six

28 UTILITY CONFLICT UTILITY CONFLICT ID UTILITY CONFLICT NUMBER UTILITY CONFLICT DESCRIPTION UTILITY CONFLICT WORK DESCRIPTION UTILITY CONFLICT LOCATION DESCRIPTION UTILITY CONFLICT START STATION MEASUREMENT UTILITY CONFLICT START OFFSET MEASUREMENT UTILITY CONFLICT END STATION MEASUREMENT UTILITY CONFLICT END OFFSET MEASUREMENT UTILITY CONFLICT X COORDINATE MEASUREMENT UTILITY CONFLICT Y COORDINATE MEASUREMENT UTILITY CONFLICT LENGTH MEASUREMENT UC APPROXIMATE RELOCATION DISTANCE MEASUREMENT UTILITY CONFLICT COMPANY NOTIFICATION FLAG UTILITY CONFLICT IMPACT FLAG UTILITY CONFLICT REIMBURSEABLE FLAG UC COORDINATION DURING CONSTRUCTION FLAG DOT PROJECT NUMBER (FK) HORIZONTAL SPATIAL REFERENCE ID (FK) VERTICAL SPATIAL REFERENCE ID (FK) UA ID (FK) UC RESOLUTION STRATEGY TYPE ID (FK) UC INVESTIGATION NEED TYPE ID (FK) UTILITY CONFLICT LOCATION TYPE ID (FK) UTILITY CONFLICT TYPE ID (FK) UTILITY CONFLICT SUBTYPE ID (FK) UTILITY FACILITY ID (FK) UITH ID (FK) COMMENT COMMENT ID COMMENT TITLE TEXT COMMENT BODY TEXT COMMENT TIMESTAMP COMMENT REPLY ID UTILITY CONFLICT EVENT NUMBER (FK) UTILITY CONFLICT EVENT TYPE UTILITY CONFLICT EVENT TYPE ID UTILITY CONFLICT EVENT TYPE NAME UTILITY CONFLICT EVENT TYPE DESCRIPTION UC EVENT TYPE STATUS FLAG UC EVENT TYPE ORDER CODE UTILITY CONFLICT TYPE UTILITY CONFLICT TYPE ID UTILITY CONFLICT TYPE NAME UTILITY CONFLICT TYPE DESCRIPTION UTILITY CONFLICT RESOLUTION STRATEGY TYPE UC RESOLUTION STRATEGY TYPE ID UC RESOLUTION STRATEGY TYPE NAME UCR STRATEGY TYPE ACRONYM TEXT UC RESOLUTION STRATEGY TYPE DESCRIPTION <default> UTILITY CONFLICT EVENT UTILITY CONFLICT EVENT NUMBER UTILITY CONFLICT EVENT RECORD TIMESTAMP UTILITY CONFLICT EVENT DATE RIGHT OF WAY PARCEL ID (FK) SYSTEM USER ID (FK) UTILITY CONFLICT EVENT TYPE ID (FK) UTILITY CONFLICT ID (FK) UAP EXCEPTION UAP EXCEPTION ID UAP EXCEPTION AMOUNT UAP EXCEPTION APPROVAL FLAG UAP EXCEPTION LIMITS FROM TEXT UAP EXCEPTION LIMITS TO TEXT UAP EXCEPTION REQUEST RECEIVE DATE UAP EXCEPTION REQUEST RESOLUTION DATE UAP EXCEPTION COMMENT UTILITY CONFLICT ID (FK) UAP EXCEPTION TYPE ID (FK) UAP EXCEPTION TYPE UAP EXCEPTION TYPE ID UAP EXCEPTION TYPE NAME UAP EXCEPTION TYPE DESCRIPTION UTILITY CONFLICT ADJUSTMENT COST UTILITY CONFLICT ADJUSTMENT COST ID UTILITY CONFLICT ADJUSTMENT COST AMOUNT UTILITY CONFLICT EVENT NUMBER (FK) UC ADJUSTMENT COST TYPE ID (FK) UTILITY CONFLICT INVESTIGATION NEED TYPE UC INVESTIGATION NEED TYPE ID UC INVESTIGATION NEED TYPE NAME UC INVESTIGATION NEED TYPE DESCRIPTION UTILITY INVESTIGATION TEST HOLE UITH ID UITH NUMBER UITH DESCRIPTION UITH SURVEYING EQUIPMENT DESCRIPTION UITH STATION MEASUREMENT UITH OFFSET MEASUREMENT UITH X COORDINATE MEASUREMENT UITH Y COORDINATE MEASUREMENT UITH HORIZONTAL PRECISION MEASUREMENT UITH VERTICAL PRECISION MEASUREMENT UITH ELEVATION UITH RMSE MEASUREMENT UITH NUMBER OF SATELLITES QUANTITY UITH NUMBER OF MEASUREMENTS QUANTITY UITH POSITION DILUTION PRECISION QUANTITY ALIGNMENT REFERENCE ID (FK) HORIZONTAL SPATIAL REFERENCE ID (FK) SURFACE TYPE ID (FK) VERTICAL SPATIAL REFERENCE ID (FK) UTILITY CONFLICT LOCATION TYPE UTILITY CONFLICT LOCATION TYPE ID UTILITY CONFLICT LOCATION TYPE NAME UCL TYPE LEGAL STATUS DESCRIPTION RIGHT OF WAY PARCEL RIGHT OF WAY PARCEL ID ROW PARCEL REQUIRED ACQUISITION DATE MEETING MEETING ID MEETING LOCATION UTILITY CONFLICT ID (FK) UTILITY CONFLICT ADJUSTMENT COST TYPE UC ADJUSTMENT COST TYPE ID UC ADJUSTMENT COST TYPE NAME UTILITY CONFLICT ASSIGNMENT UTILITY CONFLICT ID (FK) SYSTEM USER ID (FK) ROLE ID (FK) UTILITY CONFLICT EVENT DOCUMENT TYPE UTILITY CONFLICT EVENT NUMBER (FK) DOCUMENT TYPE ID (FK) Figure 4.4. Logical data model: Utility conflict subject area.

29 In general, the process to replicate UCMs involved the fol- lowing steps: • Review requirements and content of the original UCM. • Develop and test queries. • Develop and test report(s). • Enter and manage sample data. For evaluation and comparison purposes, the analysis included developing queries, forms, and reports in Access in order to reproduce the sample documents as faithfully as possible. The development and testing of forms for data entry was not included in the scope of work for this research. However, the research team did develop a few data entry forms to illustrate the capabilities of the database. Prototype UCM Example The prototype UCM and subreport were developed in Chap- ter 3 (Figures 3.2 and 3.3, respectively). The prototype UCM includes 23 data items (eight data items in the header and If the foreign key constraint would have been longer than 30 characters using full table names, the research team abbreviated table names as needed. prototype Database testing The research team populated the Access database with data from sample documents provided by the states. This section summarizes the process to replicate the following UCMs: • Prototype UCM developed in Chapter 3; • Sample UCM provided by the Alaska Department of Trans- portation and Public Facilities (Alaska DOT&PF); and • Sample UCM provided by the California Department of Transportation (Caltrans). Appendix D provides detailed descriptions of the replica- tion process for these three UCMs and for sample UCMs pro- vided by GDOT and the Texas DOT (TxDOT). For brevity, this chapter does not provide a summary for the Georgia and Texas UCMs. UTILITY FACILITY UTILITY FACILITY ID UTILITY FACILITY DESCRIPTION UTILITY FACILITY SIZE UTILITY FACILITY AGE UTILITY FACILITY DEPTH UTILITY FACILITY TYPE ID (FK) UTILITY FACILITY OPERATION TYPE ID (FK) UTILITY FACILITY MATERIAL ID (FK) UTILITY FACILITY LOCATION TYPE ID (FK) COMPANY ID (FK) HORIZONTAL SPATIAL REFERENCE ID (FK) VERTICAL SPATIAL REFERENCE ID (FK) ALIGNMENT REFERENCE ID (FK) UTILITY FACILITY TYPE UTILITY FACILITY TYPE ID UTILITY FACILITY TYPE NAME UTILITY FACILITY TYPE DESCRIPTION UTILITY FACILITY SUBTYPE ID (FK) UTILITY FACILITY TYPE ACRONYM TEXT UTILITY FACILITY OPERATION TYPE UTILITY FACILITY OPERATION TYPE ID UTILITY FACILITY OPERATION TYPE NAME UTILITY FACILITY OPERATION TYPE DESCRIPTION UTILITY FACILITY MATERIAL UTILITY FACILITY MATERIAL ID UTILITY FACILITY MATERIAL NAME UTILITY FACILITY MATERIAL ACRONYM TEXT UTILITY FACILITY MATERIAL DESCRIPTION UTILITY FACILITY OFFSET UTILITY FACILITY OFFSET ID UFO START STATION MEASUREMENT UFO END STATION MEASUREMENT UFO START OFFSET MEASUREMENT UFO END OFFSET MEASUREMENT UFO REFERENCE TYPE UTILITY FACILITY ID (FK) UTILITY FACILITY LOCATION TYPE UTILITY FACILITY LOCATION TYPE ID UTILITY FACILITY LOCATION TYPE NAME UFL TYPE ACRONYM TEXT UTILITY FACILITY LOCATION TYPE DESCRIPTION UTILITY FACILITY SUBTYPE UTILITY FACILITY SUBTYPE ID UTILITY FACILITY SUBTYPE NAME UTILITY FACILITY SUBTYPE DESCRIPTION Figure 4.5. Logical data model: Utility facility subject area.

30 MEASUREMENT UNIT MEASUREMENT UNIT ID MEASUREMENT UNIT CODE MEASUREMENT UNIT NAME UTILITY AGREEMENT UA ID UA ALTERNATE NUMBER UTILITY AGREEMENT WORK STATEMENT TEXT UA STATE UTILITY CONTRACT FLAG UA UPPER LIMIT AMOUNT UA PAYMENT AUTHORIZATION FLAG UA AUDIT EXCEPTION AMOUNT UA OUTSTANDING BALANCE AMOUNT UA COMMENT TEXT UA CONTRACTING PROCEDURE TYPE CODE (FK) UA REIMBURSEMENT APPROACH ID (FK) UA UTILITY ADJUSTMENT TYPE ID (FK) DOCUMENT SET ID (FK) PROPERTY RIGHT TYPE PROPERTY RIGHT TYPE ID PROPERTY RIGHT TYPE NAME PROPERTY RIGHT TYPE DESCRIPTION PROPERTY RIGHT SUBTYPE PROPERTY RIGHT SUBTYPE ID PROPERTY RIGHT SUBTYPE NAME PROPERTY RIGHT SUBTYPE DESCRIPTION PROPERTY RIGHT CLASS PROPERTY RIGHT CLASS ID PROPERTY RIGHT CLASS NAME PROPERTY RIGHT CLASS DESCRIPTION PROPERTY RIGHT PROPERTY RIGHT ID PROPERTY RIGHT CLASS ID (FK) PROPERTY RIGHT TYPE ID (FK) PROPERTY RIGHT SUBTYPE ID (FK) ESTIMATE ESTIMATE ID ESTIMATE COST AMOUNT ESTIMATE BETTERMENT FLAG ESTIMATE BETTERMENT RATIO NUMBER ESTIMATE DATE ACCOUNTING METHOD ID (FK) ESTIMATE TYPE ID (FK) PROJECT DEVELOPMENT PROCESS PHASE ID (FK) UA ID (FK) UTILITY CONFLICT ID (FK) UC RESOLUTION ALTERNATIVE ID (FK) UTILITY BILL UTILITY BILL ID UTILITY BILL AMOUNT UTILITY BILL PAYMENT AMOUNT UTILITY BILL APPROVED AMOUNT UTILITY BILL RECORD NUMBER UTILITY BILL TYPE ID (FK) UTILITY BILL COMMENT SYSTEM USER ID (FK) UA ID (FK) UTILITY BILL TYPE UTILITY BILL TYPE ID UTILITY BILL TYPE NAME UTILITY BILL TYPE DESCRIPTION UTILITY AGREEMENT REIMBURSEMENT APPROACH UA REIMBURSEMENT APPROACH ID UA REIMBURSEMENT APPROACH NAME UA REIMBURSEMENT APPROACH DESCRIPTION UA CONTRACTING PROCEDURE TYPE UA CONTRACTING PROCEDURE TYPE CODE UA CONTRACTING PROCEDURE TYPE NAME UA CONTRACTING PROCEDURE TYPE DESCRIPTION ELIGIBILITY RATIO ELIGIBILITY RATIO ID ELIGIBILITY RATIO DATE ELIGIBILITY RATIO NONELIGIBILE OCCUPANCY MEASUREMENT ELIGIBILITY RATIO PERCENT ELIGIBILITY RATIO SHEET NUMBER COMPOSITE ELIGIBILITY RATIO ID (FK) ELIGIBILITY RATIO TYPE ID (FK) MEASUREMENT UNIT ID (FK) UA ID (FK) UTILITY CONFLICT ID (FK) ELIGIBILITY RATIO TYPE ELIGIBILITY RATIO TYPE ID ELIGIBILITY RATIO TYPE NAME ELIGIBILITY RATIO TYPE DESCRIPTION ACCOUNTING METHOD ACCOUNTING METHOD ID ACCOUNTING METHOD NAME ACCOUNTING METHOD DESCRIPTION COMPOSITE ELIGIBILITY RATIO COMPOSITE ELIGIBILITY RATIO ID COMPOSITE ELIGIBILITY RATIO ADJUSTMENT PERCENT ELIGIBILITY RATIO PROPERTY RIGHT OCCUPANCY ER PROPERTY RIGHT OCCUPANCY ID ELIGIBILITY RATIO ELIGIBLE OCCUPANCY MEASUREMENT ELIGIBILITY RATIO ID (FK) MEASUREMENT UNIT ID (FK) PROPERTY RIGHT ID (FK) UA UTILITY ADJUSTMENT TYPE UA UTILITY ADJUSTMENT TYPE ID UA UTILITY ADJUSTMENT TYPE NAME UA UTILITY ADJUSTMENT TYPE DESCRIPTION UTILITY AGREEMENT DATE TYPE UA DATE TYPE ID UA DATE TYPE NAME UTILITY AGREEMENT DATE UTILITY AGREEMENT DATE ID UA DATE UA ID (FK) UA DATE TYPE ID (FK) ESTIMATE TYPE ESTIMATE TYPE ID ESTIMATE TYPE NAME ESTIMATE TYPE DESCRIPTION ESTIMATE TYPE TOTAL FLAG UTILITY BILL DATE TYPE UTILITY BILL DATE TYPE ID UTILITY BILL DATE TYPE NAME UTILITY BILL DATE UTILITY BILL DATE ID BILL DATE UTILITY BILL ID (FK) UTILITY BILL DATE TYPE ID (FK) PROJECT DEVELOPMENT PROCESS PHASE PROJECT DEVELOPMENT PROCESS PHASE ID PROJECT DEVELOPMENT PROCESS PHASE NAME Figure 4.6. Logical data model: Utility agreement subject area.

31 • Step 1. Create “UCM 1 estimated completion date” query. For a specific project, this query retrieves utility conflicts that have been identified or that have an estimated adjust- ment completion. • Step 2. Create “UCM 2 Estimated Completion Date” query. This query uses the query created in Step 1 to display every utility conflict for a project with the estimated completion date if that date was entered for the utility conflict. If the estimated completion date was not entered for a utility conflict, the query displays the utility conflict record with- out an entry in the utility conflict event date column. • Step 3. Create “UCM 1 multiple utility conflict status” query. This query selects all utility conflicts associated with a proj- ect with any of four utility conflict event types: utility con- flict created, utility owner informed of utility conflict, utility conflict resolved, or utility conflict resolution strategy selected. These four utility conflict event types serve as an example of how a state DOT might decide to track the status of a utility conflict (other choices involving more or fewer events are certainly possible). 15 data items in the main body). The subreport includes 25 data items (13 data items in the header and 12 data items in the main body). Replicating the UCM and subreport involved developing 10 queries and four reports. Figure 4.10 shows the UCM report that resulted from this replication process. In general, the process to replicate the prototype UCM in the database involved the following data operations: • Retrieve estimated completion date. • Retrieve utility conflict status. • Retrieve plan document sheet number. • Retrieve conflict resolution alternatives. • Calculate estimate cost. • Generate UCM and subreport. To describe this process, a summary of the Access queries and reports needed to develop the report follows. Appen- dix D provides a detailed description of each Access query and Access report, including structured query language (SQL) statements. UTILITY ADJUSTMENT PROCEDURE UTILITY ADJUSTMENT PROCEDURE ID UTILITY ADJUSTMENT PROCEDURE NAME UTILITY ADJUSTMENT PROCEDURE ACRONYM TEXT UTILITY ADJUSTMENT PROCEDURE DESCRIPTION PROJECT UTILITY ADJUSTMENT PROCEDURE UTILITY ADJUSTMENT PROCEDURE ID (FK) DOT PROJECT NUMBER (FK) DOT PROJECT DOT PROJECT NUMBER DOT PROJECT ALTERNATE NUMBER DOT PROJECT CONTRACT CSJ NUMBER DOT PROJECT DESCRIPTION DOT PROJECT LIMITS FROM DESCRIPTION DOT PROJECT LIMITS TO DESCRIPTION DOT PROJECT BEGINNING RM NUMBER DOT PROJECT BEGINNING RMD MEASUREMENT DOT PROJECT ENDING RM NUMBER DOT PROJECT ENDING RMD MEASUREMENT DOT PROJECT ENGINEER NUMBER DOT PROJECT LAST REVISION TIMESTAMP DOT PROJECT CITY NAME COUNTY ID (FK) DISTRICT ID (FK) HIGHWAY SYSTEM ID (FK) MAINTENANCE SECTION ID (FK) STATE ID (FK) HIGHWAY SYSTEM HIGHWAY SYSTEM ID HIGHWAY SYSTEM NUMBER HIGHWAY SYSTEM NAME HIGHWAY FUNCTIONAL CLASS ID (FK) HIGHWAY SYSTEM STATUS ID (FK) MAINTENANCE SECTION MAINTENANCE SECTION ID MAINTENANCE SECTION NAME DOT PROJECT DATE DOT PROJECT DATE ID PROJECT DATE DOT PROJECT DATE TYPE ID (FK) DOT PROJECT NUMBER (FK) DOT PROJECT DATE TYPE DOT PROJECT DATE TYPE ID DOT PROJECT DATE TYPE NAME HIGHWAY FUNCTIONAL CLASS HIGHWAY FUNCTIONAL CLASS ID HIGHWAY FUNCTIONAL CLASS CODE HIGHWAY FUNCTION CLASS NAME HIGHWAY SYSTEM STATUS HIGHWAY SYSTEM STATUS ID HIGHWAY SYSTEM STATUS NAME Figure 4.7. Logical data model: Project subject area.

32 Figure 4.8. Logical data model: Document subject area. DOCUMENT DOCUMENT ID DOCUMENT TITLE NAME DOCUMENT DESCRIPTION DOCUMENT ADDRESS DOCUMENT LOGICAL NAME DOCUMENT PHYSICAL NAME DOCUMENT HYPERLINK ADDRESS DOCUMENT SET MEMBER FLAG DOCUMENT COMMENT DOCUMENT TYPE ID (FK) DOCUMENT SET DOCUMENT SET ID DOCUMENT SET NUMBER DOCUMENT SET TITLE NAME DOCUMENT SET DESCRIPTION DOCUMENT SET TYPE ID (FK) UTILITY CONFLICT EVENT DOCUMENT DOCUMENT ID (FK) UTILITY CONFLICT EVENT NUMBER (FK) PROJECT DOCUMENT DOT PROJECT NUMBER (FK) DOCUMENT ID (FK) DOCUMENT SET ITEM DOCUMENT SET ID (FK) DOCUMENT ID (FK) DOCUMENT SET TYPE DOCUMENT SET TYPE ID DOCUMENT SET TYPE NAME DOCUMENT SET TYPE DESCRIPTION ORIENTATION ORIENTATION ID ORIENTATION NAME CONVEYANCE DOCUMENT DOCUMENT ID (FK) PROPERTY RECORD VOLUME NUMBER PROPERTY RECORD PAGE NUMBER CONVEYANCE TYPE ID (FK) PROPERTY DESCRIPTION DOCUMENT ID (FK) PROPERTY DESCRIPTION SURVEY DATE PROPERTY RECORD VOLUME NUMBER PROPERTY RECORD PAGE NUMBER PARCEL AREA MEASUREMENT PROPERTY DESCRIPTION TYPE ID (FK) PLAT DOCUMENT ID (FK) PLAT SURVEY DATE PLAT REMAINDER AREA MEASUREMENT PARCEL AREA MEASUREMENT PROPERTY RECORD VOLUME NUMBER PROPERTY RECORD PAGE NUMBER CONVEYANCE TYPE CONVEYANCE TYPE ID CONVEYANCE TYPE NAME CONVEYANCE TYPE DESCRIPTION ENCUMBRANCE DOCUMENT DOCUMENT ID (FK) PROPERTY RECORD VOLUME NUMBER PROPERTY RECORD PAGE NUMBER ENCUMBRANCE DOCUMENT TYPE ID (FK) LEASE AGREEMENT DOCUMENT DOCUMENT ID (FK) LEASE CANCELLATION TERM NUMBER EASEMENT DOCUMENT DOCUMENT ID (FK) PROPERTY ROLE PROPERTY ROLE ID PROPERTY ROLE NAME PROPERTY ROLE DESCRIPTION CAD DOCUMENT DOCUMENT ID (FK) CAD SOFTWARE VERSION NAME CAD DOCUMENT CELL DOCUMENT ID (FK) IMAGERY DOCUMENT DOCUMENT ID (FK) IMAGERY RESOLUTION MEASUREMENT IMAGERY UNIT CODE (FK) ORIENTATION ID (FK) IMAGERY UNIT IMAGERY UNIT CODE IMAGERY UNIT NAME AGREEMENT DOCUMENT DOCUMENT ID (FK) AGREEMENT DOCUMENT TYPE ID (FK) CERTIFICATION DOCUMENT DOCUMENT ID (FK) CERTIFICATION DOCUMENT TYPE ID (FK) LEGAL DOCUMENT DOCUMENT ID (FK) DOCUMENT NUMBER EFFECTIVE DATE EXECUTION DATE EXPIRATION DATE VOID DATE FILING DATE ROW FORM ID (FK) PLAN DOCUMENT DOCUMENT ID (FK) PLAN DOCUMENT SHEET NUMBER PLAN DOCUMENT GROUP SHEET NUMBER PLAN DOCUMENT TOTAL GROUP SHEET NUMBER PLAN DOCUMENT COMMENT SHEET GROUP ID (FK) SHEET GROUP SHEET GROUP ID SHEET GROUP NAME SHEET GROUP DESCRIPTION DOCUMENT SYSTEM USER ROLE DOCUMENT ID (FK) SYSTEM USER ID (FK) PROPERTY ROLE ID (FK) DOCUMENT ROLE ID (FK) PROPERTY DESCRIPTION TYPE PROPERTY DESCRIPTION TYPE ID PROPERTY DESCRIPTION TYPE NAME PROPERTY DESCRIPTION TYPE DESCRIPTION DOCUMENT DATE DOCUMENT DATE ID DOCUMENT DATE DOCUMENT DATE COMMENT DOCUMENT DATE TYPE ID (FK) DOCUMENT ID (FK) DOCUMENT DATE TYPE DOCUMENT DATE TYPE ID DOCUMENT DATE TYPE NAME DOCUMENT DATE TYPE DESCRIPTION CERTIFICATION DOCUMENT TYPE CERTIFICATION DOCUMENT TYPE ID CERTIFICATION DOCUMENT TYPE NAME CERTIFICATION DOCUMENT TYPE DESCRIPTION ENCUMBRANCE DOCUMENT TYPE ENCUMBRANCE DOCUMENT TYPE ID ENCUMBRANCE DOCUMENT TYPE NAME ENCUMBRANCE DOCUMENT TYPE DESCRIPTION AGREEMENT DOCUMENT TYPE AGREEMENT DOCUMENT TYPE ID AGREEMENT DOCUMENT TYPE NAME AGREEMENT DOCUMENT TYPE DESCRIPTION ROW FORM ROW FORM ID ROW FORM NUMBER ROW FORM TITLE ROW FORM HYPERLINK DOCUMENT ROLE DOCUMENT ROLE ID DOCUMENT ROLE NAME DOCUMENT ROLE DESCRIPTION DOCUMENT TYPE DOCUMENT TYPE ID DOCUMENT TYPE NAME DOCUMENT TYPE DESCRIPTION

33 utility conflict status and the corresponding utility conflict event date for each utility conflict. • Step 6. Create “UCM 3” query. This query uses the queries from Steps 2, 4, and 5 to retrieve a list of utility conflicts for a specific project. • Step 7. Create “UCM 1 estimates crosstab” query. For a spe- cific project, this query produces a crosstab between infor- mation about alternatives to resolve a utility conflict and • Step 4. Create “UCM 1 plan document sheet number” query. This query retrieves the sheet number and sheet group ID for plan documents that are associated with utility conflicts in a project. • Step 5. Create “UCM 2 utility conflict status” query. This query uses the query from Step 3 to retrieve the highest order code for all utility conflict event types associated with a project. The order code specifies the order of events for a USER EVENT USER EVENT NUMBER SYSTEM USER ID (FK) USER EVENT TIMESTAMP USER EVENT TYPE ID (FK) USER EVENT TYPE USER EVENT TYPE ID USER EVENT TYPE NAME USER EVENT TYPE DESCRIPTION COMPANY OFFICE COMPANY OFFICE ID COMPANY OFFICE NAME COMPANY OFFICE ACRONYM TEXT COMPANY OFFICE ADDRESS COMPANY OFFICE CITY NAME COMPANY OFFICE ZIP CODE COMPANY OFFICE PHONE NUMBER COMPANY OFFICE FAX NUMBER COMPANY ID (FK) STATE ID (FK) SYSTEM USER SYSTEM USER ID COMPANY USER ID (FK) DOT USER ID (FK) DOT OFFICE DOT OFFICE ID DOT OFFICE NAME DOT OFFICE ADDRESS DOT OFFICE CITY NAME DOT OFFICE ZIP CODE DOT OFFICE PHONE NUMBER DOT OFFICE FAX NUMBER DOT OFFICE PARENT ID MAINTENANCE SECTION ID DOT OFFICE EVENT TIMESTAMP DOT UNIT ID (FK) OFFICE TYPE ID (FK) STATE ID (FK) DOT UNIT TYPE DOT UNIT TYPE ID DOT UNIT TYPE NAME DOT OFFICE TYPE OFFICE TYPE ID OFFICE TYPE NAME OFFICE TYPE COMMENT PARENT OFFICE TYPE ID DOT UNIT DOT UNIT ID DOT UNIT NAME DOT UNIT NUMBER DOT UNIT TYPE ID (FK) STATE STATE ID STATE NAME STATE DOT NAME STATE DOT ACRONYM TEXT COMPANY USER COMPANY USER ID COMPANY USER FIRST NAME COMPANY USER LAST NAME (IE1.1) COMPANY USER TITLE NAME COMPANY USER EMAIL ADDRESS COMPANY USER PHONE NUMBER COMPANY USER FAX NUMBER COMPANY USER EVENT TIMESTAMP COMPANY OFFICE ID (FK) DOT USER DOT USER ID DOT USER FIRST NAME DOT USER LAST NAME (IE1.1) DOT USER TITLE NAME DOT USER EMAIL ADDRESS DOT USER PHONE NUMBER DUP MOBILE PHONE NUMBER DOT USER FAX NUMBER DOT USER EVENT TIMESTAMP DOT OFFICE ID (FK) COMPANY COMPANY ID COMPANY ACRONYM TEXT COMPANY NAME DISTRICT DISTRICT ID DISTRICT NAME STATE ID (FK) COUNTY COUNTY ID COUNTY NAME STATE ID (FK) DOT PROJECT SYSTEM USER SYSTEM USER ID (FK) DOT PROJECT NUMBER (FK) ROLE ID (FK) ROLE ROLE ID ROLE NAME ROLE DESCRIPTION Figure 4.9. Logical data model: User subject area.

34 Figure 4.10. Utility conflict resolution alternatives: Cost estimate analysis. Ulity Conflict Resoluon Alternaves Project Owner: Project No.: Texas Department of Transportaon 1234-56-789 Project Descripon: Road construcon project in Houston Highway or Route: I-10 Katy Freeway Date: 4/6/2011 Conflict ID: 1 Ulity Owner: AT&T Ulity Type: Telephone Size and/or Material: Fiber Opc Project Phase: 60% Design Cost Esmate Analysis Alternave Number Alternave Descripon Engineering Cost (DOT) Direct Cost (DOT) Total CostAlternave Advantage Alternave Disadvantage DecisionFeasibilityEngineering Cost (Ulity) Direct Cost (Ulity) Responsible Party 0 Relocaon before construcon. $0.00 $0.00 $74,250.00No design change required and no addional cost to DOT. Cost to ulity for relocaon. SelectedYes$10,375.00 $63,875.00Ulity Company 1 Protect in-place. $0.00 $0.00 $40,250.00 RejectedNo$7,875.00 $32,375.00Ulity Company 2 Design change. $95,375.00 $0.00 $95,375.00 RejectedNo$0.00 $0.00DOT 3 Excepon to policy. $0.00 $0.00 $0.00 RejectedNo$0.00 $0.00DOT

35 • Step 2. Create “Alaska 1 transmission crosstab” query. This query is similar to the previous query except that it selects electricity transmission utility facilities. • Step 3. Create “Alaska 2 total distribution” query. This query uses the crosstab query from Step 1 and produces a total of the adjustment cost and engineering cost estimates for each distribution utility conflict. • Step 4. Create “Alaska 2 total transmission” query. This query uses the crosstab query from Step 2 and produces a total of the adjustment cost and engineering cost estimates for each transmission utility conflict. • Step 5. Create “Alaska 3 distribution cost” query. This query uses the queries from Steps 1 and 3 to retrieve engineering cost and adjustment cost estimates as well as total electric distribution utility conflict costs. • Step 6. Create “Alaska 3 transmission cost” query. This query is similar to the previous query except that it uses the queries produced in Steps 2 and 4 in connection with electric transmission utility conflicts. • Step 7. Create “Alaska 4 distribution subtotal” query. This query uses the query from Step 5 to calculate subtotals of adjustment and engineering cost estimates, as well as the total cost of electric distribution utility conflicts. • Step 8. Create “Alaska 4 transmission subtotal” query. This query is similar to the previous query, except that it uses the query from Step 6 in connection with transmission utility conflicts. • Step 9. Create “Alaska 5 AC total” query. This query uses the queries from Steps 7 and 8 to retrieve totals of adjust- ment costs for both distribution and transmission utility conflicts. The total adjustment cost is calculated by adding the distribution adjustment cost total to the transmission adjustment cost total. • Step 10. Create “Alaska 5 EC total” query. This query uses the queries from Steps 7 and 8 and produces totals of engi- neering costs for both distribution and transmission utility conflicts. The total engineering cost is calculated by adding the distribution engineering cost total to the transmission engineering cost total. • Step 11. Create “Alaska 6 grand total” query. This query uses the queries from Steps 9 and 10 to produce the grand total utility cost, which is the total adjustment cost plus the total engineering cost. • Step 12. Create “Alaska 7 UCM distribution” query. This query uses the query from Step 5 and numerous other tables of the data model to produce a tabulation of electric distribution utility conflict data. • Step 13. Create “Alaska 7 UCM transmission” query. This query uses the query from Step 6 and numerous other tables of the data model to produce a tabulation of electric transmission utility conflict data. • Step 14. Create “Alaska subreport transmission” report. This report provides a list of utility conflicts, along with related estimate types, as well as a summation of estimate cost amounts. • Step 8. Create “UCM 2 alternative analysis UC34” query. This query uses the crosstab produced in Step 7 to produce a table of information about alternatives to resolve conflict number 34. A similar query would be required for every conflict that requires a separate cost estimate analysis of available resolution alternatives. • Step 9. Create “UCM” subsheet report. This report repli- cates the subsheet for the prototype UCM based on the results of the query from Step 8 (Figure 4.10). All data items provided in the report are selected from database entries, with the exception of the total cost field, which calculates a summation on the fly, and the names (and corresponding dates) of the individuals who developed, revised, or reviewed the UCM report. • Step 10. Create “UCM” report. This report replicates the prototype UCM based on the results of the query from Step 8 (Figure 4.11). All data items provided in the report are selected from database entries. The only exception is the names (and corresponding dates) of the individuals who developed, revised, or reviewed the UCM report. Alaska DOT&PF UCM Example The sample UCM from the Alaska DOT&PF (see Figure 4.12) has an average number of data items: five data items in the header and 14 in the main body. This UCM was also interest- ing because it included totals, subtotals, and grand totals of cost data elements, grouped according to whether the utility installations involved were distribution or transmission facili- ties. In general, the process to replicate the Alaska DOT&PF UCM in the database involved the following data operations: • Identify electric distribution facilities. • Identify electric transmission facilities. • Retrieve adjustment and engineering costs for distribution facilities. • Retrieve adjustment and engineering costs for transmis- sion facilities. • Calculate totals. • Generate UCM. Replicating the sample UCM involved developing 13 que- ries and three reports. Figure 4.13 shows the result of the replication process. A summary of the queries and reports needed to develop the report in Figure 4.13 follows. Appen- dix D provides a detailed description of each query and report, including SQL statements. • Step 1. Create “Alaska 1 distribution crosstab” query. This query selects two types of electric distribution utility facilities for a specific project and lists the adjustment cost estimate and the engineering cost estimate by utility conflict ID.

36 Ulity Conflict Matrix Project Owner: Project No.: Texas Department of Transportaon 1234-56-789 Project Descripon: Road construcon project in Houston Highway or Route: I-10 Katy Freeway Date: Date: _____________ _____________ Ulity Conflict Matrix Developed/Revised By: ________________________________ Reviewed By: ________________________________ Ulity Owner and/ or Contact Name Ulity Type Start Staon End Staon Start Offset End Offset Size and/or Material Ulity Invesgaon Level Needed Test Hole No. Esmated Resoluon Date Resoluon StatusDrawing or Sheet No. Ulity Conflict Descripon Recommended Acon or Resoluon Conflict ID Cost Analysis '5400+2200+12enohpeleTT&TA Lt 45' LtFiber Opc ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with construcon of frontage road widening. Relocaon before construcon.1 Detail '7300+3208+12enohpeleTT&TA Rt 37' RtFiber Opc ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with construcon of frontage road widening. Relocaon before construcon.2 Detail '8400+0305+72enohpeleTT&TA Rt 48' RtFiber Opc ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with construcon of frontage road widening. Relocaon before construcon.3 Detail '8451+5404+44enohpeleTT&TA Rt 48' RtFiber Opc ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with construcon of frontage road widening. Relocaon before construcon.4 Detail '9402+5401+54enohpeleTT&TA Lt 49' LtUnknown ytilitU0102/8/3BLQ owner informed of ulity conflict U- tcilfnoC1 with construcon of frontage road widening. Design change.5 Detail '7509+5408+54enohpeleTT&TA Lt 49' LtCopper ytilitU0102/8/3BLQ conflict idenfiedU- tcilfnoC1 with retaining wall No. ngiseD.81 change.6 Detail '5609+5208+52enohpeleTT&TA Lt 49' LtCopper ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with retaining wall No. tcetorP.81 in-place.7 Detail '2609+5208+52enohpeleTT&TA Rt 49' LtCopper ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with retaining wall No. tcetorP.81 in-place.8 Detail '5500+8204+72enohpeleTT&TA Lt 55' LtCopper ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with retaining wall No. tcetorP.81 in-place.9 Detail '5500+8204+72enohpeleTT&TA Rt 55' LtCopper ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with retaining wall No. tcetorP.81 in-place.10 Detail '2600+9250+82enohpeleTT&TA Rt 55' LtCopper ytilitU0102/8/3CLQ conflict idenfiedU- tcilfnoC1 with retaining wall No. noitpecxE.81 to policy.11 Detail '9400+6105+51enohpeleTT&TA Lt 80' RtMulple Concrete Duct ytilitU0102/8/3CLQ owner informed of ulity conflict U- tcilfnoC2 with retaining wall No. ngiseD.81 change.12 Detail '0400+6109+51enohpeleTT&TA Lt 80' RtMulple Concrete Duct ytilitU0102/8/3CLQ owner informed of ulity conflict U- tcilfnoC2 with retaining wall No. ngiseD.72 change.13 Detail '51100+2204+02enohpeleTT&TA Rt 80' RtMulple Concrete Duct ytilitU0102/8/3CLQ owner informed of ulity conflict U- tcilfnoC2 with retaining wall No. ngiseD.72 change.14 Detail '0800+3203+22enohpeleTT&TA Rt 80' RtMulple Concrete Duct ytilitU0102/8/3CLQ owner informed of ulity conflict U- tcilfnoC2 with retaining wall No. ngiseD.72 change.15 Detail '5500+8258+52enohpeleTT&TA Rt 80' RtMulple Concrete Duct ytilitU0102/8/3BLQ owner informed of ulity conflict U- tcilfnoC2 with retaining wall No. ngiseD.72 change.16 Detail '2600+0350+82enohpeleTT&TA Rt 80' RtMulple Concrete Duct ytilitU0102/8/3BLQ owner informed of ulity conflict U- tcilfnoC2 with retaining wall No. ngiseD.72 change.17 Detail '5600+5351+33enohpeleTT&TA Rt 80' RtMulple Concrete Duct ytilitU0102/8/3BLQ owner informed of ulity conflict U- tcilfnoC2 with retaining wall No. ngiseD.72 change.18 Detail '8400+64455+544elohnaMT&TA Rt 48' RtSteel ytilitU0102/2/71ALQ conflict idenfiedU- tcilfnoC2 with retaining wall No. noitacoleR.72 before construcon.19 Detail Centerpoint yticirtcelEygrenE Distribuon 445+55 446+00 48' Rt 48' RtSteel ytilitU0102/2/72ALQ conflict idenfiedU- tcilfnoC3 with retaining wall No. noitacoleR.72 before construcon.20 Detail Centerpoint yticirtcelEygrenE Distribuon 445+50 446+00 48' Rt 48' RtSteel ytilitU0102/2/73ALQ conflict idenfiedU- tcilfnoC3 with construcon of storm sewer. Relocaon before construcon.21 Detail Centerpoint yticirtcelEygrenE Distribuon 445+60 447+00 55' Rt 48' RtSteel ytilitU0102/2/74ALQ conflict idenfiedU- tcilfnoC3 with construcon of storm sewer. Relocaon before construcon.22 Detail Centerpoint yticirtcelEygrenE Distribuon 445+80 448+00 55' Rt 48' RtSteel ytilitU0102/2/75ALQ conflict idenfiedU- tcilfnoC3 with construcon of storm sewer. Relocaon before construcon.23 Detail Centerpoint yticirtcelEygrenE Distribuon 445+80 448+00 55' Rt 48' RtSteel ytilitU0102/2/76ALQ conflict idenfiedU- tcilfnoC3 with construcon of storm sewer. Relocaon before construcon.24 Detail Centerpoint yticirtcelEygrenE Distribuon 445+80 448+00 55' Rt 48' RtSteel ytilitU0102/2/77ALQ conflict idenfiedU- tcilfnoC3 with construcon of storm sewer. Relocaon before construcon.25 Detail Centerpoint yticirtcelEygrenE Distribuon 445+90 448+00 55' Rt 48' RtSteel ytilitU0102/2/78ALQ conflict idenfiedU- tcilfnoC3 with construcon of storm sewer. Design change.26 Detail Figure 4.11. Prototype UCM.

37 Figure 4.12. Alaska DOT&PF sample utility conflict report excerpt. Alaska UCM Anchorage, Alaska DOT&PF No. 50898 DRAFT Ulity Conflict Report West Dowling Road Phase 1 Start Staon Start Offset End Staon End Offset Size Type Length Conflict ADJ/REL Cost PE/CE Cost Total Cost CEA Distribuon Relocaon Costs 9+00 150' RT 200' LT 3 phi UG 350 FG Relocaon before construcon $52,500 $15,750 $68,250 16+00 100' LT 42+30 80' LT 3 phi UG 2,630 FG Relocaon before construcon $394,500 $118,350 $512,850 16+00 100' LT 15+50 100' RT 3 phi UG 250 FG Relocaon before construcon $37,500 $11,250 $48,750 16+00 100' LT 29+00 75' LT 1 phi UG 1,650 FG Relocaon before construcon $165,000 $49,500 $214,500 36+40 80' LT 35+80 350' RT 3 phi UG 430 FG Relocaon before construcon $64,500 $19,350 $83,850 36+60 80' LT 36+70 380' LT 3 phi UG 300 FG Relocaon before construcon $45,000 $13,500 $58,500 UG Loop to the North 3 phi UG 1,000 FG Relocaon before construcon $150,000 $45,000 $195,000 $909,000 $272,700 $1,181,700Subtotal: CEA Transmission Relocaon Costs 14+75 55' RT 138 kV OH 1 PWY Relocaon before construcon $30,000 $9,000 $39,000 32+75 55' RT 138 kV OH 1 EX Relocaon before construcon $50,000 $15,000 $65,000 36+38 45' RT 138 kV OH 1 EX Relocaon before construcon $50,000 $15,000 $65,000 $130,000 $39,000 $169,000Subtotal: $1,039,000 $311,700 $1,350,700Total Relocaon Costs: Figure 4.13. Alaska UCM report.

38 • Step 4. Create “CA 1 UC comment” query. For a specific project, this query provides a list of utility conflicts that have an associated comment. • Step 5. Create “CA 2 required completion date outer join” query. This query produces a list of utility conflicts with required completion dates for a specific project, including those utility conflicts that do not have a required comple- tion date. The query creates an outer join of the utility conflict ID between the utility conflict table and the query from Step 3, with two conditions: include all records from the utility conflict table, and include only those records from the Step 3 query in which the joined fields are equal. • Step 6. Create “CA 2 utility conflict comment outer join” query. This query produces a list of utility conflicts with com- ments for a specific project, including those utility conflicts that do not have a comment. The query creates an outer join of the utility conflict ID between the utility conflict table and the query from Step 4, with two conditions: include all records from the utility conflict table, and include only those records from the Step 4 query in which the joined fields are equal. • Step 7. Create “CA 3 UCM” query. This query uses the que- ries from Steps 1, 5, and 6 to retrieve utility conflict data that provide the basis for the Caltrans UCM report. The query parameter is the project number. • Step 8. Create “California UCM” report. This report uses data from the query in Step 7. All data items provided in the report are selected from database entries. The only excep- tion is the name of the document preparer, which could be inserted dynamically through a dialog box when the docu- ment is prepared. advantages of a Database approach Implementing a database for managing utility conflict data requires careful planning, experienced staff, and buy-in by multiple affected parties. The effort required to implement a database system is considerably higher than implementing a stand-alone spreadsheet. However, a database approach offers a multitude of advantages and benefits that a spread- sheet cannot offer. The prototype database structure was based on a large number of state DOT UCMs and many diverse data items, and as a result, the prototype data model and resulting data- base are flexible and capable of accommodating most data items related to utility conflicts. This flexibility was proven through the process of replicating the sample UCMs described in this report. Further, the prototype data model and data- base used standard database design principles, which should facilitate the implementation of the database to address the costs for adjustment, engineering, and total costs per transmission utility conflict and totals for all utility conflicts. This report is based on the query developed in Step 13. • Step 15. Create “Alaska subreport grand total” report. This report provides a total adjustment cost, total engineering cost, and the grand total of both distribution and trans- mission costs based on the query from Step 11. • Step 16. Create “Alaska UCM” report. This report uses the data from the query in Step 12 and incorporates the sub- reports from Steps 14 and 15 into one report. The report includes other data items from the database, such as proj- ect number and description. Caltrans UCM Example The sample UCM from Caltrans, shown in Figure 4.14, is an example of a detailed UCM with a large number of data items: four data items in the header and 20 in the main body. The UCM was also interesting because it included data items that were included in the prototype UCM, including utility sheet number, utility conflict investigation type, utility relo- cation strategy type, and utility relocation responsible party. In general, the process to replicate the Caltrans UCM in the database involved the following data operations: • Retrieve date last revised. • Retrieve plan document sheet number. • Retrieve required completion date. • Retrieve utility conflicts with comments. • Create list of utility conflicts with required completion date and comments. • Generate UCM. Replicating the sample UCM involved developing seven queries and one report. Figure 4.15 shows the result of the rep- lication process. A summary of the queries and reports needed to develop the report in Figure 4.15 follows. Appendix D pro- vides a detailed description of each query and report, including SQL statements. • Step 1. Create “CA 1 date last revised” query. This query selects the latest time stamp of a utility conflict event that is associated with a specific project. • Step 2. Create “CA 1 plan document sheet number” query. This query retrieves the sheet number of any plan document that mentions a utility conflict. The query selects a project number and then lists the sheet numbers by utility conflict. • Step 3. Create “CA 1 required completion date” query. This query produces the date by which a utility conflict is required to be completed in order for a project to pro- ceed to the construction phase without delays.

39 I-10-EA 122401-Utilities Conflict Status date of last revision May 30, 2000 this document was prepared by htpeD/tcilfnoC ytilitUtcilfnoCelohnaM/elohtoPytilitUrenwOelohtoPytilitUtcilfnoC Util. Reloc. Resp. Party Required Comments daehrevOelohnaMelohtoPnoitpircseD kroWnoitacoLnoitacoLnoitpircseD.oN.oN teehS.oN Y N Remove Relocate Other A - Abandon U- Utility Co Completion (On U-sheets) (ft) RB- Reloc.Before C- Contractor Date RD- Reloc.During P- Protect in place NC- No conflict 55.4Xhtiw tcilfnoc fo tR m 75 dna tR m 04fo tR m 26UD 04LLEBCAP12-U1 P N04.41861 .oN & 661 .oN sllaW gniniateR55+561 atS 504-I55+561 atS 504-IenohpeleT N- htiw tcilfnoc fo tR m 75 dna tR m 04fo tL m 84UD 04LLEBCAP22-U2 P Telephone I-405 Sta 165+55 I-405 Sta 165+55 Retaining Walls No. 166 & No. 168 N- htiw tcilfnoc fo tR m 34fo tR m 53UD mm 52ECS33-U3 P Located in Bristol OC I-405 Sta 165+01 I-405 Sta 165+01 Retaining Wall No. 166 N- htiw tcilfnoc fo tR m 34fo tL m 64UD mm 52ECS43-U4 P Located in Bristol OC I-405 Sta 165+01 I-405 Sta 165+01 Retaining Wall No. 166 N 07.6Xhtiw tcilfnoc fo tR m 44fo tR m 05retaW PSW mm 009DWM53-U5 P in 380 mL ENC I-405 Sta 164+96 I-405 Sta 164+95 Retaining Wall No. 166 N 05.6Xhtiw tcilfnoc fo tR m 44fo tL m 05retaW PSW mm 009DWM63-U6 P in 380 mL ENC I-405 Sta 164+96 I-405 Sta 164+95 Retaining Wall No. 166 N 00.6Xhtiw tcilfnoc 504-I fo tR m 35fo tR m 35PCR mm 006snartlaC73-U7 P I-405 Sta 163+42 from Sta 163+29 to Sta 163+42 Delhi Channel Bridge N 00.9Xhtiw tcilfnoc 504-I fo tR m 35fo tR m 35PCR mm 006snartlaC83-U8 P I-405 Sta 163+29 from Sta 163+29 to Sta 163+42 Delhi Channel Bridge N03.01Xhtiw tcilfnoc fo tR m 53fo tR m 23ni retaW PCA mm 003DWCM93-U9 P 119mL, 500mm STL Casing I-405 Sta 163+25 I-405 Sta 163+25 I-405 Widening & BR1 Line N57.8Xhtiw tcilfnoc fo tL m 33fo tL m 23retaW PCA mm 003DWCM013-U01 P 119mL, 500mm STL Casing I-405 Sta 163+25 I-405 Sta 163+25 I-405 Widening & BR1 Line N04.81Xhtiw tcilfnoc fo tR m 53fo tR m 18elohnaMCODSC11 HM3-U11 P I-405 Sta 162+92 I-405 Sta 162+92 I-405 Widening & BR1 Line N- htiw tcilfnoc fo tL m 23fo tL m 63reweS PCV mm 083CODSC213-U21 P I-405 Sta 162+91 I-405 Sta 162+90 I-405 Widening & BR1 Line XX Y55.4XlennahC tropriA htiw tcilfnoCfo tR m 85fo tR m 76L m49 ni retaW PCC mm006DWCM314-U31 RB 600 mm Waterline to be Lowered 44+161 atS 504-I44+161 atS 504-IgnisaC ltS aiD mm009 Extend Encasement N - htiw tcilfnoc fo tL m 23fo tL m 83L m49 ni retaW PCC mm006DWCM414-U41 P gninediW 504-I24+161 atS 504-I04+161 atS 504-IgnisaC ltS aiD mm009 X Y-Xhtiw tcilfnoC504-I fo tR m 27fo tR m 07retaW PCA mm 003DWCM514-U51 RD Enchroachment CT R/W and Private Owner I-405 Sta 160+29 from Sta 157+20 to Sta 160+29 AOA Line and Retaining Wall No. 268 Encased under Roadway X Y-Xhtiw tcilfnoC504-I fo tR m 27fo tR m 07retaW PCA mm 003DWCM614-U61 RD Enchroachment CT R/W and Private Owner I-405 Sta 159+07 from Sta 157+20 to Sta 160+29 AOA Line and Retaining Wall No. 268 Encased under Roadway N 53.4Xhtiw tcilfnoc 504-I fo tR m 27fo tR m 07retaW PCA mm 003DWCM715-U71 P I-405 Sta 156+87 from Sta 157+20 to Sta 160+29 AOA Line and Retaining Wall No. 268 N 02.61Xhtiw tcilfnoc fo tR m 82fo tR m 06elohnaMCODSC81 HM5-U81 P gninediW 504-I56+651 atS 504-I56+651 atS 504-I N 04.81Xhtiw tcilfnoc fo tR m 52fo tL m 64reweS PCV mm 083CODSC915-U91 P gninediW 504-I56+651 atS 504-I56+651 atS 504-I N htiw tcilfnoc fo tR m 41reweS PCV mm 038CODSC025-U02 P eniL 2B fo noitcurtsnoc 69+42 atS 2B N htiw tcilfnoc fo tL m 6reweS PCV mm 038CODSC125-U12 P eniL 2B fo noitcurtsnoc 45+52 atS 2B fo tR m8elohnaMCODSC22 HM8-U22 XYX RB MH to be Lowered Main St Sta 102+78 New Top MH Elev= 9.588 23 U-8 MH 23 SCE Manhole No. 4503 8m Rt of XYX RB MH to be Lowered 78+201 atS tS niaM3054 HM ECS New Top MH Elev= 9.583 m 24 U-8 MH 24 SCE Manhole No. 4502 8m Rt of XYX RB MH to be Lowered 71+401 atS tS niaM2054 HM ECS New Top MH Elev= 9.728 m Investigation Impact? Action Figure 4.14. Caltrans sample utility conflict status list.

40 Figure 4.15. Caltrans UCM report. California UCM This document was prepared by: _____________________ Date of last revision: 12/4/2009 I-10-EA 122401 - Ulies Conflict Status Test Hole No. Owner U­lity Descrip­on Test Hole/ Manhole Loca­on Start Sta­on End Sta­on U­lity Conflict/ Work Descrip­on U­lity Conflict Inves­ga­on Dept h (Š) Impact? U­lity Reloca­on U­lity Sheet No. Required Comple­on Date CommentsResp. Party OffsetConflict No. 1 PACBELL 40 mm DU Telephone 62 m Rt of I-405 Sta 165+55 165+55 Conflict with retaining walls No. 166 and No. 168 QLA 4.55 N PU-2 1/10/2010U40 m Rt and 57 m Rt of I-405 1 2 PACBELL 40 mm DU Telephone 48 m Lt of I-405 Sta 165+55 165+55 Conflict with retaining walls No. 166 and No. 168 14.40 N PU-2 1/10/2010U40 m Rt and 57 m Rt of I-405 2 3 SCE 25 mm DU Telephone 35 m Rt of I-405 Sta 165+01 165+01 Conflict with retaining wall No. 166 N PU-3 1/10/2010 Located in Bristol OCU43 m Rt of I-4053 4 SCE 25 mm DU Telephone 46 m Lt of I-405 Sta 165+55 165+01 Conflict with retaining wall No. 166 N PU-3 Located in Bristol OCU43 m Rt of I-4054 5 MWD 900 mm Water in 380 mL ENC 50 m Rt of I-405 Sta 165+96 164+95 Conflict with retaining wall No. 166 QLA 6.70 N PU-3 U44 m Rt of I-4055 6 MWD 900 mm Water in 380 mL ENC 50 m Lt of I-405 Sta 165+96 164+95 Conflict with retaining wall No. 166 QLA 6.50 N PU-3 U44 m Rt of I-4056 7 Caltrans 600 mm 53 m Rt of I-405 Sta 163+42 163+29 163+24 Conflict with Delhi Channel Bridge QLA 6.00 N PU-3 U53 m Rt of I-4057 8 Caltrans 600 mm 53 m Rt of I-405 Sta 163+29 163+29 163+42 Conflict with Delhi Channel Bridge QLA 9.00 N PU-3 U53 m Rt of I-4058 9 MCWD 300 mm Water in 119 mL, 500 mm STL Casing 32 m Rt of I-405 Sta 163+25 163+25 Conflict with I-405 widening and BR1 Line QLA 10.30 N PU-3 U35 m Rt of I-4059 10 MCWD 300 mm Water in 119 mL, 500 mm STL Casing 32 m Lt of I-405 Sta 163+25 163+25 Conflict with I-405 widening and BR1 Line QLA 8.75 N PU-3 U33 m Lt of I-40510 MH 11 CSDOC Manhole 81 m Rt of I-405 Sta 162+92 162+92 Conflict with I-405 widening and BR1 Line QLB 18.40 N PU-3 U35 m Rt of I-40511 12 CSDOC 380 mm Sewer 36 m Lt of I-405 Sta 162+91 162+92 Conflict with I-405 widening and BR1 Line N PU-3 U32 m Lt of I-40512 13 MCWD 600 mm Water in 94 mL, 900 mm STL Casing 67 m Rt of I-405 Sta 161+44 161+44 Conflict with airport channel QLA 4.55 Y RBU-4 600 mm waterline to be lowered, extend encasement U58 m Rt of I-40513 14 MCWD 600 mm Water in 94 mL, 900 mm STL Casing 38 m Lt of I-405 Sta 161+40 161+42 Conflict with I-405 widening N PU-4 U32 m Lt of I-40514 15 MCWD 300 mm Water 70 m Rt of I-405 Sta 160+29 157+20 160+29 Conflict with AOA line and retaining wall No. 268 QLA Y RDU-4 Encroachment CR R/W and private owner, encased under roadway U72 m Rt of I-40515 16 MCWD 300 mm Water 70 m Rt of I-405 Sta 159+07 157+20 160+29 Conflict with AOA line and retaining wall No. 268 QLA Y RDU-4 Encroachment CR R/W and private owner, encased under roadway U72 m Rt of I-40516 17 MCWD 300 mm Water 70 m Rt of I-405 Sta 156+87 157+20 160+29 Conflict with AOA line and retaining wall No. 268 QLA 4.35 N PU-5 U72 m Rt of I-40517 MH 18 CSDOC Manhole 60 m Rt of I-405 Sta 156+65 156+65 Conflict with I-405 widening QLB 16.20 N PU-5 U28 m Rt of I-40518 19 CSDOC 380 mm Sewer 46 m Lt of I-405 Sta 156+65 156+65 Conflict with I-405 widening QLA 18.40 N PU-5 U25 m Rt of I-40519 20 CSDOC 830 mm Sewer 14 m Rt of B2 Sta 24+96 Conflict with construc—on of B2 line N PU-5 U20 21 CSDOC 830 mm Sewer 6 m Lt of B2 Sta 25+54 Conflict with construcon of B2 line N PU-5 U21 MH 22 CSDOC Manhole 8 m Rt of Main St Sta 102+78 QLB Y RBU-8 MH to be lowered, new top MH elev.=9.588 m C22 MH 23 SCE MH 4503 SCE Manhole No. 4503 8 m Rt of Main St Sta 102+87 QLB Y RBU-8 MH to be lowered, new top MH elev.=9.583 m C23 MH 24 SCE MH 4502 SCE Manhole No. 4502 8 m Rt of Main St Sta 104+17 QLB Y RBU-8 MH to be lowered, new top MH elev.=9.728 m C24

41 needs of a wide range of state DOTs, including linking to existing database systems to avoid data duplication. Addi- tional advantages of a database approach to manage utility conflicts include the following: • The database can be adapted to address changes in DOT needs and business processes. • The DOT can choose to implement all or selected portions of the complete system. • The database is scalable to allow access by many users and store large datasets. • Look-up tables can be easily expanded as needed to accom- modate data items and descriptors unique to the DOT. • The database can link to existing DOT data systems. Once a database system is implemented, its real power lies in its ability to enable a wide range of queries and reports. In addition to the various UCMs replicated during the research, a short sample of reports the prototype database could enable includes the following: • A report listing all utility conflicts associated with company X (for a specific project, corridor, or time frame); • A report of all water facilities in conflict (for a specific project or corridor); • A report summarizing average conflict resolution times for electric facilities statewide; • A report providing average conflict resolution times for water facilities on project Z; • A report listing all utility conflicts with resolution times of more than 100 days; • A customized UCM report listing only the utility conflicts of a specific utility owner; and • A listing of unresolved utility conflicts at time of letting for inclusion in the PS&E package (sometimes called utility certification).