# Database setup and AIS ingestion

End-to-end guide for getting from “nothing” to “OMRAT querying real AIS linestring segments out of PostGIS.” If you only have a project `.omrat` file and no AIS database, read this chapter first — every Update AIS / Update all distributions button in OMRAT reads from the database described here.

## Architecture in one paragraph

OMRAT’s risk calculations consume constant-COG/SOG linestring segments out of PostGIS — one row per `(MMSI, time-window, course/speed)`. Those rows are produced by feeding raw AIS pings through the TDKC compression algorithm (Guo et al. 2024) implemented in the AISsegments sister package.

The full pipeline:

    raw AIS files (NMEA / CSV)
         |
         v   aisdb.decode_msgs()
    per-vessel point streams (temp SQLite)
         |
         v   aissegments.tdkc_segments()
    constant-COG/SOG linestring segments
         |
         v   psycopg2 + PostGIS bulk insert
    {schema}.segments_YYYY_M  +  {schema}.statics_YYYY  +  {schema}.states_YYYY
         |
         v   omrat_utils.handle_ais.run_sql()
    risk-analysis queries

How TDKC compression turns dense per-vessel pings into the constant-COG/SOG linestring segments OMRAT actually queries. The middle panel keeps only the pings where course or speed changes meaningfully (see Threshold tuning); the right panel chains the kept pings into one PostGIS row per “leg” of the voyage.

## 1. Stand up the database

The easiest path on a developer machine is the bundled Docker stack (`docker/` in the repository).

    cd <OMRAT repo root>
    cp docker/env.example docker/.env       # edit credentials if you want
    docker compose -f docker/docker-compose.yml up -d

That gives you `localhost:5432` with PostGIS 3.4 enabled, running as user `omrat` / database `omrat`.

For a remote or institutional database, just enable PostGIS and create a role with `CREATE` privileges on the database — see the wizard’s Database capabilities page for what it checks.

## 2. Install OMRAT’s Python dependencies

OMRAT’s `requirements.txt` lists aissegments and aisdb alongside the existing dependencies. QGIS plugin loads pull these in via `qpip` (configured in `metadata.txt`).

For development outside QGIS:

    pip install aissegments aisdb

## Supported AIS file formats

The ingestion pipeline auto-classifies each input file by extension plus a header sniff and routes it to the right decoder:

File pattern | Header signature | Routed to | Static AIS data?
--- | --- | --- | ---
`*.nm4`, `*.nmea` | (binary NMEA) | `aisdb.decode_msgs()` | ✓ from Type-5 messages
`*.csv` / `*.csv.gz` with `Message_ID` / `Repeat_indicator` | aisdb’s own CSV dump | `aisdb.decode_msgs()` | ✓
`*.csv` / `*.csv.gz` without those columns (Marine Cadastre, custom exports) | generic AIS CSV | `aissegments.read_csv_tracks()` + `read_csv_static_records()` | ✓ when static columns are present (Length/Width/Draft/IMO/…)

For the simple-CSV path (Marine Cadastre and similar), AISsegments recognises a wide range of column-name aliases — `BaseDateTime` / `timestamp` / `time`, `LAT` / `latitude` / `lat`, `LON` / `longitude` / `lon`, etc. — and parses ISO 8601 timestamps as well as Unix seconds.

If the CSV also carries vessel-info columns (Marine Cadastre’s `VesselName` / `IMO` / `CallSign` / `VesselType` / `Length` / `Width` / `Draft`), the pipeline extracts them via `aissegments.read_csv_static_records()` and populates `statics_YYYY` and `states_YYYY` accordingly. Length and Width get split half/half into AISdb’s per-quadrant antenna offsets (`dim_a` = `dim_b` = Length/2 and `dim_c` = `dim_d` = Width/2 — a centred-antenna approximation).

If the CSV is bare (just `mmsi` / `time` / `lon` / `lat` / `sog` / `cog`), the identity and voyage fields land as NULL placeholders. Downstream AIS queries derive vessel length and beam directly from the AIS Type-5 dimensions (`loa = dim_a + dim_b`, `beam = dim_c + dim_d`); ship-type classification uses the AIS `type_and_cargo` field. Air-draught distributions stay empty unless you supply a richer vessel registry of your own and reinstate a JOIN in `omrat_utils/handle_ais.py`.

## 3. Walk the wizard

Open QGIS, load the OMRAT plugin, then Settings → Database setup wizard…. The wizard has five pages:

1. Intro — quick orientation; offers a button to open the Docker quickstart (`docker/README.md`).

2. Connection — host / port / db / user / password / schema / sslmode. Click Test connection. Once a probe succeeds the Next button activates.

3. Database capabilities — runs `DbProbe` and lists what’s in place vs. what’s missing. Buttons:

   - Enable PostGIS (only if the user is superuser; otherwise displays the SQL for a DBA to run).

   - Apply OMRAT schema migrations — creates `omrat_meta` (version table + ingestion watermark) and the AIS schema you configured.

   - Create year-partitioned tables for `<year>` — provisions `statics_YYYY`, `states_YYYY`, and the 12 monthly partitions of `segments_YYYY`.

4. Ingest AIS data (optional) — the ingestion page:

   - Pick AIS files (NMEA `.nm4`, aisdb `.csv`, gzipped variants).

   - Set `min_sed_m` (default 30 m) and `min_svd_kn` (default 0.3 kn) — these are the OMRAT-tuned TDKC threshold floors.

   - Set the target year and a source label.

   - Click Run ingestion. The job runs on a `QThread`; progress messages stream into the log view. Per-MMSI: insert one `statics_YYYY` row, one `states_YYYY` row, and a batch of `segments_YYYY_M` linestring rows from `tdkc_segments(...)`.

5. Done — saves the connection profile and ingestion settings to QSettings (`omrat/db_profiles/default/*` and `omrat/ingest_profiles/default/*`). The legacy flat keys read by `omrat_utils/handle_ais.py` are mirrored automatically, so existing AIS-traffic queries pick up the new credentials transparently.

## 4. Verify with a smoke test

After the wizard finishes you can run a one-shot end-to-end check by pointing OMRAT at a small fixture. AISdb’s bundled `test_data_20210701.csv` is the easiest:

    from pathlib import Path
    import aisdb

    from omrat_utils.db_setup import (
        ConnectionProfile, IngestionSettings, Migrator,
    )
    from omrat_utils.handle_ais_ingest import IngestionPipeline

    profile = ConnectionProfile.from_qsettings()
    settings = IngestionSettings.from_qsettings(profile.name)

    # Make sure the year tables exist for the dataset's date range.
    Migrator(profile).apply_pending()
    Migrator(profile).ensure_year_partition(2021)

    aisdb_csv = (
        Path(aisdb.__file__).parent / "tests" / "testdata"
        / "test_data_20210701.csv"
    )
    result = IngestionPipeline(profile, settings).run([aisdb_csv], year=2021)
    print(result.summary())

Then in `psql`:

    SELECT count(*) FROM omrat.segments_2021;
    SELECT count(*) FROM omrat.statics_2021;
    SELECT count(*) FROM omrat_meta.segment_watermark;

You should see non-zero counts in all three.

## 5. Run risk analysis as before

Once the segments table is populated, `omrat_utils.handle_ais.run_sql` queries `{schema}.segments_{year}_{month}` exactly as it always did — the schema layout is intentionally compatible. The AIS connection settings menu in OMRAT’s main dialog reads from the same QSettings keys the wizard saved, so traffic-fetching for legs and segments works without any additional config.

See User Guide (“Importing from AIS” section) for the plugin-side workflow once the database is populated.

## Database schema reference

The schema matches OMRAT’s legacy (sjfv) layout used by `omrat_utils/handle_ais.py`. Two metadata tables in `omrat_meta` plus a year-partitioned trio in the user-configured schema:

    omrat_meta.schema_version       (version PK, name, applied_at)
    omrat_meta.segment_watermark    (mmsi PK, last_t, last_run_at, n_segments)

    {schema}.statics_YYYY            <- per-vessel IDENTITY (changes ~never)
       rowid  bigserial PK
       mmsi   bigint
       date   timestamptz          (when this identity row was reported)
       dim_a  smallint             (antenna->bow, m)
       dim_b  smallint             (antenna->stern)
       dim_c  smallint             (antenna->port)
       dim_d  smallint             (antenna->starboard)
       imo_num bigint

    {schema}.states_YYYY             <- per-VOYAGE static data (changes per leg)
       rowid  bigserial PK
       mmsi   bigint
       date   timestamptz
       draught         double precision
       type_and_cargo  smallint
       eta             timestamptz   (NULL in v0.1; populated in v0.2)
       destination     varchar(20)
       static_id       bigint  -> statics_YYYY.rowid  ON DELETE SET NULL

    {schema}.segments_YYYY           <- TDKC linestring rows
                                     (PARTITION BY RANGE date1)
       rowid    bigserial
       mmsi     bigint
       date1    timestamptz NOT NULL  (segment start; partition key)
       date2    timestamptz           (segment end)
       segment  geometry(LineString, 4326)
       cog      smallint              (mean course, [0, 359])
       sog      double precision      (mean speed, knots)
       route_id bigint                (NULL on initial ingestion)
       state_id bigint                (logical link to states_YYYY.rowid)
       heading  smallint              (NULL on initial ingestion)
       PRIMARY KEY (rowid, date1)
         segments_YYYY_1   PARTITION OF segments_YYYY  for Jan
         segments_YYYY_2   PARTITION OF segments_YYYY  for Feb
         ... segments_YYYY_12  for Dec

Differences from the legacy schema:

- Postgres declarative partitioning on `segments_YYYY` (was a single yearly table or per-month UNION). Queries against `segments_YYYY_M` continue to work; `segments_YYYY` now also works thanks to partition pruning. The composite PK `(rowid, date1)` is a Postgres requirement for partitioned-table unique constraints — `rowid` alone is still unique by virtue of `bigserial`.

- ``segment`` is constrained to `geometry(LineString, 4326)`; legacy used the unconstrained `public.geometry`. The constraint catches insert errors but is invisible to existing `ST_Intersects` queries.

- Foreign key ``states_YYYY.static_id`` → ``statics_YYYY.rowid`` is declared; `segments_YYYY.state_id` is not a hard FK (FKs from a partitioned table get nuanced in older PG).

## Static + state row deduplication

The pipeline does NOT insert a new `statics_YYYY` or `states_YYYY` row when the data is identical to the most recent existing row for that MMSI. Each ingestion path:

1. Pre-loads the latest `(mmsi, identity_tuple)` pairs for statics and the latest `(mmsi, voyage_tuple)` pairs for states from the DB, in one query each (using PostgreSQL’s `DISTINCT ON (mmsi)`).

2. For every track, computes the new identity / voyage tuple and compares to the cached one. Match → reuse the existing rowid, no INSERT. Mismatch → INSERT a new row and update the cache.

Identity tuple for `statics_YYYY`: `(dim_a, dim_b, dim_c, dim_d, imo_num)`.

Voyage tuple for `states_YYYY`: `(draught, type_and_cargo, destination, eta, static_id)`.

This means:

- A vessel reported with the same dimensions across multiple ingestion runs gets one `statics_YYYY` row total.

- A vessel reporting the same draught + destination + ETA on every message gets one `states_YYYY` row.

- A draught change, destination change, ETA change, or new IMO triggers exactly one new row. Older segments still link to the older row; newer segments link to the newer one. This gives time-windowed static linking for free as voyage data evolves.

The summary line counts both inserts and reuses, e.g.:

    Ingested 5 file(s) -> 1234 tracks, 24500 segments,
    12 static, 145 state rows, reused 1222 static + 1089 state in 412.3s

## ETA combination

The legacy schema’s `states_YYYY.eta` is a `timestamptz`. AIS Type-5 static messages encode ETA as four separate fields (`eta_month`, `eta_day`, `eta_hour`, `eta_minute`) with no year — the year is implicit in the report. The pipeline combines them using the ingestion target year as the year hint, with the AIS spec sentinels (`month=0`, `day=0`, `hour=24`, `minute=60`) and invalid calendar dates (Feb 30, etc.) all mapped to NULL.

ETA is part of the voyage tuple, so a voyage with the same draught and destination but a corrected ETA still produces a new `states_YYYY` row.

## Incremental ingestion (re-runs are cheap)

Each successful per-MMSI insert updates `omrat_meta.segment_watermark` with the latest AIS timestamp seen for that vessel. On the next run (`incremental=True`, the wizard’s default), the pipeline:

1. Reads the watermark table at the start of each ingestion path.

2. For every track it sees, drops any pings with `time <= last_t` for that MMSI before passing the track into TDKC.

3. If nothing remains after the filter, the track is recorded under `n_tracks_skipped_watermark` and skipped silently.

This makes overlapping or repeated ingestions safely idempotent — point the wizard at the same files twice and the second run inserts zero new segments (you’ll see “… skipped (watermark)” in the summary).

To force a full re-ingest, uncheck Incremental in the wizard or pass `incremental=False` to `IngestionPipeline.run(...)`. Typically you’d also `TRUNCATE` the relevant `segments_YYYY_M` partitions first, otherwise you’ll duplicate rows.

## Index strategy: build after bulk load

The `segments_YYYY` table can hold tens of millions of rows after a single year of dense AIS data. Maintaining the GiST and btree indexes during bulk INSERT slows ingestion by a factor of 5-10× — so the migration that creates the tables deliberately leaves them unindexed for `segments_YYYY`. Indexes on `statics_YYYY` and `states_YYYY` (which hold thousands of rows, not millions) are created inline since their maintenance cost is negligible.

After ingestion finishes, the pipeline runs `Migrator.create_year_indexes(year)` once on the populated tables, building:

Index | Column(s) | Used by
--- | --- | ---
`segments_YYYY_geom_gix` | GiST on `segment` | `ST_Intersects` corridor queries in `handle_ais.py`
`segments_YYYY_mmsi_date_idx` | `(mmsi, date1)` | Per-vessel time-window lookups
`segments_YYYY_state_id_idx` | `state_id` | JOIN to `states_YYYY` for ship metadata
`segments_YYYY_route_id_idx` | `route_id` | Downstream route-tagging queries

The index step also runs `ANALYZE` on all three tables so the planner picks the new indexes up immediately.

`IngestionPipeline.run(...)` does this automatically when finished (`create_indexes_after=True` by default). If you ingest several batches into the same year and want to delay indexing until the end, pass `create_indexes_after=False` on every batch except the last. Calling `create_year_indexes(year)` more than once is safe — every CREATE uses `IF NOT EXISTS`.

## Threshold tuning

TDKC keeps a ping whenever either of two measurements crosses its threshold floor:

- `min_sed_m` — Synchronized Euclidean Distance. How far the ping is from where it would be at its timestamp on the straight line between the two adjacent kept pings. A large SED means the vessel is turning.

- `min_svd_kn` — Synchronized Velocity Difference. How different the ping’s reported speed-over-ground is from the speed implied by the same chord. A large SVD means the vessel is accelerating or decelerating.

What `min_sed_m` and `min_svd_kn` measure for one candidate ping `P2` between two adjacent kept pings `P1` and `P3`. The candidate is dropped only if both SED and SVD fall below their thresholds — so a tight turn or a sudden speed change is enough on its own to keep the ping.

`min_sed_m` and `min_svd_kn` are per-ingestion — they’re settings, not constants. Edit them in the wizard’s Ingest page or override programmatically:

    settings = IngestionSettings(min_sed_m=10.0, min_svd_kn=0.1)

Sensible regimes:

Use case | `min_sed_m` | `min_svd_kn` | Effect
--- | --- | --- | ---
OMRAT default | 30 m | 0.3 kn | Risk-grade compression, filters AIS jitter.
High-fidelity research | 5 m | 0.05 kn | Preserves more detail, larger DB footprint.
Long-term archive | 100 m | 1.0 kn | Aggressive compression, ~order-of-magnitude smaller.
Paper-faithful (Guo et al.) | 0 | 0 | No floor; uses pure adaptive thresholds.

See AISsegments docs/algorithm.md for the algorithm details and parameter visualisations.

## Operational notes

- Idempotent: re-running the wizard or `apply_pending()` is safe. Schema migrations use `IF NOT EXISTS`; the migrator records its version in `omrat_meta.schema_version`.

- Watermark: `omrat_meta.segment_watermark` records the last AIS timestamp seen per MMSI. This is what the pipeline uses to skip already-ingested data on reruns.

- Threading: the ingestion worker uses `QThread`; the UI stays responsive. Cancellation isn’t wired up yet — close the wizard or stop QGIS to abort.

- Batch commits: the worker commits every 200 tracks so a long run doesn’t sit in a single transaction the entire time.

- Static-data caveat: in the v0.1 cut each MMSI gets one `statics_YYYY` row + one `states_YYYY` row per ingestion run. Vessels that change registration mid-period get a less precise mapping; the schema supports proper time-windowed linkage and a future iteration can populate it without changing the on-disk format.

## Troubleshooting

“PostGIS extension is missing”

  Either run `CREATE EXTENSION postgis;` as a DB superuser, or use the bundled Docker stack which preloads it.

“Year-partition tables missing”

  The wizard’s Capabilities page has a year-spinbox + Create tables button. Or call `Migrator(profile).ensure_year_partition(year)` programmatically.

Ingestion is slow

  Expected; profiling and parallelisation are on the v0.2 roadmap. For one Baltic month (~10M points) on a developer laptop, expect tens of minutes. Bulk inserts already use `psycopg2.extras.execute_values`; the next bottleneck will likely be TDKC itself, where Numba acceleration inside AISsegments is a clear candidate.

“AIS connection settings” menu shows defaults / blank fields

  The wizard writes credentials under `omrat/db_profiles/default/*` and also mirrors them to the legacy flat keys. If your install shows blanks, run the wizard once to seed both layouts.