{ "cells": [ { "cell_type": "markdown", "id": "3f467ad7-4bdd-4ce9-86f6-738f90481072", "metadata": {}, "source": [ "# Exercice SQL – Requêtes spatiales topologiques\n", "\n", "Les questions de cet exercice portent sur la syntaxe de requêtes SQL.\n", "\n", "Nous allons utiliser:\n", "* une base de données QSpatiaLite avec les données d’agriculture d’une région d’Inde comprenant notamment les couches « Cultivated_Fields », « Roads », « Streams », « Village » et « Wells »;\n", "* SQL pour écrire les requêtes sur cette base de données;\n", "* le language de programmation Python afin d'interroger cette base de données (exécuter les requêtes).\n", "\n", "
\n", "
\n", " Comment utiliser ce notebook?
\n", " Ce notebook est composé de cellules de texte et de cellules de code (Python). Les cellules de code doivent être exécutées afin de voir le résultat du programme.
Pour exécuter une cellule, sélectionnez-la puis cliquez simplement sur le bouton \"play\" (►) dans la barre d'outils au dessus du notebook, ou tapez shift + entrée.
Il est important d'exécuter les cellules de code dans l'ordre dans lequel elles apparaissent dans le notebook.\n", "
\n" ] }, { "cell_type": "markdown", "id": "a08280d1-df23-4e8a-ae6f-64b55897c079", "metadata": {}, "source": [ "---\n", "---" ] }, { "cell_type": "markdown", "id": "4c50fb80-2bb2-4b29-8ead-05766a824b17", "metadata": { "tags": [] }, "source": [ "# Accès à la base de données avec Python\n", "\n", "Dans cette première partie, nous vous montrons par l'exemple comment écrire et exécuter vos requêtes sur notre base de données des cigognes avec Python." ] }, { "cell_type": "markdown", "id": "e3d8815d-b67b-4d99-90bb-455a66a8cbfa", "metadata": { "tags": [] }, "source": [ "## Import des libraries Python\n", "Pour manipuler une base de donnée avec Python nous avons besoin des librairies `sqlite3` et `pandas`:" ] }, { "cell_type": "code", "execution_count": null, "id": "1661fb65-c207-4d64-8163-d2e648744eeb", "metadata": {}, "outputs": [], "source": [ "# Importing libraries\n", "import sqlite3\n", "import pandas" ] }, { "cell_type": "markdown", "id": "83333b57-f511-4ccf-8c65-9819d4a29d5e", "metadata": {}, "source": [ "## Connexion à la base de données \n", "D'abord on établit une connexion à la base de données :" ] }, { "cell_type": "code", "execution_count": null, "id": "2ae2db69-8429-4dea-949f-8457ccf0b6e1", "metadata": {}, "outputs": [], "source": [ "# Etablir la connexion à la base de données\n", "con = sqlite3.connect(\"india.sqlite\")" ] }, { "cell_type": "code", "execution_count": null, "id": "3b7fba08-8930-4bd0-8ebd-767560d268ae", "metadata": {}, "outputs": [], "source": [ "# Charger l'extension spatialite\n", "con.enable_load_extension(True)\n", "con.load_extension(\"mod_spatialite\")" ] }, { "cell_type": "markdown", "id": "8006f1ac-8e69-48ac-84bb-879ae9ad2ffd", "metadata": {}, "source": [ "## Execution des requêtes SQL\n", "\n", "Pour exécuter une requête SQL sur notre base de données avec Python, on utilise la fonction `read_sql_query` de la librairie `pandas` qui :\n", "* execute la requête\n", "* nous retourne la table résultat\n", "Pour afficher cette table il suffit ensuite de faire un `display`.\n", "\n", "Voici un exemple, qui execute la requête `select * from wells` et affiche le résultat :" ] }, { "cell_type": "code", "execution_count": null, "id": "ba3d8cff-de55-4bed-a726-a52d312a333c", "metadata": {}, "outputs": [], "source": [ "# Donner la requête à executer\n", "query = \"select * from wells\"\n", "\n", "# Executer la requête et récupérer la table résultat\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Afficher la table résultat\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "810b6945-370b-4f7a-993a-1bb6ed83138e", "metadata": {}, "source": [ "
\n", "\n", "**Question** : modifiez la requête ci-dessus de manière à récupérer le contenu de la table `streams`." ] }, { "cell_type": "markdown", "id": "ecb47a7a-a6dd-44b4-9ce1-28e8d491b7e2", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "df308bbe-b320-4ce9-8724-4b6b27908bc6", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`select * from streams`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "cdef6666-e9c7-4614-8d89-95ea6629d98d", "metadata": {}, "source": [ "---\n", "\n", "## Question 1\n", "**Question** : Entrez la requête qui renvoie la liste des identifiants des puits qui se trouvent entièrement dans les champs de M. Vajubhai Madha.\n", "\n", "Voici comment procéder pour tester votre requête : \n", "1. Modifiez la requête dans la cellule ci-dessous puis exécutez la cellule pour voir le résultat. \n", "Vous pouvez modifier et ré-exécuter la cellule autant de fois que nécessaire. \n", "2. Une fois que vous êtes satisfait.e de votre requête, vous pouvez exécuter la cellule de validation ci-dessous pour vérifier si votre requête est correcte ou non. \n", "3. Enfin, vous pouvez ensuite comparer votre requête avec la solution." ] }, { "cell_type": "code", "execution_count": null, "id": "08fc5f61-202a-4e10-a0f6-c06333274b9b", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Donner la requête à executer\n", "query = \"select * from wells\" # TODO: MODIFIER LA REQUETE ICI\n", "\n", "# Executer la requête et récupérer la table résultat\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Afficher la table résultat\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "6ac9efb7-ea81-4663-80e2-ab78ac61b5b6", "metadata": {}, "source": [ "**Validation** : Executez la cellule suivante afin de savoir si votre requête est correcte ou non. \n", "Lisez attentivement les éventuels messages d'erreur afin de corriger votre requête." ] }, { "cell_type": "code", "execution_count": null, "id": "839e19f7-bdc8-494d-9fae-b41e3035e4a1", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_requetes_topologiques import *\n", "question1_validation(query)" ] }, { "cell_type": "markdown", "id": "dc9e0a45-ab04-44d6-94e5-5a38e02db8b1", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "3248b00b-42fc-4043-849d-ce206ef3d94e", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`select Wells.idwells from Cultivated_Fields, Wells where within(Wells.Geometry, Cultivated_Fields.Geometry) and Cultivated_Fields.owner like 'Vajubhai Madha'`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "5ded8691-5cc7-4ffd-8e7a-8b9e68ec9b28", "metadata": {}, "source": [ "---\n", "\n", "## Question 2\n", "\n", "**Question** : Comptez le nombre de champs étant en contact avec une rivière.\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "id": "0eef1a44-4781-4011-8dc2-cea0c48b4ccb", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Donner la requête à executer\n", "query = \"select * from wells\" # TODO: MODIFIER LA REQUETE ICI\n", "\n", "# Executer la requête et récupérer la table résultat\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Afficher la table résultat\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "e48b0894-c785-4b8e-a87e-e90baac5bdd7", "metadata": { "tags": [] }, "source": [ "**Validation** : Executez la cellule suivante afin de savoir si votre requête est correcte ou non. \n", "Lisez attentivement les éventuels messages d'erreur afin de corriger votre requête." ] }, { "cell_type": "code", "execution_count": null, "id": "b60cc09c-cbe5-4055-bdbf-dff6ef205faa", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_requetes_topologiques import *\n", "question2_validation(query)" ] }, { "cell_type": "markdown", "id": "8764c3f4-57c1-4690-a841-ef558b20e36f", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "74758991-7c8a-49be-8c23-d99f3505b03b", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`select Cultivated_Fields.Geometry from Cultivated_Fields , Streams where intersects(Streams.Geometry, Cultivated_Fields.Geometry) group by Cultivated_Fields.Geometry`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "baf8ea9d-52c7-4f09-b2da-f7a2350edb84", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 3\n", "\n", "**Question** : Sélectionnez la liste de tous les id de champs se trouvant à moins de 10m d’une route primaire (de type double). Utilisez la fonction Distance." ] }, { "cell_type": "code", "execution_count": null, "id": "6d53f99a-d108-446c-911a-23d6b7e26687", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Donner la requête à executer\n", "query = \"select * from wells\" # TODO: MODIFIER LA REQUETE ICI\n", "\n", "# Executer la requête et récupérer la table résultat\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Afficher la table résultat\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "94ffa2a0-ac1b-4ba5-b7b1-6a59c22aa6f7", "metadata": { "tags": [] }, "source": [ "**Validation** : Executez la cellule suivante afin de savoir si votre requête est correcte ou non. \n", "Lisez attentivement les éventuels messages d'erreur afin de corriger votre requête." ] }, { "cell_type": "code", "execution_count": null, "id": "edd89993-4524-4260-ae45-c03dcef23817", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_requetes_topologiques import *\n", "question3_validation(query)" ] }, { "cell_type": "markdown", "id": "9dbd7018-fe8c-47ef-b32f-e8ebce6fb84b", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "e4b270be-9464-4b53-8dc9-ae0b6d45f30f", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`SELECT DISTINCT Cultivated_Fields.ID FROM Cultivated_Fields, Roads WHERE Distance(Cultivated_Fields.Geometry, Roads.Geometry) <10 and Roads.Type='double'`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "2181501d-9609-45db-add3-11fef5627712", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 4\n", "\n", "**Question** : Créez une zone tampon de 20 m autour des routes (nommez cette colonne geom_buffer) et affichez-le dans QGIS (ne sélectionnez que cette nouvelle géométrie)" ] }, { "cell_type": "code", "execution_count": null, "id": "b0301e33-84c2-4519-ba42-315e274b4771", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Donner la requête à executer\n", "query = \"select * from wells\" # TODO: MODIFIER LA REQUETE ICI\n", "\n", "# Executer la requête et récupérer la table résultat\n", "resulttable = pandas.read_sql_query(query, con)\n", "\n", "# Afficher la table résultat\n", "display(resulttable)" ] }, { "cell_type": "markdown", "id": "a6d3cbb4-ba05-4f42-a5cf-57c034a7031f", "metadata": { "tags": [] }, "source": [ "**Validation** : Executez la cellule suivante afin de savoir si votre requête est correcte ou non. \n", "Lisez attentivement les éventuels messages d'erreur afin de corriger votre requête." ] }, { "cell_type": "code", "execution_count": null, "id": "6f6fa35d-2308-4d9f-91ad-3b849f122479", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_requetes_topologiques import *\n", "question4_validation(query)" ] }, { "cell_type": "markdown", "id": "b5acd255-bf2f-4eb4-b3ac-e0f1d3b9c3e7", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "4b0e5608-1b34-47ca-9e4e-eac6f9fb944b", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`select Buffer(geometry,20) as geom_buffer from Streams`\n", " \n", "
\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.10" } }, "nbformat": 4, "nbformat_minor": 5 }