Gestion de la géométrie dans les agrégations
Il est possible de faire des agrégations et des tris en utilisant les champs GROUP BY Columns et ORDER BY by columns.
Lorsqu'on fait une agrégation sur une table géométrique par un GROUP BY, il est important de comprendre que cela ne traite pas automatiquement la fusion des géométries. Ainsi si on se contente de reprendre le champ geometry dans le résultat, il ne faut pas l'utiliser.
Exemple : Grouper les statuts des communes
si on fait un GROUP BY sur le statut des communes, ainsi que sur les géométries, le résultat ne fusionne pas les géométries :
SELECT geom AS geometry, st_asText(geom) AS WKT, statut, SUM(superficie) AS superficie, SUM(population) AS population
FROM commune
GROUP BY commune.statut, geom;
le résultat contient la colonne Geometry (la transformation en texte de la géométrie au format WKT est demandée à titre illustratif) :
Lors d'opérations d'agrégation de données (ex. SUM()), TOUS les autres champs doivent être agrégés, il n'en est pas possible autrement. La colonne de géométrie n'échappe pas à cette règle. Le GROUP BY est possible, comme vu dans l'exemple, mais n'a pas d'action.
Pour "regrouper" les géométries, on utilise alors ST_UNION(), car les données géométriques ne sont pas gérées de la même manière que les autres données attributaires.
Un affichage sous QGIS montre que la géométrie n'a pas été fusionnée dans la table répondant au critère 'commune simple'.
D'une façon générale si on utilise un GROUP BY, toutes les colonnes en sortie sauf celle du critère de regroupement doivent faire l'objet d'une fonction d'agrégation, ce doit être également le cas pour la géométrie.
Pour obtenir la fusion des géométries il faut donc le demander explicitement avec une commande ST_UNION().
Dans notre exemple cela donne :
CREATE TABLE EXEMPLE AS
SELECT statut, ST_UNION(Geom) AS geometry, SUM(superficie) AS superficie, SUM(population) AS population
FROM commune
GROUP BY commune.statut
ORDER BY commune.statut;
(Pour créer la table exemple dans PostGIS sans l'exporter dans QGIS on utilise un CREATE TABLE en amont du SELECT).
La table est graphique et peut donc être chargée sous QGIS pour vérification. On peut écrire :
CREATE TABLE exemple AS
SELECT statut, st_multi(ST_Union(Geom)) :: Geometry(MULTIPOLYGON, 2154) AS geom, SUM(superficie) AS superficie, SUM(population) AS population
FROM commune
GROUP BY commune.statut
ORDER BY commune.statut;
La conversion en type 'multipolygon' avec le modificateur de type Geometry() met à jour automatiquement la vue 'geometry_columns'.
(Le résultat de ST_UNION() étant soit un 'polygon', soit un 'multipolygon' on utilise la fonction ST_MULTI() pour convertir tous les résultats en 'multipolygon' si la table d'origine avait été en polygones simples.).
Nb : Pour les lignes il est possible de supprimer les discontinuités et éviter la constructions de multilignes avec la fonction ST_LINEMERGE()
Par exemple :
SELECT toponyme, row_number() over() AS id, st_linemerge(ST_Union(Geom)) AS geom
FROM troncon_hydrographique
WHERE toponyme <> ''
GROUP BY toponyme;
DBManager demandant un identifiant de type entier unique pour charger les couches sous QGIS, il est créé avec row_number() over().
Ceci permet de récupérer le numéro de ligne qui est alors utilisé comme identifiant. Pour ceux qui sont intéressés, cette syntaxe utilise les possibilités avancés de SQL sur le fenêtrage
Complément :
Les possibilités de manipulation spatiale sont très grandes... voici quelques références supplémentaires :
Sur SQL d'une façon générale on pourra consulter un cours en ligne ou ce site de référence en français.
Pour faire simple, le champ géométrie est un champ au même titre que les autres, il est donc possible de faire un GROUP BY, mais la nature de ce champ fait que le résultat ne renvoie rien.
Comme pour beaucoup d'actions sur les champs géométriques, les fonctions spécifiques à l'extension PostGIS permettent de gérer la nature particulière de la géométrie.