Technologies to Support the Storage, Retrieval, and Use of 3-D Utility Location Data (2015)

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.

18 C h a p t e r 4 In preparation for the pilot stage of the R01A project, a 3-D utility data repository was created in an Oracle database using a 3-D Utility Data Model developed by the project team. The model is meant to represent the selected properties to be stored on 3-D utility data features. These properties have been designed to serve multiple purposes in a single data/ object model. The two primary uses focused on were the following: 1. Decision making and planning functions: Conflict determi- nation, permit management, and planning assessments. 2. Design/analysis functions: Utility conflict resolution, site utility redesign, and new site planning and layout design. To facilitate industry use across multiple-vendor highway- design software environments, no proprietary data model geometry or objects were stored in the 3-D utility model. Data Model Organization The existing Spatial Data Standards for Facilities, Infrastruc- ture, and Environment (SDSFIE) Department of Defense data model (Version 3.0) was taken as a starting reference point for the SHRP 2 consolidated Utility Data Model to ensure a consistent and grounded foundation that is in accepted use. Key utility and site features were extracted from the SDSFIE data model to form the foundational base for the model. With a focus on the utility features in mind, relevant attri- butes identified during the initial Integration Definition (IDEF) planning sessions that would be applicable in an underground utility data model operations framework were added—for example, utility owner and (ASCE) quality. Key properties necessary to define or build intelligent civil design objects were added—for example, pipe diameter and material. In addition to those features and properties that define the utility data model, it was decided over the course of development that the complete utility model should also include selective ancillary and auxiliary model features. These features supplement and provide contextual information rel- evant in servicing subsurface utility engineering use cases. The inclusion of the non-utility-feature information is evi- dence that no single data model can seek to cover any and all applications and use cases. Thus, the model will ultimately be adapted and extended such that it can be appropriately and specifically targeted for various purposes by adopting agen- cies. Inclusion of these additional elements is not intended to expand the application arena and is not intended to define the boundary of information that might directly or indirectly pertain to modeling subsurface utility engineering features and use cases. Based on project assessments and meetings, the utility feature properties were classified into four categories: 1. General properties; 2. Utility-specific properties; 3. Data relationships, quality, and conflict resolution proper- ties; and 4. Civil design–specific properties. Figure 4.1 outlines the general organization for a subset of properties. The data schema—including tables by category (public works features, planning support features, and site support features), entity-relationship diagrams (ERDs), and domain values—are provided in Appendix A. reproducing the prototype 3-D Utility Data repository On completion of the data model for the prototype, the proj- ect created the deliverables needed for future reproduction of the data repository. These were a relational model created by reverse engineering the physical model and two methods for setting up the prototype database. 3-D Data Model and Data Repository

19 Relational Model The Oracle SQL Developer Data Modeler is a standalone application used for designing and translating entity, rela- tional, and physical data models. For the SHRP 2 project, Data Modeler was used to reverse engineer the relational model from the physical model (final Oracle Spatial data- base) for creation of data definition language (DDL) scripts. The DDL scripts are provided in Appendix B. Once the DDL scripts were created, the relational model served as a base for feature and domain table documentation and diagrams. The steps (1–7) listed below are for recreating a graphic view of the SHRP 2 schema documentation used to develop the SHRP 2 3-D model. 1. Extract the Data Modeler files from SHRP2 Schema- Oracle Data Modeler.zip (available at http://www.trb.org/ Main/Blurbs/171927). 2. Oracle SQL Developer Data Modeler is available for vari- ous platforms. Download it from http://www.oracle.com/ technetwork/developer-tools/datamodeler/overview/ index.html. Note that a free Oracle Web account is required for downloads. 3. Once downloaded, uncompress the zip archive, and run datamodeler.exe (32 bit) or datamodeler64.exe (64 bit). 4. Select File -> Open from the application’s main menu, and then locate and open SHRP2 Schema-Oracle.dmd, extracted earlier. 5. In the left browser panel, expand Relational Models and then SHRP2. 6. The Tables node contains feature and domain tables: a. Expand a table node to view fields, indexes, keys, and constraints. b. Double-click a table or field node to display its properties. Figure 4.1. Typical organization for subset of properties.

20 7. The SubViews node contains tables with their foreign-key relations: a. Expand a subview node to view related tables and foreign-key relations. b. Right-click a subview and select Show Diagram to dis- play that table’s ERD. Database Setup Two methods for setting up the developmental prototype 3-D Utility Data Model Oracle database are provided. Oracle DDL scripts provide a human readable export of the Oracle schema that can be directly modified before importing if desired. Oracle Data Pump exports for the prototype Oracle database are also provided. Methods for pulling the DDL or exports are outlined below. DDL Scripts Oracle DDL scripts are provided as two files in SHRP2 DDL Scripts.zip (available at http://www.trb.org/Main/Blurbs/ 171927). The first file, SHRP2-DDL-NoData.sql, creates the database tables, constraints, sequences, and triggers needed to set up an Oracle environment. The second file, SHRP2-DDL- DomainValues.sql, inserts all necessary domain data values used during the GTI SHRP 2 project development and testing. The DDL scripts can be loaded either through Oracle SQL Developer or from the command line using Oracle SQLPlus. To execute the scripts using Oracle SQLPlus, open a new Win- dows command prompt, navigate to the directory containing the DDL files, and then run the following commands: • Oracle SQLPlus username/password @ “SHRP2-DDL- NoData.sql” • Oracle SQLPlus username/password @ “SHRP2-DDL- DomainValues.sql” Oracle Data Pump Included in SHRP2 Oracle Exports.zip (available at http:// www.trb.org/Main/Blurbs/171927) are Oracle Data Pump exports with an empty 3-D Utility Data Model Oracle envi- ronment. The export file was generated using Oracle’s expdp command line utility in the following manner: expdp system/password@dbservice SCHEMAS=GIS DIRECTORY=data_pump_dir DUMPFILE=shrp2_export .dmp LOGFILE=shrp2_export.log The export file can be imported using Oracle’s impdp command line tool. Copy the export file into the default Data Pump directory on the destination Oracle server. A custom directory can be used, but ensure a corresponding Oracle directory object is created.