Database Management

SQL is a domain-specific language used in managing data held in relational database management systems or creating streamlined management systems. This project focused on updating and developing infrastructure for a continuous forest inventory and owl survey inventory.

Client
The Conservation Fund

Date Completed
Fall 2021

Role
Geospatial Analyst

Task objective

To develop a streamlined workflow that makes collecting and monitoring in-field measurements quick and easy. Separate databases were created for forest carbon measurement inventories and for Northern Spotted Owl (NSO) survey inventories.

Data Flow

Currently, The Conservation Fund uses multiple data tables for tree and plot level information, each in different Microsoft Access databases based on reporting period. The new, updated database is based in SQL and will have one table that compiles tree and plot level information for all reporting periods.

The SQL database framework also performs quality control and quality assessment (QAQC) on incoming in-field measurements. The QAQC checks for errors in the data such as typos or outliers and ensures new field measurement data is consistent with the previously measured data that is already in the database (ie. same number of columns and column names). 

Information in this database are formatted to be compatible with external models and calculations (ie. carbon storage). The external models are established and using these in their original state ensures consistency in how carbon and modeled data is generated.

Documentation

Every step of the database creation was documented so that others within the organization can understand why each step was taken and how to create their own database for similar projects. This documentation includes:

  • Necessary project background information
  • Task objectives
  • File and folder naming schemes and description of QAQC processing
  • Notes and how-tos for changing schemas and parts of the scripts
  • Flow chart visualizing the flow of data in and out of the database and external carbon models
  • Notes on any issues or things to consider throughout the process
  • Example visuals of how the information in the database can be shown and applied
  • Additional resources and references used throughout the process
  • Access to the GitHub repository