Cheat Sheets
============
.. role:: python(code)
:language: python
Below is a list of common things you will likely want to use for querying in the database
Querying
--------
The table below shows a handful of useful ways to query the database.
All querys can be built and expanded on by:
```
qry = session.query(
)
qry = qry.filter()
# Continue on chaining filters
```
.. list-table:: Querying
:widths: 10 100 1000
:header-rows: 1
* - Technique
- Usage
- Description
* - :python:`==`, :python:`!=`, :python:`>=`, :python:`<=`, :python:`>`, :python:`<`
- :python:`qry.filter(SiteData.site_id == '1S20')`
- Filter by a conditional to a value
* - :python:`.in_()`
- :python:`qry.filter(PointData.date.in_([date1, date2]))`
- Filter by matching a value in a list
* - :python:`.is_()`, :python:`isnot()`
- :python:`qry.filter(LayerData.instrument.isnot(None))`
- Filter a column that are/are not Null
* - :python:`.contains()`
- :python:`qry.filter(LayerData.comments).contains('graupel'))`
- Filter by finding a substring
* - :python:`.distinct()`
- :python:`session.query(RasterData.surveyors).distinct()`
- Reduce result to unique values
* - :python:`.limit()`
- :python:`session.query(PointsData).limit(10)`
- Limit the number of records returned, useful for testing
* - :python:`.count()`
- :python:`qry.filter(PointsData).count()`
- Count the number of records matching query/filtering
Database Tables
---------------
The table below shows the SQL table names to Python Object Relational Mapping (ORM) classes with descriptions of data
in them
.. list-table:: **Database Tables**
:widths: 10 20 180
:header-rows: 1
* - SQL Table
- snowexsql Class
- Description
* - :python:`sites`
- :py:class:`snowexsql.data.SiteData`
- Details describing pit sites
* - :python:`points`
- :py:class:`snowexsql.data.PointData`
- Data with a single value and single location
* - :python:`layers`
- :py:class:`snowexsql.data.LayerData`
- Data with a single value at a single location with a depth component
* - :python:`images`
- :py:class:`snowexsql.data.ImageData`
- Raster Data
Useful `snowexsql` Functions
----------------------------
The table below shows useful tools built with this library
.. list-table::
:widths: 10 20 180
:header-rows: 1
* - Function
- Usage
- Description
* - :py:func:`snowexsql.db.get_db`
- :python:`eng, sesh = get_db(':@/snowex')`
- Get `engine `_ / `session `_ objects to query db
* - :py:func:`snowexsql.db.get_table_attributes`
- :python:`cols = get_table_attributes(PointData)`
- Get table column names
* - :py:func:`snowexsql.conversions.query_to_geopandas`
- :python:`df = query_to_geopands(qry, engine)`
- Convert a query to a geopandas dataframe
* - :py:func:`snowexsql.conversions.raster_to_rasterio`
- :python:`ds = rasters_to_rasterio(records)`
- Convert db result to rasterio datasets
Useful PostGIS Tools
--------------------
The table below shows useful tools that can be used in python from postgis. These are accessed in two ways.
1. :python:`import sqlalchemy.sql.func as func`
2. :python:`import geoalchemy2.functions as gfunc`
.. list-table::
:widths: 10 20 180
:header-rows: 1
* - Function
- Usage
- Description
* - `ST_AsTiff `_
- :python:`session.query(func.ST_AsTiff(ImageData.raster))`
- Convert binary to GeoTiff format
* - `ST_Union `_
- :python:`session.query(func.Union(ImageData.raster, _type=Raster))`
- Merge queried tiles
* - `ST_Clip `_
- :python:`session.query(func.ST_Clip(ImageData.raster, shp))`
- Clip raster by polygon
* - `ST_Intersects `_
- :python:`session.query(func.ST_Intersects(ImageData.raster, shp))`
- Get tiles that touch polygon
* - `ST_Rescale `_
- :python:`session.query(func.ST_Rescale(ImageData.raster, res, res)`
- Rescale raster
* - `ST_Hillshade `_
- :python:`session.query(func.ST_Hillshade(ImageData.raster))`
- Get a hillshade of raster
* - `ST_Envelope `_
- :python:`session.query(func.ST_Envelope(ImageData.raster))`
- Get geometry outline of raster
* - `ST_Centroid `_
- :python:`session.query(func.ST_Envelope(ImageData.raster))`
- Get centroid of a polygon/points
* - `ST_Within `_
- :python:`session.query(func.ST_Within(SiteData.geom, shp))`
- Get data within polygon
* - `ST_Distance `_
- :python:`session.query(func.ST_Distance(PointData.geom, shp))`
- Get distances between points
Common Issues
-------------
Useful tools for debugging
.. list-table:: **Debugging Tools**
:widths: 20 180
:header-rows: 1
* - `session.rollback()`
- Rolls back the last query, useful for querys that fail after execution.
* - `session.close()`
- Closes your connection with the DB. Useful when using jupyter notebooks