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 indépendamment 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 ;
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 ;
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 DESC ;
On utilise ici l'ordre décroissant des départements pour faire apparaître dans la première ligne du résultat le nombre de gares pour un département non renseigné (NULL). 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 jointure permet-elle un bon alignement entre les 2 tables ? Quels sont les champs d'intérêt de cette nouvelle vue qui pourraient permettre la mise à jour de occ.gare
?
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. Les 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
c.nom_chf,
(i.p16_pop - i.p11_pop) AS evol_pop
-- si les champs p16_pop et p11_pop sont en texte, il faut recourrir au transtypage ''::numeric'
-- (i.p16_pop::numeric - i.p11_pop::numeric) AS evol_pop
FROM
occ.chef_lieu c,
occ.insee i
WHERE
c.insee_com = i.codgeo AND
c.statut LIKE '%réfecture%' AND
i.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_km2_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 ;
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,
l.libelle
FROM
occ.ligne_rfn l,
occ.departement d
WHERE
d.insee_reg = '76' AND
libelle = 'Exploitée' AND
st_intersects(l.geom , d.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,
v.code_ligne,
st_intersection(v.geom, d.geom) as geom
FROM
occ.v_ligne_rfn_occ v, -- on récupère la vue de l'exercice 7
occ.departement d
WHERE
d.insee_reg = '76' AND
v.libelle = 'Exploitée' AND
st_intersects(v.geom , d.geom) ;
-- la fonction st_intersects(geom1, geom2) renvoie TRUE par défaut