Cheat Sheets¶
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(<TABLE>)
qry = qry.filter(<CONDITION>)
# Continue on chaining filters
`
Technique |
Usage |
Description |
---|---|---|
|
|
Filter by a conditional to a value |
|
|
Filter by matching a value in a list |
|
|
Filter a column that are/are not Null |
|
|
Filter by finding a substring |
|
|
Reduce result to unique values |
|
|
Limit the number of records returned, useful for testing |
|
|
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
SQL Table |
snowexsql Class |
Description |
---|---|---|
|
Details describing pit sites |
|
|
Data with a single value and single location |
|
|
Data with a single value at a single location with a depth component |
|
|
Raster Data |
Useful snowexsql Functions¶
The table below shows useful tools built with this library
Function |
Usage |
Description |
---|---|---|
|
||
|
Get table column names |
|
|
Convert a query to a geopandas dataframe |
|
|
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.
import sqlalchemy.sql.func as func
import geoalchemy2.functions as gfunc
Function |
Usage |
Description |
---|---|---|
|
Convert binary to GeoTiff format |
|
|
Merge queried tiles |
|
|
Clip raster by polygon |
|
|
Get tiles that touch polygon |
|
|
Rescale raster |
|
|
Get a hillshade of raster |
|
|
Get geometry outline of raster |
|
|
Get centroid of a polygon/points |
|
|
Get data within polygon |
|
|
Get distances between points |
Common Issues¶
Useful tools for debugging
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 |