Les requêtes SQL

4 minutes de lecture

Derrière chaque carte de ce carnet, il y a une requête. La base est un moteur PostGIS, la projection est partout le Lambert 93 métropolitain, code EPSG 2154, et les calculs spatiaux s'écrivent en SQL. Cette fiche rassemble les recettes qui reviennent le plus souvent, celles qui font le gros du travail : agréger une couche fine dans une échelle plus large, répartir proprement une entité à cheval sur plusieurs unités, calculer des quantiles, mesurer si une organisation territoriale explique un indicateur, remonter d'une commune à une agence, et nettoyer les doublons. Elles sont données ici comme des patrons, à adapter en remplaçant les noms de table et de champ selon le besoin.

Agréger une couche dans un zonage, au point représentatif

La question la plus fréquente est celle-ci : combien y a-t-il de tel objet, ou quelle est la somme de tel attribut, dans chaque unité d'une échelle ? La méthode simple consiste à rattacher chaque objet fin à l'unité qui contient son point représentatif. C'est rapide et suffisant quand l'objet est petit devant l'échelle, ce qui est le cas d'une parcelle agricole dans une agence, par exemple.

SELECT z.code, z.libelle, sum(f.valeur) AS total, count(*) AS n
FROM fine f
JOIN zonage z ON ST_Contains(z.geom, ST_Centroid(f.geom))
GROUP BY z.code, z.libelle;

L'intérêt de passer par le centroïde, ou mieux par un point garanti à l'intérieur de la géométrie, est d'attribuer chaque objet à une seule unité, sans double comptage.

Répartir au prorata surfacique

Quand l'objet n'est pas petit devant l'échelle, quand une commune chevauche deux bassins ou qu'un carreau statistique tombe à la frontière de deux agences, le rattachement par point tranche brutalement ce qui devrait se partager. L'attribut se répartit alors au prorata de la surface d'intersection.

SELECT z.code,
       sum(f.valeur * ST_Area(ST_Intersection(ST_MakeValid(f.geom), z.geom))
                    / NULLIF(ST_Area(f.geom), 0)) AS valeur_proratisee
FROM fine f JOIN zonage z ON ST_Intersects(f.geom, z.geom)
GROUP BY z.code;

La correction de validité géométrique évite qu'une géométrie mal formée fasse échouer l'intersection, et la division protégée écarte toute division par une surface nulle. Cette écriture est celle qui remonte la population ou la fragilité sociale des échelles fines vers les échelles de gestion.

Calculer des quantiles

Pour classer une variable sur une carte, ou pour décrire sa distribution, le calcul passe par ses quantiles. La fonction de quantile continu de PostGIS donne directement les bornes voulues, ici du minimum au maximum par pas de dixième.

SELECT unnest(percentile_cont(ARRAY[0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 1.0])
       WITHIN GROUP (ORDER BY valeur)) AS bornes
FROM ma_vue WHERE valeur IS NOT NULL;

Les valeurs absentes sont écartées de l'ordre, jamais remplacées par un zéro qui fausserait les seuils.

Mesurer si une organisation explique un indicateur

Une organisation territoriale regroupe-t-elle des unités homogènes ? La décomposition de la variance répond en calculant la part de variance d'un indicateur expliquée par les groupes, le rapport de corrélation, ou eta carré : la variance entre groupes rapportée à la variance totale. Plus la valeur approche de 1, plus les groupes sont cohérents en interne et distincts entre eux.

WITH g AS (SELECT decoupage, avg(v) AS mg, count(*) n FROM t GROUP BY decoupage),
     tot AS (SELECT avg(v) AS m FROM t)
SELECT sum(g.n * (g.mg - tot.m) ^ 2)
       / NULLIF((SELECT sum((v - tot.m) ^ 2) FROM t, tot), 0) AS eta2
FROM g, tot;

Cette mesure vient avec une précaution ferme, développée dans les méthodes de comparaison : eta carré croît mécaniquement avec le nombre de groupes. Le nombre d'unités figure donc toujours à côté de la valeur, et seules des organisations de grain voisin se comparent équitablement.

Remonter d'une échelle à l'autre par une table de correspondance

Les échelles s'emboîtent, mais pas toujours proprement ; c'est pourquoi la base ne suppose pas l'emboîtement, elle le matérialise par des tables de correspondance explicites, qui relient chaque unité fine à son échelle de gestion. Remonter une grandeur d'une commune à une agence se fait alors par une simple jointure sur cette table.

SELECT a.eco, a.label, sum(c.population) AS pop
FROM ref.communes c
JOIN travail.pont_commune_ace p ON p.commune = c.code_insee
JOIN travail.agences_cycle_eau a ON a.eco = p.eco
GROUP BY a.eco, a.label;

La même logique vaut pour remonter du secteur hydrographique, du carreau statistique ou de la parcelle. La correspondance rend le rattachement lisible et vérifiable, au lieu de le cacher dans un calcul géométrique refait à chaque requête.

Dédoublonner par un identifiant stable

Les ingestions par lots, département après département, peuvent réintroduire des doublons quand un suivi se désynchronise. Le nettoyage se fait par identifiant stable, ici l'identifiant unique de l'IGN, en ne gardant qu'une ligne par identifiant, table par table, chaque opération dans sa propre transaction pour rester atomique.

BEGIN;
CREATE TEMP TABLE dd ON COMMIT DROP AS SELECT DISTINCT ON (cleabs) * FROM bdtopo.t;
TRUNCATE bdtopo.t; INSERT INTO bdtopo.t SELECT * FROM dd;
COMMIT;

Une interruption laisse la table intacte plutôt qu'à moitié traitée, et repasser sur une table déjà propre ne change rien.