How is the Database Structured?¶

The goal of the database is to hold as much of the SnowEx data in one place and make it easier to do research with. With that in mind follow the steps below to see how the the data base is structured.

What were about to do¶

Access the database using the snowexsql python library
Query the database to see the underlying tables
Query each table to see what columns are available
Query to see what datasets are available

Process¶

Step 1: Get a database session¶

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

Step 2: Query the DB to see what tables are available¶

# Output the list of tables in the database 
engine.table_names()

['spatial_ref_sys', 'layers', 'points', 'images', 'sites']

We can also import classes that reflect these tables in python!

from snowexsql.data import LayerData, PointData, ImageData, SiteData

Step 3: Query a Table to see what columns you can use!¶

In our python library snowexsql there are classes that reflect the database tables. This makes it easier to use in python. For google purposes this is also called Object Relational Mapping (ORM).

Import the table class from snowexsql.data and snowexsql.db.get_table_attributes. The use get_table_attributes to see what columns are in each table!

# Import the class reflecting the points table in the db
from snowexsql.data import PointData

# Import the function to investigate a table
from snowexsql.db import get_table_attributes

# Use the function to see what columns are available to use. 
db_columns = get_table_attributes(PointData)

# Print out the results nicely
print("These are the available columns in the table:\n \n* {}\n".format('\n* '.join(db_columns)))

These are the available columns in the table:
 
* date
* date_accessed
* doi
* easting
* elevation
* equipment
* geom
* instrument
* latitude
* longitude
* metadata
* northing
* site_id
* site_name
* surveyors
* time
* time_created
* time_updated
* type
* units
* utm_zone
* value
* version_number

Try this: Using what we just did, use get_table_attributes to look at the other tables.

Hint: You have to change the table class name in two places in the above code block.

Discussion: What’s the difference in these tables?¶

If working by yourself checkout https://snowexsql.readthedocs.io/en/latest/database_structure.html to see how data gets categorized.

Bonus Step: Learning to help yourself¶

snowexsql has a host of resources for you to help your self. First when you are looking for something be sure to check the snowexsql’s docs. There you will find notes on the database structure. datasets, and of course our API!

https://snowexsql.readthedocs.io/en/latest/ https://github.com/SnowEx/snowexsql/

Some other resources that may be helpful are:

https://geoalchemy-2.readthedocs.io/en/0.8.4/ - geoalchemy is library that allows us to leverage postgis and other gis functions
https://docs.sqlalchemy.org/en/14/ - sqlalchemy is the underlying library that lets us map python to databases
https://postgis.net/docs/manual-3.0/PostGIS_Special_Functions_Index.html - POSTGIS is the extension that make postgres have GIS capabilities. This is here as a resource but it will be discussed in more detail later.
https://snowexsql.readthedocs.io/en/latest/cheat_sheet.html - This is a cheat sheet we put together to help you use the database.

Recap¶

You just explored the database structure and discussed how they differ.

You should know:

Which tables matter to a snowex scientist
What columns you can work with (or how to get the available columns)
Some resources to begin helping yourself.

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

SnowEx Hackweek