DataHub PFAS

Environmental data warehouse for PFAS (per- and polyfluoroalkyl substances) contamination across France. Ingests the CNRS PDH dataset into a PostgreSQL star schema and exposes an interactive choropleth map and dashboard.

Architecture

pdh_data.parquet (CNRS PDH)
        │
        ▼
ETL pipeline  (pipeline.py)
  ① Extract   — filter France · water matrices · post-2016
  ② Transform — explode pfas_values · ng/L → µg/L · BDL flag · PubChem enrichment
  ③ Load      — upsert dimensions · bulk-insert facts (ON CONFLICT DO NOTHING)
  ④ Quality   — 8 DQ checks · JSON report · halt on critical failure
        │
        ▼
PostgreSQL 15 + PostGIS          MongoDB 7
  fact_measurements               communes  (GeoJSON polygons)
  dim_substances                  2dsphere index
  dim_networks                    ↑ seeded by migrate_communes.py
  dim_dates
  4 materialized views
        │                              │
        └──────────┬────────────────────┘
                   ▼
        Flask API  (/api/dashboard/*  /api/map/*)
                   │
                   ▼
        Leaflet choropleth map + Chart.js dashboard
                   │
                   ▼
        Looker Studio  (read-only PostgreSQL connector)

Prerequisites

Tool	Version
Python	3.11+
Node.js	18+
Docker + Compose	any recent
PostgreSQL client (`psql`, `pg_isready`)	optional, for debugging

macOS note: port 5000 is reserved by AirPlay. Run Flask on --port 5001 or disable AirPlay Receiver in System Settings → General → AirDrop & Handoff.

Quick start

1 — Start the databases

cp .env.example .env          # edit credentials if needed
docker compose up -d postgres mongodb

2 — Python environment

python -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt

3 — Frontend assets

npm install
npm run build-css             # Tailwind → app/static/dist/css/output.css

Re-run npm run build-css whenever you edit templates or input.css.
Use npm run watch-css during development for live recompilation.

4 — Database migrations

alembic upgrade head
# Creates: dim_substances, dim_networks, dim_dates, fact_measurements
#          + 4 materialized views + indexes

5 — Seed commune polygons (one-time)

Requires your existing pfas_db MongoDB (original pfas_project) to be running.

python scripts/migrate_communes.py
# Copies ~36 000 GeoJSON commune polygons → datahub_pfas MongoDB

6 — Run the ETL pipeline

Place pdh_data.parquet in data/raw/, then:

python pipeline.py --run-all

# Or stage by stage:
python pipeline.py --extract
python pipeline.py --transform
python pipeline.py --load
python pipeline.py --dq-check    # writes data/staging/dq_report.json

7 — Launch the app

flask run --port 5001
# Dashboard → http://localhost:5001/
# Map       → http://localhost:5001/map

8 — Full stack via Docker (optional)

docker compose --profile app up -d
# Builds the Flask image and starts app + databases together

API reference

Dashboard

Method	Endpoint	Description
GET	`/api/dashboard/stats`	Global counts: measurements, substances, networks, communes, threshold breaches
GET	`/api/dashboard/worst-communes?limit=20`	Communes ranked by max PFAS concentration
GET	`/api/dashboard/rankings?limit=50`	Substances ranked by detection count
GET	`/api/dashboard/trends?substance_id=&region=`	Monthly average concentration
GET	`/api/dashboard/exposure`	Population potentially exposed (> 0.1 µg/L) by department

Map

Method	Endpoint	Description
GET	`/api/map/communes?north=&south=&east=&west=&substance_id=&layer=`	GeoJSON choropleth for the current viewport
GET	`/api/map/commune/<insee_code>?substance_id=&layer=`	Full measurement history for one commune
GET	`/api/map/substances`	Substance list for the filter dropdown
GET	`/api/map/search?q=`	Commune name autocomplete

Water layer values: drinking_water · groundwater · surface_water · (empty = all)

Looker Studio connection

Looker Studio → Add data → PostgreSQL
Host: your server IP · Port: 5432
Database: datahub_pfas · User: looker_readonly · Password: from .env
Available: all dim_* tables, fact_measurements, all v_* materialized views

Running tests

pytest tests/                       # full suite (requires pdh_data.parquet)
pytest tests/ -k "not extract"      # fast subset, no parquet needed
pytest tests/ -v --tb=short         # verbose output

Project structure

DataHubPFAS/
├── app/
│   ├── config/             Database connection manager + settings
│   ├── models/             SQLAlchemy ORM models + Pydantic schemas
│   ├── repositories/       SQL and MongoDB query layer
│   ├── routes/             Flask blueprints (dashboard, map)
│   ├── static/
│   │   ├── src/input.css   Tailwind source (edit this)
│   │   └── dist/css/       Compiled output (git-ignored, built by npm)
│   ├── templates/          Jinja2 templates (base, home, map)
│   └── utils/              JSON provider, helpers
├── alembic/                Schema migrations
├── data/
│   ├── raw/                pdh_data.parquet          ← gitignored
│   ├── reference/          departements.json, deps_regs.json
│   └── staging/            ETL quarantine + DQ report ← gitignored
├── docker/                 PostgreSQL init SQL (PostGIS, read-only user)
├── etl/
│   ├── extract/            pdh_data.py, pubchem.py
│   ├── transform/          measurements.py, substances.py, geography.py
│   ├── load/               dimensions.py, facts.py, mongodb.py, views.py
│   └── quality/            runner.py — 8 DQ checks, JSON report
├── scripts/                migrate_communes.py (one-time)
├── tests/                  34 pytest tests
├── pipeline.py             ETL CLI entry point
├── wsgi.py                 WSGI entry point
├── docker-compose.yml      postgres + mongodb (+ flask via --profile app)
├── Dockerfile              Flask container
├── package.json            Tailwind build scripts
└── tailwind.config.js      Tailwind content paths

Data source

CNRS PDH Data Hub — pdh_data.parquet

942 094 rows × 21 columns (929 452 water measurements)
Filtered to: France · water matrices · category = Measurement · date ≥ 2016
Licence: open data — pdh.cnrs.fr

Navigation Menu

Repository navigation

About

Repository files navigation

DataHub PFAS

Architecture

Prerequisites

Quick start

1 — Start the databases

2 — Python environment

3 — Frontend assets

4 — Database migrations

5 — Seed commune polygons (one-time)

6 — Run the ETL pipeline

7 — Launch the app

8 — Full stack via Docker (optional)

API reference

Dashboard

Map

Looker Studio connection

Running tests

Project structure

Data source

About

Releases

Packages

Contributors

Languages

Name		Name	Last commit message	Last commit date
alembic		alembic
app		app
data/reference		data/reference
docker/postgres-init		docker/postgres-init
etl		etl
scripts		scripts
tests		tests
.env.example		.env.example
.gitignore		.gitignore
Dockerfile		Dockerfile
README.md		README.md
alembic.ini		alembic.ini
conftest.py		conftest.py
docker-compose.yml		docker-compose.yml
package-lock.json		package-lock.json
package.json		package.json
pipeline.py		pipeline.py
postcss.config.js		postcss.config.js
requirements.txt		requirements.txt
tailwind.config.js		tailwind.config.js
wsgi.py		wsgi.py

Latest commit

History

Folders and files

Repository files navigation

DataHub PFAS

Architecture

Prerequisites

Quick start

1 — Start the databases

2 — Python environment

3 — Frontend assets

4 — Database migrations

5 — Seed commune polygons (one-time)

6 — Run the ETL pipeline

7 — Launch the app

8 — Full stack via Docker (optional)

API reference

Dashboard

Map

Looker Studio connection

Running tests

Project structure

Data source

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages