Vérification d’intégrité des données pour la table protection_espece de l’INPN

L’INPN fournie une liste d’espèces réglementées s’appuyant sur son référentiel TAXREF. Réglementations et protections aux niveaux international, communautaire, national, régional ou départemental sont déclinées aux niveaux des taxons terminaux (mais pas seulement car j’ai listé des taxons pour les rangs PH, CL, OR, FM, GN, ES, SSES, VAR, FO, CAR)

RANG LV_RANG NB_TAXON
PH 40 1
CL 80 15
OR 140 160
FM 180 1911
GN 220 18690
ES 290 86205
SSES 320 8924
VAR 340 1493
FO 360 99
CAR 405 36

Vous pouvez la télécharger ici

Dans le cadre de l’intégration du référentiel TAXREFv60 j’ai voulu faire des tests sur l’intégrité des données. En effet, vu les résultats observés sur la table TAXREF j’ai préféré vérifier avant tout l’existence des clés de liaisons dans la table parente.
En effet, la contrainte de clé étrangère ne fonctionne pas.

Vérification d’intégrité

Pour cela j’ai vérifié si le cd_nom existe dans la table TAXREF

Requête SQL à exécuter

/************************************************************************
 
– Description : Tester l'intégrité sur le cd_nom avec la table taxref
 
– Auteur : Jérôme Seigneuret
 
- Date : 2013-09-13
 
*************************************************************************/
SELECT
    p.cd_nom, cd_nom_cite, nom_cite, syn_cite, nom_francais_cite, precisions
FROM
    tmp.inpn_taxref AS t
RIGHT JOIN inpn_protection_espece AS p
    ON t.cd_nom = p.cd_nom
WHERE
    t.cd_nom IS NULL;

Télécharger la requête

La requête retourne 7 résulat.

J’ai également vérifié si le cd_nom_cite existe dans la table TAXREF

Requête SQL à exécuter

/************************************************************************ 
 
– Description : Tester l'intégrité sur le cd_nom_cite avec la table taxref 
 
– Auteur : Jérôme Seigneuret 
 
- Date : 2013-09-13 
 
*************************************************************************/ 
SELECT
    DISTINCT cd_nom_cite, nom_cite, syn_cite, nom_francais_cite, precisions 
FROM
    inpn_taxref AS t 
RIGHT JOIN inpn_protection_espece AS p  
    ON t.cd_nom = p.cd_nom_cite 
WHERE
    t.cd_nom IS NULL;

Télécharger la requête

La requête retourne 15 codes à revoir.

Vous pouvez visualiser le résultat dans ce tableau.

J’ai envoyé ces résultats au référent INPN TAXREF dans l’attente d’avoir un retour.

Pour l’instant j’ai supprimé les entrées dont le cd_nom n’existe pas. Mais le problème reste présent avec le cd_nom_cite.

DELETE FROM inpn_protection_espece WHERE cd_nom IN (SELECT
    p.cd_nom
FROM
    tmp.inpn_taxref AS t 
RIGHT JOIN inpn_protection_espece AS p  
    ON t.cd_nom = p.cd_nom 
WHERE
    t.cd_nom IS NULL);

Il est probable que cela soit du au script d’export des données de la base TAXREF. Si l’on se réfère à la documentation et au contenu de la base de données, les sous-ordre ne sont pas livrés ce qui pose du coup problème pour l’intégrité des données.
Cas pratique:
En effet, je prend l’exemple de « Ophidia », qui dans l’arbre taxonomique est un sous-ordre des « Squamata », on peut observer son absence de la base de données mais il est mentionné dans la réglementation.

SELECT * FROM tmp.inpn_taxref WHERE cd_nom =186288

Du coup la famille « Colubridae » fait référence directement au taxon supérieur correspond à l’ordre « Squamata ». On voit bien que dans le traitement de hiérarchisation des taxons, les sous-ordre sont ignorés. Le seul moyen est d’ajouter les rangs intermédiaires et de mettre à jour la hiérarchisation.

Je ferai peut-être un patch en ce sens (A moins que l’INPN se décide à le faire lui même). La suite au prochain épisode.

Le code du taxon fait-il référence au taxon valide?

pour des raisons de simplicité dans les requêtes et pour éviter de chercher le taxon valide j’ai vérifié que le cd_nom correspondait bien à un taxon valide dans la table inpn_taxref

Requête SQL :

WITH t 
     AS (SELECT cd_nom 
         FROM   tmp.inpn_taxref 
         WHERE  cd_nom = cd_ref) 
SELECT p.cd_nom 
FROM   inpn_protection_espece AS p 
       LEFT JOIN t 
              ON t.cd_nom = p.cd_nom 
WHERE  t.cd_nom IS NULL

Le résultat est concluant. Nous n’avons pas à faire une recherche de taxon valide car le cd_nom est bien le même que le cd_ref

Ajouter la contrainte de clé étrangère

Pour l’instant j’ai mis « la clé étrangère » car il est impossible de la mettre sur le code du nom cité pour les raison précisé au préalable dans l’article.
J’ai donc ajouté ma contrainte sur le cd_nom uniquement.

Requête SQL :

ALTER TABLE inpn_protection_espece 
  ADD CONSTRAINT cd_nom_est_inpn_taxref_cd_nom_fk FOREIGN KEY( cd_nom) 
  REFERENCES tmp.inpn_taxref ( cd_nom) ON DELETE CASCADE ON UPDATE CASCADE;

Cette table étant de type descriptive par rapport à TAXREF, l’intérêt de conserver des informations sur une espèce n’existant pas dans la table parente n’aurait aucun sens et donc à continuer d’exister dans la table. C’est pourquoi, j’ai choisi d’ajouter la suppression et la mise à jour en cascade. Le jour où TAXREF évolue, je n’aurais pas à lancer les scripts de mise à jour et de suppression sur cette table.
Au pire je pourrais faire une table de log avec un TRIGGER pour connaitre son évolution ;-)

Publié dans Non classé | Laisser un commentaire

Mise à jour de la version 6 du référentiel TAXREF de l’INPN

Suite à l’observation de certaines erreurs, j’ai vérifié globalement les erreurs contenu dans TAXREF v6 et j’ai réalisé les correctifs détaillés ci-dessous.

Vérification et correction des noms latins et des noms d’auteurs

Je viens d’analyser les espaces contenus dans les noms de champs lb_nom et lb_auteur à l’aide de la requête suivante :

SELECT cd_nom, 
       REPLACE(lb_nom, ' ', '~')   AS nom, 
       REPLACE(lb_auteur, ' ', '~')AS auteur 
FROM   tmp.inpn_taxref 
WHERE  lb_nom ~* '(\s{2,})' 
        OR lb_auteur ~* '\s{2,}' 
        OR TRIM(lb_nom) <> lb_nom 
        OR TRIM(lb_auteur) <> lb_auteur;

Cette requête a retourné 1025 lignes contenant des erreurs. Comme vous pouvez le voir dans la requête, j’ai remplacé les espaces par des tildes (~) pour améliorer la lisibilité des résultats.

J’ai corrigé les données avec la requête suivante :

UPDATE tmp.inpn_taxref 
SET    lb_nom = Regexp_replace(TRIM(lb_nom), '[\s]{2,}', ' ', 'g') :: VARCHAR, 
       lb_auteur = Regexp_replace(TRIM(lb_auteur), '[\s]{2,}', ' ', 'g') :: VARCHAR 
WHERE  lb_nom ~* '\s{2,}' 
        OR lb_auteur ~* '\s{2,}' 
        OR TRIM(lb_nom) <> lb_nom 
        OR TRIM(lb_auteur) <> lb_auteur;

Il existe des doublons de double-cotes. Je les ai recherché avec la requête suivante :

SELECT cd_nom, 
       lb_nom AS nom, 
       lb_auteur AS auteur 
FROM   tmp.inpn_taxref 
WHERE  lb_nom ~* '"{2,}' 
        OR lb_auteur ~* '"{2,}';

Cette dernière me retourne 18 résultats que j’ai corrigé ainsi:

UPDATE tmp.inpn_taxref 
SET    lb_nom = Regexp_replace(TRIM(lb_nom), '["]{2,}', '"', 'g') :: VARCHAR, 
       lb_auteur = Regexp_replace(TRIM(lb_auteur), '["]{2,}', '"', 'g') :: VARCHAR 
WHERE  lb_nom ~* '"{2,}' 
        OR lb_auteur ~* '"{2,}';

Il existe aussi dans le fichier des cotes anglaises “” sur un nom d’espèce et sur un nom d’auteur. Dans mon cas l’intégration des données à posé un problème d’encodage. Ducoup les cotes ne sont pas visible mais le problème apparaît dans une requête lors de l’analyse spécifiques des noms en zoologie.

J’ai testé si certains noms d’auteurs ne contenait pas au moins un caractère lettré :

SELECT cd_nom, 
       lb_nom    AS nom, 
       lb_auteur AS auteur 
FROM   tmp.inpn_taxref 
WHERE  lb_auteur !~* '[a-z]';

J’ai corrigé 5 lignes avec la requête suivante:

UPDATE tmp.inpn_taxref 
SET    lb_auteur = '' 
WHERE  lb_auteur !~* '[a-z]';

Analyse spécifiques des noms en zoologie

Par la suite j’ai également souhaité vérifier le contenu des noms définis dans la base de données pour savoir s’ils sont conformes avec la Code Internationale de Nomenclature Zoologique.

-- Article 27 du ICNZ : aucun nom latin ne doit contenir de ligature entre les lettres, d'apostrophe ou de signe diachronique
-- Se référer à l'article 32.5, 33.3, sur la correction de l'orthographe des noms
SELECT * 
FROM   tmp.inpn_taxref 
WHERE  lb_nom !~* '^[a-z\s()?."]*$' 
       AND regne = 'Animalia';

D’après l’observation, je pense que les taxons contenant des termes erronés ont été inscrits comme synonymie et ont fait l’objet de nouvelles entrées dans la base avec le terme correctement orthographié.
J’ai donc réduit cette requête au taxon de référence

SELECT * 
FROM   tmp.inpn_taxref 
WHERE  lb_nom !~* '^[a-z\s()?."]*$' 
       AND regne = 'Animalia'
       AND cd_nom = cd_ref;

Ainsi, la requête permet de voir l’ensemble des erreurs sur les noms de taxon valides contenant des caractères qui ne sont normalement pas conformes (sauf pour l’exception du trait d’union en 32.5.2.4.3. comme par exemple de x-album).
Le nombre de résultat n’est pas très important je n’ai pas jugé bon de faire un regex plus complexe pour le moment.

  1. Il reste toutefois le problème sur les trait d’union pour:
    • Miocalles sancti-johni à rectifier en Miocalles sanctijohni
    • Phlebotomus colas-belcouri à rectifier en Phlebotomus colasbelcouri
  2. J’ai aussi observé des signes diachroniques :
    • Mendaña peut être converti en Medana
    • remplacer Aphanoconia küsteriana par Aphanoconia kuesteriana (conversion de l’autrichien)
    • remplacer Phlebotomus saülensis par Phlebotomus saulensis (conversion de français)
  3. “Turdus” ulietensis contenant des caractères mal encodés remplacés par "Turdus" ulietensis
  4. Certains termes préfixés par [unassigned] sont mentionnés comme niveau intermédiaire supérieur (super-ordre, super-famille) dans la GBIF à celui mentionné dans TAXREF. Je vois pas encore quoi en faire donc j’ai laissé en l’état car ce débat n’est pas de mon ressort.

Je n’ai pas tester les termes de liaison, les capitales et les espaces consécutifs dans les prénom aux abréviations des nom d’auteur. L’INPN n’a pour le moment pas diffusé de référentiel d’auteur avec des alias utilisés pour certains règnes donc je n’ai pas fait de vérification sur ce point mais cela peut être une orientation de travail.

Il me reste à vérifier les taxons spécifiques aux autres règnes que je n’ai pas encore eu le temps de vérifier.

Vérification et correction du nom complet et du nom valide

Suite à la correction de l’ensemble des noms, il est nécessaire de corriger les le nom complet et le nom valide.

J’ai vérifié les différences entre nom + ‘ ‘ + auteur et nom complet et je me retrouve avec 8795 lignes à corriger… :

SELECT cd_nom, 
       nom_complet 
FROM   tmp.inpn_taxref 
WHERE  nom_complet <> TRIM(CONCAT(lb_nom, ' ', lb_auteur)) :: VARCHAR;

J’ai utilisé la requête suivante:

UPDATE tmp.inpn_taxref 
    SET nom_complet = TRIM(CONCAT(lb_nom, ' ', lb_auteur)) :: VARCHAR
WHERE  nom_complet <> TRIM(CONCAT(lb_nom, ' ', lb_auteur)) :: VARCHAR;

Trim est nécessaire dans les cas d’anonymat de l’auteur

Suite à ce correctif j’ai analysé ne nombre taxon de référence qui nécessite d’être mis à jour. La requête suivante nous a retourné 5572 lignes à modifier :

SELECT cd_nom, cd_ref, 
       nom_complet, nom_valide 
FROM   tmp.inpn_taxref 
WHERE  cd_nom = cd_ref 
       AND nom_complet <> nom_valide;

Ducoup j’ai vérifié le nombre de taxon impactés ce qui nous retourne 22754 lignes à modifier :

SELECT t1.cd_ref, 
       t1.cd_nom, 
       t1.nom_complet, 
       t1.nom_valide 
FROM   tmp.inpn_taxref AS t1, 
       (SELECT nom_valide 
        FROM   tmp.inpn_taxref 
        WHERE  cd_nom = cd_ref 
               AND nom_complet <> nom_valide)AS t2 
WHERE  t1.nom_valide = t2.nom_valide

J’ai corrigé cette erreur avec la requête suivante:

UPDATE tmp.inpn_taxref 
SET    nom_valide = t.new_nom_valide 
FROM  (SELECT nom_complet AS new_nom_valide, 
              nom_valide 
       FROM   tmp.inpn_taxref 
       WHERE  cd_nom = cd_ref 
              AND nom_complet <> nom_valide) AS t 
WHERE  tmp.inpn_taxref.nom_valide = t.nom_valide

Ajout du taxon racine pour la gestion récursive de l’ensemble de l’arbre:

INSERT INTO tmp.inpn_taxref(
            cd_nom, cd_ref, cd_taxsup, rang, 
            lb_nom, nom_complet, nom_valide, 
            nom_vern, nom_vern_eng)
VALUES      (349525 , 349525 , 0, 'LIFE', 'Arbor Vitae','Arbor Vitae','Arbor Vitae','Arbre du vivant','Tree of life' );
Publié dans Biodiversité, Référentiel | Marqué avec , , | Laisser un commentaire

Création de la base de données Naturaliste sous Postgresql

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;
Publié dans Biodiversité, Référentiel | Marqué avec , , | Laisser un commentaire

Fonctions utiles avec Excel

Convertir une date

Les fonctions suivantes se basent sur la première cellule, la première colonne, ou la première ligne d’un tableau. Les termes des fonctions correspondent au langage français d’Excel.

Chaîne (aaaammjj) vers DATE

Exemple: 20130203
formule :

=DATE(GAUCHE(A1;4);STXT(A1;5;2);DROITE(A1;2))

DATE vers Chaîne (aaaammjj)

Exemple: 2013/02/03
formule :

=ANNEE(A1)&SI(MOIS(A1)<10;0&MOIS(A1);MOIS(A1))&SI(JOUR(A1)<10;0&JOUR(A1);JOUR(A1))
Publié dans Non classé | Marqué avec | Laisser un commentaire

Installer Git et Mercurial – Linux

Note: cette doc avance au fur et à mesure de mon utilisation de ces outils ;-)

Pour utiliser Bitbucket, vous devez installer un outil de DVCS (distributed version control or decentralized version control) – en français : contrôle de version distribué ou de contrôle de version décentralisé – sur l’ordinateur où vous écrivez votre code. En règle générale, cet ordinateur est une machine physique avec laquelle vous avez l’habitude de travailler. C’est votre système ou machine locale. Vous pouvez aussi écrire ou déployer du code sur une machine distante (Exemple: un laboratoire informatique ou un serveur dans un centre de données). Vous pouvez aussi avoir besoin d’un outil DVCS sur cette machine.
Ce tutoriel se réfère à un cas typique, votre système local, mais les instructions sont les mêmes dans les deux cas.

Bitbucket prend en charge deux outils DVCS, Git et Mercurial. Ces outils s’exécutent sur tous les systèmes d’exploitation modernes. Vous pouvez installer Git et Mercurial sur n’importe lequel des systèmes d’exploitation pris en charge. Bitbucket fonctionne avec la Git v1.6.6 + et Mercurial v1.7 +. Mercurial est dépendant du langage de programmation Python(v2.4 à 2.7). Le processus d’installation vérifie que vous détenez la bonne version de Python sur votre système.

Étant donné qu’avec Bitbucket vous pouvez utiliser à la fois Git et Mercurial, cette page vous explique comment installer ces solutions sur votre machine. Si vous avez déjà l’un ou l’autre de ces outils installés, ignorez les instructions et passez à l’étape suivante du didacticiel.

Cette documentation vous explique comment installer ces outils sur Linux Ubuntu v12.04. (Je n’explique pas comment les installer sur Windows et Mac car je n’utilise pas ces systèmes pour mes projets actuels).

Étape 1. Installer Git

Ubuntu utilise le système de gestion de paquets APT, qui fournit l’utilitaire en ligne de commande apt-get (optionnellement des interfaces graphiques telles que Synaptic et d’Aptitude). Nous allons utiliser apt-get pour installer des paquets (servez-vous des interfaces graphiques si vous êtes plus à l’aise avec).
Ouvrez une fenêtre de terminal sur votre système et procédez ainsi:

Entrez la commande suivante pour installer Git:

$ sudo apt-get install git-core

Vous pouvez vérifiez l’installation si l’installation a réussi en saisissant la commande:

$ which git
/usr/bin/git

Configurez votre nom d’utilisateur en utilisant la commande suivante:

$ git config --global user.name "John Doe"

Configurez votre adresse e-mail en utilisant la commande suivante:

$ git config --global user.email "john.doe@emailserver.com"

Étape 2. Installer Mercurial

Ouvrez une fenêtre de terminal et procédez comme suit:

Vérifier que les dépôts APT Universe sont actifs en utilisant la commande suivante:

$ sudo gedit /etc/apt/sources.list

Dans mon cas j’utilise la version d’Ubuntu 12.04 mais ces lignes doivent être adaptées à votre distribution et votre version. Les lignes doivent exister et être décommantées.

## Uncomment the following lines to add software from Universe
deb http://fr.archive.ubuntu.com/ubuntu/ precise universe
deb-src http://fr.archive.ubuntu.com/ubuntu/ precise universe
deb http://fr.archive.ubuntu.com/ubuntu/ precise-updates universe
deb-src http://fr.archive.ubuntu.com/ubuntu/ precise-updates universe
 
deb http://security.ubuntu.com/ubuntu precise-security universe
deb-src http://security.ubuntu.com/ubuntu precise-security universe

Mettez à jour la liste des paquets en entrant la commande suivante en ligne de commande:

$ sudo apt-get update

Entrez la commande suivante pour installer Mercurial:

$ sudo apt-get install mercurial

Vérifiez l’installation a réussi en tapant en ligne de commande:

$ which hg
/usr/bin/hg

Créer et éditer le fichier de configuration Mercurial < code>~/.hgrc (indispensable pour valider les modification en cours dans le dépôt) en tapant la commande:

$ gedit ~/.hgrc

Copier/Coller le texte suivant et adaptez le à votre utilisateur:

[ui]
# Nom apparaissant lors des commits (validation de la modification en cours).
username = John Doe <john.doe@emailserver.com>

Pour en savoir plus sur le fichier de configuration .hgrc vous pouvez aller sur la page Configuration files from Mercurial.

Article à suivre : Tutoriel - Initialiser son dépôt (Repository)

Publié dans HowTo | Marqué avec , , , | Un commentaire

configuration relative à ubuntu 11.10

Ajout de Chromium:

sudo apt-get install chromium-browser

Ajout des applications propriétaires :

sudo apt-get install ubuntu-restricted-extras adobe-flashplugin

Correctif suite aux bugs sur Flash :

sudo apt-get remove adobe-flashplugin
sudo apt-get install flashplugin-installer

Ajout des outils de personnalisation système :

sudo add-apt-repository ppa:tualatrix/next
sudo apt-get update
sudo apt-get install ubuntu-tweak

Gestion des archives :

sudo apt-get install p7zip-full p7zip-rar

Ajout des outils de visualisation :

sudo apt-get install gnome-sushi

Ajout de la gestion de la webcam :

sudo apt-get install cheese
Publié dans Non classé | Laisser un commentaire

Correction de l’erreur langs.xml failed de Notepad++

Je viens d’installer Notepad++ et après la deuxième ouverture du programme, j’ai le message suivant : »

Load langs.xml failed!

Si je clique sur ok le fichier s’ouvre mais le langage n’est pas déterminé.
J’ai essayé de supprimer notepad et de le réinstaller mais j’ai toujours le même problème.
Le plus bizarre c’est qu’en faisant un clic droit sur le fichier et en choisissant Edit with Notepad++, je n’ai aucun soucis.

J’ai fais remplacé le fichier langs.xml en  langs.model.xml situé dans le répertoire d’installation de notepad++. résultat… toujours la même erreur.

Suite à une recherche j’ai trouvé des fichiers ailleurs

C:\Users\%USERNAME%\AppData\Roaming\Notepad++\

Après observation, je me suis rendu compte que le fichier XML était corrompu. Ducoup voici mon script pour la modification car j’ai pas encore pu identifier si le problème était valable pour tous les utilisateurs.

A lancer dans l’utilitaire de commande:

copy "C:\Program Files (x86)\Notepad++\langs.model.xml"  "C:\Users\%USERNAME%\AppData\Roaming\Notepad++\langs.xml" /Y

 

Publié dans Non classé | 3 commentaires

Liste de services de données géographiques Suisse

La Suisse propose une liste de services de données géographique utilisable dans des applications de cartographie en client lourd ou client léger.

Voici la liste de ces données Liste.txt

Le SITG (Système d’Information du Territoire Genevois) c’est quelque peu enrichi.
Voici la page permettant d’accéder aux différents services :lien

Celle-ci contient des données utilisable sous forme de service ArcGIS, ArcIMS ou encore WMS.

Publié dans Géomatique | Marqué avec , , , , | Laisser un commentaire

Mise à jour de WordPress 2.6 chez Free

Suite à la mise à jour de WordPress, je me suis retrouvé dans l’incapacité d’ouvrir mes pages et mes articles. En effet, je tombais sur un message du type « votre page est introuvable ». Après 20 minutes de recherche, j’ai trouvé deux sujets qui m’ont aidés à corriger mon problème :

Donc, suite à la mise à jour de WordPress de la version 2.5 à 2.6, j’ai corrigé mon problème en suivant les étapes suivantes :

  1. enlever le fichier .htaccess situé à la racine du serveur FTP qui est présent par défaut.
  2. se connecter via l’interface d’administration et aller sur le menu réglages >> permaliens.
  3. Vérifier en bas de page qu’aucun éléments concernant le fichier .htaccess n’est renseigné dans la boite textuelle correspondante. Sinon, il faut en supprimer le contenu.
  4. Repasser la structure des permaliens à leurs valeur par défaut
  5. Enregistrer les paramêtres

Maintenant nous pouvons voir les articles et les pages. Mais le hic c’est que les liens des pages ne correspondent plus à ceux enregistrés par les moteurs de recherches.

Nous allons donc retourner dans le panneau d’administration afin de modifier les permaliens.

  1. se connecter via l’interface d’administration et aller sur le menu réglages >> permaliens.
  2. Sélectionner structure personnalisée et insérer le contenu de votre adresse. Si vous êtes hébergé chez Free vous devez ajouter au début de la structure un /index.php/ suivi des éléments que vous voulez retrouver dans vos liens.
    exemple : /index.php/%post_id%/%year%/%monthnum%/%postname%/
    Vous aurez plus d’info sur les éléments des permaliens sur http://codex.wordpress.org/Using_Permalinks

  3. corriger le bug de la version 2.6 en remplissant les paramètres optionnels (qui ne le sont pas):
    • en préfixe des catégories : /catégorie
    • en préfixe des tags : /tag

Maintenant, ça marche donc pour vous ça devrez fonctionner aussi.

Publié dans API Web, Erreurs, HowTo | Marqué avec , , | Laisser un commentaire

Outils en ligne pour le web

Les couleurs

Calculateur convertisseur de code couleurs

Tiens, avec une petite recherche avec notre ami Google, j’ai trouvé des sites en ligne très intéressant:

mots-clés de la recherche Résultat de la recherche
color editor on line

Autre site intéressant : 0to255

Publié dans Outils | Marqué avec | Laisser un commentaire