Vector data — decoded surface observations (TimescaleDB / PostGIS)¶

Goal: publish continuously decoded surface observations (SYNOP, METAR…) as WMS layers with a TIME dimension and as WFS features, readable by every replica the moment they are inserted, with the canonical unit carried through (ADR-0003).

flowchart LR
    SRC[SYNOP · METAR · …] --> DEC[Decoding service<br/>simulator in this prototype]
    DEC -- "INSERT (canonical unit,<br/>source_unit kept)" --> DB[(schema obs<br/>station · observation hypertable<br/>publication views)]
    GS[GeoServer ×N] -- "jdbc/meteo (read-only,<br/>datastore obs-db, schema=obs)" --> DB
    PLUG[geoserver-init plugin<br/>VECTOR_LAYERS ConfigMap] -- "feature types + TIME dimension<br/>title/unit from reference data" --> GS
    C[WMS TIME · WFS · CQL] --> GS

Hold "Alt" / "Option" to enable pan & zoom

Decision: ADR-0011. Artifacts: poc/06-vector/ (schema, Jobs, tests) and the geoserver-init plugin (ensureVectorLayers).

Prerequisites¶

Sub-projects 1 to 5 (database, image, cluster, S3, reference data in mosaic.parameter). Secrets: decoder-db (decoding-service account), meteo-reader.

Data model (schema `obs`, owned by the decoding service)¶

Object	Role
`station`	One row per station: identifier (ICAO/WMO), name, `geom Point 4326`, elevation
`observation`	TimescaleDB hypertable, PK `(station_id, time, param_key)` + surrogate `fid` ; `value` in the canonical unit, `source_unit` kept (METAR temperatures arrive in Celsius), `message_type`
`obs_air_temperature` (view)	Full series joined with stations — backs the TIME-enabled layer
`obs_latest_air_temperature` (view)	Latest value per station (`DISTINCT ON … ORDER BY time DESC`) — backs the map-friendly layer
`obs.gt_pk_metadata` (view)	Exposes the virtual key `fid` of the views to GeoTools

The schema is created by a Job run with the operator account (k8s/job-init-obs-schema.yaml); GeoServer's reader gets SELECT through the grant-reader Job (DATA_SCHEMA=obs). The two init Jobs may race: the grant Job simply retries until the schema exists (backoff).

Volumetry at scale (one row per parameter — does it hold?)¶

A message carries dozens of parameters, so the row-per-parameter model must be sized honestly. Order of magnitude for the full GTS: ~20 000 SYNOP/METAR messages per hour × ~30 parameters ≈ 600 k rows/hour ≈ 5 billion rows per year — several hundred GB raw with the current row layout. The stack is chosen for exactly this:

Time chunking (hypertable): queries only touch the chunks in their time range; indexes are per-chunk and stay small — never one year-wide index.
Native columnar compression on chunks older than a threshold (segment_by station_id, order_by time): typically 90–95 % reduction on this kind of data, bringing a year down to tens of GB while remaining queryable by GeoServer.
Integer keys (planned with the real decoders): param_key text → smallint FK to the referential and station_id text → int surrogate roughly halve the row again.
Retention: keeping a full year of raw observations online in the GIS database is a policy choice, not a requirement (drop_chunks + archive) — see ch. 11, retention ownership.

Why row-per-parameter rather than one column per parameter: message families are heterogeneous (a METAR and a SYNOP do not carry the same quantities — a wide table is sparse everywhere), a new parameter is a referential row instead of an ALTER TABLE on billions of rows, per-quantity publication views stay trivial, and columnar compression makes the narrow layout cheap. A raw message table (one row per received message, for audit/replay) can live alongside the values without changing this model.

Decoder simulator¶

ingest.py obs --from 2026-09-02T06:00Z --hours 3 --step-min 10 (same container as the raster ingestion) upserts 10 demo stations across Europe and Asia and generates one observation per station and per 10-minute step: synthetic 2 m temperature in Celsius, converted to kelvin on insert (source_unit=Cel), alternating METAR/SYNOP message types. Real SYNOP/BUFR/TAC decoding is out of scope of the prototype. Run it as a Job (k8s/job-ingest-obs.yaml, via ../05-ingestion/ingest.sh); a CronJob or the real decoding service takes over in production.

Publication by the plugin (declarative)¶

VECTOR_LAYERS="name|view|timeAttribute(- for none)|param_key|title;…" in the geoserver-mosaics ConfigMap. On each pod start the plugin ensures:

a second datastore obs-db on the same JNDI resource jdbc/meteo with schema=obs (one datastore per schema; the stores always name a JNDI resource, never a URL);
one feature type + layer per view, with TIME enabled on the given attribute (continuous interval presentation — listing every observation instant would be meaningless), default = most recent;
title and abstract carrying the canonical unit read from mosaic.parameter (Surface observations — air temperature (K)).

No GWC tile layer is created for observation layers: requests are time-qualified and served straight by WMS.

Verification (tests `poc/06-vector/tests/`)¶

Schema, grant (retry visible), 180 observations ingested:

Startup: datastore and layers created/verified:

01 Capabilities: titles with the unit, continuous TIME dimension (start/end/PT1S, default = latest):

02 The TIME dimension really filters: an in-range instant renders the stations; an out-of-range instant is rejected with an explicit ServiceException (fail loud — consistent with the project rule); WFS counts per instant: 10, 10, 0. Two valid instants render identical images by design (the default point style does not encode the value — thematic styling comes with sub-project 8):

03 One more hour ingested → TIME extent and feature counts grow on both pods, no restart, no notification (the views read the live tables):

04 The reader stays read-only (permission denied on INSERT) and the layers survive a pod replacement:

Deviations and pitfalls observed¶

A WMS request outside the TIME domain returns a ServiceException (Could not find a match for 'time' value) rather than an empty map — document it to client teams.
Identical images for two valid instants are expected with the default style; the real proof of filtering is WFS/CQL counts (test 02).
gt_pk_metadata must be visible inside the datastore's schema: the obs schema exposes a filtered view over the shared table.