Blog SIG & Territoires

Une des différences les plus marquées entre la gestion des données avec des fichiers shape et une base de données est la possibilité de créer des « vues » différentes sur les mêmes données. Dans cet article, le dernier de la série consacrée à l’utilisation de SpatiaLite avec QGis nous verrons comment construire des requêtes SQL pour générer des vues SpatiaLite.
DB Manager possède une fenêtre SQL qui permet la construction de requêtes SQL et de les exécuter sur une base de données. Cet article montre comment utiliser la fenêtre SQL pour interroger une table et créer une vue spatiale en SpatiaLite.

Création d’une requête SQL

Les différentes bases de données prennent en charge tou ou une partie des commandes SQL. SQLite prend en charge une grande partie de la norme ANSI SQL92, mais pas toutes. Pour une liste complète des opérations de SQL prises en charge, visitez le site https://sqlite.org/lang.html

Pour créer une requête SQL, effectuez les opérations suivantes :
1. Ouvrez le gestionnaire de base de données en cliquant sur Gestionnaire de base de données sous Base de données.
2. Dans l’arborescence, recherchez et sélectionnez la base de données sur laquelle vous souhaitez exécuter une requête SQL.
3. Cliquez sur Base de données | Fenêtre SQL, ou appuyez sur F2 sur votre clavier, pour ouvrir la fenêtre SQL.
4. Entrez une requête SQL dans la zone de texte, en haut. Cliquez sur le bouton exécuter ou F5 sur votre clavier pour exécuter la requête SQL sur la base de données. Les résultats de la requête seront affichés dans la zone de résultats, en bas, et le nombre de lignes traitées et le temps d’exécution s’affichera à côté du bouton Exécuter. Un exemple d’une requête d’exécution réussie est illustré dans la capture d’écran suivante :

Vous pouvez stocker n’importe quelle requête en saisissant un nom dans la zone de texte, en haut, et cliquant ensuite sur le bouton Stocker. Pour charger et exécuter la requête stockée, sélectionnez le nom de la requête dans le menu déroulant, en haut. Pour supprimer une requête stockée, sélectionnez la requête dans le menu déroulant et cliquez ensuite sur le bouton Effacer (à côté de Stocker).

Création d’une vue spatiale

La création d’une vue spatiale sur une base de données SpatiaLite, avec la fenêtre SQL dans le gestionnaire de base de données, est un processus en deux étapes.

• La première étape consiste à créer une vue contenant un champ avec des identificateurs uniques et la colonne geometry
• La deuxième étape consiste à insérer un nouvel enregistrement dans la table views_geometry_columns pour enregistrer la vue en tant que vue spatiale

Nous allons créer une vue spatiale sur la table Hôtels pour montrer tous les hôtels de Brest et de Quimper; pour ce faire, procédez comme suit :
1. Ouvrez le gestionnaire de base de données.
2. Dans l’arborescence, recherchez et sélectionnez la base de données BaseTest.sqlite.
3. Cliquez sur Base de données | Fenêtre SQL, ou appuyez sur F2 sur votre clavier, pour ouvrir la fenêtre SQL.
4. Entrez la requête suivante :
CREATE VIEW les_hotels_brest_quimper as
SELECT h.pk as ROWID,h.Nom,h.Code_Postal,h.Commune, h.geom from hotels as h
WHERE h.Commune in (‘Brest’,’Quimper’);

h ou n’importe  quelle lettre sert comme préfixe de table pour les noms de champ.

La clause SELECT défini les champs qui seront visibles dans la vue et la clause WHERE défini le critère de sélection des enregistrements qui seront visibles dans la vue.

Dans la requête CREATE VIEW,  deux champs sont obligatoires devant figurer dans l’instruction SELECT : le champ d’identificateur unique qui doit être renommé en ROWID et le champ de la géométrie. Vous devez renommer l’identifiant unique en ROWID ou vous ne pourrez pas enregistrer la vue comme vue spatiale.

5. Cliquez sur le bouton exécuter pour créer la vue.
Maintenant que la vue est créée, nous avons besoin de l’enregistrer comme une vue spatiale, en insérant une nouvelle ligne dans la table views_geometry_columns. Cette table lie la géométrie de la vue à la géométrie de la table dans laquelle s’opère la sélection.
6. Dans la fenêtre SQL, cliquez sur le bouton effacer pour effacer la zone de texte de la requête SQL.
7. Entrez la requête suivante :
INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
VALUES(‘les_hotels_brest_quimper’, ‘geom’, ‘rowid’, ‘hotels’, ‘geom’, 1);

Dans cette requête INSERT, six champs ont des valeurs insérées :

• view_name : celui-ci contient le nom de la vue que l’on souhaite inscrire comme vue spatiale.
• view_geometry : il contient le nom du champ géométrie de la vue.
• view_rowid : il contient le nom du champ rowid. Notez qu’il doit être rowid. Si le champ de rowid est nommé autrement, vous devrez recréer la vue avec un champ nommé rowid
• f_table_name : le nom de la table dont la vue est une sélection.
• f_geometry_column : le nom du champ géométrie dans la table dont la vue est une sélection
• read_only : dans ce champ, entrez 1 pour que la vue spatiale soit en lecture seule, ou bien entrez 0 pour que la vue spatiale soit en lecture/écriture. Notez que depuis la version 2.6.0 de QGIS, les vues en lecture/écriture ne peuvent pas être édités dans QGIS Desktop. Toutefois, les vues peuvent être modifiables dans certains plugins ou avec des requêtes SQL.

8. Cliquez sur le bouton exécuter pour créer la vue.
La vue est maintenant enregistrée comme vue spatiale et peut être ajoutée comme couche dans la fenêtre cartographique de QGis, comme n’importe quelle autre table spatiale SpatiaLite.

Supprimer une vue spatiale

Supprimer  une vue spatiale nécessite que vous supprimiez la table vue spatiale, mais aussi l’entrée la concernant dans la table view_geometry_columns.
Pour supprimer la table  vue spatiale, utilisez la commande SQL DROP VIEW. Par exemple, pour supprimer la vue de les_hotels_brest_quimper, vous aurez besoin d’exécuter la commande SQL suivante :

DROP VIEW les_hotels_brest_quimper

Avec la vue supprimée, la dernière étape consiste à supprimer l’entrée correspondante dans la table view_geometry_columns en utilisant la commande SQL DELETE.
Par exemple, pour supprimer l’écriture concernant les hôtels de Brest et Quimper, vous devrez exécuter la commande SQL suivante :

DELETE FROM views_geometry_columns
WHERE view_name = ‘les_hotes_brest_quimper’ ;

Si cet article vous a intéressé et que vous pensez qu'il pourrait bénéficier à d'autres personnes, n'hésitez pas à le partager sur vos réseaux sociaux en utilisant les boutons ci-dessous. Votre partage est apprécié !

Atilio Francois