Aviation Network Graph

A graph database platform for analyzing the global airline network — and a natural-language query interface on top of it.

I merged two public aviation datasets (OpenFlights + OurAirports) into a Neo4j graph of 7.9K airports, 6.2K airlines and 67K routes, built an idempotent Python ETL pipeline with data-quality validation, implemented six network-analytics workloads (runway infrastructure, airline reach, regional carriers, one-stop itinerary search, widebody compatibility), profiled and tuned them — and on top of that foundation, a Text2Cypher layer turns plain-language questions into validated, read-only Cypher with a self-annotated evaluation set.

Python Neo4j Cypher ETL LLM / Text2Cypher

Architecture

Three layers, each building on the one below — the Text2Cypher layer injects the graph's schema into its prompts and relies on its constraints for validation, so it is part of the same system, not a bolt-on demo.

flowchart LR
    subgraph SRC["Raw data · public sources"]
        OF["OpenFlights<br/>airports · airlines · routes · planes"]
        OA["OurAirports<br/>airports · runways · countries · regions"]
    end
    subgraph ETL["etl/ — idempotent Python pipeline"]
        M["merge · validate · load<br/>ICAO-keyed, config-driven precedence<br/>keyed MERGE — re-run is a no-op"]
    end
    subgraph DB["Neo4j"]
        G[("7,882 airports · 67,153 routes<br/>10,194 runways · 6,161 airlines")]
        C["4 uniqueness constraints<br/>7 performance indexes"]
    end
    subgraph NL["text2cypher/ — natural-language layer"]
        P["schema-injected<br/>prompt"] --> GEN["LLM"] --> VAL["validate<br/>EXPLAIN + notifications"] --> GRD["read-only guard<br/>driver READ session"]
    end
    SRC --> ETL --> DB --> P
    GRD --> API["api/ · POST /ask<br/>28-question eval: 89%"]
    C -. "schema injection" .-> P
    C -. "constraint-backed validation" .-> VAL

Loading

The graph model itself — six node labels, six relationship types, with loaded counts (design rationale: docs/data-model.md):

Quickstart

# 1. start Neo4j
docker compose up -d

# 2. install dependencies
pip install -r requirements.txt

# 3. run the ETL on the committed sample data (or download the full
#    datasets first — see data/README.md — and use --data-dir data/full)
python -m etl --data-dir data/sample

# 4. explore: http://localhost:7474  (neo4j / aviation-dev)
#    analytical queries live in cypher/queries/

# 5. (optional) Text2Cypher — any OpenAI-compatible API works;
#    put DEEPSEEK_API_KEY=... (or LLM_API_KEY + LLM_BASE_URL + LLM_MODEL)
#    in a .env file at the repo root
python -m text2cypher "Which airlines fly non-stop from Sydney to Singapore?"
python -m text2cypher.eval        # 28-question evaluation
python -m api                     # POST /ask on :8000

The ETL is idempotent — re-running it never creates duplicates. Add --validate-only to get the data-quality report without touching the database, --wipe for a clean re-load.

Query workloads

Query	Business question	File
Runway infrastructure	Average runway length by airport type	runway_infrastructure.cypher
Airline network reach	Top airlines by distinct time zones served	airline_network_reach.cypher
Regional carriers	Airlines flying only intra-region routes	regional_coverage.cypher
One-stop itineraries	SYD→LHR connections, same airline both legs	multihop_itinerary.cypher
Widebody infrastructure	Countries by widebody-capable airports	widebody_infrastructure.cypher
Aircraft–runway fit	Plane types serving unpaved-only airports	aircraft_runway_compat.cypher

Every query file ships with sanity-check queries (spot-checks, membership checks, cross-tab totals) used to verify the results.

Performance profile

Three representative workloads profiled with Neo4j PROFILE on the full graph (warm cache, measured through the Python driver). Full analysis: docs/performance-tuning.md.

Workload	Before	After	Root cause / fix
One-stop itinerary	110 ms / 148K db hits	6.4 ms (17×)	missing Airline.id index → 28×6,161 cartesian; planner expanding 115K routes → USING JOIN ON via hash join
Airline network reach	1,217 ms	635 ms (1.9×)	collecting DISTINCT node maps through aggregation → expand [tz_name, icao] string pairs per route row
Runway infrastructure	28.6 ms	25.5 ms	collect()+reduce() materializes 9.7K lengths → built-in avg()
Widebody infrastructure	19.0 ms	9.5 ms	already near-optimal: 96% filter selectivity + early LIMIT

Key lessons: "slow query" can be a schema bug — verify indexes actually exist; cartesian products announce themselves as row-count explosions in PROFILE; join hints are a legitimate tool for two-ended path queries.

Neo4j vs PostgreSQL: same-data comparison on three workloads — Neo4j wins anchored multi-hop traversal (~1.9× at 1 and 2 stops, once the planner is helped), PostgreSQL wins global aggregation (~4×). The engine choice is workload-driven; numbers and reasoning in docs/pg-vs-neo4j.md.

Text2Cypher — natural-language queries

Ask in English, get validated read-only Cypher and sourced results:

$ python -m text2cypher "Which airlines fly non-stop from Sydney to Singapore?"
-- MATCH (syd:Airport {iata: 'SYD'})-[r:ROUTE]->(sin:Airport {iata: 'SIN'})
-- WHERE r.stops = 0 MATCH (al:Airline {id: r.airline_id}) ...
-- (1 attempt, 9 rows: British Airways, Qantas, Singapore Airlines, …)

• Schema-constrained generation — the full 6-label / 6-relationship schema (properties, constraints, indexes, query conventions) is small enough to inject into the prompt verbatim, plus 4 few-shot pairs.
• Two-track validation-repair loop — generated Cypher is checked with EXPLAIN for syntax errors and via result.summary().notifications for schema hallucinations: Neo4j 5 does not throw on unknown labels/properties, it warns and silently returns 0 rows, so WARNING notifications are treated as failures. Both tracks feed the error back to the LLM, max 2 retries.
• Read-only guardrails, defense in depth — the driver session runs in default_access_mode=READ, so writes die at the transaction layer regardless of query text (Neo4j Community has no RBAC; the session access mode is the authoritative control). A statement allowlist (read clauses only, whitelisted procedures, write-clause scan with string literals stripped) is layer 2; keyword filtering is demoted to a log-only layer 3 — it is both bypassable and over-broad.
• Evaluation, not vibes — 28 hand-annotated question/Cypher pairs (eval/questions.jsonl) with fixed comparison rules: unordered row-multiset match (unless the question has top-k semantics), column names ignored, None ≡ missing column, floats to 1 decimal; gold answers are executed live at eval time, and questions with empty gold results are rejected. Run it: python -m text2cypher.eval (ablation switches --no-schema, --no-repair).

Results (DeepSeek, temperature 0)

Configuration	Execution match
schema injection + repair loop	24–25 / 28 (86–89%) over 3 runs
schema only (no repair)	25 / 28 (89%)
repair only (no schema)	13 / 28 (46%)
neither	12 / 28 (43%)

Schema injection is the dominant factor (+43 pp); the repair loop's effect is within run-to-run noise (±1 question) because first-attempt queries almost always pass validation when the schema is visible — the loop earns its keep exactly in the no-schema regime, where hallucinations are frequent. The residual failures are semantic, not syntactic. Failure attribution on the full configuration:

Question	Failure
routes of airline X	relationship-pattern hallucination — writes (Airline)-[:ROUTE]->(), treating ROUTE as an Airline edge; every element exists, so validation passes and the query silently counts 0. The hardest failure class; catching it needs empty-result feedback (future work)
large airports in NSW	answer shape — correct airport, extra unasked columns
airports in London	answer shape — identical rows, column order swapped

The eval itself was iterated twice and then frozen, disclosed here for honesty: one prompt fix (the original "always LIMIT 25" rule truncated complete enumerations) and one gold-standard minimality pass (golds now return exactly the columns the question asks for). Raw runs: eval/results/.

API

python -m api serves POST /ask (question → cypher + rows + attempt count) and GET /health on port 8000, implemented on the standard library (http.server) — no web framework dependency.

Data

OpenFlights (ODbL) and OurAirports (public domain). Only a referentially-consistent sample is committed; see data/README.md for download instructions and the exact graph-size breakdown produced by the validator.

Graph model decisions — routes as relationships, runways as nodes, the deliberately redundant OPERATES edge, two-source merge precedence — are in docs/data-model.md.

License

MIT — see LICENSE.