Www.emarketplace.state.pa.us



System Requirements Specification (SRS)RockITStratigraphic Information Data Management SystemPrepared ForDepartment of Conservation of Natural ResourcesBureau of Topographic and Geologic SurveySubmittedMarch 12, 2010 by525 Oregon Pike Suite 202 ◆Lancaster, PA 17601-7300 ◆ Phone (717) 399-7007 ◆ Fax (717) 399-7015Document Owner(s):Andrew Ross, aross@ , 717-399-7007Shaun Levi, slevi@, 717-399-7007 STYLEREF 1 \s 1 SEQ Table \* ARABIC \s 1 1 Revision HistoryDateAuthorRemarks11/2/2009Andrew RossFirst Draft11/2/2009Shaun LeviFirst Draft3/12/2010Andrew RossSecond Draft STYLEREF 1 \s 1 SEQ Table \* ARABIC \s 1 2 Owners and List of ContactsTitleNameOrganizationEmailProject ManagerSandip PatelDCNR BTGSsapatel@state.pa.usGeologistGale BlackmerDCNR BTGSgblackmer@state.pa.usGeologistMike MooreDCNR BTGSmichmoore@state.pa.usProject SponsorGeorge LoveDCNR BTGSgelove@state.pa.usProject ManagerAndrew RossgeographITaross@Senior GIS AnalystShaun LevigeographITslevi@Table of Contents TOC \o "1-5" \h \z \u Index of Tables PAGEREF _Toc266291767 \h 5Index of Figures PAGEREF _Toc266291768 \h 51 Introduction PAGEREF _Toc266291769 \h 61.1Purpose PAGEREF _Toc266291770 \h 61.2Brief Project History PAGEREF _Toc266291771 \h 61.3Scope PAGEREF _Toc266291772 \h 71.3.1 RockIT Goals and Objectives PAGEREF _Toc266291773 \h 71.4Associated Documentation and Resources (References) PAGEREF _Toc266291774 \h 71.5Naming Conventions, Definitions and Standards PAGEREF _Toc266291775 \h 91.6Relevant Facts and Assumptions PAGEREF _Toc266291776 \h 92 Data Model Foundation PAGEREF _Toc266291777 \h 112.1General Description PAGEREF _Toc266291778 \h 112.2Security and Data Integrity based on Projects and Users PAGEREF _Toc266291779 \h 132.2.1 Project Based Security PAGEREF _Toc266291780 \h 142.2.2 Row Level Access to Data PAGEREF _Toc266291781 \h 152.3Database Integrity & Change Tracking PAGEREF _Toc266291782 \h 172.4Lookup and Reference Tables PAGEREF _Toc266291783 \h 182.5Spatial Reference and Stations PAGEREF _Toc266291784 \h 182.5.1 General Data Model Construct PAGEREF _Toc266291785 \h 182.5.2 Intrinsic Angular Properties of Geologic Features PAGEREF _Toc266291786 \h 242.5.2.1 Planar Elements Table PAGEREF _Toc266291787 \h 252.5.2.2 Linear Elements Table PAGEREF _Toc266291788 \h 262.5.2.3 Planar to Linear Elements Join Table PAGEREF _Toc266291789 \h 272.5.3 Typical Linear Station Scenarios PAGEREF _Toc266291790 \h 282.5.4 Station Type Tables PAGEREF _Toc266291791 \h 363 General Functionality PAGEREF _Toc266291792 \h 403.1Data Searching and Viewing PAGEREF _Toc266291793 \h 413.1.1 Constructing a Search PAGEREF _Toc266291794 \h 423.1.2 Returned Results PAGEREF _Toc266291795 \h 473.2Data Entry and Editing: PAGEREF _Toc266291796 \h 483.2.1 Editing Permissions PAGEREF _Toc266291797 \h 493.2.2 Required Editing Functionality PAGEREF _Toc266291798 \h 493.3Data Import PAGEREF _Toc266291799 \h 573.3.1 Single or Batch Importing PAGEREF _Toc266291800 \h 583.3.2 Data Migration Scripts PAGEREF _Toc266291801 \h 583.4Data Reporting and Printing PAGEREF _Toc266291802 \h 583.5Data Export PAGEREF _Toc266291803 \h 593.6Data Visualization PAGEREF _Toc266291804 \h 593.7System Administration PAGEREF _Toc266291805 \h 603.7.1 Manage User Accounts PAGEREF _Toc266291806 \h 603.7.2 Manage Projects PAGEREF _Toc266291807 \h 623.7.3 Manage Lookup and Data Reference Tables PAGEREF _Toc266291808 \h 624 Detailed Data Model PAGEREF _Toc266291809 \h 644.1Images & Documents PAGEREF _Toc266291810 \h 644.2Geologic Features / Events / Samples and Analysis Results PAGEREF _Toc266291811 \h 664.2.1.1 Possibility of Schema Changes PAGEREF _Toc266291812 \h 664.2.2 Bedrock Geology PAGEREF _Toc266291813 \h 664.2.3 Coal Occurrences PAGEREF _Toc266291814 \h 884.2.4 Surficial Geology PAGEREF _Toc266291815 \h 894.2.5 Faults PAGEREF _Toc266291816 \h 914.2.6 Folds PAGEREF _Toc266291817 \h 924.2.7 Lithologic Contacts PAGEREF _Toc266291818 \h 934.2.8 Landslides PAGEREF _Toc266291819 \h 934.2.9 Karst Features PAGEREF _Toc266291820 \h 954.2.10 Interpretations / Official Stratigraphic Units PAGEREF _Toc266291821 \h 984.2.10.1 Data Structures PAGEREF _Toc266291822 \h 984.2.10.2 Searching the Data PAGEREF _Toc266291823 \h 994.2.10.3 Data Maintenance PAGEREF _Toc266291824 \h 1004.2.11 Samples PAGEREF _Toc266291825 \h 1004.2.11.1 Coal Samples PAGEREF _Toc266291826 \h 1014.2.11.2 CBM Samples PAGEREF _Toc266291827 \h 1024.2.11.3 Rock Samples PAGEREF _Toc266291828 \h 1034.2.11.4 Water Samples PAGEREF _Toc266291829 \h 1044.2.12 Analysis Results PAGEREF _Toc266291830 \h 1055 Data Sources and Migration Strategy PAGEREF _Toc266291831 \h 1095.1Users PAGEREF _Toc266291832 \h 1095.2Projects PAGEREF _Toc266291833 \h 1095.3Events / Features / Samples and Associated Stations PAGEREF _Toc266291834 \h 1105.4Survey and Measured Section Data PAGEREF _Toc266291835 \h 1125.5Samples and Analysis Results PAGEREF _Toc266291836 \h 1125.6Image Data PAGEREF _Toc266291837 \h 1136 System Administration PAGEREF _Toc266291838 \h 1156.1User Accounts PAGEREF _Toc266291839 \h 1156.1.1 USER Table PAGEREF _Toc266291840 \h 1156.1.2 Public Viewer PAGEREF _Toc266291841 \h 1166.2Projects and Edit Permissions PAGEREF _Toc266291842 \h 1166.2.1 PROJECT_PERMISSIONS Table PAGEREF _Toc266291843 \h 1176.2.2 General Project Permissions PAGEREF _Toc266291844 \h 1177 System Architecture Considerations PAGEREF _Toc266291845 \h 1187.1System Level Security PAGEREF _Toc266291846 \h 1197.2Reliability and Availability PAGEREF _Toc266291847 \h 1197.3Backup and Recovery PAGEREF _Toc266291848 \h 1197.4Capacity Requirements PAGEREF _Toc266291849 \h 1197.5Scalability and Extensibility Requirements PAGEREF _Toc266291850 \h 1197.6Capture of Field Data via Mobile Devices PAGEREF _Toc266291851 \h 1208 User Documentation and Training PAGEREF _Toc266291852 \h 1218.1User Documentation PAGEREF _Toc266291853 \h 1218.2User Training PAGEREF _Toc266291854 \h 1219 Dependencies PAGEREF _Toc266291855 \h 12210 Appendixes PAGEREF _Toc266291856 \h 123Appendix A: Reference & Lookup tables PAGEREF _Toc266291857 \h 123Appendix B: Measurement Parameters PAGEREF _Toc266291858 \h 134Appendix C: Requirements Specification Signoffs PAGEREF _Toc266291859 \h 139Index of Tables TOC \h \z \c "Table" 11 Revision History PAGEREF _Toc266291860 \h 212 Owners and List of Contacts PAGEREF _Toc266291861 \h 2Table 11 Project Stake Holders PAGEREF _Toc266291862 \h 8Table 12 Definitions PAGEREF _Toc266291863 \h 9Table 21 Point & Linear Stations against Point and Traversed features PAGEREF _Toc266291864 \h 24Table 22 Data Input Required for Typical Linear Station Scenarios PAGEREF _Toc266291865 \h 28Table 51 General Data Sources PAGEREF _Toc266291866 \h 109Table 52 Drill Hole Data Sources PAGEREF _Toc266291867 \h 110Table 53 Drillers' Logs Data Sources PAGEREF _Toc266291868 \h 110Table 54 Outcrops Data Sources PAGEREF _Toc266291869 \h 111Table 55 Landslide Data Sources PAGEREF _Toc266291870 \h 111Table 56 Karst Feature Data Sources PAGEREF _Toc266291871 \h 111Table 57 Drill Hole & Measured Section Survey Data Sources PAGEREF _Toc266291872 \h 112Table 58 Coal Quality Analysis Data Sources PAGEREF _Toc266291873 \h 112Table 59 Other Analysis Data Sources PAGEREF _Toc266291874 \h 112Table 510 Image Data Sources PAGEREF _Toc266291875 \h 113Table 61 Envisioned Permissions by Type of Staff PAGEREF _Toc266291876 \h 115Table 62 Sample User Table PAGEREF _Toc266291877 \h 116Table 63 Sample Projects Table PAGEREF _Toc266291878 \h 117Table 101 Planar and Linear Orientation Measurement Conventions PAGEREF _Toc266291879 \h 138Index of Figures TOC \h \z \c "Figure" Figure 21 Conceptual Data Architecture for Geologic Information System PAGEREF _Toc266291880 \h 13Figure 22 Ownership of Data by the Row PAGEREF _Toc266291881 \h 17Figure 23 Conceptual Model for Point & Linear Stations (Outcrops and Drill Hole Data) PAGEREF _Toc266291882 \h 33Figure 24 Conceptual Model for Linear Stations (Measured Section) PAGEREF _Toc266291883 \h 34Figure 25 Table Structure for Geologic Features, Events & Samples, Coordinates are Calculated On The Fly PAGEREF _Toc266291884 \h 35Figure 31 General Functionality Layout PAGEREF _Toc266291885 \h 40Figure 32 Schematic Structure of User Interface PAGEREF _Toc266291886 \h 41Figure 33 Returned Results Interface PAGEREF _Toc266291887 \h 48Figure 41 Table Structure for Images & Documents PAGEREF _Toc266291888 \h 65Figure 42 Interface to Search by Lithologic Unit and Age PAGEREF _Toc266291889 \h 99Figure 71 Generalized Server Setup PAGEREF _Toc266291890 \h 118IntroductionPurposeThe Bureau of Topographic and Geologic Survey, a division of the Pennsylvania Department of Conversation of Natural Resources, has solicited geographIT to develop a system requirements document for a “Stratigraphic Database”. Notwithstanding the scope implied by the title is to build a comprehensive geological information system composed of a database back end and a GUI front end which will provide broad access to the geologic data (field and lab findings, active and archived) that are owned and managed by the Bureau. The system shall ensure data quality by enforcing all of the relevant information related business rules in effect at the Bureau and will provide powerful querying, reporting, and integrated visualization tools.This system will be implemented using Oracle as the back end RDBMS and web / browser based tools for the GUI front end. The use of Oracle is predicated on the fact that the Bureau already has support for Oracle and is currently using it to host a prototype of this system. A web-based user interface is proposed for ease of deployment, ability to reach a wide audience, including the public at large if so desired, and the ready availability of skilled professionals capable of implementing and maintaining such a system.It is expected that these new tools combined with the enterprise database design will yield one of the most sophisticated geologic knowledge systems in the Country’s public resources.Brief Project HistoryThe Bureau of Topographic and Geologic Survey collects, catalogs, and provides the official mapping for all bedrock and surficial geologic features within the commonwealth of Pennsylvania. In its capacity as the official, objective source of geologic facts, the Bureau is regularly solicited by private and public parties to provide information and/or expertise regarding Pennsylvania’s geologic resources and structures. To date most field and lab findings are stored in printed archives, or in disparate locations within individual researcher’s computers. Likewise, the majority of the Bureau’s mapping efforts make use of a limited number of data sets which are painstakingly prepared for a narrow set of specific purposes and are then kept in static storage, frequently only in hardcopy (paper, mylar) format. While individually geologists can respond to data requests and manage data analysis, collectively the Bureau does not have tools in place to support collaborative work or intensive data querying across the varied research tracts coordinated by the Bureau.The Bureau has thus begun a series of initiatives (of which this document forms a part) to define and develop a framework of tools and databases meant to establish an environment in which the latest geological data is available throughout the enterprise and can be truly leveraged to promote collaborative work (a “collective state of the data” for lack of a better term). To date a database and an associated front-end module designed for geologic “Outcrops” data (also known as the “Outcrops module”) have been developed. This module is already paying dividends at the Bureau as it is facilitating data sharing amongst geologists, allowing the incorporation and manipulation of digital files obtained in the field and supporting specific data export functions to third party software for mapping and other applications. This module represents a template that the Bureau wants to extend and apply broadly to all geologic content.To provide a simple reference and establish a product name, geographIT coined the name RockIT for the Bureau’s geologic information database. RockIT will be used for the remainder of this systems requirements document to reference the geologic information database.Scope RockIT Goals and ObjectivesDevelop a comprehensive database that securely stores the geologic content currently designated for this purpose by the Bureau, as well as corresponding archive materials, such as paper files, image files, and various independent databases.Develop a browser-based user interface that provides the following functionality:Data input toolsData editing toolsData search toolsReporting toolsData import-export toolsField data uploading toolsSystem administration for all aspects of the database and applicationsEstablish a Bureau-wide protocol that centralizes all data storage within the RockIT database and enables geologists to securely share as well as review and utilize each other’s data.Implement a “historical data / audit trail” management policy where dated information can be “obsoleted” and superseded by newer data, but is never deleted from the system.Maintain a clear distinction between geological / stratigraphic interpretations and the raw data that support these.Provide the ability to reference data in two-dimensional and three-dimensional space, storing corresponding two-dimensional (X, Y) and three-dimensional coordinates (X, Y, Z) together with the data, and leverage this framework for integration with GIS and other third party modeling software.Define a data migration workflow to facilitate efforts of moving data archives into the databaseThe Commonwealth’s protocol for application / database acquisition stipulates a clear contractual process that separates system requirements from any system design or implementation. This project addresses the definition of the system requirements for developing RockIT such that it meets the aforementioned goals and objectives. In terms of our stated goals this means:The conceptual framework of the data storage requirementsThe functional requirements for the supporting applications. This leaves the actual development of the system to subsequent contract phases.As previously mentioned, the Bureau has developed a discrete portion of RockIT, what is currently called the “Outcrop” module. As part of this systems requirements project geographIT reviewed the Outcrop module and identified areas where the current database and user interface will need to be modified. These modifications will knit the Outcrops data into the overall data management framework described in the ensuing system requirements. This would include:New conceptual framework for data storage requirements for outcrop dataNew functional requirements for applications handling outcrop dataFormulation of data migration tasks from the current database schema into the extended future schemaAssociated Documentation and Resources (References)geographIT reviewed the documentation and applications produced during the development of the Outcrop module. The specific documents referenced include the following:Stratigraphic Database Development Project Software Requirements Specifications Document June 13 2005.Stratigraphic Database Development Project Software Design Specifications Document September 9 2005.Outcrops Module – application and database designAdditionally, geographIT interviewed the following users and stakeholders within the Bureau:Table 11 Project Stake HoldersBureau ContactGeologic ContentGale BlackmerOutcrops, Thin Sections, Isotopic AgeNOTE: Gale participated in all interviews with the exception of surficial depositsMichael MooreCoal Quality Analysis, Surficial DepositsCliff DodgeCoal Quality Analysis, Drill HolesBill BragonierCoal Quality Analysis, Drill HolesJohn BarnesGeochemical Analysis, XRD, SEM, Uranium OccurrencesSteve ShankGeochemical Analysis, XRD, SEM, Isotopic Age, Thin SectionsToni MarkowskiCoal-bed Methane (CBM) WellsGary FleegerSurficial Deposits, GroundwaterHelen DelanoLandslidesBill KochanovSinkholesNaming Conventions, Definitions and StandardsTable 12 DefinitionsAcronymDefinitionCBMCoal Bed MethaneDDDecimal DegreesDMSDegrees Minutes SecondsFIPSFederal Information Processing Standard (identification code)GISGeographic Information SystemsGPSGlobal Positioning SystemGUIGraphic User InterfacePAGWISPennsylvania Ground Water Information SystemSEMScanning Electron MicroscopeWISWell Information SystemXRDX-ray DiffractionRelevant Facts and AssumptionsBecause the success of this project depends on a competent appreciation of geological subject matter, as well as a critical understanding of systems architecture issues and strategies, geographIT has fielded a team that includes a geologist/senior GIS analyst and a database / application design specialist / senior GIS analyst. Before this document was written the team conducted a review of the existing system followed by a series of interviews with the Bureau subject matter experts / eventual users of the proposed system. Thus the team obtained a first hand perspective of the business processes that are to be covered, as well as the raw data that feed and is produced by these.As previously mentioned, the Bureau of Topographic and Geologic Survey (Bureau) initiated its first attempt to design and implement the geologic database in 2005. That original effort yielded the Outcrops Module which includes an Oracle database design and user interface.As part of that project the Bureau also took delivery of a set of documents, which included an outcrops module user manual, an outcrop module system specification, a set of SQL scripts designed to instantiate the schema of the database and a set of preliminary requirements for the remaining modules of this system (see section 1.4 Associated Documentation and Resources (References) above.Although the Bureau has been pleased with the Outcrops module and its efficacy, it has cautioned geographIT from relying too much on it as a template on which to define the broader Bureau-wide data storage and application requirements.However it was recognized early on that this body of work represented an unmatched opportunity to get at the business requirements set forth by the Bureau for this series of initiatives, so as a first step of the review the project, the Outcrops module was evaluated by the geographIT team. This evaluation included a study of the documents, hands on interaction with the live system, collecting the experience of its users noting the features that were successful as well as suggested improvements and finally instantiating and reviewing a copy of the existing database schema using the SQL scripts delivered as part of the outcrops module.The original strategy governing development of the existing system divided content and functionality into discrete modules, containing all of the database (storage) and application components required to function independently. This strategy, as implied by Bureau staff, involved adding new modules by creating new front end applications that would function in parallel to the outcrops application, while extending the existing database to make it function as a common repository for the new kinds of information that would result. The key principle in determining the definition of the modules has been the workflows and tasks performed by the Bureau’s geologists and staff, such that, next to the outcrops module, for example, there would be a module for drill holes, a module for surficial geology, etc.Regarding the database we agree entirely with the concept of a common repository for all of the data types to be handled by the system. This was underscored by the multitude of relationships among distinct types of data that we identified during our interviews of Bureau geologists. These relationships would be very difficult to support if the datasets resided in different databases. In fact, these relationships seem so important and pervasive that we believe the data must be organized differently than initially conceived, that is primarily in terms of the nature of its content, rather than in terms of the workflows that produce it. Thus, for example bedrock lithology will become a primary “storage category”, while the fact that it was obtained from outcrops or drill holes would be included in a separate “location storage category”. This does not mean that workflows as defined by Bureau staff will not be supported by RockIT, what it means is that the role of data design will be to support the correct description of content while it will be the role of the applications to support workflows. We believe that this clear distinction will ultimately result in a clean, uncluttered database design, which in turn will allow application development to be systematic and flexible.Regarding the application front end / user interface, as previously mentioned, we recommend the existing interface be replaced by a web based interface implementing asynchronous communication with the server back end. To reiterate our reasoning for this choice, web technology offers centralized maintenance, scalability of deployment, including the public at large and an extensive pool of available, skilled professionals. Web technology also offers flexible paths for future integration with existing and planned public-facing web applications hosted by the Bureau, such as WIS, PAGWIS, Geologic Hazards (Sinkholes, Landslides), etc.Data Model FoundationGeneral DescriptionAs mentioned above, as a result of our comprehensive survey we’ve arrived at a data architecture which organizes data in terms of the nature of its content rather than the workflows that produce it. To comply with this model, the concept of “what the data is” has to be clearly isolated from other concepts such as “what location in space is described by this data”, “how is this data grouped” and “who owns the data”.This is important because it will allow data categories to be defined with a minimum of conceptual redundancies or ambiguities so that:Once the system is implemented it shall be possible to extend it to capture new types of geologic features and descriptive details without having to re-architect the entire system / code foundation.Applications written to deal with individual data categories will be more or less independent of each other and contain a minimum of redundant functionality, resulting in code that is slimmer, more modular and easier to maintain.Procedures and applications created to handle this data can be standardized and reused, minimizing the need to create special code for every situation.A system with clearly defined components also becomes easier to master and has a better chance of being adopted within the organization it is meant to serve.The application of this philosophy in our review of geologic data types has resulted in a hierarchical “tree like data architecture which in general terms includes the following tiers / levels:Subject Matter Details / Analysis Results:This level represents the actual geologic data / content (“what the data is”) that describes every geologic feature / event / sample stored in this system.This level consists of multiple specialized tables which would typically contain items such as rock descriptions, lithologic interpretations, geochemical analyses and other lab results, special data types such as XRD, SEM and thin-sections, sinkhole descriptions, landslide descriptions, field and sample pictures, etc. These are described in more detail in the “Geologic Features / Events / Samples and Analysis Results” section 4.2.Geologic Features / Events / Samples:This level represents the actual geologic entities that are being described by the aforementioned Subject Matter Details / Test Results. The term features will henceforth be used to imply geologic features, geologic events or geologic samples.This level consists of multiple tables defined for individual types of geologic features. These are described in more detail under “Geologic Features / Events / Samples and Analysis Results” section 4.2.This level exists primarily to allow for the association of multiple types of details / results with a single feature, as well as to make possible the existence of adjacent features (of the same or different type) such that each feature is associated with its own set of details and results. A particular set of details or results, for its part, can belong to only one geologic feature / event / sample.Stations: Stations represent geometric placeholders that serve to reference geologic Features to distinct locations in space. A Station may group many adjacent or coincident Geologic Features; however, a Geologic Feature / Event / Sample may belong to only one station. Additionally, “Survey data” may be associated with a station if it represents a drill hole or a measured section. There shall exist three types of tables for stations:Stations Table: Includes the primary header data and origin coordinatesSurvey Table: Includes survey data for drill holes and measured sectionsStation Type Tables: Include specialized data required for specific types of stations.These are described in more detail under “Spatial Reference and Stations” below.Projects: The Projects level represents groupings of data based on user-defined criteria (geographic extent, research methodology or date of incident, for example). Projects are an aggregate unit, grouping all data content: features / events and samples. If a geologic feature belongs to a project, so do all of the subject matter details and test results that belong to the feature. A project can have many geologic features but a geologic feature can belong to only one project. Projects also represent the basic unit of data by which security is managed in this system. Project information is stored in two tables, a main projects table and a PROJECT_PERMISSIONS table. These are described in more detail under “Security and Data Integrity based on Projects and Users” below, with additional information in the System Administration, Section 6.Users: Users represent “logins” into the geologic information system. Logins will be assigned an edit permission level: read, edit, none (for logins that have been retired). A user shall own or have a set of permissions to one or more complete projects but not to part of a project. There shall be only one owner for a project.A user shall be able to own one or more stations. There shall, however, be only one owner for a station.User information is stored in a “USERS” tableUser ownership over projects is implemented by means of the aforementioned PROJECT_PERMISSIONS table. The main USERS table and the PROJECT_PERMISSIONS table are described in more detail under “Security and Data Integrity based on Projects and Users” below.Ownership over stations shall be implemented by including a “user id” foreign key in the main stations table.Additional basic rules regarding overall data organization:User ids/names must be unique system wide.Projects shall have two identifier columns:User defined project name, does not have to be unique system-wide but the system shall alert a user trying to create a project with a name that already exists. Project listing functions of RockIT shall include user names where project names are non-unique.Machine generated project id guaranteed to be unique system wide.Stations shall also have two identifier columns:User define station name, does not have to be unique system-wide but the system shall alert a user trying to create a station with a name that already exists. Station listing functions of RockIT shall include user names where station names are non-unique.Machine generated station id guaranteed to be unique system wide.Stations are a special case in that they are associated users directly, rather than to projects. This provision makes it easy for stations to be shared by geologic features belonging to multiple projects. This means that stations can be employed by users who do not own them in order to reference new geologic features to pre-defined locations in space.Stations may only be modified by their owners. Modifications to stations may include updates to the header information as well as adjustments to spatial positions in cases where coordinates might include errors.It shall be possible to re-assign specific geologic features from one station to another in order to improve spatial accuracy where required. In order to protect information representing the historical origin of the data, it shall not be possible to move geologic features from one project to anotherFigure 21 Conceptual Data Architecture for Geologic Information SystemIt shall be possible to create a virtual association of geologic events / features / samples to projects other than the ones in which they were created. This shall be implemented by means of a join table which shall store the association of project ids to event / feature / sample ids. It shall be possible to transfer ownership of a project from one user to another. This shall be a system administrator function. A chain of ownership shall be preserved by implementing fields for starting and ending dates of ownership within the POJECT_PERMISSIONS table.Security and Data Integrity based on Projects and UsersSecurity in RockIT means keeping users data safe from accidental or malicious corruption or deletion. In overall terms security is implemented at two levels, system and data.From the system perspective security means implementing the necessary measures when setting up the hardware and underlying software framework. This means setting up network access / firewalls and including safeguards in the software to prevent cross site scripting, code injection, session hijacking, etc.From the data perspective security means defining data structures necessary to implement user logins as well as a scheme to control access to data based on these logins. It also means setting the necessary referential integrity constraints to ensure that data is consistent.This section deals with security at the data level, the requirements / business rules for this are described as follows:Project Based SecurityAs mentioned before, the basic unit of security is the project. Ownership and use permissions for the data shall be granted or denied by the project.Also as mentioned previously, project information shall be stored in a main PROJECTS table and a PROJECT_PERMISSIONS table.The main PROJECTS table will contain primarily the header / description data associated with a project. This will consist at a minimum of the following columns:Project IdProject NameProject CreatorDate Project CreatedCurrent Project OwnerLast Editor IDDate Last EditedDescriptionThe PROJECT_PERMISSIONS table is essentially a projects/users write permissions matrix: This table maintains a list of projects and the users who have write permissions to them. At a minimum this will contain the following fields:User IdProject idGrant DateGrantorOwnership Start Date (populated if owner, null if not owner)Ownership End Date (populated if ownership expired, null if not owner or ownership current)Geologic feature / event / sample tables maintain the relationship with projects by implementing project id foreign key. Subject matter details and analysis results do not need this foreign key as they are already associated with features. The stations table also maintains the relationship with projects by means of a project id foreign key.By default all users have read access to all projects. By default write access to a project is only given to the user who created the project.Project owners (creators) can grant other users write access to the projects that they own.To keep track of which users have been granted write access to which projects, a PROJECT_PERMISSIONS table is implemented and automatically maintained by the system (see System Administration, section 6)It is not compulsory for a user to define a project for every piece of data entered into the system. If no project is defined the data is transparently associated with a “general” project, readable and writeable by everybody except for public / restricted users.When a station owner spatially moves a station that references features belonging to multiple users, a new station shall be created so that the other users’ features are not also moved without their permission. The system shall notify the corresponding users of such a change and allow them to take any actions they deem appropriate, such as assigning their geologic events / features / samples to the new station.There shall be a “public viewer” user, different from all other “internal” users. This “public viewer’ user shall have a special, limited set of permissions and will be used by the system for “non-login” connections such as those coming in from the internet or from public access “kiosk” terminals.Only the system administrator shall be able to modify the permissions regarding the “public viewer”Confidentiality: Data tagged as confidential can still be read by all internal users but not by the “public viewer” user.Data is tagged as confidential by the individual geologic feature / event / sample.If a feature / event / sample is confidential, neither it nor its corresponding subject matter details / test results will be visible to the “public viewer”. RockIT shall provide tools to simultaneously tag as confidential all of the data belonging to a project. The user shall then be able to remove the confidentiality tag from individual features as she/he sees fit.To promote maximum re-use of stations, it shall not be possible to tag stations as confidential. This means that a station could spatially reference confidential (hidden) data at the same time as non-confidential data.Owners can tag data as obsolete but cannot delete it directly. Only the system administrator can delete data.By default, users enter the system in “read only” mode to prevent them from accidentally making changes while reviewing data. Editing sessions are toggled on via button controls located at various levels of data viewing. See Data Entry and Editing, section 3.2 for more details.It should not be possible for users to bypass security restrictions by logging-in directly to the database.Row Level Access to DataRow level access is implemented within databases to allow users to own individual records within tables rather than complete tables. Essentially this involves including a foreign key to a users / owners table within every table whose rows are to be distributed among multiple users. Each row in such a table can then be populated with the id of the user to which it belongs. The Outcrops application currently implements such an approach. We propose that RockIT implement a similar ownership scheme, albeit with several modifications / improvements:While the current Outcrops application assigns ownership of all data directly to users, we advocate that ownership of all data (with the exception of stations) be assigned to projects (see previous section). If a user leaves the Bureau or is assigned to a different project, it becomes trivial to change the ownership of all of her/his data as it would only require changing ownership of a project within the projects table.While the current Outcrops application implements the ownership foreign key on every table in the system, we propose the exclusion of tables whose records are tied by means of foreign keys to other tables which already have the ownership foreign key. For example, if bedrock features are already assigned to projects, then it is not necessary for details, which are assigned to bedrock features, to also be assigned to projects. Every detail by implication belongs to the project to which its parent feature belongs. This trait reduces redundancy and prevents the accidental entry of contradictory data.Beyond the creation and population of ownership foreign keys a working implementation of row level access needs to control the actions that are allowable on the data based on who owns it. The industry standard methods for implementing row level access control can be “application” oriented or “database” oriented. Either type could be implemented but the following caveats need to be noted:The real difference between the two approaches is that the database oriented approach (in this case Oracle’s row level security implementation) is designed to support users logging in and running queries directly against a database, while an application oriented approach presupposes that users will interact with a database through an application rather than connect to it directly.Both approaches will require some specialized data structures to exist, such as a permissions matrix of users against projects, and both approaches will prevent users from touching the real data directly, implementing indirect “filtered” access instead. In an application oriented approach the application running under a special user reads the permissions matrix and performs the allowable operations on behalf of the user. In a database oriented approach, views which expose data such that users can only read or modify data according to their set of permissions, are created for all tables.Although we do not rule out either approach, in this case we recommend an application oriented row level security approach. We do this for the following two reasons:We do not believe that users should be accessing the database directly under any circumstances (a skilled malicious user could find a weakness and exploit it, an unskilled user may unintentionally corrupt the Bureau’s data), so there really is no need for this capability. Database oriented row level security is at least an order of magnitude more complicated and laborious to set up and maintain than application oriented row level security.The following diagram illustrates how ownership of rows in geologic feature tables (Bedrock and Surficial Geology in this example) is assigned to projects by means of a Project foreign key. Ownership of projects is in turn assigned to users by means of the Current User “foreign key”. Essentially this transfers ownership of records in geologic feature tables to users. In addition the PROJECT_PERMISSIONS table maintains the list of all the additional users that have write permissions within existing projects. This in turn enables write / edit permissions for users in specific sets of rows within geologic feature tables.Figure 22 Ownership of Data by the RowDatabase Integrity & Change TrackingReferential integrity must be maintained, there can be no orphaned records, that is:There can be no details / results not owned by any geologic feature. If found, orphaned details / results should be deleted from the database.There can be no features not associated with a station. If found, orphaned features and all of their details should be deleted from the databaseThere can be no features not associated with a project. If found, orphaned features and all of their details should be assigned to a “lost and found” or the “general” project.There can be no stations not owned by any project. If found, orphaned stations and their associated features and details should be moved to a “lost and found” or the “stations” project.There can be no projects not owned by a user. If found, orphaned projects and all of their associated data should be assigned to a “system administrator” user.If a station or a project must be deleted then a cascading delete must be enforced so that all of the data associated to it is also deleted, thus preventing the existence of orphaned records. It is possible to move data from one project to another, if relevant records need to be salvaged before deleting all content from the database.To prevent data inconsistencies this database has to be transactional. Since most data changes (transactions) will consist of more than one operation, none should be committed unless all operations corresponding to a change succeeded without errors.It should be possible to track the changes made by individual users.It should be possible to roll back the database to a specific state or save point.Where it is not possible to secure the above conditions through database and software design, the system shall have quality assurance/quality control reviews and reporting to help maintain data integrity and identify data management issues that need to be addressed.Change tracking in the current outcrops application is already implemented by means of populating a timestamp and the id of the acting user in every record that is modified. This is a good solution but it has the disadvantage that every data record only documents the last user that applied a change to it. We suggest that in addition to this approach a customized log be implemented in a table. This table would store the following pieces of information: TimestampUserProjectStationAffected tableText field for the actual insert or update query (or delete query in the case of the system administrator) that the application ran on behalf of the user.Select queries would not be captured as they do not modify the database. This method should be implemented such that it only contains queries belonging to transactions that completed successfully.While in extreme cases this custom log could be used to reconstruct the database (which is redundant with Oracle’s transaction logs), its real value lies in the ability to easily reconstruct an event or series of events that might have adversely affected the data.Lookup and Reference TablesLookup and reference tables within any system serve to speed up data entry and prevent errors in cases where the contents of a particular data item can only take on one of a limited set of pre-defined values. This system must, as much as possible, make use of such tables given the broad spectrum of data that it is meant to handle.Typically, this functionality is implemented at the database level by means of a foreign key in the “target” table representing a primary key in a reference or lookup table. In some cases the value of the foreign / primary key is all that is needed in the target table, in other cases the reference table can in of itself be quite complex and require multiple fields. Complex and simple references are almost identical as far as the database is concerned; however complex references require additional application mechanisms to be properly viewable and searchable.In most cases foreign keys must be nullable, that is they should not force a user to make a choice about its contents as a condition to enter a new record. This is necessary as all information may not be available when a new record is entered. Once a user is ready to make a choice, only those in the reference table would be available.Lookup and reference tables identified for this system are provided in Appendix A (note: it is not an exhaustive listing).Spatial Reference and StationsAs mentioned before, stations represent geometric placeholders that serve to reference geologic features to distinct locations in space. A station groups geologic features by common location such that if a station’s location or geometry is altered, all of the related geologic features and samples can have their locations updated in one operation.General Data Model ConstructBusiness processes and data structures associated with stations should meet the following goals:It should be possible to create a station almost transparently while entering any type of Geologic feature.Once a station has been created in this way, the association between station and geologic feature is stored in the geologic feature by means of a station id foreign key.Stations shall be “typed” by means of a field indicating the type of “location” that they describe (outcrop, drill-hole, etc.). Multiple features of different types can be associated to a single station. The ability to combine different types of stations with different types of features provides the ability to describe a broad range of scenarios.Our investigation showed that there are 2 main types of stations which tie geologic features to space in distinct ways:Stations can represent a point on the surface of the Earth (for example an outcrop). All geologic features associated to this type of station are assumed to be located at the same coordinates as the station.Stations representing drill holes and measured sections are defined as linear geometric constructs (trajectories) through 3D space. Geologic features associated with this type of station are tied to space indirectly, by means of a longitudinal position or interval along the station’s 3d trajectory. Our model adapts to these scenarios by defining a database construct capable of representing “point like” and linear stations. This model has the following characteristics:Linear stations are defined by this model as an extension of point stations. As a result both types share common elements which can be managed by means of a common code base.The model conceptually separates linear stations into “starting points” and “survey data”. A starting point, as the name implies, is the point in space where the trajectory of a linear station is defined to start. This, for example could be the location of the drill collar for a drill-hole. Point stations and starting points of linear stations are essentially the same thing, thus they are stored in the same “stations” table (see the diagram on figure 2-3).The survey data, which defines the actual trajectory of the linear station through space, consists of a series of angles and distances, which are kept in a specially designated “survey table” (see the diagram on figure 2-3). This table also has a foreign key, which maintains its relationship with the stations table.Note that angles are stored using a standard set of columns which are used throughout the RockIT system (for example see Linear Elements Table below)This setup makes it possible to move an entire linear station by just changing the coordinates of the starting point, without requiring a corresponding adjustment of every piece of survey data.This also makes it relatively easy to convert a linear station to a point station and vise versa. All that is necessary to make a point station out of a linear station is to delete its corresponding survey data in the survey table. The starting point as well as any associated geologic feature data can continue to exist undisturbed. Conversely, a linear station can be made out of a point station by creating associated survey data in the survey table. Note that if a “drill-hole” station loses its survey data, the system still knows that the station is a drill hole because of the contents of the “type field” in the stations table, and shall be able to handle the feature / event / sample data accordingly.The Stations table shall include the following columns:Station ID: Primary key of Stations table. This is automatically generated.User Defined Station Name: This field allows the definition of a user friendly name for a station. These names do not have to be unique system wide, however if a chosen name is equal to pre-existing name the system shall alert the user and provide the ability to change the name.Three fields for X, Y and Z coordinate values: This is the spatial location for a point like station. For a drill hole (linear station) this would be the location of the collar.Coordinate System & Precision: Horizontal values shall be stored as lat long in decimal degrees using double precision floating point numbers and shall conform to the NAD 83 coordinate system. Vertical coordinates shall by default be expressed in feet above mean sea level conforming to the North American Vertical Datum of 88 (NAVD 88). It shall be possible to enter horizontal and vertical coordinates in different coordinate systems and units and have the software transform these into the default units. This functionality can be implemented through a variety of freely available OGC and USGS software libraries (for example Proj4, Vertcon).This functionality should also make it possible to obtain coordinates for points on assumed grids based on sets of control points and azimuths.Optionally, it shall also be possible to use Lidar data to extract/replace vertical coordinates for stations located on the surface from Lidar data. This is highly desirable because Lidar data currently has the best available vertical accuracy and already conforms to the NAVD 88 vertical datum.Source Horizontal Datum: (Optional) If the “source” horizontal coordinates of a station were initially given in a non-default datum (or units) and thus were subsequently transformed to the default datum (or units), this field shall be populated with a term describing the original datum and units. This field shall be controlled by a lookup table which shall contain values such as “NAD 27 meters”, “Assumed Grid feet”, etc.Source Vertical Datum: (Optional) ) If the “source” vertical coordinates of a station were initially given in a non-default datum (or units) and thus were subsequently transformed to the default datum (or units) this field shall be populated with a term describing the original datum and units. This field shall be controlled by a lookup table which shall contain values such as “NGVD 29 meters”, “Assumed Elevation feet”, etc.Horizontal Coordinate Quality: (Optional) This field can contain two values: “Surveyed” and “Estimated” which indicate how the horizontal position of a station’s origin point was obtained. This qualifying information should be easily visible on the user interface.Vertical Coordinate Quality: (Optional) This field can contain two values: “Surveyed” and “Estimated” which indicate how the vertical position of a station’s origin point was obtained. This qualifying information should be easily visible on the user interface.Station Type: This shall include values such as “Outcrop”, “Drill Hole”, etc. It shall be controlled by a lookup table.Owner (Creator ID): This field shall contain the user name of the owner of this station. This field shall be populated only with values from the Users table.Creation Date: This field captures the date on which this station was created. This field shall be exposed but not editable through the user interface.Last Editor ID: This field captures the user id of the user that last edited this station. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Last Edit Date: This field captures the date on which this station was last edited. This field is neither exposed nor editable through the user interface.USGS Quad: This field shall contain the name of the 7.5’, 1:24,000 USGS in which a station is located.County Name: Name of County in which a station is located. This field shall be controlled by a lookup table. Populating this field will cause the County FIPS Code field to be automatically populated with the corresponding FIPS code.County FIPS Code: FIPS code of County in which a station is located. This field shall be controlled by a lookup table. Populating this field will cause the County Name field to be automatically populated with the corresponding name.Township Name: Name of Township in which a station is located. This field shall be controlled by a lookup table. Populating this field will cause the Township FIPS Code field to be automatically populated with the corresponding FIPS code. Township FIPS Code: FIPS code of Township in which a station is located. This field shall be controlled by a lookup table. Populating this field will cause the Township Name field to be automatically populated with the corresponding name.Other Fields: Other fields may be found to be required during the design phase of RockIT.The Survey table shall include the following columns:Survey ID: Primary key of Survey table. This uniquely identifies every survey measurement (traverse point). This is automatically generated.Station ID: Foreign key associating this traverse point to a particular station.Distance: Distance of this traverse point to a starting point or drill hole collar.Length Units: Units in which distance is expressed (feet, meters, etc). This field shall be controlled by a lookup table.Azimuth: (Optional) Horizontal angle measurement for this traverse point, measured clockwise from North, from 0 to 360 degrees. Either this field or the following 3 fields: “Quadrant Direction From”, “Quadrant Angle” and “Quadrant Direction To”, which as a group fulfill the same objective, must be populated.Quadrant Direction From: (Optional) This field and the next 2 fields are used to express horizontal angles in the format “43o East of North” or “72o West of South”. This shall contain “North” or the “South” terms, representing the compass direction from which the “Quadrant Angle” is read.Quadrant Angle: (Optional) The actual angle measured from the compass direction specified in the “Quadrant Direction From” field. This can be 0 to 90.Quadrant Direction To: (Optional) This represents the direction in which the angle is measured from the compass direction specified in Quadrant Direction From. It can contain the terms “East” or “West”Vertical Angle: Vertical angle measurement for a traverse point. It can range from -90 to 90 degrees. 90 being vertical downwards, 0 being horizontal and -90 being vertical upwards.Measurement Date: This shall be automatically populated by the system.Other Fields: Other fields may be found to be required during the design phase of RockIT.Descriptive and header information specific to each type of station will be different, thus it shall be maintained in separate tables, all of which shall include a Station ID foreign key to maintain the relational association with the Stations table. These are further described under “Station Type Tables” below.The precise spatial location of a geologic feature associated with a linear station, as stated previously, is a result of the station’s trajectory through space and the feature’s longitudinal position along this trajectory. While the trajectory information is maintained with the stations (and associated surveys), the longitudinal position data is kept with the geologic features. This separation of information essentially means that it is up to the geologic features to know how far down the drill hole they are located, while drill holes do not even need to be aware that any features are associated with them. This approach reflects the way this kind of data is handled in the real world and has the advantage of allowing changes to happen, either on the stations or on the features independently, without requiring modifications in the respective counterpart dataset.Where a geologic feature occupies a volume in space traversed by a drill hole or measured section (in most but not all cases a linear station), its spatial location is represented by a pair of longitudinal values (an interval) defining where the traverse enters and exits the geologic feature. This interval can also be described a “thickness” representing the difference between the longitudinal values (more detail below). Geologic features in this situation shall henceforth be referred to as “traversed geologic features” to distinguish them from “point like” geologic features.There shall be many different types of geologic features / events / samples. Features, for example could include bedrock and surficial geology; events would be things such as landslides; samples could include water, gas, etc. As mentioned previously, each type of feature will be implemented with its own table, in order to accommodate the different types of data inherent to different types of features.All geologic feature / event / sample tables shall share a set of columns dedicated to managing spatial reference. Point like and traversed geologic features shall coexist in the same tables (see figure 2-5). This standard set of columns shall include the following:Feature ID: Primary key of the feature table. This is automatically generated.Station ID: Foreign key associating this geologic feature / event / sample to a particular station.Longitudinal position fields: Note: If a geologic feature is “point like” rather than traversed and is located on a “point like” station, then none of these fields need to be populated. The location of the geologic feature shall be assumed to be that of the station with which it is associated.Length 1 Not Reduced: (Optional) This represents the distance from a geologic feature to the origin (drill hole collar for example) of its associated station. In the case of a traversed geologic feature this would be the distance of the station origin to the point of entry into the feature. This value is “Not Reduced” meaning that it represents a direct measurement and that no mathematical reduction has been applied to transform it to a distance perpendicular to bedding or other type of morphologic layering.If this field is populated the system will prevent the user from populating “Length 1 Reduced”, “Length 2 Reduced”, “Thickness Not Reduced” and “Thickness Reduced”. This behavior is designed to prevent confusion by allowing only one method of longitudinal position to be entered.Length 2 Not Reduced: (Optional) This field is only necessary for traversed geologic features and represents the “Not Reduced” distance between a station’s origin and the point of exit from this feature.Length 1 Reduced: (Optional) This represents the distance from a geologic feature to the origin (drill hole collar for example) of its associated station. In the case of a traversed geologic feature this would be the distance of the station origin to the point of entry into the feature. This value is “Reduced” meaning that it is the result of a mathematical transformation so that it represents distance perpendicular to bedding or other type of morphologic layering.If this field is populated the system will prevent the user from populating “Length 1 Not Reduced”, “Length 2 Not Reduced”, “Thickness Not Reduced” and “Thickness Reduced”. This behavior is designed to prevent confusion by allowing only one method of longitudinal position to be entered.Length 2 Reduced: (Optional) This field is only necessary for traversed geologic features and represents the “Reduced” distance between a station’s origin and the point of exit from this feature.Thickness Not Reduced: This field accommodates cases where traversed geologic features are represented by thickness values instead of pairs of lengths. Mathematically this is not very different from lengths since a thickness can easily be derived by subtracting the distance to the point of entry from the distance to the point of exit.This value is “Not Reduced” meaning it has not been transformed to represent thickness of a geologic feature perpendicular to bedding or other type of layering. For a layered rock, this means that this value must be considered to be “apparent” rather than “true” thickness.If this field is populated the system will prevent the user from populating “Length 1 Not Reduced”, “Length 2 Not Reduced”, “Length 1 Reduced”, “Length 2 Reduced”, and “Thickness Reduced”. This behavior is designed to prevent confusion by allowing only one method of longitudinal position to be entered.Thickness Reduced: This field accommodates cases where traversed geologic features are represented by “Reduced” thicknesses, meaning that for layered geologic features the value has been transformed to represent thickness perpendicular to layering, i.e. true thickness.If this field is populated the system will prevent the user from populating “Length 1 Not Reduced”, “Length 2 Not Reduced”, “Length 1 Reduced”, “Length 2 Reduced”, and “Thickness Not Reduced”. This behavior is designed to prevent confusion by allowing only one method of longitudinal position to be entered.Length Units: Units in which lengths or thicknesses are expressed (feet, meters, etc). This field shall be controlled by a lookup table.Coordinate Data: Coordinate fields have been included to simplify the use of this data directly within GIS or 3D modeling systems. A module shall populate these fields automatically by using available points of origin, surveys and longitudinal positions.It must be noted that these fields are not critical. XYZ coordinates could instead be produced for data exports by a module operating in a manner similar to module proposed above.If these fields are implemented they will consist of two “XYZ triplets” (two sets of 3 fields: X1, Y1, Z1 and X2, Y2, Z2). For traversed geologic features the first set represents the location where the station traverse enters the feature and the second set represents the location where the traverse exits. For point like geologic features only the first set, representing the location of features, is used.Coordinate System & Precision: Horizontal values shall be stored as lat long in decimal degrees using double precision floating point numbers. If Lat-Long values are entered as D-M-S, then they shall be automatically converted to decimal degrees before being stored. The interface shall limit the number of decimals that can be entered manually depending on the situation and the type of feature involved. Vertical units are assumed to be the same as those given for the station with which a feature is associated.Feature CreatorDate Feature CreatedLast Editor IDDate Last EditedAn advantage of this model in general is that multiple geologic features / samples (even of different types) can be tied to different locations along the same linear station, even overlapping each other without causing any kind of interference. The overlap can in fact be used as a means of spatially cross-referencing different types of geologic data in 3D. As mentioned previously, changing either the starting point or the survey information for a linear station would result in an automated re-mapping of every geologic feature / event / sample associated with that station resulting in significant savings in terms of the labor needed to maintain the data.Typical cases of point and traversed features associated with point or linear stations are shown in the table below:Table 21 Point & Linear Stations against Point and Traversed featuresPoint StationsLinear StationsPointGeologic Features / Events / SamplesBedrock OutcropsSurficial Geol. OutcropsSinkholesLandslidesOther Surface SamplesBedrock along Drill-holeSurficial Geol along Drill-holeBedrock along Surveyed Measured SectionSurficial Geol. along Surveyed Measured SectionOther Samples along Drill-Hole or Surveyed Measured SectionLinearGeologic Features / Events / SamplesBedrock along Non-Surveyed Drill-holeSurficial Geol. along Non-Surveyed Drill-holeOther Samples along Non-Surveyed Drill-holeBedrock along Non-Surveyed Measured SectionSurficial Geol. along Non-Surveyed Measured SectionOther Samples along Non-Surveyed Measured Section Bedrock along Drill-holeSurficial Geol along Drill-holeCoal Samples along Drill HoleBedrock along Surveyed Measured SectionSurficial Geol. along Surveyed Measured SectionOther Samples along Drill-Hole or Surveyed Measured SectionLandslides along survey linesIn addition to common spatial reference fields all geologic feature, event and sample tables shall share a set of fields dedicated to managing ownership and tracking editing activities. These include the following:Project: This is a foreign key referencing a unique project name, thus tying a feature with its corresponding project. A geologic feature is owned by the owner of the project.Creator ID: This field captures the user id of the user that created this feature. This is populated automatically by the system based on the login of the user that performed the operation. This field shall be exposed but not editable through the user interface.Creation Date: This field captures the date on which this feature was created. This field shall be exposed but not editable through the user interface.Last Editor ID: This field captures the user id of the user that last edited this feature. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Last Edit Date: This field captures the date on which this feature was last edited. This field is neither exposed nor editable through the user interface.Intrinsic Angular Properties of Geologic FeaturesSpatial reference, when applied to geologic features, involves more than the definition of locations in space, it also involves information about the orientation in space (angular information) of planar or linear elements intrinsic to these features. Examples of this type of information include the strike and dip of bedding planes, azimuth and plunge of imbricated clasts, the orientation of deformational fabrics such as foliations and lineations, etc. Note that the linear elements referenced here are not the same as the linear geologic features described above. Linear elements describe the internal characteristics of a geologic feature while the linear geologic feature concept describes the distribution of a geologic feature with respect to external space (and adjacent features).Within our proposed data model the spatial location of geologic features is handled by their interplay with the Stations datasets as described under 2.5.1 General Data Model Construct. The angular measurements for planar and linear elements of geologic features shall be stored in two separate tables, one for planar elements, such as bedding or foliation, and another for linear items such as lineation and imbrication. Each of these tables shall contain a relational (foreign) key corresponding to the primary key of any of the various types of features that can be described by such measurements. Placing the feature foreign keys in the planar and linear element tables makes it possible to associate many planar or linear measurements with any particular feature. In general the “features” will consist of planar and linear fabric descriptions which shall exist in their own specialized tables, which in turn shall include foreign keys corresponding to the tables of the lithologic features which they describe. Linear and planar elements may also be used to describe entities not associated with specific lithologies, for example faults, fold axes and even the orientation of cross-section diagrams. The foreign key in the plane and linear element tables shall be augmented by the inclusion of a field referencing the name of the table in which the parent geologic feature is stored.An important point to make is that it is these planar measurements that shall be used to mathematically convert (reduce) apparent stratigraphic thicknesses for lithologies associated to drill holes or measured sections into true stratigraphic thicknesses. Conversely if planar measurements are not available it shall not be possible to carry out such a conversion. In those cases a lithology shall either be assumed to either be massive or to be missing planar fabric orientation information.Additionally it shall be possible to associate planar and linear elements to each other. This capability shall serve to describe situations where related linear and planar fabrics exist within a lithology. This shall be accomplished by a many to many relationship between linear and planar elements. This many to many relationship means that multiple planar fabrics can be associated with one linear fabric (case of a linear feature defined by the intersection of two planar features) or that one planar fabric could be associated with multiple linear fabrics (two preferred crystal growth orientations defining a plane). This many to many relationship shall be implemented by means of a simple join table.Both the planar and the linear table shall include a field to store an integer value indicative of relative age. The purpose of this is to define the relative creation sequence for geologic features having more than one linear or planar element. Population of this field shall be optional.The ability to share one planar or linear element among many geologic features is not supported under this data model due to its potential for creating confusion as well as the added overhead that would be required to edit data.Planar Elements TableThis table exists in the original Outcrops application. Within RockIT’s data model this table shall undergo some minor modifications but will essentially fulfill the same function, which is to capture the angular orientation in space of planar features (such as faults) or planar fabrics (such as foliation or bedding) assigned to geologic features.Notes: All data types/fields listed below are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise.Planar Element ID: Primary key for this tablePlanar Feature Type: Type of geologic feature referenced by this planar element. Could include values such as “Bedding”, “Fault”, “Contact”, etc. Shall be controlled by a lookup table.Geologic Feature ID: Foreign key associating this element with a particular geologic feature, event or sample.Table Name: The name of the table to which corresponds the foreign key stored in “Geologic Feature ID”.Relative Age: (Optional) Sequence starting at 1 for oldest element and increasing for younger elementsStrike or Dip Vector: (Optional) Field indicates whether the following horizontal angles refer to a strike orientation or a dip vector orientation. Valid Values shall be S for strike and D for dip vector, these may be controlled by a lookup tableHorizontal Azimuth Angle: (Optional) Horizontal angle measurement clockwise from North, from 0 to 360 degrees. Either this field or the following 3 fields: “Quadrant Direction From”, “Quadrant Angle” and “Quadrant Direction To”, which as a group fulfill the same objective, must be populated.Quadrant Direction From: (Optional) This field and the next 2 fields are used to express horizontal angles in the format “43o East of North” or “72o West of South”. This shall contain “North” or the “South” terms, representing the compass direction from which the “Quadrant Angle” is read.Quadrant Angle: (Optional) The actual angle measured from the compass direction specified in the “Quadrant Direction From” field. This can be 0 to 90.Quadrant Direction To: (Optional) This represents the direction in which the angle is measured from the compass direction specified in Quadrant Direction From. It can contain the terms “East” or “West”Dip Angle: (Optional) Vertical angle measurement from 0 to 90 degreesMeasurement Date: This shall be automatically populated by the system, but be manually changeable. This will allow users to input the correct dates if entering the data after a prolonged stay in the field.Description: (Optional) Freeform entry of any additional comments regarding this planar element.Linear Elements TableThis table exists in the original Outcrops application. Within RockIT’s data model this table shall undergo some minor modifications but will essentially fulfill the same function, which is to capture the angular orientation in space of linear fabrics (such as pencil cleavage) assigned to geologic features.Notes: All data types/fields listed below are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise.Linear Element ID: Primary key for this table.Linear Feature Type: Type of geologic feature referenced by this linear element. Could include values such as “Lineation”, “Imbrication”, “Fold Axis”, etc. Shall be controlled by a lookup table.Geologic Feature ID: Foreign key associating this element with a particular geologic feature, event or sample.Table Name: The name of the table to which corresponds the foreign key stored in “Geologic Feature ID”.Relative Age: (Optional) Sequence starting at 1 for oldest element and increasing for younger elements.Horizontal Azimuth Angle: Horizontal angle measurement clockwise from North, from 0 to 360 degrees. Either this field or the following 3 fields: “Quadrant Direction From”, “Quadrant Angle” and “Quadrant Direction To”, which as a group fulfill the same objective, must be populated.Quadrant Direction From: This field and the next 2 fields are used to express horizontal angles in the format “43o East of North” or “72o West of South”. This shall contain “North” or the “South” terms, representing the compass direction from which the “Quadrant Angle” is read.Quadrant Angle: The actual angle measured from the compass direction specified in the “Quadrant Direction From” field. This can be 0 to 90.Quadrant Direction To: This represents the direction in which the angle is measured from the compass direction specified in Quadrant Direction From. It can contain the terms “East” or “West”Dip Angle: Vertical angle measurement from 0 to 90 degreesMeasurement Date: This shall be automatically populated by the system, but be manually changeable. This will allow users to input the correct dates if entering the data after a prolonged stay in the field.Description: (Optional) Freeform entry of any additional comments regarding this linear element.Planar to Linear Elements Join TableThis table defines associate pairs of planar and linear elements. A particular planar or linear element may participate in more than one pair, hence the support for a many to many relationship. This table only requires two fields:Planar Foreign Key: Corresponds to a planar fabric or element associated with a linear fabric or element.Linear Foreign Key: Corresponds to a linear fabric or element associated with a planar fabric or element.Typical Linear Station ScenariosThe data model described in the previous sections has been structured to be flexible enough to handle a wide variety of real world situations. While many of the ways in which this system should function can be inferred from the previously described requirements, we consider it necessary to provide explicit examples to insure that the design and development phases of RockIT proceed based on a clear understanding of the intent of this data model with regards to linear stations.The following table and descriptions outline a set of assumptions and procedures for various envisioned scenarios:Table 22 Data Input Required for Typical Linear Station ScenariosStationsGeologic Features / Events / SamplesPlanar ElementLinear Station TypeSurvey DataLengths Not ReducedLengths ReducedThickness Not ReducedThickness Reduced?Planar Element OrientationNon Surveyed Measured SectionAssumed Perpendicular to LayeringN/AN/AN/AValues?OPTIONALAssumed Perpendicular to LayeringN/AValuesN/ACalculated directly from Reduced Lengths?OPTIONALSurveyed Measured SectionREQUIREDN/AN/AValuesCalculated form Planar Element Orientation & Thickness Not Reduced. If no Planar Element, assumed equal Thickness Not Reduced? OPTIONALREQUIREDValuesN/AN/ACalculated form Planar Element Orientation & Lengths Not Reduced. If no Planar Element, calculated directly from Lengths Not Reduced?OPTIONALNon Surveyed Drill HoleIf No Value Assumed VerticalValuesN/AN/AThis value is calculated as before. Left blank if Planar Element Orientation is unknown?OPTIONALIf No Value Assumed VerticalN/AN/AValuesThis value is calculated as before. Left blank if Planar Element Orientation is unknown?OPTIONALSurveyed Drill HoleREQUIREDValuesN/AN/AThis value is calculated as before. Left blank if Planar Element Orientation is unknown?OPTIONALREQUIREDN/AN/AValuesThis value is calculated as before. Left blank if Planar Element Orientation is unknown?OPTIONALMeasured section with no survey information whatsoever:Using Thickness Values:Thickness values are assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “true” thickness), thus are populated in the “Thickness Reduced” fieldFor lithologic units described along measured sections a planar element orientation should be given. If it is omitted the unit is assumed to have no planar fabric or structure.In general, for measured sections the sequence of units should be maintained. In this case, because only thicknesses are used, maintaining the sequence of units is required. The system cannot enforce the correct sequence because it has no prior knowledge of the geology, however it shall notify the user that in this case the sequence is critical.XYZ coordinates are not calculated for features associated with non-surveyed measured sections.Total thickness is defined by the sum of all thickness values.Using Length ValuesLength values are assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “true” thickness), thus a re populated in the “Length Reduced” fields.For lithologic units described along measured sections a planar element orientation should be given. If it is omitted the unit is assumed to have no planar fabric or structure.In general, for measured sections the sequence of units should be maintained. In this case because length values are entered, maintaining the sequence of units is not critical.XYZ coordinates are not calculated for features associated with non-surveyed measured sections.Total thickness is defined by the maximum length reported for a geologic feature.Measured section with survey information.Every survey record must have depth as well as angular information otherwise it cannot be counted as a survey record (note that where the vertical angle is 90o the azimuth is undefined). If only depth information is present: Treat as if no survey information whatsoever is present.Measured section is assumed to describe a polygonal path with straight segments between survey points where every azimuth and vertical angle pair correspond to a straight segment starting at the distance value of the current survey point and ending at the distance value of the next survey point. The last survey point does not require angular information.Using Thickness Values:Thickness values are not assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “apparent” thickness), thus they are populated in the “Thickness Not Reduced” field. Thickness could be assumed to be true if it is known that the measured section survey line is precisely perpendicular to lithologic layering, or if a “No Visible Layers” planar element is chosen.For lithologic units described along measured sections a planar element orientation should be given. If it is omitted the unit is assumed to have no planar fabric or structure.In general, for measured sections the sequence of units should be maintained. In this case, because only thicknesses are used, maintaining the sequence of units is required. The system cannot enforce the correct sequence because it has no prior knowledge of the geology, however it shall notify the user that in this case the sequence is critical.XYZ coordinates are calculated for all features associated with a surveyed measured section.True (reduced) thickness could be calculated from survey information, apparent thickness and the orientation of a planar element describing lithologic layering. If the unit is assumed to have no planar fabric or structure, the true thickness is assumed to be equal to the non-reduced thickness.Using Length ValuesLength values are not assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “apparent” thickness), unless it is known that the measured section survey line is precisely perpendicular to lithologic layering, or if a “No Visible Layers” planar element is chosen. In any case length values should be populated in the “Length Not Reduced” fields.For lithologic units described along measured sections a planar element orientation should be given. If it is omitted the unit is assumed to have no planar fabric or structure.In general, for measured sections the sequence of units should be maintained. In this case because length values are entered, maintaining the sequence of units is not critical.XYZ coordinates are calculated for all features associated with a surveyed measured section.True thickness could be calculated from survey information, apparent thickness and the orientation of a planar element describing lithologic layering. If the unit is assumed to have no planar fabric or structure, the true thickness can be calculated directly from the non-reduced lengths.Drill-hole with no survey information whatsoever:Using Thickness Values:Drill hole is assumed to be vertical and straightThickness values are not assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “apparent” thickness), thus they are populated in the “Thickness Not Reduced” field. Thickness could be assumed to be true if it is known that the measured section survey line is precisely perpendicular to lithologic layering, or if a “No Visible Layers” planar element is chosen.No assumption is made regarding layering orientation of geologic features. Planar Element Orientation can either be recorded, assumed to be horizontal or left blank and assumed to be unknown.True (reduced) thickness is calculated unless the Planar Element Orientation is left blank.XYZ coordinates of every feature are calculated but horizontal position is the same as that of the starting point.Total thickness / depth is defined by the sum of all thickness values.Using Length Values:Drill hole is assumed to be vertical and straightLength values are not assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “apparent” thickness), unless it is known that the measured section survey line is precisely perpendicular to lithologic layering, or if a “No Visible Layers” planar element is chosen. In any case length values should be populated in the “Length Not Reduced” fields.No assumption is made regarding layering orientation of geologic features. Planar Element Orientation can either be recorded, assumed to be horizontal or left blank and assumed to be unknown.True (reduced) thickness is calculated unless the Planar Element Orientation is left blank.XYZ coordinates of every feature are calculated but horizontal position is the same as that of the starting point.Total depth is assumed to be defined by the maximum “Length” value of any feature.Drill-hole with survey informationEvery survey record must have depth as well as angular information otherwise it cannot be counted as a survey record (note that where the vertical angle is 90o the azimuth is undefined).If only one angle is present drill hole is assumed to be straight and trending as described by the angle. For multiple angles and lengths drill hole is assumed to describe a gradually changing curved path (segmented for speed of processing). This means that the back calculation required to define the drill hole’s path through 3D space must gradually change its orientation before getting to a surveyed point, such that when it gets there it is already pointing in the right direction.Using Thickness Values:Thickness values are not assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “apparent” thickness), thus they are populated in the “Thickness Not Reduced” field. Thickness could be assumed to be true if it is known that the measured section survey line is precisely perpendicular to lithologic layering, or if a “No Visible Layers” planar element is chosen.No assumption is made regarding layering orientation of geologic features. Planar Element Orientation can either be recorded, assumed to be horizontal or left blank and assumed to be unknown.True (reduced) thickness is calculated unless the Planar Element Orientation is left blank.XYZ coordinates of every feature are calculated.Total apparent thickness / depth is defined by the last survey point.Using Length Values:Length values are not assumed to represent measurements perpendicular to the orientation of lithologic layering (representative of “apparent” thickness), unless it is known that the measured section survey line is precisely perpendicular to lithologic layering, or if a “No Visible Layers” planar element is chosen. In any case length values should be populated in the “Length Not Reduced” fields.No assumption is made regarding layering orientation of geologic features. Planar Element Orientation can either be recorded, assumed to be horizontal or left blank and assumed to be unknown.True (reduced) thickness is calculated unless the Planar Element Orientation is left blank.XYZ coordinates of every feature are calculated.Total apparent thickness / depth is defined by the last survey point.The diagrams in the following pages illustrate how the main components of the proposed model in fit together and are capable of describing a stations and geologic features in space.Figure 23 Conceptual Model for Point & Linear Stations (Outcrops and Drill Hole Data)Figure 24 Conceptual Model for Linear Stations (Measured Section)Figure 25 Table Structure for Geologic Features, Events & Samples,Coordinates are Calculated On The FlyStation Type TablesTo address the varying content that is represented by a station location, users have a choice of stations available by means of tables which are related to the Stations table via a Station ID foreign key. Typical station type tables foreseen for the initial implementation of RockIT include the following:Measured SectionDrill HoleCoal Bed Methane wellOutcropNew types of station could be added at any time without affecting the overall functionality of the system. In addition to the detailed location information described in the preceding section, stations also require the following details, depending on the type of station selected.Measured Section StationsSection ID – system-generated and not editableStation ID: This is a foreign key that shall reference the station (in the stations tables) that associates records to coordinate space. There is a one-to-one relationship between outcrops/measured section stations and the outcrops/measured section table.For “Measured Sections” the Survey Table is also populated to capture the appropriate location description information as required by the survey information associated with measured sectionStation ID is foreign key in Survey Table as well as this outcrops/measured sections tableProject (or Project ID): This is a foreign key that shall reference the user defined project within which this record shall be included. If none is explicitly specified by the user this is set to the “general” project.Creator ID: This field captures the user id of the user that created this surficial geology feature. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Creation Date: This field captures the date on which the record was created. This field is neither exposed nor editable through the user interface.Last Editor ID: This field captures the user id of the user that last edited the record. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Last Edit Date: This field captures the date on which the record was last edited. This field is neither exposed nor editable through the user interface.Exposure type – pick list (e.g. float, quarry road cut)Cross-reference number – comments referencing field notebook or other document or manualAssociated documentsPhotographsSketchesOther fields may be defined during the system design phase.Drill Hole StationsRecord ID – system-generated and not editable by userStation ID: This is a foreign key that shall reference the station (in the stations tables) that associates records to coordinate space. There is a one-to-one relationship between drill hole stations and the drill holes table.For “Survey Drill Holes” the Survey Table is also populated to capture the appropriate location description information as required by the survey information associated with Survey Drill HoleStation ID is foreign key in Survey Table as well as this Drill Hole Stations tableProject (or Project ID): This is a foreign key that shall reference the user defined project within which this record shall be included. If none is explicitly specified by the user this is set to the “general” project.Creator ID: This field captures the user id of the user that created this surficial geology feature. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Creation Date: This field captures the date on which the record was created. This field is neither exposed nor editable through the user interface.Last Editor ID: This field captures the user id of the user that last edited the record. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Last Edit Date: This field captures the date on which the record was last edited. This field is neither exposed nor editable through the user interface.Driller Company Name – pick list from Companies lookup tableDrill Hole Number – as provided by drilling companyDriller Comments – as provided by drilling companyDriller Name – as provided by drilling companyCore Inspector Name – as provided by drilling companyDate drilled – as provided by drilling companyCross-reference number – comments referencing field notebook or other document or manualTotal depth measurement/units – Describes the total depth of the drill holeProperty Owner’s Name – as provided by drilling companyCoal Company Name (pick list from Companies lookup table)Data Confidential – Yes/NoMining permit numberAssociated documentsPhotographsSketchesOther fields may be defined during the system design phase.CBM Well StationsThis table is to be used when station itself is a CBM well. If station is a drill hole where CBM sampling was performed, then create a new CBM Well feature and associate the drill hole station to it that represents where sampling was performed. Add new samples through the CBM well feature interface, as related records to the CBM Well Details table.CBM ID - system-generated and not editable by userStation ID: This is a foreign key that shall reference the station (in the stations tables) that associates records to coordinate space. There is a one-to-one relationship between CBM well stations and the CBM Wells table.Project (or Project ID): This is a foreign key that shall reference the user defined project within which this record shall be included. If none is explicitly specified by the user this is set to the “general” project.Creator ID: This field captures the user id of the user that created this surficial geology feature. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Creation Date: This field captures the date on which the record was created. This field is neither exposed nor editable through the user interface.Last Editor ID: This field captures the user id of the user that last edited the record. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Last Edit Date: This field captures the date on which the record was last edited. This field is neither exposed nor editable through the user interface.Permit Number: Unique ID that is to be same as WIS Permit numberPermit status: pick list (see CBM Wells Permit Status lookup table for values)Well Location: Each well shall have field name, pool name, operator name, and farm name. Follow formatting requirements of similar fields in the WIS.Attribution as reported via WIS:Well TypeGeneral Well ClassSpecial Well ClassExploratory TypeDepth of Gas Shows in Other Reservoir (FT)Unit Water FoundDepth Water Found (FT)Comments about waterAnnual Gas Production (MCFD)Annual Water Production (Barrels)Total Depth (FT)Total Depth Unit /FM/GPSurface landowner and water purveyor with water supply within 1000 feetOwner, lessee, or operator of coal seamsName of mineScan of State well recordsType of records in WIS (see WIS Record Type in look up tables)Number of logsShort names and type of geophysical well logsDescriptionsCoal termsRock unitsOther fields may be defined during the system design phase.Outcrop StationsSection ID – system-generated and not editableStation ID: This is a foreign key that shall reference the station (in the stations tables) that associates records to coordinate space. There is a one-to-one relationship between outcrops/measured section stations and the outcrops/measured section table.For “Measured Sections” the Survey Table is also populated to capture the appropriate location description information as required by the survey information associated with measured sectionStation ID is foreign key in Survey Table as well as this outcrops/measured sections tableProject (or Project ID): This is a foreign key that shall reference the user defined project within which this record shall be included. If none is explicitly specified by the user this is set to the “general” project.Creator ID: This field captures the user id of the user that created this surficial geology feature. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Creation Date: This field captures the date on which the record was created. This field is neither exposed nor editable through the user interface.Last Editor ID: This field captures the user id of the user that last edited the record. This is populated automatically by the system based on the login of the user that performed the operation. This field is neither exposed nor editable through the user interface.Last Edit Date: This field captures the date on which the record was last edited. This field is neither exposed nor editable through the user interface.Exposure type – pick list (e.g. float, quarry road cut)Cross-reference number – comments referencing field notebook or other document or manualAssociated documentsPhotographsSketchesOther fields may be defined during the system design phase.General FunctionalityWhile the Bureau conceives of RockIT as primarily a database, it is going to be sustained by the applications that permit users to interact with and maintain the data. In that regard, the following suite of functionality will be required for the sustainable implementation of RockIT:Data Searching and ViewingData Entry and EditingData ImportData Reports and PrintingData ExportData VisualizationSystem AdministrationThe diagram below provides an illustration of how these individual applications will interact with the database and with each other:Figure 31 General Functionality LayoutData ImportData Entry / EditingData Viewing / SearchingData ExportDatabaseData ReportsSystem AdministrationData Visualization in 3rd Party GIS or 3D Modeling SoftwareThe initial entry point through which users will access the RockIT system shall be comprised of a “home page” modeled around a “Dashboard” concept which shall organize and bundle the aforementioned functionality into discrete procedural blocks supporting coherent end-to-end workflows.Referencing functionality by whether or not it is available on the dashboard, search results, and/or other dimensions of the software captures the anatomy of the user requirements articulated during interviews and through reviewing existing documentation and applications. The following list includes the main functionality bundles that we propose for the Dashboard:Create or Go to ProjectPick list will show all projects (with the user’s shown first) and include “NEW” as option for creating a new projectCreate or Go to StationPick list will provide all stations and include “NEW” as option for creating a new stationCreate New Geologic Feature / Event / SampleFor example, add a sinkhole, landslide, or other event to the database, independent of a projectSearch / Export / ReportsImport DataThe diagram below shows the interaction, in terms of functions, between the home page and the other principal interfaces / pages that shall make up RockIT. Note that the home page is not the only location where users will be able to launch the tools listed there, nor does the home page host an exhaustive list of all functionality required. Some additional tools users will be accessing through different interfaces / pages include:ReportPrintExportSave SearchFigure 32 Schematic Structure of User InterfaceData Searching and ViewingSearching, or navigating through the data maintained by RockIT represents one of its most important functionalities. As can be seen from the previous diagram it is envisioned to be the pivot point around which much of the functionality of this system revolves.The requirements for data searching have been broken down in terms of the following concepts / questions:What can be searched for and how is a search constructed?What do the returned search results look like?What can be done with the returned search results?Constructing a SearchA searching “scheme” has been put together to leverage the proposed data model for RockIT to the best possible advantage. This scheme shall be implemented by a unified “search user interface”, which will be available to the user from the dashboard as well as most other locations in the application and allow the user to navigate to a different set of data at any given point in time.This searching interface could be implemented either in a pop-up window or a collapsible side bar (triggered by clicking or hovering over a button) depending on what the Bureau selects during system design phase.According to this it shall be possible to search for data based on the following general criteria:LocationAssociation with StationsMembership in ProjectsGeologic Content: Types of features, as well as subject matter details and analysis resultsThe results obtained from a search shall be listed in terms of geologic features, stations and projects.A note about subject matter details and analysis results: Although it shall be possible to search based on subject matter details and analysis results, the returned list shall only go down to the level of geologic feature / event / sample. This is proposed in order to keep the results uncluttered and easy to navigate. Thus if a feature satisfies multiple specific search criteria based on data existing in multiple tables nested at different depths within the database, it would still only have to be listed once. The interface would provide the user with an easy path to inspect deeper levels of information associated with any returned geologic feature.As implied previously, the search interface shall provide the user with the ability to assemble a compound search by daisy-chaining multiple filters (specific, implemented search criteria will henceforth be called filters to differentiate them from general search criteria, thus for example multiple filters can be implemented based on project membership search criteria).It shall be possible to assemble compound searches from multiple filters such that returned results consist of features which satisfy all filters simultaneously (logical “and”), or of features that satisfy any filter individually (logical “or”). To illustrate the difference consider a search for rock types based on whether they are igneous or whether they contain Quartz. If the possible answers are Granite, Peridotite or Quartzite then a search requiring that both filters are satisfied simultaneously would return Granite, which is igneous and contains Quartz. A search requiring that any one filter be satisfied individually would return all three rock types. Granite and Peridotite satisfy the igneous filter, while Granite and Quartzite satisfy the “contains Quartz” filter.The search interface will expose to the user the available search criteria arranged in a hierarchical tree-like structure roughly reflecting the underlying database schema. The main data categories (branches) of this structure shall be visible but all of the fields and subcategories of each branch will be hidden.Clicking on any branch shall unfold (make visible) one level of fields and subcategories available directly under that branch. Clicking on any subcategory will unfold an additional level of fields and subcategories, and so on.Fields that are controlled by lookup or reference tables will be represented as dropdown widgets, showing all of the possible values. Fields not controlled in this way will be represented by text input widgets. In addition to exposing fields this interface shall expose a set comparison operators: =, <, >, <=, >= and != implemented through a radio button widget allowing the choice of any one. Filters are defined by making use of the exposed fields and the comparison operators, for example: Picking the >= operator and then clicking on any value from an exposed dropdown list is equivalent to saying “select all features where the field represented by the dropdown list is greater than or equal to the chosen value”. Conversely, picking the != operator and then filling out a test input field is equivalent to saying “select all features where this particular field is not equal to the value typed in the corresponding text input widget”Below the tree-like “filter builder” there shall be a scratch pad (canvass) on which will be written in sequence and in plain English all of the filters defined for this search. To cause a sequence of filters to be evaluated simultaneously the system will allow the user to insert the phrase “and also satisfy” (logical “and”) between filters shown on the scratch pad. To cause a sequence of filters to be evaluated individually the system will allow the user to insert the phrase “plus the features that satisfy” (logical “or”).It shall be possible to move filters up or down along the sequence or to delete them, as well as to add parentheses to obtain a more sophisticated set of logical “and” / “or” conditions.The user will not be forced to submit a search until she/he is satisfied that the filter sequence is constructed correctly.It shall be possible to conduct a search against all of the features stored in the database or against a currently selected set of features.It shall be possible for users to save the filter sequence shown in the scratch pad for use at a later date.The following list outlines the most important system wide search criteria (the main branches of the searching interface:Searching by Location:As location is stored within the database structure for stations, searches for features by location will, transparently to the user, involve the relationship between geologic features and stations. The user shall have the option of omitting this search criterion altogether. Only one location based filter is allowed per search as it is judged that multiple simultaneous location filters will be of limited benefit to the user while adding a significant potential for confusion.To create a location based filter the user shall choose the “Search by Location” heading (branch) located at the top level of the searching interface. Having done this, clickable headings for the following workflows will be exposed:Municipalities: The user shall have the option to choose features in terms of the municipalities on which they fall. There shall be two methods for the user to choose in order to do this:The user shall be presented with a special dropdown list of counties. Each county shall have a checkbox and a button next to it.Multiple boxes can be checked which would include all of the features within those counties regardless of municipality.Clicking on a button would open a second drop down box listing all of the municipalities for the corresponding county. Each entry would have a checkbox next to it, thus allowing the choice of features for multiple specific municipalities.Once all of the municipalities of interest have been checked the user shall click a submit button that will create the filter and make an entry for it in the scratch pad. Once this is done no further location based filters are allowed unless this one is removed.The user shall be presented with a state map showing all of the counties. Hovering the cursor over a county brings up a tool tip with the name of the county.Clicking on a county selects features for entire county regardless of municipality. Multiple counties can be clicked.Right clicking on a county zooms the map to the county and shows a map of the municipalities within that county.Hovering the cursor over a municipality brings up a tool tip with its name.Clicking on a municipality selects all features within it, multiple municipalities can be clicked.Once all of the municipalities of interest have been clicked / selected the user shall click a submit button that will create the filter and make an entry for it in the scratch pad. Once this is done no further location based filters are allowed unless this one is removed.USGS Quadrangle: The user shall have the option to choose features in term of the USGS quadrangle on which they fall. There shall be two methods to do this:The user shall be presented with a special dropdown list of USGS quadrangles. Each quadrangle shall have a checkbox next to it.Multiple boxes can be checked to select all features within more than one quad.Once all of the quadrangles of interest have been checked the user shall click a submit button that will create the filter and make an entry for it in the scratch pad. Once this is done no further location based filters are allowed unless this one is removed.The user shall be presented with a state map showing all of the USGS quadrangles. Hovering the cursor over a quadrangle brings up a tool tip with its name.Clicking on a quadrangle selects all features within it.Once all of the quadrangles of interest have been clicked / selected the user shall click a submit button that will create the filter and make an entry for it in the scratch pad. Once this is done no further location based filters are allowed unless this one is removed.Coordinate Entry: The user shall have the option of selecting features in terms of proximity to a particular point in space. There shall be two methods to do this:The user shall be presented with a form containing input fields to enter X and Y coordinates of the point and the search distance within which to find features.The form will also include two radio button widgets to choose the coordinate system /units and distances units given. The coordinate widget would offer lat long coordinates in decimal degrees or state plane coordinates in feet. The distance widget would offer feet, meters, miles or kilometers.Once the form is filled the user shall click a submit button that will create the filter and make an entry for it in the scratch pad. Once this is done no further location based filters are allowed unless this one is removed.The user shall be presented with a map of the state and given the opportunity to click anywhere on it.The map will include a form with and input field to enter the search distance and a radio button widget to specify the units for the distance, which as before, can be feet, meters, miles or kilometers.Once a point is clicked and the distance and units are filled out, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad. Once this is done no further location based filters are allowed unless this one is removed.Digitizing a Box on a Map: The user shall have the option of selecting all of the features inside a box digitized on a map.A map of the state will be presented to the user where it will be possible to click opposing corners of a box.The user shall have the ability re-digitize the box if not satisfied with its extents.Once the extents of the box are satisfactory, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad. Once this is done no further location based filters are allowed unless this one is removed.Searching by Stations: This search criterion shall allow the user to find geologic features in terms of their association with particular stations. The user shall have the option to use or omit this search criterion altogether.There shall be no limitation regarding the number of filters that can be created with this search criterion.To create this type of filter the user shall choose the “Search by Stations” heading (branch) located at the top level of the searching interface. Having done this, clickable headings for the following workflows will be exposed:Searching for features based on station name: The user shall be presented with a form that will allow the entry of a “search term” representing the name of a station.The user shall be able to use wildcards in the entered “search term” in order to retrieve many stations with similar names.The user shall be able to enter more than one search term delimited by a custom separator character. This will signify the retrieval all of the features associated with each individual station.Once the search term / s have been determined, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad.Searching stations based on station type: The user shall be presented with checkbox list of station types. This allows the user to choose features belonging to various types of station in one operation.Once the types of stations have been determined, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad.Searching by Projects: This search criterion shall allow the user to find geologic features in terms of their association with particular projects. The user shall have the option to use or omit this search criterion altogether.There shall be no limitation regarding the number of filters that can be created with this search criterion. It should be possible for the user to apply these filters in any order and or combination.To create this type of filter the user shall choose the “Search by Projects heading (branch) located at the top level of the searching interface. Having done this, clickable headings for the following workflows will be exposed:Searching projects based on project nameThe user shall be presented with a form that will allow the entry of a “search term” representing the name of a project.The user shall be able to use wildcards in the entered “search term” in order to retrieve many projects with similar names.The user shall be able to enter more than one search term delimited by a custom separator character. This will signify the retrieval all of the features associated with each individual project.Once the search term / s have been determined, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad.Searching projects based on the date on which the project was createdThe user shall be presented with a form containing a starting and ending “date picker” / calendar widget that will allow the selection of features belonging to projects created within a specific date range.To specify projects created on a specific day, both the starting and ending date could be set to the same day.Once the date range has been determined, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad.Searching for projects based on the name of the project creatorThe user shall be presented with a form containing a checkbox list of all the users who have created projects. This allows the user to choose features belonging to projects originated by various creators in one operation.Once the list of creators has been determined, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad.Searching for projects based on the name of the current project ownerThe user shall be presented with a form containing a checkbox list of all the users who currently own projects. This allows the user to choose features belonging to projects currently managed by various owners in one operation.Once the list of current owners has been determined, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad.Searching for projects based on keywords in project descriptionThe user shall be presented with a form allowing the entry of multiple keywords and a radio button widget allowing the option of selecting projects whose descriptions contain all of the keywords or selecting projects whose descriptions contain any of the keywords.Keywords shall be able to include phrases.Once the keyword / s have been determined, the user shall click a submit button that will create the filter and make an entry for it in the scratch pad.Geologic Content: This is the broadest search criterion allowing the user to find geologic features matching any specific geologic information that is associated with them.The user shall have the option to use or omit this search criterion altogether.There shall be no limitation regarding the number of filters that can be created with this search criterion. It should be possible for the user to apply these filters in any order and or combination.To create this type of filter the user shall choose the “Search by Geologic Content” heading (branch) located at the top level of the searching interface. Having done this, clickable headings for all of the different types of geologic features (sub-branches) will appear. Typically these would be Bedrock Features, Surficial Geology Features, Sink Holes, Landslides, etc.Next to each of these headings there will be a control labeled “Select all of these”. Clicking on this control for landslides for example will create a filter (and an entry in the scratch pad) that would return all recorded landslide events.Clicking on one of the actual headings would unfold one additional level of information that can be used to search for features of this type. The first level found directly under a feature type will primarily consist of header columns, that will be represented by text input fields or dropdown lists depending on whether the column is controlled by a reference / lookup table or not, and of additional headings (further sub-branches) representing subject matter details and test results. The subject matter detail and test result headings shown would be specific for the type of feature selected initially, for example a bedrock feature may include thin sections, sedimentary structures or fossils, while a coal sample may include proximal and ultimate analysis, and then again a landslide event may include multiple comment type fields.More information about each individual type of geologic feature and its associated details is given below under “Geologic Features / Events / Samples” and under “Subject Matter Details / Test Results”.Where warranted, a subject matter details and test results heading shall have a “Select all of these” control next to it. This shall allow the creation of a filter that will, for example, select all of the bedrock features with thin sections associated or containing fossils.Clicking on any subject matter details and test results heading shall unfold (make visible) one additional level of fields pertaining to the chosen details or tests. As before these will be represented by dropdown lists and text input fields. There may even exist subject matter details headings that further describe the current details.Dropdown lists and input fields, together with the comparison operators ( =, <, >, <=, >= and != ) can be used as they are found to define filters following the procedure described at the beginning of this section.In order to search for geologic features by geologic age or by officially recognized lithologic unit a special data structure and user interface shall be implemented. These are described in more detail under section 4.2.7 Interpretations / Official Stratigraphic Units.Subject to performance testing the following functionality could also be included:Filters added to a complex query can be evaluated on the fly.Values on drop down lists are highlighted depending on whether data exists for a particular category after being sifted through the current set of filters.It is expected that this will greatly improve the process of discovering data by providing guided searches to database locations that actually contain information.Returned ResultsAs mentioned earlier, once all of the filters for a search have been assembled to the user’s satisfaction, the user shall have the option to click on a submit button located near the scratch pad canvass. This will submit the search to the database.Submitting the search will unfold a results pane / user interface and fold the search interface but without deleting its contents, so that the search can be modified and re-submitted if the results are not satisfactory.The results user interface will present the results of the search in a grid with 3 columns corresponding to features, stations and projects (as shown on upper part of the diagram below). Essentially this shows every feature / event / sample that satisfied the search criteria together with the station and project with which it is associated.To the left of every feature there is a link labeled GO. Clicking on this link will fold the results user interface (again without deleting its contents so that it can be revisited at a later time) and open the viewing / editing interface for the type of feature chosen with the corresponding feature displayed. From here the user can navigate to, and optionally edit, the entire geologic information associate with this feature.To the left of every station and every project there are two links one labeled GO and one labeled with a “+” sign.Clicking on the GO link will fold (without deleting content) and open the viewing / editing interface for the corresponding station or project. This is where all of the header information for either a station or a project can be viewed / modified. In the case of stations this is also where survey information can be updated.Clicking on the “+” for any station or project in the results pane will unfold a set of rows corresponding to all of the features associated with the corresponding station or project that were not returned as part of the original search. Though shown, these features shall not be considered selected, that is they will not be available for export, reporting or other functionality designated for selected features.Figure 33 Returned Results InterfaceEach feature from such a set of rows will include two links, one labeled GO, which shall function exactly as the previously described GO links, and one labeled Add which shall add the corresponding feature to the selected set of features, thus making it available for export, reporting, etc.It shall be possible for users to save a reference to the set of resulting / selected features such that they can be used at a later date.The functionality to export features from RockIT so that they can be used by third party software applications shall be implemented so that it is launched from the search results interface, using the currently selected set of geologic features as the set of data to export.RockIT’s functionality to create data reports shall also be implemented so that it is launched from the search results interface, using the currently selected set of geologic features as the set of data from which to generate a report.Data Entry and Editing:From their experience with the Outcrops module design, the Bureau has identified two key concerns regarding data editing and entry within the RockIT applications. First is to maintain security without compromising collaboration. The Bureau wants to encourage users to share information and work together using the RockIT database and applications. It also wants to ensure that users don’t interfere with each other’s research, especially through unintentional, inadvertent editing. The second concern is to set a default “read only” mode so that editing capabilities are restricted to only being available when the user toggles on an edit session, or engages in an import function. With these concerns in mind, the following questions have framed the data entry and editing system requirements:What user is allowed to edit what project(s)?What type of editing functionality will be required while users are working within the database?Editing PermissionsAs described in the Proposed Data Model and System Administration sections, three system administration tables, USERS, PROJECTS and PROJECT_PERMISSIONS, will work in concert to police what users are allowed to edit what projects. The USERS table controls the authorized logins. This table can only be updated by the System Administrator. In order to edit any content, a user must be granted edit permissions through their user login, a USER table attribute.Once a user has “edit” permissions that user can create a new project. The projects and associated owners are logged in the PROJECTS table. By default, the project owner can edit all content within that project. Additionally, the project owner can authorize other users to be editors for the project. Whoever is added as a project editor will have the same edit permissions as the project owner. The PROJECT_PERMISSIONS table will manage the list of what users can edit what projects. Each new project and its owner are entered in this table automatically when a new project is created.Details regarding specific System Administration tables and editing tools are provided in Section 6. Required Editing FunctionalityAs we address the specific editing and data entry system requirements, keep in mind the discussion at the beginning of the General Functionality section where we describe the “Dashboard” screen. The Dashboard will provide users a succinct set of choices for how to begin working with the data they require:Create or GoTo ProjectCreate or GoTo StationCreate New Event/Feature/SampleSearch / Export / ReportsImport DataEach of these frameworks will need to be designed to accommodate data viewing as well as provide a form for entering new information. The following sections will discuss required functionality for the projects, stations, create new feature, and search results data editing frameworks. The Data Importing functionality is described below in section 3.3ProjectsInvoking the “Create or Goto Project” function of the Dashboard will launch the “Main Project Page” which will expose the following functionality to the user:Create new project. This creates a new empty project.PROJECTS table and associated PROJECT_PERMISSIONS table updated to reference new project and owner, authorized editors listed in PROJECT_PERMISSIONS tableNew Project interface appears with forms for user to update anatomy of the projectProject NameProject ID – system generated and maintainedOwner – system-generated field, not editable by user, automatically populated with user login creating projectAdd / Remove Editors (available to user who is project owner)User provide pick list of all users with edit permissions User can assign one or more users to be editors for the projectProject owners are allowed to remove users from the listOnce editor is removed from list, they are immediately blocked from the project and all associated records.Other supporting fields are updated, as requiredResearch Methodology (may require lookup table, if Enterprise wants to standardize these options)Upload imagesField notes enteredOthersSAVE and requisite system-generated fields updatedDate createdCreatorLast edit dateEditorEdit (Manage) Existing Project. This presents the user with an interface to enter a project name or a project id.Entering the project name shall be assisted by predictive text functionality utilizing the names of all of the projects defined heretofore.Successfully submitting a project name or id shall launch the interfaces used for project entry pre-populated with the selected project’s data. This shall list owners, editors, descriptions and related geologic features / events / samples.By default the interface will be in read only mode.Edit button is available on interface for a user to toggle edit session onIf user is Project Owner, all items in the project form are available for editing, except the project name and fields maintained by system. This would include:Add / Remove Editors (available to user who is project owner)User provide pick list of all users with edit permissions User can assign one or more users to be editors for the projectProject owners are allowed to remove users from the listOnce editor is removed from list, they are immediately blocked from the project and all associated records.Modify project header data and descriptionsUpload images and documents associated directly with the project.Create geologic feature, event or sample.User will be prompted to select or create a stationUser will be allowed to create a geologic feature, event or sample and associate it to the project, and the selected station.User is directed to the geologic feature editing pagesList and edit geologic features, events or samplesButton / function will launch interface listing all geologic features currently associated with this project. This list will include stations associated with each feature.Clicking on geologic feature will open page pre-loaded with all of the data associated with it.If user is Project Editor and not project owner, only the creation, listing and editing of geologic features, events and samples are allowed.SAVE and system-generated fields updated:Date last editEditorExport Data: This button / function shall allow the user to export the project’s header and descriptive data into comma delimited or xml format. It shall also allow the wholesale export into similar formats of all of the geologic features, events and samples associated with this project.Create Reports: This button / function shall allow the user to create user friendly .pdf format reports of the project’s header and descriptive data. It shall also be able to produce .pdf reports of lists of all of the geologic features, events and samples associated with the project. Print Reports: This button shall send a .pdf report to a system printer available to the client computer that is connected to the RockIT system.Search: This button shall launch the search interface / page. This shall allow the user to search for information and ultimately navigate away from this project to another project, a station or geologic feature / event / sample.Assigning a new owner to a project shall be a system administrator duty, accessible from a different interface.StationsInvoking the “Create or Goto Station” function of the Dashboard will launch the “Main Station Page” which will expose the following functionality to the user:Create New Station:User interface appears in edit mode with options for user to enter the following:Station NameStation ID – system generated, hidden from userStation Owner – assigned automatically to user who creates stationNOTE: only station owner can update station attributesStation Type from pick list (reference lookup table)Measured SectionGeographic Location to include details presented in section 2.5.1Survey table will be updated with records describing related survey information for measured sectionApplication provides additional form for completing details about Measured Section, such as type, exposure, etc. See section 2.5.3 for details.From within the Measured Section form, users will have these options:Create Event / FeatureCreate SampleSearchExportPrintUpload images and documentsDrill HoleGeographic Location to include details presented in section 2.5.1Survey table will be updated with records describing related survey information for survey drill hole stationApplication provides additional form for completing details about Drill Hole, such as drill company, depth of hole, date drilled, etc. See section 2.5.3 for details.Application prompts user to complete the required information for the associated Survey Table records that record the detailed location information for Drill Holes with surveys (see Section From within the Drill Hole form, users will have the option to do the following:Create Event / FeatureCreate SampleSearchExportPrint Upload images and documentsCBM WellGeographic Location to include details presented in section 2.5.1Application provides additional form for completing details about CBM well, such as type, etc. See section 2.5.3 for details.From within the CBM Well form, users will have these options:Create Event / FeatureCreate SampleSearchExportPrintUpload images and documentsOutcropGeographic Location to include details presented in section 2.5.1Application provides additional form for completing details about Outcrop, such as type, exposure, etc. See section 2.5.3 for details.From within the Outcrop form, users will have these options:Create Event / FeatureCreate SampleSearchExportPrint Upload images and documentsIn addition to launching this interface through the Dashboard page, other tools will need to call on the “Create Station” module. For example:Create New Event / Feature / Sample will require that record has a station - user will either select from list of existing stations or be prompted to create a station while completing details for a new recordSAVE action will update requisite system fieldsDate createdUser who created record/ownerDate last editEditorEdit (Manage) Existing Station This presents the user with an interface to enter a station name or a station id.Entering the station name shall be assisted by predictive text functionality utilizing the names of all of the stations defined heretofore.Successfully submitting a station name or id shall launch the interfaces used for station entry pre-populated with the selected station’s data. This shall list owners, descriptions and related geologic features / events / samples.By default the interface will be in read only mode.Edit button is available on interface for a user to toggle edit session onIf user is Station Owner, all items of the station form are available for editing, except fields maintained by system. This would include:Modify station header data and descriptions.Modify the station’s origin coordinates.Add survey coordinates for the station.Method varies by type of station, if type of station not chosen, user will be prompted to enter station type.User will be presented survey entry page / form and prompted to create survey coordinates.Save button allows user to save survey data. Closing form without saving discards entered information. Modify survey coordinates for the station.User will be presented survey entry page / form pre-loaded with existing survey data, and prompted to modify survey coordinates.Save button allows user to save survey data. Closing form without saving discards entered information. Delete survey coordinates for the station.Delete button shall be available through interface.User will be prompted to confirm deletion.When a station owner spatially moves a station (modifies origin coordinates or survey information) that references features belonging to multiple users, a new station shall be created so that the other users’ features are not also moved without their permission. The system shall notify the corresponding users of such a change and allow them to take any actions they deem appropriate, such as assigning their geologic events / features / samples to the new station.Upload images and documents associated directly with the station.While a station does not belong to any projects, the features associated to it may belong to many projects. Any edits done to an existing station are announced to all owners of projects containing geologic features associated with this station, so that collaborators can take the actions they deem appropriate.Create geologic feature, event or sample.User will be prompted to select or create a stationUser will be allowed to create a geologic feature, event or sample and associate it to the project, and the selected station.User is directed to the geologic feature editing pagesList and edit geologic features, events or samplesButton / function will launch interface listing all geologic features currently associated with this station. This list will include projects associated with each feature.Clicking on geologic feature will open page pre-loaded with all of the data associated with it.If user is not project owner, only the creation, listing and editing of geologic features, events and samples are allowed.SAVE and system-generated fields updated:Date last editEditorExport Data: This button / function shall allow the user to export the station’s header and descriptive data into comma delimited or xml format. It shall also allow the wholesale export into similar formats of all of the geologic features, events and samples associated with this station.Create Reports: This button / function shall allow the user to create user friendly .pdf format reports of the station’s header and descriptive data. It shall also be able to produce .pdf reports of lists of all of the geologic features, events and samples associated with the station. Print Reports: This button shall send a .pdf report to a system printer available to the client computer that is connected to the RockIT system.Search: This button shall launch the search interface / page. This shall allow the user to search for information and ultimately navigate away from this station to another station, a project or geologic feature / event / sample.Assigning a new owner to a station shall be a system administrator duty, accessible from a different interface.Geologic Features / Events / SamplesAs mentioned previously, there shall be many different types of geologic features / events / samples. Features, for example could include bedrock and surficial geology; events would be things such as landslides; samples could include water, gas, etc. For each different schema inherent to a different type of features there shall be a customized interface / page.The interfaces to enter / modify geologic features can be reached through various workflows at various locations throughout the system, these include:Entering a new geologic feature / event / sample from the RockIT dashboard.This workflow requires user to use an existing station or create a new one.This workflow allows but does not require user to use an existing project or create a new one. If no project is defined the feature is added to the “general” project, accessible and editable by everybody.Entering a new geologic feature / event / sample from the Projects Page.This workflow requires user to use an existing station or create a new one.The current project is assigned to the new geologic feature.Entering a new geologic feature / event / sample from the Stations Page.Selecting an existing geologic feature / event / sample from the Search Interface.Selecting an existing geologic feature / event / sample from the Projects Page.Selecting an existing geologic feature / event / sample from the Stations Page.The first three workflows imply creating a new geologic feature / event / sample. Aside from additional steps required to define a station and a project, the following procedure shall be implemented:Create New Feature / Event / Sample: User interface appears in edit mode with options for user to select the type of feature. This selection determines what suite of data entry screens are displayed to user. Type will be selected from pick list that is generated by look up table. For example:BedrockSurficial GeologyLandslideSinkholeContactCoal DetailOthers…Once the feature type has been selected the user will be prompted to enter the following information:Name – user-definedID – unique system generated IDStation - (required if not defined already by workflow): User will select an existing station location or be prompted to create a new station by entering all pertinent header and geographic information. User will be asked to add details described in section 2.5.1. See Create Station in previous Stations section.Project - (not necessary if already defined by workflow): As mentioned previously this is not required. If desired the user will select an existing project or be prompted to create a new project by entering all pertinent header information. User will be asked to add details described in section 2.5.1. See Create Project in previous Projects section.Longitudinal Position Fields – If this feature is defined as “traversed” meaning that it is associated to a drill hole or a measured section station, a data entry form will become available and allow the user to enter length or thickness values and units as described in section 2.5.3 above.Coordinate Fields – These shall be populated automatically as the data entry process takes place.Feature / Event / Sample DetailsAttributes available for editing are driven by the typeWhen designing these screens, use the attributes provided in Section 4. Review this listing with the client to make sure attributes are current and complete. Samples require an additional foreign key to other geologic features and events, thus allowing the cross reference of multiple samples to a particular feature or event.Creating a new sample brings up a list of the features and events already associated with the current station allowing the user to choose one. The ID of the chosen feature is used as the foreign key.Types of samples include:CoalCBMRockWaterWhen working with an event / feature, user will be able to select button to ADD a new sample record.When working with a sample, user will be able to select a button to ADD a new analysis record.SAVE to commit edits and update system-generated fieldsDate createdCreatorDate last editEditorThe last three workflows imply editing an existing geologic feature / event / sample. In general this requires that a feature, event or sample be chosen from a list provided through the projects, stations or search interface.User will be presented with the aforementioned data entry interface, pre-loaded with all of the data corresponding to the selected geologic feature.By default the interface will be in read only mode.An edit button is available to toggle on edit mode.If the user owns the project to which this feature is associated or if the feature belongs to the “general” project all fields except those generated by the system shall be available for editing.When working with an event / feature, user will be able to select button to ADD a new sample record.Feature / Event interface will include list of all associated sample records. User can select an sample record to view / editWhen working with a sample, user will be able to select a button to ADD a new analysis recordSample interface will include list of all associated analysis records. User can select an analysis record to view / editSAVE to commit edits and update system-generated fieldsDate last editEditorMove data from one station to anotherAs described previously, it sometimes may be necessary to assign a geologic feature to a different station after it’s been discovered that the original station was incorrectly referenced to space.This action can only take place if user is owner of the project containing the feature.User shall be able to select multiple owned records and transfer them simultaneously.Records retain all attribute information with the exception of the associated station IDWithin the event / feature / samples entry pages when edit session is on or off, user is also able to access the following featuresExport Data: This button / function shall allow the user to export the geologic feature / event / sample header and descriptive data into comma delimited or xml format. It shall also allow the wholesale export into similar formats of all of the samples and analyses associated with this feature.Create Reports: This button / function shall allow the user to create user friendly .pdf format reports of the feature / event / sample header and descriptive data. It shall also be able to produce .pdf reports of lists of all of the samples and analyses associated with this feature.Print Reports: This button shall send a .pdf report to a system printer available to the client computer that is connected to the RockIT system.Search: This button shall launch the search interface / page. This shall allow the user to search for information and ultimately navigate away from this geologic feature / event / sample to another feature / event / sample, a project or geologic a station.Analysis ResultsCreate New Analysis RecordFrom the Sample page, users are provided a button to insert a new analysis recordNew record acquires the Sample ID of the record where analysis was addedAnalysis Record gets system-generated IDEnter associated analysisUser is provided page to create an analysis record or set of records and enter results that are associated to the SampleSample ID becomes foreign key to analysis recordAnalysis Options will be framed by sample type. For example:RockXRDSEMGeochemicalCoalCoal Quality AnalysisTrace elementsOther geochemicalCBMGeochemicalWaterGeochemicalEtc.User updates the appropriate attributes; see Section 4.2.10 for listing of fields to be populated.Some samples submitted to the Laboratory may be a known standard. User shall be able to enter name of standard from a drop down list containing known standards.When standard is used, application automatically checks results lab reports with known values for standard and reports how closely the Laboratory is meeting the standard.SAVE to commit edits and update system-generated fieldsDate createdCreatorDate last editEditorOnce created, associated analysis results are available within the Sample screen to view and/or selectEdit existing analysis recordOnce created, associated analysis results are available within the Sample screen for users to view and/or selectOnce record is selected, opens initially in read-only modeUser can select Edit button to begin an edit sessionAll fields except system-generated date and ID fields are available for editingSAVE to commit edits and update system-generated fieldsDate last editEditorWithin the Analysis screen user will have the following functionality available:ExportReportPrintUpload images and documentsImportData ImportData importing primarily refers to activities that pull groups of new records into the database, pulling the information into the appropriate tables and fields. Initially, data importing will facilitate migrating digital source materials into the database. Additionally, data import functionality, in concert with data entry tools, will help streamline data entry as workflows within the Bureau become rooted around the RockIT database, and lab results, field notes, photos, etc. are routinely cataloged and accessed through the database. The import routines the Bureau requires to support their current and future workflows include the following:Single or Batch Importing (to move one or more new records into one or more tables within the database)Data Migration Scripts (scripts tailored to automate data migration for non-standard, digital data sources)NOTE: Attaching images or other documents to records within the database has been addressed separately in Section 4.1, Images and Documents.Single or Batch ImportingThe majority of digital data sources the Bureau receives and/or develops is stored as Excel (.xls) or Access (.mdb) files. The application will need to accommodate importing all records in a source file, or a subset of those records, based on user-defined queries. Single and batch importing functionality will be accessed from the main Dashboard page and from the Analysis results screen and will include:Support for the following data type as data source files:Delimited TXTXLSMDBNOTE: Standard templates in .xls or .mdb format shall be provided in order to import data from well known data sources.For a given source file, the user will have the ability to define a query to select a sub-set of records (one or more). The Data Import tool will have the sensitivity to import all or only those selected records.The tool will provide a user interface that provides a side-by-side comparison of the source fields listed with their matching fields in the destination table. Within that interface, users will be allowed to do the following:Update the “matching fields” to correct instances where the Data Import tool incorrectly matches source and destination fields, or where there isn’t a matching data field between source and destination table.When a user wants to add a field from source data that is not already available in the destination table schema, the user must contact the System Administrator. The tool will not permit users, even users with edit permissions, to edit table schema.Data Migration ScriptsFor one time migration of existing Bureau data or for new “unexpected” data sources with “unknown” schemas the Bureau may need to develop some simple scripts, to automate data migration into RockIT. If a new data source becomes commonplace, new .xls and/or .mdb templates can also be developed.One such example would be the directory of drill hole images that the Bureau inherited from the R& P mining company. This is an archive of some 80,000 images that are tied to drill hole core samples. Images may have a consistent naming scheme that is unique to the mine’s record-keeping. Data migration scripts could parse the images into their associated records more quickly than could be done manually.Data Reporting and PrintingUsers will be able to access reports and printing functionality from several screens of the RockIT GUI (see section 3.2 “Data Entry and Editing” for specific references). The primary location for generating reports and/or opting to print results will be through the Search Results window (see Data Search Requirements, section 3.1 for details regarding search results content). The Search Results window offers an intuitive location for users to elect to consolidate selected records into a formatted report and/or to print results for distribution.Report formats will be developed under close consultation with Bureau investigators and staff. To date, the primary “reporting” the Bureau provides is for specific data requests received from the public, private companies, or government agencies. As the Bureau does not have any regulatory role presently, it does not have reporting requirements per say. That said, the Bureau does require organized templates for presenting and printing data search content, such as lab analysis results, event locations (such as landslides or sinkholes), station and feature locations. Whatever the specific reporting format, it will include the option to print directly to a network printer as well as the option to create a PDF document that can then be saved to a user-defined location. The option to generate a PDF document facilitates the distribution of database search results, as well as archiving results for future reference.Similarly, any printing that is performed directly from the search results grid will include the options to print to a network printer or to PDF. Ideally, this functionality will include automatic launching of the Adobe Reader to allow the user view the PDF document, print, and/or save the document.As the RockIT database and applications mature, data reporting may integrate with the Data Visualization tools described below in section 3.6 to support attribute reporting alongside mapping and other visualization products. This reporting/visualization integration is not presently a formal system requirement. It is functionality to note for future enhancements.Data ExportData export functionality will allow users to store selected records into the following formats: TXT, XLS, MDB, and personal geodatabase (MDB with some requisite tables and fields, an ESRI file format).The following user applications will provide data export functionality:Search ResultsProject PageStation PageEvent / Feature / Sample Page, including related attributesAnalysis Results tableThe Bureau uses a variety of third-party applications to display and/or further analyze the data stored within RockIT, Logplot and Igpet being two such examples. In the current work environment, the geologists that rely on these tools have developed custom-tailored xls spreadsheets to corral the attributes required to run LogPlot or IgPet analyses.It would be a worthwhile investment to develop a few custom data export scripts that re-produce the xls schema the geologists have already designed so that data coming from RockIT can be immediately directed into the next analysis environment, without creating additional work for the users. We recommend these custom tools include an editable configuration environment so that if the third-party applications change schema requirements or data format types, the Bureau can update the configuration file before running the tool, so the export results will display what is required for the third-party application.Data VisualizationAn important dimension for the RockIT database is the ability to transform the content of the records into mapped and graphic visuals for researchers to examine in space. As described early in the proposed data model, each record entered must have well-described geographic attributes, and with such attributes, the Bureau can insert the data into a variety of applications for rendering the shape of the features and their relative location to one another.The ESRI ArcGIS environment is already a standard GIS software the Bureau has implemented, mostly at the local computer level, with shapefiles, personal geodatabases storing the majority of the mapped content. Some mapping has been completed in the ArcInfo Workstation environment. An enterprise geodatabase has not yet been developed or implemented for the Bureau.It is recommended that as the RockIT database develops and data are migrated into the environment, the Bureau invest in a simultaneous effort to integrate the RockIT database within an ArcGIS environment (published ArcGIS Server website, for example) to support mapping and data visualization across the enterprise.System AdministrationLike the main RockIT user application Dashboard, the system administration login will be found on the dashboard, for the System Admin user account, with the following functionality:Manage User AccountsManage ProjectsManage Lookup and Data Reference TablesThis last item in the list includes the introduction of another type of user in the RockIT environment, what we describe as a “Subject Matter Expert” (SME). This concept is explained in detail in section 6.1 of the System Administration Tables section. Its general purpose is to provide a thin layer of administrative permissions to a strategic group of users, in addition to the System Administrator. These users will be responsible for helping maintain the lookup and data reference tables. Obviously, any user assigned this role warrants these privileges due to their subject matter expertise. At the Bureau’s request SMEs may be set to perform their duties exclusively through the system administrator.Manage User AccountsThe RockIT System Administrator will be the only authorized user who can create a new user account and/or edit its settings. Here are the system requirements for managing user accounts:Create User Account Form appears with table cued to “edit” mode. System Administrator sets the following fields (see System Administration section 6 for details about the contents of the USERS table):User NameNo character limitApplication verifies string is unique before committing entry If not unique, won’t accept user name, message appears reporting that user name is not unique.Required fieldEdit PermissionsPick list of choice, pulled from lookup table: Read, Edit, None.Notes:The system administrator account is pre defined at development time. System administrator can change any data as well as manage other accounts and passwords.Lookup and reference tables are conceptually defined by Subject Matter Experts (SME) and implemented or edited by System Administrator. Recommended default value = READStatusPick list choices, pulled from lookup table: Active, RetiredRecommended default Value = ACTIVEPress SAVE to commit entry to tableThe following fields are automatically populated when a new user account is saved:Unique User IDPassword generated for new user8 characters long Set character minimum at 5 and maximum at 32Do not allow spacesCase-sensitiveWhen new password entered, verify that it is a complex passwordCharacters displayed in user application form so System Administrator can see passwordUser allowed to edit upon first loginDate AddedDate EditedAfter saving new USER record, application returns to display showing list of all user accounts and their current settings.From this page, user has choice of the followingCreate New User AccountEdit User AccountReturn to main dashboard pageEdit / Remove User AccountSystem Administrator will be shown list of all user accounts in the system with current settings visibleRead-only mode is defaultSystem Administrator selects record and associated EDIT button or link to identify record to edit and to turn on edit sessionThe following fields are available for editing by System Administrator:User NameIf User name changes, system-generated ID remains the samePasswordStatusEdit PermissionsPress SAVE to commit changes and end edit sessionThe following fields will be automatically updated when USER record is updated:Date EditedThe following fields will not be edited by System Administrator or by application after initial creation of USER record:User IDDate AddedSystem Administrator is allowed to delete user accountsWorkflow Recommendation: This is discouraged unless account is recent or has always been “read-only”, having made no edits to databasePreferred method for “removing” user accountSet Status = RETIREDThis setting automatically triggers Edit Permissions to set at “NONE” and user account is effectively disabled from viewing or editing contentSince Date Edited field is automatically updated when USER ID record is edited, this maintains a history of the user account, in case it has been used to create a project, there by “own” records within the databaseAfter saving new USER record, application returns to display listing of all user accounts and their current settings.From this page, user has choice of the followingCreate New User AccountEdit User AccountReturn to main dashboard pageManage ProjectsIn the case where a researcher has resigned or retired from the Bureau and is no longer able to manage an active project, the Bureau has two options available for managing the Project(s) and associated database content owned by that researcher:Deactivate “Owner” AccountThis essentially freezes the project for editing by removing all edit permissions and grants.Projects frozen in this way still remain viewable.Update the Project with a new “Owner”This action assigns a new owner to the project, transferring the project owner edit permissions to that user.This does not remove the original owner for the project from the PROJECT_PERMISSIONS table. All it does is populate an ending date for “ownership”. The new owner shall be appended with a new starting date of “ownership”. This procedure will in fact preserve a chain of ownership for any project.The System Administrator is the only user account authorized to make these changes. It is recommended that any changes of this nature are performed after the Bureau’s Director and lead researchers have been informed, to ensure that the change is appropriate given existing resources.Upon selecting the “Manage Projects” button from the System Administrator dashboard page, a screen appears with a form listing the contents of the PROJECTS table set to “read-only” mode. The user has the following functionality available:Update OwnerSystem Administrator selects project form list on formOwner field is available for editingCued to read pick list of all users who are eligible to be project owners (edit permissions and status fields in USERS table determine this)User selects name from listPress OK or SAVE to commit edit to table and end edit sessionApplication automatically updates the DATE EDITED field in the PROJECTS tableApplication automatically updates the PROJECT_PERMISSIONS table to reflect this changeManage Lookup and Data Reference TablesExperience with the current Outcrops module has firmly established the fact that reference information is dynamic (consider the accepted list of geologic formations and their ages for example) and in need of constant update. Experience has also shown that the update process has to be managed carefully because any corruption of reference data will affect the entire database and by extension the work of the staff throughout the enterprise. This has already happened with the lookup table for color names which has been updated by many users in an ad-hoc manner and now contains a very large list including a significant amount of redundancies.Update of reference and lookup tables should therefore be performed through the system administration module and should be subject to the review and approval of subject matter experts assigned with this specific responsibility.When updating lookup and reference data it shall not be possible to delete any records in order to prevent breakage of referential integrity with existing features that use those references. It shall however be possible to tag a lookup or reference value as obsolete. Once tagged as obsolete a lookup value can still be used by the records that previously referenced it but can no longer be associated with new features entered into the system.The System Administrator shall have access to the following functionality through their dashboard pages:Manage Lookup and Reference TablesThis action allows the user to edit content of any lookup table or other data reference table, such as Associated Age for Formations, etc.Note: Lookup tables are entered the first time by customized scripts during initial implementation of the system.Upon selecting the MANAGE LOOKUP AND REFERENCE TABLE option from the dashboard, form opens listing columns of categories of lookup and reference tables, e.g. Bedrock, Surficial, Landslides, System Administration, etc. (read-only)User selects on category and the display changes to list only those tables (read-only)User toggles to an edit session and display includes the following options:Retire Value: user can select a value to retire it. This may be necessary if it was entered erroneously or has become obsolete.Application retains original code and description, no longer makes it available for subsequent editing.The retired value is preserved in order to continue support for features that reference this value, records that use this value will retain it.Add Value: user can add a new value to the listApplication automatically generates a new item code that is referenced in any records where the selected pick list is employedPress OK or SAVE to commit changes and end edit sessionDisplay listing tables from previously selected category returnsUser can select another table from this categoryUser can return to main listing to choose a new categoryDetailed Data ModelImages & DocumentsImages & documents occupy a special niche in this project as they can be associated with a variety of entities existing at various levels throughout the RockIT database. In this context entities are understood to include Projects, Stations, geological features / events / samples as well as specific descriptive properties of these.Based on interviews with end users the following business rules for pictures and documents have been established:It shall be possible to assign multiple pictures / documents to a specific database entity.It shall be possible to re-use a picture / document by assigning it to more than one database entity. This is particularly useful in the case of stock or reference imagery.There shall be an interface to allow users to browse through stock and reference pictures and assign them to the database entities that they choose. This interface shall organize pictures by the following themes: fossils, petrology, mineralogy, structural geology, geomorphology and any other criterion nominated by the Bureau at the time of development. This interface shall also include thumbnails to aid the users in discovering the desired material.It shall be possible for the user to “upload” a picture / document into the system from any drive available on a local desktop, including a thumb-drive.Uploading reference pictures shall require the choice of corresponding themes, and shall be a restricted functionality.Antivirus software should be implemented by RockIT to scan “uploaded” pictures / documents.The users shall be able to name their images / documents anyway they want, including the creation of redundant names, without risking overwriting other users’ images / documents or even their own.It is expected that images and documents will comprise a very large volume of information and constitute a significant percentage of the data stored in RockIT. In order to obtain the best possible performance these should not be stored directly in the database; instead they should be stored in a specially designated file system server. The path to the dedicated file server shall be stored in a system configuration parameter. Changing this parameter is all that is necessary to access the images if they need to be migrated to a different server.Supporting the ability to share images / documents among multiple database entities as well as to assign multiple images / documents to one entity implies by definition the existence of a many to many relationship between them. This many-to-many relationship shall be implemented by means of a join table as shown in the diagram below. Notice that in order to allow the existence of redundant names for documents and images, this structure differentiates between user-assigned names and the actual file names that the system assigns to each uploaded file. The actual file names shall consist of a random and unique sequence of characters generated by the system during the upload process. The uniqueness of the file names prevents unintended overwrites while the users only need to concern themselves with the names they assigned.Notice also that the type of file shall be recorded in the type field of the images / documents table. This field shall be controlled by a lookup table containing all of the allowable types of reference images as well as entries for “non reference” images and “non image” document types.The workflows for handling images will be implemented through the interfaces for geological features, as well as subject matter details which require associated images or documents (see below). These workflows shall include the following:Choosing to include an image / document while editing an entityUploading the image / documentChoosing a user name for the uploaded image / documentSearching and choosing reference imagesDisplaying an already stored image / document while reviewing / editing a geologic feature. This in some cases requires the installed software on the user’s desktop.Searching for keywords in already stored ASCII documents. This particular functionality shall be implemented from the search interface described above. Figure 41 Table Structure for Images & DocumentsAt the Bureau’s option, there shall exist the ability to georeference special images such as maps and cross sections in 3 D space. This would require the creation of an additional table and relationship (see table and arrows displayed in red, above) as well as the creation of a simple interface which would allow the mapping of image pixel coordinates (P-X and P-Y fields, above) to real world coordinates (fields X, Y and Z, above).This should include azimuth and length values for drill hole imaging + drill hole image tag.Geologic Features / Events / Samples and Analysis ResultsWith respect to RockIT, geologic features / events / samples represent the conceptual data containers that serve to gather geologic, chemical and physical properties (information) and bundle them into coherent, manageable units.The actual definition of each type of geologic feature, event or sample has been established based on what seems rational from a geologic standpoint. For example consider bedrock geology. Geologists classify bedrock into formations, units, horizons, etc. Each of these have multiple characteristics (age, rock type, mineralogy, depositional environment, etc) which, varying within certain constraints produce a characteristic signature which defines the unit. If an entire formation was the storage unit for bedrock geology the variability of its characteristics would be lost as properties would either have to be averaged or captured as ranges. Conversely, excessively subdivided data (storing individual properties separately for example) would result in the loss (or difficult reconstruction) of meaningful relationships among the individual lithologic attributes. Thus for bedrock geology the basic storage unit is defined as the “bedrock feature”, meaning a complete set of characteristics for a rock sample obtained or described in situ at one specific location.Each type of geologic feature / event / sample shall have its own table. These are supported by lookup and reference tables (see below).Aside from fields that are specific to particular types of geologic features each table shall have a set of standard fields designated to handle the relationship with stations, thus tying the features to space. As mentioned previously, at the database level this association is supported by a station_id foreign key field in the case of “point-like” stations (outcrops), an additional length and coordinate fields for “linear” stations (drill holes and measured sections).Geologic features shall furthermore be associated with projects. To support this association each geologic feature / event / sample table shall include a “Project” foreign key field. When creating a new feature this field will by default be set to the “General” project unless the user explicitly specifies a different project in which she/he has write permissions.Possibility of Schema ChangesAside from changes to the contents of lookup and reference tables it may also frequently be necessary to extend target tables (types of features) to include reference data that they did not include previously. Take porosity for example. Infotech’s requirements define a porosity-type lookup table for thin sections, but at some later date it may be decided that porosity can also be described for hand samples.Essentially these changes amount to schema changes, and as such should not be doable via user or system administration software. Nevertheless the system should be architected so that it is simple to modify in order to incorporate such a change. At the database level it should only be necessary to include a new foreign key in an existing target table, and to create a new reference table if it does not exist already. While at the application level this may require more involved changes the Bureau should be in a position to handle these as time goes on.Bedrock GeologyBedrock geology represents the geologic feature requiring the most complex storage structure and user interface in RockIT. It is expected that a large percentage of the content stored in this system will be bedrock geology. As mentioned previously the basic unit of bedrock geology shall be the bedrock feature, which is a complete rock description corresponding to one location in space. A bedrock feature may not occupy more than one location in space, but one location in space may be occupied by multiple bedrock features.Bedrock features shall be stored in a bedrock geology table which shall have fields to store the following types of data:Notes: All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Bedrock Id: Primary key of this tableCreator ID, Creation Date, Last Editor ID, Last Edit Date: Same rules as described previously.Rock Type: This shall be supported by a lookup table of rock types organized in terms of sedimentary, metamorphic and igneous concepts and possibly broken down further in terms of the most important categories beneath them. The actual organization of this shall be worked out with the participation of the Bureau’s subject matter experts.Rock Material Source: This field captures information about the type of “sample” that was used to produce the rock description, typically this would be things such as: Outcrop, Float, Cuttings, Core, etc. This shall be supported by a lookup table.Description (text field; optional)Texture Based on Rock Type exposes only certain values. For a Sedimentary rock this field shall include values such as “Clast Supported” and “Matrix Supported”; for metamorphic rocks it shall include values such as “Well Foliated”, “Poorly Foliated”; for igneous rocks it shall contain values such as “Coarse Grained”, “Medium Grained” and “Fine Grained”. It shall be controlled by a lookup tableRock Description (optional): This concept is actually supported by three fields containing foreign keys to three rock description (petrology) tables: Sedimentary rocksMetamorphic rocks Igneous rocks.Multiple descriptions of the same type can be associated with one bedrock feature to allow for the re-evaluation of a bedrock feature by one or more researchers. The foreign keys to the rock descriptions only contain the ids of the most recent rock description record in the corresponding table. This is considered to be the most authoritative description for the bedrock feature. The owner of the feature shall have the ability to choose among the existing descriptions to designate a new most authoritative description.In order to support the aforementioned ability to associate multiple rock descriptions of one type with a bedrock feature, each of the rock description tables includes a foreign key to the bedrock feature id.Sedimentary Description: This constitutes the rock description table designated to capture the characteristics of sedimentary rocks.Note that sedimentary descriptions can be associated to bedrock features as well as to surficial geology features. Thus sedimentary descriptions shall contain a foreign key bedrock features and a foreign key for surficial geology features. It shall not be possible to populate both for the same sedimentary description.This shall include the following data types / fields:Sedimentary Description Id: Primary key of this table.Bedrock Feature Id: This is the foreign key that ties a sedimentary description to a bedrock feature.Surficial Geology Feature id: This is the foreign key that ties a sedimentary description to a surficial geology feature.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Same rules as described rmal Rock Unit (optional): If a rock description and/or sample are obtained from a source outside the Bureau, this field will capture whatever name the outside source gave to the rock.Percent of Unit: This represents the percentage of the containing bedrock unit that is made up by this sedimentary description.Fresh Color: Color of fresh rock surface. This shall be supported by a lookup table which shall be worked out with the Bureau’s subject matter experts, possibly to reflect a standard scheme such as the Munsell Color Chart.Weathered Color (optional, may not be available in core or cuttings): Color of a weathered rock surface. As with fresh color, this shall be supported by a lookup table reflecting a standard color scheme.Fragmentation Size (optional): This indicates the average size of fragments in cases where the rock tends to break up. Values shall be > ? inch and < ? inch. These shall be available from a lookup table.Lithology Arrangement (optional, may not be apparent from cuttings): This represents the macroscopic physical arrangement of the described rock unit. Typical values might be Dominant, Interbed, Interlayer, Lens, Pods, Veins, etc.Lithology Arrangement Thickness (optional): Average width for lenses, pods, etc.Lithology Arrangement Length (optional): Average length for lenses, pods, etc.Lithology Arrangement Unit (required if width or height given): Units in which lithology arrangement width or length are given. This shall be controlled by a lookup table.Planar Fabrics: This category represents a variety of morphological features which can be described by planes with measured orientations within a bedrock feature (lithologic unit).Planar fabrics shall be accessed from a common entry interface which shall expose all types of planar fabrics by means of a dropdown list.Planar fabrics shall be implemented by means of external tables, which shall be associated to sedimentary, metamorphic or igneous descriptions by means of a “rock description id” field. In order to determine which type of description there shall also be a field to capture the name of the table to which the “rock description id” corresponds. It shall be possible to associate one or more planar fabrics with a particular “rock description”. Certain types of planar fabrics, such as bedding, shall be restricted to only on fabric per rock description, while others, such as partings or foliations may include multiple fabrics per rock description.Additionally each of these “fabric tables” shall contain a set of fields to capture the specific attributes typical of the type of planar fabric being described.The measurements describing the orientation of every individual planar feature shall be kept in the “Planar Elements Table” described in section 2.5.2.1 rather than within the “fabric tables” themselves. As described above this association shall be implemented by means of the “Geologic Feature Id” and the “Table Name” fields within the “Planar Elements Table. This arrangement allows, if necessary, to associate many planar orientation measurements with one planar fabric.The planar fabrics described in this section correspond to those commonly associated with sedimentary rocks (such as bedding). Other planar fabrics may be associated with igneous or metamorphic rocks. It must also be stated that this is not an exhaustive list of planar fabrics for sedimentary rocks. New ones could be defined at any moment, in fact all that is necessary to create a new planar fabric is to create a new table following the aforementioned guidelines.Bedding: This information, which refers to the layering that is commonly visible in sedimentary rocks, shall be accessed through the “Planar Fabrics interface”. This data type shall be contained in a ”Bedding Table”.A special bedding input interface shall be exposed upon selecting “Bedding” from the “Planar Fabrics Interface”. The bedding entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.Although this database structure would allow the association of multiple bedding features with one sedimentary description, only one is allowed. If it is necessary to describe multiple bedding features for one bedrock feature, multiple sedimentary descriptions, each with its own bedding, should be used.All Bedding Table Fields are optional; they comprise the following:The contents of this table shall be exposed by a specific input interface that will allow the user to enter new -or modify existing data. Bedding Id: Primary Key of this table, neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Same rules as described previously.Rock Description Id: This is the foreign key to the sedimentary (or metamorphic) description table.Table Name: This field indicates which description table is associated with this bedding record.Bedding Type (optional): This shall contain values such as “Crossbedded”, “Plane Laminated”, “Lenticular”, etc. This field shall be controlled by a lookup table. (Massive?)Bedding Thickness: This refers to the average thickness of bedding if present. This is not a measured quantity; there is a limited set of allowable values controlled by a lookup table. (Redundant?)Set Thickness (optional): Set Thickness Units (required if set thickness given): Measurement units for thicknesses. This is controlled by a lookup table.Coset Thickness (optional): Average thickness of a group of beds with similar bedding structures.Coset Thickness Units (required if Average, maximum or minimum coset thickness is given): Measurement units for thicknesses. This is controlled by a lookup table.Cross Bedding Laminae Thickness (optional): Average maximum thickness of preserved ripple/dune laminae based on lee, stoss or both.Cross Bedding Laminae Thickness Units (required if Cross Bedding Laminae Thickness is given): Measurement units for thicknesses. This is controlled by a lookup table.Relative Dip of Cross Bedding Laminae (optional) ? ABSOLUTE NOT RELATIVEBed Surface Features (optional): These are features identifiable on the surface of individual beds (tool marks for example). Theses shall be stored in a “bed surface features” table to allow the existence of multiple features per bedding description.Intra Bed Features (optional): These are features found within beds. Theses shall be stored in an “intra bed features” table to allow the existence of multiple features per bedding description.Bed Relation Descriptions (optional): This contains descriptions regarding how beds relate to their immediate neighbors. Theses shall be stored in a “bed relation descriptions” table to allow the existence of multiple relations per bedding description.Bedding Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the Sedimentary Description table. This arrangement allows the association of multiple orientation measurements with this one sedimentary description.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current bedding ments (optional): Free form text field allowing the editor to include any additional comments / notes.Parting (optional): Parting refers to systems of planar surfaces along which rocks tend to break. Parting, for the purposes of this system shall be accessed through the “Planar Fabrics interface”. A special input interface shall be exposed upon selecting “Parting” from the “Planar Fabrics Interface”. The parting entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in a “Parting table” such that multiple partings can be associated with one sedimentary description.Parting Table: This shall contain the data types listed below:Parting Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Rock Description Id: This is the foreign key to the sedimentary description table.Table Name: This field indicates which description table is associated with this parting record.Parting Spacing: This refers to the average spacing between parting surfaces. This is not a measured quantity, there is a limited set of allowable values controlled by a lookup table.Parting Type: Common values for this field include: “Jointing”, “Bedding”, “Coal Cleat”, “Cleavage”, “Fissility”. Etc. This field shall be controlled by a lookup table.Parting Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the parting table. Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current parting ments (optional): Free form text field allowing the editor to include any additional comments / notes.Miscellaneous Planar Fabric (optional): This includes planar fabrics not included among the previous “fabric tables”.A special input interface shall be exposed upon selecting “Miscellaneous Planar Fabrics” from the “Planar Fabrics Interface”. The Miscellaneous Planar Fabrics entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type is contained in a “Miscellaneous Planar Fabrics table” such that multiple fabrics can be associated with one sedimentary description.Miscellaneous Planar Fabrics Table: This shall contain the following data types / fields:Planar Fabric Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Rock Description Id: This is the foreign key to the sedimentary description table.Table Name: This field indicates which description table is associated with this bedding record.Planar Fabric: This field shall include a descriptive term characterizing the particular type of fabric being describedPlanar Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the bedding table. Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current sedimentary fabric feature.Linear Fabrics: This category represents a variety of morphological features which can be described by lines with measured orientations within a bedrock feature (lithologic unit).Linear fabrics shall be accessed from a common entry interface which shall expose all types of linear fabrics by means of a dropdown list.Linear fabrics shall be implemented by means of external tables, which shall be associated to sedimentary, metamorphic or igneous descriptions by means of a “rock description id” field. In order to determine which type of description there shall also be a field to capture the name of the table to which the “rock description id” corresponds. It shall be possible to associate one or more linear fabrics with a particular “rock description”. Certain types of linear fabrics may need to be restricted to only on fabric per rock description, while others may include multiple fabrics per rock description.Additionally each of these “fabric tables” shall contain a set of fields to capture the specific attributes typical of the type of linear fabric being described.The measurements describing the orientation of every individual planar feature shall be kept in the “Linear Elements Table” described in section 2.5.2.2 rather than within the “fabric tables” themselves. As described above this association shall be implemented by means of the “Geologic Feature Id” and the “Table Name” fields within the “Linear Elements Table. This arrangement allows, if necessary, to associate many linear orientation measurements with one linear fabric.The linear fabrics described in this section correspond to those commonly associated with sedimentary rocks. Other linear fabrics may be associated with igneous or metamorphic rocks. It must also be stated that this is not an exhaustive list of linear fabrics for sedimentary rocks. New ones could be defined at any moment, in fact all that is necessary to create a new linear fabric is to create a new table following the aforementioned guidelines.Miscellaneous Linear Fabric (optional): This includes linear fabrics not included within specialized “fabric tables”.A special input interface shall be exposed upon selecting “Miscellaneous Linear Fabrics” from the “Linear Fabrics Interface”. The Miscellaneous Linear Fabrics entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type is contained in a “Miscellaneous Linear Fabrics table” such that multiple fabrics can be associated with one sedimentary description.Miscellaneous Linear Fabrics Table: This shall contain the following data types / fields:Linear Fabric Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Rock Description Id: This is the foreign key to the sedimentary description table.Table Name: This field indicates which description table is associated with this bedding record.Linear Fabric: This field shall include a descriptive term characterizing the particular type of fabric being describedLinear Orientation (optional): This shall be stored within the Linear Elements Table (see section 2.5.2.2) which shall include a foreign key pointing to this record in the Miscellaneous Linear Fabrics table. Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current sedimentary fabric feature.Sorting (optional): Typical values for this field would include “Well Sorted”, “Semi Well Sorted” and “Poorly Sorted”. This field shall be controlled by a lookup table. Grading (optional): Typical values for this field would include “Fining Upward”, “Graded Bedding” and “Coarsening Upward”. This field shall be controlled by a lookup table. MINERALS: This category shall include all minerals that make up a sedimentary rock. Minerals for sedimentary descriptions shall be stored in a separate external sedimentary minerals table, such that many minerals can be associated with one sedimentary description. This table should not be confused with the Minerals reference table, which contains basic descriptions for all of the minerals that could possibly be used to describe any particular geologic feature.A specialized sedimentary minerals table is necessary in order capture a set of specialized fields which only pertain to minerals in a sedimentary environment, such as “Mineral Grain Roundness” and “Grain vs. Matrix”.Sedimentary Minerals Table: This shall contain the following data types / fields:Mineral Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable. Rock Description Id: This is a foreign key pointing to the description id of a sedimentary (and in some cases metamorphic) rock that contains these grains/clasts.Table Name: This field indicates which description table (sedimentary or metamorphic) is associated with this mineral record.Mineral: Name of the mineral. This should be populated from a drop down list, making available the contents of the minerals reference table.Mineral Type: This shall contain values for mineral families such as: “Halides”, “Sulphides”, “Carbonates”, etc. Picking this will narrow down the list of minerals presented in the next field. If this is not chosen, picking a mineral will automatically populate this field. This shall be controlled by the Minerals reference table.Grain vs. Matrix: Valid values for this field are “Grain” and “Matrix”. This indicates whether the mineral being entered is clastic or forms part of the matrix of the sedimentary rock.Mineral Grain Shape: This shall include values such as “Equant”, “Oblong”, “Hexagonal”, etc. Field shall be controlled by lookup table.Mineral Grain Roundness: This shall include values such as “Angular”, “Subangular”, “Subround”, etc. Field shall be controlled by lookup table.Mineral Grain Abundance (optional if percentage is populated): This shall include “Abundant”, “Common”, “Few”, “Rare”. Field shall be controlled by lookup table.Mineral Grain Percentage (optional if abundance is populated): Percentage of this rock type that is made up by grains of this mineral.Mineral Grain Provenance (optional): This shall contain an officially recognized lithologic unit and shall be populated through the same interface as described for Interpretation (see below).Mineral Grain Crystal Habit (optional): This shall include values such as “Anhedral”, “Fibrous”, “Columnar”, “Cubic”, etc. This shall be controlled by a lookup table.Mineral Grain Dominant Size Category: This shall include the following values: “Fine”, “Medium”, “Coarse”, “Fine to Coarse”, etc. This field shall be controlled by a lookup table.Mineral Grain X-Axis Size (optional): This is a measurement value for the average long axis for this type of grain.Mineral Grain Y-Axis Size (optional): This is a measurement value for the average intermediate axis for this type of grain.Mineral Grain Z-Axis Size (optional): This is a measurement value for the average short axis for this type of grain.Mineral Grain Average Size (optional): This is a measurement value for the average diameter of the grains to be used instead of X, Y and Z-Axis Sizes, if preferred by the researcher.Mineral Grain Size Units: Measurement units for axis sizes. This shall be controlled by a lookup table.Mineral Grain Preferred Orientation: This shall be stored within the Linear Elements Table (see section 2.5.2.2) which shall include a foreign key pointing to this record in the Sedimentary Minerals Table. Matrix Mineral Crystal Habit: This shall include values such as “Anhedral”, “Fibrous”, “Columnar”, “Cubic”, etc. This shall be controlled by a lookup table.Matrix Mineral Percentage: Percentage of the containing rock that is made up by this mineral.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current precipitate ments (optional): Free form text field allowing the editor to include any additional comments / notes.Lithic_Clasts: This type of data shall describe the lithic grains and / or clasts that make up a sedimentary rock. Lithic Clasts shall be stored in a separate external Lithic Clasts table, such that many types of clasts can be associated with one sedimentary description. Lithic Clasts Table: This shall contain the following data types / fields:Clast Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable. Rock Description Id: This is a foreign key pointing to the description id of a sedimentary (and in some cases metamorphic) rock that contains these grains/clasts.Table Name: This field indicates which description table (sedimentary or metamorphic) is associated with this lithic clast mon Rock Name: Common name of the rock type that makes up this set of grains. This shall be supported by a lookup table of rock types.Lithic Id: (Optional) This is a foreign key pointing to the description id of a sedimentary, igneous or metamorphic rock. If a more complete petrologic description of the grains (clasts) is required, the grains interface shall allow the user to enter a new rock description (sedimentary, metamorphic or igneous) and will automatically assign the primary key of this description to this field. While the underlying database structure for rock descriptions should be re-used to store lithic clasts (rather than creating new specialized tables, this keeps the database design as simple as possible), the interface to enter these may at the Bureau’s request be greatly simplified to enter only the data types that it considers appropriate.Alternatively it shall be possible to pick a description from a list of rock descriptions (sedimentary, metamorphic or igneous) already associated with this bedrock feature and have its Description Id assigned to this field. The Lithic_Table field shall be used in tandem with this field in order to distinguish the table referenced by the contained foreign key.Lithic_Table: (required if Lithic_Id populated) This contains one of three values “Sedimentary”, “Igneous”, “Metamorphic”. This indicates which rock description table holds the rock description corresponding to this type of grain/clast.Lithic Clast Shape: This shall include values such as “Equant”, “Oblong”, “Hexagonal”, etc. Field shall be controlled by lookup table.Lithic Clast Roundness: This shall include values such as “Angular”, “Subangular”, “Subround”, etc. Field shall be controlled by lookup table.Lithic Clast Abundance (optional if percentage is populated): This shall include “Abundant”, “Common”, “Few”, “Rare”. Field shall be controlled by lookup table.Lithic Clast Percentage (optional if abundance is populated): Percentage of this rock type that is made up by this type of clast.Lithic Clast Provenance (optional): This shall contain an officially recognized lithologic unit and shall be populated through the same interface as described for Interpretation (see below).Lithic Clast Dominant Size Category: This shall include the following values: “Fine”, “Medium”, “Coarse”, “Fine to Coarse”, etc. This field shall be controlled by a lookup table.Lithic Clast X-Axis Size (optional): This is a measurement value for the average long axis for this type of clast.Lithic Clast Y-Axis Size (optional): This is a measurement value for the average intermediate axis for this type of clast.Lithic Clast Z-Axis Size (optional): This is a measurement value for the average short axis for this type of clast.Lithic Clast Average Size (optional): This is a measurement value for the average diameter of the clasts to be used instead of X, Y and Z-Axis Sizes, if preferred by the researcher.Lithic Clast Size Units: Measurement units for axis sizes. This shall be controlled by a lookup table.Lithic Clast Preferred Orientation: This shall be stored within the Linear Elements Table (see section 2.5.2.2) which shall include a foreign key pointing to this record in the Lithic Clasts Table. Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current precipitate ments (optional): Free form text field allowing the editor to include any additional comments / notes.Fossils (optional): This represents fossils preserved in this sedimentary rock.A special input interface shall exist to enter fossils. This fossil entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in a “fossils table” such that multiple fossils can be associated with one sedimentary description. Fossils Table: This shall contain the data types listed below:Fossil_Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Rock Description Id: This is the foreign key to the sedimentary description table.Fossil Type ID: This is a foreign key that references the Fossil lookup table. The fossil lookup table includes the following:Fossil Name: This shall include the common or scientific name for the fossilized organism (or organism parts, as in conodont elements). Field shall be controlled by lookup table.Fossil Type: This shall include values such as “Plant”, “Marine Vertebrate”, “Marine Invertebrate”, etc. Field shall be controlled by lookup table.Taxon: This shall include the name of the taxonomic grouping to which the fossilized organism belongs, for example “Mollusca”, “Foraminifera”, etc. Field shall be controlled by lookup table.General Fossil DescriptionFossil Distribution: This shall include values such as “Uniform”, “Layered”, “Channel Lag”, etc. Field shall be controlled by lookup table.Preferred Orientation: This shall be stored within the Linear Elements Table (see section 2.5.2.2) which shall include a foreign key pointing to this record in the Fossils Table.Abundance: This shall include “Abundant”, “Common”, “Few”, “Rare”, etc. Field shall be controlled by lookup table.Fossil Condition: This shall include values such as “Fragments”, “Hash”, “Body Fossil”, “Trace Fossil”, etc. Field shall be controlled by lookup table.Fossil Mode: This shall include the mode of preservation, such as “Molds”, “Casts”, “Carbonization”, “Replacement”, etc. Field shall be controlled by lookup table.Preservation: This shall include values such as “Well Preserved”, “Poorly Preserved”, “Halo”, etc. Field shall be controlled by lookup table.Fossil Mineral (optional): This corresponds to the minerals that make up a particular type of fossil. A special input interface shall exist to enter fossil minerals. This fossil mineral entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in a “fossil minerals table” such that multiple minerals can be associated with one fossil type. Fossil Minerals Table: This shall contain the data types listed below:Fossil Mineral Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Fossil Id: This is the foreign key to the fossils table.Mineral Type: This shall contain values for mineral families such as: “Halides”, “Sulphides”, “Carbonates”, etc. Picking this will narrow down the list of minerals presented in the next field. If this is not chosen, picking a mineral will automatically populate this field. This shall be controlled by the Minerals reference table.Mineral: The mineral name. This shall be controlled by the Minerals reference table.Mineral Texture (Crystal Habit) (optional): This shall include values such as “Anhedral”, “Fibrous”, “Columnar”, “Cubic”, etc. This shall be controlled by a lookup table.Abundance (optional if percentage is populated): This shall include “Abundant”, “Common”, “Few”, “Rare”. Field shall be controlled by lookup table.Percentage (optional if abundance is populated): Percentage of this fossil type that is made up by this mineral.Percentage of Rock: Percentage of the containing rock that is made up by this fossil.Percentage of Assemblage (optional): If more than on fossil type is present, this is the percentage of the entire fossil assemblage that is made up by this fossil.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current ments (optional): Free form text field allowing the editor to include any additional comments / notes.Concretions (optional): Ovoid to irregularly shaped zones in sedimentary rock with relatively higher resistance to weathering due to cementation. A special input interface shall exist to enter concretions. This concretion entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in a “concretions table” such that multiple types of concretions can be associated with one sedimentary description.Concretions Table: This shall contain the data types listed below:Concretion Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable. Rock Description Id: This is a foreign key pointing to the description id of a sedimentary rock that contains these concretions. Composition / Mineral (optional) This shall contain the dominant mineral involved in the cementation of the concretion. This field shall be controlled by the minerals reference table.Note: At the Bureau’s option this could be modified to capture more than one mineral per concretion type.Shape: This shall include values such as “Spherical”, “Ovoid”, etc. Field shall be controlled by lookup table.Abundance: This shall include “Abundant”, “Common”, “Few”, “Rare”. Field shall be controlled by lookup table.General Size: This is a measurement value for the average size of this type of concretion, useful if axis information is not available.X-Axis Size (optional): This is a measurement value for the average long axis for this type of concretion.Y-Axis Size (optional): This is a measurement value for the average intermediate axis for this type of concretion.Z-Axis Size (optional): This is a measurement value for the average short axis for this type of concretion.Size Units: Measurement units for sizes. This shall be controlled by a lookup table.Preferred Orientation: This shall be stored within the Linear Elements Table (see section 2.5.2.2) which shall include a foreign key pointing to this record in the Concretions Table.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current concretion ments (optional): Free form text field allowing the editor to include any additional comments / notes.Depositional Environment (optional): These shall be kept in a Depositional Environments Table so that multiple interpretations can be associated to one sedimentary rock description.The contents of this table shall be exposed by a specific input interface that will allow the user to enter new -or modify existing data.Depositional Environments Table: This shall contain the data types listed below:Depositional Environment Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable. Rock Description Id: This is a foreign key pointing to the description id of a sedimentary rock that represents this depositional environment.Depositional Environment: This shall include values such as “Aeolian”, “Flood Plain”, “Intertidal”, “Alluvial Fan”, etc. This shall be controlled by a lookup table.Confidence Rating: This concept shall represent the level of confidence of the investigator when proposing this depositional environment. This field shall be mandatory only when an interpretation is made. This field shall be controlled by a lookup table containing the following values:Not SureFairly SureSureConfidentVery ConfidentImages / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current depositional ments (optional): Free form text field allowing the editor to include any additional comments / notes.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current sedimentary ments (optional): Free form text field allowing the editor to include any additional comments / notes.Metamorphic Description: This constitutes the rock description table designated to capture the characteristics of metamorphic rocks.This shall include the following data types / fields:Metamorphic Description Id: Primary key of this table. Is neither exposed nor editable.Bedrock Feature Id: This is the foreign key that ties a metamorphic description to a bedrock feature.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Same rules as described rmal Rock Unit (optional): If a rock description and/or sample are obtained from a source outside the Bureau, this field will capture whatever name the outside source gave to the rock.Percent of Unit: This represents the percentage of the containing bedrock unit that is made up by this metamorphic description.Fresh Color: Color of fresh rock surface. This shall be supported by a lookup table which shall be worked out with the Bureau’s subject matter experts, possibly to reflect a standard scheme such as the Munsell Color Chart.Weathered Color (optional, may not be available in core or cuttings): Color of a weathered rock surface. As with fresh color, this shall be supported by a lookup table reflecting a standard color scheme.Fragmentation Size (optional): This indicates the average size of fragments in cases where the rock tends to break up. Values shall be > ? inch and < ? inch. These shall be available from a lookup table.Lithology Arrangement (optional, may not be apparent from cuttings): This represents the macroscopic physical arrangement of the described rock unit. Typical values might be Dominant, Interbed, Interlayer, Lens, Pods, Veins, etc.Lithology Arrangement Thickness (optional): Average width for lenses, pods, etc.Lithology Arrangement Length (optional): Average length for lenses, pods, etc.Lithology Arrangement Unit (required if width or height given): Units in which lithology arrangement width or length are given. This shall be controlled by a lookup table.Metamorphic Type: This refers to the main categories of metamorphism and include values such as “Contact (Thermal)”, “Regional (Dynamo-Thermal)”, “Cataclastic”, etc. This field shall be controlled by a lookup positional Type: This refers to the main composition types of the original rocks and includes values such as “Ultramafic”, “Mafic”, “Pelitic”, “Calcareous”, etc. This field shall be controlled by a lookup table.Metamorphic Facies (optional): This shall record the metamorphic facies corresponding to the current description; this shall include values such as “Zeolite”, “Greenschist”, “Hornfels”, “Blueschist”, etc. If Retrograde Metamorphic Facies is defined this shall correspond to the maximum facies that is recognizable in this rock. This field shall be controlled by a lookup table.Retrograde Metamorphic Facies (optional): This shall record the metamorphic facies in the event that retrograde metamorphism is recognized. This shall use the same lookup table as the previous field.Planar Fabrics: This category represents a variety of morphological features which can be described by planes with measured orientations within a bedrock feature (lithologic unit).Planar fabrics shall be accessed from a common entry interface which shall expose all types of planar fabrics by means of a dropdown list.Planar fabrics shall be implemented by means of external tables, which shall be associated to sedimentary, metamorphic or igneous descriptions by means of a “rock description id” field. In order to determine which type of description there shall also be a field to capture the name of the table to which the “rock description id” corresponds. It shall be possible to associate one or more planar fabrics with a particular “rock description”. Certain types of planar fabrics, such as bedding, shall be restricted to only on fabric per rock description, while others, such as partings or foliations may include multiple fabrics per rock description.Additionally each of these “fabric tables” shall contain a set of fields to capture the specific attributes typical of the type of planar fabric being described.The measurements describing the orientation of every individual planar feature shall be kept in the “Planar Elements Table” described in section 2.5.2.1 rather than within the “fabric tables” themselves. As described above this association shall be implemented by means of the “Geologic Feature Id” and the “Table Name” fields within the “Planar Elements Table. This arrangement allows, if necessary, to associate many planar orientation measurements with one planar fabric.The planar fabrics described in this section correspond to those commonly associated with metamorphic rocks (such as foliation). Other planar fabrics may be associated with igneous or sedimentary rocks. It must also be stated that this is not an exhaustive list of planar fabrics for metamorphic rocks. New ones could be defined at any moment, in fact all that is necessary to create a new planar fabric is to create a new table following the aforementioned guidelines.Bedding: This information refers to the layering that is commonly visible in sedimentary rocks. It is included here because bedding is often visible in metamorphic rocks as a relict sedimentary texture. Bedding shall be accessed through the “Planar Fabrics interface”. This data type shall be contained in the ”Bedding Table” described for sedimentary rocks.A special bedding input interface shall be exposed upon selecting “Bedding” from the “Planar Fabrics Interface”. The bedding entry interface shall expose the data types previously listed for sedimentary descriptions and allow the user to enter new -or modify existing data.Although this database structure would allow the association of multiple bedding features with one metamorphic description, only one is allowed. If it is necessary to describe multiple bedding features for one bedrock feature, multiple metamorphic descriptions, each with its own bedding, should be used.Parting (optional): Parting refers to systems of planar surfaces along which rocks tend to break. Parting, for the purposes of this system shall be accessed through the “Planar Fabrics interface”. A special input interface shall be exposed upon selecting “Parting” from the “Planar Fabrics Interface”. The parting entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Parting table” laid out for sedimentary descriptions such that multiple partings can be associated with one metamorphic description.Foliation (optional): Foliation refers to a planar fabric defined by preferred growth orientation/s of (usually platy) minerals or segregation of minerals into distinct planar zones, or the flattening, along a preferred orientation, of fossils, clasts or other relict features contained in a metamorphic rock. Foliation, for the purposes of this system shall be accessed through the “Planar Fabrics interface”. A special input interface shall be exposed upon selecting “Foliation” from the “Planar Fabrics Interface”. The foliation entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in a “Foliation table” such that multiple foliations can be associated with one metamorphic description.Foliation Table: This shall contain the data types listed below:Foliation Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Rock Description Id: This is the foreign key to the metamorphic description table.Table Name: This field indicates which description table is associated with this foliation record.Foliation Type: This shall contain values such as “Gneissic Banding”, “Schistosity”, “Slaty Cleavage”, etc. This field shall be controlled by a lookup table.Foliation Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the foliation table. Relative Age (optional): This shall contain the relative age of this foliation with respect to other foliations or lineations associated with this metamorphic rock. It shall be represented by values such as “D1”, “D2”, “D3”. This field shall be controlled by a lookup table. Note: An alternative to the use of this field would be the use of the relative age fields in the planar elements table.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current parting ments (optional): Free form text field allowing the editor to include any additional comments / notes.Miscellaneous Planar Fabric (optional): This includes planar fabrics not included among the previous “fabric tables”.A special input interface shall be exposed upon selecting “Miscellaneous Planar Fabrics” from the “Planar Fabrics Interface”. The Miscellaneous Planar Fabrics entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Miscellaneous Planar Fabrics Table” laid out for sedimentary descriptions such that multiple foliations can be associated with one metamorphic description.Linear Fabrics: This category represents a variety of morphological features which can be described by lines with measured orientations within a bedrock feature (lithologic unit).Linear fabrics shall be accessed from a common entry interface which shall expose all types of linear fabrics by means of a dropdown list.Linear fabrics shall be implemented by means of external tables, which shall be associated to sedimentary, metamorphic or igneous descriptions by means of a “rock description id” field. In order to determine which type of description there shall also be a field to capture the name of the table to which the “rock description id” corresponds. It shall be possible to associate one or more linear fabrics with a particular “rock description”. Certain types of linear fabrics may need to be restricted to only on fabric per rock description, while others may include multiple fabrics per rock description.Additionally each of these “fabric tables” shall contain a set of fields to capture the specific attributes typical of the type of linear fabric being described.The measurements describing the orientation of every individual planar feature shall be kept in the “Linear Elements Table” described in section 2.5.2.2 rather than within the “fabric tables” themselves. As described above this association shall be implemented by means of the “Geologic Feature Id” and the “Table Name” fields within the “Linear Elements Table. This arrangement allows, if necessary, to associate many linear orientation measurements with one linear fabric.The linear fabrics described in this section correspond to those commonly associated with metamorphic rocks. Other linear fabrics may be associated with igneous or sedimentary rocks. It must also be stated that this is not an exhaustive list of linear fabrics for metamorphic rocks. New ones could be defined at any moment, in fact all that is necessary to create a new linear fabric is to create a new table following the aforementioned guidelines.Lineation (optional): Lineation refers to a linear fabric defined by preferred growth orientation/s of (commonly columnar of fibrous) minerals, or the stretching, along a preferred orientation, of fossils, clasts or other relict features contained in a metamorphic rock. Lineation, for the purposes of this system shall be accessed through the “Linear Fabrics interface”.A special input interface shall be exposed upon selecting “Lineation” from the “Linear Fabrics Interface”. The lineation entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in a “Lineation table” such that multiple lineations can be associated with one metamorphic description.Lineation Table: This shall contain the data types listed below:Lineation Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Rock Description Id: This is the foreign key to the metamorphic description table.Table Name: This field indicates which description table is associated with this foliation record.Lineation Type: This shall contain values such as “Stretched Pebbles”, “Aligned Minerals”, etc. This field shall be controlled by a lookup table.Lineation Orientation (optional): This shall be stored within the Linear Elements Table (see section 2.5.2.2) which shall include a foreign key pointing to this record in the foliation table. Relative Age (optional): This shall contain the relative age of this lineation with respect to other foliations or lineations associated with this metamorphic rock. It shall be represented by values such as “D1”, “D2”, “D3”. This field shall be controlled by a lookup table. Note: An alternative to the use of this field would be the use of the relative age fields in the linear elements tables.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current parting ments (optional): Free form text field allowing the editor to include any additional comments / notes.Miscellaneous Linear Fabric (optional): This includes linear fabrics not included within specialized “fabric tables”.A special input interface shall be exposed upon selecting “Miscellaneous Linear Fabrics” from the “Linear Fabrics Interface”. The Miscellaneous Linear Fabrics entry interface shall expose the associated data types and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Miscellaneous Linear Fabrics Table” laid out for sedimentary descriptions such that multiple lineations can be associated with one metamorphic description.Minerals: This corresponds to the minerals that can be identified within this metamorphic rock. This data type shall be contained in a “metamorphic minerals table” such that multiple minerals can be associated with one metamorphic description. Metamorphic Minerals Table: This shall contain the following data types / fields:Metamorphic Mineral Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Metamorphic Description Id: This is the foreign key to the metamorphic description table.Mineral Type: This shall contain values for mineral families such as: “Halides”, “Sulphides”, “Carbonates”, etc. Picking this will narrow down the list of minerals presented in the next field. If this is not chosen, picking a mineral will automatically populate this field. This shall be controlled by the Minerals reference table.Mineral: The mineral name. This shall be controlled by the Minerals reference table.Mineral Texture (Crystal Habit) (optional): This shall include values such as “Anhedral”, “Fibrous”, “Columnar”, “Cubic”, “Porphyroblasts”, “Porphyroclasts”, “Augen”, etc. This shall be controlled by a lookup table.Crystal Size (optional): Measured quantity useful for large mineral crystals.Size Units: Units for crystal size measurement. This shall be controlled by a lookup table.Percentage (optional): Percentage of the containing rock that is made up by this mineral.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current metamorphic ments (optional): Free form text field allowing the editor to include any additional comments / notes.Sedimentary Mineral Grains (optional): This type of data shall describe mineral grains in a metamorphic rock which can be identified as sedimentary relicts due to rounding, sorting or some other detrital characteristic. Although this data shall be accessed from the interface for metamorphic rocks and will clearly be identified as relict grains, behind the scenes it will in fact store information within the Sedimentary Minerals Table described above. The interface itself may be customized to omit fields which are not relevant to relict sedimentary grains within metamorphic rocks.As with sedimentary rocks multiple sedimentary mineral grain types can be associated with one metamorphic description.Sedimentary Lithic Clasts (optional): This type of data shall describe relict sedimentary lithic clasts contained within a metamorphic rock. Although this data shall be accessed from the interface for metamorphic rocks and will clearly be identified as relict grains, behind the scenes it will in fact store information within the Lithic Clasts Table laid out for sedimentary rocks (see above). The interface itself may be customized to omit fields which are not relevant to relict sedimentary grains within metamorphic rocks.As with sedimentary rocks multiple lithic clast types can be associated with one metamorphic description.Fossils (optional): Fossils are included with metamorphic rocks to allow for the description of relict fossils structures which may be visible in low grade metamorphic rocks. This data type shall be contained in the same “fossils table” used for sedimentary rocks, which shall be re-used in order to keep the database design as simple as possible, however the interface used to populate fossils for metamorphic rocks shall, at the Bureau’s request, be simplified in order to capture fewer (relevant) types of data.Additionally, the input procedure shall automatically code the “Rock_Type” field with the “Metamorphic” value. This indicates that this fossil type is a relict in a metamorphic rock.As with sedimentary rocks multiple fossils can be associated with one metamorphic description.Images / Documents (optional): This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current metamorphic ments (optional): Free form text field allowing the editor to include any additional comments / notes.Igneous Description: This constitutes the rock description table designated to capture the characteristics of Igneous rocks.Igneous Description Id: Primary key of this table. Is neither exposed nor editable.Bedrock Feature Id: This is the foreign key that ties an igneous description to a bedrock feature.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Same rules as described rmal Rock Unit (optional): If a rock description and/or sample are obtained from a source outside the Bureau, this field will capture whatever name the outside source gave to the rock.Percent of Unit: This represents the percentage of the containing bedrock unit that is made up of this igneous description.Fresh Color: Color of fresh rock surface. This shall be supported by a lookup table which shall be worked out with the Bureau’s subject matter experts, possibly to reflect a standard scheme such as the Munsell Color Chart.Weathered Color (optional, may not be available in core or cuttings): Color of a weathered rock surface. As with fresh color, this shall be supported by a lookup table reflecting a standard color scheme.Fragmentation Size (optional): This indicates the average size of fragments in cases where the rock tends to break up. Values shall be > ? inch and < ? inch. These shall be available from a lookup table.Lithology Arrangement (optional, may not be apparent from cuttings): This represents the macroscopic physical arrangement of the described rock unit. Typical values might be Dominant, Interbed, Interlayer, Lens, Pods, Veins, etc.Lithology Arrangement Thickness (optional): Average width for lenses, pods, etc.Lithology Arrangement Length (optional): Average length for lenses, pods, etc.Lithology Arrangement Unit (required if width or height given): Units in which lithology arrangement width or length are given. This shall be controlled by a lookup table.Igneous Type: This represents the main types of igneous rocks; it shall include “Intrusive” or “Extrusive”. Field shall be controlled by lookup table.Igneous Compositional Type: This represents the main compositional types of igneous rocks, it shall include values such as “Ultramafic”, “Mafic”, “Intermediate” and “Felsic”. Field shall be controlled by lookup table.Intrusive Morphology (optional; can be omitted if extrusive morphology populated): This shall include values such as “Laccolith”, “Sill”, “Dike”, etc. Field shall be controlled by lookup table.Extrusive Morphology (optional; can be omitted if intrusive morphology populated): This shall include values such as “Lava Flow”, “Ash Fall”, etc. Field shall be controlled by lookup table.Planar Fabrics: This category represents a variety of morphological features which can be described by planes with measured orientations within a bedrock feature (lithologic unit).Planar fabrics shall be accessed from a common entry interface which shall expose all types of planar fabrics by means of a dropdown list.Planar fabrics shall be implemented by means of external tables, which shall be associated to sedimentary, metamorphic or igneous descriptions by means of a “rock description id” field. In order to determine which type of description there shall also be a field to capture the name of the table to which the “rock description id” corresponds. It shall be possible to associate one or more planar fabrics with a particular “rock description”. Certain types of planar fabrics, shall be restricted to only on fabric per rock description, while others may include multiple fabrics per rock description.Additionally each of these “fabric tables” shall contain a set of fields to capture the specific attributes typical of the type of planar fabric being described.The measurements describing the orientation of every individual planar feature shall be kept in the “Planar Elements Table” described in section 2.5.2.1 rather than within the “fabric tables” themselves. As described above this association shall be implemented by means of the “Geologic Feature Id” and the “Table Name” fields within the “Planar Elements Table. This arrangement allows, if necessary, to associate many planar orientation measurements with one planar fabric.The planar fabrics described in this section correspond to those commonly associated with igneous rocks. Other planar fabrics may be associated with metamorphic or sedimentary rocks. It must also be stated that this is not an exhaustive list of planar fabrics for igneous rocks. New ones could be defined at any moment, in fact all that is necessary to create a new planar fabric is to create a new table following the aforementioned guidelines.Parting (optional): Parting refers to systems of planar surfaces along which rocks tend to break. Parting, for the purposes of this system shall be accessed through the “Planar Fabrics interface”. A special input interface shall be exposed upon selecting “Parting” from the “Planar Fabrics Interface”. The parting entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Parting table” laid out for sedimentary descriptions such that multiple partings can be associated with one metamorphic description.Miscellaneous Planar Fabric (optional): This includes planar fabrics not included among the previous “fabric tables”.A special input interface shall be exposed upon selecting “Miscellaneous Planar Fabrics” from the “Planar Fabrics Interface”. The Miscellaneous Planar Fabrics entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Miscellaneous Planar Fabrics Table” laid out for sedimentary descriptions such that multiple foliations can be associated with one igneous description.Linear Fabrics: This category represents a variety of morphological features which can be described by lines with measured orientations within a bedrock feature (lithologic unit).Linear fabrics shall be accessed from a common entry interface which shall expose all types of linear fabrics by means of a dropdown list.Linear fabrics shall be implemented by means of external tables, which shall be associated to sedimentary, metamorphic or igneous descriptions by means of a “rock description id” field. In order to determine which type of description there shall also be a field to capture the name of the table to which the “rock description id” corresponds. It shall be possible to associate one or more linear fabrics with a particular “rock description”. Certain types of linear fabrics may need to be restricted to only on fabric per rock description, while others may include multiple fabrics per rock description.Additionally each of these “fabric tables” shall contain a set of fields to capture the specific attributes typical of the type of linear fabric being described.The measurements describing the orientation of every individual planar feature shall be kept in the “Linear Elements Table” described in section 2.5.2.2 rather than within the “fabric tables” themselves. As described above this association shall be implemented by means of the “Geologic Feature Id” and the “Table Name” fields within the “Linear Elements Table. This arrangement allows, if necessary, to associate many linear orientation measurements with one linear fabric.The linear fabrics described in this section correspond to those commonly associated with igneous rocks. Other linear fabrics may be associated with metamorphic or sedimentary rocks. It must also be stated that this is not an exhaustive list of linear fabrics for igneous rocks. New ones could be defined at any moment, in fact all that is necessary to create a new linear fabric is to create a new table following the aforementioned guidelines.Lineation (optional): Lineation refers to a linear fabric defined by alignment of (commonly columnar) minerals such as amphiboles. Lineation, for the purposes of this system shall be accessed through the “Linear Fabrics interface”.A special input interface shall be exposed upon selecting “Lineation” from the “Linear Fabrics Interface”. The lineation entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Lineation table” laid out for metamorphic descriptions such that multiple lineations can be associated with one igneous description.Miscellaneous Linear Fabric (optional): This includes linear fabrics not included within specialized “fabric tables”.A special input interface shall be exposed upon selecting “Miscellaneous Linear Fabrics” from the “Linear Fabrics Interface”. The Miscellaneous Linear Fabrics entry interface shall expose the associated data types and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Miscellaneous Linear Fabrics Table” laid out for sedimentary and metamorphic descriptions such that multiple lineations can be associated with one igneous description.Minerals: This corresponds to the minerals that can be identified within this igneous rock. This data type shall be contained in an “igneous minerals table” such that multiple minerals can be associated with one igneous description. Igneous Minerals Table: This shall contain the following data types / fields:Igneous Mineral Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Igneous Description Id: This is the foreign key to the igneous description table.Mineral Type: This shall contain values for mineral families such as: “Halides”, “Sulphides”, “Carbonates”, etc. Picking this will narrow down the list of minerals presented in the next field. If this is not chosen, picking a mineral will automatically populate this field. This shall be controlled by the Minerals reference table.Mineral: The mineral name. This shall be controlled by the Minerals reference table.Mineral Texture (Crystal Habit): This shall include values such as “Anhedral”, “Fibrous”, “Columnar”, “Cubic”, etc. This shall be controlled by a lookup table.Crystal Size (optional): Measured quantity useful for large mineral crystals.Size Units: Units for crystal size measurement. This shall be controlled by a lookup table.Phenocrysts: This is a Boolean (Yes/No) field, Yes meaning that this mineral appears as phenocrysts, No meaning that this mineral is part of the phaneritic groundmass.Percentage: Percentage of the containing rock that is made up by this mineral.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current igneous ments (optional): Free form text field allowing the editor to include any additional comments / notes.Aphanitic Groundmass Percentage (required for Igneous Type = “Extrusive”): If this igneous rock has no phenocrysts then this value should be 100%.Xenoliths (optional): This type of data shall describe fragments of foreign rock incorporated into an igneous rock after being broken off from the surrounding lithology during magmatic flow.A special input interface shall exist to enter xenoliths. This entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data.Although this data shall be accessed from the interface for igneous rocks and will clearly be identified as xenoliths, behind the scenes it will in fact store information within the Lithic Clasts Table laid out for sedimentary rocks (see above). The interface itself may be customized to omit fields which are not relevant to xenoliths within igneous rocks.As with sedimentary rocks multiple xenolith types can be associated with one igneous description.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current igneous ments (optional): Free form text field allowing the editor to include any additional comments / notes.InterpretationThis represents an assessment made by an investigator regarding the best possible fit of the currently described bedrock feature to a known lithostratigraphic unit (formation), igneous intrusion or metamorphic unit. Interpretations shall be accessed from the Bedrock Features Interface and shall be stored in a designated “Interpretations Table” which shall be associated to bedrock features or lithologic descriptions by means of a “Bedrock Id” foreign key assisted by a “Table Name” field defining the table containing the lithologic description to which any particular interpretation is related.Interpretation Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Bedrock Id: This is the foreign key to either of the Bedrock Geology Table, the Sedimentary Description Table, the Metamorphic Description Table, or the Igneous Description Table.Table Name: This field indicates which aforementioned tables table is associated with this interpretation record.Interpretation / Official Lithologic Unit (optional, may not be available at the time of initial data entry): This field shall be populated from a Lithologic Unit reference table accessed through a specialized interface allowing the investigator to effectively navigate through the existing lithologic units. This methodology is described in detail under section 4.2.7.2.Interpretation Confidence Rating: This concept shall represent the level of confidence of the investigator when making the aforementioned interpretation. This field shall be mandatory only when an interpretation is made. This field shall be controlled by a lookup table containing the following values:Not SureFairly SureSureConfidentVery ConfidentInterpretation Comments (optional): This shall be a free form text field allowing the editor to include any additional comments / notes that may be deemed necessary to justify or clarify the choice of lithologic ments (optional): This shall be a free form text field allowing the editor to include any additional comments / notes that may be deemed necessary to further describe the bedrock feature or to alert other users of any special situation concerning it.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current bedrock feature.Coal OccurrencesThis category of data shall store individual descriptions of coal occurrences as they appear at particular locations along drill holes, measured sections or within outcrops. To avoid confusion, these descriptions are not meant to be generalized descriptions of entire coal seams as they appear over their geographic extents (that kind of summarized information belongs in reference literature), rather specific observations at specific locations.Many of these observations currently exist on paper records that the Bureau keeps about named and un-named coal seams within the State of Pennsylvania. Regarding these it is important to note that a significant percentage was obtained from coal mining companies and may be tagged as confidential.There exists also a fair amount of information, obtainable from the substantial inventory of drill cores warehoused by the Bureau that is yet to be recorded.Coal occurrences shall be stored in a dedicated Coal Occurrence table which shall have fields to store the following types of data: Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Coal Seam Name: This shall capture the seam name as reported in the source documentation. Note that this is not necessarily the correct name. The investigator shall have the opportunity to enter the coal seam name that she/he considers correct further along the interface. (To do this an alias table with correct names and typical aliases used in different regions including border states shall be employed). This field shall be controlled by a lookup table.Coal Type: This shall capture the general coal type. It shall include values such as “Bituminous”, “Lignite”, “Anthracite”, etc. This field shall be controlled by a lookup table.Coal Samples: The complete characterization of the coal present in this coal occurrence (including analysis results) will be included in a coal sample table. This way multiple samples can be associated with one coal occurrence. This is particularly important as coal seams are often subdivided into “benches” which are commonly described individually. Coal samples are described in detail within the Samples section below.Washability Test (optional): This test can be performed on the portion of the coal seam that will be mined rather than on individual benches, thus it can be related directly to the seam rather than to individual samples in the coal samples table. The results of the washability test are kept in a “washability test table” such that multiple test results can be associated to a single coal seam.Washability tests can also be performed on individual samples (benches) rather than on the entire seam. To accommodate both scenarios the Washability Test Table has been outfitted with a foreign key to the coal sample table as well as a foreign key to the coal occurrence table.Washability Test Table: This table shall have the following fields:Test Id: Primary key of this table. Is not exposed nor editableCreator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Coal Id: This is the foreign key to the coal occurrence table.Coal Sample Id: This is the foreign key to the coal sample table for cases where the washability has been performed on an individual bench sample rather than on the entire seam.Is Composite (optional): Boolean ( Y / N ) By default this is set no “N”. If this sample is a composite, either because multiple samples have been analyzed together, or because the analysis results of multiple samples have been combined into a weighted average this field is set to “Y” (see Samples below for an explanation of how compositing is implemented).Composite Test ID (optional): If the information / analysis results associated with this sample have been merged with other samples to create a composite “pseudo sample” (one that has its “Is Composite” field set to “Y”) this field shall reference the test id of this resulting composite pseudo sample (see Samples below for an explanation of how compositing is implemented).Percentage Fines: This is the percentage of the crushed coal sample that will not be included in the washability test. By default 100 minus this quantity is the percentage that is included in the test.Gravity Fluid: This is the type / specific gravity of the gravity fluid employed in this test. This shall be provided by a lookup table.Percentage Float: This is the percentage of the tested coal fraction that floats in this gravity fluid.Percentage Sink: This is the percentage of the tested coal fraction that sinks in this gravity fluid.Coal Seam Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the coal occurrences table. Note that thickness can be 0 for coal seams.System shall be able to calculate ments (optional): This shall be a free form text field allowing the editor to include any additional comments / notes that may be deemed necessary to further describe the coal occurrence or to alert other users of any special situation concerning it.Associated Documents: photographs, photo descriptions and notes (see discussion under Documents and Images in Section 4.1)Surficial GeologyThis category is meant to describe all of the surficial sediments that have not yet undergone lithification to become sedimentary rocks. These shall be stored in a surficial geology table which shall have fields to store the following types of dataNotes: All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Deposit Id: Primary key of this table Creator ID, Creation Date, Last Editor ID, Last Edit Date: Same rules as described previously.Deposit Type: This field shall capture the general type of surficial deposit. Typical values shall be “Glacial”, “Alluvium”, “Colluviums”, “Man-made fill”, etc. This field shall be controlled by a lookup rmal Sediment Name (optional): If a description is obtained from a source outside the Bureau, this field will capture whatever name the outside source gave to the sediment.Fresh Color: Color of fresh deposit surface. This shall be supported by a lookup table which shall be worked out with the Bureau’s subject matter experts, possibly to reflect a standard scheme such as the Munsell Color Chart.Weathered Color (optional): Color of a weathered deposit surface. As with fresh color, this shall be supported by a lookup table reflecting a standard color scheme.Moisture (required if fresh or weathered color is given): This shall capture values such as: “Dry”, “Moist”, “Wet”. Field shall be controlled by lookup table.Deposit Arrangement (optional): This represents the macroscopic physical arrangement of the described deposit. Typical values might be Lens, Pod, Layered, Massive, etc.Deposit Arrangement Width (optional): Average width for lenses, pods, etc.Deposit Arrangement Length (optional): Average length for lenses, pods, etc.Deposit Arrangement Unit (required if width or height given): Units in which deposit arrangement width or length are given. This shall be controlled by a lookup table.Porosity (Optional): This value shall be given in percent (of total volume).Porosity Type (required if Porosity given): This shall contain values such as “Moldic”, “Vuggy”, “Intergranular”, “Fracture”. Field shall be controlled by a lookup table.Permeability (optional):Permeability Units (required if permeability given):Compactness (optional): This shall capture the following values: “Hard”, “Moderate”, “Soft”. Field shall be controlled by lookup table.Cohesiveness (optional): This shall capture the following values: “Cohesive”, “Non Cohesive”. Field shall be controlled by lookup table.Plasticity (optional): This shall capture the following values: “Non Plastic”, “Low Plasticity”, and “High Plasticity”. Field shall be controlled by lookup ographic Feature (optional): This shall capture the values such as: “Terrace”, “Flood Plain”, etc. Field shall be controlled by lookup table.Sedimentary Description: As mentioned previously, surficial sediments as well as sedimentary rocks shall make use of the same sedimentary description tables. This is accomplished by outfitting the main sedimentary rock description table with a foreign key to the surficial geology features table (in addition to the foreign key pointing to the bedrock geology features).The rationale for the re-use of the sedimentary description tables is based on the need to reduce the number of specialized tables, where possible, in order to simplify the implementation and maintenance of the database.However, as pointed out by the Bureau’s subject matter expert on surficial sediments, not all categories of information captured for sedimentary rocks are necessary for surficial sediments. Therefore the interfaces to enter sedimentary description information for surficial geology features shall, at the option of the Bureau, only expose a subset (relevant) of the data types supported by the underlying database structure. For example, it is already known that partings shall not be necessary for surficial features, thus they shall be omitted from the corresponding ments (optional): This shall be a free form text field allowing the editor to include any additional comments / notes that may be deemed necessary to further describe the bedrock feature or to alert other users of any special situation concerning it.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current bedrock feature.FaultsFaults (optional): Faults are kept in a separate “Faults Table”. They are related to bedrock geology features by means of a join table, which allows a bedrock feature to include multiple faults while allowing a fault to intersect multiple bedrock features.A special input interface shall exist to enter faults. This fault entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data. The new fault will automatically be associated to this bedrock feature by means of the appropriate inserts and updates into the Faults table and Bedrock-Fault Join Table. Optionally this interface shall present a list of all of the faults existing at this station and allow the user to choose an existing fault. The chosen fault will then be associated to this bedrock feature by means of the appropriate entries into the Bedrock-Fault Join Table.Bedrock-Fault Join Table: This table shall have the following fields:Bedrock Id: This is the foreign key to either of the Bedrock Geology Table, the Sedimentary Description Table, the Metamorphic Description Table, or the Igneous Description Table.Table Name: This field indicates which aforementioned tables table is associated with this interpretation record.Fault ID: This is a foreign key pointing to the Fault ID field (primary key) of the faults table.Faults Table: This table shall have the following fields:Fault Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Fault Plane Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the Faults table. Direction of Motion: This field shall include the basic descriptors that qualitatively characterize a fault in terms of the relative motions of its hanging wall and foot wall. This shall contain any of the following terms: “normal”, “reverse”, “right lateral”, “right lateral normal”, “right lateral reverse”, “left lateral”, “left lateral normal” and “left lateral reverse”. This field shall be controlled by a lookup table.Strike Slip: Length value representing the motion along strike of the hanging wall relative to the foot wall. This shall be an absolute value. The strike orientation shall be assumed to be that defined for the fault plane orientation and the relative direction of motion is implicitly defined by the “Direction of Motion” field.Dip Slip Length value representing the motion along dip strike of the hanging wall relative to the foot wall. This shall be an absolute value. The dip orientation shall be assumed to be that defined for the fault plane orientation and the relative direction of motion is implicitly defined by the “Direction of Motion” field.Slip Units: Length units used for Strike Slip and Dip Slip measurements.Linear Fabrics: This category represents a variety of morphological features which can be described by lines with measured orientations contained within a fault plane.Linear fabrics shall be accessed from a common entry interface which shall expose all types of linear fabrics by means of a dropdown list.Linear fabrics shall be implemented by means of external tables, associated to faults in a manner similar to the linear fabrics that describe sedimentary, metamorphic or igneous descriptions. It shall be possible to associate one or more linear fabrics with a particular fault. As in the previous cases, each “fabric table” shall contain a set of fields to capture the specific attributes typical of the type of linear fabric being described.The measurements describing the orientation of every individual planar feature shall be kept in the “Linear Elements Table” described in section 2.5.2.2 rather than within the “fabric tables” themselves. As described above this association shall be implemented by means of the “Geologic Feature Id” and the “Table Name” fields within the “Linear Elements Table. This arrangement allows, if necessary, to associate many linear orientation measurements with one linear fabric.The linear fabrics described in this section correspond to those commonly associated with faults such as slickenside striations. This is not an exhaustive list of linear fabrics for faults. New ones could be defined at any moment, in fact all that is necessary to create a new linear fabric is to create a new table following the aforementioned guidelines.Miscellaneous Linear Fabric (optional): This includes linear fabrics not included within specialized “fabric tables”.A special input interface shall be exposed upon selecting “Miscellaneous Linear Fabrics” from the “Linear Fabrics Interface”. The Miscellaneous Linear Fabrics entry interface shall expose the associated data types and allow the user to enter new -or modify existing data.This data type shall be contained in the same “Miscellaneous Linear Fabrics Table” laid out for sedimentary, metamorphic and igneous descriptions such that multiple lineations can be associated with one fault.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current planar ments (optional): Free form text field allowing the editor to include any additional comments / notes.FoldsFolds are kept in a separate “Folds Table”. They are related to bedrock geology features by means of a join table, which allows a bedrock feature to include multiple folds while allowing a fold to involve multiple bedrock features.A special input interface shall exist to enter folds. This fold entry interface shall expose the data types listed below and allow the user to enter new -or modify existing data. The new fold will automatically be associated to this bedrock feature by means of the appropriate inserts and updates into the Folds table and Bedrock-Fold Join Table. Optionally this interface shall present a list of all of the folds existing at this station and allow the user to choose an existing fold. The chosen fold will then be associated to this bedrock feature by means of the appropriate entries into the Bedrock-Fold Join Table.Bedrock-Fold Join Table: This table shall have the following fields:Bedrock Id: This is the foreign key to either of the Bedrock Geology Table, the Sedimentary Description Table, the Metamorphic Description Table, or the Igneous Description Table.Table Name: This field indicates which aforementioned tables table is associated with this interpretation record.Fold ID: This is a foreign key pointing to the Fold ID field (primary key) of the folds table.Folds Table: This table shall have the following fields:Fold Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Axial Plane Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the Folds table. Fold Axis Orientation (optional): This shall be stored within the Linear Elements Table (see section 2.5.2.2) which shall include a foreign key pointing to this record in the Folds table.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current planar ments (optional): Free form text field allowing the editor to include any additional comments / notes.Associated LithologiesLithologic ContactsThis geologic feature type shall include all “non fault” contacts. Faults are not included as they are already handled in their own section. It shall be possible to define contacts between any pair of bedrock or surficial geology features (including between one bedrock and one surficial) located at one station.Notes: All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Lithologic Contacts shall be stored in a “Lithologic Contacts” table which shall have the following fields:Geologic Feature Type 1: This field can contain values “Bedrock” or “Surficial”. It alerts the system as to which table to search for the first geologic feature participating in this contact. This shall be controlled by a lookup table.Geologic Feature Id 1: This is a foreign key to the table (Bedrock or Surficial, depending on the choice in the previous field) containing the first geologic feature participating in this contact.Geologic Feature Type 2: This field can contain values “Bedrock” or “Surficial”. It alerts the system as to which table to search for the second geologic feature participating in this contact. This shall be controlled by a lookup table.Geologic Feature Id 1: This is a foreign key to the table (Bedrock or Surficial, depending on the choice in the previous field) containing the second geologic feature participating in this contact.Contact Type: This field shall contain values such as “Sharp”, “Gradational”, “Unconformity”, etc. This field shall be controlled by a lookup table.Contact Plane Orientation (optional): This shall be stored within the Planar Elements Table (see section 2.5.2.1) which shall include a foreign key pointing to this record in the Contacts table. Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current planar ments (optional): Free form text field allowing the editor to include any additional comments / notes.Note: Since there is no method proposed governing which geologic feature becomes feature 1 and which becomes feature 2, any query that searches for contacts based on the content of the involved geologic features will have to be designed to search both features simultaneously.The interface to enter or edit contacts shall be available from the Stations interface.LandslidesLandslides are not a “once-n-done” event. The Bureau monitors and tracks recent landslide events (about 100 on-going monitoring locations), as well as historic and recurrent landslide events. This means that a single landslide location may lie dormant for several to hundreds of years between episodes. Within the proposed RockIT database, landslide events may be tracked any number of ways. A single station can serve the location of many landslide event records, each recording the date and conditions of the unique landslide events that occurred at the location. Or a landslide project may have several stations and associated landslide events within it to track the nature of activity in that location, including samples that have been collected at various locations across the landslide. This data tracking flexibility is inherent to the RockIT database environment and the supporting applications will allow users to search the database and find landslide events and their associated information and related samples, regardless of the approach the individual researcher uses to organize content.The Project will serve as the overall unit to group landslide events and any associated samples together.Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.The following fields encompass the details required to describe landslide events:Client Name (from contact person pick list)Fault distanceMine distanceSurficial geology type: selected from pick list (surficial geology lookup table referencing colluviums, alluvium, glacial, man-made fill).Date Landslide reportedDate investigated and confirmed, including person from Bureau who made visitProperty Owner nameBedrock exposure: If bedrock is exposed the investigator shall have an opportunity to capture a corresponding petrologic description. The bedrock user interface shall be available for this purpose. The following items show the fields most likely to be of use when characterizing bedrock for the purposes of a landside description, however more detailed sedimentary, metamorphic or igneous descriptions can also be filled out at the investigator’s option.Rock Type: This shall be supported by a lookup table of rock types organized in terms of sedimentary, metamorphic and igneous concepts and possibly broken down further in terms of the most important categories beneath them. The actual organization of this shall be worked out with the participation of the Bureau’s subject matter rmal Rock Unit (optional): If a rock description and/or sample are obtained from a source outside the Bureau, this field will capture whatever name the outside source gave to the rock.Rock Material Source: This field captures information about the type of “sample” that was used to produce the rock description; typically this would be things such as: Outcrop, Float, Cuttings, Core, etc. This shall be supported by a lookup table.Fresh Color: Color of fresh rock surface. This shall be supported by a lookup table which shall be worked out with the Bureau’s subject matter experts, possibly to reflect a standard scheme such as the Munsell Color Chart.Weathered Color (optional, may not be available in core or cuttings): Color of a weathered rock surface. As with fresh color, this shall be supported by a lookup table reflecting a standard color scheme.Lithology Arrangement (optional, may not be apparent from cuttings): This represents the macroscopic physical arrangement of the described rock unit. Typical values might be Lens, Pods, Layered, Massive, Veins, etc.Lithology Arrangement Width (optional): Average width for lenses, pods, etc.Lithology Arrangement Length (optional): Average length for lenses, pods, etc.Lithology Arrangement Unit (required if width or height given): Units in which lithology arrangement width or length are given. This shall be controlled by a lookup table.Porosity (Optional): This value shall be given in percent (of total volume).Porosity Type (required if Porosity given): This shall contain values such as “Moldic”, “Vuggy”, “Intergranular”, “Fracture”. Field shall be controlled by a lookup table.Permeability (optional):Permeability Units (required if permeability given):Fragmentation Size (optional): This indicates the average size of fragments in cases where the rock tends to break up. Values shall be > ? inch and < ? inch. These shall be available from a lookup ments (optional): This shall be a free form text field allowing the editor to include any additional comments / notes that may be deemed necessary to further describe the bedrock feature or to alert other users of any special situation concerning it.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current bedrock ology setting: comments entered by investigatorDate Repaired, plus flag field to note if repairs have already been completedDate of actual landslide occurrenceStratigraphic unit: pick listLandslide Description to include the following fields: Length, Width, Percent slope, Slope azimuthAssociated Costs: Known dollar values, cost descriptions, uploaded documentation (e.g. newspaper articles verifying costs and source of data)Known Cause: pick list (construction, rainfall, etc.)Type of Landslide: Debris slide, Debris flow, Rock fall, SlumpRecurrence: Y/NRelative age of Landslide: active, recent, oldAssociated Documents: photographs, photo descriptions and notes, letters, newspaper reports (see discussion under Documents and Images in Section 4.1)Landslide status: this is referenced in first generation system requirements, although it has not been updated through these system requirements. Review this with client upon design.Analysis from samples collected at site: attributes reported in the geochemical analysis section, see Section 4.2.10.Karst FeaturesThe karst features data is a gradually expanding collection of locations and descriptions that, like landslides, helps the Bureau monitor and track areas of recent geologic activity, with additional research being done to describe “vulnerable” or “at risk” areas. Karst related sinkhole events have a 50% chance of exposing underlying bedrock. The Karst event table, like the landslide event table, will reference as foreign key, the bedrock feature record where the exposed features have been described, see item 17 in the list.Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Required Fields :PAGS Case File NumberRecord Source (optional): This shall include values such as “Aerial Photography” (note that scanned images can be included with documentation, below), “Citizen Report”, “Field Visit”, etc.Contact Information of person who reported karst featureDate karst feature reportedDate visited and confirmed, including person from Bureau who made visitDistance to FaultDistance to MineDistance Units (required if Distance to Mine or Distance to Fault given)Karst Feature Type: This shall include the following values: Cave Opening, Sinkhole, Closed Depression.Karst Feature Area (optional)Area Units (Required if Karst Feature Area given)Karst Feature Depth (optional)Depth Units (Required if karst feature depth given)Sinkhole description (optional): to include size, shape, throat presence, and orientation.Surficial geology type: selected from pick list (surficial geology lookup table referencing colluviums, alluvium, glacial, man-made fill).Stratigraphic Unit – selected from pick listBedrock exposure: If bedrock is exposed the investigator shall have an opportunity to capture a corresponding petrologic description. The bedrock user interface shall be available for this purpose. The following items show the fields most likely to be of use when characterizing bedrock for the purposes of a sinkhole description, however more detailed sedimentary, metamorphic or igneous descriptions can also be filled out at the investigator’s option.Rock Type: This shall be supported by a lookup table of rock types organized in terms of sedimentary, metamorphic and igneous concepts and possibly broken down further in terms of the most important categories beneath them. The actual organization of this shall be worked out with the participation of the Bureau’s subject matter rmal Rock Unit (optional): If a rock description and/or sample are obtained from a source outside the Bureau, this field will capture whatever name the outside source gave to the rock.Rock Material Source: This field captures information about the type of “sample” that was used to produce the rock description; typically this would be things such as: Outcrop, Float, Cuttings, Core, etc. This shall be supported by a lookup table.Fresh Color: Color of fresh rock surface. This shall be supported by a lookup table which shall be worked out with the Bureau’s subject matter experts, possibly to reflect a standard scheme such as the Munsell Color Chart.Weathered Color (optional, may not be available in core or cuttings): Color of a weathered rock surface. As with fresh color, this shall be supported by a lookup table reflecting a standard color scheme.Lithology Arrangement (optional, may not be apparent from cuttings): This represents the macroscopic physical arrangement of the described rock unit. Typical values might be Lens, Pods, Layered, Massive, Veins, etc.Lithology Arrangement Width (optional): Average width for lenses, pods, etc.Lithology Arrangement Length (optional): Average length for lenses, pods, etc.Lithology Arrangement Unit (required if width or height given): Units in which lithology arrangement width or length are given. This shall be controlled by a lookup table.Porosity (Optional): This value shall be given in percent (of total volume).Porosity Type (required if Porosity given): This shall contain values such as “Moldic”, “Vuggy”, “Intergranular”, “Fracture”. Field shall be controlled by a lookup table.Permeability (optional):Permeability Units (required if permeability given):Fragmentation Size (optional): This indicates the average size of fragments in cases where the rock tends to break up. Values shall be > ? inch and < ? inch. These shall be available from a lookup ments (optional): This shall be a free form text field allowing the editor to include any additional comments / notes that may be deemed necessary to further describe the bedrock feature or to alert other users of any special situation concerning it.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current bedrock feature.Structures / Utilities affected: able to select multiple itemsDate Repaired, plus flag field to note if repairs have already been completedRepair Method: Crushed Stone, Large Rock, Concrete, Unknown, Not applicableSinkhole Status: Open Hole, Partial Repaired, UnknownComments (optional): This shall be a free form text field allowing the editor to include any additional comments / notes that may be deemed necessary to further describe the karst feature or to alert other users of any special situation concerning it.Associated Documents: photographs, photo descriptions and notes (see discussion under Documents and Images in Section 4.1)Interpretations / Official Stratigraphic UnitsData StructuresRockIT shall maintain a list of officially recognized stratigraphic units which shall be available to index new data being entered into the system or to search and browse through existing data.The data structure envisioned for storing stratigraphic units shall be designed to accommodate its multi-level hierarchical “tree-like” structure in which units typically form part of larger super-unit, while themselves being divided into smaller sub-units. In order to accomplish this, a simple “recursive” table structure is proposed where every unit is referenced to its parent unit. In this setup a unit’s “parent” and / or “child” lineage can be obtained by means of a recursive procedure, capable of looping through the database structure until all “parents” and “parents’ parents” as well as all “children’s children” have been found. The search interface shown below allow a user to manually step “up” or “down” through such a lineage if desired.In addition the age of every unit shall be defined by named geologic periods as they appear on Pennsylvania’s geologic age correlation chart. This chart will reside in a special table which shall include every named geologic period for Pennsylvania, together with its beginning (maximum age) and ending (minimum age) dates, given in terms of millions of years.The Geologic Age Correlation Table shall have the following fields:Period ID: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Period Name: Shall include names such as “Triassic”, “Jurassic”, “Neogene”, etc.Maximum Age: Beginning date of period in millions of years before present.Minimum Age: Ending date of period in millions of years before present.Obsolete (Y / N): Default to “N”Stratigraphic Unit Reference Table: this table shall include the following fields:Unit Id: Primary key of this table. Is neither exposed nor editable.Creator ID, Creation Date, Last Editor ID, Last Edit Date: Login Id populated automatically, none visible, none editable.Period ID: This is a foreign key pointing to the Geologic Age Correlation Table, thus it determines the age of this stratigraphic unit.Unit Name: Name of the formation, igneous body or metamorphic unit.Unit Type: This shall include values such as “sedimentary”, “metamorphosed sedimentary”, “igneous intrusive”, etc. This shall be controlled by a lookup table.Description: Free form text field allowing the capture of the most important aspects defining this unit.Parent Unit Id (optional): This field contains the “Unit ID” of the stratigraphic super-unit that contains this unit. This field implements the recursive structure needed to find the “parent” and “child” lineage of this unit. To find this unit’s “parent” unit all that is necessary is to search for the stratigraphic unit whose unit id is equal to this parent unit id. To search for the “grandparent” one would look at the parent unit id of the parent. To search for “child” units, all that is necessary is to search for stratigraphic units whose parent unit id is equal to the unit id of this unit. Complete lineages within this structure would be similarly obtained by means of a recursive procedure capable of traversing this “tree-like” structure.Sequential Order: If a unit has a parent it may have siblings (other units with the same parent unit). This shall be an integer value that captures the sequential order of this unit with respect to its siblings.Obsolete (Y / N): Default to “N”Searching the DataDue to the multitude of stratigraphic units a special interface shall be needed to aid in the search / navigation of existing units. This interface shall allow searching in terms of named geologic period, ad-hoc age range, unit type and unit name (see diagram below):Figure 42 Interface to Search by Lithologic Unit and AgeThe main functional feature of this interface is a “canvass” showing the list of available stratigraphic units. Stratigraphic units are indented to indicate the level of nesting in terms of parent and grandparent units.To the left of every stratigraphic unit there are two icons labeled with a “+” Clicking on the first one (at any time) will cause the parent of the corresponding unit to be displayed above it, out-dented one level. This will turn the “+” into a “-“. Clicking on the second icon will display any “child” units of the corresponding unit to be displayed below it, indented one level. This will turn the “+” of that icon into a “-“. Clicking on any “-“ will hide the corresponding parent or child units and turn the icon back into a “+”.Entering Age limits or un-checking stratigraphic unit types in the provided interface widgets shall narrow down the list of formations shown in the canvass.Named Geologic Periods shall be available from a drop down list organized in approximate chronological order. Choosing one of these shall also narrow down the list of units displayed on the canvass.Entering a stratigraphic unit name in the provided text input field shall also narrow down the list of units displayed on the canvass. A special “type ahead” functionality shall be implemented whereby as the user types only unit names starting with the letters that the user has already typed (and satisfy the previously chose age and type, if any) are displayed on the canvass. The user shall be able to stop typing at any time and make use of the units displayed on the canvass.It shall be possible to sort the list of stratigraphic units displayed in terms of age / hierarchy or alphabetically by name. Sorting by name is important because multiple units often share the same name. This would place units with similar names side by side allowing the user to make the correct choice. Hovering the cursor over any displayed unit shall cause the presentation of a tool tip containing additional information for that unit such as age, type, etc.If this operation constitutes the indexing of a feature, then clicking on any of the displayed units shall populate it into the Interpretation / Stratigraphic Unit field for that feature.If this operation constitutes a search for geologic features, then an additional “submit” button shall be exposed. Clicking that button shall return all of the features that have been interpreted as corresponding to the selected lithologic units and / or age interval.Data MaintenanceMaintenance of this data shall be carried out by a special interface / procedure available only to the system administrator. Essentially this shall expose the information in the Geologic Age Correlation Table and the Stratigraphic Unit Reference Table and allow the editing as well as inserting new data.New Data: Incorporating new Named Geologic Periods and Stratigraphic Units shall be accomplished by simple insert operations.Deleting Data: It shall not be possible to delete Named Geologic Periods or Stratigraphic Units, as that may break referential integrity to other records within the database. Instead, Named Geologic Periods or Stratigraphic Units may be “obsoleted”. Obsolete values are valid for units that reference them but may to be assigned to new units.Changing Data: To avoid changing information associated with existing geologic features, a change of Named Geologic Periods or Stratigraphic Units is essentially equivalent to “obsoleting” the existing values and entering new superseding information.Any change in a Named Geologic Period or Stratigraphic Unit may result units whose age no longer agrees with the age of its “parents” or “children” or overlaps the age of its “siblings”. A special tool shall be developed to search through the unit hierarchy and highlight such age mismatches thus making it possible to take corrective actions.SamplesPrincipal Characteristics: Samples, like other types of geologic features can be referenced directly to space by means of stations.Samples shall also be structured to reference other geologic features (or events, ex: water sample from sinkhole) in order to provide supporting quantitative and descriptive information.If a sample is associated with another geologic feature its direct spatial reference becomes optional as it can always be assumed to share a location in space with the geologic feature that it supplements.In addition to an automatic primary key all samples shall have a user defined number or code which shall be used as a cross reference to any cataloguing systems that the Bureau may have for samples.Samples are the main reference entities for analysis records. Wherever possible analysis records should be tied to specific samples rather than to more general geologic features. This provides the basis for the repeatability of such tests.Samples may be composited, that is many samples may be combined in some proportion to yield a resulting “pseudo sample”. This compositing could occur in two ways:Physically, meaning that samples are combined before being analyzed (in order to reduce costs). In this case all that is necessary is to indicate that a particular sample, as well as its analysis results, are composites. This is done by setting a flag (the “Is Composite” Boolean field) to indicate this fact.Mathematically, meaning that separate analysis results are combined into a weighted average (sometimes averages are more useful than individual measurements). In this case “source” sample data would exist throughout the corresponding samples table. In order to track which original samples were combined into which composite sample, each “source” sample shall include a reference to the composite sample in which it participates. This reference shall consist of the primary key (Sample ID) of the composite sample stored in a “Composite Sample ID” (foreign key) field.A special interface shall exist to aid in the management of composited samples. This shall allow the input of the composite sample id into the participating source or component samples. This shall also aid in the population of the attributes of the composite sample by listing all of the component samples and offering various methods for populating the composite based on the components. These methods shall include:Leave blank.Enter a value manually.Pick a value from any one component sample.Calculate a straight average from all component samples.Pick any numeric field and use it as a weighting factor in the calculation of a weighted average. Typically this would be sample weight or sample thickness (as recorded in the spatial reference information)The RockIT database and user applications have so far been defined with the following sample types:CoalRockCBM WellWaterThis list is by no means exhaustive, and it is expected to grow as time goes on. Fortunately the open architecture of RockIT makes it easy to add new types of samples.NOTE: These system requirements did not include a formal review of “water” data, per say, although many of the database items that are included, such as landslides and sinkholes, often require water sampling. Given its importance as a sample type for geologic research and investigation, and the well-monitoring role the Bureau currently has, water samples are an inherent requirement for the RockIT database. Additionally, water samples are a key component for carbon sequestration work that is looming on the horizon in Pennsylvania.Coal SamplesCoal samples are meant to be related directly to coal occurrences and are stored in a separate “Coal Samples” in such a way that multiple coal samples can be associated to one “coal occurrence”. Coal samples could, however, also be related directly to space by means of a station. In some cases, as when a sample describes a bench within a coal seam, a coal sample should have its own spatial reference in order to provide the bench’s relative stratigraphic position with respect to the entire seam.Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Sample ID: System-generated, readable, not available for editIs Composite (optional): Boolean ( Y / N ) By default this is set no “N”. If this sample is a composite, either because multiple samples have been analyzed together, or because the analysis results of multiple samples have been combined into a weighted average this field is set to “Y”.Composite Sample ID (optional): If the information / analysis results associated with this sample have been merged with other samples to create a composite “pseudo sample” (one that has its “Is Composite” field set to “Y”) this field shall reference the sample id of this resulting composite pseudo sample.Coal Id: This is the foreign key relating this coal sample to a coal occurrence.Sample Type (optional): Values for this field are “Full Seam”, “Bench”, “Partial Bench”. This shall be controlled by lookup table.Bench Id (required if sample type = “Bench” or “Partial Bench”): Bureau assigned id for bench.Collection Date (optional): Date on which the sample was collected.Sample weight (optional)Weight unitName/Date sample was borrowed from inventory (optional)Banding (optional): This shall include values such as: “Banded”, “Non Banded”, “Mixed”. This field shall be controlled by a lookup table.Percentage of Vitrain Banding (optional): This shall assume the following values: “> 60%”, “30-60%”, “5-30%”, “0-15%”. This field shall be controlled by a lookup table.Thickness of Vitrain Band (optional): This shall assume the following values: “5 mm”, ”2-5 mm”, “1/2-2 mm”. This shall be controlled by a lookup table.Clarain-Durain Luster (optional): This field can have the following values: “Bright”, “Moderately Bright”, “Midlustrous”, “Moderately Dull”, “Dull”. It shall be controlled by a lookup table.Washability Test Table (input optional): The Washability Test Table includes a foreign key that references this coal sample table, as described above under coal occurrences.Related analysis records: Related analysis records for this sample are kept in the system wide analysis results table. This table specifies the resulting values for each individual analyte or measured quantity. This table is supported by three other tables which store detection limits, general lab information and analytical standards. Results are referenced back to this sample by means of its sample id contained in the analytical results table.Images / Documents (optional): This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current coal ments (optional): Free form text field allowing the editor to include any additional comments / notes.CBM SamplesCoal Bed Methane samples should be related directly to coal occurrences. Usually, but not always, there is a one to one relationship between a CBM sample and a Coal Occurrence. In any case CBM samples should always have an explicit reference to space by means of a direct relationship to stations. CBM samples exist in their own table and may or may not include an explicit relationship to a coal seam (occurrence). The CBM Samples table shall include the following fields:Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Sample ID: System-generated, readable, not available for edit.CBM ID: User defined sample id / name.Is Composite (optional): Boolean ( Y / N ) By default this is set no “N”. If this sample is a composite, either because multiple samples have been analyzed together, or because the analysis results of multiple samples have been combined into a weighted average this field is set to “Y”.Composite Sample ID (optional): If the information / analysis results associated with this sample have been merged with other samples to create a composite “pseudo sample” (one that has its “Is Composite” field set to “Y”) this field shall reference the sample id of this resulting composite pseudo sample.Coal Id: This is the foreign key relating this coal sample to a coal occurrence.Collection Date (optional): Date on which the sample was collected.Collection Method (optional):Sample Volume / Weight (optional):Extraction Duration (optional): Amount of time it took to obtain sample.Duration Units (required if extraction duration is entered): Time units in which extraction duration is given.Related analysis records: Related analysis records for this sample are kept in the system wide analysis results table. This table specifies the resulting values for each individual analyte or measured quantity. This table is supported by three other tables which store detection limits, general lab information and analytical standards. Results are referenced back to this sample by means of its sample id contained in the analytical results table.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current coal sample or Gas extraction ments (optional): Free form text field allowing the editor to include any additional comments / notes.Rock SamplesRock samples can be related to any geologic feature or event. As such they can either be referenced to space directly by means of a station, or can inherit their spatial location from the geologic feature or event that they reference. Rock samples shall be hosted with a “Rock Samples” table in order to allow the association of multiple samples to a single geologic feature or event.The Rock Samples table shall include the following fields:Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Sample ID – system-generated, readable, not available for editIs Composite (optional): Boolean ( Y / N ) By default this is set no “N”. If this sample is a composite, either because multiple samples have been analyzed together, or because the analysis results of multiple samples have been combined into a weighted average this field is set to “Y”.Composite Sample ID (optional): If the information / analysis results associated with this sample have been merged with other samples to create a composite “pseudo sample” (one that has its “Is Composite” field set to “Y”) this field shall reference the sample id of this resulting composite pseudo sample.Feature-Event ID (optional): Foreign key to event / feature table. Table Name: Since there are multiple geologic feature and event tables, this field is necessary to determine to which table the Feature-Event ID relatesSample Type (optional): pick list (see Samples look up table)Collection Date (optional):Sample Width / unit (optional)Sample weight / mass / unit (optional) Related analysis records: Related analysis records for this sample are kept in the system wide analysis results table. This table specifies the resulting values for each individual analyte or measured quantity. This table is supported by three other tables which store detection limits, general lab information and analytical standards. Results are referenced back to this sample by means of its sample id contained in the analytical results table.GeochemistryIsotopic AgeUranium analysisImages / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current rock sample.PhotographsThin SectionXRD analysis resultsSEM analysis resultsComments (optional): Free form text field allowing the editor to include any additional comments / notes.Water SamplesWater samples can be related to any geologic feature or event. As such they can either be referenced to space directly by means of a station, or can inherit their spatial location from the geologic feature or event that they reference. Water samples shall be hosted within a “Water Samples” table which shall allow the association of multiple samples to a single geologic feature or event.The Water Samples table shall include the following fields:Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership, project tracking and spatial reference fields (stations, longitudinal positions and thicknesses) are included in this table as described in section 2.5.1 so they shall not be repeated here.Sample ID – system-generated, readable, not available for edit.Is Composite (optional): Boolean ( Y / N ) By default this is set no “N”. If this sample is a composite, either because multiple samples have been analyzed together, or because the analysis results of multiple samples have been combined into a weighted average this field is set to “Y”.Composite Sample ID (optional): If the information / analysis results associated with this sample have been merged with other samples to create a composite “pseudo sample” (one that has its “Is Composite” field set to “Y”) this field shall reference the sample id of this resulting composite pseudo sample.Feature-Event ID (optional): Foreign key to event / feature table. Table Name: Since there are multiple geologic feature and event tables, this field is necessary to determine to which table the Feature-Event ID relatesSource Type: pick list (Lake, river, stream, road cut, sinkhole, landslide, etc.)Collection Date (optional):Collection Method (optional):Sample Volume (optional):Volume UnitRelated analysis records: Related analysis records for this sample are kept in the system wide analysis results table. This table specifies the resulting values for each individual analyte or measured quantity. This table is supported by three other tables which store detection limits, general lab information and analytical standards. Results are referenced back to this sample by means of its sample id contained in the analytical results table.Images / Documents: This is supported by the images / documents database structure described under section 4.1 above. This entry corresponds to a component of this interface allowing you to associate an image or a document with the current water sample or water sampling ments (optional): Free form text field allowing the editor to include any additional comments / notes.Analysis ResultsPrincipal Characteristics: This database structure encompass lab tests that include geochemical analysis, coal quality analysis, CBM well testing, isotopic age testing, uranium analysis, arsenic testing for water samples, and others. Some of these tests are managed independently by the Bureau. The vast majority of analyses are sent to outside laboratories with lab results reported to the Bureau using paper documents and the occasional Excel (.xls) spreadsheet.Analysis Results shall be kept in a table called Analysis Results, which shall contain a foreign key, which in combination with a “table name” field shall be used to reference each analysis result back to a sample in one of the sample tables, or to a geologic feature or event in one of the feature tables. It is recommended that wherever possible analysis results be referenced to samples rather than geologic features, especially if physical samples are maintained by the Bureau or are otherwise available.The analysis results table is supported by three other tables providing information about the particular tests that were run, any standards that may have been analyzed to validate these tests and general information about the lab and personnel that carried out these tests. The schema and relational structure of these tables is described below together with the analysis results table.Analysis results are grouped in “Runs”. A run for the purposes of this discussion consists of all of the tests performed by a laboratory for a particular set of samples, which were received and processed in one batch and have a common set of detection limits and standard tests. Essentially it is a synonym for “test batch”.Analysis results may be composited, that is many results (for the same analyte) may be combined in some proportion to yield a resulting “pseudo result value”. This compositing could occur in two ways:Physically, meaning that the analyzed samples are combined before being analyzed (in order to reduce costs). In this case all that is necessary is to indicate that a particular analysis value (result) is a composite. This is done by setting a flag (the “Is Composite” Boolean field) to indicate this fact.Mathematically, meaning that individual existing results are combined into a weighted average (sometimes averages are more useful than individual measurements). In this case “source” results would exist throughout the analysis results table. In order to track which original results were combined into which composite results, each “source” result shall include a reference to the composite result in which it participates. This reference shall consist of the primary key (Result ID) of the composite result stored in a “Composite Result ID” (foreign key) field for the source result record.A special interface shall exist to aid in the management of composited results. This shall allow the input of the composite result id into the participating source or component result records. This shall also aid in the population of the composite result value by listing all of the component results and offering various methods for populating the composite. These methods shall include:Leave blank.Enter a value manually.Pick a value from any one component result.Calculate a straight average from all component results.Pick any common numeric field available in the samples of geologic features that were analyzed and use it as a weighting factor in the calculation of a weighted average. Typically this would be sample weight or sample thickness (as recorded in the spatial reference information)Notes:All data types/fields are obligatory unless explicitly designated as optional. All data types / fields are exposed and editable through the user interface for this type of feature unless explicitly designated otherwise. Decisions regarding obligatory fields may be changed during the system design phase.Basic ownership and project tracking fields are included in this table as described in section 2.5.1 so they shall not be repeated here.Analysis Results Table:Result ID: system-generated identification, not available for editingIs Composite (optional): Boolean ( Y / N ) By default this is set no “N”. If this result is a composite, either because multiple samples have been analyzed together, or because the analysis results of multiple samples have been combined into a weighted average this field is set to “Y”.Composite Result ID (optional): If this result has been merged with other results (for the same analyte) to create a composite “pseudo result” (one that has its “Is Composite” field set to “Y”) this field shall reference the result id of this resulting composite pseudo result.Sample ID: This is a foreign key that relates to the sample (or geologic feature) that was analyzed in this test.Table Name: Since there are multiple sample tables, this field is necessary to determine to which table the sample id relates. It is also possible, but not recommended, to enter the name of other geologic feature tables in this field. In such a situation this would be associated directly to a geologic feature, rather than to a specific sample.Test ID: This is a foreign key to the Tests_By_Run table. This table uniquely lists the different tests performed for a particular run. It includes tested analytes, detection limits and obtained values for standards.Tests_By_Run Table: This shall include the following fields:Test ID: This is the primary key, system-generated, not available for editing.Test Type: This shall include values such as “Coal Quality”, “Geochemistry”, “Isotopes”, etc. Test types typically presuppose a certain set of analytes or measured quantities, and provide useful information regarding the purpose of a particular set of tests.Run ID: This is a foreign key to the Run_By_Lab table. This table uniquely lists the different runs performed, including the Lab and Analyst who participated in the run.Run_By_Lab Table: This shall include the following fields:Run ID: This is the primary key, system-generated, not available for editing.Laboratory (optional): pick list (from Laboratories lookup table)Laboratory Reference Number as provided in lab report (optional).Name of Analyst who performed testing (optional).Date of Analysis (optional).Analyte or Measured Quantity: Any chemical element or compound, physical characteristic, etc.Measurement Units: Should be recorded because they may be different for different runs, especially if performed by different labs.Analysis Measurement Method (optional): This may be obtained from a lookup tableDetection Limits (optional): May also be different for each run, often depending on instrumentation.Standard ID (optional): This is a foreign key to the standards table. This table lists accepted result values for standard materials commonly used to calibrate measuring equipment.Standards Table: This shall include the following fields:Standard ID: This is the primary key, system-generated, not available for editing.Standard NameAnalyte or Measured Quantity: Note, a standard may include multiple analytes. Accepted Value: Accepted value for the analyte or measured quantity.Value Of Tested Standard (optional): This is the result obtained from running the test on the standard material using the same instrumentation as that employed for the submitted samples. In general, if this is too different from the accepted value for this analyte, as it appears in the standards table, it throws into question the validity of the corresponding analysis results for all of the samples included in this run.Analysis Result: Actual result reported for the sample referenced by the “Sample ID” by running the test referenced by the “Test ID”Some results will be reported by referencing detection limits, as qualified data. User can populate either the value/units field combination, or the detection limit, operator fieldsDetection limit: value enteredOperator that describes analysis result relative to detection limit: pick list (Greater than >, Less than <, etc.)Database will support stored procedures so that specific calculations can be performed and results be viewed on the fly: For example:Calculation of specific analytical indices resulting from analytical results such as:Agpiatic IndexCoal equations for capturing both: “Dry” and “Dry and Ash Free” bases.When series of results are entered, if they represent portions of a total, their values will be checked to add to 100%Proximate Analysis, example: Moisture, Ash, Volatile Matter, Fixed Carbon individual values should total exactly 100%.Ultimate Analysis, example: Hydrogen, Carbon, Nitrogen, Sulfur, Oxygen, Ash individual values should total exactly 100%.Associated Documents (optional): photographs, photo descriptions and notes (see discussion under Documents and Images in Section 4.1)Comments (optional): Free form text field allowing the editor to include any additional comments / notes.Data Sources and Migration StrategyThe following sections summarize the data sources identified during user interviews, including brief recommendations on data cleanup and/or data migration techniques to consider when developing the specific data migration workflows and supporting data migration scripts.In general, once the most recent information is transferred into RockIT for each section of the Bureau, it was recommended to target data migration on specific regions of the state as required by key projects, moving back in time using 5-year intervals (this can be modified as needed).UsersThe System Administrator will need to enter each user into the system, individually, setting passwords and edit permissions as required. Once populated, this content will remain fairly static.ProjectsThe Projects information currently housed in the Outcrops database is the most recent set of digital data that has a formal “projects” structure to the data. All other information stored at the Bureau will need to be assembled into “projects” as data are migrated into the database. The data migration scripts and workflow will need to account for the decision about whether data are to be entered into the “General” project, or whether data need to be grouped into a specific project category.For some data, it might be helpful to build projects out of the station ID, such as drill holes, and use that ID as the flag to tie all associated paper records together. When data entry occurs, the editors can first search the database for a project with the station ID listed on the paper document. If that project exists, all associated events / features / sample results should be stored with that project, too.Some data migration might simply group data entry by year, and assign that collection of records its own project, such as “Landslide Data 1995.” These decisions need to be discussed with the Bureau investigators as the data migration scripts and workflows are developed during the next phase of RockIT system design and implementation.Table 51 General Data SourcesProject DataSizeContactPaper archives stored onsite at the BureauUnknownGale Blackmer, primaryAll geologistsOutcrops Oracle databaseUnknown, actively used to store researchGale BlackmerEvents / Features / Samples and Associated StationsRecommended strategy for migrating drill holes stored in paper archives is to use the data entry forms from the Dashboard page, enter a new station to capture the drill hole location and characteristics and then create the associated records that correspond to that drill hole (station), such as coal quality analysis samples, CBM samples, etc.Table 52 Drill Hole Data SourcesDrill Hole Locations and CharacteristicsSizeContactPaper archives stored in stacks at the Bureau, includes a variety of materials: paper strip charts, drilling reports and logsUnknownCliff DodgeRochester and Pittsburgh (R&P) database – includes links to imagery for drill holes and pilings. Utilizes 3-digit lithology codes that were developed by R & P, will need to develop code “translation” table to map values into database standard values~ 10,000 drill hole records~ 80,000 scanned images of 35 mm slides tied to drill holesBill BragonierNCRDS (National coal Resources Data System) developed by US Geological Survey. Includes point location of drill holes with quality data provided. Unknown data quality. Not sure how to export resources from Oracle database.UnknownCliff Dodge, Mike MooreAccess database originally built by Roger Faill, currently being edited by John Neubaum. Database includes coordinate values, sample ID, and analysis results (approximately 1000 records)~ 1000 recordsCliff Dodge, Gale Blackmer Penn State University developed coal quality database with variable data quality, especially with regard to sample location. Where coordinates are reliable, db provides strong, intensive analysis.UnknownCliff Dodge, Mike MooreLogPlot excel sheets used for data input into modelUnknownCliff DodgeMost of the attributes that had been associated with the Driller’s Log are now stored in the Drill Holes table that is related to Drill Hole stations. The drilling company names will be incorporated into the Companies look up table to include name, address, and other relevant contact information.Table 53 Drillers' Logs Data SourcesDriller’s Log- to be incorporated into Companies Lookup TableSizeContactPaper archives stored onsite at the BureauCliff DodgeThe Bureau is currently maintaining an Oracle database housing recent Outcrops research. Table 54 Outcrops Data SourcesOutcrop Location and CharacteristicsSizeContactPaper archives stored onsite at the BureauUnknownGale BlackmerOutcrops Oracle databaseUnknown, actively used to store researchGale BlackmerIt is not clear the extent of overlap the landslide source materials will have. Landslide events do not have a unique assigned to them so initial data review may need to include a “quick” digitizing effort to identify redundant data points before proceeding to move all content into RockIT.Table 55 Landslide Data SourcesLandslidesSizeContactPaper archives stored on-site at the BureauUnknownHelen DelanoExcel worksheet and various digital reportsUnknownHelen Delano7.5’ quadrangle maps depicting landslide inventoryCovers half of the CommonwealthHelen DelanoPublished book of landslides produced by Survey – this has been digitized to point feature class in a geodatabase. Serves as foundation of efforts to develop “susceptibility zones”.~ 1300 landslide locationsHelen Delano, Mike MooreLandslides the Survey is actively “monitoring” – especially interested in recurring landslides at same location< 100, subset of total inventory to dateHelen DelanoPennDOT landslide recordsUnknown?Like landslides, sinkholes do not currently have a unique ID assigned to them and the content within the three sources listed below overlaps to some degree. It may be helpful to digitize these sinkholes to identify redundant records, remove the duplicates, and then migrate data into RockIT.Table 56 Karst Feature Data SourcesKarst FeaturesSizeContactExcel fileUnknownMay contain records listed in other sourcesBill KochanovHandheld device that stores records in Access database2500 – 3000 recordsMay contain records listed in other sourcesBill KochanovOnline database distributed @ ~2200 recordsMay contain records listed in other sourcesBill KochanovSurvey and Measured Section DataOnly a handful of drill hole stations will have survey information available for them, at present. The Bureau hopes to acquire as much survey detail as possible when working with drill holes, creating their own, and/or coordinating with the private sector to collect information.Table 57 Drill Hole & Measured Section Survey Data SourcesStation Survey and Measured SectionsSizeContactMeasured sections stored in researcher’s project notesUnknownGale BlackmerARM engineering surveyed drill holes and provided reports that include photos, xyz coordinates, and deviation valuesUnknown, handfulCliff DodgeMeasured sections would be found in Outcrops paper archives or in Outcrops databaseUnknownGale BlackmerSamples and Analysis ResultsTable 58 Coal Quality Analysis Data SourcesCoal Quality Analysis Source DocumentsSizeContactPaper archives stored onsite at the BureauUnknownCliff DodgeAccess database originally built by Roger Fail, currently being edited by John Neubaum. Database includes coordinate values, sample ID, and analysis results (approximately 1000 records)~ 1000 recordsCliff Dodge or Gale Blackmer contactsPenn State University developed coal quality database with variable data quality, especially with regard to sample location. Where coordinates are reliable, db provides strong, intensive analysis.UnknownCliff Dodge, Mike MooreTable 59 Other Analysis Data SourcesOther Analysis DataSizeContactGeochemical results in paper archives stored onsite at the BureauUnknownJohn BarnesGeochemical results in Excel worksheets (.xls)UnknownJohn BarnesUranium Occurrences Access database built in the 1990’s to support Survey’s research on radon. Lists uranium and thorium occurrences.UnknownJohn BarnesBasalt dataUnknownJohn BarnesBarite Occurrences databaseUnknownJohn BarnesCBM Well Analysis Data (paper and excel worksheets)UnknownToni MarkowskiIsotopic Age paper archivesUnknown: Just a few per yearSteve Shank, Gale BlackmerThin Sections paper and digital archivesUnknownSteve Shank, Gale BlackmerSurficial Deposits analysis results are stored in paper archivesUnknownGary FleegerXRD results – stored in XRD software applicationUnknownJohn BarnesSEM results – stored in SEM software applicationUnknownJohn Barnes, Steve ShankImage DataTable 510 Image Data SourcesLinked Images and DocumentsSizeContactRochester and Pittsburgh (R&P) Access database – includes links to imagery for drill holes and pilings.~ 10,000 drill hole records~ 80,000 scanned images of 35 mm slides tied to drill holesBill BragonierThin Sections paper and digital archivesUnknownSteve Shank, Gale BlackmerTiff images of geophysical logsUnknownCliff DodgeSEM: imagery and energy-dispersive X-ray spectra stored in SEM software application. Can be exported to a number of formats: JPEG, PICT, Pixmap, PCX, PSD, Sun Raster, TARGAS, TIFF, WMF, WPG, Xbitmap. All image or spectrum exports include a .TXT file that contains documentation of parameters used to obtain image or spectrum.~4.5 GB~18,900 filesJohn BarnesSEM: cathode luminescence detector images stored as JPEG images in SEM software application~650 MB~ 380 filesJohn BarnesXRD: analysis results stored in Jade software application. Export options currently known are copy/paste to insert images/tables into a standard document, such as .doc. Another export option is to employ Adobe technology to consolidate analysis components into a PDF~25 MB~ 2000 filesReflects 9 years worth of dataJohn BarnesXRD: analysis results stored in Philips PC-APD application. Only export option at present is printing (then scan to make digital)~4.95 MB~206 filesReflects 5 years of dataJohn BarnesStrip charts generated by LogPlot: paper and digital filesUnknown and recurringCliff DodgeFor drill holes, there are archives of photography capturing top/bottom depths of core samples and labeled by core sample. Photo labels correspond with sample boxes in basement.UnknownCliff DodgeIgPET resultsSteve ShankSystem AdministrationUser AccountsThe System Administrator will be the only authorized user allowed to create new user accounts. By default, users (except the “Public” account) acquire read-only access to all tables across the entire database. Users will not be allowed to establish restricted access. All data will be available for viewing and searching. As with most enterprise environments, the Bureau will have a diverse panel of users accessing the RockIT database and user applications. The table below summarizes the general categories of users and the associated tasks they will most likely require:Table 61 Envisioned Permissions by Type of StaffUSER TableThe USER table records all user accounts that have been authorized to log in to RockIT. Only the System Administrator will be allowed to add / update records within the USER table. To be consistent with the system requirements stated elsewhere in this document, the System Admin tools will not allow records to be deleted from the USER table. When a user account is retired the RockIT application will provide a mechanism for the System Administrator to assign a new “owner” for all projects owned by that retired user account (as described in the PROJECT_PERMISSIONS table below). This ensures that research projects are not abandoned or lost due to staff turnover.The USER table will manage user logins permissions within the database. As the table below indicates, a user account requires a user name, password, data added (system generated), status, and permissions. Table 62 Sample User TableThe STATUS field allows for two values: active, retired. When set to retired, edit permission are automatically switched to NONE and the user account is no longer able to view, edit or query the database.The PEMISSIONS field supports the following values: EDIT, NONE, ADMIN, READ, and SME. The default setting for all new user accounts can be set to READ. The ADMIN permissions are to be assigned to one System Administrator. The SME permissions refer to “Subject Matter Expert” and permit any user with these permissions to edit the lookup and reference tables within the database. For obvious reasons, it is important that users given SME permissions, indeed, possess the appropriate content expertise and system knowledge to effectively administer the lookup and reference tables.Public ViewerThere shall be a “public viewer” user, different from all other “internal” users. This “public viewer’ user shall have a special, limited set of permissions and will be used by the system for “non-login” connections such as those coming in from the internet or from public access “kiosk” terminals.Projects and Edit PermissionsEdit permissions are established via two levels. First, a user must be added to the USER table and have their STATUS field set to ACTIVE and their PERMISSIONS field set to EDIT. As previously discussed, a USER will have read-only access to all tables within the database (the “Public” account is the one exception). Secondly, a USER account that has been added to the USER table and has EDIT permissions is allowed to create a project or can have the System Administrator assign them to the “General” project, referred to as “TOPOGEO” in the example below. This table authorizes the user account edit access to all records within the assigned project(s), across all database tables. Only the System Administrator can set PERMISSIONS for a user account. Once a user account has EDIT permissions and has created a project, then as the “owner” of a project, that user can grant edit permissions to research collaborators.If a user account is ACTIVE and its permissions set to READ, that user will be able to read all content throughout the database, and will not be able to edit any content. If a user account is ACTIVE and its permissions set to EDIT, that user will be able to read all content throughout the database, and will have access to edit projects where the account has been assigned as an authorized user. By default, any user with EDIT permissions will be allowed to create a project, and as owner of the project, that user account will be allowed to edit all of its content. If a user account is RETIRED, its permissions are automatically set to NONE and the account is no longer eligible to log in to RockIT.PROJECT_PERMISSIONS TableThe PROJECT_PERMISSIONS table manages what users are allowed to edit what projects. If a user is not assigned to a project in the PROJECT_PERMISSIONS table, the user will not have access to edit its content, even if the user has general EDIT permissions. The PROJECT_PERMISSIONS table works with the PROJECTS table, where the owner information is originally stored.The following fields will audit user access to projects: User Name, Project Name, Date user was assigned to the project (reflects the date the user created the project or assigned a collaborator to the project), and the Grantor (user account that permitted another user account access, can only be project owner or System Administrator).Table 63 Sample Projects TableThe PROJECT_PERMISSIONS table is automatically populated when a USER with EDIT permissions creates a project, such as record #1 in the example above. User “CDODGE” has created the “LEIDY” project on Oct 10, 2007. Because CDODGE is both USER and GRANTOR we know that CDODGE is owner of the project. On Oct 17 2007, CDODGE added TMARKOWSKI as an authorized editor for the LEIDY project. The LEIDY project has two user accounts authorized to edit all content. All other users within the database will be able to read the notes, station locations, sample results records, etc. that CDODGE and TMARKOWSKI edit within the project. No other users will be allowed to edit the data.General Project PermissionsIt is not compulsory for a user to define a project for every piece of data entered into the system. If no project is defined the data is transparently associated with a “general” project, readable by everybody and writable by a defined set of “general” users. The group of users allowed to edit the Genera Project will be managed in the PROJECT_PERMISSIONS table. The System Administrator will be the only user authorized to add users to the “General” project.In the example above, the General Project has been called TOPOGEO and there are two users allowed to edit it: MMOORE and SYSTEM ADMIN.System Architecture ConsiderationsAs mentioned previously we propose an architecture consisting of a server-side web framework underlying asynchronously updated web pages / client-side applications, as shown in the diagram below. Figure 71 Generalized Server SetupWithin this setup the server-side framework will serve as the core of the system, implementing / triggering all of the Bureau’s business rules for update and maintenance of geologic data, as well as spawning the client-side web pages / applications that will allow users to interact with the system. In order to maximize performance all of the intensive disk access involving data retrieval will be left to the image file system server and the database server which furthermore will perform all necessary relational assembly of data before submitting it back to the web server. This will leave the web server with the relatively light load of responding to client requests by passing back image URLs or by generating and submitting queries to the database server, and then passing the results back to the client.To illustrate what is meant by “the database server performing all relational assembly of data”, consider a search for information by geologic age or period. Such a search may involve querying many different tables simultaneously. For this approach to be effective the query/s must be structured such that results returned from the database server are ready to be used by the client, needing only minor processing by the web server, such as being wrapped in HTML, for example. This advantage would of course be voided if instead the web server received a multitude of datasets which it then would have to relationally assemble (in code), before passing them on to the client / user interface.From a purely generic systems architecture perspective the components of this system must perform the tasks listed below:Web Client Functions:Display dataSend asynchronous requests to serverCapture server response and update corresponding data displayWeb Server Functions:Manage Session, implement securityLaunch clientReceive requests from clientTurn client requests into queriesSubmit queries to DatabaseCapture results from queriesSend data asynchronously to client or re-launch clientRetrieve Images or Image URIs from Image File Server and submit asynchronously to web clientDatabase FunctionsImplement SecurityExecute queries and submit resultsImage File Server FunctionsImplement virtual web directories to store image filesServe up image filesThere is a wide variety of server-side technologies, such as , Php, Perl, etc. that could be combined with client-side technologies such as Silverlight, Flash with F-Jax, DHTML with Ajax, etc. to achieve the desired result. The particular choice of technologies should be predicated on any software standards existing at the Bureau, as well as on the expertise of the most qualified developers available for this effort.System Level SecurityRouting and Firewalls.Prevention of Cross Site ScriptingPrevention of Cross Site Request ForgeriesPrevention of Session HijackingCookies, Tokens, Sessions, renewed after logins, destroyed after logoutsPrevention of Code injectionProper escaping of inputs submitted for queriesRegex Validation of everythingReliability and Availability Failover setup with backup database server, redundant image server and redundant web server. Database maintained via database replication, images maintained via file system replicationBackup and RecoveryDatabase must be on regular maintenance and backup planCapacity Requirements Database server needs massive processing capability plus fast disk. Image file system server needs moderate processing power but very fast disk. Storage hardware needs to be high capacity high performance possibly raid or sanImages expected to be a large chunk of storage could be in separate box altogetherTerabytes of storageEquivalent to Quad Core server processing power for both the database and the web serverScalability and Extensibility Requirements It is expected that at the onset data will increase rapidly as various sets of data and paper records distributed throughout the system will be imported into RockIT. Subsequently, it is expected that with each passing year, the percentage of data increase with respect to the existing will naturally decrease as the total volume of data increases at a steady rate. Due to the specialized nature of the data managed by RockIT it will not initially have to service a very large number of users simultaneously. However, if RockIT is opened up to external users or even the public at large, this scenario could change. In general the system should be designed so as not to prevent scale-out strategies. In order to deal with large numbers of external read-only visitors the simplest scale-out solution would be to provide multiple copies of the database on separate server boxes kept synchronized by the use of replication (such as in a failover scenario). Each would be serviced by its own web server which in turn would receive requests form the internet in a round-robin fashion (managed by a “gateway server”).If internal usage generates contention a strategy such as Data Dependent Routing (DDR) can be used. In this scenario data can be distributed among multiple live editing database servers either by vertical partitioning, that is placing different tables in different servers or by horizontal partitioning (recommended approach) meaning having complete versions of the database in multiple servers, each containing a subset of the data. Horizontal partitioning should probably be done at the project or geologic feature level. In other words details belonging to one complete project (or feature) should be stored in one database server rather than distributed among two or more.Capture of Field Data via Mobile DevicesIn general there are two approaches envisioned for data capture in the field using mobile devices:Use of a handheld GPS data logger.These have a small (less than 5”) screen, ex Trimble Geo XTCapture data via predefined data “dictionaries” which essentially are simplified versions of the database schema that is to be populated back at the office.Data is typically downloaded to a PC where it is post processed to improve spatial accuracy. The final output usually consists of an “Access” database or a comma delimited set of tables.To import these data the standard Importing tools or import scripts may be used.Issues associated with use data loggers:Pro: Device is light an easy to handleCon: Data dictionaries have to be developed in the first placeCon: Data dictionaries do not support deep relational structures very wellCon: Multiple steps to import data.Use of a GPS enabled laptop or tablet PC.These would be deployed with a database replica of the entire system, as well as a local web-server.Importing of field collected data to the main database would only require a synchronization operation which would take place after connecting the laptop to a network back at the office.Issues associated with use laptop or tablet:Con: Device is bulkier.Con: Application to capture GPS coordinates and make them available to the RockIT interface would have to be developed.Pro: Entire database is available for review in the field.Either one, or both of these approaches can be used successfully. The choice is primarily going to be based on user preference, and the best way to establish that requires that users actually gain some experience using similar applications in the field.User Documentation and TrainingThe RockIT database and user applications will require the following user documentation and training:User DocumentationRockIT Database Data DictionaryDocument that defines all tables and the fields associated with themDocuments each attribute valueDocuments all lookup table and data reference table valuesRockIT Database DiagramFor the completed database design, Bureau will want a database diagram outlining all relationships between data content tablesUser GuideDocument provides step-by-step instructions for how to complete all use cases addressed by the software system design and implementation.For example, User Guide would outline How to create a project and then associate an event and/or sample to the projectExplain what a station is and how to create a new oneProvide instruction on how to search the databaseIllustrate how to insert images/documents to events / features / samples within the databaseDocument would describe the data migration strategy, including steps for how to use data migration scripts that have been developed for the BureauUser TrainingWith the above documents serving as the foundation, the Bureau will provide the following user training sessions:Basic use orientation – This class will be relevant for 85% of the RockIT users. It will teach:How to Login/LogoutHow to searchHow to print/export resultsSME and editor trainingHow to login/logoutHow to searchHow to editPrinting/Exporting ResultsEditing Lookup and Data Reference TablesSystem Administrator trainingBasic orientation to end-user applicationLogin/logoutSearchEditPrint/exportEditing lookup and Data Reference TablesManaging User AccountsManaging ProjectsGiven the intuitive nature of browser-based applications, we anticipate rapid acquisition of the knowledge and skills to use the RockIT environment. Consequently, each of the above training sessions could be covered within 2 - 4 hours, using a teaching model that incorporates discussion and hands-on exercises.It will be important for the Bureau to assign a core team of users who can provide the initial coaching and support that will be required to help move each geologist from their current workflow into the new RockIT environment. It may be most cost-effective for the Bureau to implement a train-the-trainer approach and have the Bureau staff who have been most involved in the database and application development assume the mantle of teaching their colleagues how to use the application upon implementation. DependenciesWhen RockIT is first implemented it will have a limited amount of system dependencies to accommodate, mainly the small fleet of hand-held systems the Bureau uses to collect field data. Initially, RockIT can accommodate these devices through the import functionality, whereby the hand-helds provide access or xls format snapshots of the records collected and the import functionality is employed to pull records into the appropriate environment.As RockIT matures and the adoption of hand-held field collection devices expands, RockIT will need to accommodate a larger range of data formats. Ideally, the hand-held devices that come into service during or after RockIT’s implementation will accommodate the inherent database design and populate the forms on the hand-held devices accordingly. One advantage to the JavaScript environment proposed here is that it will be easy for third-party vendors to provide data exchange formats that work directly between their products and the Bureau’s RockIT database.As RockIT contributes to the Bureau’s adoption of an enterprise-wide data management paradigm, it can help consolidate the independent web applications the Bureau has developed for monitoring events such as landslide locations, sinkholes, and water well testing. RockIT has the flexibility and comprehensive design to serve as the database for all these data monitoring efforts, with different web interfaces pointing out to the public.As was discussed with the sinkhole monitoring program, the Bureau could migrate and update its current sinkhole monitoring web application so that users can report events through the web application where they are contained until a panel of subject matter experts review the claims. If they are deemed accurate, the panel can commit the event to the database, and then the database will report the information back out to the public. This approach provides a greater degree of data quality assurance and quality control than is currently implemented with the sinkhole website. Plus, it leverages the RockIT database as the engine for delivering critical geologic content to the public. AppendixesAppendix A: Reference & Lookup tablesThe following listing references lookup tables and referenced tables identified so far. Values presented are not exhaustive list.ProjectsProject Table (This is neither reference nor lookup)Name of the ProjectDescription of the ProjectContact Person for the project (This shall come from Users lookup data table)StationsStation TypeOutcropDrill HoleMeasured SectionCBM WellSurface featureUnderground featureMunicipality Reference TableName of the MunicipalityFIPS CodeCounty of the MunicipalityFlag (T/B/C)for identification of Township/ Borough / CityQuadrangle Reference TableName of the QuadrangleAbbreviation (2-letters) of the QuadrangleScale of the QuadrangleQuadrangle ScaleName of the Quadrangle ScaleAbbreviation (2-letters) of the Quadrangle ScaleScale number of the Quadrangle ScaleDER code or FIPS number of the QuadrangleMinimum and Maximum LongitudeMinimum and Maximum LatitudeOther Codes (This would be required before table design)Base Map DatumName of the Base Map DatumDescription of the Base Map DatumLocation MethodName of the location methodDescription of the location methodSource of ElevationName of the Source of ElevationDescription of the Source of ElevationGPSPaper mapDRGDEMLiDARField SurveyExposure CharacteristicsName of the Exposure CharacteristicsDescription of the Exposure CharacteristicsUniformNot uniformExposure Type Reference TableName of the Exposure TypeDescription of the Exposure TypeFloatStrip MineQuarryRoad cutMiscellaneousCommodity Code and Category Reference Table (Non Fuel Mineral Industries)Code of the CommodityCategory of the CommodityC- construction aggregateD - dimension stoneR - railroad ballast"Operation Types (Non Fuel Mineral Industries)Code of the Operation TypesName of the Operation Typesopen pitquarrydredgeslag bankCompany (Coal, Drilling, Nonfuel, Oil and Gas)Name of the CompanyAddress of the CompanyContact Number of the CompanyContact Person at the Company – Linked to PersonIndicator for coal, drilling, nonfuel, oil and gas companyType of BusinessFacility Type (Quarry, Office , Mine) – Linked to FacilityPersonName of the PersonDescription of the PersonIndicator (Contact Person)FacilityName of the FacilityDescription of the FacilitySamplesRockCoalCBMWaterMeasurementsMeasurement MethodName of the parameters defining Measurement MethodDescription of the parameters defining Measurement MethodRight hand azimuthRight hand QuadrantMeasurement DirectionName of the ProjectDescription of the ProjectUnits (Measurement)Name of the UnitDescription of the UnitLithologic InterpretationsStratigraphic Age Reference TableName of the Stratigraphic AgeParent of the Stratigraphic AgeSequential order among the siblingsStratigraphic Unit Reference TableName of the Stratigraphic UnitParent of the Stratigraphic UnitStratigraphic Rank of the Stratigraphic UnitStratigraphic Rank Lookup TableID of the Stratigraphic RankName of the Stratigraphic RankSuper GroupGroupFormationMemberBedShort name of the Stratigraphic UnitMap Symbol of the Stratigraphic UnitStratigraphic Age of the Stratigraphic UnitSequential order among the siblingsDescriptionConfidence RatingCode of the parameter which defines Confidence RatingName of the parameter which defines Confidence RatingDescription of the parameter which defines Confidence RatingRock Descriptions: Bedrock & SurficialMap unit (to be deprecated)Name of the Map unitSymbol of the Map unitDescription of the Map unitInformal Lithologic and Rock UnitName of the unitSymbol of the unitDescription of the unitFlag (L/I) for identification of Lithologic / Rock UnitLithology Prevalence & PercentageCode of the Lithology PrevalenceDescription of the Lithology PrevalenceOneTwoThreeFourColor (also for surficial)Code of the ColorName of the ColorDescription of the ColorFlag (Y/N) for identification of color from the standard color chartWeathering Type (also for surficial)Name of the Weathering TypeDescription of the Weathering TypeFreshWeatheredVery WeatheredBedding/Parting ThicknessName of the Bedding/Parting ThicknessDescription of the Bedding/Parting Thickness<1/32"1/32" - 1/4"1/4" - 1/2"1/2" - 3"3"-1'1'-2'2'-4'>4' (allow entering actual thickness in this case)1-2 cmType of Parting (also for Coal)Name of the Type of PartitionDescription of the Type of PartitionBeddingCoal cleatFissilityCleavageGrain Size (Clasts)Name of the Grain SizeDescription of the Grain SizeFineCoarseMediumFine to CoarseFine Sand (Surficial)Medium Grained Sand (Surficial)Trace (Surficial)Texture (Clasts) (also for Surficial)Name of the TextureDescription of the TextureClast SupportedMatrix SupportedClass to Matrix equantWell-foliatedPoorly FoliatedSediments Roundness (Clasts)Name of the parameter which defines Sediments RoundnessDescription of the parameter which defines Sediments RoundnessVery AngularAngularSub RoundBinder and CementName of the Binder and CementDescription of the Binder and CementClaySilicaCarbonateSedimentary Structure TypeName of the Sedimentary Structure TypeDescription of the Sedimentary Structure TypeGranular (Surficial)Blocky (Surficial)Platy (Surficial)Gradational Change TypeName of the Gradational Change TypeDescription of the Gradational Change TypeFining upwardGraded beddingCoarsening upwardSorting TypeName of the Sorting TypeDescription of the Sorting Typewell sortedpoorly sortedsemi well sortedFossilCommon Name of the FossilFossil Type TaxonDescription of the FossilFossil TypeName of the Fossil TypeDescription of the Fossil TypePlantMarine VertebrateMarine InvertebrateetcAbundance (Fossils, Fossil, Minerals?)Name of the AbundanceDescription of the AbundanceAbundantCommonFewRareOccurrence (Fossils)Name of the OccurrenceDescription of the OccurrenceFragmentHashBody FossilMineral (also for Surficial (clasts))Symbol of the MineralName of the MineralChemical group of the MineralDiagnostic / AccessoryDescription of the MineralChemical GroupName of the Chemical GroupName of the Class Code of the GroupDescription of the Chemical GroupRock FragmentsName of the Rock FragmentsDescription of the Rock Fragments<1/2 inch >1/2 inchbothDepositional Environment (Rock description & Station)Name of the Depositional EnvironmentDescription of the Depositional EnvironmentAeolianFluvialAlluvialCoalCoal Zone (Seam?)Name of the Coal ZoneDescription of the Coal ZoneBandedNon-BandedMixedType of Sample (Coal)Name of the Type of SampleDescription of the Type of Samplefull seampartial benchfull benchAbundance of Vitrain BandName of the Thickness of Vitrain BandDescription of the Thickness of Vitrain Band> 60%30-60%15-30%0-15%Thickness of Vitrain BandName of the Thickness of Vitrain BandDescription of the Thickness of Vitrain Band5 mm2-5 mm1/2-2 mmClarain-Durain LusterName of the Clarain-Durain LusterDescription of the Clarain-Durain LusterBrightModerately BrightMidlustrousModerately DullDullRock Descriptions: Surficial OnlySurficial Deposit TypeName of the Surficial Deposit TypeDescription of the Surficial Deposit TypeGlacialAlluviumColluviumsMan-made fillCompactness TypeName of the Compactness TypeDescription of the Compactness TypeHardSoftModerateSurficial Deposit CohesivenessName of the CohesivenessDescription of the CohesivenessCohesiveNon CohesiveSurficial Deposit PlasticityName of the PlasticityDescription of the PlasticityNon PlasticLow PlasticityHigh PlasticitySurficial Deposit MoistureName of the MoistureDescription of the MoistureDryMoistWetSurficial Deposit Classification of LithologyName of the Classification of LithologyDescription of the Classification of LithologyCoarse Grained DepositsFine Grained DepositsTopography FeatureName of the Topography FeatureDescription of the Topography FeatureGeologic StructuresContact TypeName of the parameter which defines Contact TypeDescription of the parameter which defines Contact TypeFaultFormationLithologicUnconformityGradationalSharp (Surficial)Gradational (Surficial)Plane TypeName of the Plane TypeDescription of the Plane TypeFoliationFractureBeddingAxial PlaneVeinSlip PlaneFormation – ContactJointCleavageLinear TypeName of the Linear TypeDescription of the Linear TypeFold axisCrenulationsIntersectionMineralStretchingRelative Age (of strain features)Name of the Relative AgeDescription of the Relative AgeD1D2D3Analysis DataLaboratoryName of the LaboratoryDescription of the LaboratoryAddress and contact information of the LaboratoryAnalysis Name (Elements & Oxides) (this could be all types, include description field)Name of the AnalysisDescription of the AnalysisMethods of Analysis (description should say if CBM, trace elements, oxides, etc.)Name of the Method of AnalysisDescription of the Method of AnalysisAnalytesTo include full list of all analytes that may be reported in laboratory analyses (periodic table, plus)Detection Limit OperatorGreater ThanLess ThanGreater Than or equalLess Than or equalStandardsListing of the standards names used for checking laboratory results with known valuesLab results will be compared with values for the selected standardCBM WellsApparent Rank of CBMName of the parameters defining Apparent Rank of CBMDescription of the parameters defining Apparent Rank of CBMHV-A High ValueLV Low ValueCBM Well Permit StatusName of the parameters defining CBM Well Permit StatusDescription of the parameters defining CBM Well Permit StatusNPP - Non producing and PluggedN - New ActiveDI - Dry and InactiveWIS Record TypeF – front screen information onlyCR – complete reportPC – plugging certificateSTRAT – interpreted StratigraphyL- LogsP – production reportS – samplesSinkholesSinkhole TypeName of the parameters defining Sinkhole TypeDescription of the parameters defining Sinkhole TypeCavesSurface MineClosed DepressionSink HoleSinkhole StatusName of the parameters defining Sinkhole StatusDescription of the parameters defining Sinkhole StatusOpen HolePartial RepairedUnknownSinkhole Repair MethodName of the parameters defining Sinkhole Repair MethodDescription of the parameters defining Sinkhole Repair MethodCrushed StoneLarge RockConcreteUnknownNot ApplicableLandslidesLandslide TypeName of the parameters defining Landslide TypeDescription of the parameters defining Landslide TypeDebris SlideDebris FlowRock fallSlumpLandslide AgeName of the parameters defining Landslide AgeDescription of the parameters defining Landslide AgeActiveRecentOldLandslide Known CauseName of the parameters defining Landslide Known causeDescription of the parameters defining Landslide Known causeRainfall EventConstructionLandslide Repair MethodName of the parameters defining Landslide Repair MethodDescription of the parameters defining Landslide Repair MethodIsotopic AgeIsotopic MineralZirconMetamorphicIsotopic Age TypeName of the Age TypeDescription of the Age TypeIgneousMetamorphicSystem AdministrationEdit PermissionsReadEditAdminSMENoneUsersNames of users with edit permissionsInserted into Project_permissions table and elsewhereUser Account StatusActiveRetiredAppendix B: Measurement ParametersNOTE: The material presented below is taken directly from the Appendix of Infotech’s document “Stratigraphic Database Development Project: Software Design Specifications Document” (September 2005). It would simplify the software design process if the Bureau could standardize on one measurement method. A point of discussion as the system design specifications is developed.This section describes the three methods for collecting and recording measurement data. Each of these methods could be applied using one of two different compass styles – azimuth or quadrant. A final methodology is quadrant based and references all strike measurements to the northern compass hemisphere. Therefore, this document describes the data entry and conversion process for seven (7) different data-collection method/compass combinations. Measurement data displayed in, or exported from, the database should always be in right-hand-rule, azimuth format, therefore, it makes sense to store all measurements in right-hand-azimuth format. Right Hand Rule - Azimuth Compass:User enters:Strike Azimuth - between 0 and 360 degrees as measured clockwise from northDip Angle – between 0 and 90 degreesRight-hand azimuth format is determined as follows:Strike Azimuth = Strike AzimuthDip Azimuth = (90 + Strike Azimuth) MOD360Right-Hand Rule – Quadrant Compass:User enters:Strike Bearing - between 0 and 90 degrees measured from either north or southQuadrant – one of four quadrants – NE, SE, SW, or NWDip Angle – between 0 and 90 degreesRight-hand azimuth format is determined as follows:Measurements in the NE quadrantStrikeAzimuth = Strike Bearing Dip Azimuth = (90 + Strike Bearing)Measurement in the SE quadrant:Strike Azimuth = (180 - Strike Bearing)Dip Azimuth = (270 - Strike Bearing)Measurement in the SW quadrant:Strike Azimuth= (180 + Strike Bearing) Dip Azimuth = (270 + Strike Bearing)Measurement in the NW quadrant:Strike Azimuth = (360 - Strike Bearing)Dip Azimuth = (90 - Strike Bearing)Left-Hand Rule - Azimuth Compass:User enters: Strike Azimuth - between 0 and 360 degrees as measured clockwise from northDip Angle – between 0 and 90 degreesRight-hand azimuth format is determined as follows:Strike Azimuth = Strike AzimuthDip Azimuth = (270 + Strike Azimuth) MOD360Left-Hand Rule – Quadrant Compass:User enters:Strike Bearing - between 0 and 90 degrees measured from either north or southQuadrant – one of four quadrants –NE, SE, SW, or NWDip Angle – between 0 and 90 degreesRight-hand azimuth format is determined as follows:Measurements in the NE quadrantStrike Azimuth = Strike Bearing Dip Azimuth – (270 + Strike Bearing) MOD360Measurement in the SE quadrant:Strike Azimuth = (180 - Strike Bearing)Dip Azimuth = (90 - Strike Bearing)Measurement in the SW quadrant:Strike Azimuth= (180 + Strike Bearing) Dip Azimuth = (90 + Strike Bearing)Measurement in the NW quadrant:Strike Azimuth = (360 - Strike Bearing)Dip Azimuth = (270 - Strike Bearing)Dip Vector - Azimuth Compass:Dip Azimuth - between 0 and 360 degrees as measured clockwise from northDip Angle – between 0 and 90 degreesRight-hand azimuth format is determined as follows:Strike Azimuth = (90 + Dip Azimuth) MOD360Dip Azimuth = Dip Azimuth Dip AngleDip Vector – Quadrant Compass:User enters:Dip Bearing - between 0 and 90 degrees measured from either north or southQuadrant – one of four quadrants – NW, NE, SW, SEDip Angle – between 0 and 90 degreesRight-hand azimuth format is determined as follows:Measurements in the NE quadrantStrike Azimuth = (270 + Dip Bearing) MOD360Dip Azimuth = Dip BearingMeasurement in the SE quadrant:Strike Azimuth = (90 – Dip Bearing)Dip Azimuth = (180 - Dip Bearing) Measurement in the SW quadrant:Strike Azimuth= (90 + Dip Bearing) Dip Azimuth = (180 + Dip Bearing) Measurement in the NW quadrant:Strike Azimuth = (270 - Dip Bearing)Dip Azimuth = (360 - Dip Bearing)Northern HemisphereUser enters:Strike Bearing - between 0 and 90 degrees measured from either north or southStrike Quadrant – one of two quadrants – East or WestDip Angle – between 0 and 90 degreesDip Direction – One of eight compass directions: N, NE, E, SE, S, SW, W, or NWBusiness rules for data entry include:If Strike Bearing is 0 (due north) then Dip Direction must be either “E” or “W”If Strike Bearing is 90 (due East or due West) then Dip Direction must be either “N” or “S”If Strike Bearing is > 0 and < 90 and Strike Quadrant is “East”, then Dip Direction must be either “NW” or “SE”If Strike Bearing is > 0 and < 90 and Strike Quadrant is “West”, then Dip Direction must be either “NE” or “SW”Right-hand azimuth format is determined as follows:If Strike Bearing is 0 (due north) and Dip Direction “E” then:Strike Azimuth = 0Dip Azimuth = 90If Strike Bearing is 0 (due north) and Dip Direction is “W” then:Strike Azimuth = 0Dip Azimuth = 270If Strike Bearing is 90 and Dip Direction is “N” then:Strike Azimuth = 270Dip Azimuth =0If Strike Bearing is 90 and Dip Direction is “S” then:Strike Azimuth = 90Dip Azimuth =180If Strike Bearing is > 0 and < 90 , Strike Quadrant is “East”, and Dip Direction is “NW” then:Strike Azimuth = (180 + Strike Bearing)Dip Azimuth = (270 + Strike Bearing)If Strike Bearing is > 0 and < 90 , Strike Quadrant is “East”, and Dip Direction is “SE” then:Strike Azimuth = Strike BearingDip Azimuth = (90 + Strike Bearing)If Strike Bearing is > 0 and < 90, Strike Quadrant is “West”, and Dip Direction is “NE” then:Strike Azimuth = (360 – Strike Bearing)Dip Azimuth = (90 - Strike Bearing)If Strike Bearing is > 0 and < 90, Strike Quadrant is “West”, and Dip Direction is “SW” then:Strike Azimuth = (180 – Strike Bearing)Dip Azimuth = (270 - Strike Bearing)The following chart summarizes the business rules logic in tabular format. Note: This needs to be discussed during the logic development phase. Table 101 Planar and Linear Orientation Measurement ConventionsStrikeAzimuthStrikeBearingStrikeQuadrantQuadrantDipAzimuthDipBearingDipAngleDipDirectionValue Range ->0 - 3600 - 90E, WNE, SE, SW, NW0 - 3600 - 900 - 90N,E,S,W,NE,NW,SE,SWRHR - AzimuthUser Enters---Calculate---RHR - QuadrantCalculateUser Enters-User EntersCalculate---LHR - AzimuthUser Enters---Calculate---LHR - QuadrantCalculateUser Enters-User EntersCalculate---Dip Vector - AzimuthCalculate---User Enters-User Enters-Dip Vector - QuadrantCalculate--User EntersCalculateUser Enters--Northern HemisphereCalculateUser EntersUser Enters-Calculate-User EntersUser Enters?????????Appendix C: Requirements Specification SignoffsFunctionNameApprovedDateCommentsSignatureProject ManagerAndrew RossDatabase and Application ConsultantShaun LeviBureau of Topographic and Geologic Survey Project ManagerSandip PatelBureau of Topographic and Geologic Survey RepresentativeGale BlackmerBureau of Topographic and Geologic Survey RepresentativeMike Moore ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download