> ## Documentation Index
> Fetch the complete documentation index at: https://docs.withleaf.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Querying Leaf Lake

> Schema reference and example queries for Leaf Lake: the points table for all operation types, SSURGO soil data, and state/county boundaries.

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](/machine-data/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.

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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).

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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](/api-reference/fields). 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.

```sql theme={null}
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.

```sql theme={null}
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`.

```sql theme={null}
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

```sql theme={null}
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](/leaf-lake/overview) — Product description and key concepts.
* [API Reference: Leaf Lake](/api-reference/leaf-lake) — Endpoint details, request/response format, and code examples.
* [Sample Output](/machine-data/sample-output) — Full standardGeojson property reference by operation type.
* [Units](/machine-data/units) — Unit reference for all numeric properties.