Intégrer la dernière version de TAXREF à la base de données grace aux « Foreign Data Wrapper »

• Mise à jour du 6 novembre 2018 pour conformité avec TAXREF v12 (changement de nom du fichier source 🙂 )
• Mise à jour du 19 janvier 2018 pour conformité avec TAXREF v11
• Mise à jour du 28 juin 2017 avec ajout des colonnes sa & ta (TAXREF v10)
• Mise à jour du 4 juillet 2016 avec ajout de la colonne cd_sup (TAXREF v9)

Téléchargement de taxref :

https://inpn.mnhn.fr/telechargement/referentielEspece/taxref/12.0/menu#

CREATE EXTENSION file_fdw;

CREATE SERVER inpn FOREIGN DATA WRAPPER file_fdw;

CREATE SCHEMA fdw;

CREATE FOREIGN TABLE fdw.taxref_v12 (regne character varying, phylum character varying, classe character varying, ordre character varying, 
famille character varying, sous_famille character varying, tribu character varying, group1_inpn character varying, group2_inpn character varying, 
cd_nom character varying, cd_taxsup character varying, cd_sup character varying, cd_ref character varying, rang character varying, 
lb_nom character varying, lb_auteur character varying, nom_complet character varying, nom_complet_html character varying, nom_valide character varying,
nom_vern character varying, nom_vern_eng character varying, habitat character varying, fr character varying, gf character varying, 
mar character varying, gua character varying, sm character varying, sb character varying, spm character varying, may character varying, 
epa character varying, reu character varying, sa character varying, ta character varying, taaf character varying,
pf character varying, nc character varying, wf character varying, cli character varying, url character varying) SERVER inpn
OPTIONS (format 'csv', header 'true', filename '/tmp/TAXREFv12.txt', delimiter E'\t', null '');

Et voilà; nous pouvons faire une requête SQL sur notre fichier txt!

SELECT * FROM fdw.taxref_v12 WHERE ordre ILIKE 'ODONATA' limit 10;

Le fichier doit être lisible par postgres.

Un problème d’encodage a été rencontré sur les versions de TAXREF antérieures à la v10 de 2016, il a été résolu avec la commande

iconv -f LATIN1 -t UTF8 TAXREFv10.txt > TAXREFv10_utf8.txt

Les perspectives sont très intéressantes :

• moissonnage des données externes (cas d’une base de données partenaire mysql)
• interrogation de services web (json, xml)
• et pourquoi pas des connecteurs vers les formats de fichier SIG ?

Ressources :

• PostgreSQL Up and Runing
• http://www.postgresonline.com/journal/categories/70-fdws
• Correction de TaxRef V6 par Jérôme Seigneuret :
  • http://blog2fouine.free.fr/index.php/blog2fouine.free.fr/tag/taxref/

Comment lister les tyopes enum utilisé dans ma base de données, dans quel schéma sont-ils définis et qsuelles sont les valeurs de leurs différenst éléments ?

Avec PostgreSQL 9.x :

SELECT nspname AS schema, typname AS nom , string_agg(enumlabel::text, ', '::text) AS definition
  FROM pg_enum JOIN pg_type ON enumtypid = pg_type.oid
  JOIN pg_namespace ON pg_type.typnamespace = pg_namespace.oid
 WHERE typcategory = 'E'
 GROUP BY nspname, typname
 ORDER BY 1,2

Avec PostgreSQL 8.5 :

SELECT nspname AS schema, typname AS nom , array_to_string(array_agg(enumlabel::text),', ') AS definition
  FROM pg_enum JOIN pg_type ON enumtypid = pg_type.oid
  JOIN pg_namespace ON pg_type.typnamespace = pg_namespace.oid
 WHERE typcategory = 'E'
 GROUP BY nspname, typname
 ORDER BY 1,2

En voulant mettre à jour des données d'altitude d'une distribution de points, nous avons rencontré une erreur :

La requête suivante :

UPDATE export.tous_point_espece_selon_format_esri SET pente_mnt = ST_VALUE(rast, geometrie, true)
FROM raster.pente_mnt30
WHERE code_insee LIKE '30%' AND st_intersects(geometrie, rast)

retournait ceci :

ERREUR: syntaxe en entrée invalide pour l'entier : « NaN »
CONTEXTE : PL/pgSQL function "st_world2rastercoordx" lors de la conversion de la valeur de retour au type de retour de la fonction PL/pgSQL function "st_value" line 13 at RETURN

La solution est venue de la liste postgis-users et particulièrement de Bborie Park (http://postgis.17.n6.nabble.com/st-value-st-world2rastercoordx-error-td4999247.html).

Certaines geometries étaient nulles. Il faut donc ajouter cette condition à la claude WHERE (AND geometrie IS NOT NULL), ou corriger les données concernées.

Bborie a créé un ticket suite à ce problème pour tester dans la fonction l'existence de la géometrie.

A vouloir aller trop vite on oublie de parcourir la documentation…

Lors d'un précédent article nous avons chargé "simplement" un raster dans la base de donnée pour l'exploiter ensuite.

Les requêtes fonctionnaient mais avec des temps de réponses qui rendent impossible une exploitation "en live " du MNT.

Après avoir posté sur la liste postgis-users au sujet d'une erreur rencontrée, on m'a conseillé de réaliser une jointure simple avec une condition de jointure (ST_Intersects(geometrie, rast)) plutôt qu'une jointure croisée.

Donc faire ceci :

UPDATE export.tous_point_espece_selon_format_esri SET altitude_mnt = ST_VALUE(rast, geometrie, true)
FROM raster.mnt30
WHERE code_insee LIKE '30%'
AND ST_Intersects(geometrie, rast)

plutôt que cela :

UPDATE export.tous_point_espece_selon_format_esri SET altitude_mnt = ST_VALUE(rast, geometrie, true)
FROM raster.mnt30
WHERE code_insee LIKE '30%'

Dans un premier temps cela n'a fait qu'augmenter le temps d'exécution de la requête déjà passablement longue.

Cela vient du fait que le raster a été initialement stocké comme une seule tuile (un table contenant une seule ligne).

Nous avons réintégré le raster à la base de donnée en le découpant en tuiles de 100×100 pixels avec cette commande :

raster2pgsql -s 2154 -C -I -r -M -F -t 100x100 DEPT30.asc raster.mnt30 | psql -h localhost -d odo_pap -U dba

Cela génère une table contenant environ 2400 lignes (tuiles) indexées.

Le résultat est sans appel, la mise à jour de l'altitude de 19000 données ponctuelles prend 31 secondes sur une machine de base contre plusieurs heures précédemment.

Liens utiles :

• http://postgis.refractions.net/docs/using_raster.xml.html#RT_Raster_Loader
• http://postgis.refractions.net/docs/RT_reference.html#Raster_Processing

Présentation réalisée à l'occasion du 6ème FORUM TIC de l'ATEN

Voir le diaporama

Les demandes de données sont fréquentes et il est intéressant d'une part, de se faciliter la vie, et d'autre part de garder une trace de ces exports afin de valoriser le travail réalisé par les naturalistes et de les tenir informés de l'utilisation qui est faite de leurs données.

On créer pour cela deux tables :

• une destinée à recueillir l'emprise des zones sur lesquelles des demandes de données sont réalisées

CREATE TABLE export.zone_export_donnee
(
  id_zone serial NOT NULL,
  motif character varying(200),
  date date NOT NULL DEFAULT ('now'::text)::date,
  geometrie geometry,
  id_structure integer,
  CONSTRAINT pk_zone_export PRIMARY KEY (id_zone ),
  CONSTRAINT enforce_dims_geometrie CHECK (st_ndims(geometrie) = 2),
  CONSTRAINT enforce_geotype_geometrie CHECK (geometrytype(geometrie) = 'MULTIPOLYGON'::text OR geometrie IS NULL),
  CONSTRAINT enforce_srid_geometrie CHECK (st_srid(geometrie) = 2154)
)
WITH (
  OIDS=FALSE
);
COMMENT ON TABLE export.zone_export_donnee
  IS 'Table utilisée dans un projet Qgis pour définir des emprises sur lesquelles exporter les données. Assure une traçabilité des export dans la table export.structure_a_recu_donnee';

• la seconde pour garder la trace des envois

CREATE TABLE export.structure_a_recu_donnee
(
  date_export date NOT NULL,
  id_entite character varying NOT NULL,
  id_structure integer NOT NULL,
  CONSTRAINT structure_a_recu_ese_pk PRIMARY KEY (date_export , id_entite , id_structure ),
  CONSTRAINT id_structure_reference_structure FOREIGN KEY (id_structure)
      REFERENCES md.structure (id_structure) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
COMMENT ON TABLE export.structure_a_recu_donnee
  IS 'Trace l''ensemble des données envoyées à une structure, ainsi que la date d''envoi.';

Enfin, on crée un script (export_atlas_par_zone_export.sh) qui sera utilisé dans une action QGis :

mkdir -p "export"/$1/$3
cd "export"/$1/$3
pgsql2shp -f point_atlas_odopap_lr.shp -h adr.es.sse.ip -u nom_utilisateur -g geometrie nom_bdd "SELECT export.tous_point_espece_selon_format_esri.* FROM export.tous_point_espece_selon_format_esri, export.zone_export_donnee WHERE st_intersects(export.tous_point_espece_selon_format_esri.geometrie, export.zone_export_donnee.geometrie) AND id_zone='$2';"
psql -c "INSERT INTO export.structure_a_recu_donnee(date_export, id_entite, id_structure) SELECT current_date, id_entite, id_structure FROM export.tous_point_espece_selon_format_esri, export.zone_export_donnee WHERE st_intersects(export.tous_point_espece_selon_format_esri.geometrie, export.zone_export_donnee.geometrie) AND id_zone='$2'" -h adr.es.sse.ip -U nom_utilisateur nom_bdd 
echo "export terminé!"

L'action unix est créée comme ceci dans QGis :

./export_atlas_par_zone_export.sh %id_structure %id_zone %date

Nous venons d'installer postgis 2.0 sur la base de développement de l'atlas des papillons de jour et des libellules du Languedoc-Roussillon.

Cette fonctionalité des plus intéressantes nous intéresse particulièrement pour caractériser l'écologie des espèces et décrire leurs préférences écologiques.

Un premier test nous a conduit à importer dans la base de données le MNT du département de la lozère issu de la BD_TOPO de l'IGN et ensuite à afficher l'altitude des données de ce département.

Résultat concluant!

raster2pgsql -s 2154 mnt_48.asc raster.mnt48 > mnt48.sql
psql odo_pap -> i mnt48.sql
select ST_Value(rast, geometrie, true) from rater.mnt48, observation WHERE observation.codeçinsee LIKE '48%';

Voir  l'article "PostGIS raster, suite" pour comprendre les raisons du barré.

Ressources

• http://linfiniti.com/2012/05/installing-postgis-2-0-on-ubuntu/
• http://postgis.org/documentation/manual-2.0/using_raster.xml.html
• Présentation réalisée par Pierre Racine à l'hiver 2011

Voilà quelques modules (Version 7 de Drupal) qui ont permis de réaliser le site Allo'Grenouille* (www.allo-grenouille.fr), enquête participative sur la répartition des grenouilles "vertes" de la Région Languedoc-Roussillon, visant à collecter des observations sonores (chants).

* Ce site a été élaboré dans le cadre du programme REPERE (Réseau d'Échange et de projets sur le Pilotage de la Recherche et l'Expertise) du projet MEDINA (Conservation de la biodiversité en région MEDiterranéenne: une INterface recherche – gestion pour identifier les enjeux et Alimenter le processus de décision).

Le site sig.cenlr.org est devenue difficilement navigable.

Initialement utilisé comme un bloc-note ou un registre de notre travail géomaticien au sein du CEN L-R, le wiki mis en place en 2008 s'est étoffé, a beaucoup grossi. Ce nouveau site s'appelle si.cenlr.org, pour garder l'ancien site accessible et pour mieux correspondre au contenu qui ne concerne pas que la partie géographique du SI.

L'organisation du contenu y ait peu évidente et il devient difficile de parcourir le site. Par ailleurs, la forme utilisée rend peu compte de son dynamisme et des fréquents ajouts de contenu.

Ce site est mis en oeuvre avec de drupal, sur une base de données postgresql.

Petit à petit, nous allons tenter de migrer le contenu du wiki (sig.cenlr.org) vers ce site.

Bonne visite