Phase 5E: Boundary System - Transition Summary

✅ Phase 5E.1: Boundary Import - COMPLETE (Jan 6, 2026)

Final Results

ARTCC:     951 imported, 0 failed (951 normalized)
High:      351 imported, 0 failed
Low:       401 imported, 0 failed
Superhigh: 250 imported, 0 failed
TRACON:   1098 imported, 0 failed (1091 normalized)
Total:    3051 imported, 0 failed (2064 normalized)

---

✅ Phase 5E.2: Boundary Detection - READY FOR DEPLOYMENT

Overview

Detects ARTCC, sectors (LOW/HIGH/SUPERHIGH), and TRACON for each active flight. Supports multiple overlapping sectors per type.

Schema Changes

New columns on adl_flight_core:

Column	Type	Description
current_artcc	VARCHAR(10)	Single ARTCC code
current_artcc_id	INT	ARTCC boundary ID
current_sector_low	VARCHAR(255)	Comma-separated low sector codes
current_sector_low_ids	NVARCHAR(MAX)	JSON array of low sector IDs
current_sector_high	VARCHAR(255)	Comma-separated high sector codes
current_sector_high_ids	NVARCHAR(MAX)	JSON array of high sector IDs
current_sector_superhigh	VARCHAR(255)	Comma-separated superhigh sector codes
current_sector_superhigh_ids	NVARCHAR(MAX)	JSON array of superhigh sector IDs
current_tracon	VARCHAR(50)	Single TRACON code
current_tracon_id	INT	TRACON boundary ID
boundary_updated_at	DATETIME2(0)	Last boundary check timestamp

Files to Deploy

File	Purpose
adl/migrations/boundaries/006_fix_boundary_log_schema.sql	Schema updates + multi-sector columns
adl/procedures/sp_ProcessBoundaryDetectionBatch.sql	Batch detection with overlap support
adl/procedures/sp_Adl_RefreshFromVatsim_Normalized.sql (V8.3)	Integration
api/adl/boundaries.php	Updated API with multi-sector response

Deployment Order

1. Run: boundaries/006_fix_boundary_log_schema.sql
2. Run: sp_ProcessBoundaryDetectionBatch.sql
3. Run: sp_Adl_RefreshFromVatsim_Normalized.sql (V8.3)
4. Deploy: api/adl/boundaries.php

Detection Logic

Type	Behavior	Storage
ARTCC	Single value, prefer non-oceanic, smallest area	Single code + ID
SECTOR_LOW	All overlapping sectors detected	Comma-separated codes + JSON IDs
SECTOR_HIGH	All overlapping sectors detected	Comma-separated codes + JSON IDs
SECTOR_SUPERHIGH	All overlapping sectors detected	Comma-separated codes + JSON IDs
TRACON	Single value (below FL180 only), smallest area	Single code + ID

Example Output

SELECT callsign, current_artcc, current_sector_low, current_sector_high, 
       current_sector_superhigh, current_tracon
FROM adl_flight_core WHERE callsign = 'AAL123';

callsign	current_artcc	current_sector_low	current_sector_high	current_sector_superhigh	current_tracon
AAL123	ZNY	ZNY31,ZNY32	ZNY42,ZNY43	ZNY90	N90

API Response (action=flight)

{
  "success": true,
  "flight": {
    "flight_uid": 12345,
    "callsign": "AAL123",
    "position": { "lat": 40.6413, "lon": -73.7781, "altitude": 35000 },
    "artcc": { "code": "ZNY", "name": "New York Center" },
    "sectors_low": ["ZNY31", "ZNY32"],
    "sectors_high": ["ZNY42", "ZNY43"],
    "sectors_superhigh": ["ZNY90"],
    "tracon": { "code": "N90", "name": "New York TRACON" },
    "boundary_updated_at": "2026-01-06 15:30:00"
  }
}

API Response (action=contains)

{
  "success": true,
  "position": { "lat": 40.6413, "lon": -73.7781, "alt": 35000 },
  "artcc": { "boundary_id": 123, "boundary_code": "ZNY", "boundary_name": "New York Center" },
  "sectors_low": [
    { "boundary_id": 201, "boundary_code": "ZNY31", "floor_altitude": 0, "ceiling_altitude": 230 },
    { "boundary_id": 202, "boundary_code": "ZNY32", "floor_altitude": 0, "ceiling_altitude": 230 }
  ],
  "sectors_high": [
    { "boundary_id": 301, "boundary_code": "ZNY42", "floor_altitude": 240, "ceiling_altitude": 350 }
  ],
  "sectors_superhigh": [],
  "tracon": null
}

Verification Queries

-- Flights with multiple overlapping sectors
SELECT callsign, current_artcc, current_sector_low, current_sector_high
FROM adl_flight_core 
WHERE is_active = 1 
  AND current_sector_low LIKE '%,%'
ORDER BY callsign;

-- Sector transition log
SELECT TOP 20 
    fc.callsign, 
    log.boundary_type, 
    log.boundary_code, 
    log.entry_time, 
    log.exit_time,
    log.duration_seconds
FROM adl_flight_boundary_log log
JOIN adl_flight_core fc ON fc.flight_uid = log.flight_uid
ORDER BY log.entry_time DESC;

-- Active flights per sector
SELECT boundary_code, boundary_type, COUNT(*) as flights
FROM adl_flight_boundary_log
WHERE exit_time IS NULL
GROUP BY boundary_code, boundary_type
ORDER BY flights DESC;

---

Phase 5E.3: MapLibre Visualization - PLANNED

Goals

• Add boundary layer with fill and outline styling
• Click handlers to show boundary info
• Toggle boundary types on/off
• Style by type: ARTCC=blue, sector=orange, TRACON=green
• Highlight sectors containing selected flight

API Ready

GET /api/adl/boundaries.php?action=geojson&type=SECTOR_HIGH
GET /api/adl/boundaries.php?action=geojson&artcc=ZNY
GET /api/adl/boundaries.php?action=contains&lat=40.64&lon=-73.78