Exercice 8 : requêtes SQL avec QspatiaLite
Réaliser des requêtes SQL avec QspatiaLite
Objectif : créer une nouvelle base avec QspatiaLite et réaliser des requêtes SQL
Question
Q1 : Charger les couches suivantes dans QGIS :
BD_TOPO/I_ZONE_ACTIVITE/PAI_SANTE.SHP
/BD_TOPO/H_ADMINISTRATIF/COMMUNE.SHP
/BD_TOPO/E_BATI/BATI_INDUSTRIEL.SHP
/BD_TOPO/F_VEGETATION/ZONE_VEGETATION.SHP
avec QspatiaLite créer une nouvelle base de nom BDTOPO72
Importer toutes les couches chargées sous QGIS en EPSG 2154 (Lambert 93).
Créer une nouvelle couche BATI_INDUSTRIEL10 et la charger dans QGIS en sélectionnant dans la table BATI_INDUSTRIEL les 'Bâtiment industriel' (attention à la majuscule!) dont la hauteur est d'au moins 10 m
La table résultat doit contenir 8 enregistrements.
Question
Q2 : Afficher dans la fenêtre 'Result' de QspatiaLite les 'Forêt fermée de conifères' de la commune de La Flèche. Ne pas oubliez de mettre une condition de jointure entre les deux couches... qui devra être ici spatiale.
La table résultat doit contenir 55 enregistrements.
SELECT *
FROM "ZONE_VEGETATION", "COMMUNE"
WHERE "ZONE_VEGETATION".'NATURE' = 'Forêt fermée de conifères' and "COMMUNE".'NOM' = 'La Flèche' and st_intersects("ZONE_VEGETATION".'Geometry', "COMMUNE".'Geometry')
Question
Q3 : Plus difficile...
Calculer la somme des surfaces des 'Forêt fermée de feuillus' de la commune de la Flèche en ha (1ha = 10 000 m2), en faisant attention à ne prendre en compte que les parties de surfaces des polygones réellement situées à l'intérieur de la commune.
Ainsi dans l'exemple ci-dessus il ne faut prendre en compte que la partie du polygone jaune qui est à l'intérieur de la commune de la Flèche... on pourra penser à la fonction st_Intersection() qui retourne un objet géométrique intersection de deux objets...le résultat est 565 ha
SELECT round(sum(st_area(st_intersection("ZONE_VEGETATION".'Geometry',"COMMUNE".'Geometry'))) / 10000) as surface_ha
FROM "ZONE_VEGETATION", "COMMUNE"
WHERE st_intersects("ZONE_VEGETATION".'Geometry',"COMMUNE".'Geometry') and "ZONE_VEGETATION".'NATURE' = 'Forêt fermée de feuillus' and "COMMUNE".'NOM' = 'La Flèche'
Question
Q4 : Construire une nouvelle couche dans QGIS (non graphique) de nom ETABLIS_PLUS_PROCHE qui pour chaque établissement hospitalier de la couche PAI_SANTE donne l'identifiant (ID) de l'établissement industriel de la couche BATI_INDUSTRIEL le plus proche, ainsi que la distance
ATTENTION : Cet exercice fait appel pour sa solution à l'utilisation d'une sous-requête, il peut être jugé complexe, dans ce cas essayez de comprendre la solution. Son objectif est de montrer la puissance du SQL pour la résolution de problèmes parfois complexes...
Le résultat est :
Il est conseillé de décomposer un problème complexe en problèmes plus simples pour arriver à la solution...
On pourra dans un premier temps construire une table qui donne les distances de tous les établissements industriels de la couche BATI_INDUSTRIEL pour chaque établissement hospitalier. Il faut pour cela utiliser les deux tables PAI_SANTE et BATI_INDUSTRIEL. On notera qu'on ne peut donner une condition de jointure, ni attributaire (pas de champ commun), ni géographique (les objets ne se superposent pas). Dans ce cas on peut construire le produit des deux tables (produit cartésien) sans condition (ce que n'autorise pas MapInfo).
SELECT * FROM "Pai_SANTE","BATI_INDUSTRIEL"
Il reste à ajouter la colonne donnant les distances.
ATTENTION : Faire un produit cartésien sur deux tables sans condition de jointure doit être réservé à des tables de petite dimension.
nb : Pour éviter de faire le produit cartésien complet, on pourrait penser à utiliser sous PostGIS la fonction ST_DWithin() avec un rayon de recherche maximum, fonction qui est disponible que dans spatialite 4.0 sous le nom de PtDistWithin().
La table précédent peut nous donner accès pour chaque PAI_SANTE à la distance minimum de l'établissement le plus proche avec un GROUP BY
SELECT PAI_SANTE.ID, min(st_distance(PAI_SANTE.Geometry, BATI_INDUSTRIEL.Geometry)) AS distance_min from PAI_SANTE,BATI_INDUSTRIEL GROUP BY PAI_SANTE.ID
On pourrait penser à demander dans le tableau BATI_INDUSTRIEL.ID... mais le résultat serait faux, car il ne faut pas oublier lorsqu'on utilise un GROUP BY que chaque colonne en sortie (dans la clause SELECT) doit être, soit le critère de rupture (celui du GROUP BY), soit être le résultat d'une fonction d'agrégation...(sous PostGIS vous aurez d'ailleurs un message du type
ERREUR : la colonne "bati_industriel.id" doit apparaître dans la clause GROUP BY ou être utilisée dans une fonction d'agrégation
Spatialite est plus tolérant, mais il vaut mieux prendre les bonnes habitudes !
Nous voila donc avec le tableau suivant :
Il faut maintenant trouver les couples (PAI_SANTE.ID BATI_INDUSTRIEL.ID) pour lesquels la distance est l'une ou l'autre des distances de la table précédente... autrement dit exécuter une requête du type
SELECT...st_distance(...) as distance FROM PAI_SANTE, BATI_INDUSTRIEL WHERE distance IN (... résultat de la requête donnant les deux distances minimum)
La solution est :
SELECT PAI_SANTE.ID, BATI_INDUSTRIEL.ID, st_distance(PAI_SANTE.Geometry, BATI_INDUSTRIEL.Geometry) AS distance FROM PAI_SANTE,BATI_INDUSTRIEL WHERE distance IN (SELECT min(st_distance(PAI_SANTE.Geometry, BATI_INDUSTRIEL.Geometry)) AS distance_min FROM PAI_SANTE,BATI_INDUSTRIEL GROUP BY PAI_SANTE.PKUID)
Pour charger la table dans QGIS on utilisera un 'Create Table & Load in QGIS' (puisque ce n'est pas un table spatiale).
Complément :
Le but de l'exercice est de montrer l'intérêt et la syntaxe d'une requête complexe. Cependant pour répondre à la question posée nous aurions pu utiliser l'outil Vecteur -> Outils d'analyse -> Matrice des distances.
Cet outil ne fonctionnant que sur des couches de points, il faut au préalable créer la couche BATI_INDUSTRIEL_CENTROID avec la fonction Vecteur -> Outil de géométrie -> Centroïdes de polygones.
On utilise ensuite l'outil 'matrice de distances' en choisissant 'Utiliser uniquement les points cibles les plus proches' avec k=1, Ceci génère un fichier csv qui donne le résultat cherché. Les distances sont un peu différentes qu'avec la fonction st_distance qui utilise le contour des polygones au lieu du centroïde.
On pourrait également utiliser le plugin NNJoin qui permet de calculer pour chaque objet de la couche (input layer), l'objet le plus proche de la couche 'Join vector Layer', ainsi que sa distance.
Il est possible pour la couche 'input layer' d'utiliser les centroides si la couche n'est pas une couche de points et pour calculer les objets les plus proches d'utiliser 'Approximate by index geometries' qui utilise les rectangles englobant plutôt que la géométrie exacte ce qui permet un calcul beaucoup plus rapide.
