Méthode de calcul et requêtes SQL

À quoi sert cette page ? Elle donne les requêtes SQL qui reproduisent les calculs clés directement sur le géopackage, un fichier SQLite étendu à la géométrie. Chaque bloc peut être rejoué tel quel : comptage des assemblées, population par écorégion, densité de drainage, nappes à cheval, recouvrement avec les hydro-écorégions.

Le fichier ecoregions-2027.gpkg est un GeoPackage, c'est-à-dire un SQLite étendu à la géométrie. Toutes les requêtes ci-dessous s'exécutent directement avec spatialite, ogr2ogr, QGIS ou tout client SQLite standard ; les géométries sont stockées en WKB dans la colonne geom. Ce qui suit reproduit les calculs clés, du comptage des unités jusqu'aux diagnostics de cogestion.

Interroger le catalogue

Le GeoPackage se documente lui-même. La table catalogue_couches liste chaque couche avec ses métadonnées, ce qui permet de vérifier producteurs, millésimes et licences sans quitter la base.

-- Lister toutes les couches avec leurs métadonnées
SELECT theme, couche, producteur, millesime, licence, n_entites, description
FROM catalogue_couches
ORDER BY theme, couche;

-- Compter les couches par thème
SELECT theme, COUNT(*) AS n_couches
FROM catalogue_couches
GROUP BY theme
ORDER BY theme;

Les 189 assemblées

La brique élémentaire des assemblées est le sous-secteur hydrographique BD Carthage, 1 140 entités couvrant l'Hexagone et la Corse. Une assemblée résulte de la dissolution des sous-secteurs partageant la même écorégion et le même secteur hydrographique parent. Le résultat compte 189 assemblées : 187 unités ordinaires, un secteur entièrement contenu dans une écorégion, plus deux entités à cheval produites par la fragmentation de secteurs limites (Z5, îles de la Méditerranée occidentale, et Y6, Var-Argens), dont les sous-secteurs relèvent de deux écorégions.

-- Assemblée = dissolution des sous-secteurs par (CdSecteurH, eco)
SELECT
    s.CdSecteurH                  AS secteur,
    e.eco                         AS ecoregion,
    s.CdSecteurH || '_' || e.eco  AS id_assemblee,
    COUNT(*)                      AS n_sous_secteurs,
    ST_Union(s.geom)              AS geom          -- dissolution géométrique
FROM sous_secteurs s
JOIN (
    -- affecter chaque sous-secteur à son écorégion par point représentatif
    SELECT ss.fid AS ss_fid, ef.eco
    FROM sous_secteurs ss
    JOIN ecoregions_fin ef
      ON ST_Within(ST_PointOnSurface(ss.geom), ef.geom)
) e ON e.ss_fid = s.fid
GROUP BY s.CdSecteurH, e.eco;
-- Requiert l'extension SpatiaLite pour ST_Union et ST_Within

Population par écorégion

La population de chaque écorégion s'obtient par agrégation des communes ADMIN EXPRESS et se trouve stockée dans la couche ecoregions_fin. La dispersion mesure l'écart entre l'unité la plus peuplée et la moins peuplée.

-- Classement des écorégions par population
SELECT eco AS ecoregion, pop AS population,
       km2 AS surface_km2, dens AS densite_hab_km2
FROM ecoregions_fin
ORDER BY pop DESC;

-- Statistiques de dispersion
SELECT COUNT(*) AS n_ecoregions,
       MIN(pop) AS pop_min, MAX(pop) AS pop_max,
       ROUND(AVG(pop), 0) AS pop_moyenne,
       ROUND(CAST(MAX(pop) AS REAL) / MIN(pop), 1) AS ratio_max_min,
       SUM(pop) AS pop_totale
FROM ecoregions_fin;

Densité de drainage

La longueur du réseau hydrographique nommé, croisée avec la surface des écorégions, donne la densité de drainage en kilomètres de cours d'eau par kilomètre carré.

-- Longueur du réseau par écorégion (requiert SpatiaLite)
SELECT
    ef.eco  AS ecoregion,
    ef.km2  AS surface_km2,
    ROUND(SUM(ST_Length(ST_Intersection(ce.geom, ef.geom))) / 1000.0, 1)
            AS long_reseau_km,
    ROUND(SUM(ST_Length(ST_Intersection(ce.geom, ef.geom))) / 1000.0 / ef.km2, 3)
            AS densite_drainage_km_par_km2
FROM ecoregions_fin ef
JOIN cours_eau ce ON ST_Intersects(ce.geom, ef.geom)
GROUP BY ef.eco, ef.km2
ORDER BY densite_drainage_km_par_km2 DESC;

Nappes à cheval et cogestion

La couche meso_diagnostic identifie les masses d'eau souterraines dont l'emprise franchit plusieurs bassins. Une valeur de coupee_bassins à 1 signale une nappe qui impose une cogestion entre agences. Le découpage actuel en 7 circonscriptions laisse 124 nappes à cheval sur 639, soit près d'une nappe sur cinq.

-- Nappes à cheval sur au moins deux bassins
SELECT md.code_meso, md.nom_meso,
       md.n_bassins_recoupes, md.liste_bassins,
       ROUND(md.surf_totale_km2, 0) AS surf_km2
FROM meso_diagnostic md
WHERE md.n_bassins_recoupes >= 2
ORDER BY md.n_bassins_recoupes DESC, md.surf_totale_km2 DESC;

-- Combien de nappes à cheval par nombre de bassins recoupés
SELECT n_bassins_recoupes, COUNT(*) AS n_nappes
FROM meso_diagnostic
GROUP BY n_bassins_recoupes
ORDER BY n_bassins_recoupes;

Comparaison avec les hydro-écorégions

Les 22 hydro-écorégions de niveau 1 (Wasson, 2002) et les écorégions hydrographiques reposent sur des logiques distinctes, physico-chimique et écologique d'un côté, hydrologique et hydrographique de l'autre. La mesure des recouvrements éclaire ce que chaque maillage capte.

-- Surface de recouvrement HER-1 x écorégion (requiert SpatiaLite)
SELECT h.NomHER1 AS her1, ef.eco AS ecoregion,
       ROUND(ST_Area(ST_Intersection(h.geom, ef.geom)) / 1e6, 0) AS surf_km2_commun
FROM her1 h
JOIN ecoregions_fin ef
  ON ST_Intersects(h.geom, ef.geom)
 AND ST_Area(ST_Intersection(h.geom, ef.geom)) > 1e6  -- seuil 1 km2
ORDER BY her1, surf_km2_commun DESC;

Le détail du code qui produit ces couches figure dans les scripts commentés ; les vérifications qui referment la boucle sont dans les contrôles de cohérence.

Ce qu'il faut en retenir

Le géopackage se documente lui-même par sa table de catalogue, et chaque chiffre du dossier se recalcule par une requête publiée ici. Les traitements spatiaux demandent l'extension SpatiaLite, mais la logique reste lisible : agréger des communes par zonage, dissoudre des secteurs par écorégion, croiser des nappes avec des bassins. Rien n'est caché derrière un calcul non reproductible.