Skip to content

simbrief_parsing_summary.md

Latest commit

 

History

History
247 lines (183 loc) · 6.95 KB

simbrief_parsing_summary.md

File metadata and controls

247 lines (183 loc) · 6.95 KB
Raw

SimBrief Parsing Implementation Summary

Date: January 7, 2026
Feature: ETA Enhancement Item #7 - SimBrief Parsing
Status: Ready for Deployment

Overview

This implementation adds SimBrief/ICAO flight plan data extraction to PERTI. It parses the VATSIM API's remarks and route fields to extract valuable operational data that enhances ETA calculations and provides richer flight information.

Data Extracted

From Remarks Field (ICAO Item 18)

Indicator Description Example
DOF/ Date of Flight DOF/260107
REG/ Aircraft Registration REG/N12345
OPR/ Operator OPR/UAL
PBN/ Performance-Based Navigation PBN/A1B1C1D1S1S2
RMK/ Remarks RMK/TCAS SIMBRIEF
SEL/ SELCAL Code SEL/ABCD
EET/ Estimated Elapsed Time EET/KZNY0045 KZBW0120

From Route String

Pattern Description Example
WPT/N0460F360 Step climb at waypoint TAS 460kts, FL360
WPT/M082F390 Step climb (Mach) Mach 0.82, FL390
WPT/F350 Altitude only FL350 at waypoint

Derived Data

Field Source Description
is_simbrief Remarks patterns Flight generated by SimBrief
dep_runway DP name parsing Runway from SID name
arr_runway STAR/APP parsing Runway from STAR/approach
initial_alt_ft First step climb Initial cruise altitude
final_alt_ft Last step climb Final cruise altitude
stepclimb_count Route parsing Number of altitude changes

SimBrief Detection Patterns

A flight is flagged as SimBrief-generated if remarks contain:

  1. Explicit: SIMBRIEF or SB/
  2. TCAS marker: RMK/TCAS (SimBrief always includes this)
  3. Full Item 18: PBN/ + DOF/ + RMK/ together
  4. PBN pattern: PBN/A1B1... + NAV/

Database Schema

Modified Tables

adl_flight_plan:

is_simbrief         BIT         -- SimBrief-generated flag
simbrief_id         NVARCHAR(32) -- SimBrief OFP ID (if extractable)
cost_index          INT         -- Cost Index (if present)
dep_runway          NVARCHAR(4) -- Departure runway
dep_runway_source   NVARCHAR(16) -- DP_PARSE, TBFM, USER
arr_runway          NVARCHAR(4) -- Arrival runway  
arr_runway_source   NVARCHAR(16) -- STAR_PARSE, TBFM, USER
initial_alt_ft      INT         -- Initial cruise altitude
final_alt_ft        INT         -- Final cruise altitude
stepclimb_count     INT         -- Number of step climbs

adl_flight_stepclimbs: (existing table, now populated)

flight_uid          BIGINT      -- FK to flight
step_sequence       INT         -- Order of step
waypoint_fix        NVARCHAR(64) -- Waypoint for step
altitude_ft         INT         -- Target altitude
speed_kts           INT         -- TAS if specified
speed_mach          DECIMAL(4,3) -- Mach if specified
speed_type          NVARCHAR(8) -- TAS, MACH, IAS
source              NVARCHAR(16) -- ROUTE, REMARKS
raw_text            NVARCHAR(128) -- Original parsed text

Stored Procedures

sp_ParseSimBriefData

Parses SimBrief/ICAO data for a single flight.

EXEC sp_ParseSimBriefData @flight_uid = 12345, @debug = 1;

Process:

  1. Detect SimBrief via remarks patterns
  2. Parse ICAO Item 18 indicators
  3. Extract step climbs from route string
  4. Extract runways from SID/STAR names
  5. Update adl_flight_plan
  6. Insert step climb records
  7. Update waypoints with step climb flags

sp_ParseSimBriefDataBatch

Batch process multiple flights.

-- Process unparsed flights
EXEC sp_ParseSimBriefDataBatch @batch_size = 100;

-- Reprocess all active flights
EXEC sp_ParseSimBriefDataBatch @batch_size = 500, @only_unparsed = 0;

Helper Functions

Function Purpose
fn_ParseICAORemarks Extract ICAO Item 18 indicators from remarks
fn_ParseRouteStepClimbs Extract step climbs from route string
fn_IsSimBriefFlight Detect SimBrief-generated flight plan
fn_ExtractRunwayFromProcedure Extract runway from SID/STAR name

Integration Points

With sp_ParseRoute

The route parser now has waypoint flags:

  • is_step_climb_point - Waypoint has altitude change
  • planned_alt_ft - Target altitude at waypoint
  • planned_speed_kts / planned_speed_mach - Target speed

With sp_CalculateETABatch

Step climb data can improve ETA calculation:

  • Initial cruise altitude for climb time estimation
  • Step climb waypoints for altitude profile
  • Final altitude for optimal cruise speed lookup

With Demand Visualization

SimBrief data provides:

  • More accurate runway assignments for demand counting
  • Better departure/arrival time correlation with DOF

Files Created

File Description
adl/procedures/sp_ParseSimBriefData.sql Full procedure source
adl/migrations/navdata/002_simbrief_parsing.sql Deployment migration

Deployment

  1. Run migration:

    -- In SSMS connected to VATSIM_ADL
:r adl/migrations/navdata/002_simbrief_parsing.sql

  2. Test with debug mode:

    -- Find a flight with remarks
SELECT TOP 10 flight_uid, fp_remarks 
FROM adl_flight_plan 
WHERE fp_remarks LIKE '%PBN%';

-- Parse with debug
EXEC sp_ParseSimBriefData @flight_uid = <id>, @debug = 1;

  3. Run batch processing:

    EXEC sp_ParseSimBriefDataBatch @batch_size = 500;

Sample Output

Step Climbs Parsed:

sequence_num | waypoint_fix | altitude_ft | speed_kts | speed_mach
-------------|--------------|-------------|-----------|----------
1            | TUDEP        | 34000       | 460       | NULL
2            | NETKI        | 35000       | NULL      | 0.82
3            | BAKUR        | 37000       | NULL      | 0.84

SimBrief Detection:

flight_uid | is_simbrief | dep_runway | arr_runway | stepclimb_count
-----------|-------------|------------|------------|----------------
12345      | 1           | 06R        | 28L        | 3

Performance Targets

Metric Target Notes
Single flight parse <50ms Per flight
Batch of 100 <2s With step climbs
Memory usage Minimal Table variables only

Future Enhancements

  1. Cost Index Extraction: Parse CI from remarks if present
  2. SimBrief API Integration: Fetch full OFP data via SimBrief ID
  3. TOC/TOD Detection: Mark waypoints as top-of-climb/descent
  4. Wind Data Correlation: Use step climb points for wind calculation
  5. ETA Refinement: Use altitude profile for more accurate ETA

ETA Enhancement Status

Item Description Status
✅ #6 ETA Consolidation Complete
✅ #3 Aircraft Performance (OpenAP) Complete
✅ #4 OOOI Zone Detection Complete
✅ #7 SimBrief Parsing Complete
⏳ #1 Route Distance Ready
⏳ #5 Sector ETA Ready
⏳ #2 Wind Data Ready