St. Petersburg Coastal and Marine Science Center's Geologic Core and Sample Database Metadata

Information from core description sheets and publications on sediment samples and cores were populated into an Excel spreadsheet by manual entry. Sediment samples and cores that were physically archived in the USGS SPCMSC Core Laboratory were populated with associated repository storage locations in the database.

Information from description sheets and published research on rock (which have since been reclassified to a different material type to improve descriptive accuracy) and coral cores archived within the USGS SPCMSC Core Laboratory repository were populated into Excel spreadsheet by manual entry.

Publishing the database (original methodology): Data from the SPCMSC Core Archive Portal were combined into comparable columns in the SPCSMC Sediment Samples Database in Microsoft Excel to create a single, complete database of all SPCMSC geologic samples and cores, formally known as the SPCMSC Geologic Core and Sample Database. Attribute names were adjusted, but no essential information was lost or deleted during the collation. After collation of the two datasets, the Excel spreadsheet file (.xlsx) of the database was filtered to exclude cores and samples that have not been documented in published research. A filter was applied to the ‘publish’ field as ‘TRUE’ in the .xlsx. This filtered copy of the published-only database was saved as a comma-separated values file (.csv). To preserve attribute field names, the .csv file was imported as a feature class into a file geodatabase using ArcMap 10.1. Next, the file geodatabase feature class was imported into an ArcSDE enterprise geodatabase. The enterprise geodatabase feature class was added and saved into a map document (.mxd), and then published as a web service using ArcGIS Server in ArcGIS Enterprise. The web service was then added to a new web map (SPCMSC Core Viewer Web Map) in ArcGIS Online and saved. Lastly, the web map was added to a web mapping application using ArcGIS Web AppBuilder and various widgets and functionality were developed on the application. The SPCMSC Geologic Core and Sample Database was made publicly accessible on ArcGIS Online via a web mapping application, known as the SPCMSC Core Viewer Web Mapping Application, at https://doi.org/10.5066/F7319TR3.

Republishing the database (original methodology): Information about samples and cores were added to the existing Excel spreadsheet as more were collected in the field, or new information was published about previously collected cores or samples. Only the published cores and sample records were republished at every iteration. Core and sample information may have been altered in the database from previous iterations of the database, as more accurate information from published research was discovered. Once updated with new information, the database (.csv) was republished using the methodology mentioned in the previous process step. The version of ArcMap used to republish the database changed over time as new versions were released by Esri. The final version used for this publishing method was ArcMap 10.6. Once the enterprise geodatabase feature class was updated and the web service was republished, the existing web map and web application automatically updated on ArcGIS Online. This methodology was performed on a continual, as-needed basis from 2013-2021. New publishing methodology was introduced in September 2022. Please refer to the subsequent process steps for more information.

Editing the database in PostgreSQL (current methodology): A new editing method for the SPCMSC Geologic Core and Sample Database was implemented in September 2022 to improve and modernize management of the database. The .xlsx copy of the database containing both the unpublished and published sample records was uploaded into Navicat (version 11.2.16) as a PostgreSQL database. Navicat is graphical database management and development software. PostgreSQL is an open-source relational database management system that utilizes Structured Query Language (SQL), a programming language used for database design, manipulation, and management. Edits to the database are made periodically throughout the biannual update window, and a backup is exported each day edits occur (in .sql format). The backups are stored on an internal server for safekeeping. Edits to the database are executed by either of the following methods: manual edit to the database within Navicat using an edit session in the software, or upload of a .csv to the database in Navicat containing edits of additions using the primary key identifier (pk_id) to add a new record or edit existing records. Updates and edits to the database are made in the following instances: new samples are collected by SPCMSC researchers, new donated or contracted samples are received by SPCMSC, new research about samples are published, changes are made to disposition or storage locations, or new sample information becomes available. All published edits to the database are recorded within a version history document.

Quality control and quality assurance (QA/QC) (current methodology): Once edits are complete for the biannual update, they are quality checked by another member of the SPCMSC data management group to ensure data integrity. This reviewer validates the following: geospatial and temporal coordinates have been transcribed/transposed correctly, changes to the metadata are complete and accurate, the version history fully documents changes made to the database, no typos or errors were introduced during the data entry process, field activity numbers and associated publications are accurate with valid links, and the analyses field uses controlled vocabulary from the USGS Thesaurus.

Publishing the database (current methodology): After QA/QC has been performed, a copy of the database is published to ArcGIS Online. This new method improves the integration of the database with online services. The current methods are as follows: 1. In Navicat, the samples database manager applies a filter to the database. Samples with 'TRUE' in the 'publish' attribute are selected, and the data is exported as a comma-separated values (.csv) file. 2. A pre-defined template ArcGIS Project File (.aprx) is opened in ArcGIS Pro 2.8.3. The ‘Table to Point Feature Class’ geoprocessing tool is used to convert the .csv file into a feature class and is saved in a file geodatabase (.gdb) on an internal server to preserve the field names. The coordinate system for the shapefile is set to the World Geodetic System of 1984 (WGS 84). 3. Six copies of the samples database feature class are added to the Contents pane within ArcGIS Pro. These layers are then selected and placed into a Group Layer and each sample category is defined using a Definition Query. This process sorts the data by the material and sample type fields. The data is currently classified into the following categories: coral cores, coral samples, coral reef cores, limestone cores, sediment cores, and sediment samples. Each layer in the Contents pane is renamed to reflect these sample categories. Additional categories may be added in the future if new sample types are collected and published. 4. Next, each sample category is uniquely symbolized for increased accessibility. Symbol colors and shapes have been chosen to make the dataset and web applicable more accessible to people with disabilities based on Section 508 standards for information and communication technology. For more information about Section 508, refer to https://www.section508.gov/. 5. The group layer is shared as a hosted feature layer to ArcGIS Online as the SPCMSC Geologic Core and Sample Database Feature Layer (hosted). To do this, in ArcGIS Pro, the samples database manager selects the group layer in the contents pane and choses 'Share' > 'Share As Web Layer' > 'Publish'. Other supporting documents and files are also published to the web for user access, which includes this metadata record (.txt and .xml), a version history document (version_history_SPCMSC_samples_database.docx) and a comma-separated values (CSV) file (SPCMSC_samples_database.csv) export of the database. These files are published as a CSV Collection (SPCMSC_samples_database.zip) item in ArcGIS Online and are uploaded directly to the item using a web browser. Next, the samples database manager sends an email request to the USGS Enterprise GIS (EGIS) team to make the ArcGIS Online items (Feature Layer (hosted) and CSV collection) publicly available. During the first time publishing the database, the hosted feature layer was added to the SPCMSC Core Viewer Web Map. This was completed by opening the existing ArcGIS Online web map (generated from the previous publishing methodology) and replacing the prior web service URL to the database with the URL of the new hosted feature layer. In subsequent updates, this web map is automatically updated with new data once the hosted feature layer is published and made public. This web map is also linked to the SPCMSC Core Viewer Web Mapping Application, and automatically updates within the application. The SPCMSC Geologic Core and Sample Database is republished approximately every six months. During republishing, the same steps are followed but the older copy of the database feature class in the geodatabase is renamed to append the date of the last update in YYYYMMDD format. The data source for the group layer is also updated to the new feature class source, which reloads and updates the dataset while preserving the previously set definition queries and symbology. Next, the hosted feature layer is overwritten by clicking on the group layer and selecting Share > Overwrite Web Layer > Publish. Upon overwriting the layer, it is no longer publicly shared, and the samples database manager will send an email to the USGS EGIS team requesting the item be made public again. This may result in a brief downtime, where the database may be unavailable to users. At this time, the ArcGIS Online CSV Collection item is also updated with the newest files by the samples database manager via the ArcGIS Online website.