Route forecasts

Documentation Index

Fetch the complete documentation index at: https://docs.axiomancer.io/llms.txt

Use this file to discover all available pages before exploring further.

​

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

​

What gets forecast

• 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

​

The dataset

​

vessel_route_forecasts

​

vessel_route_forecast_waypoints

​

How it’s computed

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

​

Querying

-- 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

• 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.

-- 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.