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