An integrated NL2SQL–RAG framework for large-scale EMS log analytics and evidence-based policy analysis
Emergency medical service (EMS) activity logs represent one of the largest repositories of operational public-sector data, yet their analytical utilization remains limited due to the coexistence of structured dispatch records and heterogeneous unstructured evaluation notes. This study presents an integrated NL2SQL–RAG framework for large-scale EMS log analytics and evidence-based policy analysis. Rather than introducing a new standalone NL2SQL or retrieval algorithm, the framework provides a system-level integration of structured statistical analysis, generative-AI–based narrative structuring, metadata-aware retrieval, accumulated analytical context, and policy-oriented evidence synthesis. The framework unifies (i) structured statistical query generation via natural language–to–SQL (NL2SQL), (ii) generative-AI–based structuring of narrative evaluation notes using a 5W1H representation, (iii) retrieval-augmented generation (RAG)–based similar case search over unstructured text, and (iv) a policy-oriented analytical synthesis module. For structured analytics, we use a failure-sensitive evaluation scheme that retains generation/execution failures and empty-result outcomes and combines execution-level (QES) and structural (QFS) diagnostics, enabling analysis beyond surface-level query accuracy. Controlled comparisons across 36 queries did not provide statistically supported QES improvements from runtime-introspected schema representation or fixed three-shot prompting. For unstructured retrieval, we systematically examine chunking strategies, metadata-aware hybrid retrieval, and evaluation-note structuring using Filter Satisfaction Rate (FSR), Filter Drop Rate (FDR), and RAGAS-based faithfulness and relevance metrics. Across the evaluated RAG conditions, the results revealed trade-offs among dense, sparse, and metadata-aware retrieval strategies, with the clearest benefit of metadata filtering observed for metadata-relevant queries. Policy control analysis further showed that accumulated evidence increased Evidence Support and Evidence Coverage relative to closed-book generation and increased Evidence Coverage, but not Evidence Support, relative to single-turn hybrid RAG; the separate expert evaluation showed criterion-dependent reliability and an uncertain association between Evidence Quality and Overall Quality. Overall, the experiments demonstrate the feasibility of integrating structured analytics, case-based retrieval, and evidence-oriented policy synthesis within the evaluated EMS dataset and query scenarios. The primary contribution of this work is therefore an empirically evaluated system-level architecture that connects structured and unstructured EMS evidence across successive analytical interactions within a single decision-support workflow, together with an analysis of the capabilities, trade-offs, and limitations of its major components.
Authors
- Youngjung Suh (ORCID: https://orcid.org/0009-0001-3762-9609)
- 김혜진
- Chaehee Park
Institutions
- Kongju National University (KR)
Publication Details
- Journal
- Journal Of Big Data
- Published
- 2026-11-20
- DOI
- https://doi.org/10.1186/s40537-026-01621-3
- Primary Topic
- Topic Modeling
- Type
- article
- Field-Weighted Citation Impact
- 0.00