2.3 Interroger une BDD dans QGIS

2.3.1 Le langage SQL pour manipuler et interroger les BDD

SQL (Structured Query Langage) est un langage de BDD relationnelle. Il permet :

• la création de la base et des tables,
• l'ajout d'enregistrements sous forme de lignes,
• l'interrogation de la base,
• la mise à jour,
• le changement de structure de la table : ajout, suppression de colonnes,
• la gestion de droits d'utilisateurs de la base.

Il est aussi possible en SQL de faire des programmes procéduraux avec des itérations et des conditions (notion de procédure stockée).

Souvent, des instructions en langage SQL sont incorporées dans le code source d'un programme écrit dans un autre langage, c'est le cas de nombreux sites web (couplage HTML/CSS/PHP/SQL). Parmi eux, certains sites intégrant du webmapping (cartographie interactive) peuvent se connecter et interroger en dynamique une BDD spatiale, à l'aide du SQL incorporé dans son code source.

Enfin, tous les logiciels SIG offrent la possibilité d'exécuter des instructions SQL pour interroger/traiter les données spatiales de différents formats.

Les instructions SQL s'écrivent d'une manière qui ressemble à celle de phrases ordinaires en anglais. Cette ressemblance voulue vise à faciliter l'apprentissage et la lecture (source Wikipedia).

Le plus grand défi pour l’utilisation de PostgreSQL/PostGIS (ou tout autre SGBD basé sur SQL) est ainsi d'apprendre les rudiments de syntaxe de requêtes SQL pour manipuler, interroger et transformer la BDD. Il faut procéder par étape en complexifiant sa requête au fur et à mesure :)

Les objectifs pédagogiques sont triples :

1. savoir exécuter une requête et enregistrer son résultat dans une couche ou vue, puis la visualiser dans QGIS,
2. comprendre et savoir écrire une requête SQL : autrement dit, traduire en français une requête en SQL et vice versa,
3. savoir reprendre et adapter le contenu d'une requête-exemple pour répondre à votre question/besoin.

2.3.2 Utiliser la fenêtre SQL de DB Manager pour faciliter la création et l'exécution des requêtes

Tout comme pour l'éditeur SQL de DBeaver, la fenêtre SQL de DB Manager est dotée d'un assistant qui aide à l'écriture en SQL. Il offre une auto-complétion des termes et une coloration syntaxique : en violet les commandes SQL, en bleu les noms des schémas, tables et champs.

Chaque requête s'exécute à l'aide du bouton Exécuter. Il est possible de Charger un fichier de requête et d'Enregistrer dans un fichier une requête écrite dans l'éditeur.

En sortie, il est indiqué un temps d'exécution et un tableau de résultats (pour les requêtes de sélection notamment) que l'on pourra de manière non exclusive :

• exporter dans un format tabulaire ou texte délimité (csv) par copier-coller,
• charger sous la forme d'une couche virtuelle dans QGIS (que l'on pourra exporter ensuite pour la sauvegarder) en cochant l'option Charger en tant que nouvelle couche (seulement si toutes les conditions sont remplies pour constituer une couche spatiale),
• créer une vue dans la base grâce au bouton Créer une vue puis la visualiser dans QGIS (seulement si toutes les conditions sont remplies pour constituer une couche spatiale).

En savoir plus sur l'exécution de requêtes SQL avec DB Manager dans la doc QGIS...

Rem : toutes les requêtes SQL peuvent s'exécuter dans DB Manager sur des tables/vues PostGIS (comme présenté ci-après), mais aussi sur tout type de couche/table de données géographiques (GeoPackage, Oracle Spatial, SpatiaLite et couches du projet dites "couches virtuelles" de différents formats shapefiles, geojson, etc.).

2.3.3 Requêtes SELECT de base

• Sélectionner tous (*) les champs d'une table :

SELECT 
    * 
FROM 
    occ.chef_lieu ; 
-- renvoie un tableau de résultats identique à la table `chef_lieu` du schéma `occ`

• Requête avec filtre sur les champs dans SELECT :

SELECT 
    insee_com, 
    nom_chf 
FROM 
    occ.chef_lieu ; 
-- renvoie un tableau de résultats avec autant de lignes que la table mais seulement 2 colonnes

• Requête avec conditions WHERE pour filtrer les données et ORDER BY pour les ordonner (ASC pour ordre croissant, DESC pour ordre décroissant)

-- Sélection des communes de l'Hérault (34) de plus de 40 000 habitants
SELECT 
    nom_com, 
    population -- filtre sur les colonnes (ici 2 champs)
FROM 
    occ.commune 
WHERE 
    insee_dep = '34' AND -- ici 34 est entre guillemets car "insee_dep" est de type texte
    population > 40000 -- filtre sur les lignes (ici 2 critères)
ORDER BY 
    population DESC ; -- résultat : Montpellier (281613), Béziers (76493), Sète (43609)

La clause WHERE est définie par une condition qui s’exprime à l’aide d’opérateurs de comparaison et logiques, dont voici les principaux :

Opérateurs de comparaison	Opérateurs logiques
A = valeurA1 ou A <> valeurA1 A > valeurA1 ou A < valeurA1 A IN (valeurA1, valeurA2, …) A LIKE 'valA%' (tout ce qui commence par valA) A IS NULL (son contraire : A IS NOT NULL)	OR pour séparer 2 conditions dont au moins 1 doit être vérifiée AND pour séparer 2 conditions qui doivent être vérifiées simultanément NOT pour inverser la condition

---

-- Exercice 1 utilisant la table `occ.chef_lieu`
SELECT 
    insee_com, 
    nom_chf 
FROM 
    occ.chef_lieu 
WHERE
    statut LIKE '%réfecture%' and
    insee_com LIKE '11%' or insee_com LIKE '34%' -- "or" pour sélectionner les 2 départements
ORDER BY nom_chf ; -- résultat : Béziers, Carcassonne, Limoux, Lodève, Montpellier, Narbonne

2.3.4 Requêtes de création d'une table/vue spatialisée

Les commandes CREATE OR REPLACE TABLE et CREATE OR REPLACE VIEW permettent respectivement de créer une table et une vue.

Pour rappel : la vue est la représentation "virtuelle" d'une requête de sélection sous la forme d'une table. Cette vue s'actualise automatiquement à chaque UPDATE, INSERT ou DELETE dans la ou les tables requêtées. Attention, on ne peut pas directement modifier les données à partir d'une vue !

Les vues peuvent être utiles si on souhaite que les utilisateurs de la base n'aient pas accès à toutes les données d'une table ou pour rassembler des données provenant de plusieurs tables dans une seule entité, pour avoir des requêtes plus simples, etc.

Si parmi les champs sélectionnés dans la requête il y a un champ de type spatial (geometry, geography, raster, topology), alors la table ou la vue est normalement visualisable dans le panneau de carte de QGIS.

Voici les requêtes-type optimisées pour une visualisation dans QGIS, dans le cas où vous n'utiliseriez pas l'option Créer une vue de la fenêtre SQL de DB Manager :

CREATE OR REPLACE TABLE nom_schema.nom_table (
     id int4, -- ou row_number() OVER () as id, pour générer automatiquement un numéro de ligne
     geom GEOMETRY(Type,SRID) -- complétez Type et SRID (ex: Point,4326)
    -- ajoutez à la suite les champs, séparés par une virgule
);

-- Ajout clé primaire 
ALTER TABLE nom_schema.nom_table 
  ADD CONSTRAINT nom_table_pkey PRIMARY KEY (id);

-- Création index spatial
CREATE INDEX nom_table_geom_idx
  ON nom_schema.nom_table USING gist(geom);

CREATE OR REPLACE VIEW nom_schema.nom_vue AS 
SELECT 
    id int4, -- ou row_number() OVER () as id, pour générer un numéro de ligne
    geom::geometry(Type, SRID) -- ou ST_MaFonction(nom_table.geom, autres_parametres)::geometry(Type, SRID)
FROM
    nom_schema.nom_table ;

/* Cette requête crée la vue spatialisée représentant les communes d'Occitanie 
avec des données calculées issues de l'INSEE (densité de pop) et un calcul de superficie
*/
CREATE OR REPLACE VIEW occ.v_commune_insee AS
SELECT 
    row_number() OVER () as id,
    c.id as commune_id, c.geom::geometry(MultiPolygon,2154), c.insee_com,
    libgeo, p16_pop, p11_pop,
    (ST_Area(c.geom) / 1000000)::integer AS superf_km2,
    (p16_pop / (ST_Area(c.geom) / 1000000))::integer AS p16_densite
FROM 
    occ.commune c LEFT JOIN occ.insee i ON c.insee_com = i.codgeo
ORDER BY 
    superf_km2 ;

2.3.5 Principales fonctions SQL utiles pour traiter les chaînes de caractères

Les types character varying(n), varchar(n) sont de longueur variable avec une limite n. Les types character(n) et (char(n) sont à longueur fixe n, complété par des espaces. Enfin le type text est à longueur variable illimitée (donc à éviter autant que possible !). En savoir plus....

Il existe de nombreuses fonctions permettant de manipuler ces types de données dont voici les principales.

• UPPER, LOWER ou INITCAP : convertit respectivement en majuscule, en minuscule ou la première lettre en capitale

SELECT 
    upper(nom_chf) AS nom_chf_maj -- AS permet de donner un nom au nouveau champ calculé
FROM 
    occ.chef_lieu ;

• LENGTH : renvoie la longueur de la chaîne (nombre de caractères + espaces)

SELECT 
    length(nom_com) AS long_mot
FROM 
    occ.commune 
WHERE 
    nom_com = 'Saint-Affrique-les-Montagnes' ; -- Résultat : 28

• || (ou CONCAT ou CONCAT_WS) : concatène 2 champs (rem : CONCAT_WS permet de définir un séparateur en premier argument)

SELECT 
    nom_chf || ' statut : ' || statut AS nom_statut
FROM 
    occ.chef_lieu 
LIMIT 3 ; 

SELECT concat('abcde', 2, NULL, 22) ; -- renvoie abcde222, alors que
SELECT concat_ws(';', 'abcde', 2, NULL, 22) ; -- renvoie abcde;2;22

• CAST('string' AS INTEGER) ou CAST('string' AS DOUBLE) ou CAST('string' AS DATE) ou :: : convertit la chaîne de caractères en nombre, date, etc.

La fonction CAST() dans le langage SQL est une fonction de transtypage qui permet de convertir une donnée d'un type en un autre.

-- Syntaxe 1 utilisant CAST
SELECT
    CAST(insee_com AS INTEGER)
FROM 
    occ.commune ;

-- Syntaxe 2 équivalente en utilisant les "::"
SELECT
    insee_com::integer
FROM 
    occ.commune ;

Voir toutes les fonctions sur les chaînes de caractères...

2.3.6 Principales fonctions SQL utiles pour traiter les types numériques

• Opérateurs classiques + - * /

• Fonctions mathématiques

Parmi ces nombreuses fonctions (cf. doc PostgreSQL) on peut citer la fonction très pratique round(numeric) ou round(numeric, integer) qui arrondit les valeurs en résultat. Par exemple round(42.4382, 2) → 42.44.

• Fonctions d’agrégation SUM(), COUNT(), MAX(), MIN(), AVG()

L’agrégation permet de regrouper les enregistrements (lignes) de la table en sortie selon des critères et d’obtenir des informations statistiques sur ces regroupements. Lorsqu'on souhaite obtenir plusieurs valeurs en résultat par catégorie (regroupement), il faut dans ce cas utiliser la clause GROUP BY.

/* Exemple où une seule valeur est attendue en résultat : 
calcul de la population totale de l'Occitanie
*/
SELECT 
    sum(population) AS pop_tot 
FROM 
    occ.commune ; -- renvoie 5808435

/* Exemple où plusieurs valeurs sont attendues en résultat : 
calcul de la population totale pour chacun des 13 départements 
avec un classement par ordre décroissant (regroupement par catégorie, ici par département) */
SELECT 
    insee_dep, 
    sum(population) AS pop_tot_dep 
FROM 
    occ.commune 
GROUP BY insee_dep 
ORDER BY pop_tot_dep DESC ; -- renvoie un tableau à 13 lignes

-- Résultat renvoyant un résultat numérique simple
SELECT 
    round(avg(population)) as result
FROM 
    occ.commune ; -- renvoie 1304

2.3.7 Jointures attributaires

Pour traduire en SQL une jointure entre deux tables qui partagent des attributs en commun, la notation la plus simple est de lister les 2 tables dans la clause FROM et de préciser la condition de jointure dans la condition WHERE. La condition de jointure détermine les lignes des deux tables sources considérées comme correspondantes.

Il existe une autre notation SQL plus précise où le type de jointure est dans le FROM en utilisant soit le paramètre d'égalité entre les tables INNER JOIN...ON, soit le paramètre de jointure externe LEFT JOIN...ON ou RIGTH JOIN...ON (LEFT JOIN : pour chaque ligne de T1 qui ne satisfait pas la condition de jointure avec les lignes de T2, une ligne jointe est ajoutée avec des valeurs NULL dans les colonnes de T2, et inversement pour RIGHT JOIN). Souvent on utilise un alias de table pour en faciliter l'écriture. En savoir plus....

SELECT 
    commune.nom_com, 
    departement.nom_dep 
FROM 
    occ.commune, 
    occ.departement
WHERE 
    commune.insee_dep = departement.insee_dep ; -- condition de jointure 
/*  Attention, vérifiez que les 2 champs soient du même type dans la condition de jointure, 
ici varchar(3), sinon il est parfois utile de recourir au
transtypage d'un champ)*/

-- Syntaxe équivalente en utilisant "INNER JOIN...ON" dans le FROM
SELECT 
    c.nom_com, 
    d.nom_dep 
FROM 
    occ.commune c -- alias "c" pour la table `commune`
    INNER JOIN occ.departement d -- alias "d" pour la table `departement`
    ON c.insee_dep = d.insee_dep ; -- "ON" définit la condition de jointure

-- Requête de sélection attributaire dans le cadre d'une jointure
CREATE OR REPLACE VIEW occ.v_gare_corrig as
SELECT
    g.id,
    g.code_uic,
    g.libelle,
    g.fret,
    g.voyageurs,
    g.commune,
    g.departement,
    r.gare_alias_libelle_noncontraint,
    r.departement_libellemin,
    r.departement_numero,
    r.commune_libellemin,
    r.commune_code,
    r.geom
FROM
    occ.gare g left join
    occ.referentiel_gares_voyageurs r on
    g.code_uic::integer = r.uic_code::integer -- transtypage nécessaire !

-- Requête de mise à jour d'une table à l'aide de UPDATE nom_schema.nom_table SET ...

UPDATE occ.gare
SET departement = UPPER(v.departement_libellemin) -- "UPPER" pour mettre en majuscule
FROM 
    occ.v_gare_corrig v
WHERE 
    gare.id = v.id and
    gare.departement is null -- mise à jour uniquement pour les lignes où pas de valeur

Nous quittons les gares pour un nouvel exercice permettant d'illustrer la jointure entre une table spatiale et une table non spatiale afin de faire des calculs d'évolution de la population héraultaise.

2.3.8 Principales fonctions SQL spatiales utiles pour traiter les types géométriques

La quasi totalité des fonctions SQL spatiales dans PostGIS sont préfixées par le standard spatial type "ST_" (ex : ST_Area, ST_Intersection). PostGIS les renomme au fur-et-à-mesure selon la convention de nommage "SQL-MM-centric convention" du standard SQL/MM (source : postgis.net).

1. Information sur les géométries

• ST_GeometryType(geometry) retourne le type de la géométrie,

• ST_NDims(geometry) retourne le nombre de dimensions d’une géométrie,

• ST_SRID(geometry) retourne l’identifiant de référence spatiale de la géométrie (code EPSG),

• ST_X(geometry) retourne la composante X (pour les points uniquement),

• ST_Y(geometry) retourne la composante Y (pour les points uniquement).

SELECT 
    ST_GeometryType(geom), 
    ST_NDims(geom),
    ST_SRID(geom)
FROM 
    occ.ligne_rfn ;

2. Systèmes de projection

• ST_AsText retourne la représentation au format Well-Known Text (WKT) sans la métadonnée SRID.
• ST_SetSRID(geometry, srid) met à jour le SRID (= SCR = code EPSG du système de référence spatial) d’une géométrie. Retourne la même géométrie. Cela ne modifie pas les coordonnées de la géométrie. Cette fonction est utile pour reconditionner les géométries sans SRID.
• ST_Transform(geometry, srid) retourne une nouvelle géométrie après avoir re-projeté les données dans le système correspondant au SRID passé en paramètre.

-- Pour visualiser le contenu de manière lisible du champ "geom" d’une table
SELECT 
    libelle, -- ex : Sète
    commune, -- ex : SETE
    departement, -- ex : HERAULT
    st_astext(geom) -- ex : POINT(756447.285499974 6257324.80730262)
FROM 
    occ.gare ;

-- Pour changer le SRID (SCR) d’un champ geometry déjà existant et dont le SRID est erroné
-- On modifie la structure de la table, donc on utilise obligatoirement "ALTER TABLE"
ALTER TABLE ma_table 
ALTER COLUMN geom TYPE geometry(POINT,4326) 
-- on indique quel champ on modifie et son SRID correct
USING ST_SetSRID(geom,4326); 
-- il faut obligatoirement rajouter la fonction ST_SetSRID(geom,SRID correct)

3. Mesures

• ST_Length(geometry) retourne la longueur de la géométrie dans l’unité du système de référence spatiale.

/* Calcul de la longueur en km de la ligne de train entre Bordeaux et Montpellier 
(code_ligne = '640000', attention c'est du texte)
Attention, ici le calcul est possible car le SCR est en mètre */
SELECT 
    code_ligne,
    round(st_length(geom)/1000) AS long_ligne_bdx_montp_km 
FROM 
    occ.ligne_rfn
WHERE 
    code_ligne = '640000' ; -- Résultat : 640000 | 475.0
-- rem : on utilise "round" pour arrondir la valeur

• ST_Area(geometry) retourne l’aire d’une géométrie dans l’unité du système de référence spatiale.

/* Calcul de la superficie en km² des départements de l'Occitanie 
Attention, ici le calcul est possible car le SCR est en mètre */
SELECT 
    nom_dep,
    round(st_area(geom)/1000000) AS superficie_km2  
FROM 
    occ.departement
WHERE
    insee_reg = '76'
ORDER BY superficie_km2 DESC ; 
-- renvoie les 13 départements et leur superficie respective

• ST_Perimeter(geometry) retourne le périmètre de la géométrie dans l’unité du système de référence spatiale.

• ST_NumPoints(linestring) retourne le nombre de sommets dans une ligne.

• ST_NumRings(polygon) retourne le nombre de contours dans un polygone.

4. Conversions

• ST_AsText(geometry) retourne une géométrie au format WKT.
• ST_AsGML(geometry) retourne la géométrie au format standard OGC GML.
• ST_AsGeoJSON(geometry) retourne une géométrie au format “standard” GeoJSON.

5. Relations

• ST_Distance(geometry, geometry) retourne la distance entre deux géométries dans l’unité du système de référence spatiale.

• ST_Within(geometry A, geometry B) retourne TRUE si A est inclus totalement dans B, sinon FALSE.

• ST_Intersects(geometry, geometry) retourne TRUE si les géométries s'intersectent, sinon FALSE.

• ST_Contains(geometry A, geometry B) retourne TRUE si A contient totalement B, sinon FALSE.

• ST_Crosses(geometry, geometry) retourne TRUE si une ligne ou les contours d’un polygone croisent une ligne ou un contour de polygone, sinon FALSE.

2.3.9 Jointures spatiales

Comme pour les jointures attributaires, les deux tables doivent être citées dans le FROM. La condition de jointure doit être placée dans la condition WHERE (dans la notation simple).

Plusieurs fonctions permettent de comparer deux géométries (deux tables PostGIS ici) :

• ST_Contains(geometry A, geometry B) retourne TRUE si la géométrie A contient la géométrie B (ordre important entre A et B, inverse de ST_Within).
• ST_Crosses(geometry A, geometry B) retourne TRUE si la géométrie A croise la géométrie B.
• ST_Disjoint(geometry A , geometry B) retourne TRUE si les géométries ne s’intersectent pas.
• ST_Intersects(geometry A, geometry B) retourne TRUE si A intersecte B.
• ST_Overlaps(geometry A, geometry B) retourne TRUE si A et B ont un espace en commun, mais ne sont pas complètement incluses l’un dans l’autre.
• ST_Touches(geometry A, geometry B) retourne TRUE si le contour extérieur de A touche B.
• ST_Within(geometry A, geometry B) retourne TRUE si A est inclus totalement dans B (ordre important entre A et B; inverse de ST_Contains).
• Voici la seule fonction qui ne renvoie pas un booléen mais une distance : ST_Distance(geometry A, geometry B) retourne la distance minimum entre deux géométries.

Il faut donc utiliser l'une de ces fonctions ci-dessus (sauf ST_Distance) dans la condition de jointure présente dans le WHERE de la requête.

En savoir plus sur les fonctions de comparaison spatiale...

/* Liste des chefs-lieux de la région Occitanie : 
Comme il n'y a pas de champ qui permet une sélection par région, 
nous allons utiliser une requête spatiale pour sélectionner les points des 
chefs-lieux inclus dans les départements de la région Occitanie.
*/

CREATE OR REPLACE VIEW occ.v_chef_lieu_occ AS
SELECT
    row_number() OVER () as id2, -- génère un serial/integer (numéro séquentiel)
    chef_lieu.* -- '*' est utilisé pour sélectionner toutes les colonnes de la table
FROM
    occ.chef_lieu,
    occ.departement
WHERE
    insee_reg = '76' AND
    st_intersects(chef_lieu.geom , departement.geom) ; 
-- la fonction st_intersects(geom1, geom2) renvoie TRUE par défaut

2.3.10 Fonctions de construction de géométries prenant des géométries en entrée et retournant de nouvelles formes

• ST_Union() renvoie un objet géométrique qui représente l’ensemble d’union des objets géométriques désignés.

• ST_Union([geometry]) : une version agrégée qui prend un ensemble de géométries et retourne une géométrie contenant l’ensemble des géométries rassemblées. Il faut utiliser la commande SQL « GROUP BY » pour créer un ensemble rassemblant des sous-ensembles de géométries basiques.

/* Créez une vue `occ.v_union_dept` représentant le contour de la régionOccitanie 
via l’union des départements
 */

CREATE OR REPLACE VIEW occ.v_union_dept AS
SELECT
    row_number() OVER () as id,
    insee_reg,
    st_union(geom)::geometry(MultiPolygon,2154) AS geom
FROM 
    occ.departement
WHERE   
    insee_reg = '76'
GROUP BY 
    insee_reg ;

• ST_Union(geometryA,geometryB) : version avec 2 paramètres qui prend les 2 géométries et retourne l’union des deux. [non démontré ici]

• ST_Buffer(geometry, distance) retourne une géométrie qui représente tous les points dont la distance depuis cette géométrie est inférieure ou égale à la distance utilisée. Les calculs se font dans le système de référence spatial de cette géométrie.

-- Créez une zone tampon (buffer) de 50 km autour des préfectures de région

SELECT 
    st_buffer(geom,50000)
FROM 
    occ.chef_lieu ; 
-- Attention cette requête est valide mais ne crée pas de nouvelle couche de zone tampon

-- Exemple de vue spatialisant la zone tampon de 10 km autour de Montpellier

CREATE VIEW occ.v_buffer_montpellier AS 
SELECT 
    row_number() OVER () as id, 
    ST_Buffer(geom, 10000)::geometry(POLYGON, 2154) as geom
FROM occ.chef_lieu
WHERE nom_chf = 'Montpellier'

/* Créez une zone tampon de 5 km autour de la ligne de chemin de fer entre Sète et Tarascon. 
(code_ligne 810000)
Créez une vue `occ.v_buffer_sete_tarascon`. 
*/

CREATE OR REPLACE VIEW occ.v_buffer_sete_tarascon AS
SELECT 
    row_number() OVER () as id, -- identifiant unique obligatoire
    code_ligne,
    st_buffer(geom,5000)::geometry(Polygon,2154) as geom 
    -- pour définir une géométrie polygonale de SCR 2154
FROM 
    occ.ligne_rfn 
WHERE
    code_ligne = '810000' ;

• ST_Intersection(geometry A, geometry B) retourne une géométrie qui représente la portion commune des géométries A et B.

SELECT 
    ST_Intersection(a.geom, b.geom) as intersect_ab 
FROM 
    a INNER JOIN b ON ST_Intersects(a.geom,b.geom)
WHERE 
    ST_isvalid(a.geom)='t' AND ST_isvalid(b.geom)='t';

Remarquez ici l'utilisation de la fonction ST_isvalid(geometry) qui permet de vérifier la validité de l'objet spatial en renvoyant t (true) ou f (false). La validité est déterminante pour les polygones qui définissent des aires et requièrent une bonne structuration. Les lignes et les points étant des objets simples, ils ne peuvent pas être invalides. Dans la requête ci-dessus cela filtre uniquement les géométries valides des 2 tables (sous-entendu true). En savoir plus sur la validité des objets spatiaux...

Le schéma ci-dessous montre les différents cas possibles d'intersections (source IBM) :

/* Créez et visualisez une vue spatialisée `occ.v_buffer_commune` 
qui va découper les polygones de communes par la zone tampon.  
*/

CREATE OR REPLACE VIEW occ.v_buffer_commune AS
SELECT 
    row_number() OVER () as id,
    c.insee_com,
    c.nom_com,
    st_multi(st_intersection(b.geom, c.geom))::geometry(MultiPolygon,2154) as geom 
-- st_multi transforme le type *Polygon* en *MultiPolygon*
-- st_intersection réalise l'intersection entre les deux géométries
FROM 
    occ.v_buffer_sete_tarascon as b,
    occ.commune as c
WHERE
    st_intersects(b.geom, c.geom) AND
    ST_isvalid(b.geom)='t' AND 
    ST_isvalid(c.geom)='t'; 
-- st_intersects réalise la condition de jointure spatiale (= TRUE)

En savoir plus sur les fonctions de construction de géométries...

2.3.11 À RETENIR

• Le langage SQL (Structured Query Language) sert à exploiter des BDD relationnelles de tout SGBD basé sur SQL.
• Les principales commandes sont SELECT, CONNECT, CREATE, INSERT, UPDATE et DELETE.
• Dans le SELECT on peut filtrer les champs d'une ou plusieurs tables et on peut en calculer d'autres (somme, compte, concaténation, aire, etc.).
• Dans le FROM on indique la ou les tables/vues requêtées.
• Dans le WHERE, on peut filtrer les lignes à l'aide de condition(s) souvent sous la forme "champ = valeur AND champ in(valeur1, valeur2)".
• La jointure attributaire/spatiale entre tables s'écrit dans le WHERE ou le FROM,
• Le résultat d'une requête SELECT est sous la forme d'un tableau exportable. Celui d'une requête CREATE TABLE/VIEW créé une table/vue, potentiellement visible dans le panneau de carte de QGIS.