1. Database Name

Access database - "U:\UGSSharedResources\UCRC_Catalog\InventoryV3\RockcoreV3.accdb"

Exports from Access database (more info below) U:\UGSSharedResources\UCRC_Inventory\Inventory_Export

UCRC_Database - hosted in AGOL Live feature service for editing: https://services.arcgis.com/ZzrwjTRez6FJiOq4/arcgis/rest/services/UCRC_Database/FeatureServer

2. Schema

Schema document here. The fields in the database could be reduced - many of them are not used in the application.

Domains - none existing

Indexes – none existing

Photographs table - when adding new photographs to the photographs table do the following:

• If adding multiple photographs, append then run python script that changes well_records to yes/no for photographs field
• If adding one set of photographs, append then manually change photographs field (in well_records) to yes
• In the photographs table, the file URL for the photographs is built using the filename field, the top_depth/bottom_depth, the path field. I don't believe the fileurl field is used. However, to check your photographs location (i.e. make sure the photos will show in app), use the file url field and replace the 'W:/' with 'file://ugspub.nr.utah.gov/core_photos/'. Common syntax errors found in the photographs table include spaces in the folder name when the actual folder has underscores and underscores between the depths in the file name field when the photos actually have dashes.

Relationship classes:

Well_Records related to Inventory Relationship ID 0

• Primary key - UWI, Foreign Key - UWI
• One to many relationship

Well_Records related to Photographs Relationship ID 1

• Primary key - UWI, Foreign Key - UWI
• One to many relationship

Relationship IDs: The relationship ID indicates the order the relationships were built in the geodatabase. Relationship ID 0 means this relationship was the first one built within the geodatabase, relationship ID 1 indicates it was built second (i.e. run as the second geoprocessing task). When rebuilding relationships, they must be built in the same exact order in the geodatabase EACH TIME

REST endpoint schema: https://lucid.app/lucidchart/2a9c3d55-6e92-409b-9567-151543568a2a/edit?invitationId=inv_961e91a8-8320-4127-8ff3-2a26549c3b8d

Downloads: Downloadable inventory: \168.180.168.181\geology\html\docs\xls Update each time application is updated. Schema differs from web app

Database notes - meeting with Peter on 6/24/21: https://docs.google.com/document/d/19IDQQG3WOZbPdcmGdkN9QMRKTFyigVj0um6nDxIbJZk/edit

Access database tables for web application (these were used with Peter N and are probably not going to be used anymore):

Photograph_Tables - Category: Core Photographs_05-17-2021 - This is a table with links to the large UCRC Core Photos directory with everything in it. UGP2 and DUGWAY Core Photographs 06-23-2021 - This is the new table of core photographs of the core we photographed this year. These will need to be loaded into the database and the images moved from Core Photo Archives\UGP2 to the web server. Cheryl is desizing them now.

Web_Core_Photographs - Category: Core_Photographs_06-14-2021 - This is the file we have been working on this year. I think you created this and we have been updating the names, UWI number, depths, etc and matching the corresponding filenames on the server. You will need to update your web tables from this table. You will also see that I have the export SQL there that I used to generate the CSV file. This is a very good SQL View of the photographs table.

UCRC Tables - Category: RECORDS - unique table of samples here at the core center. ROWID is the unique identifier which I use to update records in this table all the time. This table has the UWI, Well Name, Lat, Long, Easting, Northing, and other well information. Not sample type identifiers in this table. UWI is one to many to INVENTORY_TYPEs or INVENTORY on what you are looking for in the SQL statement. INVENTORY_TYPES: table of sample types, PK is ROWID. This is primarly UWI and TYPES. Types can also have different TD, formations, etc. I generally do a UWI one to many relationship from INVENTORY_TYPES to INVENTORY and a 2nd relationshop of TYPES one to many. INVENTORY: This is the actual inventory of boxes in the core center.

3. Permissions

The live feature class is editable by anyone its shared with (it’s a private feature class) The feature class view is not editable

4. Workflow

There are two ways of adding data to rockcore - the most common method is appending new data to the live feature service. The workflow below describes that process: Workflow for appending data to live feature: New data is provided via email spreadsheets (xlsx), mostly from Peter. The data is appended to the live feature service in ArcGIS Pro or ArcGIS Online. A descriptive workflow can be accessed here.

Workflow for overwriting entire database: First, download the current version of the app from AGOL. QA/QC the new data, then project it the same projection as the web app (). To ensure schema is exactly the same, truncate all downloaded tables and append the new data to the truncated tables . Create relationship classes (Inventory first, photographs second). Run scripts for core_types and photographs provided in the workflow document attached to this wiki (above).

This data should be moved into the energy_min SDE eventually - moving the data will require re-coding of all field names in the application to lowercase.

The Northing/Easting data in the Access database(or spreadsheets from energy/min dept) is in WKID 4269

5. Update Schedule

? - twice a year on average

6. Data Services

https://github.com/UGS-GIO/rockcore/wiki/Data---Service-locations