AGI Logo

Return to Title page

Appendix 6b: State Geological Survey Reporting Schema

Date updated: 2025-02-05

Economic Analysis of the Costs and Benefits of Geological Mapping in the United States of America from 1994 to 2019

Appendix written by: Nevada Bureau of Mines and Geology, University of Nevada, Reno

This database contains self-reported information on funding, mapping activity, and web metrics from State Geological Surveys and the USGS. Below metadata describe the tables and columns of sgs_reporting.db (DuckDB), which supplements the Economic Analysis of the Costs and Benefits of Geological Mapping (2024) report. The accompanying Microsoft Access (.accdb) database adheres to the below schema, however, column data types may differ where incompatible with DuckDB. Comma Separated Value (.csv) exports are provided for all tables within the database. Corresponding data dictionary tables are located in csv/other_tables/.

License:

This database is made available under the Open Database License: http://opendatacommons.org/licenses/odbl/1.0/. Any rights in individual contents of the database are licensed under the Database Contents License: http://opendatacommons.org/licenses/dbcl/1.0/

Database metadata:

agency

  • shape: 51 rows, 6 columns
  • description: agency name and staff/student population
agency.idx
    dtype: INTEGER
    domain: None
    description: auto-number primary key
agency.state_id
    dtype: VARCHAR
    domain: None
    description: two-letter postal abbreviation for state that serves as a relational and grouping key throughout the database
agency.agency_name
    dtype: VARCHAR
    domain: None
    description: full name of agency
agency.geologists
    dtype: FLOAT
    domain: None
    description: number of 100% FTE geologist staff
agency.non_geologists
    dtype: FLOAT
    domain: None
    description: number of 100% FTE non-geologist staff
agency.students
    dtype: FLOAT
    domain: None
    description: number of students

data_dictionary

  • shape: 3 rows, 4 columns
  • description: long-form values for encoded data
data_dictionary.idx
    dtype: INTEGER
    domain: None
    description: auto-number primary key
data_dictionary.table
    dtype: VARCHAR
    domain: None
    description: table containing encoded data
data_dictionary.alias
    dtype: VARCHAR
    domain: None
    description: encoding alias value
data_dictionary.description
    dtype: VARCHAR
    domain: None
    description: full description of value

derivatives

  • shape: 1332 rows, 6 columns
  • description: status of derivative mapping products/datasets as of 2019
derivatives.idx
    dtype: INTEGER
    domain: None
    description: auto-number primary key
derivatives.state_id
    dtype: VARCHAR
    domain: None
    description: two-letter postal abbreviation for state that serves as a relational and grouping key throughout the database
derivatives.derivative
    dtype: VARCHAR
    domain: None
    description: type of derivative product
derivatives.500k_or_smaller
    dtype: VARCHAR[]
    domain: ['a', 'b', 'c']
    description: status at scales 500k or smaller
derivatives.101k_to_499k
    dtype: VARCHAR[]
    domain: ['a', 'b', 'c']
    description: status at scales 101k to 499k
derivatives.100k_or_larger
    dtype: VARCHAR[]
    domain: ['a', 'b', 'c']
    description: status at scales 100k or larger

funding

  • shape: 1331 rows, 7 columns
  • description: funding amounts received per year as reported by agency for years 1994-2019
funding.idx
    dtype: INTEGER
    domain: None
    description: auto-number primary key
funding.state_id
    dtype: VARCHAR
    domain: None
    description: two-letter postal abbreviation for state that serves as a relational and grouping key throughout the database
funding.year
    dtype: INTEGER
    domain: None
    description: year pertaining to funding reported 
funding.federal
    dtype: FLOAT
    domain: None
    description: amount of federal funding (USD) received in year
funding.state
    dtype: FLOAT
    domain: None
    description: amount of state funding (USD) received in year
funding.other
    dtype: FLOAT
    domain: None
    description: amount of other funding (USD) received in year
funding.total
    dtype: FLOAT
    domain: None
    description: amount of total funding (USD) received in year

mapping

  • shape: 362 rows, 9 columns
  • description: statistics on mapping completion for various map scales as of 2019
mapping.idx
    dtype: INTEGER
    domain: None
    description: auto-number primary key
mapping.state_id
    dtype: VARCHAR
    domain: None
    description: two-letter postal abbreviation for state that serves as a relational and grouping key throughout the database
mapping.scale
    dtype: VARCHAR
    domain: None
    description: map scale
mapping.total_sq_mi_cumulative
    dtype: FLOAT
    domain: None
    description: square miles mapped at specified scale
mapping.perc_state_area_cumulative
    dtype: FLOAT
    domain: None
    description: percent of total state area mapped at specified scale
mapping.total_sq_mi_quaternery
    dtype: FLOAT
    domain: None
    description: square miles of quaternary units mapped at specified scale
mapping.perc_state_area_quaternery
    dtype: FLOAT
    domain: None
    description: percent of total state area comprised of mapped quaternary units at specified scale
mapping.total_sq_mi_bedrock
    dtype: FLOAT
    domain: None
    description: square miles of bedrock units mapped at specified scale
mapping.perc_state_area_bedrock
    dtype: FLOAT
    domain: None
    description: percent of total state area comprised of mapped bedrock units at specified scale

web

  • shape: 1479 rows, 8 columns
  • description: metrics on web views and downloads per year for years 1994-2022
web.idx
    dtype: INTEGER
    domain: None
    description: auto-number primary key
web.state_id
    dtype: VARCHAR
    domain: None
    description: two-letter postal abbreviation for state that serves as a relational and grouping key throughout the database
web.year
    dtype: INTEGER
    domain: None
    description: year pertaining to metrics reported 
web.site_views
    dtype: INTEGER
    domain: None
    description: number of visits to primary agency web site
web.site_views_unique_users
    dtype: INTEGER
    domain: None
    description: number of visits to primary agency web site by unique IP addresses
web.map_views
    dtype: INTEGER
    domain: None
    description: number of views for online map products
web.map_downloads
    dtype: INTEGER
    domain: None
    description: number of downloads for online map products
web.map_sales
    dtype: INTEGER
    domain: None
    description: number of online sales for map-related products (such as print products)

Table of Contents