Retrieve Sites that Surveyors Found Graupel#

Goal: Plot all the pit locations and identify which pits had graupel and which didn’t

Approach:

  1. Find all the pits with graupel in the comments.

  2. Find all pits without graupel mentioned in the comments

  3. Plot them!

Process#

Step 1. Use the LayerData class to find hand hardness profiles that mention graupel#

from snowexsql.data import LayerData, SiteData
from snowexsql.db import get_db
from snowexsql.conversions import query_to_geopandas
import geopandas as gpd
import matplotlib.pyplot as plt 

# Connect to the database
db_name = 'db.snowexdata.org/snowex'

engine, session = get_db(db_name, credentials='./credentials.json')

# Build a query looking at hand hardness profiles, 
q_base = session.query(LayerData).filter(LayerData.type == 'hand_hardness').filter(LayerData.site_name == 'Grand Mesa')

# add on to the query by filtering on comments containing graupel (case insensitive)
qry = q_base.filter(LayerData.comments.contains('graupel'))

# Send query and convert records to a dataframe with the results
df_graupel = query_to_geopandas(qry, engine)

print('Sites with Graupel: {}'.format(', '.join(df_graupel['site_id'])))
Sites with Graupel: 8S41, 8N38, 4C30, 1N7, 2N21, 2S35, 1S17, FL1B, 8N25, 5S21, 2S6, 1C7, 2C12, 1N3, 3S47, 2S11, County Line Open, 1N23, 6S32, County Line Open, County Line Tree, Mesa West Open, Mesa West Open, Mesa West Trees, Mesa West Trees, Skyway Open

Step 2. Use the same query but request the opposite to get pits without graupel#

# Use the exact same query except use ~ to ask for the opposite (pits w/o graupel)
qry= q_base.filter(~LayerData.comments.contains('graupel'))

# There are multiple layers for a given site at different depths and dates. So insure we don't grab a site that on a given date had graupel
qry = qry.filter(~LayerData.date.in_(df_graupel['date']), ~LayerData.site_id.in_(df_graupel['site_id']))

# Send query and convert records to a dataframe with the results
df = query_to_geopandas(qry, engine)

# Close the database session to avoid hanging transactions
session.close()

Step 3. Plot pits with graupel in color and without in grey#