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`
|