{ "cells": [ { "cell_type": "markdown", "id": "3f467ad7-4bdd-4ce9-86f6-738f90481072", "metadata": {}, "source": [ "# Exercice SQL – Requêtes spatiales géométriques\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": 20, "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": 21, "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": 22, "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** : Affichez la géométrie du village sous forme WKT. Nommez cette colonne geometry_wkt.\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_geometriques 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 AsText(geometry) as geometry_wkt from Village`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "5ded8691-5cc7-4ffd-8e7a-8b9e68ec9b28", "metadata": {}, "source": [ "---\n", "\n", "## Question 2\n", "\n", "**Question** : De quel type est cette géométrie (Point, Polygone, Multipolygone) ? Nommez cette colonne geometry_type.\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_geometriques 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 GeometryType(geometry) as geometry_type from Village`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "baf8ea9d-52c7-4f09-b2da-f7a2350edb84", "metadata": {}, "source": [ "---\n", "\n", "## Question 3\n", "\n", "**Question** : Listez les champs (tous les attributs) qui ont une aire supérieure à 4ha." ] }, { "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_geometriques 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 * from Cultivated_Fields where Area(geometry) > 40000`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "2181501d-9609-45db-add3-11fef5627712", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 4\n", "\n", "**Question** : Trouvez le centroide (nommez le geom_centr) du village et affichez-le dans QGIS." ] }, { "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_geometriques 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 Centroid(geometry) as geom_centr from Village`\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "43863949-1e0d-4dee-9f08-f560700677a5", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 5\n", "\n", "**Question** : Quelle est la longueur totale des routes secondaires (de type « simple »)? Nommez cette colonne longueur." ] }, { "cell_type": "code", "execution_count": null, "id": "934f9bfa-dd22-456f-9137-7eed970324a4", "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": "296dde86-dd6f-4d2f-8c17-d8ab577d9455", "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": "4aaf1326-f94a-4527-b334-266e5989238f", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_requetes_geometriques import *\n", "question5_validation(query)" ] }, { "cell_type": "markdown", "id": "0ef2b7d1-2221-4833-b7f6-2741adb7e24c", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "e3030f35-7b38-4be0-852b-b1dff7635117", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`Select sum(GLength(Geometry)) as longueur From Roads Where Type like 'simple'`\n", "\n", " \n", "
\n" ] }, { "cell_type": "markdown", "id": "8db43a73-7383-4f1c-a875-434742cb7001", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 6\n", "\n", "**Question** : Listez tous les puits situés au Sud-Ouest du centroïde du village." ] }, { "cell_type": "code", "execution_count": null, "id": "32fdb028-4dcf-4b96-8cde-05e7bded33f2", "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": "9b3de033-9140-49f8-ab40-ac187c02b40f", "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": "cf911d59-4dfd-45e4-a780-27ef0d62c0ba", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_requetes_geometriques import *\n", "question6_validation(query)" ] }, { "cell_type": "markdown", "id": "4b9be0c0-2c5b-4017-b4ba-486440c52b5f", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "ea940598-7455-4e12-9164-25393a14c08f", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`SELECT COUNT(*) AS num_SW FROM Wells, Village WHERE X(Wells.Geometry)\n" ] }, { "cell_type": "markdown", "id": "a09fd9b4-7dc2-440a-8114-fe4113ecc51b", "metadata": { "tags": [] }, "source": [ "---\n", "\n", "## Question 7\n", "\n", "**Question** : Donnez le code EPSG du SCR de la couche « Wells ». Nommez cette colonne EPSG." ] }, { "cell_type": "code", "execution_count": null, "id": "6014335a-4241-405e-9e25-bcf84201cb7c", "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": "b0323150-c4c4-41ba-b0a5-b6194d6c5eec", "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": "5562a720-c7f2-43af-b711-666afbe530c9", "metadata": { "tags": [] }, "outputs": [], "source": [ "from SQL_requetes_geometriques import *\n", "question7_validation(query)" ] }, { "cell_type": "markdown", "id": "3e9a87a1-c70f-4b3e-9122-78dfa75e7033", "metadata": { "tags": [] }, "source": [ "**Solution** : Cliquez sur les trois petits points \"...\" ci-dessous." ] }, { "cell_type": "markdown", "id": "941f4861-f978-4538-a9e4-828730f58bce", "metadata": { "jupyter": { "source_hidden": true }, "tags": [] }, "source": [ "
\n", "\n", "Voici la requête correcte :\n", " \n", "`Select SRID(Geometry) as EPSG From Wells`\n", "\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 }