DEV Community: José Catalá

628,000 Flights Later: Building a Flight Analytics Engine on Accumulated Scrape Data

José Catalá — Mon, 06 Apr 2026 16:40:24 +0000

Every time MyAirports scrapes a flight board, the flights are stored in PostgreSQL. What started as a caching mechanism turned into something more interesting: a dataset of real flight behaviour that can answer questions no static API can answer.

After accumulating 628,000+ flight records across 1,000+ airports, the data supports four categories of analytics:

Airline reliability rankings per airport (on-time rate, average delay, cancellation rate)
Delay pattern breakdowns by airline, destination, day of week, and time of day
Airport busyness heatmaps (7×24 grid: average flight volume by hour and day)
Route reliability stats with per-airline breakdown (LHR→JFK: which airline is most reliable?)

Here's how the analytics layer works.

The raw data

Each Flight record in PostgreSQL has:

flightNumber, airline (IATA), type (arrival/departure)
airport (IATA), origin, destination
scheduled, estimated, actual (timestamps, local timezone)
status (scheduled/boarding/departed/arrived/delayed/cancelled/unknown)
confidence, strategy, scrapedAt

The actual timestamp and status field are what make analytics possible. When a flight's status is arrived and actual is set, we know when it actually landed. The delay is simply actual - scheduled in minutes.

Two compute strategies: incremental and nightly

Running full analytics queries over 628,000 rows on every API request would be too slow. The system uses a two-track approach.

Incremental counters — updated after each scrape:

When a new batch of flights arrives, the system immediately updates lightweight counters: per-airline flight counts at that airport, per-terminal/gate flight counts. These are cheap to maintain and power fast lookups.

Nightly full recompute — runs at 2 AM UTC:

Once per day, the system runs a full pass over all historical data to recompute the richer analytics tables. This is where the delay stats, airline scorecards, and busyness grids are built. It's more expensive, but the data doesn't need to be real-time — yesterday's delay patterns are as useful as today's.

Airline reliability rankings

The query is straightforward in concept, but requires minimum thresholds to be useful:

SELECT
  airline_iata,
  COUNT(*) as total_flights,
  COUNT(*) FILTER (WHERE delay_minutes <= 15) * 100.0 / COUNT(*) as on_time_pct,
  AVG(delay_minutes) FILTER (WHERE delay_minutes > 0) as avg_delay_minutes,
  COUNT(*) FILTER (WHERE status = 'cancelled') * 100.0 / COUNT(*) as cancelled_pct
FROM flights
WHERE airport_iata = 'LHR'
  AND actual IS NOT NULL
  AND status IN ('arrived', 'departed')
GROUP BY airline_iata
HAVING COUNT(*) >= 10
ORDER BY on_time_pct DESC;

The HAVING COUNT(*) >= 10 is the important part. Without a minimum flight threshold, airlines that had one flight and it happened to be on time appear at the top with 100% on-time performance. Ten flights is a low bar, but it filters out pure noise.

The result for LHR looks like this:

{
  "rank": 1,
  "airlineIata": "LX",
  "airlineName": "Swiss International Air Lines",
  "onTimePct": 88.2,
  "avgDelayMinutes": 7.4,
  "cancelledPct": 0.3,
  "totalFlights": 2340
}

Delay patterns: finding when and why delays happen

The delay breakdown goes a level deeper than overall stats. It slices delay data by:

Day of week — Monday departures are often worse than Wednesday ones
Time bucket — morning (06-12), afternoon (12-18), evening (18-24), night (00-06)
Destination — some routes are systematically late
Airline — some carriers at a specific airport are consistently worse than their global average

The time bucket breakdown is particularly useful. At large hub airports, delays compound through the day: a slot disruption in the morning propagates through afternoon flights. The heatmap data makes this visible.

Airport busyness heatmap

The busyness grid answers "when is this airport busy?" — useful for scheduling, understanding delay risk by time, and just general situational awareness:

SELECT
  EXTRACT(DOW FROM scheduled) as day_of_week,  -- 0=Sun, 6=Sat
  EXTRACT(HOUR FROM scheduled) as hour,
  AVG(flight_count) as avg_flights
FROM (
  SELECT
    DATE_TRUNC('hour', scheduled) as bucket,
    EXTRACT(DOW FROM scheduled) as day_of_week,
    EXTRACT(HOUR FROM scheduled) as hour,
    COUNT(*) as flight_count
  FROM flights
  WHERE airport_iata = $1
  GROUP BY 1, 2, 3
) hourly
GROUP BY day_of_week, hour
ORDER BY day_of_week, hour;

The result is a 7×24 matrix of average hourly flight volumes, suitable for heatmap rendering in any charting library.

Gate and terminal predictions

The most interesting use of accumulated data is predicting which gate or terminal a flight will use before the airport announces it. Airlines tend to use the same gates consistently at their hub airports.

After each scrape, the system updates per-airline terminal and gate counters:

// After normalizing a batch of flights
for (const flight of flights) {
  if (flight.terminal) {
    incrementCounter(`${airport}:${airline}:terminal:${flight.terminal}`);
  }
  if (flight.gate) {
    incrementCounter(`${airport}:${airline}:gate:${flight.gate}`);
  }
}

When serving a flight that doesn't have a gate assigned yet, the API enriches it with the most common gate for that airline at that airport:

if (!flight.gate && insights?.terminal?.predicted) {
  flight.insights = {
    terminal: { predicted: insights.terminal.value, confidence: insights.terminal.confidence }
  };
}

The confidence value is the fraction of historical flights that used the predicted terminal. For BA at LHR, it's nearly always Terminal 5 — confidence approaches 1.0. For a code-share flight operated by a third party, confidence might be 0.3.

The route reliability endpoint

One API endpoint that required no special query design: route-level stats. Given an origin and destination, return reliability data for each airline that operates the route:

GET /api/routes/LHR/JFK/insights

{
  "origin": "LHR",
  "dest": "JFK",
  "route": {
    "totalFlights": 1240,
    "onTimePct": 78.3,
    "avgDelayMinutes": 16.5,
    "cancelledPct": 1.8,
    "topAirline": "BA",
    "topAirlineOnTimePct": 84.2
  },
  "airlines": [
    { "airlineIata": "BA", "onTimePct": 84.2, "totalFlights": 890 },
    { "airlineIata": "VS", "onTimePct": 71.4, "totalFlights": 350 }
  ]
}

This is data that's genuinely hard to find anywhere else. The big flight data providers have it, but it's locked behind enterprise pricing. The MyAirports version is built from first-party airport data, accumulated one scrape at a time.

What the data still can't do

A few honest caveats about 628,000 flights:

It's not uniform. Airports with many active users have more scrape history than airports discovered recently. LHR has thousands of records; a newly added regional airport might have dozens.

It's biased toward requested airports. The system scrapes when someone requests data. Popular airports are over-represented relative to their actual traffic volumes.

Delay calculations require actual timestamps. Many airport APIs only return scheduled times, not actual arrival/departure times. Those flights contribute to volume counts but not delay calculations.

The data is directionally useful — the patterns it reveals are real — but it's not a controlled dataset. It's accumulated opportunistically.

All three endpoints are available in the MyAirports API:

GET /api/airports/:iata/insights/airlines
GET /api/airports/:iata/insights/delays
GET /api/airports/:iata/insights/busyness
GET /api/routes/:origin/:dest/insights

Free tier at myairports.online/developers. No credit card required.

Normalizing Flight Statuses Across 7 Languages: What I Learned Building a Global Airport API

José Catalá — Mon, 06 Apr 2026 16:39:30 +0000

When you scrape flight data from airport websites across 85+ countries, you quickly discover that nobody agrees on anything. Not the data format, not the field names, not the status strings.

"Departed" is SAL, FLY, CER, AIR, DEP, DEPARTED, Departed, Отправлен, Väljunud, DESPEGADO, gestartet, or partito, depending on which airport website you're looking at.

Building MyAirports meant writing a status normalizer that handles all of these — and maps them to a single, predictable enum. Here's how it works and what surprised me along the way.

The target schema

Every flight the API returns has a status field from this set:

scheduled | boarding | departed | arrived | delayed | cancelled | unknown

Seven values. Simple, predictable, usable in any frontend without additional mapping. The hard part is getting there from the real world.

Layer 1: Exact string matches

The first layer is the most straightforward — a lookup table of known status strings to their canonical values:

const EXACT = {
  // English
  'scheduled': 'scheduled',
  'on time': 'scheduled',
  'departed': 'departed',
  'in flight': 'departed',
  'arrived': 'arrived',
  'landed': 'arrived',
  'delayed': 'delayed',
  'cancelled': 'cancelled',
  'boarding': 'boarding',
  'gate open': 'boarding',

  // Spanish (AENA codes)
  'SAL': 'departed',  // salida
  'AIR': 'departed',  // en aire
  'FLY': 'departed',
  'CER': 'departed',  // cerrado
  'LND': 'arrived',   // aterrizó
  'FNL': 'arrived',   // final
  'IBK': 'arrived',   // in-block
  'BOR': 'boarding',  // embarcando
  'EMB': 'boarding',
  'ULL': 'boarding',
  'OPE': 'boarding',
  'EST': 'scheduled', // estimado
  'PRG': 'scheduled', // programado
  'RET': 'delayed',   // retraso
  'CNL': 'cancelled',

  // Russian
  'Отправлен': 'departed',
  'Прилетел': 'arrived',
  'Посадка': 'boarding',
  'Задержан': 'delayed',
  'Отменён': 'cancelled',

  // Estonian
  'Väljunud': 'departed',
  'Saabunud': 'arrived',
  'Pardal': 'boarding',

  // Finnish (Finavia)
  'Lähtenyt': 'departed',
  'Saapunut': 'arrived',
  'Nousussa': 'boarding',
};

This handles probably 60-70% of real-world cases. The remainder need more work.

Layer 2: Substring patterns

Some status strings embed dynamic information — actual times, gate numbers, gate closures. You can't match them exactly, but you can match them by pattern:

const SUBSTRING_PATTERNS = [
  // "Delayed 00:45"
  [/delay/i, 'delayed'],
  // "Cancel" in any language starting with 'cancel'
  [/cancel/i, 'cancelled'],
  // AENA-style: "CERRADO" (closed), "PROGRAMADO" (scheduled)
  [/^cerr/i, 'departed'],
  [/^progr/i, 'scheduled'],
  // "Check-in open" → scheduled (not boarding yet)
  [/check.?in/i, 'scheduled'],
];

Layer 3: Regex patterns for dynamic content

A handful of airports embed actual timestamps or qualifiers directly in the status string. Phoenix Sky Harbor is a good example — their status field for delayed departures looks like "Now 14:22" (the actual departure time). That's not a status, that's a timestamp, and it means the flight has departed.

const REGEX_PATTERNS = [
  // Phoenix: "Now 12:21 PM" → departed
  [/^now\s+\d/i, 'departed'],
  // Finavia: "Expected 14:35" → still scheduled
  [/^expected\s+\d/i, 'scheduled'],
  // Generic: "14:35" alone means actual time → departed/arrived
  [/^\d{1,2}:\d{2}$/, 'arrived'],
];

Layer 4: Russian stem matching

Russian is morphologically rich — words inflect heavily based on grammatical case, number, and gender. "Посадка" (boarding) appears as "посадке", "посадкой", "посадку" in different sentence positions. Exact matching misses these.

The solution isn't a full NLP library (overkill for six words) — it's stem prefixes. The first four or five characters of a word are usually enough to identify it:

const RUSSIAN_STEMS = {
  'посад': 'boarding',   // posad- (посадка, посадке...)
  'отпра': 'departed',   // otpra- (отправлен, отправился...)
  'прил':  'arrived',    // pril- (прилетел, прилетела...)
  'задер': 'delayed',    // zader- (задержан, задержка...)
  'отмен': 'cancelled',  // otmen- (отменён, отменённый...)
};

This catches all the common inflected forms without false positives.

The object guard

One thing I didn't expect: some airport APIs return status as an object rather than a string:

{ "status": { "code": "DEP", "description": "Departed", "color": "#ff0000" } }

The normalizer checks for this and extracts code or description before attempting any matching. Without this guard, typeof status === 'object' crashes the string matching silently.

AENA's 30+ status codes

AENA (the Spanish airport operator, covering all 22 Spanish airports) has the most elaborate status system I encountered. They use two to three character codes from their SIGER ground handling system:

FNL, LND, IBK → arrived
BOR, EMB, ULL, OPE, APE → boarding
SAL, AIR, FLY, CER, APA → departed
EST, PRG, INI, SCH, HOR → scheduled
RET, DEM, REM → delayed
CNL, DES, CAN → cancelled

Each code has a specific operational meaning (IBK = in-block, ULL = final boarding call, DEM = ground delay) that the normalizer collapses to the six canonical values. The operational detail is lost, but that's the right tradeoff for a public API.

What "unknown" means in practice

The unknown bucket catches everything the normalizer can't confidently classify. In production, it appears for:

Empty strings or null values (some airports just don't return status for on-time flights)
Airport-specific codes that haven't been mapped yet
Status strings in languages I haven't configured (Arabic, Japanese, Chinese)

Returning unknown is far better than returning a wrong value. Frontends can show "—" for unknown statuses and users understand it means data isn't available.

Running the normalizer

The normalizer runs at the end of every scrape, after the field mapper has identified which JSON field contains the status. It's a pure function:

normalizeStatus(raw) // → 'scheduled' | 'boarding' | 'departed' | 'arrived' | 'delayed' | 'cancelled' | 'unknown'

Input is always a string (after the object guard). Output is always one of the seven values. No exceptions are thrown — unrecognized inputs return 'unknown'.

The broader lesson

I went into this project expecting the hard part to be scraping. The really hard part turned out to be normalization — taking data from wildly inconsistent sources and making it uniformly useful.

Status normalization is one example. The same problem appears with timestamps (airports use their local timezone, not UTC, and the formats vary wildly), location fields (some put "MILAN, MALPENSA" in the IATA field), and airline codes (some use IATA codes, some use ICAO, some use full names).

The principle that worked: design a strict output schema first, then build the normalization layer to enforce it, and treat every edge case as an addition to the normalizer rather than a special case in the consumer.

The API is live and free to try at myairports.online/developers. Arrivals and departures for 1,000+ airports, statuses normalized to the seven values above.

Next in this series: how 628,000 accumulated flight records are turned into airline delay rankings, gate predictions, and airport busyness heatmaps.

How I Built a Web Scraper That Gets Faster and Cheaper With Every Request

José Catalá — Mon, 06 Apr 2026 16:38:33 +0000

Most web scrapers are dumb: they do the same work every time. Open a browser, load a page, parse the DOM, extract data, close the browser. Every single request.

Building MyAirports — a real-time flight data API covering 1,000+ airports — forced me to think about scraping differently. At scale, running a headless browser for every request is expensive and slow. The system I ended up building instead gets faster and cheaper the more it runs, because it learns.

Here's how it works.

The core insight: most airport SPAs load data from internal JSON APIs

Go to any modern airport website. Open DevTools. Click on the Network tab. Filter for XHR/Fetch requests. What you'll see almost immediately is a call to something like:

GET https://api.heathrow.com/pihub/flights/arrivals
GET https://aena.es/sites/Satellite?pagename=AENA_ConsultarVuelos&airport=MAD
GET https://cph.dk/api/FlightInformation/GetFlightInfoTable?direction=A

The webpage is a React or Angular SPA, and the actual flight data comes from an internal API. That API is often unauthenticated, or authenticated only by session cookies the browser already has. If you can capture that API call, you can make it directly — no browser needed for subsequent requests.

This is the foundation of the whole system.

The 5-layer pipeline

Every flight data request flows through five layers:

Layer 1: Operator registry
For ~500 airports I've already reverse-engineered the API, the system goes directly there. No discovery needed. A curated registry maps IATA codes to endpoints, required headers, and a transform function for normalizing the response. This handles roughly half of all requests, takes 1-2 seconds, and costs nothing beyond the API call.

Layer 2: API cache (discovered endpoints)
The system maintains a database of APIs it has discovered during previous browser sessions. Before firing up a browser, it checks: have we already seen a good API for this airport? If yes, call it directly. This is the "memory" layer — it's how the system gets cheaper over time.

Layer 3: Browser discovery
Only if both previous layers miss does the system launch a real browser (Playwright, stealth mode). But even here it's not scraping the DOM — it's intercepting network traffic. The browser loads the airport's flight page, and the interceptor scores every JSON response it sees:

function scoreResponse(url, body) {
  let score = 0;
  if (url.includes('flight') || url.includes('arrival') || url.includes('departure')) score += 3;
  if (Array.isArray(body) && body.length > 5) score += 2;
  if (body[0]?.flightNumber || body[0]?.flight_no) score += 3;
  if (body[0]?.scheduled || body[0]?.scheduledTime) score += 2;
  return score;
}

Any response with a score above 5 is a candidate. The best candidate is saved to the ApiCache table — the discovered endpoint, the required headers, the response shape. Future requests for that airport hit Layer 2 instead.

Layer 4: Normalizer
Regardless of which layer found the data, every response goes through a normalizer that converts it to a unified flight schema. Airport APIs have wildly different shapes — nested objects, flat arrays, GraphQL responses, tables with inconsistent column names. The normalizer handles all of it.

Layer 5: Persistence and stale-while-revalidate cache
Results are stored in PostgreSQL and kept in an in-memory cache with a stale-while-revalidate strategy. A stale response is returned immediately while a fresh scrape runs in the background.

The result: the system pays for its own speed

When the system first encounters a new airport, it's slow: a full browser session takes 10-15 seconds. But the next time that airport is requested, the discovered API is available in the cache — and the call takes 1-2 seconds.

Over time, the API cache fills up. More and more airports become fast. The "discovery" cost is paid exactly once per airport. After that, it's a cheap HTTP call.

There's also an eviction mechanism: if a cached API endpoint starts returning 0 flights three times in a row, it gets flagged as stale and the system re-discovers it. Airport websites do change their APIs occasionally.

The auto-discovery tool

To seed the API cache without waiting for organic traffic, I built a batch auto-discovery CLI that processes airports from the OpenFlights CSV database:

npm run auto-discover -- --country IT
npm run auto-discover -- --min-size large

It runs the full browser discovery pipeline for each airport, then writes the discovered endpoints to the config files automatically. Processing 1,300+ airports this way found 276 new ones — airports whose APIs I hadn't previously known existed.

AI pattern learning as a last resort

For airports where network interception fails (because the page loads data from a server-side render rather than a client-side API call), there's a DeepSeek-powered fallback: send the page's HTML to the AI and ask it to identify CSS selectors for the flight table.

GYD (Baku) — a.flt_row card layout with .fl_col.* columns
KTW (Katowice) — div.timetable__row.flight-board__row with .flight-board__col--*
TLL (Tallinn) — li.flights-list__item card layout

The AI doesn't always get it right on the first try, but for airports with non-standard layouts it's significantly faster than manually inspecting the DOM.

What I learned

The most important architectural decision was treating discovery as a one-time investment rather than an ongoing cost. Every browser session is expensive — it's a browser, running JavaScript, waiting for a page to load. If you pay that cost once and cache the result, you amortize it across hundreds of future requests.

The second insight was scoring rather than parsing: instead of trying to understand an airport's specific API format upfront, intercept everything and look for responses that smell like flight data. The scoring heuristic is simple but effective, and it works across wildly different API shapes.

The third was building eviction in from the start. APIs change. Caching without a sensible eviction strategy means you end up with a database full of dead endpoints.

The result is a system that today handles most requests in 1-2 seconds, covers 1,000+ airports, and gets incrementally better with every new airport it encounters.

The API is free to try at myairports.online/developers.

If you found this interesting, I write about building MyAirports — a real-time flight data API. The next post covers how the system normalizes flight status strings from 7 different languages.

MyAirports: Real-Time Flight Data for 1,000+ Airports — Now with a Free API

José Catalá — Sun, 05 Apr 2026 10:06:42 +0000

Whether you're building a travel app, a flight notification bot, or just want to check if your flight is on time — finding reliable, real-time flight data has always been painful. Most data providers charge hundreds of dollars per month, require lengthy contracts, or give you stale data wrapped in clunky XML.

MyAirports.online is changing that.

What Is MyAirports?

MyAirports (https://myairports.online) is a live flight tracking platform covering 1,000+ airports across 85+ countries. It delivers up-to-the-minute arrivals and departures for airports from London Heathrow to Singapore Changi, Madrid Barajas to New York JFK — all through a simple REST API or a clean web interface.

The API — Simple, Fast, and Free to Start

GET https://myairports.online/api/airports/LHR/arrivals

You get clean JSON back — flight number, airline, origin/destination, times, gate, terminal, status, and code-share numbers.

Free accounts: 100 requests/day. Pro plan ($29/month): 1,000 requests/day.

Beyond Flights: Ground Transport, Parking & Car Rental

Ground connections — metro, buses, trains, shuttles, taxis with pricing and times.
Parking options — short-stay, long-stay, valet, economy, premium with current rates.
Car rental — major brands and local companies at the airport.

Endpoints:

https://myairports.online/connections/MAD — Madrid ground transport
https://myairports.online/parking/MAD — Madrid parking
https://myairports.online/car-rental/MAD — Madrid car rental

Global Coverage: 85+ Countries

Europe: LHR, MAD, BCN, AMS, VIE, CPH, DUB, FCO, MXP, ZRH, PRG, LIS, and more.
Americas: JFK, EWR, LGA, DFW, DEN, MCO, PHX, LAS.
Middle East & Asia: DXB, SIN.
Oceania & Africa: MEL, BNE, PER, NBO, CAI.

Developer Dashboard

API Playground — test endpoints with auto-generated curl/JS/Python snippets.
Usage Monitoring — 14-day chart of API calls and remaining quota.
API Key Management — generate/rotate ma_live_* keys with one click.
Request Logs — filterable log with timestamp, endpoint, and status.

Who Is This For?

Developers building travel apps or flight bots — clean JSON, no enterprise pricing.
Travelers who want a fast, ad-free flight board.
Businesses that need reliable airport data at reasonable cost.

Try It Now

Visit myairports.online — no auth needed for public flight boards.

For API access: myairports.online/signup

Real-time flight data. Clean JSON. No contracts. No headaches.

Originally published on Medium