Subversion Repositories eFlore/Projets.eflore-projets

Créer une base de données tb_cel avant de lancer les scripts

== Sommaire ==

1) à propos de la mise à jour de septembre 2013

2) à propos de la table cel_references

==============

1) cel_references.sql

création de la table `cel_references`

2) maj-struct-201307.sql

mise à jour des structure de table (les index notamment):

3) maj-cleanup-201307.sql

uniformisation des données (lon/lat, date, ...)

et des NULL vs 0 (pour nom_sel_nn et nom_ret_nn)

4) fix-utilisateur-32.sql

5) dedup-ordre-201307.sql

6) maj-referentiel-201307.sql

fix le référentiel pour les observation ayant un nom_sel_nn sans nom_referentiel en se

basant sur une match exact de CONCAT(nom_sci, auteur) parmi bdtfx, bdtxa et isfan

7) referonosaure.sql

MAJ des observations (valides) avec les nouvelles données générées, à partir de bdtfx/bdtxa/isfan

=====

8) TODO: maj-nom-ret.sql

TODO (pas sûr) MAJ du référentiel pour les observation ayant un nom_ret sans nom_ret_nn mais dont le nom_ret

 ne match pas le nom_sci en BDTFX (car en BDTFX nom_ret_nn peut être égal à 0 !)

9) maj-referentiel-und-201307.sql

MAJ du référentiel pour les observation n'ayant pas de nom_ret_nn (tentative de détermination par nom)

=== 2: À propos de la table cel_references ===

Celle-ci existe car:

* les projets doivent être indépendants (eflore, cel, projets nvjfl, ...)

* les données nécessaires à l'export et à l'import sont massives

* or les webservices s'appellent parfois récursivement, sont lents et inadaptés

La conséquence est que la construction d'une table dérivée de bdtfx/bdtxa/isfan contenant

les informations utiles pour CEL s'avère nécessaire.

cel_references.sql construit une telle table.

Suivent quelques éléments de compréhension et exemples de requêtes liés à cette initialisation:

1) Détermination des noms vernaculaires meilleurs et uniques:

Ce sont ceux qui ont le num_statut le plus élevé pour un num_taxon donné dans nvjfl_v2007.

Plusieurs méthodes sont exposées ci-dessous, sachant que le couple (référentiel, num_nom) est la clef

unique de cel_references.

Il existe à ce jour 16146 nom communs français distincts, 12312 num_taxon pour code_lang = fra et aucun num_statut NULL en français.

1.1:

SELECT n.num_taxon, n.nom_vernaculaire, n.num_statut, n2.num_statut FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon) WHERE n.num_taxon < 32 AND n.code_langue = 'fra' GROUP BY n.num_taxon, n.num_statut HAVING n.num_statut = MAX(n2.num_statut) LIMIT 100;

# 12311 résultats

1.2:

SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n INNER JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' GROUP BY n.num_taxon;

# 2680 résultats

1.3:

SELECT n.num_taxon, n.nom_vernaculaire FROM nvjfl_v2007 n LEFT JOIN nvjfl_v2007 n2 ON (n.num_taxon = n2.num_taxon AND n.code_langue = n2.code_langue AND n.num_statut > n2.num_statut) WHERE n.code_langue = 'fra' AND n2.num_statut IS NOT NULL GROUP BY num_taxon;

# 2680 résultats

Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';

2) à propos de l'insertion dans cel_references proprement dit:

Le modèle simplifié théorique de base est le suivant:

INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \

       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, MAX(n.num_statut)	FROM bdtfx_v1_01 b LEFT JOIN nvjfl_v2007 n	ON (b.num_taxonomique = n.num_taxon AND n.code_langue = 'fra' )	GROUP BY b.num_nom \

       UNION \

       SELECT "bdtxa", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, n.nom_vernaculaire, NULL				FROM bdtxa_v1_00 b LEFT JOIN nva_v2013_06 n	ON (b.num_tax = n.num_taxon AND n.code_langue = 'fra' )			GROUP BY b.num_nom \

       UNION \

       SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;

Mais évidemment, les noms communs n'existent que pour bdtfx[nvjfl], bdtxa[nva], de même que les données baseflor/baseveg.

Plusieurs tables temporaires sont donc nécessaires en particulier puisque toutes les colonnes n'ont pas

des indexes adaptés pour effectuer des JOIN efficaces dans le cadre de ce script d'intégration particulier.

Une version plus aboutie, mais spécifique à bdtfx, après création préalable de T_nvjfl_v2007, était la suivante (présence des noms communs):

INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \

       SELECT "bdtfx", b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci, b.auteur, n.nom_vernaculaire FROM bdtfx_v1_01 b LEFT JOIN T_nvjfl_v2007 n ON (b.num_taxonomique = n.num_taxon );

À noter:

SELECT b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci FROM bdtfx_v1_01 b where b.num_taxonomique = '';

# 3968, c'est à dire des num_taxon vides, pourtant INDEX et NOT NULL.

Idem pour bdtxa

3) à propos de baseveg/baseflor:

Note au 16/07/2013: les schémas sont susceptibles de changer à l'avenir.

La jointure entre bdtfx et baseflor se fait sur le référentiel ("bdtfx") et num_nom.

À partir de là nous disposons d'un catminat_code qui correspond au code_catminat dans baseveg, afin d'obtenir le syntaxon.

Quelques exemples:

SELECT code_catminat, syntaxon, lumiere, hum_atmos, temperature, oceanite, ph_sol, hum_edaph, texture_sol FROM baseveg_v2013_01_09;

SELECT * from baseflor_v2012_12_31 where cle = 1174;

SELECT * from baseveg_v2013_01_09 where code_catminat = '05/3.0.1.0.2' AND niveau = 'ALL';

# 7 résultats

SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat) WHERE f.BDNT = "BDTFX" and f.cle = 1174;

# 7 résultats

SELECT f.num_nomen, f.num_taxon, f.catminat_code, f.ve_lumiere, f.ve_temperature, f.ve_continentalite, f.ve_humidite_atmos, f.ve_humidite_edaph, f.ve_reaction_sol, f.ve_nutriments_sol, f.ve_salinite, f.ve_texture_sol, f.ve_mat_org_sol FROM baseflor_v2012_12_31 f LEFT JOIN baseveg_v2013_01_09 v ON (f.catminat_code = v.code_catminat AND v.niveau = 'ALL') WHERE f.BDNT = "BDTFX" and f.cle = 1174;

# 1 résultat

Nous utilisons v.niveau = ALL pour nous assurer la présence d'un seul f.num_nomen dans `T_basevegflor` et donc assurer l'unicité de la PRIMARY KEY de `cel_references`