How is the Database Structured?
Contents
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()
/tmp/ipykernel_2329/3417980224.py:2: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release. Please refer to Inspector.get_table_names(). (deprecated since: 1.4)
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
* registry
* 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 the readthedocs page on database structure to see how data gets categorized.
# Close out the session to avoid hanging transactions
session.close()
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!
Database Usage/Examples¶
Database Building/Notes¶
Extra Resources¶
PostGIS Functions - 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.
GeoAlchemy2 - geoalchemy is library that allows us to leverage postgis and other gis functions
SqlAlchemy - sqlalchemy is the underlying library that lets us map python to databases
Hackweek DB Cheat Sheet - 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!