L'assistant de requête SQL de QspatiaLite
L'assistant de requête est une aide pour réaliser des requêtes SQL. Son utilisation n'est pas obligatoire, mais utile pour les débutants en SQL et pour les utilisateurs connaissant MapInfo qui ne seront pas trop dépaysés.
Cette boîte de dialogue permet de construire la requête SQL. La démarche est d'abord de sélectionner la ou les tables sur lesquelles on souhaite travailler (ex : ''AERODROME'', puis les colonnes de ces tables que l'on souhaite en sortie ou mettre * dans la case Columns pour choisir toutes colonnes.
Ex :
"AERODROME".'NATURE',
"AERODROME".'DESSERTE',
"AERODROME".'TOPONYME'
et éventuellement d'ajouter une condition (clause where) pour laquelle on peut utiliser les listes déroulantes à droite.
exemple :
NATURE = 'Normal'
Remarque :
Il n'y a pas * par défaut (comme sous MapInfo) dans le champ 'Columns' dans la boîte de dialogue pour sélectionner tous les champs,
on peut écrire = ou == comme opérateur d'égalité.
on ne dispose pas de = ANY (utiliser IN)
|| → est l'opérateur de concaténation (ne pas utiliser + comme sous MapInfo)
Complément :
GLOB : est similaire à l'opérateur LIKE (%= 0 à n caractères, _= 1 caractère) mais utilise les jokers unix (* = 0 à n caractères, ?= 1 caractère ) et est sensible à la casse.
BETWEEN n'est pas disponible dans les menus déroulants, mais est utilisable.
MATCH : permet de comparer un ensemble de valeurs de ligne à un ensemble de lignes retourné par une sous-requête (usage rare). Voir ici pour en savoir plus.
REGEXP : permet d'utiliser les expressions régulières ou rationnelles (voir ici). Le documentation précise toutefois qu'il faut se définir sa propre fonction regexp() car il n'y en pas par défaut. L'utilisation de cet opérateur sans définir de fonction génère un message d'erreur. Très peu utile pour les besoins des services.
Attention :
SQLite est laxiste sur le contrôle de type des champs, plus précisément SQLite utilise un typage dynamique. Là où MapInfo affichera un message du type 'Non-concordance de type de données dans l'expression' QspatiaLite n'affichera rien mais le résultat sera 'Empty résultat' (résultat vide). Rappelons la fonction cast pour faire des comparaisons avec changement de type.
Affichage de toutes les communes avec calcul d'un champ supplémentaire de densité
Le choix d'une colonne dans la liste déroulante 'columns' ajoute le nom du champ précédé d'une virgule. Ce n'est pas toujours souhaitable comme dans le cas où l'on fait une division de deux champs. Il faut alors supprimer la virgule. Par défaut le nom de la table est ajouté devant le nom de la colonne.
L'appel aux fonctions comme round pour arrondir que l'on peut choisir dans la liste déroulante 'math' n'affiche pas la syntaxe de la fonction. Pour une aide sur les fonctions spatiale, il est conseillé d'utiliser une description en ligne. (il est possible d'obtenir la version SpatiaLite dans le menu 'a propos' de QGIS). Pour QGIS 2.2 la version de spatialite est la 4.1.1
Pour une aide sur les fonctions de sqlite on pourra consulter ce site.
La validation par OK affiche alors la syntaxe en SQL. Dans notre cas :
SELECT *,
Round(cast("COMMUNE".'POPULATION' as float) /
"COMMUNE".'SUPERFICIE',2) as densite
FROM "COMMUNE"
onglet option
Il permet de fixer la destination du résultat :
par défaut dans une nouvelle table provisoire 'sqlResult'.
On peut :
Load in QGIS as Spatial layer : charger dans QGIS comme table spatiale (contenant une géométrie). Dans ce cas on peut désigner la colonne contenant la géométrie.
Create Table & Load in QGIS : créer une table (uniquement attributaire) du nom indiqué dans Table Name dans Spatialite et charger cette table dans QGIS.
Create Spatial Table & Load in QGIS : créer une table spatiale (avec la géométrie) et la charger dans QGIS.
Create View & Load in QGIS : créer une Vue et la charger dans QGIS.
Create spatial View & Load in QGIS : créer une Vue spatiale et la charger dans QGIS.
Une vue est une table virtuelle. Seule la requête est mémorisée et à nouveau exécutée à chaque fois que l'on utilise la vue comme une table. Le concept est tout à fait similaire aux tables requêtes de MapInfo.
Conseil : 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 goup By Columns et Order 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 :
si on fait un group by sur le statut des communes :
select geometry, asText(geometry) as WKT, statut, sum(superficie) as superficie, sum(population) as population from commune group by commune.statut
le résultat
contient la colonne Geometry (la transformation en texte de la géométrie au format WKT est demandée à titre illustratif)
Cependant un affichage sous QGIS montre que la géométrie est uniquement celle de Clermont-Créans (première commune dans la table répondant au critère 'commune simple' et non la fusion des géométries de toutes les communes de Statut '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(Geometry) 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 spatialite sans l'exporter dans QGIS on utilise un create table en amont du select).
La table est affichée comme étant uniquement attributaire.
Il faut utiliser la fonction RecoverGeometryColumn() pour mettre à jour les tables internes de métadonnées de spatialite :
SELECT RecoverGeometryColumn('EXEMPLE', 'Geometry', 2154, 'MULTIPOLYGON', 'XY')
ou SELECT RecoverGeometryColumn('EXEMPLE', 'Geometry', 2154, 'POLYGON', 'XY') si les objets sont des polygones simples.
nb : sous PostGIS (version antérieure à la 2.0) on pourra utiliser Populate_Geometry_Columns() ou Probe_Geometry_Columns()
La table devient graphique et peut-être chargée sous QGIS pour vérification.
A partir de la version 2.0 de PostGIS il n'est plus nécessaire d'utiliser les fonctions de mise à jour des tables internes. Le type geometry étant un type à part entière 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').
nb : Pour les lignes il est possible de supprimer les discontinuités et éviter la constructions de multilignes avec la fonction st_linemerge()
ex : 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.
