Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 890 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
741 raphael 1
Créer une base de données tb_cel avant de lancer les scripts
2
 
755 raphael 3
== Sommaire ==
890 raphael 4
1) à propos de la mise à jour de septembre 2013
755 raphael 5
2) à propos de la table cel_references
6
==============
741 raphael 7
 
822 raphael 8
1) cel_references.sql
9
création de la table `cel_references`
10
 
11
2) maj-struct-201307.sql
755 raphael 12
mise à jour des structure de table (les index notamment):
13
 
822 raphael 14
3) maj-cleanup-201307.sql
755 raphael 15
uniformisation des données (lon/lat, date, ...)
890 raphael 16
et des NULL vs 0 (pour nom_sel_nn et nom_ret_nn)
755 raphael 17
 
822 raphael 18
4) fix-utilisateur-32.sql
777 raphael 19
 
822 raphael 20
5) dedup-ordre-201307.sql
890 raphael 21
 
22
6) maj-referentiel-201307.sql
23
fix le référentiel pour les observation ayant un nom_sel_nn sans nom_referentiel en se
24
basant sur une match exact de CONCAT(nom_sci, auteur) parmi bdtfx, bdtxa et isfan
25
 
26
7) referonosaure.sql
27
MAJ des observations (valides) avec les nouvelles données générées, à partir de bdtfx/bdtxa/isfan
28
 
822 raphael 29
=====
890 raphael 30
8) TODO: maj-nom-ret.sql
865 raphael 31
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
32
 ne match pas le nom_sci en BDTFX (car en BDTFX nom_ret_nn peut être égal à 0 !)
755 raphael 33
 
890 raphael 34
9) maj-referentiel-und-201307.sql
755 raphael 35
MAJ du référentiel pour les observation n'ayant pas de nom_ret_nn (tentative de détermination par nom)
36
 
37
 
38
 
39
 
40
 
41
 
42
 
43
 
44
=== 2: À propos de la table cel_references ===
45
Celle-ci existe car:
741 raphael 46
* les projets doivent être indépendants (eflore, cel, projets nvjfl, ...)
890 raphael 47
* les données nécessaires à l'export et à l'import sont massives
741 raphael 48
* or les webservices s'appellent parfois récursivement, sont lents et inadaptés
49
 
890 raphael 50
La conséquence est que la construction d'une table dérivée de bdtfx/bdtxa/isfan contenant
741 raphael 51
les informations utiles pour CEL s'avère nécessaire.
822 raphael 52
cel_references.sql construit une telle table.
741 raphael 53
 
54
Suivent quelques éléments de compréhension et exemples de requêtes liés à cette initialisation:
55
 
890 raphael 56
1) Détermination des noms vernaculaires meilleurs et uniques:
741 raphael 57
 
890 raphael 58
Ce sont ceux qui ont le num_statut le plus élevé pour un num_taxon donné dans nvjfl_v2007.
741 raphael 59
Plusieurs méthodes sont exposées ci-dessous, sachant que le couple (référentiel, num_nom) est la clef
60
unique de cel_references.
61
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.
62
1.1:
63
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;
64
# 12311 résultats
65
 
66
1.2:
67
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;
68
# 2680 résultats
69
 
70
1.3:
71
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;
72
# 2680 résultats
73
Mais problème ensuite: SELECT n.* from cel_references NATURAL JOIN nvjfl_v2007 n WHERE `nom_commun` = '' AND n.code_langue = 'fra';
74
 
75
 
76
 
77
2) à propos de l'insertion dans cel_references proprement dit:
78
Le modèle simplifié théorique de base est le suivant:
79
 
80
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `nom_sci`, `auteur`, `nom_commun`) \
81
       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 \
82
       UNION \
83
       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 \
84
       UNION \
85
       SELECT "isfan", b.num_nom, b.num_nom_retenu, b.nom_sci, b.auteur, NULL FROM isfan_v2013 b;
86
 
890 raphael 87
Mais évidemment, les noms communs n'existent que pour bdtfx[nvjfl], bdtxa[nva], de même que les données baseflor/baseveg.
88
Plusieurs tables temporaires sont donc nécessaires en particulier puisque toutes les colonnes n'ont pas
89
des indexes adaptés pour effectuer des JOIN efficaces dans le cadre de ce script d'intégration particulier.
741 raphael 90
 
91
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):
92
INSERT INTO @dst (`referentiel`, `num_nom`, `num_nom_retenu`, `num_taxon`, `nom_sci`, `auteur`, `nom_commun`) \
93
       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 );
94
 
95
 
96
À noter:
97
SELECT b.num_nom, b.num_nom_retenu, b.num_taxonomique, b.nom_sci FROM bdtfx_v1_01 b where b.num_taxonomique = '';
98
# 3968, c'est à dire des num_taxon vides, pourtant INDEX et NOT NULL.
99
Idem pour bdtxa
100
 
101
 
102
3) à propos de baseveg/baseflor:
103
Note au 16/07/2013: les schémas sont susceptibles de changer à l'avenir.
104
La jointure entre bdtfx et baseflor se fait sur le référentiel ("bdtfx") et num_nom.
105
À partir de là nous disposons d'un catminat_code qui correspond au code_catminat dans baseveg, afin d'obtenir le syntaxon.
106
Quelques exemples:
107
 
108
SELECT code_catminat, syntaxon, lumiere, hum_atmos, temperature, oceanite, ph_sol, hum_edaph, texture_sol FROM baseveg_v2013_01_09;
109
SELECT * from baseflor_v2012_12_31 where cle = 1174;
110
 
111
SELECT * from baseveg_v2013_01_09 where code_catminat = '05/3.0.1.0.2' AND niveau = 'ALL';
112
# 7 résultats
113
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;
114
# 7 résultats
115
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;
116
# 1 résultat
822 raphael 117
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`