Subversion Repositories eFlore/Projets.eflore-projets

Rev

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