IPT 2014–2015 L’objectif de ce TP est de manipuler une base de donnée SQLite à l’intérieur d’un programme Python. Pour l’occasion, nous parlerons des arbres de Paris. 1 TP 15 • cur = dbh.cursor() (cur est alors l’outil de manipulation de la base de donnée, il « pointe » dessus, d’où le nom de curseur). • cur.execute("SELECT␣(...)") pour exécuter une requête. Vous pouvez mettre n’importe quelle chaine qui est une requête SQL valide. • res = cur.fetchall() et res contient alors le résultat de la requête sous forme d’une liste de tuples. Pendant que l’ordi démarre Pour ce TP, ni sqliteman ni Sqlite Manager ne sont indispensable. Vous pouvez les utiliser si vous voulez regarder la structure des tables, mais n’y perdez pas I Question 1 Faire en sorte que Python affiche le nombre d’arbres total dans la table, à l’aide de la requête SELECT count(*) FROM arbres. trop de temps ! La base 15-arbres.db est sur le serveur. Vous pouvez le copier dans /tmp/1 en tapant : (attention aux espaces après cp et * !) cp /home/users/etourniaire/Documents/TP/15-* /tmp/ I Question 2 Parmi ces arbres, combien sont des « Acer pseudoplatanus » ? I Question 3 Quel est le plus vieil arbre de Paris (parmi ceux dont on connait l’âge) ? Où se trouve-t-il ? On pourra utiliser google map pour se donner une Cette base contient une seule table arbres et un enregistrement par arbre dont idée. les champs sont les suivants : • • • • id est une clé primaire. lat et lon sont les coordonnées GPS de l’arbre. espece est l’espèce de l’arbre. circonference et hauteur sont respectivement la circonférence de l’arbre (en centimètres) et sa taille (en mètres) • age est l’âge depuis lequel cet arbre est planté, en jours (calculé le 201605-29). • adresse est une indication sur l’adresse de l’arbre en quesiton. • localisation donne le type de lieu dans lequel se trouve l’arbre (par exemple, JARDIN pour les arbres se situant dans un jardin). 2 Importer une table en Python I Question 4 Écrire une fonction distance(lat1,lon1,lat2,lon2) qui prend en entrée 4 coordonnées (latitude et longitude) et calcule une approximation de la distance entre les deux. On pourra considérer que la terre est sphérique de rayon 6370km, et on pourra éventuellement ajouter que notre zone d’étude est d’extension spatiale faible (il y a plusieurs manières de répondre à cette question, vérifier que la distance entre la gare St Lazare et la gare d’Austerlitz est d’environ 5km). I Question 5 Vérifier que la distance entre l’arbre le plus au nord et l’arbre le plus au sud de la base est d’environ 23.7 km. I Question 6 [Facultative pour plus tard] Calculer le rayon approximatif de la terre aux alentours de Paris en utilisant la norme WGS_84. Rappel des commandes pour manipuler la base en python : • import sqlite3 as lite (charge le module) • dbh = lite.connect('/tmp/15-arbres.db') (ouvre le fichier de base de donnée). 1 /tmp/ est un dossier local temporaire effacé à chaque redémarrage. 3 Afficher les arbres On utilisera la bibliothèque matplotlib.pyplot dans cette partie. Pour des raisons de performance, afficher tous les points d’un coup peut ralentir votre 1/2 IPT 2014–2015 machine. Il est conseillé de limiter votre recherche à 5000 points dans un premier temps (en utilisant limit 5000 directement dans la requête SQL). La fonction plt.scatter(x,y) prend en entrée deux listes de coordonnées (x1 , . . . , xn ) et (y1 , . . . , yn ) et affiche la liste des couples (xi , yi ). I Question 7 Afficher la position de tous les arbres. On doit obtenir un graphique ressemblant à ceci : TP 15 l’échelle, il faudra rajouter les bords de l’image avec la syntaxe suivante : plt.imshow(image,extent=[2.15,2.5,48.7,48.95],aspect=1.515) I Question 10 Expliquer à quoi correspondent les 5 valeurs numériques de la question précédente. 4 Calculs d’arbres L’intérêt d’utiliser Python, on l’a vu, est de pouvoir faire facilement des calculs plus évolués que ce qu’on pourrait avoir en SQL directement. I Question 11 Déterminer l’arbre le plus proche de la salle informatique où vous vous trouvez, c’est à dire du point de coordonnées (48.845104, 2.348346).2 I Question 12 Trouver un moyen de n’avoir que les arbres situés dans Paris (on pourra éliminer les autres avec traçant quelques droites). Calculer le barycentre de tous ces arbres, puis l’arbre le plus proche de ce barycentre, qu’on appellera le roi des arbres. I Question 13 Pour les arbres pour lesquels ces données sont connues, afficher l’âge et la taille sous forme d’un nuage de points. Qu’en pensez-vous ? I Question 14 Tracer la droite de régression après avoir éliminé les erreurs manifestes dans le fichier3 . I Question 8 Même chose mais en n’affichant que les arbres d’un seul type (on pourra commencer par regarder quels sont les types d’arbres existants). I Question 15 Faire une fonction qui calcule la densité en arbres dans la ville de Paris, en découpant la ville par des carrés de taille raisonnable. Superposer la carte de densité d’arbres à la carte déjà existante pour obtenir une représentation I Question 9 Afficher les arbres sur le plan de Paris. Pour cela, on va plus visuelle des zones d’arbre dans Paris. afficher une carte en surimpression, par exemple avec une vue satellite. Le fichier 15-ParisS.png contient une image satellite des alentours de Paris, mise à disposition gracieusement par l’IGN. On chargera l’image en mé2 Ce sont sans doute les arbres de la place Levinas ! 3 moire avec l’instruction : image = plt.imread("/tmp/15-ParisS.png") et Ces erreurs ne sont pas de moi, la mairie de Paris affirme vraiment qu’un de ses arbres on l’affichera avec la fonction plt.imshow. Pour décaler l’image et ajuster mesure 800m de haut. 2/2