{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Introduction to the SnowEx Database "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"\n",
"## What we're gonna attempt to cover\n",
"* Introduction \n",
"* Database Structure/ Contents \n",
"* Forming Useful Queries \n",
"* Examples \n",
"* Exporting Data \n",
"* QGIS setup \n",
"\n",
"## Why a database?\n",
"> *\"Dude, I am into pits not bits. What gives?!\"*\n",
"\n",
"- Standardizing diverse datasets\n",
"- Cross referencing data\n",
"- Enables GIS functionality\n",
"- Ready for use in your code\n",
"- Provenance!\n",
"- Ready for use in a GIS software like ArcGIS or QGIS!\n",
"\n",
"### TL;DR Do less wrangling, do more crunching. \n",
"\n",
"\n",
"## What is it exactly?\n",
"\n",
"* PostgreSQL database\n",
"* PostGIS extension\n",
"* Supports vector and raster data\n",
"* And a host of GIS operations\n",
"\n",
"## What's in it?\n",
"\n",
"**note:**`Data extent is limited to Grand Mesa and in EPSG:26912 for Hackweek!`\n",
"\n",
"* Snow pits - Density, hardness profiles, grain types + sizes\n",
"* Manual snow depths - TONS of depths, Can you say spirals?\n",
"* Snow Micropenetrometer profiles - (Subsampled to every 100th)\n",
"* Snow depth + SWE rasters from ASO inc\n",
"* GPR\n",
"* Pit site notes\n",
"* Camera Derived snow depths\n",
"* Snow off DEM from USGS 3DEP \n",
"* And almost all the associated metadata\n",
"\n",
"**All this and more is easily indexed, cross referencable, and put into GIS ready formats!**\n",
"\n",
"![](https://snowexsql.readthedocs.io/en/latest/_images/gallery_overview_example_12_0.png)\n",
"\n",
"\n",
"## How do I get at this magical box of data?\n",
"\n",
"* [SQL](https://www.postgresql.org/docs/13/tutorial-sql.html) \n",
"* [snowexsql](https://github.com/SnowEx/snowexsql/) **←**"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Import the connection function from the snowexsql library\n",
"from snowexsql.db import get_db\n",
"\n",
"# This is what you will use for all of hackweek to access the db\n",
"db_name = 'snow:hackweek@db.snowexdata.org/snowex'\n",
"\n",
"# Using the function get_db, we receive 2 ways to interact with the database\n",
"engine, session = get_db(db_name)\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 1. Using the Engine Object\n",
"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 \n",
"strings to interact with the database. \n",
"\n",
"**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.\n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Form a typical SQL query and use python to populate the table name\n",
"qry = \"SELECT DISTINCT site_id FROM sites\"\n",
"\n",
"# Then we execute the sql command and collect the results\n",
"results = engine.execute(qry)\n",
"\n",
"# Create a nice readable string to print the site names using python \n",
"out = ', '.join((row['site_id'] for row in results))\n",
"\n",
"# Print it with a line return for readability\n",
"print(out + '\\n')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### 2. Using the Session Object\n",
"The session object allows a user to interact with the database in a pure python form. This approach is called Object Relational Mapping (ORM)."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Import the table classes from our data module which is where our ORM classes are defined \n",
"from snowexsql.data import SiteData\n",
"\n",
"# Form the query to receive all the site_id from the sites table\n",
"qry = session.query(SiteData.site_id).distinct()\n",
"\n",
"# Execute the query and collect the results\n",
"results = qry.all()\n",
"\n",
"# Print it with a line return for readability\n",
"print(', '.join([row[0] for row in list(results)]))"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"# Close your session to avoid hanging transactions\n",
"session.close()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Crash Course in Object Relational Mapping (ORM)\n",
"\n",
"**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!\n",
"\n",
"Consider the following table:\n",
"\n",
"\n",
"| id | site_id | ground_roughness |\n",
"| ----|---------| -----------------|\n",
"| 0 | GML | rough | \n",
"| 1 | 2S27 | smooth | \n",
"| 2 | 3S52 | smooth | \n",
"\n",
"\n",
"In our python repo we have a made up class `SiteData` defined to map to this table.\n",
"\n",
"``` python \n",
" \n",
" from snowexsql.data import SiteData\n",
"``` \n",
"\n",
"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. \n",
"\n",
"``` console\n",
"[, , ]\n",
"```\n",
"\n",
"This at first doesn't seem useful until you start to use the objects.\n",
"\n",
"``` python \n",
"\n",
"print(my_queried_data[0].ground_roughness)\n",
"```\n",
"\n",
"``` console\n",
"rough\n",
"```\n",
"\n",
"**Question**\n",
"\n",
"* How would you access from our list the `site_id` of the 2nd row?\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Recap\n",
"\n",
"You just:\n",
"\n",
"* Accessed a geodatabase using python \n",
"* Saw two methods for interacting with the db using the snowexsql library\n",
"* Pulled all the unique pit site id numbers from the db \n",
"* Had a high level intro to ORM"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.8"
}
},
"nbformat": 4,
"nbformat_minor": 4
}