Contents

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

Approach:

  1. Connect to the DB

  2. Build a query filtering by dataset and date

  3. 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 points table
from snowexsql.data 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 = 'snow:hackweek@db.snowexdata.org/snowex'

# Using the function get_db, we receive 2 ways to interact with the database
engine, session = get_db(db_name)

Step 2: Build a query

# Pick a dataset
dataset = 'depth'

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

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

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

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

# Limit it to a couple thousand 
qry = qry.limit(2000)

# 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!')

304 records returned!

Step 3: Plot it!

# Get the Matplotlib Axes object from the dataframe object, color the points by snow depth value
ax = df.plot(column='value', legend=True, cmap='PuBu')

# Use non-scientific notation for x and y ticks
ax.ticklabel_format(style='plain', useOffset=False)

# Set the various plots x/y labels and title.
ax.set_title(f'{len(df.index)} {dataset.title()}s collected on {collection_date.strftime("%Y-%m-%d")}')
ax.set_xlabel('Easting [m]')
ax.set_ylabel('Northing [m]')

# Close the session to avoid hanging transactions
session.close()
../../_images/4_get_spiral_example_6_0.png

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()
print(result)

[('mesa',), ('magnaprobe',), ('camera',), ('pulse EKKO Pro multi-polarization 1 GHz GPR',), ('pit ruler',)]

Try This:

  1. Change the date

Hint: You may need to form two queries just to see what dates and surveyors are in the db

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?

Recap

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!

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