Navigating the Database #

The tables in the AKVEG Database can be broadly classified into data tables, referenced tables, and metadata tables.

Data tables contain information on vegetation, environmental, and soils characteristics, as well as information about the plots at which these data were collected (e.g., plot coordinates, dimensions).

Referenced tables are referenced by the data tables to constrain the set of allowable values in a field. For example, the cover type id field in the vegetation_cover table references the cover_type table. The cover_type table specifies the types of methods that can be used to collect cover data.

Finally, metadata tables contain information about the database itself:

  1. database_schema: Information on the structure of the database, including field descriptions, relationships between tables, and specification of null values.
  2. database_dictionary: List and definitions of constrained values.

As with all tables, the metadata tables can be accessed at any time by running the following query: sql: SELECT * FROM table_name;

The AKVEG Database follows the Minimum Standards for Field Observation of Vegetation and Related Properties developed by the Alaska Vegetation Working Group (VWG). The Minimum Standards for Field Observation provide additional information on data collection standards and how the fields relate to data tiers.

Overview #

There are 13 data tables in the AKVEG Database:

Project & Site

  1. Project
  2. Site
  3. Site Visit
  4. Photos (not part of database structure)

Vegetation

  1. Vegetation Cover
  2. Abiotic Top Cover
  3. Whole Tussock Cover
  4. Ground Cover
  5. Structural Group Cover
  6. Tree Structure
  7. Shrub Structure

Environment

  1. Environment
  2. Soil Metrics
  3. Soil Horizons

Tables 1, 2, and 3 contain general information on the sites at which data were collected and the project the sites belong to. The remaining tables contain information on vegetation, environmental, and soils characteristics.

The Photos table is still under development and is not currently included in the AKVEG database.

Project & Site Schema #

Below is a simplified diagram that illustrates how the project, site, and site_visit tables are related to each other.

The site_visit_code, first specified in the site_visit table, is the field that all subsequent tables (Tables 5-14) refer to. In contrast, the site and project tables are not referred to beyond these initial three tables. You will likely want to consult the Site and Project to obtain coordinates and plot dimensions (in the Site table) and for general information on the projects that the data belong to (in the Project table).

erDiagram
direction RL
project {
varchar project_code
}
site {
varchar establishing_project_code
varchar site_code

    }
    site_visit {
        varchar project_code
        varchar site_code
        varchar site_visit_code
    }
    site_visit }o--|| project : " "
    site_visit }o--|| site : " "
    site }o--|| project : ""

Project Table #

The project table provides information about the overall projects that the data were collected for. This table includes information on the organization(s) that collected the data (originator_id) and funded the project (funder_id), as well as the years during which the data collection took place (year_start, year_end), a short description of the purpose of the project (project_description), and whether the data is publicly available (private). Only public data is included in the AKVEG Database that is available to the public.

This table references the following metadata tables: organization, personnel, and completion.


erDiagram
direction TB
project {
varchar project_code PK
varchar project_name UK
smallint originator_id FK
smallint funder_id FK
smallint manager_id FK
smallint completion_id FK
smallint year_start
smallint year_end
varchar project_description
boolean private
}
project }o--|| organization : originator_id
project }o--|| organization : funder_id
project }o--|| personnel : manager_id
project }o--|| completion : completion_id

Site Table #

The site table provides information about the site at which data were collected. In addition to the site_code, which is used to identify unique sites, the table contains information on:

  • Perspective (ground or aerial)
  • Cover method (e.g., visual estimate, line-point intercept)
  • Plot coordinates
  • Plot dimensions

All fields except for the site_code field refer to constrained values that are specified in the project table or in the following metadata tables: perspective, cover_method, plot_dimensions, h_datum, positional_accuracy, location_type.

There is only one possible value for h_datum_epsg: 4269 (NAD83). All coordinates are converted to this datum prior to ingestion in the database.

Open Site table in fullscreen for larger viewing.


erDiagram
direction TB
site {
varchar site_code PK
varchar establishing_project_code FK
smallint perspective_id FK
smallint cover_method_id FK
smallint plot_dimensions_id FK
integer h_datum_epsg FK
decimal latitude_dd
decimal longitude_dd
decimal h_error_m
smallint positional_accuracy_id FK
smallint location_type_id FK
}
site }o--|| project : establishing_project_code
site }o--|| perspective : perspective_id
site }o--|| cover_method : cover_method_id
site }o--|| plot_dimensions : plot_dimensions_id
site }o--|| h_datum : h_datum_epsg
site }o--|| positional_accuracy : positional_accuracy_id
site }o--|| location_type : location_type_id

Site Visit Table #

The site visit table contains the site_visit_code, which identifies unique site visits. This site visit code is used as a foreign key in all subsequent tables (Tables 5-14). The site visit table also includes information on the taxonomic scope of the survey, the date of the site visit, the personnel that were involved, and the dominant vegetation class at the time of the visit.

To simplify the diagram, we do not show all of the relationships between the site visit table and the Scope and Personnel tables. Fields with the ‘scope’ prefix refer to the scope table. Fields that refer to people (i.e., that include the word ‘observer’ or ‘recorder’) refer to the personnel table.

erDiagram
direction TB
site_visit {
varchar site_visit_code PK
varchar project_code FK
varchar site_code FK
smallint data_tier_id FK
smallint scope_vascular_id FK
smallint scope_bryophyte_id FK
smallint scope_lichen_id FK
varchar observe_date
smallint veg_observer_id FK
smallint veg_recorder_id FK
smallint env_observer_id FK
smallint soils_observer_id FK
varchar structural_class_code FK
boolean homogeneous
}
site_visit }o--|| project : project_code
site_visit }o--|| site : site_code
site_visit }o--|| data_tier : data_tier_id
site_visit }o--|| scope : scope_vascular_id
site_visit }o--|| personnel : veg_observer_id
site_visit }o--|| structural_class : structural_class_code

Cover Tables #

Vegetation Cover #

The vegetation cover table contains data on the vegetation (vascular plants, bryophytes, and lichens) encountered during site visits. Both the original taxonomic name that was recorded in the source data (name_original) and the accepted taxonomic code (code_adjudicated) are provided.

There are two instances where a site visit will contain multiple entries for the same accepted taxonomic code:

  • If the taxon was encountered in both ‘dead’ and ’live’ forms. In these cases, the taxon will have two entries that will differ on the basis of dead_status.
  • If there were different original names provided that now refer to the same accepted name. In these cases, the taxon will have two entries that will differ on the basis of name_original.

Join the vegetation_cover table with cover_type on cover_type_id to retrieve the descriptive names of each cover‑type method, and with taxon_all on code_adjudicated to retrieve the taxonomic name. We also recommend joining taxon_all with taxon_accepted on taxon_accepted_code to retrieve the current accepted names in the event that the taxonomy has changed since the dataset was processed. Refer to the Vegetation Cover SQL query for a ready-made implementation.

erDiagram
direction TB
vegetation_cover {
serial vegetation_cover_id PK
varchar site_visit_code FK
smallint cover_type_id FK
varchar name_original
varchar code_adjudicated FK
decimal cover_percent
boolean dead_status
}
vegetation_cover }o--|| site_visit : site_visit_code
vegetation_cover }o--|| cover_type : cover_type_id
vegetation_cover }o--|| taxon_all : code_adjudicated

Abiotic Top Cover #

The abiotic top cover table records the percent top cover for each abiotic element class within a plot. Abiotic top cover is defined as the proportional area covered by the abiotic element and not overtopped by any vegetation (i.e., visible from an aerial perspective). The sum of all abiotic top cover measurements within a plot cannot exceed 100%.

The abiotic element code references the ground_element table because the abiotic elements are a subset of ground elements. Join the abiotic_top_cover table with ground_element to obtain the descriptive names of the abiotic/ground element codes. Refer to the Abiotic Top Cover SQL query for a ready-made implementation.

erDiagram
direction LR
abiotic_top_cover {
serial abiotic_cover_id PK
varchar site_visit_code FK
varchar abiotic_element_code FK
decimal abiotic_top_cover_percent
}
abiotic_top_cover }o--|| site_visit : site_visit_code
abiotic_top_cover }o--|| ground_element : abiotic_element_code

Whole Tussock Cover #

The whole tussock cover table records the proportional ground area covered by tussock structures within each plot. Whole tussock cover includes both live and dead plant material and the tussock base. It can only be reported as either absolute canopy cover or top canopy cover.

Join the whole_tussock_cover table with cover_type on cover_type_id to retrieve the descriptive names of each cover‑type method. Refer to the Whole Tussock Cover SQL query for a ready-made implementation.

erDiagram
direction LR
whole_tussock_cover {
serial tussock_id PK
varchar site_visit_code FK
smallint cover_type_id FK
decimal cover_percent
}
whole_tussock_cover }o--|| site_visit : site_visit_code
whole_tussock_cover }o--|| cover_type : cover_type_id

Ground Cover #

The ground cover table records the percent cover for each ground element within a plot. Ground cover percent is defined as the proportional area of ground covered by the ground element at the soil surface. The sum of all ground cover measurements must equal 100%.

Join the ground_cover table with ground_element on ground_element_code to retrieve the descriptive names of each ground element. Refer to the Ground Cover SQL query for a ready-made implementation.

erDiagram
direction LR
ground_cover {
serial ground_cover_id PK
varchar site_visit_code FK
varchar ground_element_code FK
decimal ground_cover_percent
}
ground_cover }o--|| site_visit : site_visit_code
ground_cover }o--|| ground_element : ground_element_code

Structure Tables #

Structural Group Cover #

The structural group cover table records the percent cover of each structural group within a plot. Cover type must be either absolute foliar cover or absolute canopy cover. Top cover types are invalid because measurements represent vegetation structure, which includes multiple canopy strata - not just the top stratum.

Join the structural_group_cover table with cover_type on cover_type_id to retrieve the descriptive names of each cover‑type method. Join structural_group_cover with structural_group on structural_group_id to retrieve the descriptive names of each structural group element. Refer to the Structural Group SQL query for a ready-made implementation.

erDiagram
direction TB
structural_group_cover {
serial structural_cover_id PK
varchar site_visit_code FK
smallint cover_type_id FK
smallint structural_group_id FK
decimal cover_percent
}

structural_group_cover }o--|| site_visit : site_visit_code
structural_group_cover }o--|| cover_type : cover_type_id
structural_group_cover }o--|| structural_group : structural_group_id

Tree Structure #

The tree structure table records characteristics for tree species that occur within a plot and within a crown class stratum. Top cover types are invalid for this table as the measurements represent woody vegetation structure across all strata, rather than just the top stratum. Field definitions are provided in Section X. Tree Structure in the Minimum Standards for Field Observation (second mention).

Note: As of 2025-08-20, there are no records in the Tree Structure table.

erDiagram
direction TB
tree_structure {
serial tree_structure_id PK
varchar site_visit_code FK
varchar name_original
varchar code_adjudicated FK
smallint crown_class_id FK
smallint height_type_id FK
decimal height_cm
smallint cover_type_id FK
decimal cover_percent
decimal mean_dbh_cm
smallint number_stems
smallint mean_tree_age_y
decimal tree_subplot_area_m2
}

tree_structure }o--|| site_visit : site_visit_code
tree_structure }o--|| taxon_all : code_adjudicated
tree_structure }o--|| crown_class : crown_class_id
tree_structure }o--|| height_type : height_type_id
tree_structure }o..|| cover_type : cover_type_id

Shrub Structure #

The shrub structure table records characteristics for shrub species that occur within a plot and within a shrub class stratum. Top cover types are invalid for this table as the measurements represent woody vegetation structure across all strata, rather than just the top stratum. Field definitions are provided in Section XI. Shrub Structure in the Minimum Standards for Field Observation (second mention).

Refer to the Shrub Structure SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the shrub structure table.

erDiagram
direction TB
shrub_structure {
  serial shrub_structure_id PK
  varchar site_visit_code FK
  varchar name_original
  varchar code_adjudicated FK
  smallint shrub_class_id FK
  smallint height_type_id FK
  decimal height_cm
  smallint cover_type_id FK
  decimal cover_percent
  decimal mean_diameter_cm
  smallint number_stems
  decimal shrub_subplot_area_m2
}

shrub_structure }o--|| site_visit : site_visit_code
shrub_structure }o--|| taxon_all : code_adjudicated
shrub_structure }o--|| shrub_class : shrub_class_id
shrub_structure }o--|| height_type : height_type_id
shrub_structure }o..|| cover_type : cover_type_id

Environment Table #

The environment table records various environmental characteristics of a plot at the time of the site visit. Data on a plot’s physical geography, soil-hydrologic regime, and primary disturbance process are included.

Here, we show a simplified diagram of the environment table that omits most of the relationships between this table and the referenced tables. The omitted relationships are straightforward: Fields that end in _id are foreign keys. The referenced table has the same name as the field, minus the _id suffix. For example, the microtopography_id refers to the microtopography table, while the disturbance_severity_id refers to the disturbance_severity table. The join field in the referenced table is the same as the foreign key. Refer the Environment SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the environment table. You can also view all relationships by opening the Environment table in fullscreen.

erDiagram
direction LR
  environment {
    serial environment_id PK
    varchar site_visit_code FK
    smallint physiography_id FK
    smallint geomorphology_id FK
    smallint macrotopography_id FK
    smallint microtopography_id FK
    decimal microrelief_cm
    smallint drainage_id FK
    smallint moisture_id FK
    decimal depth_water_cm
    decimal depth_moss_duff_cm
    decimal depth_restrictive_layer_cm
    smallint restrictive_type_id FK
    smallint disturbance_id FK
    smallint disturbance_severity_id FK
    smallint disturbance_time_y
    boolean surface_water
    smallint soil_class_id FK
    boolean cryoturbation
    varchar dominant_texture_40_cm_code FK
    decimal depth_15_percent_coarse_fragments_cm
    }

    environment }o--|| site_visit : site_visit_code
    environment }o--|| physiography : physiography_id
    environment }o--|| restrictive_type : restrictive_type_id
    environment }o..|| soil_texture : dominant_texture_40_cm_code

Soils Tables #

Soil Metrics #

The soil metrics table records measurements on pH, conductivity, and temperature within a plot. Where the water table is at or above the soil surface, the water can be measured instead. In these cases, the value of the water_measurement field is True.

Refer to the Soil Metrics SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the soil metrics table.

erDiagram
direction TB
  soil_metrics {
    serial soil_metric_id PK
    varchar site_visit_code FK
    boolean water_measurement
    decimal measure_depth_cm
    decimal ph
    decimal conductivity_mus
    decimal temperature_deg_c
    }

soil_metrics }o--|| site_visit : site_visit_code

Soil Horizons #

The soil horizons table records a soil horizon’s position and its physical characteristics within a plot. Both quantitative and qualitative data are recorded, including horizon depth, thickness, color, and texture. Measurements on pH, conductivity, and temperature are included in the soil metrics table, rather than the soil horizons table.

Here, we show a simplified diagram of the environment table that omits most of the relationships between this table and the referenced tables. The horizon_primary_code and horizon_secondary_code both refer to the soil_horizon_type table. Fields starting with horizon_suffix refer to the soil_horizon_suffix table. The texture_code and structure_code foreign keys refer to the soil_texture and soil_structure tables, respectively.

Refer to the Soil Horizons SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the soil horizons table. You can also view all relationships by opening the Soil Horizons table in fullscreen.

erDiagram
direction LR

  soil_horizons {
        serial soil_horizon_id PK
        varchar site_visit_code FK
        smallint horizon_order
        decimal thickness_cm
        decimal depth_upper_cm
        decimal depth_lower_cm
        boolean depth_extend
        varchar horizon_primary_code FK
        varchar horizon_suffix_1_code FK
        varchar horizon_suffix_2_code FK
        varchar horizon_secondary_code FK
        varchar horizon_suffix_3_code FK
        varchar horizon_suffix_4_code FK
        varchar texture_code FK
        decimal clay_percent
        decimal total_coarse_fragment_percent
        decimal gravel_percent
        decimal cobble_percent
        decimal stone_percent
        decimal boulder_percent
        varchar structure_code FK
        varchar matrix_hue_code FK
        decimal matrix_value
        smallint matrix_chroma
        varchar nonmatrix_feature_code FK
        varchar nonmatrix_hue_code FK
        decimal nonmatrix_value
        smallint nonmatrix_chroma
    }

    soil_horizons }o--|| site_visit : site_visit_code
    soil_horizons }o..|| soil_horizon_type : horizon_primary_code
    soil_horizons }o..|| soil_horizon_suffix : horizon_suffix_1_code
    soil_horizons }o..|| soil_hue : matrix_hue_code
    soil_horizons }o..|| soil_nonmatrix_features : nonmatrix_feature_code
    soil_horizons }o..|| soil_hue : nonmatrix_hue_code
Previous Getting Started Contribute Data Next