PostGIS Schema Design for Farm Data

The schema is where a farm data platform is won or lost. Every pipeline elsewhere on this site — spatial interpolation for yield data, management zone classification, every zonal statistic and every prescription — groups by field and season, so the way those two entities are modelled determines whether a five-season history is a straightforward query or an archaeology project. The output of this topic is a working PostGIS schema: canonical fields with validity intervals, a crosswalk of external identifiers, season-partitioned telemetry, raster metadata pointing at object storage, and the ingestion manifest that makes re-runs safe. It sits under farm data platform engineering.

Prerequisites

• PostgreSQL 16+ with PostGIS 3.4+, and the btree_gist extension for exclusion constraints
• Python 3.11+, SQLAlchemy 2.0., GeoAlchemy2 0.15., psycopg 3.2., geopandas 1.0.
• Boundaries arriving in EPSG:4326 — the canonical storage SRID used throughout
• Familiarity with the CRS discipline in understanding CRS in precision agriculture; every area and distance in this schema is computed after an explicit transform
• Telemetry arriving from machine data APIs or from monitor exports

1. Concept: Four Entities, One of Which Moves

The canonical field owns identity

A field in this schema is a row you own with a surrogate primary key. It is never keyed on a management system’s identifier, a name, or a farm-plus-name pair — all three change without warning. Its boundary lives in a separate table with a validity interval, so the geometry that was current during the 2024 harvest survives the 2025 boundary edit.

The crosswalk owns the outside world

Every external identifier — a management system field identifier, a monitor’s field name, a regulatory parcel code — is a row pointing at the canonical field, carrying the source, the identifier, when it was first and last seen, and its validity. Two systems calling the same ground by different names is normal; one identifier pointing at two canonical fields is a defect, and the schema should make it impossible.

The field-season owns agronomy

Crop, variety, planting date, target population, and the season’s operations belong to the field-season, not the field. This is the join key for nearly every analytical query, and separating it is what makes “yield by field across five seasons” a two-table join rather than a set of assumptions.

Telemetry is big, dumb and immutable

Points, samples, passes. Written once, never updated, queried by field-season and geometry. It is the only table in the schema whose size forces design decisions, and the only one that should be partitioned.

2. Step-by-Step Implementation

Step 1 — Fields and versioned boundaries

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE field (
    field_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    grower_id   bigint NOT NULL REFERENCES grower(grower_id),
    name        text   NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE field_boundary (
    boundary_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    field_id    bigint NOT NULL REFERENCES field(field_id) ON DELETE CASCADE,
    geom        geometry(MultiPolygon, 4326) NOT NULL,
    source      text   NOT NULL,
    valid_from  timestamptz NOT NULL,
    valid_to    timestamptz,
    CONSTRAINT boundary_valid CHECK (ST_IsValid(geom)),
    CONSTRAINT boundary_period CHECK (valid_to IS NULL OR valid_to > valid_from),
    -- At most one current boundary per field, enforced by the database rather than by code.
    EXCLUDE USING gist (
        field_id WITH =,
        tstzrange(valid_from, coalesce(valid_to, 'infinity')) WITH &&
    )
);

CREATE INDEX field_boundary_geom_gix ON field_boundary USING gist (geom);
CREATE INDEX field_boundary_current_ix ON field_boundary (field_id) WHERE valid_to IS NULL;

Three details do the work. The column type is MultiPolygon, not Polygon, because fields with a wetland exclusion or two disjoint blocks are ordinary — declaring Polygon forces an implicit conversion that quietly drops geometry. The ST_IsValid check refuses self-intersecting rings at the door rather than letting them reach fixing shapely invalid geometry errors. And the exclusion constraint makes overlapping validity periods impossible, which is the invariant every “which boundary applied then?” query depends on.

Step 2 — The external identifier crosswalk

CREATE TABLE field_external_id (
    field_id     bigint NOT NULL REFERENCES field(field_id) ON DELETE CASCADE,
    source       text   NOT NULL,          -- 'operations_center', 'fieldview', 'monitor_export'
    external_id  text   NOT NULL,
    first_seen   timestamptz NOT NULL DEFAULT now(),
    last_seen    timestamptz NOT NULL DEFAULT now(),
    valid_to     timestamptz,
    PRIMARY KEY (source, external_id, first_seen)
);

-- An active external identifier must resolve to exactly one canonical field.
CREATE UNIQUE INDEX field_external_active_uix
    ON field_external_id (source, external_id) WHERE valid_to IS NULL;

The partial unique index is the constraint that matters. Without it, a botched match run assigns one management-system field to two canonical fields, and every aggregate afterwards double-counts without any error appearing.

Step 3 — Field-seasons and operations

CREATE TABLE field_season (
    field_season_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    field_id   bigint   NOT NULL REFERENCES field(field_id) ON DELETE CASCADE,
    season     smallint NOT NULL,
    crop       text     NOT NULL,
    planted_on date,
    UNIQUE (field_id, season)
);

CREATE TABLE operation (
    operation_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    field_season_id bigint NOT NULL REFERENCES field_season(field_season_id) ON DELETE CASCADE,
    op_type         text NOT NULL CHECK (op_type IN ('plant', 'apply', 'harvest', 'tillage')),
    machine_id      text,
    started_at      timestamptz NOT NULL,
    ended_at        timestamptz NOT NULL,
    CONSTRAINT operation_period CHECK (ended_at >= started_at),
    UNIQUE (field_season_id, op_type, started_at, machine_id)
);

The natural unique key on the operation is what makes re-ingestion safe when a display re-uploads the same work under a new provider identifier — the second insert conflicts and is discarded.

Step 4 — Partitioned telemetry

CREATE TABLE telemetry_point (
    operation_id bigint NOT NULL REFERENCES operation(operation_id) ON DELETE CASCADE,
    season       smallint NOT NULL,
    ts           timestamptz NOT NULL,
    geom         geometry(Point, 4326) NOT NULL,
    yield_dry    double precision,
    speed_m_s    real,
    width_m      real,
    PRIMARY KEY (season, operation_id, ts)
) PARTITION BY LIST (season);

CREATE TABLE telemetry_point_2025 PARTITION OF telemetry_point FOR VALUES IN (2025);
CREATE TABLE telemetry_point_2026 PARTITION OF telemetry_point FOR VALUES IN (2026);

CREATE INDEX telemetry_2026_geom_gix ON telemetry_point_2026 USING gist (geom);
CREATE INDEX telemetry_2026_op_ix    ON telemetry_point_2026 (operation_id, ts);

Season is carried on the row rather than joined from the operation so that the partition key is available to the planner without a join. That denormalisation is deliberate and it is the only one in this schema; everything else is normalised, because everything else is small.

Step 5 — Raster metadata, not rasters

CREATE TABLE raster_artifact (
    artifact_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind         text NOT NULL,           -- 's2_window', 'ortho', 'ndvi'
    field_id     bigint REFERENCES field(field_id) ON DELETE SET NULL,
    footprint    geometry(Polygon, 4326) NOT NULL,
    acquired_at  timestamptz NOT NULL,
    band_desc    text[] NOT NULL,
    code_version text NOT NULL,           -- derived products only; 'raw' for published data
    object_key   text NOT NULL UNIQUE,
    UNIQUE (kind, field_id, acquired_at, code_version)
);

CREATE INDEX raster_artifact_gix ON raster_artifact USING gist (footprint);
CREATE INDEX raster_artifact_time_ix ON raster_artifact (field_id, acquired_at DESC);

The code_version column in the unique key is what lets version 4 of an index algorithm coexist with version 3 rather than overwrite it — the derived-product versioning discussed in the section overview.

Step 6 — The manifest

CREATE TABLE ingest_manifest (
    manifest_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source       text NOT NULL,
    external_id  text NOT NULL,
    checksum     text NOT NULL,
    object_key   text,
    promoted_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (source, external_id, checksum)
);

CREATE INDEX ingest_manifest_recent_ix ON ingest_manifest (source, promoted_at DESC);

The unique constraint is the idempotency guarantee. An ingestion task that inserts here inside the same transaction that publishes the data cannot produce a duplicate, no matter how many times it is retried — the second attempt conflicts and rolls back cleanly.

3. Key Parameters and Tuning

4. Edge Cases and Failure Modes

Two systems, one field, different boundaries. The management system’s polygon and the monitor’s recorded pass extent will never agree exactly. Treat the management system’s boundary as authoritative for area and the telemetry extent as evidence; if the telemetry consistently exceeds the boundary by more than a working width, the boundary is stale.

Fields that split or merge mid-season. The clean model is to close the old canonical field’s boundary validity, create the new fields, and record the lineage in a field_lineage table with parent and child identifiers. Merging seasons across a split is then an explicit, visible choice rather than a silently wrong join.

Invalid geometry arriving from a monitor export. Self-intersections from GPS jitter are routine in machine-generated boundaries. The ST_IsValid check will refuse them; repair with ST_MakeValid at ingest, and record that a repair happened rather than doing it silently — a boundary that needed repair is a signal about the source, not just a row to fix.

Timezone-naive timestamps. Every timestamp column here is timestamptz. A timestamp column silently reinterprets on a server timezone change, and harvest data that shifts by an hour across a daylight-saving boundary destroys any flow-delay correction applied downstream in yield monitor data cleaning.

Partition pruning that never happens. A query filtering WHERE ts >= '2026-05-01' without a season predicate scans every partition. Either always carry the season in the predicate or use range partitioning on ts instead — but pick one and write the query patterns down, because the two choices optimise different questions.

Cascade deletes reaching telemetry. ON DELETE CASCADE from field down to telemetry means deleting one mis-imported field can remove millions of rows in a single unnoticed statement. In production, revoke delete on field from the application role entirely and handle removal as an explicit, reviewed operation.

5. Verification and Output Validation

Four assertions belong in a scheduled job rather than in a migration.

-- 1. No field has two current boundaries (belt and braces behind the exclusion constraint).
SELECT field_id, count(*) FROM field_boundary WHERE valid_to IS NULL
GROUP BY field_id HAVING count(*) > 1;

-- 2. No active external identifier resolves to more than one canonical field.
SELECT source, external_id, count(DISTINCT field_id) FROM field_external_id
WHERE valid_to IS NULL GROUP BY source, external_id HAVING count(DISTINCT field_id) > 1;

-- 3. Every geometry is valid and in the canonical SRID.
SELECT boundary_id FROM field_boundary WHERE NOT ST_IsValid(geom) OR ST_SRID(geom) <> 4326;

-- 4. Telemetry falls inside its field, allowing one working width of overhang.
SELECT t.operation_id, count(*) AS strays
FROM telemetry_point t
JOIN operation o USING (operation_id)
JOIN field_season fs USING (field_season_id)
JOIN field_boundary b ON b.field_id = fs.field_id AND b.valid_to IS NULL
WHERE NOT ST_DWithin(t.geom::geography, b.geom::geography, 30)
GROUP BY t.operation_id HAVING count(*) > 100;

All four must return zero rows. The fourth is the one that catches a bad field match: telemetry from a neighbouring field attached to the wrong canonical field shows up as thousands of strays on one operation, which no downstream step would otherwise notice.

Prove the constraints work by attempting the violations in the test suite — insert two overlapping boundary periods, insert a duplicate manifest row, insert a Polygon where MultiPolygon is declared — and assert each raises. A constraint nobody has ever tripped is an assumption, not a guarantee.

6. Integration with the Broader Pipeline

This schema is the store that everything else reads and writes. Machine data APIs write fields, boundaries, operations and telemetry into it; satellite imagery APIs and STAC catalogues write raster_artifact rows pointing at objects created by cloud-optimized storage for field imagery; orchestrating seasonal pipelines with Airflow reads the manifest to decide what work remains. On the way out, the boundary is the clip geometry for clipping rasters to field boundaries and the export extent for variable-rate export to ISOXML.

Getting Python objects into these tables efficiently — type mapping, SRID handling, batch sizes — is covered in loading GeoDataFrames into PostGIS with GeoAlchemy2, and keeping the big tables fast is covered in spatial indexing and partitioning yield points.

Frequently Asked Questions

Can I skip the crosswalk and just store the provider identifier on the field? Only if you will never integrate a second source, never re-import a monitor export, and never have a grower change management systems. All three happen. The crosswalk costs one table and one partial unique index; retrofitting it after two seasons of data means reconciling identifiers by hand.

Should management zones be stored as geometry or as rasters? Both, for different consumers. Store the vectorised zones as MultiPolygon for prescription export and map display, and keep the classified raster as an object with a raster_artifact row for anything pixel-based. Deriving one from the other on demand is slower than storing both and less reproducible.

How big can the boundary table get before it needs attention? It stays small — one row per boundary version, so a few thousand rows for a large operation over a decade. The table that grows is telemetry, and the rule of thumb is that a season of a 400-field operation adds tens of millions of rows. Plan the partitions before the first harvest, not after the third.

This topic is part of Farm Data Platform Engineering: APIs, Storage & Orchestration — see there for the platform layers this schema sits inside.

Related

• Spatial Indexing and Partitioning Yield Points in PostGIS — keeping a hundred-million-row telemetry table at millisecond queries
• Loading GeoDataFrames into PostGIS with GeoAlchemy2 — type mapping, SRID handling and bulk-load patterns from Python
• Machine Data APIs for Management-System Integration — where the field, operation and telemetry rows come from
• Cloud-Optimized Storage for Field Imagery — the object side of the raster metadata table
• Yield Monitor Data Cleaning & Telemetry QA — the first consumer of the telemetry partitions