SnowEx Database Introduction#
Tutorial Author Micah’: Micah Sandusky
Tutorial Author Micah_o: Micah Johnson
SnowEx has introduced a unique opportunity to study SWE in a way that’s unprecedented, but with more data comes new challenges.
The SnowEx database is a resource that shortcuts the time it takes to ask cross dataset questions
Standardizing diverse data
Cross referencing data
Provenance!
Added GIS functionality
Connect w/ ArcGIS or QGIS!
CITABLE
What’s in it?#
Snow pits - Density, hardness profiles, grain types + sizes
Manual snow depths - TONS of depths (Can you say spirals?)
Snow Micropenetrometer (SMP) 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
Technically, what is it?#
PostgreSQL database
PostGIS extension
Supports vector and raster data
And a host of GIS operations
AND NOW WITH API!
So what’s the catch?#
New tech can create barriers…
TL;DR Do less wrangling, do more crunching.#
How do I get at this magical box of data ?#
Welcome to API Land#
from snowexsql.api import PointMeasurements
df = PointMeasurements.from_filter(type="depth", instrument='pit ruler', limit=100)
df.plot(column='value', cmap='jet', vmin=10, vmax=150)
df
| version_number | equipment | value | latitude | longitude | northing | easting | elevation | utm_zone | geom | ... | date | time_created | time_updated | id | doi | date_accessed | instrument | type | units | observers | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | ruler | 83.0 | 39.04496 | -108.06311 | 4.325871e+06 | 754172.639132 | 3253.169922 | 12 | POINT (754172.639 4325871.377) | ... | 2020-03-12 | 2022-06-30 22:56:52.635035+00:00 | None | 41824 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 1 | 1 | ruler | 100.0 | 39.04563 | -108.19593 | 4.325583e+06 | 742673.504400 | 3048.699951 | 12 | POINT (742673.504 4325582.611) | ... | 2020-01-30 | 2022-06-30 22:56:52.635035+00:00 | None | 41825 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 2 | 1 | ruler | 117.0 | 39.00760 | -108.14791 | 4.321491e+06 | 746962.448982 | 3087.709961 | 12 | POINT (746962.449 4321490.615) | ... | 2020-01-29 | 2022-06-30 22:56:52.635035+00:00 | None | 41826 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 3 | 1 | ruler | 98.0 | 39.02144 | -108.16401 | 4.322983e+06 | 745520.203184 | 3099.639893 | 12 | POINT (745520.203 4322983.253) | ... | 2020-02-09 | 2022-06-30 22:56:52.635035+00:00 | None | 41827 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 4 | 1 | ruler | 92.0 | 39.03404 | -108.19103 | 4.324309e+06 | 743137.395316 | 3055.590088 | 12 | POINT (743137.395 4324309.223) | ... | 2020-01-28 | 2022-06-30 22:56:52.635035+00:00 | None | 41828 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 95 | 1 | ruler | 92.0 | 39.03596 | -108.20975 | 4.324472e+06 | 741510.229315 | 3030.070068 | 12 | POINT (741510.229 4324472.430) | ... | 2020-01-28 | 2022-06-30 22:56:52.635035+00:00 | None | 41919 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 96 | 1 | ruler | 35.0 | 39.03126 | -108.18948 | 4.324005e+06 | 743281.122092 | 3060.439941 | 12 | POINT (743281.122 4324004.792) | ... | 2020-02-05 | 2022-06-30 22:56:52.635035+00:00 | None | 41920 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 97 | 1 | ruler | 101.0 | 39.01843 | -108.15596 | 4.322671e+06 | 746227.685533 | 3103.750000 | 12 | POINT (746227.686 4322670.914) | ... | 2020-02-01 | 2022-06-30 22:56:52.635035+00:00 | None | 41921 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 98 | 1 | ruler | 102.0 | 39.01437 | -108.14158 | 4.322259e+06 | 747487.040217 | 3100.860107 | 12 | POINT (747487.040 4322259.296) | ... | 2020-01-29 | 2022-06-30 22:56:52.635035+00:00 | None | 41922 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
| 99 | 1 | ruler | 115.0 | 39.03918 | -108.00313 | 4.325399e+06 | 759385.731498 | -9999.000000 | 12 | POINT (759385.731 4325399.285) | ... | 2020-02-03 | 2022-06-30 22:56:52.635035+00:00 | None | 41923 | https://doi.org/10.5067/9IA978JIACAR | 2022-06-30 | pit ruler | depth | cm | None |
100 rows × 23 columns
Old Ways / Advanced Users#
Advanced queries can be made using SQL or SQAlchemy under the hood.
See previous presentations
Engine objects, session objects, and a crash course in ORM, oh my!