Contents

Introduction to the SnowEx Database

What we’re gonna attempt to cover

  • Introduction

  • Database Structure/ Contents

  • Forming Useful Queries

  • Examples

  • Exporting Data

  • QGIS setup

Why a database?

”Dude, I am into pits not bits. What gives?!”

  • Standardizing diverse datasets

  • Cross referencing data

  • Enables GIS functionality

  • Ready for use in your code

  • Provenance!

  • Ready for use in a GIS software like ArcGIS or QGIS!

TL;DR Do less wrangling, do more crunching.

What is it exactly?

  • PostgreSQL database

  • PostGIS extension

  • Supports vector and raster data

  • And a host of GIS operations

What’s in it?

note:Data extent is limited to Grand Mesa and in EPSG:26912 for Hackweek!

  • Snow pits - Density, hardness profiles, grain types + sizes

  • Manual snow depths - TONS of depths, Can you say spirals?

  • Snow Micropenetrometer profiles - (Subsampled to every 100th)

  • Snow depth + SWE rasters from ASO inc

  • GPR

  • Pit site notes

  • Camera Derived snow depths

  • Snow off DEM from USGS 3DEP

  • And almost all the associated metadata

All this and more is easily indexed, cross referencable, and put into GIS ready formats!

How do I get at this magical box of data?

# Import the connection function from the snowexsql library
from snowexsql.db import get_db

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

1. Using the Engine Object

The engine object returned from the get_db function is not used much in the snowexsql library. It does allow you to use typical SQL strings to interact with the database.

Note: Users who have used python + SQL before will likely be more familiar with this approach. Additionally those who don’t know python but know SQL will also be more comfortable here.

# Form a typical SQL query and use python to populate the table name
qry = "SELECT DISTINCT site_id FROM sites"

# Then we execute the sql command and collect the results
results = engine.execute(qry)

# Create a nice readable string to print the site names using python 
out = ', '.join((row['site_id'] for row in results))

# Print it with a line return for readability
print(out + '\n')

6N36, 2S7, 2S4, 6S22, 2S6, 9N30, 9S40, 1N1, 9N47, 1N3, 9N29, 8N45, 9N39, 8C35, 1C5, 6N31, 5N24, 3S33, 3N53, 1C7, 7N40, 8N55, 6N18, 5C20, 8N52, 5N10, 2N21, 1N5, 6S32, GML, 2C3, 1S1, 8S41, 6S53, 8C11, 2C13, 8N54, 7S23, 6N16, 8C31, 5S42, 8N34, 6C37, 5S49, 9S39, 2S35, 1S17, 1C8, 8N9, FL2A, 5S31, 8N38, 6S26, 2S46, 8S28, 8C36, 5N15, 2C33, 6N46, 6S15, 8N35, 3S14, TLSFL2A, 2N13, 3N26, 1S8, 3S47, 3S52, 4N2, 2S9, 9S51, 6C24, 5C27, 2S25, FL1B, 9N42, 1N6, 2S11, 2N8, 9N59, 1N7, 8C25, 3S5, 8N58, 9C28, 2S10, 2S45, 5C21, 7S50, 5S24, 8C22, 2N49, 2N14, 9C17, 5N19, 2C9, 5N50, 2N4, 1C14, 2C2, 8S18, 7C15, 2S20, 1S12, 6S44, 2S48, 9C19, 9N43, 9N56, 9N44, 8S30, 8C26, 7N57, 9C16, 3S38, 6S34, 5N11, 4N27, 5S21, 2C12, 2N12, 1S2, 3N22, 9C23, 5S29, 2S3, 2N48, 8N25, 2C4, 2C6, 2S37, 2S16, 1S13, 4C30, 8N37, 6S19, 2S36, 9N28, 1N23, 6N17, 8N51, 1N20, 5N41, 8C32, 5N32, 8N33, 5S43, 6C10, 2S27, 6C34, 8C29, 8C18, 1C1

2. Using the Session Object

The session object allows a user to interact with the database in a pure python form. This approach is called Object Relational Mapping (ORM).

# Import the table classes from our data module which is where our ORM classes are defined 
from  snowexsql.data import SiteData

# Form the query to receive all the site_id from the sites table
qry = session.query(SiteData.site_id).distinct()

# Execute the query and collect the results
results = qry.all()

# Print it with a line return for readability
print(', '.join([row[0] for row in list(results)]))

6N36, 2S7, 2S4, 6S22, 2S6, 9N30, 9S40, 1N1, 9N47, 1N3, 9N29, 8N45, 9N39, 8C35, 1C5, 6N31, 5N24, 3S33, 3N53, 1C7, 7N40, 8N55, 6N18, 5C20, 8N52, 5N10, 2N21, 1N5, 6S32, GML, 2C3, 1S1, 8S41, 6S53, 8C11, 2C13, 8N54, 7S23, 6N16, 8C31, 5S42, 8N34, 6C37, 5S49, 9S39, 2S35, 1S17, 1C8, 8N9, FL2A, 5S31, 8N38, 6S26, 2S46, 8S28, 8C36, 5N15, 2C33, 6N46, 6S15, 8N35, 3S14, TLSFL2A, 2N13, 3N26, 1S8, 3S47, 3S52, 4N2, 2S9, 9S51, 6C24, 5C27, 2S25, FL1B, 9N42, 1N6, 2S11, 2N8, 9N59, 1N7, 8C25, 3S5, 8N58, 9C28, 2S10, 2S45, 5C21, 7S50, 5S24, 8C22, 2N49, 2N14, 9C17, 5N19, 2C9, 5N50, 2N4, 1C14, 2C2, 8S18, 7C15, 2S20, 1S12, 6S44, 2S48, 9C19, 9N43, 9N56, 9N44, 8S30, 8C26, 7N57, 9C16, 3S38, 6S34, 5N11, 4N27, 5S21, 2C12, 2N12, 1S2, 3N22, 9C23, 5S29, 2S3, 2N48, 8N25, 2C4, 2C6, 2S37, 2S16, 1S13, 4C30, 8N37, 6S19, 2S36, 9N28, 1N23, 6N17, 8N51, 1N20, 5N41, 8C32, 5N32, 8N33, 5S43, 6C10, 2S27, 6C34, 8C29, 8C18, 1C1

# Close your session to avoid hanging transactions
session.close()

Crash Course in Object Relational Mapping (ORM)

Question: How is a database used in pure python?!…Are you down with the O.O.P? The answer is as a Class where each column is mapped to that class as an attribute e.g. obj.attribute AND… in the correct type for python!

Consider the following table:

id

site_id

ground_roughness

0

GML

rough

1

2S27

smooth

2

3S52

smooth

In our python repo we have a made up class SiteData defined to map to this table.

     
    from snowexsql.data import SiteData

If we queried the whole table above using the session object we would get back 3 Sitedata objects in a list. 1 for each row.

[<snowexsql.data.SiteData object at 0x7fcf0f9bea90>, <snowexsql.data.SiteData object at 0x7fcf0f9bec70>, <snowexsql.data.SiteData object at 0x7fcf0f9bed00>]

This at first doesn’t seem useful until you start to use the objects.


print(my_queried_data[0].ground_roughness)

rough

Question

  • How would you access from our list the site_id of the 2nd row?

Recap

You just:

  • Accessed a geodatabase using python

  • Saw two methods for interacting with the db using the snowexsql library

  • Pulled all the unique pit site id numbers from the db

  • Had a high level intro to ORM