mercredi 27 mai 2015

QGIS et PostgreSQL : des données sur des cartes

Avec le couple QGIS/PostgreSQL (et son extension PostGIS), nous avons agrégé des données démographiques pour les projeter ensuite sur une carte de l'Europe.

QGIS est un très bel outil pour la cartographie l'analyse des données géospatiales. Ces dernières années, les nouvelles versions se sont succédé à un rythme rapide du fait d'une communauté très active de développeurs et d'utilisateurs.
La difficulté de travailler avec un tel outil tient plutôt à la forme la plus fréquemment disponible des données, un ensemble de fichiers (d'extensions .shp, .shx, .dbf, .proj). Ces fichiers (shapefile pour faire court) sont faits pour l'échange mais pas du tout pour le travail en équipe, simultané, sur un jeu de données cartographiques. C'est un peu comme si on revenait à l'échange de fichiers de traitement de texte par mail, alors que les outils en ligne sont bien plus efficaces pour écrire à quatre mains ou gérer les versions successives d'un document. De plus, le shapefile est assez primaire sur les champs attributaires et rend délicat l'agrégation de données d'origines diverses.
Tout ça plaide pour coupler QGIS à une vraie base de données géospatiale, comme PostgreSQL/PostGIS. PostgreSQL est un SGBDR, PostGIS désigne ses extensions géospatiales.

Le connecteur PostgreSQL de QGIS

Créer une base PostGIS


Il est nécessaire de créer une nouvelle base et d'y ajouter les extensions PostGIS, on résume ici le manuel de Debian. Dans un terminal, après avoir pris les droits de l'utilisateur 'postgres', on tape:


createdb --o
createlang plpgsql -d

psql
-f /usr/share/postgresql/*/contrib/postgis-*/postgis.sql
psql
-f /usr/share/postgresql/*/contrib/postgis-*/spatial_ref_sys.sql
psql

grant all on geometry_columns to
;
grant select on spatial_ref_sys to
;
\q
# en tant qu'utilisateur normal :
psql -h
-U -d


 
QGIS rend visibles sur la carte les statistiques de toutes origines

Importer les données dans Postgres


Pour accueillir la carte des entités NUTS récupérée sur Eurostat (au format d'échange Shapefile), on emploie shp2pgsql, en rajoutant, dans le fichier de commandes sql qu'il génère, nos propres contraintes, notamment pour la clé primaire. On fait donc, dans un terminal :

shp2pgsql -c -s 3035 /chemin/vers/le/shapefile.shp > /chemin/vers/le/fichier/de/sortie.sql


La projection est EPSG 3035 (obtenue par réexport de QGIS, où l'on a aussi préparé les champs), bien adaptée à la représentation de l'ensemble de l'Europe. Plutôt que d'exécuter le fichier sql (psql < sortie.sql), on l'ouvre dans un éditeur et modifie les commandes de création de table.

CREATE TABLE eEU (
    niveau SMALLINT NOT NULL,
    nuts_id CHAR(5) PRIMARY KEY,
    shap_len DOUBLE PRECISION,
    shap_are DOUBLE PRECISION,
    cod_p CHAR(2) NOT NULL,
);
//On remarque la ligne qui rajoute la colonne de géométrie :
SELECT AddGeometryColumn('', 'eEU','geom',3035,'MULTIPOLYGON',2);


À présent, on a une carte mais il lui manque des données attributaires un peu consistantes. C'est là où on ressort le fichier des entités LAU1 et LAU2, récupéré aussi sur Eurostat. Ce fichier étant une vue exportée, il ne correspond pas nécessairement à nos besoins, ni aux critères pour la meilleure cohérence des données. Dans un premier temps, on l'a normalisé, en passant du premier schéma logique à un schéma à trois tables.

lau2(idnuts3,lau1,lau2,nom,nomlat,area) -->

lau2_2010 (nuts3,lau2,nom,area) PK (nuts3,lau2)
lau1_2010 (nuts3,lau1,nom,area) PK (nuts3,lau1)
lau_12 (nuts3,lau1,lau2) PK (nuts3,lau1,lau2)


Le modèle est peut-être plus difficile à appréhender, mais il est moins « troué » et il peut simplifier la création de vues, comme on va le voir. Le code pour créer les tables est simple et ennuyeux, on l'épargnera au lecteur.

Une vue sur la population


On a importé dans Postgres une table de population et une autre sur les entités nuts3. Puis, on a créé une vue qui calcule la surface de ces entités, à partir de ses subdivisions, et calcule la densité, en fonction de la population. La requête est sensiblement plus rapide qu'avec le schéma précédent, alors qu'il y a plus de tables.

CREATE OR REPLACE VIEW nuts3_hab_km2 AS
SELECT nuts3_2010.nuts3,nom,round(km2,2) AS superficie_km2,population,round(population/km2) AS hab_km2,pnuts2 AS parent FROM (
  select nuts3,sum(area)/1000000 AS km2
  from lau22_2010 where nuts3 not in (
    select nuts3 from lau22_2010
    where area IS NULL
    )
  group by nuts3
  union
  select nuts3,sum(area)/1000000 AS km2
  from lau1_2010 where nuts3 not in (
    select nuts3 from lau1_2010
    where area IS NULL
    )
  group by nuts3
) AS nuts_surf
JOIN nuts3_2010
ON nuts3_2010.nuts3=nuts_surf.nuts3
JOIN pop
ON nuts_id=nuts3_2010.nuts3
;

La gueule de la table résultante est intéressante.

select * from nuts3_hab_km2 limit 4;

 nuts3 |        nom         | superficie_km2 | population | hab_km2 | parent
-------+--------------------+----------------+------------+---------+--------
 ITC49 | Lodi    |    782.20    |    229082    |    293    |    ITC4
 DE22B | Straubing-Bogen    |    1201.90    |    97323    |    81    |    DE22
 EL431    |    Irakleio    |    2641.22    |    |    |    EL43
 UKG21 | Telford and Wrekin |    290.31    |    168713    |    581    |    UKG2


Jointure dans QGIS


La récréation a sonné, on lance QGIS, connecte la base Postgres (icône d'éléphant) et importe les tables utiles (carte, population, et la vue nuts3_hab_km2 fraîchement créée). Lors de cette opération, il est nécessaire de choisir un champ de clé primaire valide (ici nuts3).
Ensuite, on ouvre les propriétés de la couche multipolygones et on ajoute une jointure vectorielle (onglet jointures dans la fenêtre de propriétés) entre la table et la carte. C'est fini !

À partir de cet exemple, on peut imaginer la suite. Des données combinées entre elles avec du code SQL, des couches importées de OSM ou créées par les rédacteurs... Avec le réglage des droits d'accès, des sauvegardes, Postgres est bien fait pour bosser en groupe.

Aucun commentaire:

Publier un commentaire