Exercice 7 : Requêtes et fonctions spatiales
Réaliser des requêtes et fonctions spatiales
Exercice tutoré : envoi des réponse aux tuteurs
Écrire les requêtes SQL demandées (Vérifier qu'elles fonctionnent sous le DBmanager) dans l'exercice qui va suivre dans un fichier texte (txt, doc...) sous la forme suivante :
Q1 : SELECT ...
Q2 : SELECT...
...
et remettre ce fichier texte sous le nom "NomStagiaire_EX07" à l'équipe de formation selon la modalité indiquée en début de formation.
En utilisant les tables de 'QGIS_perf_sandbox.sqlite' réaliser les 4 requêtes suivantes
Question
Q1 : quels sont les ponctuels hydrographiques de la commune de La Flèche ?
Indice
On utilisera les tables PONCTUEL_HYDROGRAPHIQUE et COMMUNE... trouver la relation géométrique entre PONCTUEL_HYDROGRAPHIQUE.Geometry et COMMUNE.Geometry.
Question
Q2 : quelle est la longueur de la 'rivière le loir' dans chacune des communes intersectées par le cours d'eau ?
résultat à obtenir :
Indice
Chaque commune peut contenir plusieurs tronçons, il faut donc calculer la somme des longueurs des tronçons pour chaque commune... donc utiliser un GROUP BY et une fonction sum(). On utilisera la fonction ST_Length() pour obtenir la longueur de chaque tronçon. Il faut également tenir compte que certains tronçons sont à cheval sur plusieurs communes, et donc ne prendre en compte que la longueur des tronçons qui sont à l'intérieur de chaque commune pour ce faire on utilisera st_intersection(a.geom, b.geom) qui permet de récupérer la géométrie de l'objet a qui intersecte celle de l'objet b.
Question
Q3 : sélectionner les 'PONCTUELS HYDROGRAPHIQUES' qui sont à moins de 5 km d'un établissement d'enseignement (couche ETABLISSEMENT)
Indice
On pourra utiliser une fonction st_distance() ou une fonction st_buffer() associée à un opérateur de type st_contains() ou st_intersects().
Question
Q4 : Quel est l'établissement le plus proche du centroide de la commune de la Flèche?
On utilisera les coordonnées X_COMMUNE et Y_COMMUNE et la fonction st_makepoint() ou Makepoint() sous spatialite ou encore st_point() qui est un alias de Makepoint(). Le SRID (Identifiant du Système de Référence Spatial) est 2154, mais on pourra le cas échéant généraliser la requête à tout SRID en utilisant la fonction srid() qui récupère le srid d'une géométrie.
Indice
utiliser la fonction distance(), ORDER BY pour trier et LIMIT 1 pour prendre le 1er objet renvoyé.
Une requête de type
SELECT nom_comm, srid(Geometry) AS SRID, MakePoint(X_COMMUNE, Y_COMMUNE, srid(Geometry)) AS Geometry FROM commune WHERE commune.nom_comm = 'LA FLECHE'
retourne des points au centroide calculé à partir des coordonnées X_COMMUNE, Y COMMUNE.