L’INPN, IUCN et d’autres organismes fournissent un ensemble de référence de données permettant aux naturalistes de travailler sur les espèces de faune et de flore. Pour nos besoins, j’ai créé une base de données exploitant les données fournis.
Pour cela, je travaille avec sous Windows7 avec un serveur PostgreSQL v9.2 (sur poste bureautique) et la Microsoft Excel 2010 pour la manipulation des tableaux
Création de la base de données
La base de données utilisée est nommée ref_naturaliste . J’y ai créé un schéma nommé tmp pour réaliser mes tests.
Toutes les tables intégrés correspondent à leur nom d’extraction (mis au singulier car c’est la convention, en minuscule, sans version et préfixé par le nom de l’organisme qui l’a produit).
-- DROP DATABASE IF EXISTS ref_naturaliste; CREATE DATABASE ref_naturaliste WITH OWNER = jerome ENCODING = 'UTF8';
Attention: les paramètres LC_COLLATE et LC_CTYPE sont à adapter en fonction de votre serveur. Dans mon cas je ne l’ai ai pas ajouté car sous windows c’est l’encodage système qui est utilisé par défault
Création d’un schéma de données temporaire :
CREATE SCHEMA tmp AUTHORIZATION jerome; GRANT ALL ON SCHEMA tmp TO jerome; COMMENT ON SCHEMA tmp IS 'schéma temporaire pour tester l''intégrité des données';
Création du référentiel taxonomiques
L’INPN fourni un ensemble de référence de données permettant au naturaliste de travailler sur les espèces de faune et de flore. Actuellement, j’utilise la version 6 du Référentiel espèces.
Pour les remarques ça se passe ici :
http://inpn.mnhn.fr/contact/contacteznous/topic/taxref
J’ai pour ma part créé une version 6.1 (Version mineur intégrant seulement des correctifs) que je ne peux fournir directement. Je vous présenterai seulement les mises à jour réalisées sur le post INPN – TAXREF – Mise à jour apporté à la version 6 et leur validation le cas échéant par le responsable du projet.
Ajout de la table taxref_statut
Le statut biogéographique informe de la présence sur le territoire.
Suppression des contraintes et suppression de la table le cas échéant:
-- Suppression des contraintes sur les autres tables ALTER TABLE tmp.inpn_taxref DROP CONSTRAINT statut_fr_fk, DROP CONSTRAINT statut_gf_fk , DROP CONSTRAINT statut_mar_fk, DROP CONSTRAINT statut_gua_fk , DROP CONSTRAINT statut_sm_fk , DROP CONSTRAINT statut_sb_fk, DROP CONSTRAINT statut_spm_fk , DROP CONSTRAINT statut_may_fk , DROP CONSTRAINT statut_epa_fk , DROP CONSTRAINT statut_reu_fk, DROP CONSTRAINT statut_taaf_fk, DROP CONSTRAINT statut_pf_fk , DROP CONSTRAINT statut_nc_fk, DROP CONSTRAINT statut_wf_fk , DROP CONSTRAINT statut_cli_fk; -- Suppression de la table DROP TABLE IF EXISTS inpn_taxref_statut;
Création de la table:
-- Suppression de la table DROP TABLE IF EXISTS inpn_taxref_statut; -- Création de la table CREATE TABLE inpn_taxref_statut( code CHAR(1) PRIMARY KEY, description VARCHAR(50)); COMMENT ON COLUMN inpn_taxref_statut.code IS 'Code du statut'; COMMENT ON COLUMN inpn_taxref_statut.description IS 'Libellé du statut biogéographique'; COMMENT ON TABLE inpn_taxref_statut IS 'Liste des statuts biogéographiques pour les territoires français';
Injection des données de statut
-- Insertion des données INSERT INTO inpn_taxref_statut (code, description) VALUES ('P','Présent (indigène ou indéterminé'), ('A','Absent'), ('B','Ocassionnel'), ('C','Cryptogène'), ('D','Douteux'), ('E','Endémique'), ('F','Trouvé en fouille'), ('I','Introduit'), ('J','Introduit envahissant'), ('M','Introduit non établi (dont domestique)'), ('S','Subendémique'), ('W','Disparu'), ('X','Éteint'), ('Y','Introduit éteint/ disparu'), ('Z','Endémique éteint'), ('Q','Mentionné par erreur');
Création de la table des rang
Le rang taxonomique informe sur le classement hiérarchique des taxons.
Suppression des contraintes si nécessaire:
ALTER TABLE tmp.inpn_taxref DROP CONSTRAINT rang_est_inpn_taxref_rang_code_fk;
Création de la table:
-- Suppression de la table DROP TABLE IF EXISTS inpn_taxref_rang; -- Création de la table CREATE TABLE inpn_taxref_rang ( code VARCHAR(4) PRIMARY KEY NOT NULL, description VARCHAR(50), rg_level INTEGER, rg_categorie INTEGER ); COMMENT ON TABLE inpn_taxref_rang IS 'Liste des rangs taxonomiques utilisés pour définir la hiérarchie des taxons'; COMMENT ON COLUMN inpn_taxref_rang.code IS 'Code du rang'; COMMENT ON COLUMN inpn_taxref_rang.description IS 'Libellé du rang taxonomique';
Injection des données de statut
INSERT INTO inpn_taxref_rang(code, description, rg_level, rg_categorie) VALUES ('LIFE','Arbre du vivant',0,0), ('Dumm','Domaine',10,1), ('SPRG','Super-Règne',10,1), ('KD','Règne',20,1), ('SSRG','Sous-Règne',30,1), ('IFRG','Infra-Règne',35,1), ('DV','Division',40,1), ('PH','Phylum/Enbramchement',40,1), ('SBDV','Sous-division',50,1), ('SBPH','Sous-Phylum',50,1), ('IFPH','Infra-Phylum',55,1), ('SPCL','Super-Classe',60,1), ('CLAD','Cladus',70,1), ('CL','Classe',80,1), ('SBCL','Sous-Classe',90,1), ('IFCL','Infra-classe',100,1), ('LEG','Legio',110,1), ('SPOR','Super-Ordre',120,1), ('COH','Cohorte',130,1), ('OR','Ordre',140,1), ('SBOR','Sous-Ordre',150,1), ('IFOR','Infra-Ordre',160,1), ('SPFM','Super-Famille',170,1), ('FM','Famille',180,1), ('SBFM','Sous-Famille',190,1), ('TR','Tribu',200,1), ('SSTR','Sous-Tribu',210,1), ('GN','Genre',220,2), ('SSGN','Sous-Genre',230,2), ('SC','Section',240,2), ('SBSC','Sous-Section',250,2), ('SER','Série',260,2), ('SSER','Sous-Série',270,2), ('AGES','Agrégat',280,3), ('ES','Espèce',290,3), ('SMES','Semi-Espèce',300,4), ('MES','Micro-Espèce',310,4), ('SSES','Sous-Espèce',320,4), ('NAT','Natio',322,4), ('HYB','Hybride',325,3), ('CVAR','Convariété',330,4), ('VAR','Variété',340,4), ('SVAR','Sous-Variété',350,4), ('FO','Forme',360,4), ('SSFO','Sous-Forme',370,4), ('FOES','Forma species',380,4), ('LIN','Linea',390,4), ('CLO','Clône',400,4), ('CAR', 'Cultivar', 405, 4), ('RACE','Race',410,4), ('MO','Morpha',420,4), ('AB','Abberatio',430,4);
Création de la table taxref :
Table contenant les principales informations sur la taxonomie des espèces.
Création de la table
CREATE TABLE tmp.inpn_taxref ( regne VARCHAR, phylum VARCHAR, classe VARCHAR, ordre VARCHAR, famille VARCHAR, cd_nom INTEGER NOT NULL, cd_taxsup INTEGER, cd_ref INTEGER, rang VARCHAR(4), lb_nom VARCHAR, lb_auteur VARCHAR, nom_complet VARCHAR, nom_valide VARCHAR, nom_vern VARCHAR, nom_vern_eng VARCHAR, habitat INTEGER, fr CHAR(1), gf CHAR(1), mar CHAR(1), gua CHAR(1), sm CHAR(1), sb CHAR(1), spm CHAR(1), may CHAR(1), epa CHAR(1), reu CHAR(1), taaf CHAR(1), pf CHAR(1), wf CHAR(1), cli VARCHAR, url VARCHAR, CONSTRAINT inpn_taxref_pkey PRIMARY KEY (cd_nom), CONSTRAINT fk_cd_ref_est_cd_nom FOREIGN KEY (cd_ref) REFERENCES tmp.inpn_taxref (cd_nom) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) ALTER TABLE tmp.inpn_taxref OWNER TO postgres; COMMENT ON COLUMN tmp.inpn_taxref.regne IS 'Règne auquel le taxon appartient (calcul récursif sur le cd_taxsup et rang )'; COMMENT ON COLUMN tmp.inpn_taxref.phylum IS 'Embranchement auquel le taxon appartient (calcul récursif sur le cd_taxsup et rang )'; COMMENT ON COLUMN tmp.inpn_taxref.classe IS 'Classe à laquelle le taxon appartient (calcul récursif sur le cd_taxsup et rang )'; COMMENT ON COLUMN tmp.inpn_taxref.ordre IS 'Ordre auquel le taxon appartient (calcul récursif sur le cd_taxsup et rang )'; COMMENT ON COLUMN tmp.inpn_taxref.famille IS 'Famille à laquelle le taxon appartient (calcul récursif sur le cd_taxsup et rang )'; COMMENT ON COLUMN tmp.inpn_taxref.cd_nom IS 'Identifiant unique du nom scientifique'; COMMENT ON COLUMN tmp.inpn_taxref.cd_taxsup IS 'Identifiant (cd_nom) du taxon supérieur'; COMMENT ON COLUMN tmp.inpn_taxref.cd_ref IS 'Identifiant (cd_nom) du taxon de référence (dont le nom est validé)'; COMMENT ON COLUMN tmp.inpn_taxref.rang IS 'Rang taxonomique lié à inpn_taxref_rang)'; COMMENT ON COLUMN tmp.inpn_taxref.lb_nom IS 'Nom scientifique du taxon (sans l’autorité)'; COMMENT ON COLUMN tmp.inpn_taxref.lb_auteur IS 'Autorité du taxon (Auteur, année, gestion des parenthèses)'; COMMENT ON COLUMN tmp.inpn_taxref.nom_complet IS 'Combinaison du nom scientifique et de son auteur pour obtenir le nom complet (lb_nom+" "+lb_auteur ou lb_nom si anomyme)'; COMMENT ON COLUMN tmp.inpn_taxref.nom_valide IS 'Nom complet du taxon de référence (nom valide)'; COMMENT ON COLUMN tmp.inpn_taxref.nom_vern IS 'Noms vernaculaires français (séparé par une virgule dans les cas n+*)'; COMMENT ON COLUMN tmp.inpn_taxref.nom_vern_eng IS 'Noms vernaculaires anglais'; COMMENT ON COLUMN tmp.inpn_taxref.habitat IS 'Code de l''habitat (lié à inpn_taxref_habitat)'; COMMENT ON COLUMN tmp.inpn_taxref.fr IS 'Statut biogéographique en France métropolitaine (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.gf IS 'Statut biogéographique en Guyane (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.mar IS 'Statut biogéographique en Martinique (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.gua IS 'Statut biogéographique en Guadeloupe (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.sm IS 'Statut biogéographique à Saint-Martin (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.sb IS 'Statut biogéographique à Saint-Barthélémy (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.spm IS 'Statut biogéographique à Saint-Pierre et Miquelon (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.may IS 'Statut biogéographique à Mayotte (lien vers inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.epa IS 'Statut biogéographique aux Îles Éparses (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.reu IS 'Statut biogéographique à la Réunion (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.taaf IS 'Statut biogéographique aux TAAF (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.pf IS 'Statut biogéographique en Polynésie française (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.nc IS 'Statut biogéographique en Nouvelle-Calédonie (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.wf IS 'Statut biogéographique à Wallis et Futuna (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.cli IS 'Statut biogéographique à Clipperton (lié à inpn_taxref_statut)'; COMMENT ON COLUMN tmp.inpn_taxref.url IS 'adresse web de la fiche du taxon sur le site web de INPN';
Ajout des contraintes sur le statut:
-- Ajout des contraintes étrangères sur la table inpn_taxref ALTER TABLE tmp.inpn_taxref ADD CONSTRAINT statut_fr_fk FOREIGN KEY (fr) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_gf_fk FOREIGN KEY (gf) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_mar_fk FOREIGN KEY (mar) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_gua_fk FOREIGN KEY (gua) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_sm_fk FOREIGN KEY (sm) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_sb_fk FOREIGN KEY (sb) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_spm_fk FOREIGN KEY (spm) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_may_fk FOREIGN KEY (may) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_epa_fk FOREIGN KEY (epa) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_reu_fk FOREIGN KEY (reu) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_taaf_fk FOREIGN KEY (taaf) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_paf_fk FOREIGN KEY (pf) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_nc_fk FOREIGN KEY (nc) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_wf_fk FOREIGN KEY (wf) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT statut_cli_fk FOREIGN KEY (cli) REFERENCES inpn_taxref_statut (code), ADD CONSTRAINT rang_est_inpn_taxref_rang_code_fk FOREIGN KEY (rang) REFERENCES inpn_taxref_rang (code);
Note : J’aurai pu utiliser Un TYPE ENUM inpn_taxref _habitat et inpn_taxref_statut mais pour des raisons de compatibilité SQL j’en ai rien fait. Cela pourra faire l’objet d’une optimisation future. Pour nos besoins, nous n’utilisons pas la table de description des statuts.
Intégration du fichier TAXREFV60.txt
Au préalable, j’ai ouvert le fichier en important le texte dans Excel avec les paramètres suivants :
- jeu de caractères > « Windows ANSI »
- « Séparé par » > Tabulation
- « Séparateur de texte » > forcer à vide (supprimer le double cotes car seul deux propositions sont faites)
- Aucune autre case ne doit être cochée.
En principe, tous les taxons font l’objet d’une fiche sur le site web de l’INPN. Donc l’URL ne peut pas être vide. Et comme c’est la dernière colonne, cela m’arrange bien car ça m’évite de faire un script pour analyser la structure. Or, suite à la vérification, le fichier d’export est corrompu car certaines lignes du fichier contiennent une ou plusieurs tabulations en trop.
Les lignes corrigées sont :
- 20289
- 99942
- 99943
- 99944
- 176838
Une fois corrigé j’ai importé mon fichier texte en sachant qu’Excel sauvegarde systématiquement dans l’encodage du système.
SET CLIENT_ENCODING TO 'LATIN1'; COPY tmp.inpn_taxref FROM 'D:/work/gis/data/src/inpn/taxref/TAXREFv60_patch_20130904.txt' DELIMITER E'\t' HEADER CSV;
