Créer la base de données en SQLite avec SpatiaLite

Pourquoi SQLite et SpatiaLite ?

SQLite est un système de base de données relationnelle ultra-léger : toute la base est stockée dans un seul fichier (pratique) sur votre ordinateur. Pas besoin d'un serveur, pas de configuration complexe.

Petite info bonus : le format GeoPackage que nous avons créé dans QGIS est en réalité un fichier SQLite avec quelques conventions supplémentaires pour gérer les géométries. Donc si vous avez suivi le chapitre précédent, vous avez déjà une base SQLite sans le savoir .

SQLite seul ne gère pas les géométries spatiales. C'est là qu'intervient SpatiaLite : une extension qui greffe sur SQLite toutes les capacités de gestion de géométries (points, polygones, lignes, projections, calculs de distances et de surfaces...). Ensemble, SQLite et SpatiaLite forment un SIG de poche très capable.

Dans ce chapitre, nous allons créer la base complète avec SpatiaLite, en utilisant l'interface graphique DB Browser for SQLite.

Installation des outils

DB Browser for SQLite

Rendez-vous sur https://sqlitebrowser.org/dl/, rien de super compliqué normalement...

Une fois qu'on a SQLite Browser, on peut faire une base locale, mais il manque encore les capacités spatiales pour SQLite.

SpatiaLite

SpatiaLite se présente sous la forme d'un fichier d'extension (mod_spatialite) que DB Browser va charger à la demande. Son installation peut être un peu reloue (surtout sur Windows ), alors je préfère vous guider un peu.

• Windows : Si vous avez déjà QGIS installé, le fichier mod_spatialite.dll est déjà sur votre machine, dans le dossier bin de QGIS (ex: C:\Program Files\QGIS 3.x\bin\). Sinon, téléchargez l'archive correspondant à votre système sur https://www.gaia-gis.it/gaia-sins/ et décompressez-la dans un dossier facile à retrouver.
• Linux (Ubuntu/Debian) : sudo apt install libsqlite3-mod-spatialite Le fichier se trouve ensuite dans /usr/lib/x86_64-linux-gnu/mod_spatialite.so
• macOS : brew install libspatialite (nécessite Homebrew)

Création de la base de données

C'est bon, on se lance dans le bain !

1. Lancez DB Browser for SQLite
2. Cliquez sur "Nouvelle base de données" (ou Fichier → Nouvelle base de données)
3. Choisissez un dossier, nommez le fichier prospections_foret.db et cliquez sur Enregistrer
4. La fenêtre "Modifier la définition de la table" s'ouvre automatiquement, vous pouvez la fermer (dans tous les sens, je suis fatigué et à moitié sourd alors silence, même derrière vos écrans ). Nous allons d'abord activer SpatiaLite (oui, il y a une vraie raison à ne pas créer une table tout de suite).

N'oubliez pas de cliquer sur "Écrire les modifications" (Ctrl+S) régulièrement — DB Browser travaille en mémoire et ne sauvegarde pas automatiquement (et vous pouvez vous douter que les mauvaises surprises arrivent bien vite ).

Activer SpatiaLite

Cette étape est à faire une fois à l'ouverture de chaque session de travail sur la base. SpatiaLite n'est pas chargé automatiquement : il faut le charger manuellement dans DB Browser (oui, je sais, relou, mais c'est comme tout dans la vie, relou).

Charger l'extension

Menu "Outil" → "Charger l'extension..." → Naviguez jusqu'au fichier mod_spatialite :

• Windows : mod_spatialite.dll
• Linux : mod_spatialite.so
• macOS : mod_spatialite.dylib

Et "OK". Encore quelques vérifications...

Pour vérifier que tout fonctionne, tapez dans l'éditeur SQL et cliquez sur "Exécuter" (ou F5) :

SELECT spatialite_version();

Si une version s'affiche dans les résultats (ex: 5.0.1), c'est bon !

Initialiser les métadonnées spatiales

Cette opération crée les tables internes dont SpatiaLite a besoin (systèmes de projection, métadonnées des géométries...). Elle se fait une seule fois par base de données, pas à chaque ouverture :

SELECT InitSpatialMetaData(1);

Normalement, vous obtenez un retour de résultat 1. Si vous avez 0 et que vous ne pouvez plus sauvegarder votre fichier, retirez le 1 de la commande : SELECT InitSpatialMetaData();, ça devrait corriger le soucis.

Cette commande va créer plusieurs tables système et importer environ 5 000 systèmes de projection dans la table spatial_ref_sys. C'est tout à fait normal et attendu, ne les supprimez pas ! Si vous avez des doutes sur les projections, à quoi ça sert, tout ça, il va falloir réviser un peu les SIG, je vous laisse vous gérer.

Si rien n'est apparu, pas de soucis, c'est normal bande de :badger:, il faut aller sur l'onglet "Structure de la base de données" et cliquer sur "Rafraîchir".

Cliquez sur "Écrire les modifications" après cette étape.

Structure de la base : rappel

Voici ce que nous allons créer, dans l'ordre :

1. Les listes de valeurs (tables L_) : des petites tables de référence qui contiennent les options des menus déroulants
2. Les tables principales (tables T_) : les données de terrain
3. Les colonnes de géométrie : ajoutées via SpatiaLite après la création des tables

Création des listes de valeurs

Les listes de valeurs permettent de standardiser la saisie. Par exemple, plutôt que de taper "Céramique", "céramique" ou "ceramique" à chaque entrée, on stocke le mot une seule fois dans une liste et on y fait référence par un numéro. C'est le principe des menus déroulants (thesaurus, vocabulaires contrôlés, enfin toutes ces joyeusetés).

Pour créer chaque table, la procédure est toujours la même :

1. Dans l'onglet "Structure de la base de données", cliquez sur "Créer une table"
2. Donnez un nom à la table
3. Ajoutez les champs un par un avec "Ajouter"

4. Cliquez sur OK pour valider

5. L_auteurices

La liste des personnes qui participent aux prospections.

NB : En fonction des versions et traductions, des fois c'est écrit "AI" et d'autres, "IA", genre c'est traduit...

Vous avez vu ? Ça écrit la commande de génération en SQL en dessous, c'est sympa non ?

• L_priorites

Les niveaux de priorité pour les zones de prospection.

• L_natures_mob

Les types de mobilier archéologique.

• L_natures_str

Les types de structures archéologiques.

• L_contextes

Les contextes de découverte particuliers (chablis, taupinière...).

• L_etats

L'état de conservation des structures.

Création des tables principales

Important : Ne créez pas de colonne geom via l'interface graphique. Les colonnes de géométrie seront ajoutées ensuite par SpatiaLite via une commande SQL. Si vous les créez manuellement, SpatiaLite ne les reconnaîtra pas comme des colonnes spatiales (je sais, c'est aussi relou que le reste, je ne vais pas recommencer sur la vie et tout).

• T_zones

Les zones de prospection définies avant le terrain.

• T_mobilier

Les objets archéologiques découverts.

• T_structures

Les structures archéologiques.

• T_anomalies

Les anomalies générales non encore caractérisées.

• T_tracking

Les traces GPS des balades de prospection.

Ajouter les colonnes de géométrie avec SpatiaLite

Maintenant que les tables attributaires sont créées, on va leur ajouter leurs colonnes spatiales. Contrairement aux colonnes ordinaires, les colonnes de géométrie doivent passer par la fonction SpatiaLite AddGeometryColumn() pour être correctement enregistrées dans les métadonnées spatiales.

Allez dans l'onglet "Exécuter le SQL", assurez-vous que SpatiaLite est bien chargé (voir plus haut), puis exécutez ces commandes une par une :

-- Zones : polygones
SELECT AddGeometryColumn('T_zones', 'geom', 3949, 'POLYGON', 'XY');

-- Mobilier : points
SELECT AddGeometryColumn('T_mobilier', 'geom', 3949, 'POINT', 'XY');

-- Structures : polygones
SELECT AddGeometryColumn('T_structures', 'geom', 3949, 'POLYGON', 'XY');

-- Anomalies : polygones
SELECT AddGeometryColumn('T_anomalies', 'geom', 3949, 'POLYGON', 'XY');

-- Tracking : lignes
SELECT AddGeometryColumn('T_tracking', 'geom', 3949, 'LINESTRING', 'XY');

Le 3949 est le code EPSG de la projection CC49 (zone 8), adaptée à la région parisienne. Si vous travaillez dans une autre région, adaptez ce code (ex: 2154 pour RGF93/Lambert-93 à l'échelle nationale). Par rapport au GeoPackage pour lequel je vous avais dit que prendre le CC49 avec un code IGN ou EPSG, c'était pas forcément un gros problème (tant que vous restez avec l'un ou l'autre), ici, il faut choisir une projection par rapport à son code EPSG, alors on n'a pas le choix...

Après exécution, vous verrez que chaque table T_ possède maintenant une colonne geom, et qu'une nouvelle vue geometry_columns recense toutes ces colonnes — c'est ce que QGIS ou d'autres SIG liront pour comprendre vos géométries.

Comme vous l'aurez probablement compris (mais si, je suis sûr que oui), les colonnes de géométries sont définies par une projection (le code EPSG), un type de géométrie (point, ligne ou polygone) et des dimensions (X et Y au minimum et puis Z pour la hauteur et même parfois M comme champ scalaire).

Remplissage des listes de valeurs

1. Cliquez sur l'onglet "Parcourir les données"
2. Dans le menu déroulant "Table", sélectionnez la table à remplir
3. Cliquez sur "Insérer un nouvel enregistrement dans la table en cours" (icône sur un tableur)
4. Cliquez sur la cellule et saisissez la valeur
5. Répétez pour chaque entrée

Voici des exemples de valeurs à saisir :

• L_auteurices : le prénom ou les initiales de chaque membre de l'équipe (pour l'exemple, j'ai juste mis des lettres, par flemme et aussi parce que j'ai zéro imagination)

• L_priorites :

• L_natures_mob (quelques exemples au giga pif) :

• L_natures_str (quelques exemples) :

• L_contextes :

• L_etats :

Les liens entre tables (clés étrangères)

Plusieurs champs de nos tables principales sont des clés étrangères : ils contiennent un numéro qui correspond à l'identifiant (id) d'une entrée dans une autre table. Par exemple, le champ zone dans T_mobilier contient la clé primaire de la zone correspondante dans T_zones (Si le mobilier est dans la zone "B1" dont la clé primaire est "4", c'est "4" qui sera conservé dans la table, même si "B1" sera tout de même affiché).

On déclare une clé étrangère, c'est donc de ce côté du lien (le "n") qu'il faut aller. Par exemple, pour déclarer le lien entre zones et mobilier :

Pour les déclarer via l'interface de DB Browser :

1. Faites un clic droit sur la table T_mobilier dans "Structure de la base de données" → "Modifier la table"

2. Allez sur l'onglet "Clés étrangères" (vous pouvez aussi rajouter une clé étrangère depuis le premier onglet (celui des champs), mais ce ne sera pas lisible, donc pour la formation, on fait au plus simple)

3. Cliquez sur "Ajouter"

4. Dans la ligne qui vient d'apparaître: "Colonnes" = zone - "Nom" = Comme vous voulez, moi je vais mettre zones_a_mobilier - Références = T_zones , id (grâce aux menus déroulants)
5. Vous avez une jolie commande SQL qui s'affiche à côté _ dans la fenêtre en dessous, vous avez aussi une nouvelle commande CONSTRAINT qui vient d'apparaître !

Les clés étrangères sont des contraintes (comme considérer les gens comme étrangers d'ailleurs, c'est beaucoup d'énergie perdue pour faire de la ségrégation), c'est pour ça qu'on se limite à celles qui sont structurelles et qui pourraient menacer l'intégrité des données (je ne parle vraiment QUE de la base, ne me faites pas dire ce que je ne dis pas !). Donc pour les listes de valeurs, on ne s'embête pas.

Faire des requêtes sur vos données

On a fait une base en SQL, vous n'êtes pas obligés de coder, mais bon, c'est pour manipuler un peu !

Requêtes attributaires classiques

Cliquez sur l'onglet "Exécuter le SQL" pour accéder à l'éditeur de requêtes.

Voir tout le mobilier avec le nom de la zone et la nature :

SELECT
    m.identifiant,
    z.nom AS zone,
    n.nature_mob AS nature,
    m.date_creation,
    m.commentaires
FROM T_mobilier m
LEFT JOIN T_zones z ON m.zone = z.id
LEFT JOIN L_natures_mob n ON m.nature = n.id;

Là, qu'est-ce qu'on vient de faire ? Déjà, on commence par SELECT, c'est la commande qui nous permet d'indiquer ce qu'on veut voir (les champs qui seront affichés). Les AS, c'est pour afficher un alias, on met un nom de champ plus joli, lisible ou même explicite que celui de la base; par exemple, le champ "nom" de la table zone, on le comprend quand on est dans cette table, en revanche, au niveau de la base elle-même, ça pourrait être n'importe quoi. Ensuite, pour appeler un champ, on fait : nom_de_la_table.nom_du_champ, mais vous le voyez, je n'ai mis qu'une seule lettre, l'explication vient ensuite. Tous les champs que je veux afficher, viennent d'une table "T_mobilier" et c'est indiqué par la commande FROM

Pour résumer :

• j'affiche les champs indiqués par SELECT
• depuis la table choisie avec FROM
• Et j'applique les liens avec les JOIN
• J'indique la fin de ma commande avec ; (et je ne répèterai jamais assez de ne pas l'oublier (moi, c'est tout le temps et je m'autofatigue de ouf ))

Compter le mobilier par zone :

SELECT z.nom, COUNT(m.id) AS nb_mobilier
FROM T_zones z
LEFT JOIN T_mobilier m ON m.zone = z.id
GROUP BY z.nom;

Voir les zones non encore prospectées :

SELECT nom, commentaires
FROM T_zones
WHERE fait = 0 OR fait IS NULL;

Le WHERE, ça ajoute une condition. Ici, c'est si est à 0 (donc pas coché) ou null, c'est-à-dire que la valeur est manquante. En clair, ça donnera la liste des zones sur lesquelles on n'a pas strictement indiqué qu'elles avaient été prospectées.

Requêtes spatiales avec SpatiaLite

SpatiaLite ajoute des fonctions SQL pour interroger et calculer sur les géométries. Pensez à charger l'extension avant d'exécuter ces requêtes.

Voir les coordonnées du mobilier :

SELECT identifiant, X(geom) AS x, Y(geom) AS y
FROM T_mobilier
WHERE geom IS NOT NULL;

Là on met les coordonnées dans deux colonnes, de façon classique. Ça ne devrait pas vous surprendre si vous avez l'habitude d'insérer des données dans QGIS depuis des fichiers textes, genre CSV.

Calculer la superficie de chaque structure (en m²) :

SELECT identifiant, ROUND(ST_Area(geom), 1) AS superficie_m2
FROM T_structures
WHERE geom IS NOT NULL;

Trouver tout le mobilier contenu dans une zone donnée :

SELECT m.identifiant, z.nom AS zone
FROM T_mobilier m
JOIN T_zones z ON ST_Within(m.geom, z.geom) = 1
WHERE z.nom = 'A1';

Calculer la longueur totale des tracés de tracking (en m) :

SELECT date, gens, ROUND(ST_Length(geom), 0) AS longueur_m
FROM T_tracking
WHERE geom IS NOT NULL;

Le ROUND, c'est pour arrondir les valeurs. Ici, j'ai mis 0 parce que les chiffres après la virgule ça me saoule...

Sauvegarder une requête - les vues

Et si vous voulez sauvegarder votre requête, vous pouvez l'enregistrer comme une vue (on peut voir les vues enregistrées dans l'onglet "Structure de la base données"). Dans ce cas, on rajoute CREATE VIEW nom_de_la_vue AS devant la requête SQL. Par exemple :

CREATE VIEW mobilier_par_zone AS
SELECT m.identifiant, z.nom AS zone
FROM T_mobilier m
JOIN T_zones z ON ST_Within(m.geom, z.geom) = 1
WHERE z.nom = 'A1';

Cette requête est maintenant sauvegardée. Ça signifie donc qu'elle sera recalculée à chaque fois que vous la consulterez. C'est assez pratique pour les exports de données et c'est tout le temps à jour !

Insérer une géométrie manuellement (le WKT pour les curieux)

Les géométries viennent normalement de votre GPS ou de QGIS. Mais si vous souhaitez en saisir une manuellement (des fois, on s'ennuie), vous pouvez utiliser le format WKT (Well-Known Text), qui décrit les géométries en texte lisible (je sais, ça porte pas très bien son nom parce que c'est vraiment hyper lisible, mais c'est plus lisible qu'autre chose je suppose :nerd_face:) :

-- Ajouter un point de mobilier avec sa géométrie
INSERT INTO T_mobilier (identifiant, zone, date_creation, geom)
VALUES ('MOB-001', 1, datetime('now'), GeomFromText('POINT(648500 6861200)', 3949));

Pour ajouter des données dans SQLite, ça commence par INSERT INTO puis la table suivie des champs concernés entre parenthèses. La ligne suivante, ce sont les valeurs, VALUES, qui seront insérées dans les champs appelés juste avant et dans le même ordre que leur appel.

Là je vous entends (j'ai beau être sourd comme un pot, j'ai perçu votre cri d'étonnement): "Wow, bonhomme ! Pourquoi on a mis GeomFromText pour geom ? Et quand je clique sur les données dans DB Browser, ça ressemble pas à ce que j'ai mis ?!" Alors déjà, tu m'appelles pas "bonhomme", même si c'est cool d'avoir pensé à mettre un élément de code entre , un bon point pour ça. Enfin je vous réponds (oui, je vous vouvoie parce que je suis poli, moi) : Les données sont concrètement enregistrées sous forme de BLOB (Binary Large OBject), donc des données binaires qui sont interprétées par l'extension SpatiaLite. On utilise donc une commande spécifique à SpatiaLite pour convertir les coordonnées, ici depuis le WKT (d'où la commande "from text") vers le BLOB de géométrie.

Les coordonnées sont en mètres dans la projection CC49 (X, Y). Alors ça demande de connaître les coordonnées dans le bon système... Une fois de plus, c'est pour indiquer la manip, pas pour la réaliser au quotidien.

Exporter vos données

DB Browser for SQLite permet d'exporter vos tables en CSV. Allez dans l'onglet "Structure de la base de données", clic-droit sur un table et puis "Exportez les données au format CSV" (plutôt explicite comme manipulation).

Pour la colonne geom, si vous l'exportez comme ça, ça sera du binaire pas lisible. Je l'ai indiqué deux paragraphes au-dessus (peut-être un tout petit peu plus, si j'ai trop modifié le texte ), donc si vous n'avez pas retenu, bah j'y suis vraiment pour rien . Donc le plus simple, c'est de créer une vue et de l'exporter. Comme ça, on pourra mettre la commande de conversion directement dans le SQL et pouvoir exporter des coordonnées lisibles

Par exemple, si je veux extraire le point MOB-001 avec sa nature (pour pimenter un peu l'exercice ) et ses coordonnées lisibles :

SELECT m.identifiant AS identifiant, n.nature_mob AS nature, AsText(m.geom) AS cordonnees_wkt
FROM T_mobilier m
LEFT JOIN L_natures_mob n ON m.nature = n.id
WHERE m.identifiant = 'MOB-001';

Et la magie opère ! Vous pouvez exporter les vue de la même manière que les tables.

Et puis ensuite, pour les formulaires, je laisse ça à QGIS dans le cadre de ce support.

Ressources en plus

• Documentation SpatiaLite : https://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html — la liste complète des fonctions spatiales disponibles
• Documentation SQLite : https://www.sqlite.org/docs.html