Forming Queries: Example Visualizng Depths

During the SnowEx campaigns a TON of manual snow depths were collected, surveys for hackweek showed an overhelming interest in the manual snow depths dataset. This tutorial shows how easy it is to get at that data in the database while learning how to build queries

Don’t forget your cheat sheets!

Goal: Visualize a small subset of snow depths


  1. Connect to the DB

  2. Build a query filtering by dataset and date

  3. 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 points table
from import PointData

# 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: Build a query

# Pick a dataset
dataset = 'depth'

# Pick a date
collection_date = date(2020, 2, 7)

# Site name
site_name = "Grand Mesa"

# Get a session
engine, session = get_db(db_name)

# The part inside the query function is what we want back, in this case all columns for the point data
qry = session.query(PointData)

# Filter by site
qry = qry.filter(PointData.site_name == site_name)

# We then want to filter by the selected the data type depth.
qry = qry.filter(PointData.type == dataset)

# Filter by a date
qry = qry.filter( == collection_date)

# Limit it to a couple hundred - just for exploration
qry = qry.limit(200)

# Execute the query and convert to geopandas in one handy function
df = query_to_geopandas(qry, engine)

# how many did we retrieve?
print(f'{len(df.index)} records returned!')

200 records returned!

Step 3: Plot it!

Lets try to filter to get the data to show only a depth spiral.

# Let see what instruments are available 
result = session.query(PointData.instrument).filter(PointData.type == 'depth').distinct().all()
[(None,), ('Mala 1600 MHz GPR',), ('Mala 800 MHz GPR',), ('pulse EKKO Pro multi-polarization 1 GHz GPR',), ('pit ruler',), ('mesa',), ('magnaprobe',), ('camera',)]

Try This: Go back and add a filter to reduce to just one spiral. Do you know what instrument was used to make depth spirals?


You just plotted snow depths and reduce the scope of the data by compounding filters on it

You should know:

  • How to build queries using filtering

  • Where a useful tools like query_to_geopandas live in the snowexsql library

If you don’t feel comfortable with these, you are probably not alone, let’s discuss it!