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.

mardi 12 mai 2015

Les data au tamis de PostgreSQL

Les données pour le journalisme quantitatif, qu'elles soient le produit de scraping ou issues d'institutions, sont souvent défectueuses. Des variations de format (point ou virgule pour la marque décimale), de type ('1' est un caractère, tandis que 1 est de type numérique), les espaces en fin de champ ou mélangés aux tabulations, les champs nuls et autres coquilles rendent leur exploitation statistique ou cartographique hasardeuse.

Le tableur est assez limité mais néanmoins utile et en tous cas nécessaire pour préparer les fichiers. Les outils en ligne gratuits, comme Table Fusion, impliquent un partage des données avec le fournisseur et une dépendance à son égard. On va voir ici comment les systèmes de gestion de bases de données relationnelles (SGBDR) peuvent aider.

L'idée est de créer des tables temporaires pour accueillir les données à valider, avant de les intégrer dans des tables définitives, qui auront toutes les contraintes référentielles voulues. Les exemples sont donnés ici avec PostgreSQL mais ils devraient fonctionner sur d'autres  SGBDR.

Contraintes de type

 

On a travaillé sur le fichier des entités statistiques LAU2 de Eurostat. Le tableau, un fichier bureautique, a été enregistré sans les entêtes de colonnes et au format TSV (valeurs séparées par des tabulations), avec l'encodage de caractères de la base (UTF-8, ici). Avant d'importer ce fichier dans la base, on doit créer une table pour l'accueillir. Il faut réfléchir au type de données que contiendra chaque colonne (chaîne de caractères, de longueur fixe ou variable, numériques, entiers ou à virgule, avec quelle précision, date, booléen ... ). Le plus contraignant sera le mieux.

CREATE TABLE lau2_2010 (
idnuts3 char(5), /* les identifiants NUTS3 font exactement cinq caractères*/
lau1 varchar(15), /*les identifiants LAU1 font au maximum 15 caractères*/
lau2 varchar(15),
nom varchar, /* les noms sont de longueur variable et non spécifiée - ce dernier point est une particularité de PostgreSQL*/
area numeric
) ;

L'opération de copie du fichier TSV échoue si une seule ligne viole une contrainte. En général, l'erreur attire l'attention sur un problème commun à de nombreuses autres lignes que l'on doit résoudre avec le tableur. Une difficulté rencontrée tenait à la variété des indicateurs de données absentes, tantôt n.c.a., n.c.a, NA, etc. Sur un fichier de près de 120000 lignes, il est improbable de les déceler dès la première lecture. Les données non existantes doivent être traduites par \N pour être admises dans Postgres. Attention, le tableur peut ajouter des erreurs, au moment de l'ouverture du fichier source (s'il n'est pas dans son format naturel). On a vu 00AM et 00PM, qui sont des codes en vigueur au Royaume-Uni, interprétés en un format d'heures : minuit et midi. Même chose pour 95E15, vu comme la notation du nombre 95000000000000000.
Une fois les contraintes de type respectées, l'opération copie les 120000 lignes en deux secondes.

\copy into essai_temp from /chemin/vers/fichier.tsv

 

 À la recherche du schéma logique

 

Une fois la table peuplée, on peut rechercher des incohérences plus subtiles et chercher à comprendre le schéma logique de la table. La recherche des champs vides et les clés candidates sont au menu.

SELECT idnuts3,lau1,nom FROM lau2_2010
WHERE lau2 IS NULL;

SELECT idnuts3,lau2,nom FROM lau2_2010
WHERE lau1 IS NULL
AND lau2 IS NOT NULL;

Ces requêtes renvoient les lignes dont LAU2 est vide et dont LAU1 est vide et pas LAU2, respectivement. On comprend ainsi que certains pays possèdent des entités LAU1 qui ne contiennent aucune LAU2 (la Grèce et le Danemark), tandis que d'autres ont rangé des entités LAU2 directement sous les NUTS3, sans LAU1. Cette table est sans doute une vue, une table virtuelle produite par une requête, facile à exploiter mais pas idéale pour organiser les données dans la base. Cette table pourrait être renormalisée, décomposée en tables dépourvues de redondances ou de champs nuls.

On cherche si un identifiant est unique au niveau européen, ce qui sera bien utile pour faire des jointures spatiales dans un logiciel de cartographie. L'identifiant LAU2 est-il unique au niveau européen ? Une requête à auto-jointure affiche les lau2 communs à des entités dont les noms diffèrent.

SELECT L1.nom,L1.lau2,L2.nom
FROM lau2_2010 AS L1
JOIN lau2_2010 AS L2
ON L1.lau2 = L2.lau2
WHERE L1.nom < L2.nom;

La liste renvoyée n'est pas nulle, elle est même longue (21178 entités). LAU2 ne peut être retenu comme identifiant unique, même en ignorant les lignes où il est vide.

Le couple idnuts3-lau2 est-il alors une clé candidate ? Cherchons s'il existe plusieurs noms pour ce couple d'attributs.

La directive GROUP BY regroupe les lignes avec des attributs communs. C'est très utile pour faire des statistiques mais aussi pour trouver d'éventuels doublons dans une table brute d'importation.

SELECT L1.idnuts3,L1.lau2
FROM (
SELECT * FROM lau2_2010
WHERE lau1 IS NOT NULL
AND idnuts3 IS NOT NULL
) AS L1
GROUP BY L1.idnuts3,L1.lau2
HAVING count (*) > 1
;

La requête montre les pays qui n'ont pas d'entités lau2 (Danemark et Grèce), ce qui est attendu, mais aussi une ligne, qui s'avère être un doublon. Une ligne fautive sur 120000, introduite lors des manipulations sur le tableur ou peut-être d'origine Eurostat.

Une autre requête confirme, par son résultat vide, que le couple idnuts3-lau2 est un identifiant possible, pour les lignes où lau2 n'est pas vide. Ici, on compare les noms.

SELECT R1.idnuts3,R1.lau2,R1.nom AS nom1,R2.nom AS nom2
FROM (
SELECT * FROM lau2_2010 WHERE lau2 IS NOT NULL
AND idnuts3 IS NOT NULL
) AS R1
JOIN (
SELECT * FROM lau2_2010 WHERE lau2 IS NOT NULL
AND idnuts3 IS NOT NULL
) AS R2
ON R1.idnuts3 = R2.idnuts3
WHERE R1.lau2 = R2.lau2
AND R1.nom < R2.nom
;

 idnuts3 | lau2 | nom1 | nom2
---------+------+------+------
(0 ligne)

 

Au croisement des données

 

Une jointure sur une autre table, qui recense les entités NUTS niveau 1, 2 et 3, met en évidence trois identifiants NUTS3 non répertoriés.

SELECT DISTINCT idnuts3
FROM lau2_2010
WHERE idnuts3 NOT IN (
SELECT nuts_id FROM nuts2010
);

idnuts3
---------
N_A 
DEZZZ
FR215
(3 lignes)

N_A est encore une variante pour un champ vide, qui a échappé au tableur. DEZZZ désigne une 'Extra-Regio NUTS 3', ligne absente de la table nuts2010 et que l'on rajoute. FR215 est sans doute une coquille. Les communes françaises sous cet identifiant sont situées en Haute-Marne, soit FR214, dans la version 2010 de NUTS3. On rétablit la situation avec :

UPDATE lau2_2010
SET idnuts3 = 'FR214'
WHERE idnuts3 = 'FR215' ;

Des fonctions statistiques simples du SGBD permettent des vérifications croisées entre plusieurs sources. Avec une requête, on a calculé la surface des entités NUTS3, à partir de celle des entités LAU2 qui les constituent, et la densité de peuplement de chaque NUTS3, à partir d'une jointure sur une table de population d'origine Eurostat. En fin de requête, on demande un classement pour montrer les entités les plus densément peuplées en haut de la liste.

SELECT surf.nuts_id,surf.nom,round(1000000*(population/surface)) AS densite
FROM (
SELECT n10.nom,n10.nuts_id,sum(l10.area) AS surface
FROM (
SELECT * FROM nuts2010 EXCEPT (
SELECT * FROM nuts2010
WHERE niveau =3 AND nuts_id IN (
SELECT idnuts3 FROM lau2_2010 WHERE area IS NULL
))) AS n10
JOIN lau2_2010 AS l10
ON l10.idnuts3=n10.nuts_id
GROUP BY n10.nuts_id,n10.nom) AS surf
JOIN pop ON pop.nuts_id = surf.nuts_id
WHERE pop.population IS NOT NULL
GROUP BY surf.nuts_id,surf.nom,surf.surface,pop.population
ORDER BY densite DESC
LIMIT 50;

Le problème est apparu immédiatement. Des régions néerlandaises étaient bien plus denses que Paris (qui a plus de 21000 hab/km2). L'anomalie touchait aussi la Suède. Pour ces deux pays, la surface était exprimée en ha et non en m2, comme pour le reste de la base. Un petit correctif a remis les bons ordres de grandeur.

UPDATE lau2_2010
SET area = area * 10000
WHERE idnuts3 LIKE 'SE%' OR idnuts3 LIKE 'NL%';

Enfin, la comparaison des surfaces et des densités obtenues avec d'autres sources, comme Wikipedia, a permis de s'assurer de la cohérence de la base.

Voilà, ces quelques exemples montrent comment une base SQL peut aider une rédaction, un journaliste, à valider un grand ensemble de données.