Route forecasts

For every vessel currently in transit, Axiom Overwatch projects a great-circle waypoint sequence to the vessel's declared destination port and an ETA distribution (p10 / p50 / p90) at each waypoint. This gives downstream consumers a forward-looking view of where every active vessel is going and when it is expected to arrive — at the ship level, not just at the port aggregate level.

When to use this

• Power a vessel detail view that shows the projected path and arrival window
• Estimate inbound cargo arrivals by joining forecasts to draft-derived tonnage
• Monitor vessels that are running materially behind their declared ETA
• Pre-position alerts based on which vessels will reach a region in the next 24 / 48 / 72 hours

If you only need port-level cargo volume forecasts, use the Forecasts API. Use the route-forecast dataset when you need a per-vessel, per-waypoint breakdown.

What gets forecast

Route forecasts are produced for vessels that meet all of these conditions on the hourly run:

• Latest AIS position is less than 6 hours old
• Vessel is outside every defined port_zone (entering port-call mechanics is out of scope)
• Vessel has a non-null AIS-declared destination string
• Resolved destination is at least 30 nautical miles away

Vessels that fail any of these checks are silently skipped on the run and re-evaluated the next hour.

The dataset

Each qualifying vessel produces one forecast row per hourly run plus eight waypoint rows keyed by forecast_id. Old rows are retained so accuracy can be backtested later.

vessel_route_forecasts

vessel_route_forecast_waypoints

Eight rows per forecast (waypoint_index 0 through 7). Index 0 is the origin, index 7 is the destination, and the six intermediate points are spaced evenly along the great-circle arc.

How it's computed

Each hour at minute :25 (offset from AIS polling at :15 and the latest-positions matview refresh) the compute-route-forecasts job runs:

1. Pull active vessels. Read mv_latest_positions filtered to non-null destinations within the last 6 hours, capped at 500 vessels per run.
2. Resolve the destination. Match the AIS destination string against the ports_with_centroid view via a four-step cascade — exact slug, exact name, last whitespace-separated tail token (handles strings like NL RTM > BR SSZ), then a final ILIKE fragment match. Skip the vessel if no port resolves.
3. Filter port-call mechanics. Skip vessels currently inside any port_zone (resolved via the point_inside_any_port_zone PostGIS RPC) and vessels within 30 NM of their destination.
4. Build the great-circle arc. Slerp eight waypoints between origin and destination centroid on a unit sphere.
5. Compute the ETA distribution. Project ETA at each waypoint using cumulative_distance / projection_speed. The p10 band is 20% faster than p50 and the p90 band is 30% slower (vessels lose more speed than they add).
6. Persist one forecast row plus eight waypoint rows.

Projection speed

The projection speed is the vessel's current SOG when SOG is at least 5 knots. Below that threshold the SOG is considered too noisy or stationary to extrapolate (drifting, engine-down, or spoofed-stationary) and the run falls back to a class-typical cruise speed:

The same projection speed is used for every waypoint in a single forecast. The next hourly run picks up any speed change.

Querying

The dataset lives in vessel_route_forecasts and vessel_route_forecast_waypoints. Both tables are RLS-protected with read-all and service-role-write policies. Indexes on (imo_number, computed_at DESC) and (destination_port_id, computed_at DESC) cover the most common access patterns.

-- Latest forecast for one vessel, with its eight waypoints in order.
select
  f.imo_number,
  f.destination_port_id,
  f.total_distance_nm,
  f.current_speed_knots,
  w.waypoint_index,
  w.lat,
  w.lng,
  w.cumulative_distance_nm,
  w.eta_p10,
  w.eta_p50,
  w.eta_p90
from vessel_route_forecasts f
join vessel_route_forecast_waypoints w on w.forecast_id = f.id
where f.imo_number = $1
order by f.computed_at desc, w.waypoint_index asc
limit 8;

-- All vessels with a p50 ETA inside the next 48 hours at a destination port.
select distinct on (f.imo_number)
  f.imo_number,
  f.current_speed_knots,
  w.eta_p50 as expected_arrival,
  w.eta_p10 as earliest,
  w.eta_p90 as latest
from vessel_route_forecasts f
join vessel_route_forecast_waypoints w on w.forecast_id = f.id
where f.destination_port_id = $1
  and w.waypoint_index = 7
  and w.eta_p50 between now() and now() + interval '48 hours'
order by f.imo_number, f.computed_at desc;

-- Plot the projected great-circle path as a line for the latest forecast.
select array_agg(
  array[w.lng, w.lat] order by w.waypoint_index
) as line
from vessel_route_forecasts f
join vessel_route_forecast_waypoints w on w.forecast_id = f.id
where f.imo_number = $1
  and f.computed_at = (
    select max(computed_at) from vessel_route_forecasts where imo_number = $1
  );

Helper view and RPC

The migration ships two reusable helpers because ports itself has no lat/lng columns:

• ports_with_centroid — view exposing (id, slug, name, country, lat, lng) derived from each port's port_zones geometry. Prefers the geofence zone and falls back to any zone. SECURITY INVOKER, granted to anon, authenticated, and service_role.
• point_inside_any_port_zone(p_lat numeric, p_lng numeric) — STABLE PostGIS RPC returning true when the point sits inside any defined port zone.

Both are useful outside the forecaster. Use the view whenever you need a port centroid without joining port_zones manually.

-- Top 10 ports closest to a given lat/lng.
select id, slug, name,
  earth_distance(
    ll_to_earth($1, $2),
    ll_to_earth(lat::float, lng::float)
  ) / 1852 as nm_away
from ports_with_centroid
where lat is not null and lng is not null
order by nm_away asc
limit 10;

Operational notes

• Per-vessel errors (resolution failure, insert failure) are caught individually and logged. The run bails after 10 errors to avoid log floods.
• Every run writes one row to ingestion_logs with source = 'compute_route_forecasts'. status = 'partial' indicates per-vessel errors; status = 'failed' indicates a fatal error before any vessels were processed.
• The hourly cron job is registered as compute-route-forecasts-hourly. Re-running the migration upserts the schedule by name and is safe.