Short answer

A geological data API should expose a stable domain model, boreholes, samples, mapped units, contacts, structural measurements, not whatever shapefiles happen to be in the project folder. The reliable path is a three-layer separation: raw ingestion, a normalised PostGIS store, and a thin public API that follows OGC API - Features so existing GIS clients can consume it without custom code. Get four things right and the rest follows: a clean schema, an explicit CRS policy, versioning that lets a consumer reproduce a past result, and metadata that travels with every feature.

Layer the system before you choose a framework

The most common failure is wiring an API directly onto source files. Sources are messy and change; a public contract must not. Separate three concerns:

  1. Ingestion. Loaders that pull from drilling exports, lab CSVs, field GeoPackages, and legacy shapefiles, normalising units and CRS on the way in. This layer is allowed to be ugly and is rewritten freely.
  2. Normalised store (PostGIS). The single source of truth with a stable schema, constraints, and spatial indexes. Analysis and the API both read from here.
  3. Public API. A read-mostly interface that projects the store into documented collections. It changes only on a deliberate, versioned schedule.

This separation is what lets you re-ingest a corrected lab batch without breaking a single downstream consumer.

A workable PostGIS schema

Model the geological entities explicitly rather than dumping attributes into one wide table. A minimal core:

CREATE TABLE borehole (
  id            bigserial PRIMARY KEY,
  hole_id       text UNIQUE NOT NULL,
  geom          geometry(PointZ, 32633) NOT NULL,  -- collar, UTM 33N + height
  collar_elev_m double precision,
  total_depth_m double precision,
  drilled_on    date,
  source_id     int REFERENCES source(id),
  valid_from    timestamptz NOT NULL DEFAULT now(),
  valid_to      timestamptz
);

CREATE INDEX borehole_geom_gix ON borehole USING GIST (geom);

CREATE TABLE sample (
  id          bigserial PRIMARY KEY,
  borehole_id bigint REFERENCES borehole(id),
  from_m      double precision NOT NULL,
  to_m        double precision NOT NULL,
  lithology   text,
  CHECK (to_m > from_m)
);

Note the choices: a single canonical projected CRS in the geometry column (SRID 32633 here) declared with ST_SetSRID on insert; a GIST index so spatial filters are fast; CHECK constraints that enforce geological sanity (a sample interval cannot end above where it starts); and valid_from/valid_to so history is preserved rather than overwritten. Set SRID explicitly on ingest:

INSERT INTO borehole (hole_id, geom, ...)
VALUES ('BH-014', ST_SetSRID(ST_MakePoint(512345, 6712345, 412.3), 32633), ...);

Expose it with OGC API - Features

Rather than inventing query parameters, adopt OGC API - Features. It defines a small, well-understood resource tree:

  • GET /collections — the catalogue of feature types (boreholes, samples, contacts).
  • GET /collections/boreholes/items — paginated GeoJSON of features, with bbox, datetime, limit, and property filters.
  • GET /collections/boreholes/items/{id} — a single feature.

Because the output is GeoJSON over HTTP with a documented filter grammar (CQL2 in the extended conformance classes), QGIS, ArcGIS Pro, and web clients can connect natively. Implementations like pygeoapi can sit directly on a PostGIS table, so you publish the store without hand-writing endpoints. Reserve the deeper parts of your relational model for analysis and surface clean, flat feature collections through the API.

CRS policy: pick one canonical frame, reproject on output

Store everything in one CRS, ideally a projected metric CRS that matches your analysis (UTM zone, EPSG:32633 in the example). Advertise it. On output, reproject with ST_Transform:

SELECT jsonb_build_object(
  'type','Feature',
  'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,
  'properties', to_jsonb(b) - 'geom'
)
FROM borehole b
WHERE ST_Intersects(geom, ST_Transform(ST_MakeEnvelope(:minx,:miny,:maxx,:maxy,4326), 32633));

OGC API - Features serves CRS84 (WGS84 lon/lat, GeoJSON default) unless the client requests another via the crs parameter, so reprojecting to 4326/CRS84 on output while storing in a metric CRS is the standard pattern. Note the envelope is transformed into the storage CRS so the spatial index is used; transforming every row's geometry to match the query bbox would defeat the index.

Versioning so results are reproducible

Two kinds of versioning, and you need both:

  • Schema version lives in the path: /v1/collections/.... A breaking change to fields or semantics becomes /v2/, and /v1/ keeps working for existing consumers.
  • Data version lives in the temporal columns. Because rows are closed (valid_to set) rather than deleted, a consumer can request datetime=2026-01-01T00:00:00Z and get exactly the data as it stood then. This is what makes a report reproducible months later, which in geological and regulatory work is often a hard requirement.

Metadata that travels with the data

Every feature should carry, or link to, its provenance: source dataset, acquisition/drill date, the CRS it was captured in, the lab method, and a licence. Expose dataset-level metadata at the collection level (OGC API - Records is the companion standard for catalogues). Without this, a consumer cannot judge whether a borehole assay is fit for their decision, and the API becomes a pile of numbers with no traceability.

Pagination, performance, and stability

Use cursor or offset pagination with a hard limit default (say 1000) and a next link, as OGC API - Features prescribes, so a careless client cannot pull a million features in one request. Keep the GIST index on every geometry column, and consider a materialised view for expensive joins that the API reads frequently, refreshed on ingest rather than per request.

Common pitfalls and why they happen

  • APIs glued straight to source files. Sources change shape; the contract then breaks for everyone. Insert the normalised PostGIS layer between them.
  • No declared CRS, or reprojecting per row against the query bbox. The first makes geometries ambiguous; the second kills the spatial index. Store one CRS, transform the query envelope into it, reproject output.
  • Overwriting records on update. This destroys reproducibility. Use validity timestamps and close old rows instead.
  • A bespoke query language. You will reinvent filtering, paging, and bbox poorly. OGC API - Features already specifies them and is supported by mainstream clients.
  • Metadata as an afterthought. Without provenance and licence per feature, consumers cannot trust or legally reuse the data.

QA validation

Confirm the schema enforces geological constraints (interval ordering, unique hole ids); every geometry column has an SRID and a GIST index; the API advertises its storage CRS and serves CRS84 by default; pagination has a default and maximum limit; a datetime query returns a reproducible historical snapshot; and each feature exposes source, date, and licence.

Bathyl perspective

We design geological APIs as durable assets, not one-off exports: a normalised PostGIS core, an OGC-standard surface so any GIS client connects without bespoke code, and temporal versioning so a number served today can be reproduced next year. The standards do the heavy lifting; the discipline is keeping the public contract stable while the messy ingestion underneath evolves freely.

Related reading

Sources