Solutions des exercices sur les bases de données spatiales

Exercice 1

1) Exécutez la requête ci-dessous. Que signifie-t-elle ?

Réponse : cette requête signifie "Sélectionnez le code INSEE et le nom des préfectures de région, préfectures et sous-préfectures des départements de l'Aude (11) et de l'Hérault (34). Ordonnez le tableau de résultat par ordre alphabétique croissant des noms de villes."

2) Les tables chef_lieu et commune permettent d'obtenir le même résultat. A l'aide de l'exemple ci-dessous, trouvez la requête qui permet d'obtenir le même résultat, mais cette fois à partir de la table commune.
-- Solution en utilisant la table `occ.commune`
SELECT
    insee_com,
    nom_com
FROM
    occ.commune
WHERE
    statut LIKE '%réfecture%' and
    insee_dep in ('11', '34')
ORDER BY nom_com ; -- résultat : Béziers, Carcassonne, Limoux, Lodève, Montpellier, 

Exercice 2

Trouvez la requête permettant d'obtenir la phrase en résultat : "La préfecture de région de l'Occitanie est TOULOUSE." en utilisant les champs "statut" et "nom_com" uniquement.
SELECT 
    'La ' || lower(statut) || ' de l''Occitanie est ' || upper(nom_chf) || '.' AS resultat
FROM 
    occ.chef_lieu 
WHERE 
    nom_chf = 'Toulouse' ;

-- solution alternative
SELECT 
    concat('La ', lower(statut), ' de l''Occitanie est ', upper(nom_chf), '.') AS resultat
FROM 
    occ.chef_lieu 
WHERE 
    nom_chf = 'Toulouse' ;

Exercice 3

Que signifie la requête SQL ci-dessous ?

Réponse : cette requête signifie "Quel est le nombre moyen d'habitants des communes en Occitanie ? Arrondir le résultat."

-- Résultat renvoyant un résultat numérique simple sans mentionner le nom de la commune
SELECT 
    round(avg(population)) as result -- "avg" calcule la moyenne
FROM 
    occ.commune ; -- renvoie 1304

Exercice 4a

Combien y a-t-il de gares par département de France métropolitaine ? Classez le résultat par ordre antéchronologique des départements. Que remarquez-vous ?
SELECT 
    departement,
    count(id) as nb_gares
FROM 
    occ.gare 
GROUP BY departement
ORDER BY departement ; 

Il y a 133 gares pour lesquelles aucun département n'est attribué (ni aucune géolocalisation). Attention donc à l'interprétation des résultats ! Il faut toujours étudier attentivement les données source.

Exercice 4b

Observez la table attributaire de la vue occ.v_gare_corrig. Que remarquez-vous ?

La vue contient les 4282 gares recensées dans la table occ.gare du fichier source originel.

Les données des 2975 gares de voyageurs issues du fichier source referentiel-gares-voyageurs.geojson ont été correctement alignées grâce à la condition de jointure. Des données des champs "departement_libellemin" et "commune_libellemin" vont pouvoir servir pour mettre à jour la table occ.gare.

Exercice 4c

Réalisez une requête similaire pour mettre à jour la commune dans la table occ.gare.
-- Requête de mise à jour d'une table à l'aide de UPDATE nom_schema.nom_table SET ...
UPDATE occ.gare
SET commune = UPPER(v.commune_libellemin) -- "UPPER" pour mettre en majuscule
FROM 
    occ.v_gare_corrig v
WHERE 
    gare.id = v.id and
    gare.commune is null -- mise à jour seulement dans le cas où le champ "commune" est NULL

Exercice 4d

Réalisez une requête similaire pour mettre à jour le champ "geom" de la table occ.gare.
-- Requête de mise à jour d'une table à l'aide de UPDATE nom_schema.nom_table SET ...
UPDATE occ.gare 
SET geom = v.geom 
FROM 
    occ.v_gare_corrig v
WHERE 
    gare.id = v.id and
    gare.geom is null -- mise à jour seulement dans le cas où le champ "geom" est NULL

Exercice 4e

Ré-exécutez la requête de l'exercice 4a et observez la différence : que constatez-vous ?

Il n'y a plus que 14 gares sans données de commune/département et de géolocalisation :)

Par contre, on peut observer un problème d'accent qui génère des départements en doublon :( -> un traitement par SQL est possible mais ne sera pas vu dans cette formation.

Exercice 5

Calculez l'évolution de la population (en nombre d'habitants) entre 2011 (p11_pop) et 2016 (p16_pop) pour les préfectures et sous-préfectures de l'Hérault (34).
SELECT 
    chef_lieu.nom_chf,
    (insee.p16_pop - insee.p11_pop) AS evol_pop
-- si les champs p16_pop et p11_pop sont en texte, il faut recourrir au transtypage ''::numeric'
-- (insee.p16_pop::numeric - insee.p11_pop::numeric) AS evol_pop
FROM
    occ.chef_lieu,
    occ.insee
WHERE
    chef_lieu.insee_com = insee.codgeo AND
    chef_lieu.statut LIKE '%réfecture%' AND
    insee.dep='34'
ORDER BY evol_pop ;

result exo5

Exercice 6

Calculez l'aire (km²) de la région Occitanie ainsi que le nombre de départements à l'aide de la table departement. Attention ici vous allez devoir faire un regroupement et par conséquent utiliser la clause GROUP BY. En résultat vous devez obtenir insee_reg | aire_km²_occ | nb_dept.
SELECT 
    insee_reg,
    round(sum(st_area(geom)/1000000)) AS aire_km2_occ,
    count(insee_dep) AS nb_dep
FROM 
    occ.departement
WHERE
    insee_reg = '76'
GROUP BY insee_reg ; -- renvoie 76 | 73405.0 | 13

Exercice 7

Générez une vue sélectionnant les tronçons de chemin de fer exploités intersectant la région Occitanie.
-- Liste des segments de lignes de chemin de fer intersectant la région Occitanie

CREATE OR REPLACE VIEW occ.v_ligne_rfn_occ AS
SELECT 
    row_number() OVER () as id2,
    ligne_rfn.*
FROM 
    occ.ligne_rfn, 
    occ.departement
WHERE 
    departement.insee_reg = '76' AND
    libelle = 'Exploitée' AND
    st_intersects(ligne_rfn.geom , departement.geom) ; 
-- la fonction st_intersects(geom1, geom2) renvoie TRUE par défaut

v_ligne_rfn_occ

Remarque : certains tronçons dépassent les contours de la région.

Exercice 8

Générez une vue occ.v_ligne_rfn_occ_inter sélectionnant les tronçons de chemin de fer exploités intersectant la région Occitanie en veillant à bien découper les tronçons selon les contours de la région, ce qui est rendu possible grâce à la fonction ST_intersection.
-- Liste des segments de lignes de chemin de fer exploités, 
-- découpés sur les contours de la région Occitanie

CREATE OR REPLACE VIEW occ.v_ligne_rfn_occ_inter AS
SELECT 
    row_number() OVER () as id2,
    l.code_ligne,
    st_intersection(l.geom, d.geom)::geometry(MultiLinestring,2154) as geom 
FROM 
    occ.ligne_rfn l, 
    occ.departement d
WHERE 
    d.insee_reg = '76' AND
    l.libelle = 'Exploitée' AND
    st_intersects(l.geom , d.geom) ; 
-- la fonction st_intersects(geom1, geom2) renvoie TRUE par défaut

intersection