Welcome to the API!

Welcome to the API!#

Goal: Easy programmatic access to the database with no user SQL

Notes#

This is not a REST API, more of an SDK
Current access is for point and layer data
Funtions return lists or Geopandas Dataframes

Step 1. Import the classes, explore them#

# imports
from datetime import date
import geopandas as gpd
from snowexsql.api import PointMeasurements, LayerMeasurements

# The main functions we will use are `from_area` and `from_filter` like this
df = PointMeasurements.from_filter(
    date=date(2020, 5, 28), instrument='camera'
)
df.head()

	site_name	date	time_created	time_updated	id	doi	date_accessed	instrument	type	units	...	northing	easting	elevation	utm_zone	geom	time	site_id	version_number	equipment	value
0	Grand Mesa	2020-05-28	2022-06-30 22:58:59.800562+00:00	None	42443	None	2022-06-30	camera	depth	cm	...	4.321444e+06	743766.479497	None	12	POINT (743766.479 4321444.155)	18:00:00+00:00	None	None	camera id = W1B	-2.99924
1	Grand Mesa	2020-05-28	2022-06-30 22:58:59.800562+00:00	None	42444	None	2022-06-30	camera	depth	cm	...	4.321444e+06	743766.479497	None	12	POINT (743766.479 4321444.155)	19:00:00+00:00	None	None	camera id = W1B	1.50148
2	Grand Mesa	2020-05-28	2022-06-30 22:58:59.800562+00:00	None	43187	None	2022-06-30	camera	depth	cm	...	4.331951e+06	249164.808618	None	13	POINT (249164.809 4331951.003)	18:00:00+00:00	None	None	camera id = E9B	-1.15255
3	Grand Mesa	2020-05-28	2022-06-30 22:58:59.800562+00:00	None	43188	None	2022-06-30	camera	depth	cm	...	4.331951e+06	249164.808618	None	13	POINT (249164.809 4331951.003)	19:00:00+00:00	None	None	camera id = E9B	1.16381
4	Grand Mesa	2020-05-28	2022-06-30 22:58:59.800562+00:00	None	43189	None	2022-06-30	camera	depth	cm	...	4.331951e+06	249164.808618	None	13	POINT (249164.809 4331951.003)	20:00:00+00:00	None	None	camera id = E9B	-2.31073

5 rows × 23 columns

Notice:#

We did not need to manage SQL
We got a geopandas array
We filtered on specific attributes known to be in the database

How do I know what to filter by?#

# Find what you can filter by
print(PointMeasurements.ALLOWED_QRY_KWARGS)
print(LayerMeasurements.ALLOWED_QRY_KWARGS)

['site_name', 'site_id', 'date', 'instrument', 'observers', 'type', 'utm_zone']
['site_name', 'site_id', 'date', 'instrument', 'observers', 'type', 'utm_zone', 'pit_id']

How do I know what values work for filtering?#

print(PointMeasurements().all_observers)

[('Catherine Breen, Cassie Lumbrazo',), (None,), ('Ryan Webb',), ('Randall Bonnell',), ('Tate Meehan',)]

Try it out#

What instrument could you filter by for PointData?
What site names could you filter by for LayerData?

Notice we instantiate the class PointMeasurements() Before calling the property .all_observers

# <YOUR CODE HERE>

# Explore the points
df.crs
df.to_crs("EPSG:4326").loc[:,["id", "value", "type", "geom", "instrument"]].explore()

Make this Notebook Trusted to load map: File -> Trust Notebook

What if I have a point or a shapefile#

Both the PointMeasurement and LayerMeasurement class have a function called from_area that takes either a shapely polygon or a shapely point and a radius as well as the same filter kwargs available in .from_filter

# Set up a fake shapefile
gdf = gpd.GeoDataFrame(
    geometry=gpd.points_from_xy(
        [743766.4794971556], [4321444.154620216], crs="epsg:26912"
    ).buffer(2000.0)
).set_crs("epsg:26912")

# This is the area we will filter to
gdf.explore()

Make this Notebook Trusted to load map: File -> Trust Notebook

# Get density near the point
df = LayerMeasurements.from_area(
    type="density",
    shp=gdf.iloc[0].geometry,
)

df.to_crs("EPSG:4326").loc[:,["id", "depth", "value", "type", "geom"]].explore()

Make this Notebook Trusted to load map: File -> Trust Notebook

How much filtering is enough?#

I got a LargeQueryCheckException

GIVE ME THE DATA PLEASE

# This query will fail
df = PointMeasurements.from_filter(
    instrument="magnaprobe",
)

Failed query for PointData

---------------------------------------------------------------------------
LargeQueryCheckException                  Traceback (most recent call last)
/var/folders/jh/tvv3prb117d22jyn0vmbjn880000gn/T/ipykernel_51325/2889856166.py in <module>
      1 # This query will fail
----> 2 df = PointMeasurements.from_filter(
      3     instrument="magnaprobe",
      4 )

~/projects/m3works/snowexsql/snowexsql/api.py in from_filter(cls, **kwargs)
    186                 session.close()
    187                 LOG.error("Failed query for PointData")
--> 188                 raise e
    189 
    190         return df

~/projects/m3works/snowexsql/snowexsql/api.py in from_filter(cls, **kwargs)
    181             try:
    182                 qry = session.query(cls.MODEL)
--> 183                 qry = cls.extend_qry(qry, **kwargs)
    184                 df = query_to_geopandas(qry, engine)
    185             except Exception as e:

~/projects/m3works/snowexsql/snowexsql/api.py in extend_qry(cls, qry, check_size, **kwargs)
    111 
    112         if check_size:
--> 113             cls._check_size(qry, kwargs)
    114 
    115         return qry

~/projects/m3works/snowexsql/snowexsql/api.py in _check_size(cls, qry, kwargs)
     69         count = qry.count()
     70         if count > cls.MAX_RECORD_COUNT and "limit" not in kwargs:
---> 71             raise LargeQueryCheckException(
     72                 f"Query will return {count} number of records,"
     73                 f" but we have a default max of {cls.MAX_RECORD_COUNT}."

LargeQueryCheckException: Query will return 33364 number of records, but we have a default max of 1000. If you want to proceed, set the 'limit' filter to the desired number of records.

# Th queries will pass
df = PointMeasurements.from_filter(
    instrument="magnaprobe",
    limit=100
)

df.head()

	site_name	date	time_created	time_updated	id	doi	date_accessed	instrument	type	units	...	northing	easting	elevation	utm_zone	geom	time	site_id	version_number	equipment	value
0	Grand Mesa	2020-01-29	2022-06-30 22:56:52.635035+00:00	None	8713	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.322865e+06	741881.102466	3037.8	12	POINT (741881.102 4322865.037)	14:56:00+00:00	None	1	CRREL_C	85.0
1	Grand Mesa	2020-01-29	2022-06-30 22:56:52.635035+00:00	None	8714	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.322859e+06	741878.675380	3038.0	12	POINT (741878.675 4322859.408)	14:57:00+00:00	None	1	CRREL_C	72.0
2	Grand Mesa	2020-01-29	2022-06-30 22:56:52.635035+00:00	None	8715	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.322855e+06	741877.080058	3037.1	12	POINT (741877.080 4322854.914)	14:57:00+00:00	None	1	CRREL_C	84.0
3	Grand Mesa	2020-01-29	2022-06-30 22:56:52.635035+00:00	None	8716	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.322850e+06	741875.484733	3035.5	12	POINT (741875.485 4322850.421)	14:57:00+00:00	None	1	CRREL_C	84.0
4	Grand Mesa	2020-01-29	2022-06-30 22:56:52.635035+00:00	None	8717	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.322845e+06	741873.923512	3034.6	12	POINT (741873.924 4322844.818)	14:57:00+00:00	None	1	CRREL_C	78.0

5 rows × 23 columns

DANGER ZONE#

If you need more than 1000 points returned, you can specify so with the limit

The intention is to be aware of how much data will be returned

# DANGER ZONE
# If you need more than 1000 points returned, you can specify so with the limit
df = PointMeasurements.from_filter(
    date=date(2020, 1, 28),
    instrument="magnaprobe",
    limit=3000
)
df.head()

	site_name	date	time_created	time_updated	id	doi	date_accessed	instrument	type	units	...	northing	easting	elevation	utm_zone	geom	time	site_id	version_number	equipment	value
0	Grand Mesa	2020-01-28	2022-06-30 22:56:52.635035+00:00	None	4663	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.323938e+06	747760.127417	3143.9	12	POINT (747760.127 4323937.874)	20:22:00+00:00	None	1	CRREL_B	64.0
1	Grand Mesa	2020-01-28	2022-06-30 22:56:52.635035+00:00	None	4102	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.324060e+06	747975.533229	3151.8	12	POINT (747975.533 4324060.214)	18:48:00+00:00	None	1	CRREL_B	106.0
2	Grand Mesa	2020-01-28	2022-06-30 22:56:52.635035+00:00	None	4103	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.324058e+06	747973.005869	3153.8	12	POINT (747973.006 4324057.912)	18:48:00+00:00	None	1	CRREL_B	110.0
3	Grand Mesa	2020-01-28	2022-06-30 22:56:52.635035+00:00	None	4104	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.324057e+06	747973.040848	3153.5	12	POINT (747973.041 4324056.802)	18:48:00+00:00	None	1	CRREL_B	106.0
4	Grand Mesa	2020-01-28	2022-06-30 22:56:52.635035+00:00	None	4105	https://doi.org/10.5067/9IA978JIACAR	2022-06-30	magnaprobe	depth	cm	...	4.324055e+06	747972.245032	3154.0	12	POINT (747972.245 4324054.555)	18:48:00+00:00	None	1	CRREL_B	107.0

5 rows × 23 columns

Welcome to the API!

Contents

Welcome to the API!#

Notes#

Step 1. Import the classes, explore them#

Notice:#

How do I know what to filter by?#

How do I know what values work for filtering?#

Try it out#

What if I have a point or a shapefile#

How much filtering is enough?#

DANGER ZONE#

THE END#

Go forth and explore#