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 ;
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
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