Import/export de données vecteurs-PostGIS

A Outils graphiques

AA Import avec shp2pg-gui

shp2pgsql-gui est un utilitaire fourni avec la version Windows de Postgis . C’est la version graphique du shp2pgsql en ligne de commande.

Par défaut, il se trouve au chemin C:\Program Files\PostgreSQL\9.3\bin\postgisgui\shp2pgsql-gui.exe mais il peut éventuellement être ailleurs en fonction de l’installation de PostGIS. S’il n’est pas installé, il peut être récupéré en téléchargeant PostGIS pour Windows . Il n’est pas nécessaire d’avoir les droits d’administrateur pour se servir de shp2pgsql-gui (à condition de choisir le ZIP sans le -setup ).

Répertoire contenant le programme shp2pgsql-gui . Ce répertoire peut varier d’un système à l’autre.

Se rendre sur cette adresse : http://epp.eurostat.ec.europa.eu/portal/page/portal/gisco_Geographical_information_maps/popups/references/administrative_units_statistical_units_1
Récupérer le ZIP NUTS_2006_03M_SH.zip sous la ligne NUTS 2006 1 : 3 Million Europe Point / Polygon Shapefile 2006 ETRS89 17.2 NUTS_2006_03M_SH.zip
Désarchiver le ZIP dans un répertoire
Identifier l’emplacement de shp2pg-gui et le lancer
Dans shp2pg-gui , effectuer l’import du Shapefile nuts/data/NUTS_RG_03M_2006.shp dans la table nommée nuts_2006

QUESTION AAA Capture d’écran de shp2-pg-gui après l’import (avec les messages de sortie)

AB Import/export avec ogr2gui

ogr2gui est une interface graphique pour ogr2ogr , un outil puissant qui permet de convertir d’un format vecteur à l’autre, qu’il s’agisse de fichiers, de bases de données, ou d’assemblages plus dynamiques.

Cela signifie qu’on peut importer en base moult formats de fichiers , ainsi que les exporter depuis la base, ou même copier d’une base à l’autre.

ogr2gui fournit une interface pratique mais cependant simpliste en comparaison de ce qui est possible avec ogr2ogr , mais permet de spécifier toute option souhaitée dans la zone de texte Options (la ligne de commande est toujours ce qu’il y a de plus puissant).

L’utilitaire est téléchargeable sur le site officiel : http://www.ogr2gui.ca/

L’exécutable `ogr2gui` parmi les autres fichiers du répertoire extrait de l’archive téléchargée.

Il suffit d’inverser Source et Target pour copier les données depuis la base PostGIS vers un shapefile au lieu du contraire.

Comme l’import, on peut exporter dans moult formats de fichiers et même d’une base de données à une autre.

Se référer à la documentation de ogr2ogr pour les options avancées.

AC Import avec le Gestionnaire de bases de données QGIS

Cet outil permet d’importer divers formats vectoriels dans PostGIS (y compris non-géométriques, comme les CSV). Comme il est lié à QGIS , c’est un outil de choix lorsqu’on travaille avec ce logiciel, car le gestionnaire de bases de données permet aussi d’inspecter les tables, exécuter des requêtes SQL, etc.

La connexion à la base doit être créée au préalable sur QGIS (Couche → Ajouter une couche PostGIS )
Dans QGIS , cliquer sur le menu Base de donnée puis Gestionnaire de bases de données → Gestionnaire de bases de données .

(cette méthode a déjà été expérimentée dans le module Travailler sur la France )

AD Export avec QGIS

QGIS est un autre ogr2gui , car il utilise lui aussi OGR , sans passer par ogr2ogr mais en exploitant directement les fonctions de la bibliothèque OGR . Il est plus évolué que ogr2gui , donc à utiliser de préférence lorsqu’on peut disposer de QGIS.

Cette méthode ne peut exporter que des couches existantes du projet. Cela signifie qu’il faut ajouter la couche souhaitée au préalable, mais sous-entend aussi qu’on peut faire l’opération pour exporter les formats traitables avec QGIS et pas seulement des tables PostGIS.

Ouvrir QGIS
Ajouter au projet la couche nuts_2006 (importée dans AA )
Faire clic droit sur la couche puis Enregistrer sous
Choisir le format GeoJSON et valider l’enregistrement

QUESTION ADA Les 5 premières lignes du fichier GeoJSON généré ainsi que les 287 premiers caractères de la 6^e ligne

B Outils en ligne de commande

BA Import avec shp2pgsql

shp2pgsql est un utilitaire fourni avec PostGIS qui s’utilise en ligne de commande. Il ne fait qu’une seule chose : à partir d’un Shapefile , générer des instructions SQL qui créent la table et la peuple avec les données.

USAGE: shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]

Exécuter shp2pgsql sans arguments pour obtenir de l’aide. Les options les plus importantes sont :

-s 4326 pour définir le SRID EPSG:4326 (ou un autre)
-I pour créer l’index spatial automatiquement
-W LATIN1 pour interpréter les textes du shapefile en ISO-8859-1 au lieu de UTF-8 (si les accents passent mal)

shp2pgsql ne se connecte pas à la base, il ne fait que générer le SQL qu’on pourra ensuite exécuter dans la base, en faisant ou bien un pipe vers la commande psql :

shp2pgsql shapefile.shp | psql -d postgis_database

... qui est le plus simple et le plus élégant (quand on dispose de psql ), sinon en enregistrant la sortie dans un fichier :

shp2pgsql shapefile.shp > shapefile.sql

... avant d’exécuter ce fichier, avec pgAdmin par exemple.

Récupérer la donnée suivante au format Shapefile http://data.iledefrance.fr/explore/dataset/gares_ferroviaires_de_tous_types_exploitees_ou_non/
Désarchiver le ZIP puis ouvrir l’interpréteur de commandes cmd
Exécuter la commande shp2pgsql pour générer le fichier gares_ferroviaires_de_tous_types_exploitees_ou_non.sql avec le bon SRID
Exécuter le SQL enfin, avec l’une ou l’autre des méthodes

QUESTION BAA La ligne de commande shp2pgsql complète qui produit le fichier SQL

QUESTION BAB La première commande INSERT du fichier généré

BB Export avec pgsql2shp

pgsql2shp est l’inverse de shp2pgsql , ou presque. Elle se connecte à la base PostGIS spécifiée et exporte la table voulue dans un fichier shapefile du répertoire courant, nommé d’après la table.

Dans cet exemple :

pgsql2shp -h localhost -u user -P password postgis_database data_table

... la table data_table est exportée en shapefile , en produisant les 4 fichiers du Shapefile :

data_table.dbf : attributs
data_table.prj : information de projection
data_table.shp : géométries
data_table.shx : index spatial

Ouvrir l’interpréteur de commande cmd puis exécuter pgsql2shp pour exporter la table etablissements_culturels

QUESTION BBA Ligne de commande

QUESTION BBB Poids du fichier etablissements_culturels.shp généré

BC Import avec ogr2ogr

Nous allons maintenant importer des données de démographie carroyées sur le territoire, dont il va falloir reconstituer les géométries (les carreaux , polygones carrés).

À partir de ce lien : http://www.insee.fr/fr/themes/detail.asp?reg_id=0&ref_id=donnees-carroyees&page=donnees-detaillees/donnees-carroyees/donnees-carroyees-km.htm
Télécharger le ZIP Fichier métropole et le désarchiver quelque part
Écrire la ligne de commande ogr2ogr qui permet d’importer la donnée dans la base PostGIS

QUESTION BCA La commande et ses arguments

QUESTION BCB La donnée est-elle géographique, au sens métier ?

QUESTION BCC La donnée est-elle géométrique, au sens de ogr2ogr ?

Une fois la table importée dans PostGIS, on va reconstituer, non pas seulement le point géométrique, mais le polygone carré. Il faut tout d’abord ajouter le champ geom à la table r_rfl09_laea1000 .

QUESTION BCD À quelle position dans le carreau correspondent les coordonnées fournies (centre, coin inférieur-gauche, coin supérieur-droit...) ? (une méta-donnée qu’on ne peut deviner)

QUESTION BCE Taille du côté des carreaux, en mètres

QUESTION BCF Nom de la projection, d’après les méta-données

QUESTION BCG SRID de la projection (un code EPSG, indiqué par l’INSEE via le lien Documentation générale sur le carroyage , depuis le lien d’origine ci-dessus)

QUESTION BCH Requête UPDATE qui définit la géométrie (avec SRID) pour tous les enregistrements de la table

Vérifier la couche avec QGIS :

Ajouter la table r_rfl09_laea1000 (astuce : au lieu de Ajouter une couche PostGIS , il y a le panneau Parcourir )
Appliquer un style Gradué sur le champ ind :
- symbole sans bordure (Symbole → Modification... → Remplissage simple → Style de la bordure → Pas de ligne )
- Palette BuPu (va du blanc au violet en passant par le bleu)
- 50 classes réparties en mode Quantile (effectifs égaux)

QUESTION BCI Capture QGIS sur l’étendue de l’île d’Ouessant (échelle à laquelle les carrés sont bien visibles)

C Traiter les fichiers CSV

CA Préparer un fichier CSV à partir d’une source Métier

Comme exercice, nous allons importer des données européennes de la base ESPON Database sur l’emploi par groupe de secteur d’activité et par région européenne. ESPON fournit des jeux de données très complets et détaillés sur toutes sortes de thématiques, c’est pourquoi nous l’utiliserons à de multiples reprises au cours de cette formation.

Se rendre sur http://database.espon.eu/
Via le menu, ouvrir Search → Query
Choisir la thématique Labour Market : le filtre indiqué par Filtered by devrait indiquer : SEMANTIC Labour Market DATA ALL ALL WHERE EU28+4 WHEN 1628-2100

Page de résultat sur le site web d’ESPON
Dans la liste de résultats, c’est Employment by economic sector qui nous intéresse. Pour le trouver, regarder éventuellement les pages suivantes ou affichier plus de résultats dans le menu Results by page en bas
Pour récupérer les données, cliquer à droite sur la flèche vers le bas (sous-titre : Download the indicator data and metadata (XLS file) ). Enregistrer le fichier XLS temporairement quelque part, puis l’ouvrir avec LibreOffre (ou OpenOffice ).

Pourquoi LibreOffice plutôt que Microsoft Excel ?

Raison fonctionnelle : Excel ne permet pas de préciser certaines options lors de l’enregistrement d’un fichier CSV (caractère de délimitation, type d’encoding...), or tout l’enjeu est là. Il ne permet pas non plus de les spécifier à l’ouverture, ce qui pose problème avec les CSV qui utilisent le point-virgule ou la tabulation comme caractère de délimitation. Il existe cependant un Assistant d’import de texte mais il faut y penser alors que LibreOffice , lui, propose ces options dès l’ouverture d’un fichier CSV. Pour plus d’informations, consulter la la documentation Microsoft sur les CSV
Droits & licence : tout un chacun peut installer LibreOffice . Ce n’est pas le cas de Microsoft Office qui requiert des contreparties financières et la privatisation de l’ordinateur sur lequel il doit s’exécuter.

Dans le tableur, les 3 premières feuilles de calcul (Dataset , Indicators et Sources ) contienent des méta-données permettant de remonter à la source, savoir qui contacter en cas de besoin, etc.

Copier-coller la feuille de calcul Data dans un nouveau tableur (Fichier → Nouveau → Feuille de calcul )
Supprimer les 5 colonnes Source
Ré-écrire les autres noms de colonnes en minuscules en remplaçant les espaces par des underscores _ : Unit code → unit_code , Object type → object_type , etc.
Ré-écrire aussi les champs WF_AGRICULTURE , etc. en minuscule sur la même ligne, puis :
Supprimer les 2 premières lignes devenues inutiles (afin d’avoir un tableau propre avec une seule ligne d’en-têtes de colonnes puis les données)
Utiliser Chercher & Remplacer pour remplacer toutes les valeurs N/A par rien , afin que ces valeurs soit interprétées comme des NULL par PostgreSQL.
Enregistrer le fichier, d’abord au format tableur (Tableur ODF ) sous le nom espon-labour-market-sectors comme sauvegarde du travail réalisé.
Enfin, exporter en CSV (Ctrl+Maj+S ) sous le nom espon-labour-market-sectors.csv , en gardant les réglages par défaut lors de la confirmation (Délimiteur de champ = , , Délimiteur de texte = " )
Vérifier le contenu texte du fichier généré en l’ouvrant avec un bloc-note

QUESTION CAA Nombre de colonnes contenues dans le CSV

QUESTION CAB Nombre d’enregistrements contenus dans le CSV

Premières lignes du fichier CSV enregistré

CB Inspecter les colonnes pour créer la table

Pour importer un contenu CSV dans PostgreSQL, il faut que la table et ses colonnes existent au préalable, ce qui peut être fait soit avec CREATE TABLE , soit graphiquement avec pgAdmin (ou d’autres outils).

L’enjeu est de déterminer le bon type à appliquer à chaque colonne, parmi les 3 principaux : integer (nombre entier), real (nombre décimal) et character varying (texte). S’il n’y a pas de méta-données sur le CSV, il faut les déterminer, soit par inspection des données, soit par itération d’essai/erreur :

tenter integer , qui refusera tout nombre décimal non entier et tout texte ; sinon :
tenter real , qui refusera tout texte (mais accepterait des nombres entiers) ; sinon :
utiliser character varying , qui acceptera tout (puisque toute valeur du CSV est un texte valide)

Tant que l’import réussit, il est possible (et fréquent) de retravailler les colonnes par la suite, comme par exemple, de générer une colonne integer à partir d’une colonne texte.

Exemple

En lisant le content CSV suivant :

id,name,evol,count
1,toto,43.3,5
2,tata,11,8
3,titi,8.1,0

On peut en déduire la table à créer :

CREATE TABLE espon_labour_market_sector
(
  id integer,
  name character varying,
  evol real,
  count integer
)

QUESTION CBA SQL de création de la table pour le CSV précédent

CC Importer un CSV dans le PostgreSQL local avec COPY FROM

PostgreSQL permet d’importer le contenu d’un fichier CSV dans une table, avec l’instruction COPY FROM , qui s’utilise le plus couramment comme ceci :

COPY nom_de_la_table FROM ’/chemin/du/fichier.csv’ CSV HEADER

S’assurer que le CSV est dans un répertoire local accessible par le serveur PostgreSQL, et récupérer le chemin absolu (sous Windows, il serait de la forme C:\dossier\...\espon-labour-market-sectors.csv ).

QUESTION CCA SQL de l’instruction COPY FROM pour importer le fichier CSV précédent

QUESTION CCB SQL de l’instruction COPY FROM dans le cas où l’on aurait des valeurs séparées par un point virgule au lieu d’une virgule (d’après la documentation PostgreSQL)

CD Importer un CSV dans le PostgreSQL distant avec \copy

Le shell PostgreSQL nommé psql offre des commandes propres, c’est-à-dire en amont du SQL, il gère lui-même. C’est le cas de la commande \copy qui fonctionne avec la même syntaxe que SQL COPY, mais en utilisant un fichier local au client (et non pas au serveur)

Pour reprendre l’exemple précédent, on taperait ceci dans psql :

\copy nom_de_la_table FROM ’/chemin/du/fichier.csv’ CSV HEADER

CE Générer la géométrie d’après X/Y

On peut rencontrer des CSV géolocalisés , qui sont des CSV normaux qu’on importe en base mais à partir desquels on va pouvoir générer une colonne géométrique en utilisant des champs X et Y .

Télécharger le jeu de données suivant (format CSV) : https://www.data.gouv.fr/fr/datasets/liste-des-organismes-publics-culturels-geolocalises-571911/

QUESTION CEA Le caractère de délimitation utilisé dans ce CSV

Importer le CSV selon la méthode précédente
- dans la table etablissements_culturels
- première colonne nommée organisme
- noms de colonnes en minuscules

QUESTION CEB Nom des champs X et Y, respectivement

QUESTION CEC SRID utilisé pour les coordonnées (n’est pas précisé sur data.gouv.fr, donc à deviner) ; il s’agit de l’identifiant numérique

Ajouter la colonne geom de type GEOMETRY(Point,srid) où srid est la réponse CEC

QUESTION CED Code SQL de l’instruction ALTER TABLE

Peupler le champ geom d’après les colonnes X et Y grâce à ST_MakePoint

QUESTION CEE Code SQL de l’instruction UPDATE

Note : Ces opérations sont l’équivalent dans QGIS de l’ajout de couche CSV (Couche → Ajouter une couche de texte délimité ), permettant de l’enregistrer ensuite en Shapefile ou de l’exploiter dans le SIG.

CF Export CSV : la méthode du copier-coller avec pgAdmin

Cette méthode peut s’appliquer lorsque le volume de données n’est pas trop grand : jusqu’à quelques dizaines de milliers d’enregistrements, voire davantage en fonction de la mémoire vive du poste de travail.

Le panneau de sortie de pgAdmin, quand tout est sélectionné screen-pgadmin-selectall.png 400 right

Dans pgAdmin, exécuter :

SELECT * FROM etablissements_culturels

Cliquer dans le gris en haut à gauche du tableau, de façon à sélectionner toutes les lignes et toutes les colonnes
Copier avec Ctrl+C
Ouvrir un tableur LibreOffice vierge et tout sélectionner (tout le blanc) de la même façon
Coller avec Ctrl+V : une fenêtre s’affiche pour importer le contenu CSV

QUESTION CFA Le caractère de délimitation à utiliser (pour tout copié-collé depuis pgAdmin)

QUESTION CFB Capture de la fenêtre LibreOffice avec le haut du tableau

Ici, le contenu CSV a transité par le presse-papier directement vers le tableur. On aurait aussi bien pu l’enregistrer dans un fichier .csv avec un éditeur de texte.

CG Export CSV : la méthode robuste avec COPY TO

De la même façon qu’avec COPY FROM, l’instruction COPY TO permet d’exporter une table vers un fichier CSV, avec la syntaxe de base suivante :

COPY nom_de_la_table TO ’/chemin/du/fichier.csv’ CSV HEADER

La variante \copy , comme on l’a vu précédemment avec \copy nom_de_la_table FROM ... , fonctionne aussi pour l’export d’une table dans un fichier CSV local au client :

\copy nom_de_la_table to ’/chemin/du/fichier/local.csv’ CSV HEADER

QUESTION CGA Instruction COPY qui permettrait d’exporter la supposée table big_data_table dans le fichier /tmp/big_data_table.csv en utilisant le délimiteur * et avec les textes dans l’encoding iso-8859-1 (l’instruction n’est pas à exécuter, c’est une question théorique)

CH Stratégie d’import CSV : production d’un shapefile intermédiaire avec QGIS

Le SIG QGIS permet d’exploiter directement les CSV avec une interface riche en options, qui accepte non seulement des points en colonnes X et Y , mais aussi du WKT. Cet écran est disponible sous le menu Couche → Ajouter une couche de texte délimité .

À partir de là, on peut importer la couche dans la base PostGIS :

Soit enregistrant la couche en Shapefile (clic droit sur la couche puis Sauvegarder sous ), pour ensuite l’importer avec n’importe quelle méthode (shp2pgsql-gui , shp2pgsql , ogr2gui , Gestionnaire de bases de données QGIS ...)
Soit en important dans PostGIS la couche QGIS en tant que telle (c’est bien une couche QGIS mais sa source est l’interprétation particulière d’un fichier CSV), avec le Gestionnaire de bases de données QGIS tel que vu précédent dans AC )

PostGIS › Import/export de données vecteurs

Objectifs du module

À retenir par coeur