Cheat Sheets

Cheat Sheets#

Below is a list of common things you will likely want to use for querying in the database

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(<TABLE>) qry = qry.filter(<CONDITION>) # Continue on chaining filters `

Table 1 Querying#
Technique	Usage	Description
`==`, `!=`, `>=`, `<=`, `>`, `<`	`qry.filter(SiteData.site_id == '1S20')`	Filter by a conditional to a value
`.in_()`	`qry.filter(PointData.date.in_([date1, date2]))`	Filter by matching a value in a list
`.is_()`, `isnot()`	`qry.filter(LayerData.instrument.isnot(None))`	Filter a column that are/are not Null
`.contains()`	`qry.filter(LayerData.comments).contains('graupel'))`	Filter by finding a substring
`.distinct()`	`session.query(RasterData.surveyors).distinct()`	Reduce result to unique values
`.limit()`	`session.query(PointsData).limit(10)`	Limit the number of records returned, useful for testing
`.count()`	`qry.filter(PointsData).count()`	Count the number of records matching query/filtering

The table below shows the SQL table names to Python Object Relational Mapping (ORM) classes with descriptions of data in them

Table 2 **Database Tables**#
SQL Table	snowexsql Class	Description
`sites`	`snowexsql.data.SiteData`	Details describing pit sites
`points`	`snowexsql.data.PointData`	Data with a single value and single location
`layers`	`snowexsql.data.LayerData`	Data with a single value at a single location with a depth component
`images`	`snowexsql.data.ImageData`	Raster Data

The table below shows useful tools built with this library

Function	Usage	Description
`snowexsql.db.get_db()`	`eng, sesh = get_db('<USER>:<PASS>@<IP>/snowex')`	Get engine / session objects to query db
`snowexsql.db.get_table_attributes()`	`cols = get_table_attributes(PointData)`	Get table column names
`snowexsql.conversions.query_to_geopandas()`	`df = query_to_geopands(qry, engine)`	Convert a query to a geopandas dataframe
`snowexsql.conversions.raster_to_rasterio()`	`ds = rasters_to_rasterio(records)`	Convert db result to rasterio datasets

The table below shows useful tools that can be used in python from postgis. These are accessed in two ways.

import sqlalchemy.sql.func as func

import geoalchemy2.functions as gfunc

Function	Usage	Description
ST_AsTiff	`session.query(func.ST_AsTiff(ImageData.raster))`	Convert binary to GeoTiff format
ST_Union	`session.query(func.Union(ImageData.raster, _type=Raster))`	Merge queried tiles
ST_Clip	`session.query(func.ST_Clip(ImageData.raster, shp))`	Clip raster by polygon
ST_Intersects	`session.query(func.ST_Intersects(ImageData.raster, shp))`	Get tiles that touch polygon
ST_Rescale	`session.query(func.ST_Rescale(ImageData.raster, res, res)`	Rescale raster
ST_Hillshade	`session.query(func.ST_Hillshade(ImageData.raster))`	Get a hillshade of raster
ST_Envelope	`session.query(func.ST_Envelope(ImageData.raster))`	Get geometry outline of raster
ST_Centroid	`session.query(func.ST_Envelope(ImageData.raster))`	Get centroid of a polygon/points
ST_Within	`session.query(func.ST_Within(SiteData.geom, shp))`	Get data within polygon
ST_Distance	`session.query(func.ST_Distance(PointData.geom, shp))`	Get distances between points

Useful tools for debugging

Table 3 **Debugging Tools**#
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