Intégration des référentiels de l'INPN

Intégration des référentiels de l'INPN

Référentiels habitat

L'INPN a mis en ligne récemment les référentiels habitats au format excel

Le travail consiste maintenant a intégrer ces référentiels à la base de données, en utilisant les commentaires de table et de colonne pour stocker les métadonnées fournies avec le référentiel (2 premières feuilles de calcul du fichier).

Les étapes de son intégration à la bdd sont :

la création de la table
la création des commentaires sur la table et sur les colonne
l'intégration des données
la création des contraintes

Le premier fichier intégré concerne la typologie NATURA 2000 Le fichier sql correspondant est disponible ici

Le second fichier intégré concerne la typologie corine biotopes Le fichier sql correspondant est disponible ici

Référentiels taxonomiques

La version 4 du référentiel taxonomique de l'INPN a été mise à disposition jour en octobre 2011.

page de téléchargement de l'INPN : http://inpn.mnhn.fr/telechargement/referentielEspece

conditions de mise à disposition

aucune copie du référentiel TAXREF de sera faite et transmise à un tiers sans que celui-ci n'ait rempli le présent formulaire ;
le référentiel TAXREF, ou une de ses composantes telles que décrites dans le document de citation des sources, sera cité dans toute publication ou présentation qui l'utilisera ;
aucune partie du référentiel TAXREF ne sera mise à disposition en ligne sans autorisation préalable du SPN/MNHN ;
en cas de découverte d'erreurs il est prié de bien vouloir en informer les éditeurs de façon à constamment accroitre la qualité du référentiel.

Décompression de l'archive dans le dossier taxref

Placer les 3 fichiers ci dessous (2 à télécharger et 1 à créer) dans ce répertoire. Ce répertoire contiendra donc à terme les fichiers suivants :

1_creation_table.sql
2_insertion_donnees_inpn.sql (fichier créé par le script taxref.sh à partir de TAXREFv4.0.txt)
3_creation_contraintes.sql
taxref.sh
TAXREFv4.0.txt (téléchargé sur le site de l'INPN)

Création de la table dans le schema inpn

1_creation_table.sql

DROP TABLE IF EXISTS temp.taxref;
CREATE TABLE temp.taxref
(
  regne character varying,
  phylum character varying,
  classe character varying,
  ordre character varying,
  famille character varying,
  cd_nom character varying PRIMARY KEY,
  cd_taxsup character varying,
  cd_ref character varying,
  rang character varying,
  lb_nom character varying,
  lb_auteur character varying,
  nom_complet 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,
  taaf character varying,
  pf character varying,
  nc character varying,
  wf character varying,
  cli character varying,
  aphia_id character varying,
  CONSTRAINT fk_cd_ref_est_cd_nom FOREIGN KEY (cd_ref)
      REFERENCES temp.taxref (cd_nom) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
 
COMMENT ON COLUMN temp.taxref.REGNE IS 'Règne auquel le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.PHYLUM IS 'Embranchement auquel le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.CLASSE IS 'Classe à laquelle le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.ORDRE IS 'Ordre auquel le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.FAMILLE IS 'Famille à laquelle le taxon appartient (champ calculé à partir du CD_TAXSUP)';
COMMENT ON COLUMN temp.taxref.CD_NOM IS 'Identifiant unique du nom scientifique';
COMMENT ON COLUMN temp.taxref.CD_TAXSUP IS 'Identifiant (CD_NOM) du taxon supérieur';
COMMENT ON COLUMN temp.taxref.CD_REF IS 'Identifiant (CD_NOM) du taxon de référence (nom retenu)';
COMMENT ON COLUMN temp.taxref.RANG IS 'Rang taxonomique (lien vers TAXREF_RANG)';
COMMENT ON COLUMN temp.taxref.LB_NOM IS 'Nom scientifique du taxon (sans l’autorité)';
COMMENT ON COLUMN temp.taxref.LB_AUTEUR  IS 'Autorité du taxon (Auteur, année, gestion des parenthèses)';
COMMENT ON COLUMN temp.taxref.NOM_COMPLET IS 'Combinaison des champs pour donner le nom complet (~LB_NOM+" "+LB_AUTEUR)';
COMMENT ON COLUMN temp.taxref.NOM_VERN IS 'Noms vernaculaires français';
COMMENT ON COLUMN temp.taxref.NOM_VERN_ENG IS 'Noms vernaculaires anglais';
COMMENT ON COLUMN temp.taxref.HABITAT IS 'Code de l''habitat (lien vers TAXREF_HABITATS)';
COMMENT ON COLUMN temp.taxref.FR IS 'Statut biogéographique en France métropolitaine (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.GF IS 'Statut biogéographique en Guyane (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.MAR IS 'Statut biogéographique en Martinique (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.GUA IS 'Statut biogéographique en Guadeloupe (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.SM IS 'Statut biogéographique à Saint-Martin (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.SB IS 'Statut biogéographique à Saint-Barthélémy (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.SPM IS 'Statut biogéographique à Saint-Pierre et Miquelon (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.MAY IS 'Statut biogéographique à Mayotte (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.EPA IS 'Statut biogéographique aux Iles Eparses (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.REU IS 'Statut biogéographique à la Réunion (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.TAAF IS 'Statut biogéographique aux TAAF (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.PF IS 'Statut biogéographique en Polynésie française (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.NC IS 'Statut biogéographique en Nouvelle-Calédonie (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.WF IS 'Statut biogéographique à Wallis et Futuna (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.CLI IS 'Statut biogéographique à Clipperton (lien vers TAXREF_STATUTS)';
COMMENT ON COLUMN temp.taxref.APHIA_ID IS 'Identifiant unique de World Register of Marine Species (WoRMS)';

Ajout de la contrainte d'intégrité référentielle entre cd_taxsup et cd_nom, Création des index

nécessite le remplacement par null des valeurs '0', concerne les règnes :

3_creation_contraintes.sql

UPDATE temp.taxref SET cd_taxsup=NULL WHERE cd_taxsup='0';
INSERT INTO "temp".taxref(regne, cd_nom, cd_taxsup, cd_ref) VALUES ('vivant',349525,349525,349525);
ALTER TABLE temp.taxref 
  ADD CONSTRAINT fk_cd_taxsup_est_cd_nom FOREIGN KEY (cd_taxsup)
      REFERENCES temp.taxref (cd_nom) DEFERRABLE INITIALLY DEFERRED;
 
CREATE INDEX cd_ref_ind ON temp.taxref USING btree (cd_ref);
CREATE INDEX classe_ind ON temp.taxref USING btree (classe);
CREATE INDEX famille_ind ON temp.taxref USING btree (famille);
CREATE INDEX lb_nom_ind ON temp.taxref USING btree (lb_nom);
CREATE INDEX nom_complet_ind ON temp.taxref USING btree (nom_complet);
CREATE INDEX ordre_ind ON temp.taxref USING btree (ordre);
CREATE INDEX phylum_ind ON temp.taxref USING btree (phylum);
CREATE INDEX regne_ind ON temp.taxref USING btree (regne);

Création du script sql d'insertion

taxref.sh

## On supprime du fichier les balises html
sed -e "s/<i>//g" TAXREFv4.0.txt > TAXREFv4.0_.txt && mv TAXREFv4.0_.txt TAXREFv4.0.txt 
sed -e "s/<\/i>//g" TAXREFv4.0.txt > TAXREFv4.0_.txt && mv TAXREFv4.0_.txt TAXREFv4.0.txt
## On supprime la première ligne du fichier (nom des colonnes)
sed '/REGNE/d' TAXREFv4.0.txt > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on vérifie que la ligne d'en-tête n'existe plus
head -n 1 TAXREFv4.0.sql
## on échape les simples quote (apostrophe)
sed -e "s/'/''/g" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on vérifie qu'elles ont été échapées
grep -i "\w'\w" TAXREFv4.0.sql
## on remplace les tabulations (délimiteurs) par ','cartactères)
## erreur ! vire la colonne cli!!!
sed "s/\t/\',\'/g" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on crée le début de la commande sql
sed "s/^/INSERT INTO temp.taxref VALUES ('/" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
## on crée la fin de la commande sql
sed "s/\r/');/" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
##on remplace les chaines vides '' par la valeur null
sed "s/''/null/g" TAXREFv4.0.sql > TAXREF_INPN_sp.sql && mv TAXREF_INPN_sp.sql TAXREFv4.0.sql
 
mv TAXREFv4.0.sql 2_insertion_donnees_inpn.sql
 
# Exécution des 3 scripts sur la base de données
# adapter la commande avec vos paramètres de connexion
psql -h 192.168.1.230 -U dba -d sicen -f 1_creation_table.sql
psql -h 192.168.1.230 -U dba -d sicen -f 2_insertion_donnees_inpn.sql
psql -h 192.168.1.230 -U dba -d sicen -f 3_creation_contraines.sql

Statut des espèces

Intégration du fichier TAXREF_INPN_STATUTS.txt

CREATE TABLE inpn.statut(
statut character(1) PRIMARY KEY,
description character varying);
 
COMMENT ON COLUMN inpn.statut.statut IS 'Abrégé tel qu''il apparaît dans le référentiel pour les champs ci-dessus';
COMMENT ON COLUMN inpn.statut.description IS 'Libellé du statut d''indigénat';
COMMENT ON TABLE inpn.statut IS 'Permet d''interpréter les champ FR, REU, GUA, SMSB, MAR, GF, MAY, TAAF et SPM du référentiel.';
 
INSERT INTO inpn.statut VALUES('A','Absente');
INSERT INTO inpn.statut VALUES('B','Accidentelle / Visiteuse');
INSERT INTO inpn.statut VALUES('C','Cryptogène');
INSERT INTO inpn.statut VALUES('D','Douteux');
INSERT INTO inpn.statut VALUES('E','Endémique');
INSERT INTO inpn.statut VALUES('F','Trouvé en fouille');
INSERT INTO inpn.statut VALUES('I','Introduite');
INSERT INTO inpn.statut VALUES('J','Introduite envahissante');
INSERT INTO inpn.statut VALUES('M','Domestique / Introduite non établie');
INSERT INTO inpn.statut VALUES('P','Présente');
INSERT INTO inpn.statut VALUES('S','Subendémique');
INSERT INTO inpn.statut VALUES('W','Disparue');
INSERT INTO inpn.statut VALUES('X','Eteinte');
INSERT INTO inpn.statut VALUES('Y','Introduite éteinte');
INSERT INTO inpn.statut VALUES('Z','Endémique éteinte');
 
ALTER TABLE inpn.taxref ADD CONSTRAINT fk_fr_est_statut FOREIGN KEY (fr) REFERENCES inpn.statut (statut);

Espèces faisant l'objet d'une réglementation

Page de mise à disposition de ces référentiel sur le site de l'INPN

les différentes réglementations

Le fichier sql correspondant est disponible ici

les espèces protégées

Le fichier sql correspondant est disponible ici

Liste des espèces pour Natura 2000 (412 espèces)

L'inpn mentionne 413 espèces sur la page correspondante mais le Grand cormoran (cd_nom=2440) est mentionné 2 fois

CREATE TABLE inpn.taxons_natura_2000
(
  cd_ref character varying NOT NULL,
  annexe character varying,
  CONSTRAINT pk_taxons_nature2000 PRIMARY KEY (cd_ref),
  CONSTRAINT fk_cd_ref_est_dans_taxref FOREIGN KEY (cd_ref)
      REFERENCES inpn.taxref (cd_nom) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Le fichier sql correspondant (avec les données) est disponible ici