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


  1. Connect to the DB

  2. Explore the data

  3. Build a query filtering to the dataset you want

  4. Convert to a GeoDataFrame and plot


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 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 = ''

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

# Close session
['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

# Close session
['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

Step 4: Convert to a GeoDataFrame and plot

Extra: Can you get the bulk density of the snowpack?

# Your code here
# a block for closing errant sessions