Leaf Lake exposes your agronomic data through four tables. This page documents the schema of each table and provides example queries you can adapt.
Leaf Lake uses BigQuery SQL. All queries are automatically scoped to your API owner account — you only see data belonging to your Leaf users.
The points table
Each row represents a single data point from a machine file. The columns match the standardGeojson point properties produced by Leaf’s machine data pipeline. See Sample Output for the full property definitions.
Common columns (all operation types)
| Column | Type | Description |
|---|
operationType | string | planted, harvested, applied, or tillage |
geometry | string | Geographic location as WKT (convert with ST_GeogFromText) |
timestamp | timestamp | When the data was recorded |
crop | string | Normalized crop name |
fieldIds | array | Leaf field UUID(s) associated with this point |
leafUserId | string | The Leaf user who owns this data |
area | float | Area covered at this point |
distance | float | Distance traveled |
heading | float | Machine heading in degrees |
speed | float | Machine speed |
elevation | float | Elevation |
equipmentWidth | float | Width of the implement |
recordingStatus | string | Recording status (e.g. On) |
sectionId | int | Section/row identifier |
machinery | list | Machine and implement names |
variety | string | Crop variety name |
Planted columns
| Column | Type | Description |
|---|
seedRate | int | Seeds per area at this point |
seedRateTarget | int | Target seed rate |
seedDepth | float | Planting depth |
downForce | float | Down force reading |
singulation | float | Meter singulation percentage |
skips | float | Skip percentage |
doubles | float | Double percentage |
Harvested columns
| Column | Type | Description |
|---|
harvestMoisture | float | Grain moisture percentage |
wetMass | float | Wet mass |
wetMassPerArea | float | Wet mass per area |
wetVolume | float | Wet volume |
wetVolumePerArea | float | Wet volume per area |
dryMass | float | Dry mass |
dryMassPerArea | float | Dry mass per area |
dryVolume | float | Dry volume |
dryVolumePerArea | float | Dry volume per area |
Applied columns
| Column | Type | Description |
|---|
appliedRate | float | Application rate at this point |
appliedRateTarget | float | Target application rate |
products | list | Array of product objects with name and rate |
tankMixName | string | Name of the tank mix |
Tillage columns
| Column | Type | Description |
|---|
tillageDepthTarget | float | Target tillage depth |
tillageDepthActual | float | Actual tillage depth |
tillType | list | Tillage implement type |
Filtering by field
Filter points to a specific Leaf field using the fieldIds column. No WKT boundary polygon needed.
SELECT *
FROM points
WHERE operationType = 'harvested'
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
ST_Intersects, or join against the fields or states_counties tables. See the examples below.
Operations query examples
List operations on a field
See what operation types, crops, and date ranges exist for a field.
SELECT
EXTRACT(YEAR FROM timestamp) AS op_year,
operationType,
crop,
COUNT(*) AS point_count,
MIN(timestamp) AS start_time,
MAX(timestamp) AS end_time
FROM points
WHERE 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
GROUP BY EXTRACT(YEAR FROM timestamp), operationType, crop
ORDER BY op_year, start_time
Planted: seed rate by variety and year
SELECT
crop,
EXTRACT(YEAR FROM timestamp) AS op_year,
variety,
COUNT(*) AS point_count,
ROUND(MIN(seedRate), 0) AS min_seed_rate,
ROUND(AVG(seedRate), 0) AS avg_seed_rate,
ROUND(MAX(seedRate), 0) AS max_seed_rate
FROM points
WHERE operationType = 'planted'
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
GROUP BY crop, variety, EXTRACT(YEAR FROM timestamp)
ORDER BY op_year
Planted: spatial point data
Retrieve individual planting points with coordinates for mapping.
SELECT
crop,
EXTRACT(YEAR FROM timestamp) AS planting_year,
seedRate,
ST_X(ST_GeogFromText(geometry)) AS lon,
ST_Y(ST_GeogFromText(geometry)) AS lat
FROM points
WHERE operationType = 'planted'
AND seedRate IS NOT NULL
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
Applied: summary by product and year
SELECT
EXTRACT(YEAR FROM timestamp) AS op_year,
tankMixName,
COUNT(*) AS point_count,
MIN(timestamp) AS start_time,
MAX(timestamp) AS end_time
FROM points
WHERE operationType = 'applied'
AND timestamp >= '2025-01-01'
AND timestamp <= '2025-12-31'
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
GROUP BY EXTRACT(YEAR FROM timestamp), tankMixName
ORDER BY op_year, start_time
Applied: point data with product details
SELECT
TO_JSON_STRING(products) AS products_json,
appliedRate,
timestamp,
ST_X(ST_GeogFromText(geometry)) AS lon,
ST_Y(ST_GeogFromText(geometry)) AS lat
FROM points
WHERE operationType = 'applied'
AND timestamp >= '2025-01-01'
AND timestamp <= '2025-12-31'
AND appliedRate IS NOT NULL
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
Harvested: yield statistics
Leaf Lake stores yield in metric units. Convert to bu/ac using crop-specific factors (corn: 62.77 kg/ha per bu/ac, soybeans: 67.25 kg/ha per bu/ac).
SELECT
crop,
EXTRACT(YEAR FROM timestamp) AS op_year,
COUNT(*) AS point_count,
ROUND(AVG(wetMassPerArea) / 62.77, 1) AS avg_yield_bu_ac,
ROUND(MIN(wetMassPerArea) / 62.77, 1) AS min_yield_bu_ac,
ROUND(MAX(wetMassPerArea) / 62.77, 1) AS max_yield_bu_ac,
ROUND(STDDEV(wetMassPerArea) / 62.77, 1) AS std_yield_bu_ac
FROM points
WHERE operationType = 'harvested'
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
GROUP BY crop, EXTRACT(YEAR FROM timestamp)
ORDER BY op_year DESC
Harvested: outlier filtering with CTEs
Filter yield data to remove statistical outliers. Adjust the standard deviation threshold to control how aggressively outliers are removed. This replaces pipeline-level reprocessing — change the threshold and re-run the query.
WITH stats AS (
SELECT
crop,
EXTRACT(YEAR FROM timestamp) AS harvest_year,
AVG(wetMassPerArea) AS mean_yield,
STDDEV(wetMassPerArea) AS std_yield
FROM points
WHERE operationType = 'harvested'
AND wetMassPerArea IS NOT NULL
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
GROUP BY crop, EXTRACT(YEAR FROM timestamp)
)
SELECT
p.crop,
EXTRACT(YEAR FROM p.timestamp) AS harvest_year,
p.wetMassPerArea / 62.77 AS yield_bu_ac,
p.harvestMoisture AS moisture,
ST_X(ST_GeogFromText(p.geometry)) AS lon,
ST_Y(ST_GeogFromText(p.geometry)) AS lat
FROM points p
JOIN stats s ON p.crop = s.crop
AND EXTRACT(YEAR FROM p.timestamp) = s.harvest_year
WHERE p.operationType = 'harvested'
AND p.wetMassPerArea IS NOT NULL
AND 'YOUR_FIELD_UUID' IN UNNEST(p.fieldIds)
AND p.wetMassPerArea BETWEEN s.mean_yield - 3 * s.std_yield
AND s.mean_yield + 3 * s.std_yield
Tillage: basic query
SELECT
EXTRACT(YEAR FROM timestamp) AS op_year,
tillType,
COUNT(*) AS point_count,
ROUND(AVG(tillageDepthTarget), 1) AS avg_depth_target,
MIN(timestamp) AS start_time,
MAX(timestamp) AS end_time
FROM points
WHERE operationType = 'tillage'
AND 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
GROUP BY EXTRACT(YEAR FROM timestamp), tillType
ORDER BY op_year
The fields table
Leaf field boundaries synced from the Fields API. Automatically scoped to your API owner account.
| Column | Type | Description |
|---|
id | string (UUID) | The Leaf field ID |
field_geometry | geography | Union of all boundary geometries for the field |
boundary_id_list | array | List of boundary IDs |
updated_date | date | Last update date |
Join operations with field boundaries
Use the fields table to spatially join points with field boundaries instead of hardcoding WKT polygons.
SELECT
f.id AS field_id,
p.operationType,
p.crop,
COUNT(*) AS point_count
FROM points p
JOIN fields f ON ST_Intersects(ST_GeogFromText(p.geometry), f.field_geometry)
WHERE f.id = 'YOUR_FIELD_UUID'
GROUP BY f.id, p.operationType, p.crop
The ssurgo table
The SSURGO (Soil Survey Geographic Database) table contains USDA soil map unit polygons with associated attributes.
| Column | Type | Description |
|---|
mukey | string | Map unit key — unique identifier for the soil map unit |
soil_name | string | Soil map unit name |
county | string | County name |
drainage_class | string | Soil drainage classification (e.g. “Well drained”, “Poorly drained”) |
farmland_class | string | Farmland classification (e.g. “Prime farmland”) |
hydric_rating | string | Hydric soil rating |
flooding_frequency | string | Flooding frequency class |
ponding_frequency | string | Ponding frequency class |
aws0_150 | float | Available water storage, 0-150 cm depth |
aws0_999 | float | Available water storage, full soil profile |
geometry | string | Boundary of the soil map unit (convert with ST_GeogFromText) |
Query soil units intersecting a field
Derive the field extent from your points using a CTE, then intersect with SSURGO.
WITH field_envelope AS (
SELECT ST_ConvexHull(ST_Union_Agg(ST_GeogFromText(geometry))) AS field_geom
FROM points
WHERE 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
)
SELECT
mukey,
soil_name,
county,
drainage_class,
farmland_class,
ROUND(ST_Area(ST_Intersection(
ST_GeogFromText(ssurgo.geometry),
fe.field_geom
)) / 4046.86, 2) AS acres,
ST_AsText(ST_Intersection(
ST_GeogFromText(ssurgo.geometry),
fe.field_geom
)) AS clipped_geometry
FROM ssurgo
CROSS JOIN field_envelope fe
WHERE ST_Intersects(ST_GeogFromText(ssurgo.geometry), fe.field_geom)
ORDER BY mukey ASC
Join harvest data with soil types
Calculate average yield by soil map unit within a field. This is a spatial join between the points table and ssurgo.
WITH field_envelope AS (
SELECT ST_ConvexHull(ST_Union_Agg(ST_GeogFromText(geometry))) AS field_geom
FROM points
WHERE 'YOUR_FIELD_UUID' IN UNNEST(fieldIds)
),
field_soils AS (
SELECT
mukey,
soil_name,
drainage_class,
farmland_class,
ssurgo.geometry AS soil_geom
FROM ssurgo
CROSS JOIN field_envelope fe
WHERE ST_Intersects(ST_GeogFromText(ssurgo.geometry), fe.field_geom)
),
yield_by_soil AS (
SELECT
s.mukey,
AVG(p.wetMassPerArea) AS avg_yield,
COUNT(*) AS harvest_points
FROM points p
JOIN field_soils s ON ST_Intersects(ST_GeogFromText(s.soil_geom), ST_GeogFromText(p.geometry))
WHERE p.operationType = 'harvested'
AND p.timestamp >= '2025-01-01'
AND p.timestamp <= '2025-12-31'
AND 'YOUR_FIELD_UUID' IN UNNEST(p.fieldIds)
GROUP BY s.mukey
)
SELECT
s.mukey,
s.soil_name,
s.drainage_class,
s.farmland_class,
ROUND(COALESCE(y.avg_yield, 0) / 62.77, 1) AS avg_yield_bu_ac,
y.harvest_points
FROM field_soils s
LEFT JOIN yield_by_soil y ON s.mukey = y.mukey
ORDER BY avg_yield_bu_ac DESC
The states_counties table
US state and county boundary polygons for geographic grouping and filtering.
| Column | Type | Description |
|---|
STATE | string | State code (e.g. IL, IA) |
COUNTYNAME | string | County name |
geometry | geography | State or county boundary |
Filter operations by county
SELECT
c.STATE,
c.COUNTYNAME,
EXTRACT(YEAR FROM p.timestamp) AS op_year,
p.operationType,
p.crop,
COUNT(*) AS point_count
FROM points p
JOIN states_counties c ON ST_Intersects(ST_GeogFromText(p.geometry), c.geometry)
WHERE c.STATE = 'IL'
AND c.COUNTYNAME = 'La Salle'
GROUP BY c.STATE, c.COUNTYNAME, EXTRACT(YEAR FROM p.timestamp), p.operationType, p.crop
ORDER BY op_year
What to do next
Last modified on March 24, 2026
