Exploiter les Fichiers fonciers avec PostgreSQL/PostGIS CEREMA DTer Nord-Picardie Partie 1 Mise à jour – Février 2016 Présentation : Perrine Rutkowski et Magali Journet L’interface de PgAdmin (1/2) 4 Février 2016 2 L’interface de PgAdmin (2/2) 4 Février 2016 3 1 Notions générales sur le SQL 4 Février 2016 4 Qu’est-ce qu’une requête SQL ? SQL= « Structured Query Language » ou « langage de requête structuré » Une requête SQL est une instruction envoyée au gestionnaire de base de données Une requête peut être de deux types : Exécution : la requête modifie la base de données, sans retour pour l’utilisateur Résultat : la requête renvoie des données (tableau) La requête « SELECT » est la plus importante : elle permet de sélectionner et croiser des données ; elle renvoie un résultat 4 Février 2016 5 Comment exécuter une requête SQL ? Exemples d’interfaces graphiques permettant d’exécuter des requêtes SQL : PgAdmin : logiciel qui permet de visualiser le contenu de la base de données et d’exécuter des requêtes personnalisées QGIS : permet d’afficher directement des données géographiques stockées dans PostgreSQL, sans avoir à les convertir dans un format SIG standard Psql : utilitaire s’utilisant en ligne de commandes (peu accessible pour les non initiés, mais facilite l’exécution de requêtes en série) 4 Février 2016 6 Comment écrire une requête SQL ? Certaines règles à respecter : Chaque requête doit se terminer par un point-virgule (;) Tous les mots obligatoires doivent être présents dans le bon ordre Plusieurs requêtes peuvent s’enchaîner (niveau avancé) Les requêtes importantes doivent être enregistrées Les mots obligatoires doivent être écrits en majuscule (lisibilité) La requête doit être mise en forme : espaces, tabulations La requête doit être commentée (« -- » en début de ligne ou « /* » et « */ ») 4 Février 2016 7 Pratiquer de manière autonome Le plus important est de savoir où chercher : Sites officiels (cf. document html « liens utiles ») Recherches intuitives sur Google Forums collaboratifs 4 Février 2016 8 Organisation et nommage des objets PostgreSQL est un système de gestion de bases de données : il peut gérer plusieurs bases de données avec des droits d’accès différenciés pour chaque base Au sein de chaque base, les tables sont rangées dans des « schémas » (sous-répertoires) : Une base > des schémas > des tables > des colonnes La colonne d’une table est identifiable de la manière suivante : <nom_schema>.<nom_table>.<nom_colonne> Exemple : ff_d83_2014.d83_2014_pnb_parcelle.idpar Nommage : éviter les accents, espaces et caractères spéciaux, sauf pour les alias Ne contient que les caractères [a-z], [0-9] ou underscore (_) Commencer par une lettre (exemple : ff_d59_2009) 4 Février 2016 9 2 Notions de base sur la requête la plus utile : SELECT 4 Février 2016 10 La requête SELECT (1/2) La requête SELECT est la plus couramment utilisée dans un SGBD : elle permet de sélectionner les résultats issus d'une ou plusieurs tables La requête SELECT n'est qu'une fonction parmi d'autres. Important : page d'aide des requêtes SQL disponibles dans PostgreSQL (notamment pour placer les mots-clés dans le bon ordre) http://docs.postgresql.fr/9.0/sql-commands.html Il suffit de lire l’URL pour vérifier d’un coup d’oeil qu’on se trouve sur la page d’aide de la version souhaitée. Sinon, il suffit de la changer dans l’URL. 4 Février 2016 11 La requête SELECT (2/2) Forme type d’une requête SELECT : 4 Février 2016 12 Exemple de requête SQL 4 Février 2016 13 Exercice 1 : sélection de colonnes, de lignes, et alias Sélectionner dans la table des parcelles 2014 du département 83 : L’identifiant de parcelle, La surface de parcelle, Le nom de commune, La surface des subdivisions fiscales. Limiter l’affichage aux 50 premières lignes Donner un nom plus « parlant » aux 2e et 3e colonnes grâce aux alias 4 Février 2016 14 WHERE Permet de filtrer les lignes voulue Doit être suivi d’une expression renvoyant une valeur booléenne (vrai ou faux) On utilise les opérateurs logiques : OR, AND, NOT, >, <=, etc. Forme : 4 Février 2016 15 Exercice 2 : filtrage sur les lignes Sélectionner dans la table des locaux 2014 du département 83 : L’identifiant de local, Le type de local, La surface totale des parties d’évaluation, Sélectionner uniquement les locaux des communes de « Toulon » ou « Ollioules », dont la surface totale des parties d’évaluation est supérieure à 200 m² Limiter l’affichage à 100 lignes 4 Février 2016 16 Usage : la densité bâtie ou COS réel Utilisation de la surface des pev à l’échelle de la parcelle (variables stoth, stotp, etc.) : cf. fiche 3.2 « La densité bâtie » du groupe de travail sur la consommation d’espaces La densité bâtie est le rapport entre la surface de plancher et la surface parcellaire ; elle permet notamment de juger des potentiels de densification et de l’optimisation du foncier 4 Février 2016 Densité bâtie sur Boulogne-sur-Mer Densité bâtie sur le SCOT du Boulonnais (62) – carroyage 100mx100m Source : CETE Nord-Picardie 17 ORDER BY Permet de trier le tableau renvoyé Doit être suivi d’un champ, d’une liste de champs ou d’une expression, séparés par une virgule Forme : 4 Février 2016 18 Exercice 3 : calculs sur les champs et tri Sélectionner les parcelles pour lesquelles la surface totale des subdivisions fiscales (sufs) est différente de celle de la parcelle Ne garder que les colonnes pertinentes Trier de manière descendante par la valeur absolue de la différence de surface Complément : exporter les données textuelles (explorer les deux solutions : « copier/coller » et « disquette ») 4 Février 2016 19 Usage : corriger les surfaces de sufs aberrantes Cas très rares mais qui localement peuvent être problématiques Par exemple, pour 0,023% des parcelles du fichier national 2009, la surface de la parcelle est différente de la somme de la ou des sufs qui la composent Exemple de Boulogne-sur-Mer (le cas le plus extrême dans les fichiers) : 598 ha de surface de parcelles, 1952 de surface de sufs, 1875 ha de surface urbanisée hors voirie (une dizaine de parcelles avec plusieurs centaines de sufs). Raisons : erreurs d'enregistrement, non renseignement de surfaces, copropriétés « horizontales » Le redressement est réalisé en prenant comme référence la surface parcellaire Si dcntpa>ssuf : la surface (dcntpa-ssuf) a une occupation indéterminée Si dcntpa<ssuf, il faut corriger les dcnt pour que la somme des dcnt soit égale à dcntpa (les cas les plus extrêmes invitent à corriger la surface de « sol » dcnt13) 4 Février 2016 20 Exercice 4 : conversions et types de données Sélectionner les parcelles bâties pour lesquelles la densité de logements est inférieure à 1 logement par ha Trier par densité croissante 4 Février 2016 21 Usage : identifier les parcelles très peu denses Cf. fiche 3.1 du groupe de travail national sur la consommation d’espaces Cas les plus fréquents : exploitations agricoles, maisons forestières, châteaux, etc. 4 Février 2016 22 Importance pour le suivi de l’urbanisation Choix effectué en Picardie : parcelles de densité supérieure ou égale à 1 logement/ha Effet de lissage : Évolution des surfaces bâties pour l'habitat Base 100 en 1962 Avant filtre Après filtre 500 400 350 300 250 200 150 100 50 0 400 300 200 100 0 Aisne Oise Somme Picardie Aisne Oise Somme Picardie 4 Février 2016 23 Repérer des spécificités territoriales 4 Février 2016 24 CREATE TABLE/VIEW CREATE TABLE : Permet de créer une nouvelle table correspondant au résultat d’une requête SELECT Syntaxe : Le principe est de faire suivre les termes CREATE TABLE […] AS par la requête SELECT de son choix CREATE VIEW : Une vue est une sorte de table virtuelle (mise à jour en continu) 4 Février 2016 25 Complément à l’exercice 4 : export de données géographiques Sur une commune au choix, sélectionner les parcelles comprenant au moins un logement Exporter les données géographiques : Avec le plugin du CEREMA Nord-Picardie Avec QGIS Visualiser sur QGIS 4 Février 2016 26 Merci de votre attention Lien vers le portail fichiers fonciers : http://geoinfo.metier.i2/fichiers-fonciers-r549.html 4 Février 2016 27