Database Structure#
The data base is formed of 4 tables that end users will query.
points: Contains all data that has a single value with a single coordinate pair (e.g. snow depths).
layers: Contains all data that has a depth component associated to a single coordinate pair (e.g. density profiles).
images: Contains all rasters and any query for a raster tile should be done here.
sites: Contains all the pit site details.
There are other tables available, but they are auto-generated to support the 4 tables above. These other tables are:
geography_columns
geometry_columns
spatial_ref_sys
raster_columns
raster_overviews
Points Table#
The points table contains any data that can be described by a single coordinate pair and a single value.
- Examples of this type of data includes:
snow depths
GPR SWE
GPR two way travel
GPR Depth
This table is mapped to python with snowexsql.data.PointData
.
At a minimum an single entry in python would be similar to the following:
PointData(value=52, easting= 758635.01, northing=4325308.56, type='depth')
This table has the following columns:
date - Date data was collected
easting - UTM projected coordinate in the east direction in meters
elevation - Elevation at the site or acquisition in meters
equipment - String indentifying more info about the instruments used
geom - GIS software friendly version of the coordinates of where the data was collected in UTM.
id - Unique identifier that is automatically assigned when uploaded
instrument - Name of the instrument used to collect the data
latitude - Geographic northing coordinate of the acquisition location in degrees
longitude - Geographic westing coordinate of the acquisition location in degrees
northing - Northing coordinate projected in UTM in meters
site_id - Unique identifier to pit location
site_name - Name describing the general survey area ( e.g. Grand Mesa)
observers - Name of the people who acquired the data
time - Time (MST) that the data was collected
time_updated - Time this entry was last modified
type - Name of the data collected
units - Units of the data collected
utm_zone
value - Value of the data collected
version_number
Layers Table#
The layers table contains all data that can be described by a single coordinate pair, a depth in the snowpack, and a single value. This means that a single entry in the Layers table is a single layer from a vertical profile.
- Examples of this data include:
density profiles
SMP
SSA
temperature
Hand hardness
This table is mapped to python with snowexsql.data.LayerData
.
At a minimum an single entry would be similar to the following:
LayerData(value='300', depth=30, easting= 758635.01, northing=4325308.56, type='density')
NOTE: All values in this table are stored as strings to accommodate a wide range of data.
This table contains the following columns:
bottom_depth
comments
date - Date data was collected
depth - Depth in centimeters in the snowpack that the data was collected
easting - UTM projected coordinate in the east direction in meters
elevation - Elevation at the site or acquisition in meters
flags - data that was flagged typically just pits
geom - GIS software friendly version of the coordinates of where the data was collected in UTM.
id - Unique identifier that is automatically assigned when uploaded
instrument - Name of the instrument used to collect the data
latitude - Geographic northing coordinate of the acquisition location in degrees
longitude - Geographic westing coordinate of the acquisition location in degrees
northing - Northing coordinate projected in UTM in meters
sample_a - 1 of potentially three samples that could have been taken for this measurement, e.g. density
sample_b - 1 of potentially three samples that could have been taken for this measurement, e.g. density
sample_c - 1 of potentially three samples that could have been taken for this measurement, e.g. density
site_id - Unique identifier to pit location
site_name - Name describing the general survey area ( e.g. Grand Mesa)
observers - Names of the people performing the acquisition
time - Time (MST) at the beginning of acquisition
time_created - Time this entry was uploaded
time_updated - Time this entry was last modified
type - Name of the data collected
units - Units of the data collected
utm_zone - UTM Zone
value - Value of the data collected
Images Table#
The images table contains all rasters. Its not called rasters because the tables named raster are reserved keywords for postgis.
- Examples of this include:
DEMS
UAVSAR products
Lidar acquisitions
This table is mapped to python with snowexsql.data.ImageData
.
At a minimum an single entry in python would be similar to the following:
ImageData(raster=<RasterElement>, type='depth')
This table contains the following columns:
date - Date data was collected
description - Any notes to add
id - Unique identifier that is automatically assigned when uploaded
instrument - Name of the instrument used to collect the data
raster - Raster data in Well Known Binary Format (WKB) best generated using raster2psql in the command line
site_id - Unique identifier to pit location
site_name - Name describing the general survey area ( e.g. Grand Mesa)
observers - Names of the people or organization that acquired the data
time_created - Time this entry was uploaded
time_updated - Time this entry was last modified
type - Name of the data collected
units - Units of the data collected
Sites Table#
The sites table contains all the details regarding pit site details. This table is formed exclusively from the SiteDetails.csv files that were provided with stratigraphy.csv and density.csv files.
This table is mapped to python with snowexsql.data.SiteData
.
This table has a lot of columns. They are:
air_temp - Air temperature in degrees C at time of digging the pit
aspect - Slope Aspect in degrees from north
date - Date data was collected
easting - UTM projected coordinate in the east direction in meters
elevation - Elevation at the site or acquisition in meters
geom - GIS software friendly version of the coordinates of where the data was collected in UTM.
ground_condition - Description of the surface below snow
ground_roughness - A description of how rough the surface below the snow is
ground_vegetation - Description of the vegetation below snow
id - Unique identifier that is automatically assigned when uploaded
latitude - Geographic northing coordinate of the acquisition location in degrees
longitude - Geographic westing coordinate of the acquisition location in degrees
northing - Northing coordinate projected in UTM in meters
precip - Description of the precip during pit digging
site_id - Unique identifier to pit location
site_name - Name describing the general survey area ( e.g. Grand Mesa)
site_notes - Any special site specific notes
sky_cover - Description of the cloud cover
slope_angle - Angle of the slope in degrees
time - Time (MST) acquisition began
time_created - Time this entry was uploaded
time_updated - Time this entry was last modified
total_depth - Snow depth at location in centimeters
tree_canopy - Description of the tree canopy at location
utm_zone - UTM zone
vegetation_height - Estimated vegetation height
weather_description - Brief description of the weather during acquisition
wind - Description of the wind during acquisition