Real-Time Port Operations Monitoring with SQL

Ports handle thousands of vessel movements, crane cycles, truck gate transactions, and cargo transfers every day. Coordinating this complexity in real time — knowing which berths are occupied, which vessels are approaching, which cranes are idle, and which transfers are behind schedule — requires a data layer that keeps pace with physical operations. RisingWave, a PostgreSQL-compatible streaming database, provides exactly this with streaming SQL and materialized views.

Why Port Operations Need Real-Time Monitoring

Modern container ports are extraordinarily complex operational environments. A single vessel call involves coordinating the vessel's arrival, pilot and tug assignments, berth allocation, crane deployment, yard truck dispatch, rail transfers, and gate movements — all against a tight window that directly affects the next vessel in the queue.

Traditional port management information systems batch-process event data from terminal operating systems (TOS), vessel tracking services (AIS), and equipment telemetry. Dashboard refreshes every 5 to 15 minutes mean that a crane breakdown, a berth delay, or a vessel rerouting is visible to coordinators only after several cycles. In a port where each hour of berth occupancy costs tens of thousands of dollars, this lag is operationally costly.

Real-time monitoring means every AIS position update, every crane cycle completion, every gate transaction, and every cargo transfer event flows immediately into a live view of port state — visible to berth planners, equipment controllers, and vessel agents the moment it happens.

How Streaming Port Monitoring Works

The architecture connects four data streams to RisingWave:

1. AIS vessel positions: continuous GPS positions from vessels in and approaching the port
2. Berth and equipment events: crane cycle events, equipment status changes, and berth allocation updates from the TOS
3. Gate transactions: truck gate entries and exits
4. Cargo transfer events: container moves, load/discharge confirmations

RisingWave maintains materialized views that represent current port state at every level: vessel positions, berth occupancy, crane productivity, yard density, and gate throughput. Operations teams query these views through standard PostgreSQL-compatible clients or BI tools.

Step-by-Step Tutorial

Step 1: Set Up the Data Source

Define Kafka sources for the primary port data streams:

-- AIS vessel position reports
CREATE SOURCE ais_positions (
    mmsi          VARCHAR,   -- vessel identifier
    vessel_name   VARCHAR,
    vessel_type   VARCHAR,
    latitude      DOUBLE PRECISION,
    longitude     DOUBLE PRECISION,
    speed_knots   DOUBLE PRECISION,
    heading_deg   DOUBLE PRECISION,
    nav_status    VARCHAR,   -- 'UNDERWAY','AT_ANCHOR','MOORED','RESTRICTED'
    event_time    TIMESTAMPTZ,
    destination   VARCHAR,
    eta           TIMESTAMPTZ
)
WITH (
    connector    = 'kafka',
    topic        = 'ais-positions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Berth and equipment events from TOS
CREATE SOURCE terminal_events (
    event_id      VARCHAR,
    event_type    VARCHAR,  -- 'BERTH_ALLOC','BERTH_RELEASE','CRANE_CYCLE','EQUIP_STATUS'
    vessel_id     VARCHAR,
    berth_id      VARCHAR,
    crane_id      VARCHAR,
    container_id  VARCHAR,
    event_time    TIMESTAMPTZ,
    value         DOUBLE PRECISION,  -- crane productivity, cycle time, etc.
    status        VARCHAR
)
WITH (
    connector    = 'kafka',
    topic        = 'terminal-events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Gate transactions
CREATE SOURCE gate_transactions (
    transaction_id  VARCHAR,
    gate_id         VARCHAR,
    truck_plate     VARCHAR,
    container_id    VARCHAR,
    direction       VARCHAR,  -- 'IN','OUT'
    event_time      TIMESTAMPTZ,
    queue_wait_sec  INTEGER,
    appointment_id  VARCHAR
)
WITH (
    connector    = 'kafka',
    topic        = 'gate-transactions',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
)
FORMAT PLAIN ENCODE JSON;

-- Reference: berth configuration
CREATE TABLE berths (
    berth_id      VARCHAR PRIMARY KEY,
    berth_name    VARCHAR,
    max_loa_m     DOUBLE PRECISION,  -- maximum length overall
    max_draft_m   DOUBLE PRECISION,
    quay_cranes   INTEGER,
    terminal_id   VARCHAR
);

Step 2: Build the Core Materialized View

Create the live vessel position and port state views:

-- Current vessel positions: latest AIS report per vessel
CREATE MATERIALIZED VIEW live_vessel_positions AS
SELECT DISTINCT ON (mmsi)
    mmsi,
    vessel_name,
    vessel_type,
    latitude,
    longitude,
    speed_knots,
    heading_deg,
    nav_status,
    event_time    AS last_ais_report,
    destination,
    eta           AS vessel_eta,
    NOW() - event_time AS ais_age
FROM ais_positions
ORDER BY mmsi, event_time DESC;

-- Vessels currently approaching the port (within ~20 nautical miles)
-- Port center: example coordinates
CREATE MATERIALIZED VIEW approaching_vessels AS
SELECT
    mmsi,
    vessel_name,
    vessel_type,
    latitude,
    longitude,
    speed_knots,
    nav_status,
    vessel_eta,
    -- Approximate distance in nautical miles (1 NM ≈ 1.852 km)
    (111.0 * SQRT(
        POWER(latitude  - 1.2647, 2) +   -- example: Singapore port lat
        POWER(longitude - 103.820, 2) * POWER(COS(RADIANS(latitude)), 2)
    ) / 1.852)   AS distance_nm_to_port,
    last_ais_report
FROM live_vessel_positions
WHERE nav_status IN ('UNDERWAY', 'CONSTRAINED')
  AND (111.0 * SQRT(
        POWER(latitude  - 1.2647, 2) +
        POWER(longitude - 103.820, 2) * POWER(COS(RADIANS(latitude)), 2)
    ) / 1.852) < 20;

-- Current berth occupancy
CREATE MATERIALIZED VIEW berth_occupancy AS
SELECT
    b.berth_id,
    b.berth_name,
    b.terminal_id,
    b.quay_cranes,
    alloc.vessel_id    AS occupied_by,
    alloc.event_time   AS occupied_since,
    NOW() - alloc.event_time AS occupancy_duration
FROM berths b
LEFT JOIN (
    SELECT DISTINCT ON (berth_id)
        berth_id, vessel_id, event_time
    FROM terminal_events
    WHERE event_type = 'BERTH_ALLOC'
    ORDER BY berth_id, event_time DESC
) alloc ON b.berth_id = alloc.berth_id
WHERE NOT EXISTS (
    SELECT 1 FROM terminal_events r
    WHERE r.event_type = 'BERTH_RELEASE'
      AND r.berth_id = b.berth_id
      AND r.event_time > alloc.event_time
);

Step 3: Add Productivity and Throughput Aggregations

Build the operational KPI views that port managers need throughout the day:

-- Crane productivity: moves per hour in rolling 1-hour windows
CREATE MATERIALIZED VIEW crane_productivity AS
SELECT
    window_start,
    window_end,
    crane_id,
    COUNT(*)              AS crane_cycles,
    COUNT(*) / 1.0        AS moves_per_hour,  -- window is 1 hour
    AVG(value)            AS avg_cycle_time_sec,
    MIN(value)            AS min_cycle_time_sec
FROM TUMBLE(terminal_events, event_time, INTERVAL '1 hour')
WHERE event_type = 'CRANE_CYCLE'
GROUP BY window_start, window_end, crane_id;

-- Gate throughput: trucks per hour
CREATE MATERIALIZED VIEW gate_throughput AS
SELECT
    window_start,
    window_end,
    gate_id,
    COUNT(*) FILTER (WHERE direction = 'IN')    AS trucks_in,
    COUNT(*) FILTER (WHERE direction = 'OUT')   AS trucks_out,
    AVG(queue_wait_sec) FILTER (WHERE direction = 'IN') AS avg_wait_sec_in,
    MAX(queue_wait_sec) FILTER (WHERE direction = 'IN') AS max_wait_sec_in
FROM TUMBLE(gate_transactions, event_time, INTERVAL '1 hour')
GROUP BY window_start, window_end, gate_id;

-- Fleet-wide port summary: snapshot for operations center screen
CREATE MATERIALIZED VIEW port_operations_summary AS
SELECT
    COUNT(DISTINCT CASE WHEN nav_status = 'MOORED' THEN mmsi END)  AS vessels_at_berth,
    COUNT(DISTINCT CASE WHEN nav_status = 'AT_ANCHOR' THEN mmsi END) AS vessels_at_anchor,
    (SELECT COUNT(*) FROM berth_occupancy WHERE occupied_by IS NOT NULL) AS berths_occupied,
    (SELECT COUNT(*) FROM berth_occupancy WHERE occupied_by IS NULL)     AS berths_free,
    (SELECT COUNT(*) FROM approaching_vessels)                            AS vessels_approaching
FROM live_vessel_positions;

-- Vessels with stale AIS data (potential safety alert)
CREATE MATERIALIZED VIEW stale_ais_alerts AS
SELECT
    mmsi,
    vessel_name,
    nav_status,
    last_ais_report,
    ais_age
FROM live_vessel_positions
WHERE ais_age > INTERVAL '15 minutes'
  AND nav_status NOT IN ('AT_ANCHOR', 'MOORED');

How This Compares to Traditional Approaches

FAQ

Can RisingWave process AIS data directly from a live AIS feed?

Yes. AIS data providers offer WebSocket or Kafka streams of decoded AIS messages. You can create a Kafka source in RisingWave pointed at a topic that receives AIS position reports, and all downstream materialized views update automatically as new position reports arrive.

How do I handle vessels that transit through the port area without calling?

The approaching_vessels view can be refined by filtering on destination to show only vessels whose declared destination matches the port's LOCODE. Transit vessels that are passing through but not calling can be filtered out, or shown in a separate view for traffic awareness.

How do I generate shift-end productivity reports from the streaming views?

Query crane_productivity and gate_throughput with a date range filter corresponding to the shift times. These views retain historical window data (subject to retention configuration). For long-term reporting, configure an Iceberg sink to archive window results to object storage, where they can be queried alongside other historical data.

Key Takeaways

• RisingWave streams AIS positions, berth events, crane cycles, and gate transactions into always-current materialized views, giving port operations centers live situational awareness across all terminal activities with sub-second latency.
• Views like berth_occupancy, approaching_vessels, and port_operations_summary provide the high-level port state that coordinators need, while crane_productivity and gate_throughput surface the operational metrics that drive shift performance.
• Because RisingWave is PostgreSQL-compatible, port information systems, BI tools, and customer portals can all query the same views using standard SQL — no custom APIs or middleware required.

Ready to try this yourself? Get started with RisingWave in minutes. Join our Slack community.