Skip to content

ESG — Flood risk on DPE F and G housing

Persona path: carol (analyst) · Catalogs: postgres_postgis_esg · Duration: ~25 min · Difficulty: star star

This demo combines an energy-performance dataset (DPE) with a flood risk geospatial layer. The Catalog hosts the join hint; ADEN composes the PostGIS query; the BI layer renders the map. Per Sprint 62.3 spec, roughly 33% of DPE F and G buildings sit inside a high flood risk polygon.

What this proves

  • ADEN can compose a spatial join when the Catalog declares the geometry columns.
  • The PostGIS extension on the source database stays the system of record; AKKO orchestrates the query.
  • The BI layer renders both a table and a map without extra plumbing.
  • Carol gets a defensible ESG insight with provenance back to the source rows.

Pre-requisites

  • Demo URL: https://demo.akko-ai.com
  • Catalog postgres_postgis_esg already federated.
  • 1 persona provisioned: carol.

Step 1 — Carol signs in and explores the ESG catalog

Sign in as carol. Navigate to Governance → Catalog explorer → postgres_postgis_esg.

Expected tables:

esg.dpe_buildings        (412 000 rows, geometry POINT)
esg.flood_zones          (8 240 rows, geometry POLYGON)
esg.communes             (35 000 rows, geometry POLYGON)
esg.iris                 (49 000 rows, geometry POLYGON)

Click dpe_buildings and confirm the columns:

  • building_id — ULID
  • address — text
  • dpe_class — A to G
  • living_area_m2 — integer
  • geom — POINT SRID 4326

Click flood_zones and confirm:

  • zone_id — text
  • risk_level — low, medium, high
  • geom — POLYGON SRID 4326

Screenshot: tests/e2e/playwright/artefacts/demos/esg-flood-risk/01-catalog-tables.png

Step 2 — Carol opens ADEN

Click AI → ADEN. Set:

  • Catalog: postgres_postgis_esg
  • Model: qwen2.5-coder:7b

Screenshot: tests/e2e/playwright/artefacts/demos/esg-flood-risk/02-aden-scope.png

Step 3 — Carol asks the spatial question

Prompt:

show buildings with DPE class F or G located inside a high flood risk zone,
return count, share of total F/G stock, and a sample of 1000 with coordinates

Click Ask. Expected SQL:

WITH fg AS (
    SELECT b.building_id, b.address, b.living_area_m2, b.geom
    FROM postgres_postgis_esg.esg.dpe_buildings b
    WHERE b.dpe_class IN ('F', 'G')
), exposed AS (
    SELECT fg.*
    FROM fg
    JOIN postgres_postgis_esg.esg.flood_zones f
      ON f.risk_level = 'high'
     AND ST_Intersects(fg.geom, f.geom)
)
SELECT
    (SELECT count(*) FROM exposed)                                   AS n_exposed,
    (SELECT count(*) FROM fg)                                        AS n_fg_total,
    CAST(100.0 * (SELECT count(*) FROM exposed)
                / NULLIF((SELECT count(*) FROM fg), 0) AS DECIMAL(5,2)) AS share_pct;

Expected result:

| n_exposed | n_fg_total | share_pct |
| 27 412    | 82 980     | 33.04     |

Screenshot: tests/e2e/playwright/artefacts/demos/esg-flood-risk/03-aden-result.png

Step 4 — Carol promotes the map dashboard

Click Promote to dashboard. ADEN detects the geometry column and proposes a 4-tile dashboard:

  1. KPI — Exposed buildings (27 412)
  2. KPI — Share of F/G stock (33.04%)
  3. Map — Exposed buildings color-coded by class
  4. Table — Sample of 1000 with address and area
+-------------------- AKKO ESG — DPE F/G at flood risk -----------------+
| 27 412 buildings | 33.04% of F/G stock                                |
+----------------------------------------------------------------------+
| [ Map: red dots over flood polygons ]   [ Table sample 1000 ]        |
+----------------------------------------------------------------------+

Screenshot: tests/e2e/playwright/artefacts/demos/esg-flood-risk/04-map-dashboard.png

Step 5 — Carol drills down by region

In the dashboard, add a filter on region_code. Set to 13 (Bouches-du-Rhône).

Expected: the map zooms to the south of France, the KPI updates to 1 842 exposed / 35.7% share and the table refreshes accordingly.

Screenshot: tests/e2e/playwright/artefacts/demos/esg-flood-risk/05-drilldown-13.png

Step 6 — Carol exports the GeoJSON for a partner

Click Export → GeoJSON.

Expected: a .geojson file with 27 412 features, properties building_id, dpe_class, living_area_m2. The address column is truncated to the street name by the Governance layer for non-admin roles.

Screenshot: tests/e2e/playwright/artefacts/demos/esg-flood-risk/06-export-geojson.png

Cleanup

  • Sign out.
  • Optional: remove the dashboard filter so the next demo starts clean.

What this proves

  • ADEN handles PostGIS spatial joins natively when the Catalog declares geometry types.
  • The 33% figure aligns with the Sprint 62.3 synthetic dataset spec.
  • Map rendering happens in the BI layer; the Query layer streams the geometry.
  • Address truncation is enforced at export, not just in the UI.

Files in the repo

File Role
scripts/seed-esg.sh Seeds DPE buildings and flood polygons
dbt/models/marts/esg/dpe_flood_exposed.sql Materialized view for the dashboard
helm/akko/charts/akko-init/templates/esg-seed-job.yaml Bootstraps the schema