Partie 1

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