Technical Report
Conversational AI  ·  Ocean Data

FloatChat: Conversational Oceanographic Intelligence

A zero-friction assistant that turns ten years of ARGO float depth, temperature, and salinity profiles into plain-language answers — backed by a dual RAG engine that keeps working with no internet and no API credits.

255k+
Depth profiles
2015–25
Temporal coverage
2,060 m
Max depth span
<200 ms
Local engine latency
React + Flask proxy  ·  FastAPI + LangChain 0.3.27  ·  OpenAI GPT-3.5-Turbo  ·  Sentence-Transformers + ChromaDB  ·  PostgreSQL + PostGIS

Contents

  1. 01Executive Summary
  2. 02Why This Project
  3. 03Three-Tier Architecture
  4. 04Dual RAG Engine
  5. 05Data Pipeline & Schema
  6. 06Spatio-Temporal Querying
  7. 07API Endpoints
  8. 08Testing Strategy
  9. 09Key Design Decisions
  10. 10Challenges Faced
  11. 11Performance Metrics
  12. 12Future Enhancements & Stack
01

Executive Summary

FloatChat democratises access to oceanographic data. The international ARGO program deploys autonomous floats that cycle between the sea surface and 2,000 m depth, recording temperature, salinity, and pressure — but that data is locked in multi-dimensional NetCDF binaries that require specialised tools (Ocean Data View, MATLAB, xarray) to read.

FloatChat lets a researcher, climate analyst, or policy maker simply ask "What was the average salinity near Gujarat in winter 2023?" It answers by combining Retrieval-Augmented Generation, natural-language-to-SQL synthesis, a local NLP semantic parser, and an interactive dashboard — with a fully offline fallback so it never depends on paid cloud credits to function.

Dual engine
Online + offlineCloud LLM path plus a 100% local rule & embeddings engine
Precision
SQL, not vectorsNumerical aggregations run as real SQL to avoid LLM math hallucination
Transparency
Shows the SQLThe generated query is displayed alongside every answer
Scale
255k+ rows10 years of profiles across 22+ floats, 0–2,060 m depth
02

Why This Project

Oceanographic monitoring underpins climate research, monsoon forecasting, marine-ecosystem preservation, and maritime navigation. Yet accessing ARGO data presents three obstacles:

  • Complex binary formats — data lives in multi-dimensional NetCDF (.nc) files following CF metadata conventions.
  • Steep technical barrier — analysis requires Ocean Data View, MATLAB, or Python xarray / netcdf4.
  • Disconnected search — non-technical users cannot ask natural-language questions across depth, region, and season.
Design goal

Make deep-sea data universally queryable in plain language, with mathematical precision and full auditability — and keep it working when the cloud is unavailable.

03

Three-Tier Decoupled Architecture

A React dashboard talks to a Flask reverse proxy, which routes each query to one of two intelligence engines — a cloud FastAPI backend or a local Flask fallback — both of which resolve against the same PostgreSQL + PostGIS store of 255,000+ profiles.

Presentation Tier React Web Dashboard glassmorphism UI · port 3000 Flask Frontend Server reverse proxy · SSR templates HTTP POST /api/query Intelligence & Orchestration Tier Router / mode cloud mode local / fallback FastAPI Server (main.py) port 8000 LangChain SQL agent ChromaDB vector store Flask Backend (backend_server.py) port 5000 SmartOcean regex + spatial MiniLM-L6-v2 embeddings Data Storage & Processing Tier PostgreSQL + PostGIS argo_db · profiles table Raw NetCDF (.nc) ARGO / INCOIS archives ETL Pipeline etl_final.py · batch insert
Figure 1 — Request routing. The accent path is the cloud LLM engine; the neutral path is the fully offline fallback. Both converge on the same relational store, so answers are numerically identical regardless of which engine served the query.
04

Dual RAG Engine

Path A — Online Cloud RAG
  • FastAPI drives LangChain's SQL Database Agent with OpenAI gpt-3.5-turbo.
  • The agent inspects live database schemas and generates structured SQL.
  • ChromaDB retrieves relevant oceanographic domain metadata for context.
Path B — Offline Local RAG / Fallback
  • main_local.py / smart_query.py run with no external API.
  • SentenceTransformer (all-MiniLM-L6-v2) embeddings + spatio-temporal regex parsing.
  • Maps regions, seasons, and measurement types directly into parameterised SQL.

Processing workflow

  1. Query submission — the user types a natural-language question in the React chat interface.
  2. Dual-path processing — the router picks the cloud agent or the local engine based on configuration and API-key availability.
  3. Execution & synthesis — the database returns numerical results; the engine formats a natural-language response with metadata and the underlying SQL shown for scientific auditability.
05

Data Pipeline & Schema

The ETL pipeline (etl_final.py, load_all_incois_data.py) ingests multi-dimensional .nc files, decodes Julian timestamps, flattens depth profiles, normalises coordinates, and bulk-inserts into the profiles table.

[NetCDF (.nc) files]
       │
       ▼  xarray extraction: platform ID, lat, lon, time, pressure, temp, salinity
       ▼  data cleaning: timezone normalization, NaN filtering, QC checks
       ▼  flattening: 2-D arrays (time × depth) → 1-D relational rows
       ▼  batch insertion via SQLAlchemy Core (BATCH_SIZE = 50)
[PostgreSQL `profiles` table]

PostgreSQL schema — profiles

CREATE TABLE IF NOT EXISTS profiles (
    id             SERIAL PRIMARY KEY,
    filename       VARCHAR(255),
    float_id       VARCHAR(50),
    profile_index  INTEGER,
    depth_index    INTEGER,
    timestamp      TIMESTAMP,
    latitude       DOUBLE PRECISION,
    longitude      DOUBLE PRECISION,
    pressure       DOUBLE PRECISION,
    temperature    DOUBLE PRECISION,
    salinity       DOUBLE PRECISION,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_profiles_float_id   ON profiles(float_id);
CREATE INDEX idx_profiles_timestamp  ON profiles(timestamp);
CREATE INDEX idx_profiles_location   ON profiles(latitude, longitude);
CREATE INDEX idx_profiles_filename   ON profiles(filename);

Pre-mapped geographic bounding boxes — smart_query.py

RegionLatitudeLongitude
Gujarat Coast[20.0, 24.5][68.0, 74.5]
Arabian Sea[10.0, 25.0][60.0, 75.0]
Bay of Bengal[5.0, 22.0][80.0, 95.0]
Indian Ocean[−40.0, 25.0][20.0, 120.0]
06

Spatio-Temporal Intelligent Querying

Named regions
Gujarat, Arabian Sea, Bay of Bengal, Indian Ocean, Chennai, Mumbai — resolved to SQL BETWEEN latitude / longitude clauses.
Seasonal timeframes
Winter, Monsoon, Summer, Spring, Autumn — plus specific calendar years 2015–2025.
Depth resolution
Surface layer (0–10 m), shallow profiles (0–50 m), and deep-sea levels down to 2,060 m.

Query and SQL transparency: the generated SQL is displayed alongside the conversational answer so every result is scientifically auditable.

07

API Endpoints

Frontend proxy — http://localhost:3000

MethodEndpointDescription
GET/Project landing page
GET/homeMain dashboard & chat interface
POST/api/queryProxies chat requests to the backend engine
GET/api/healthFrontend + backend connectivity check
GET/api/dummy-dataSample oceanographic records for UI testing

Local RAG backend — http://localhost:5000

POST /api/rag-answer   {"question": "What is the salinity near Gujarat in winter 2023?"}
GET  /api/health       database and embedding-model status
GET  /                 API metadata and endpoint index

FastAPI cloud RAG — http://localhost:8000

POST /query   {"query": "How many ARGO floats are in the database?"}
GET  /health  backend system status
GET  /docs    interactive Swagger UI
GET  /redoc   ReDoc technical documentation
08

Testing Strategy & Verification

SuiteWhat it validates
Database & data (test_data.py)Table counts, null percentages, temperature / salinity ranges, coordinates within standard geographic bounds
RAG pipeline (test_rag.py, enhanced_test_rag.py)End-to-end QA across basic counts, statistical aggregations, and spatial queries; fallback triggers when the LLM endpoint is unreachable
OpenAI auth (test_openai_key.py)Credential validity and available token quota
Schema unit tests (create_test_table.py)Safe creation and teardown of test tables and indexes
09

Key Design Decisions

Hybrid dual-engine RAG
Rather than depend solely on paid cloud LLMs, a deterministic rule-based SQL generator and local transformer fallback keep the system available with no credits or connectivity.
Decoupled frontend proxy
The Flask frontend proxies backend microservices — no CORS issues, and backends can be swapped without touching UI code.
Direct SQL over vector chunking
Numerical time-series needs exact AVG / MIN / MAX. Generating SQL over a relational DB prevents LLM math hallucination; ChromaDB handles semantic metadata search.
Spatial bounding-box indexing
Pre-defined oceanographic coordinate boxes plus composite B-tree indexes give sub-second spatio-temporal lookups over hundreds of thousands of rows.
Chunked ingestion
BATCH_SIZE = 50 with periodic commits and explicit dataset closing handles tens of thousands of NetCDF files without Python memory exhaustion.
10

Challenges Faced & Solutions

ChallengeImpactEngineering solution
Inconsistent NetCDF dimensionsSome files use N_PROF / N_LEVELS, others TIME / PRES / depthDynamic dimension inspection in etl_final.py reads ds.dims and adapts coordinate extraction automatically
OpenAI quota & rate limitsExternal LLM calls could fail during demos or peak trafficmain_local.py / smart_query.py — local SentenceTransformer + regex SQL builders as a zero-cost fallback
High memory during ingestionIngesting tens of thousands of files caused out-of-memory errorsStreaming generator chunking with periodic transaction commits and explicit dataset closing
Geographic ambiguityUsers say "Gujarat" or "Arabian Sea" without coordinatesBounding-box knowledge dictionary translating regional names into SQL BETWEEN clauses
11

Performance Metrics

MetricValue
Total ingested measurements127,825 – 255,650+ records
Temporal reach10 years — September 2015 to September 2025
Float fleet coverage22+ unique ARGO profiling floats (Indian & Southern Oceans)
Depth range0 m (surface) to 2,060 m
Data quality> 98% valid temperature & salinity readings
Response time — local engine< 200 ms
Response time — cloud LangChain LLM2.0 – 4.5 s
12

Future Enhancements & Tech Stack

01 · Live telemetry
Real-time INCOIS satellite ingest webhooks that parse new float profiles as they surface.
02 · Geospatial map
Leaflet / Mapbox trajectory visualiser with depth–temperature heatmaps in the dashboard.
03 · BGC-ARGO
Expand to dissolved oxygen, pH, nitrate, and chlorophyll-A parameters.
04 · Anomaly detection
Agentic background jobs that flag marine heatwaves and alert researchers.
Frontend
React.js, HTML5 / CSS3 glassmorphism, Vanilla JS ES6+, Flask (proxy & SSR)
Backend & AI
FastAPI, Flask, LangChain 0.3.27, OpenAI GPT-3.5-Turbo, Sentence-Transformers all-MiniLM-L6-v2, ChromaDB
Data & DB
xarray, netCDF4, Pandas, NumPy, SQLAlchemy, PostgreSQL 15+ with PostGIS

Setup

git clone <repo-url> && cd floatchat_prototype
python -m venv venv && .\venv\Scripts\activate
pip install -r requirements.txt

python etl_final.py          # run the ETL pipeline
python launch.py             # option 3 → start backend + frontend
# Landing  http://localhost:3000
# Docs     http://localhost:8000/docs

FloatChat · AI-Powered Conversational Oceanographic Intelligence System · Technical Report