Exercice 6 : sélections SQL avec DBManager
Réaliser ses premières requêtes SQL avec DBManager sous QGIS
en utilisant les tables de 'QGIS_perf_sandbox.sqlite' avec DBManager réaliser les requêtes suivantes :
Question
Q1 : sélectionner tous les IRIS (table IRIS_extrait72) de la commune de LA FLECHE (colonne Nom_Com).
nb : On fera attention à l'écriture FLECHE (LA) dans la table COMMUNE, avec deux espaces entre FLECHE et (LA).
Utiliser la table iris_extrait72 et mettre une condition après la clause WHERE permettant d'indiquer qu'on se limite à la commune de la Flèche.
SELECT * FROM iris_extrait72 WHERE Nom_com LIKE '%FLECHE%'
On peut aussi utiliser le code INSEE de la commune :
SELECT * FROM IRIS_extrait72 WHERE DepCom = '72154'
On doit obtenir un tableau de 7 lignes avec colonne Nom_Com toujours égale à FLECHE (LA).
Question
Q2 : sélectionner les communes du département de la Sarthe de plus de 1500 habitants en affichant un tableau avec les noms de communes et leur population.
Utiliser la table commune, sélectionner les champs demandés (nom des communes et population) dans la clause SELECT. Mettre deux conditions 'département de la Sarthe' ET 'population de plus de 1500 habitants' dans la clause WHERE.
SELECT nom_comm, population FROM commune WHERE population > 1500 AND nom_dept='SARTHE'
On doit obtenir un tableau de 2 lignes :
MALICORNE-SUR-SARTHE : 2000
LA FLECHE : 15400
Question
Q3 : sélectionner les communes de la table COMMUNE dont le statut n'est pas chef-lieu de canton et afficher les colonnes NOM_COMM en lui donnant comme alias NOM et les colonnes, STATUT, POPULATION et SUPERFICIE
On peut traduire le "n 'est pas" par l'utilisation de NOT
SELECT nom_comm AS nom,statut, population, superficie FROM commune WHERE NOT(statut = 'Chef-lieu de canton')
autres syntaxes possible :
SELECT nom_comm AS nom,statut, population, superficie FROM commune WHERE statut <> 'Chef-lieu de canton'
SELECT nom_comm AS nom,statut, population, superficie FROM commune WHERE statut IS NOT 'Chef-lieu de canton'
On doit obtenir un tableau de 18 lignes pour lesquelles le statut est commune simple ou Sous-préfecture.
Question
Q4 : sélectionner les différents noms des tronçons comportant le nom 'ruisseau' dans la colonne TOPONYME de la table TRONCON_HYDROGRAPHIQUE
Utiliser la table troncon_hydrographique. On pourra utiliser LIKE pour indiquer que le nom de tronçon doit contenir la chaîne 'ruisseau'.
SELECT DISTINCT toponyme FROM troncon_hydrographique WHERE toponyme LIKE '%ruisseau%'
la clause DISTINCT permet de supprimer les doublons dans la réponse (on ne s'intéresse ici qu'au tableau de résultat et pas aux objets géographiques).
On obtient un tableau de 10 lignes. Le toponyme doit contenir 'ruisseau'.
Question
Q5 : à partir de la table COMMUNE, calculer pour chaque département ; la population totale, la densité moyenne de population des communes = moyenne(population commune /superficie commune) arrondie à deux décimales, la population de la commune la plus peuplée et celle de la moins peuplée, la superficie moyenne des communes.
Le résultat doit être :
on cherche des sommes, moyennes,...par département il faut donc utiliser un GROUP BY (agrégation) avec comme critère le nom de département (NOM_DEPT).
Qui dit agrégation implique automatiquement l'utilisation de fonctions d'agrégation...On utilisera les fonctions d'agrégation donnant la somme, la moyenne, le maximum et le minimum.
SELECT nom_dept, sum(population) AS population_dept,
round(avg(cast(population as float)/superficie),2) AS densite_moy_communes,
max(population) AS pop_max_commune, min(population) AS pop_min_commune, round(avg(superficie),2) AS surface_moy_commune FROM commune
GROUP BY nom_dept
nb : le cast n'est pas utile si on utilise PostGIS au lieu de spatialite.
Question
Q6 : quels sont les surfaces (en km2) et périmètres (en km), arrondis à deux chiffres après la virgule, des communes du département de la Sarthe ?
trouver la fonction géométrique qui renvoie une aire, et celle qui renvoie un périmètre. Ces fonctions ne prennent pas de paramètres d'unités, il faut donc faire la conversion soit même par une division.
nb : pour spatialite on fera attention à ne pas utiliser length() qui renvoi la longueur d'une chaîne de caractères. Dans les dernières versions on utilisera la même fonction que sous PostGIS pour renvoyer le périmètre.
SELECT nom_comm, round(st_area(geometry)/1000000,2) AS SURFACE_km2, round(ST_Perimeter(Geometry)/1000,2) AS PERIMETRE_km FROM commune WHERE nom_dept = 'SARTHE'
La fonction de calcul retourne une valeur dans les unités de la projection qui est ici le mètre, d'où la nécessaire division pour convertir dans les unités demandées.
nb : La fonction ST_perimeter() n'est pas disponible dans la version de spatialite fournie avec QGIS 1.8, on utilise alors Glength(). Elle est disponible dans les dernières versions (QGIS > 2.2).
sous postGIS on écrira :
SELECT nom_comm, round((st_area(geom)/1000000) :: numeric,2) AS SURFACE_km2, round((ST_perimeter(geom)/1000) :: numeric,2) AS PERIMETRE_km FROM commune WHERE nom_dept = 'SARTHE'
On obtient un tableau de 16 lignes.
Pour ARTHEZE ont doit obtenir une surface de 8.7 km2 et un périmètre de 15.59 km.
Question
Q7 : sélectionner le nombre de tronçons de la 'rivière le loir', par classe de largeur (colonne LARGEUR)
Il faut 'compter' le nombre de tronçons, donc utiliser une agrégation avec la fonction d'agrégation qui permet de compter.
il y a 3 tronçons dans la classe 0 à 15 mètres et 29 dans la classe plus de 50 mètres.
SELECT toponyme, count(*) AS nbre_troncons, largeur FROM troncon_hydrographique WHERE toponyme = 'rivière le loir' GROUP BY largeur
on doit obtenir 3 tronçons de 0 à 15 mètres et 29 tronçons de plus de 50 mètres.
Complément :
La fonction count() accepte des expressions comme paramètre... on peut par exemple écrire
SELECT count(DISTINCT toponyme) from TRONCON_HYDROGRAPHIQUE qui donne 13 toponymes différents dans la table TRONCON_HYDROGRAPHIQUE
Question
Q8 : quelle est la longueur de la 'rivière le loir' par type de largeur sur ce jeu de données ?
Il faut partir de la requête précédente et ajouter une colonne qui va calculer la somme de la longueur des tronçons... on utilisera la fonction st_length qui donne la longueur d'un objet linéaire.
SELECT LARGEUR, TOPONYME,round(sum(ST_LENGTH(Geometry))/1000,4) AS Longueur_km FROM TRONCON_HYDROGRAPHIQUE WHERE TOPONYME = 'rivière le loir' GROUP BY LARGEUR
On doit obtenir :
de 0 à 15 mètres : 0.7981 km
plus de 50 mètres : 19.3578 km
