Bonus Challenge: Analyzing Pits
Contents
Bonus Challenge: Analyzing Pits¶
LayerData, like pits, have some nuance. This challenge will give us some chance to explore the data and get some practice querying and plotting. This can be done as a small group exercise.
Don’t forget your cheat sheets!
Goal: Get more familiar with LayerData and create a vertical profile plot of density
Approach:
Connect to the DB
Explore the data
Build a query filtering to the dataset you want
Convert to a GeoDataFrame and plot
Process¶
Step 1: Get connected¶
# Import the function to get connect to the db
from snowexsql.db import get_db
# Import our class for the layer data
from snowexsql.data import LayerData
# Import a useful function to format that data into a dataframe
from snowexsql.conversions import query_to_geopandas
# Import some tools to build dates
from datetime import date
# This is what you will use for all of hackweek to access the db
db_name = 'snow:hackweek@db.snowexdata.org/snowex'
Step 2: Explore the data¶
from snowexsql.db import get_table_attributes
# print the columns available
db_columns = get_table_attributes(LayerData)
print("These are the available columns in the table:\n \n* {}\n".format('\n* '.join(db_columns)))
These are the available columns in the table:
* bottom_depth
* comments
* date
* date_accessed
* depth
* doi
* easting
* elevation
* flags
* geom
* instrument
* latitude
* longitude
* metadata
* northing
* observers
* pit_id
* registry
* sample_a
* sample_b
* sample_c
* site_id
* site_name
* time
* time_created
* time_updated
* type
* units
* utm_zone
* value
Find the site names and site ids¶
# Connect
engine, session = get_db(db_name)
# Find the distinct site names
result = session.query(LayerData.site_name).filter(LayerData.type == 'density').distinct().all()
site_names = [r[0] for r in result]
# Find the distinct site_names for the site
print(site_names)
# Close session
session.close()
['American River Basin', 'Boise River Basin', 'Cameron Pass', 'East River', 'Fraser Experimental Forest', 'Grand Mesa', 'Jemez River', 'Little Cottonwood Canyon', 'Mammoth Lakes', 'Niwot Ridge', 'Sagehen Creek', 'Senator Beck']
# Connect
engine, session = get_db(db_name)
# We can filter to a site_name, change this to whichever value you want as the site name order may not be consistent
site_name = site_names[0]
# Find the distinct site ids for a site_name
result = session.query(LayerData.site_id)
result = result.filter(LayerData.type == 'density')
result = result.filter(LayerData.site_name == site_name)
result = result.distinct().all()
site_ids = [r[0] for r in result]
# Find the distinct site_ids for the site
print(site_ids)
# Close session
session.close()
['Caples Lake']
Step 3: Build a query to get the values for 1 pit¶
A few things to keep in mind
You will need to filter to one site_id and one unique date OR one pit_id
You will need the density and depth columns to create a vertical profile
# Your code here
pass
Step 4: Convert to a GeoDataFrame and plot¶
# Use the query you built to build a GeoDataFrame and plot the data
# Your code here
pass
Extra: Can you get the bulk density of the snowpack?¶
# Your code here
pass
# a block for closing errant sessions
session.close()