Querying Leaf Lake - Leaf Documentation

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)

You can also filter spatially using a WKT polygon with 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

Leaf Lake Overview — Product description and key concepts.
API Reference: Leaf Lake — Endpoint details, request/response format, and code examples.
Sample Output — Full standardGeojson property reference by operation type.
Units — Unit reference for all numeric properties.

​The points table

​Common columns (all operation types)

​Planted columns

​Harvested columns

​Applied columns

​Tillage columns

​Filtering by field

​Operations query examples

​List operations on a field

​Planted: seed rate by variety and year

​Planted: spatial point data

​Applied: summary by product and year

​Applied: point data with product details

​Harvested: yield statistics

​Harvested: outlier filtering with CTEs

​Tillage: basic query

​The fields table

​Join operations with field boundaries

​The ssurgo table

​Query soil units intersecting a field

​Join harvest data with soil types

​The states_counties table

​Filter operations by county

​What to do next