PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Pierre Chevalier Géologue EI Mesté Duran 32100 Condom Tél+fax: 09 75 27 45 62 05 62 28 06 83 06 22 08 23 99
Auteur: P. Chevalier Date:
10/05/2012
S6 licence STE - UE Géologie numérique – 2011-12 Support de cours
1.Généralités, contenu 1.1.Thématique générale Constitution et gestion des bases de données géologiques. Exploitation de données par divers outils, en aval de bases de données.
1.2.Objectif Appréhender la notion fondamentale de Base de Données, leur rôle, leur puissance, leur omniprésence. Mais aussi ne pas cacher leur complexité. Démystifier certaines idées reçues, les bases de données relationnelles, bases de données hiérarchiques, avantages, inconvénients, effets de mode. Architectures clients-serveur, ou fichier: avantages et inconvénients. Application aux géosciences, lien avec la géographie: les données sont pratiquement toujours géoréférencées. En 2D (cartographie géologique) mais aussi en 3D (sondages, modélisation de corps), voire 4D (évolution dans le temps, soit les temps géologiques, soit le temps de l'avancement d'un chantier, par exemple).
1.3.Contenu Constitution d'une base de données géologiques, utilisation avec des SIGs. Utilisation de logiciels de SGBDR libres, avec emphase sur postgreSQL et sqlite. Utilisation conjointe de logiciels SIG libres: qGIS, GvSIG1. Exemple de travail avec d'autres logiciels propriétaires. Recueil de données de base. Constitution d'une base de données. Requêtes de vérifications, tests d'intégrité, intégrité référentielle. Une fois la base propre, requêtes plus « productives »: faire parler les données. Exemples du "monde réel": des applications dans le monde industriel, exemples en exploration minière. Moins prosaïque: faire parler les données sous forme cartographique: utilisation conjointe de SIG et bases de données.
1.4.Évaluation Production d'une minute sur la région de Bédarieux (terrain à faire prochainement), avec petit rapport combinant des résultats de requêtes, cartes thématiques, sur une thématique au choix. 1
Au cours de l'enseignement, gvSIG n'a finalement pas été utilisé, comme il était initialement prévu.
1 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
2.Introduction, contexte Ce cours est principalement consacré aux Bases de Données, et à leurs applications en Sciences de la Terre. La mise en place de ce cours partait d'un constat amer qu'au jour d'aujourd'hui, dans le monde de l'entreprise, on ne trouve personne, ou si peu, qui soit correctement formé à ces outils essentiels, dans notre domaine des Sciences de la Terre. Des bévues colossales se font quotidiennement, en tout innocence, en croyant bien faire, dans le domaine de la gestion des données. Les choses vont en ce moment de mal en pis, avec l'accroissement exponentiel des possibilités d'échanges rapides de données. Ce constat vaut en particulier parmi les jeunes qui arrivent sur le marché de l'emploi, alors qu'on pourrait s'attendre à l'inverse, que ces jeunes ont eu une formation solide en informatique, que nous, plus âgés 2, n'avons jamais eue dans le cadre d'un cursus "normal", nous sommes le plus souvent autodidactes en la matière. Il y a une réelle nécessité, au moins de sensibiliser les jeunes à ces techniques, de manière à ce que ces (gros) mots ne soient pas qu'un vocabulaire vide de sens, dont on se gargarise dans les salons et Réunions au Sommet, en prenant à la légère des décisions lourdes de conséquences, sans savoir de quoi l'on cause... Le but n'est pas ici de former des administrateurs de bases de données, mais plutôt de fournir aux étudiants les notions de bases pour que, dans leur vie professionnelle, lorsqu'ils seront confrontés à une problématique de base de données: ● ● ●
●
ils comprennent les enjeux, le vocabulaire, la logique de base, ils ne se fassent pas trop « embobiner » par des beaux parleurs, ils sachent où chercher, au besoin, toute la panoplie de base nécessaire à l'implémentation: ○ la documentation, ○ les commandes de base, ○ comment constituer une base de données: ■ notions d'architecture de base de données, ■ commandes nécessaires à l'implémentation; ○ les extensions, notamment cartographiques, ○ l'utilisation avec une panoplie d'outils, en passant par des conduits: ■ odbc, jdbc, ■ exports ASCII, ■ automatisation; ils sachent aborder d'une manière critique ces problématiques, en connaissance de cause.
3.Quelques prérequis préalables rapides: informations, informatique Informatique = technique de l'information. Mises en œuvre par des ordinateurs, qui fonctionnent, aujourd'hui, en binaire. Qu'est-ce qu'une information? => pour un être humain: ... => pour un ordinateur: des 0 et des 1 notions de bits de données, données binaires regroupements de bits en octets (bytes), regroupements d'octets en kilo, méga, giga, téra... octets.
2
L'auteur date de 1967.
2 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
4.Les Bases de Données: notions théoriques 4.1.Généralités, modèles théoriques Les principes, les mythes, avantages/inconvénients. Petit récapitulatif historique sur ce qu'est un SGBD, soit un Système de Gestion de Bases de Données. Quelques modèles: modèle hiérarchique, modèle relationnel pour l'essentiel. Notion de « maths modernes » (terme surrané qui désignait naguère ce qui avait trait à la théorie des ensembles): le modèle relationnel y fait largement appel. Un grand regret que les premières soient pratiquement passées de mode (j'en connais guère plus que 2 qui tournent: l'une est une grande honte, l'autre est d'un accès archi-abscon), alors qu'elles sont extraordinairement bien adaptées à notre mode de pensée, à nous géologues, et à tous les naturalistes d'une manière générale. Si ces bases de données hiérarchiques revenaient à la mode, nombre de problèmes qui nous (les administrateurs de bases de données en géologie) empoisonnent s'évanouiraient comme neige au soleil. Un aperçu de la puissance, de l'omniprésence (souvent à notre insu) des bases de données dans notre quotidien: banques, finances, supermarchés, sécurité sociale, etc. Démystification de quelques idées reçues: les bases de données sont organisées, mais elles ne sont (le plus souvent) pas ordonnées, paradoxalement. Démonstration par aplusb que sans avoir recours aux bases de données, c'est le bazar dans les données en moins de 2. Surtout en cette période moderne où les échanges de donnnées se font de manière ultrarapide, en dupliquant les données, sans mécanismes de mises à jour de versions, la plupart du temps, ce qui génère des quantités de versions, très difficiles à re-homogénéiser et ordonner. Règle générale: en matière de données, il est très facile de diviser, de multiplier 3; il est très difficile de synthétiser, regrouper, fusionner4. En ce moment, la grande mode, ce sont les bases de données relationnelles, tout le monde ne jure plus que par ça... Enfin, presque: le mouvement NoSQL (très mal nommé) est là, avec des bases de données simplistes, hyperspécialisées et hyperefficaces, mais qui n'ont pas que des avantages... Mais on n'en est pas là. Les XML aussi, mais leur efficacité est loin d'être au rendez-vous dès que des tailles importantes sont atteintes, ils ne peuvent rivaliser avec les SGBDR en termes d'accès multiutilisateurs, de mises à jour importantes, de performances. Le JSON combine astucieusement le côté hiérarchique du XML tout en étant moins verbeux; dans la pratique, il est peu utilisé pour des données massives. Les SGBDR (Systèmes de Gestion de Bases de Données Relationnelles) ont prouvé leur efficacité, ce sont eux qui sous-tendent la plupart des systèmes qui nous entourent: billets d'avions, de train, paiements par carte, caisses de supermarchés, systèmes de réservations, gestion de clientèle, suivis de productions, etc. Donc relationnel = ce qui marche, très bien, aujourd'hui. On démystifie le fait que pour beaucoup, relationnel => relation = jointure, ce qui est FAUX5. Relation = table, on fait le lien avec la théorie des ensembles. Et Facebook, dans tout ça? Il n'échappe pas à la règle: il est sous-tendu par des bases de données, mais pas des bases de données relationnelles; il s'inscrit dans le mouvement NoSQL. Dans le paysage du web moderne, énormément de pages web dynamiques sont supportées par une base de données. La combinaison la plus courante est LAMP, pour un serveur web combinant Linux (système d'exploitation ), Apache (serveur http), Mysql (serveur de base de données), Php (langage générant dynamiquement des pages html en allant piocher dans la base mysql). Dans le temps, on trouvait aussi des WAMP, qui se font plus rares.
3 4 5
Ce qui engendre l'anarchie, la perte de temps, le doute, la dispersion. Afin d'arriver à l'unicité, en matière de donnée. Ce qui est fondamental. Cette confusion est issue d'une erreur de traduction d'un logiciel, au siècle dernier.
3 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
4.2.Exemples de SGBDs Tour de quelques bases de données concernant la géologie, aperçu de ce qu'on pouvait faire en croisant différentes bases. Aperçu des différents SGBD, des plus antiques aux plus modernes, avantages et inconvénients des uns et des autres: ● ● ● ● ● ● ● ● ● ●
dBase IV, un vénérable ancêtre; foxpro; postgresql; oracle; mysql; firebird; access; sql express; sqlite; etc.
Certains sont basés sur une architecture client-serveur, d'autres sont basés sur un simple fichier, sans notion de client-serveur. Avantages et inconvénients des uns et des autres. Rappel: informatique = techniques de l'information, qui sont appliquées par des ordinateurs. Ce sont les programmes qui font le travail, les ordinateurs ne sont que de bêtes exécutants. Les programmes prennent des données, et en font d'autres choses. Par exemple, à partir de données d'altitude, un programme de SIG fabriquera une carte d'isocontours: le programme a fabriqué une image à partir d'un jeu de données. Opposition des approches: ●
●
approche traditionnelle d'un logiciel lambda: ○ ouverture d'un fichier contenant des données, ○ lecture des données et copie en mémoire vive (RAM) de la machine, ○ traitement dans la mémoire vive, ○ fabrication d'un résultat ○ éventuellement, écriture des données depuis la RAM vers un fichier; approche en passant par une base de données: ○ connexion à une base de données, ○ envoi d'une requête à la base, recueil des données fournies par la base, ○ traitement, ○ fabrication d'un résultat, ○ éventuellement, écriture directe dans la base de modifications.
La différence peut paraître subtile, mais à l'usage, c'est fondamentalement différent: ●
●
dans le premier cas, si l'on change les données initiales, ou si l'on change de fichier de base, il faut refaire les traitements; si la quantité de données excède certaines limites, on se trouve « coincé »6. Dans le pire des cas, on doit reprendre le programme de traitement, et/ou scinder les données en des sous-jeux. On est également limité par la mémoire de la machine: il sera impossible de charger plusieurs téra-octets de données sur une machine dont la RAM ne fait que quelques giga-octets, par exemple. On aura recours à l'artifice du swap, mais les performances s'écroulent. Dans le second cas, si les données changent, il n'y a rien à faire que de refaire tourner le programme de traitement: c'est le SGBD qui se charge de tout ce qui concerne la gestion des données, peu importe leur nombre. Tout SGBD digne de ce nom offre des capacités pratiquement illimitées, en matière de nombre d'enregistrements. On ne charge jamais l'ensemble des données en RAM, mais uniquement ce qui est utile, ce qu'on a besoin d'afficher, à la demande. Ainsi, une machine n'ayant que 2 Mo de RAM pourra lire très rapidement des masses de données de plusieurs Go, voire des To.
D'une manière générale, on aura intérêt à se tourner vers une solution de type base de données quand les données deviennent: ● ● ● ● 6
trop nombreuses (notamment quand on excède les capacités de certains tableurs en nombres de lignes); très vivantes (mises à jour fréquentes, avec nécessité de produire des résultats rapidement; par exemple des cartes d'avancements de travaux, ou des statistiques quotidiennes de gestion de chantier); susceptibles d'être considérées sur du long terme; susceptibles d'être traitées de manières standard, comme des jeux de données standards collectés lors de
Cas typique avec la plupart des tableurs, qui ne savent pas gérer plus de n lignes de données.
4 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
●
campagnes de terrain; si nombreuses et à gérer par plusieurs personnes qu'un accès concurrent aux données devient indispensable (cas où plusieurs opérateurs doivent entrer des données en même temps, pour de gros projets générant beaucoup de données).
Au contraire, il est parfois inutile de se compliquer la vie avec une base de données lorsque: ● ● ● ●
on doit faire une opération ponctuelle, comme une étude sur un sujet, bien cerné dans l'espace et le temps, avec un jeu de données réduit, et qu'on n'aura, selon toute probabilité, aucune occasion de refaire ce travail ou un travail équivalent à l'avenir (exemple: rapports d'expertise); il y a une équipe réduite, voire unipersonnelle; le modèle des données est déjà défini, et tellement compliqué qu'il serait contre-productif de l'implémenter dans une structure de base de données; on n'a pas les compétences requises: on peut certes les acquérir sur le tas, mais en prêtant garde.
Le choix entre travailler avec une base de données ou pas n'est pas anodin, il faut bien soupeser le pour et le contre, en se projetant dans le long terme.
4.3.Interaction avec un SGBD, interfaces Comment interagir, utiliser une base de données? En passant par une interface entre l'homme et la machine: on parle de Computer Human Interface, ou User Interface. Cela peut être: ● ●
une interface graphique (Graphical User Interface), qui se destine uniquement à un humain; une interface en mode texte (ligne de commande), qui se destine tant aux humains qu'aux machines.
L'interface la plus universelle est en mode texte 7, elle passe par un langage. Les langages: SEQUEL, QBE, SQL, QUEL. Différents types: langages formels, langages graphiques, avantages, inconvénients des uns et des autres. Le fameux et incontournable langage SQL, un modèle de simplicité et d'efficacité brutale. Un langage très naturel qui, moyennant un peu d'apprentissage, se parle (s'écrit) de manière assez instinctive. Pour ceux qui réfléchissent mieux en terme d'objet (les développeurs), il existe des ORM (Object-Relational Mapping), on a l'impression de manipuler, dans son langage de programmation, une base de données orientée objet, alors qu'on s'adresse à une base de données relationnelle. Avantage énorme des normes: le SQL, qui correspondent à la norme ISO 9075, permet en un tournemain de faire ce qu'on veut sur n'importe quel SGBD, pourvu que celui-ci respecte les normes en vigueur. Autant les bases ellesmêmes ne sont pas normalisées (mais ça n'est pas grave), autant leur interface, à savoir le langage permettant de leur parler, d'interagir avec elles, le sont. Et c'est tant mieux. En prenant un niveau d'abstraction plus loin, on trouve les interactions avec les bases de données via des formulaires, des résultats de requêtes, des états, des cartes, des graphiques, des applications complexes, des sites internet dynamiques, etc. Architectures client-serveur, on évoque le pourquoi et le comment, de l'intérêt de la chose.
4.4.Données géographiques Le Monde des Systèmes d'Informations Géographiques, très lié au monde des Bases de Données; on parle couramment de SIG/BD. Comme pour les bases de données, deux approches: ●
7
approche fichier: SIGs bureautiques, formats classiques: ○ shapefile (arcview, arcGIS): 3 fuchiers au moins: ● .shp contient les objets géographiques ● .dbf contient les attributs: c'est une table dBase ● .shx fait le lien entre les deux: pour chaque objet géographique correspond un enregistrement dans la table .dbf ○ mapinfo:
En informatique, la toute-puissance passe forcément, à un moment ou à un autre, par un langage, en mode texte: que ce soit de la ligne de commande interprétée (shell, programmation, scripts) ou du code compilé ou interprété (C, python, basic, C++, ruby, php, SQL).
5 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
4 fichiers au moins: ● .tab un en-tête ● .map contient les objets géographiques ● .id index ● .dat contient les attributs: c'est une table dBase, avec un encodage déguisé sqlite avec spatialite: 1 fichier: ● .sqlite (ou autre extension) contient toutes les tables; les tables contiennent l'information géographique ■
○
●
approche serveur: serveurs de données géographiques: passage par des conduits, nécessité de normes: rôle de l'OGC (Open Geospatial Consortium http://www.opengeospatial.org/) ○ WMS: Web Map Service on "sert" des cartes toutes faites ○ WFS: Web Feature Service on "sert" des entités, cartographiables ○ serveurs de données géographiques: ■ postgis = extension postgresql: les géométries sont un attribut dans une relation ■ mysql spatial extensions ■ mapserver ■ geoserver ■ sql server spatial ■ oracle spatial ■ etc.
Évolution historique: ● ● ●
première génération de SIGs: ○ géométries + données + index stockés sur système de fichiers => accès par logiciel propriétaire seconde génération de SIGs: ○ géométries + données + index stockées sur système de fichiers => accès par logiciel propriétaire ○ des données attributaires stockées sur SGBDR => accès par SQL troisième génération de SIGs: ○ données + données attributaires stockées sur SGBDR
5.Des exemples concrets d'applications de bases de données Ou: comment, à partir de sommes incommensurables de chiffres et de lettres patiemment et soigneusement ordonnées, on reproduit8 les créations de Dame Nature dans un ordinateur, et on se sert de ces reproductions pour comprendre l'agencement des formations géologiques. Application dans divers domaines.
5.1.Une base de données de référence en France: la BSS Exemple d'accès par le site Infoterre du BRGM: informations très diverses: indices, sondages, travaux lourds (puits et traçages), piézomètres, puits pour eau, forages pétroliers, carrières, etc. Application du Code Minier, de la Loi sur l'Eau. 8
Enfin, on tente de reproduire. On n'atteint jamais la complexité du monde réel, on se contente de le « modeler » d'une manière qui nous satisfasse, pour les besoins du moment (cartographie géologique, gisement, exploitation, exploration, synthèse d'aquifère, ou essais de pompages: à chaque thématique ses échelles, ses modèles.
6 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 1: BRGM, portail infoterre, quelque part en France: http://infoterre.brgm.fr/viewer/MainTileForward.do
Fig. 2: visualisation de points de la BSS, informations sur un point (icône "i")
7 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 3: informations sur détaillée sur le point 06448X4005: http://ficheinfoterre.brgm.fr/InfoterreFiche/ficheBss.action?id=06448X4005/GT
Fig. 4: autre exemple, au Pays Basque Nord: une des sources à la discordance régionale sur le Paléozoïque 8 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 5: fiche détaillée; il y a des documents scannés:
9 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 6: exemple de document scanné
10 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 7: autre exemple dans le Bassin Aquitain: points BSS; apparition d'artefacts: homogénéité de la base de données Tout ceci est sous-tendu par une base de données oracle, de type client-serveur, gérée au niveau national, avec des correspondants répartis dans chaque région. Les données sont très diverses, puisqu'elles incluent, outre des valeurs texte et des valeurs numériques, des images (scans de documents). Ceci requiert un travail important de collecte de données, de vérifications, de mise au propre, de codage, etc. de manière à pouvoir mettre à disposition du public ces données. Seule une architecture client-serveur autorise autant d'interactivité entre de multiples acteurs. Les fonds (cartes géologiques, cartes topographiques) sont également servis par des systèmes de bases de données géographiques, chargés de distribuer des données de type raster.
11 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
5.2.Exploration minière Il s'agit d'un des domaines de prédilection de l'auteur. Plus d'emphase a naturellement été donnée à ce domaine. Travaux à différentes échelles, depuis le régional (travaux stratégiques) jusqu'aux travaux tactiques. À grande échelle: prédominance des SIGs, de la cartographie:
Fig. 8: exemple: cartographie géologique: analyse thématique sur couche shapefile, à l'échelle d'un pays
Pour restreindre les zones à explorer, il y a nombre de méthodes d'exploration. Une des plus classiques est la géochimie. 12 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 9: exemple de données de géochimie sol, emplacements de sondages Télédétection, géophysique:
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Traitement de simple image satellite:
Fig. 11: traitement d'image satellite (google maps)=> mise en évidence d'altérations (JF, grass)
Fig. 12: on passe aux relevés de terrain: carte géologique issue du traitement de l'image satellite, calée sur les observations de terrain 14 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Extrait d'un fichier .gpx, issu d'un GPS, avec les observations de terrain inclues dans le fichier: ... 381.526120 794 Affleurement Nm5/80/W/40/N/D? Roche blanche, siliceuse, fabrique forte planaire + ligne, grain grossier. Séricite. Friable. Probablement hydrothermalite, cisaillement et altération hydrothermale = blanchiment, silicification. Critère S/C, dubitatif, dextre-normal:? ... 2012-01-07_16h24 289.013550 288.532959 ... 341.405273
=> fichier GPX = XML = langage balisé; structure hiérarchique. Contient des points d'observation (WPT pour waypoints) et des tracés (TRK pour tracks). => cartographiable directement par outils SIG.
Fig. 13: point d'affleurement, observations; ce rendu est tiré du fichier .gpx
15 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
On restreint encore, on passe aux tranchées:
Fig. 14: cartographie par GPS (simple relevés ponctuels d'affleurements, d'indices, et tracés); tranchées d'exploration Études spécifiques: étude structurale:
Fig. 15: étude structurale, cartographie structurale détaillée; toujours relevés par GPS; cartographie de symboles structuraux => utilisation conjointe d'outils SIG et DAO; fonds = données topographiques 16 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 16: compilation de données: ancienne carte géologique, scannée, géoréférencée => utilisation d'outils SIG
Fig. 17: grilles de géochimie sol tactique, tranchées d'exploration
17 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Niveau de zoom beaucoup plus restreint, et passage dans la troisième dimension: sondages destructifs:
Fig. 18: mise en place de sondeuse; foration en RC Sondages non destructifs => carottes de sondages:
Fig. 19: photographies de carottes de sondages d'exploration (échantillonnées à moitié)
18 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
=> comment mettre ceci sous forme de données? Et pourquoi? Pour pouvoir estimer des gisements, pour pouvoir se "promener" en 3 dimensions dans les corps géologiques, comprendre la géologie souterraine, l'agencement des formations, appréhender les corps en 3 dimensions, les explorer. Et, finalement, estimer les gisements qui seront exploités.
Fig. 20: document montrant un sondage, avec descriptions géologiques et résultats analytiques de l'échantillonnage: log de sondage
19 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 21: plan: sondages projetés verticalement, histogrammes de teneurs; points bleus signalant les sondages ouverts => requête dynamique
Fig. 22: session de travail: plan, coupe, et log de sondages => tout est connecté à une BD; projets de sondages complémentaires
20 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Modèle géologique d'un gisement: la pensée du géologue, à partir des coupes de sondages, retranscrite sous forme d'une coupe-type:
Fig. 23: schéma manuel conceptuel d'un modèle géologique de corps minéralisé Là, on n'a PAS recours à une base de donnée! Modèle de la minéralisation, sous forme de sondages avec des teneurs symbolisées par des niveaux de couleurs:
Fig. 24: vue en 3D des sondages, mettant en évidence les teneurs en or
21 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Tout ce qu'on vient de voir (ou presque) provient d'une base de données d'exploration. Base de donnée de sondages d'exploration: ● ●
aperçu de la structure, exploitation des données: ○ cartographie: logiciels de SIG, liaison dynamique aux données, ○ logiciels spécialisés, exemple de GDM9: ■ données, ■ plans, ■ coupes, ■ logs, ■ liaisons dynamiques entre toutes ces vues des mêmes données.
Fig. 25: schéma de base de données d'exploration => un peu complexe => nécessité de simplifier On voit XtraTrst10, comme exemple de données numériques appliquées à la géologie, à l'exploration minière. Il s'agit d'un gisement de métal précieux. Quelques notions de base d'exploration minière et d'exploitation minière. Puis vues en carte, sondages, avec les logiciels qGis, GDM. On montre là des choses « vivantes », où les logiciels « tournent » réellement, en liaison avec les bases de données, qui sont continuellement interrogées. Pour raccrocher avec la réalité, illustration par quelques photos de terrain. Ce sont des photographies numériques, ça s'inscrit donc bien dans la thématique du cours.
9
Notez qu'en tant qu’étudiants, vous pouvez demander une licence gratuite de GDM au BRGM pour la durée de vos études. Voyez sur http://gdm.brgm.fr/. 10 Il s'agit d'un projet vivant sur lequel travaille l'auteur; les noms sont ici mélangés, dans un souci de confidentialité.
22 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
En passant, on aborde une notion fondamentale: nous sommes des géologues, pas des informaticiens. Les ordinateurs sont là pour nous servir, pas pour nous asservir. Il faut toujours garder du recul par rapport à l'informatique, savoir revenir au papier et crayon, raccrocher à la réalité autant que possible. Ne jamais oublier le bon vieux précepte « sh!t in, sh!t out » qui s'applique ô combien à l'informatique. Ce n'est pas parce qu'on a un beau graphique généré bien proprement par un logiciel hors de prix que c'est la vérité: tout se fonde sur des données de base, qui doivent convenablement refléter la réalité des choses, pour l'échelle où l'on travaille. Et sur la manière d'exploiter ces données de base, c'est-à-dire la manière de faire des requêtes. Un petit exemple de deux manières différentes de poser la même question: on a une base de données contenant des données de sondages, concernant le dernier exemple. On s'intéresse aux récupérations des sondages carottés. Les pertes de carottes sont notées dans la table d'échantillonnage. On calcule, pour chaque sondage, le pourcentage de récupération: •
Les longueurs des sondages sont définies dans la table des têtes (collets): SELECT id, length FROM dh_collars ORDER BY id; id | length ------+-------S508 | 76.13 S509 | 118.05 S510 | 60.95 S511 | 44.75 S512 | 80.60 S513 | 81.00 S514 | 143.85 S515 | 117.05 S516 | 179.90 S517 | 90.05 S518 | 110.05 S519 | 174.00 S520 | 110.90 S521 | 183.05 (14 lignes)
Pour chaque sondage (GROUP BY id), on somme les pertes de carottes (qu'on convertit en mètres, avec un format à deux chiffres après le séparateur décimal): SELECT id, sum(core_loss_cm / 100)::numeric(10, 2) AS sum_core_loss_m FROM dh_sampling_grades GROUP BY id ORDER BY id; id | sum_core_loss_m ------+----------------S508 | 10.70 S509 | 13.70 S510 | 1.85 S511 | 7.85 S512 | 1.86 S513 | 2.15 S514 | 7.50 S515 | 6.00 S516 | 2.35 S517 | 0.85 S518 | 12.35 S519 | 6.35 S520 | 6.05
23 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique S521 | (14 lignes)
•
4.40
On fait la jointure avec la table des têtes, de manière à voir en même temps les longueurs des sondages et la somme des pertes de carottes: pour ce, on fait une sous-requête (alias tmp): SELECT dh_collars.id, length, tmp.id, sum_core_loss_m FROM ( SELECT id, sum(core_loss_cm / 100)::numeric(10, 2) AS sum_core_loss_m FROM dh_sampling_grades GROUP BY id ) AS tmp JOIN dh_collars ON (tmp.id = dh_collars.id) ORDER BY dh_collars.id; id | length | id | sum_core_loss_m ------+--------+------+----------------S508 | 76.13 | S508 | 10.70 S509 | 118.05 | S509 | 13.70 S510 | 60.95 | S510 | 1.85 S511 | 44.75 | S511 | 7.85 S512 | 80.60 | S512 | 1.86 S513 | 81.00 | S513 | 2.15 S514 | 143.85 | S514 | 7.50 S515 | 117.05 | S515 | 6.00 S516 | 179.90 | S516 | 2.35 S517 | 90.05 | S517 | 0.85 S518 | 110.05 | S518 | 12.35 S519 | 174.00 | S519 | 6.35 S520 | 110.90 | S520 | 6.05 S521 | 183.05 | S521 | 4.40 (14 lignes)
•
Ainsi, on peut calculer le pourcentage de récupération globale, pour chaque sondage: SELECT dh_collars.id, length, tmp.id, sum_core_loss_m, ((length - sum_core_loss_m)/length * 100)::numeric(10,1) AS recup_pc FROM ( SELECT id, sum(core_loss_cm / 100)::numeric(10, 2) AS sum_core_loss_m FROM dh_sampling_grades GROUP BY id ) AS tmp JOIN dh_collars ON (tmp.id = dh_collars.id) ORDER BY dh_collars.id; id | length | id | sum_core_loss_m | recup_pc ------+--------+------+-----------------+---------S508 | 76.13 | S508 | 10.70 | 85.9 S509 | 118.05 | S509 | 13.70 | 88.4 S510 | 60.95 | S510 | 1.85 | 97.0 S511 | 44.75 | S511 | 7.85 | 82.5 S512 | 80.60 | S512 | 1.86 | 97.7 S513 | 81.00 | S513 | 2.15 | 97.3 S514 | 143.85 | S514 | 7.50 | 94.8 S515 | 117.05 | S515 | 6.00 | 94.9 S516 | 179.90 | S516 | 2.35 | 98.7 S517 | 90.05 | S517 | 0.85 | 99.1 S518 | 110.05 | S518 | 12.35 | 88.8 S519 | 174.00 | S519 | 6.35 | 96.4 S520 | 110.90 | S520 | 6.05 | 94.5 S521 | 183.05 | S521 | 4.40 | 97.6 (14 lignes)
On a là deux représentations des mêmes données: il est évident que la présentation sous forme de pourcentage paraît bonne, à première vue, et le sondeur aura tendance à montrer ces chiffres. En revanche, le client bondira d'horreur en voyant des pertes de carottes décamétriques, et sera en droit de réclamer au sondeur un travail mieux fait.
24 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
5.3.Géotechnique, aménagements: exemple de reconnaissances géotechniques en G0 pour des tunnels Exemple vécu de reconnaissance pour les tunnels autoroutiers de l'A89, dans les Monts du Lyonnais, région de Violay. Il s'agir d'études géotechniques assez « grand luxe ». Radiocarottages, diagraphies, diagraphies orientées. Démonstration de ce qui se fait à partir de sommes de données recueillies en sondages, imagerie de paroi, mesures structurales, confrontation avec les carottes 11.
Fig. 26: sondeuse travaillant à l'horizontale, dans l'axe du projet de tunnel autoroutier
Fig. 27: log de sondage complet, incluant des diagrphies, notamment des imageries de paroi; structures déterminées par les imageries orientées
11 Au passage, on évoque des notions fondamentales de sondages, destructifs ou carottés, sondages déviés, dirigés, etc.
25 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 28: caisses de carottes: toujours revenir aux fondamentaux
26 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
5.4.Risques naturels, aménagements Exemple vécu d'étude de risques naturels à La Réunion, dans le cadre de missions de Service Public. Notions d'aléas, d'enjeux et de risques déjà assimilées. Modèle de raisonnement, process à partir du MNT (DEM) fourni par l'IGN, raisonnements naturalistes et pragmatiques traduits en calculs de grilles, calage sur la cartographie naturaliste, ajustement des paramètres de calculs jusqu'à ce que les résultats coïncident de manière satisfaisante, puis process de l'ensemble du du territoire: ce qui prend des mois de cartographie de terrain naturaliste se fait en quelques secondes. Nécessité d'avoir une vision critique, de vérifier les résultats des calculs. Tout spécialement dans ce domaine d'application.
Fig. 29: cartographie d'aléa risques naturels mouvements de terrain
27 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Aléa érosion: analyse multicritères, recours massif à des traitements SIG. Tout est basé sur un ensemble de données et de bases de données.
Fig. 30: détermination de l'aléa érosion: combinaison de données
28 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 31: processus complexe: genèse de la validation des résultats cartographique => itérations, jusqu'à ce que le résultat soit satisfaisant
Fig. 32: une carte d'aléa érosif Fig. 33: répartition de l'aléa érosif, par bassins versants
29 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 34: Final: carte d'aléa Ça, c'était pour la partie théorique. Maintenant, de la pratique. Durant les cours, théorie et pratique ont été mélangées dans le temps.
6.Pratique: mise en place d'une base de données Dans un but pédagogique, on va mettre en œuvre l'intégralité de la mise en place d'une base de données, de A à Z. On se positionne là dans le rôle de l'administrateur de base de données, un métier souvent ingrat. L'autre bout de la chaîne est l'utilisateur de données, celui qui, en tant que client, utilise les données mises à disposition. Au cours des séances, on a tantôt pris un rôle, puis l'autre.
6.1.Base de données géologiques de l'Université de St-Etienne: poursuite de l'action entamée en 2011 Afin de joindre l'utile à l'agréable, en mai 2010, en compagnie d'Arnaud Villaros, nous avions commencé à mettre en place une base de données qui soit utile à la fac. On avait passé en revue les besoins inhérents au labo, et on était très vite retombé sur des problèmes récurrents un peu partout. Il s'agissait de stocker ces types d'informations: ● ● ● ● ● ● ●
observations de terrain, structurale, photos, échantillons, analyses, descriptions de lames, analyses minérales sur lames.
30 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
En 2011, on avait commencé à mettre des sondages dans cette base. On était parti sur un moteur de base de données libre, qui est d'une rare efficacité: PostgreSQL. Le moteur n'est pas installé sur les machines, mais chacun pourra faire cela librement, chez lui, sur son ordinateur. On va recréer une base de données, y reverser les données recueillies l'an dernier, puis continuer à étoffer cette base. Il faudrait, pour continuer de manière constructive cette action, mettre en place un serveur de base de données qui héberge de manière stable et pérenne ces données: cette action a été menée, et un serveur a été mis en place 12, surnommé "linuxette", tournant sous Ubuntu, et situé physiquement dans le bureau des thésards. "Linuxette" a pour adresse IP 161.3.7.249 L'utilisateur principal a pour nom ordi, et mot de passe u... Ouverture de session sur cette machine, par ssh: # pierre@autan:~$ < 2012_05_09__15_35_34 > ssh [email protected][email protected]'s password: Welcome to Ubuntu 11.04 (GNU/Linux 2.6.38-14-generic i686) * Documentation:
https://help.ubuntu.com/
36 packages can be updated. 36 updates are security updates. New release 'oneiric' available. Run 'do-release-upgrade' to upgrade to it. Last login: Wed Apr 18 16:55:01 2012 from bge-nomad7.univ-st-etienne.fr ordi@ordi-desktop:~$
6.2.Configuration du serveur de base de données: postgresql Pour faire une base de données, en mode client-serveur, il faut un moteur qui tourne. On a commencé par se connecter, depuis le site de la Tréfilerie, sur la machine de JF, qui faisait office de serveur de base de données. On a interagi avec la base de données par l'intermédiaire d'un "conduit" en mode texte, via putty. On a ensuite procédé de même, sur le serveur "linuxette". Au passage, on rappelle que le travail d'un serveur SGBDR consiste: •
•
pour moitié environ, à gérer les requêtes, ◦
en y répondant rapidement,
◦
gestion des indexations,
◦
gestion des déclencheurs (triggers);
pour l'autre moitié environ, à gérer les accès concurrents des divers utilisateurs de la base de données: ◦
bloquer les enregistrements ou les champs ou les tables en écriture pour le premier utilisateur modifiant une donnée,
◦
gérer les droits d'accès, d'écriture à divers enregistrements, tables, champs, etc.
◦
fournir des réponses aux requêtes qui soient cohérentes; recours aux mécanismes de transactions atomiques, entre autres.
Le second aspect est souvent passé sous silence; il convient de s'y atteler en premier, en définissant des utilisateurs et leurs droits. Il faut donc pouvoir accéder au serveur. Compte tenu du nombre d'étudiants, il serait fastidieux de faire un login par étudiant: on fait donc un seul compte générique. Je fais un compte pour les étudiants; tout le monde aura le même login: Ajout compte unix: => login: etudiants, mdp: etie...: root@ordi-desktop:/home/ordi# adduser etudiants Ajout de l'utilisateur `etudiants' ... Ajout du nouveau groupe `etudiants' (1001) ... 12 Dans un premier temps, on a commencé à se connecter sur la base de données postgresql hébergée par l'ordinateur de JF.
31 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique Ajout du nouvel utilisateur `etudiants' (1001) avec le groupe `etudiants' ... Création du répertoire personnel `/home/etudiants'... Copie des fichiers depuis `/etc/skel'... Entrez le nouveau mot de passe UNIX : Retapez le nouveau mot de passe UNIX : passwd : le mot de passe a été mis à jour avec succès Modification des informations relatives à l'utilisateur etudiants Entrez la nouvelle valeur ou « Entrée » pour conserver la valeur proposée Nom complet []: etudiants université St-Etienne N° de bureau []: Téléphone professionnel []: Téléphone personnel []: Autre []: chfn : nom contenant des caractères non ASCII : etudiants université St-Etienne Ces informations sont-elles correctes ? [O/n]
Ajout d'un compte postgresql pour « etudiants »: il faut pouvoir se connecter en tant qu'utilisateur de base de données: root@ordi-desktop:/home/ordi# su postgres postgres@ordi-desktop:/home/ordi$ psql psql (8.4.11) Saisissez « help » pour l'aide. postgres=# CREATE USER etudiants WITH PASSWORD 'etienne'; CREATE ROLE postgres=#
Le gros avantage, sur un système Unix, d'avoir un identifiant unix identique à l'identifiant de la base de données est que l'authentification est gérée par le shell. On n'a donc pas à entrer de mot de passe à tout bout de champ. Configuration des droits: root@ordi-desktop:/home/ordi# vim /etc/postgresql/8.4/main/pg_hba.conf ... # Database administrative login by UNIX sockets local all postgres
ident
# TYPE
METHOD
DATABASE
USER
CIDR-ADDRESS
# "local" is for Unix domain socket connections only local all all # IPv4 local connections: host all all 127.0.0.1/32 # IPv6 local connections: host all all ::1/128
ident md5 md5
#pour autoriser tout le monde à St-Etienne en 2012: host all all 161.3.109.0/24 md5 host all all 161.3.7.0/24 md5 host all all 161.3.111.0/24 md5 host all all 161.3.111.145/32 md5 #pour autoriser tout le monde à St-Etienne en 2012 dans la salle, en réseau local déconnecté et autonome: host all all 192.168.1.0/24 md5
On doit redémarrer le serveur, pour prendre en compte ces modifications de la sécurité: root@ordi-desktop:/home/ordi# /etc/init.d/postgresql restart * Restarting PostgreSQL 8.4 database server
[ OK ]
Installation de putty sur tous les postes.
6.3.Création d'une base de données On dialogue avec le moteur de la base via la ligne de commandes, en lui passant des instructions SQL. Peu importe l'outil, il en existe plein. Le plus minimal est le psql, dont la légèreté lui permet de passer à travers n'importe quel "tuyau" (un terminal poussif, un tunnel ssh, etc.). Pgadmin fournit aussi un outil convivial pour dialoguer en SQL. Et il existe plein d'autres outils: peu importe le vin (l'outil), pourvu qu'on ait l'ivresse (le SQL). Pour créer la base, qu'on appelle bdgeol, on colle la ligne suivante dans un quelconque terminal permettant d'avoir la ligne de commande SQL:
32 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique CREATE DATABASE bdgeol ENCODING='UTF8';
L'encodage UTF8 permet d'avoir des caractères accentués correctement gérés. Par défaut, c'est l'encodage à choisir, au jour d'aujourd'hui. On donne tous les droits à l'utilisateur « etudiants » pour cette base: GRANT ALL ON DATABASE bdgeol TO etudiants;
En même temps, on crée une base pour l'utilisateur par défaut de la machine: CREATE DATABASE ordi ENCODING='UTF8';
Ainsi qu'une base pour l'utilisateur « etudiants »: CREATE DATABASE etudiants ENCODING='UTF8';
Il faut ensuite nous "logger" sur notre base de données tout juste créée. En ligne de commande, ça donne quelque chose comme ça: psql bdgeol
ou: psql -d bdgeol
Pour se connecter depuis une autre machine: psql -d bdgeol -h 161.3.7.249 -U etudiants
Il faut mettre l'extension spatiale: 13 postgis est fourni sous forme d'une série de scripts, à implémenter sur la base de données: psql bdgeol -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql psql bdgeol -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
Pareil pour la base etudiants: psql etudiants -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql psql etudiants -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
On met les tables de la base de JF dans la base de données etudiants: etudiants=> \dt Liste des relations Schéma | Nom | Type | Propriétaire --------+------------------+-------+-------------public | geol_fr | table | etudiants public | geometry_columns | table | etudiants public | hercynian_map | table | etudiants public | magm_fol_trends | table | etudiants public | magm_type | table | etudiants public | mc_granites | table | etudiants public | metam_fol_trends | table | etudiants public | metam_points | table | etudiants public | metam_type | table | etudiants public | sondages | table | etudiants public | spatial_ref_sys | table | etudiants public | struct_trends | table | etudiants (12 lignes)
13 Durant le cours, l'extension avait été mise par le biais d'un template, mais son installation n'était pas totalement fonctionnelle: ainsi, l'installation sera fonctionnelle. Les commandes shell sont à adapter en fonction de la plate-forme, comme dans beaucoup de cas.
33 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
On peut maintenant cartographier ces tables, depuis un client SIG, en allant chercher une source de données postgis. Exemple dans qgis: •
menu couche / ajouter une couche postgis
•
connexion: •
•
nouveau: •
nom: linuxette
•
hôte: 161.3.7.249
•
base de données: etudiants
•
nom d'utilisateur: etudiants
•
mot de passe: eti...e
•
enregistrer les noms d'utilisateur et mot de passe,
•
tester la connexion
connecter: •
choisir les couches;
•
attention aux systèmes de coordonnées! Ne pas oublier d'activer la reprojection à la volée dans qgis.
On peut mélanger ces données à n'importe quelle autre donnée géographique.
Fig. 35: un exemple, avec des données de la "linuxette", et des données diverses
34 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 36: le même exemple, plus zoomé sur la France
Fig. 37: le même exemple, zoomé vers le Massif Central
35 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Un exemple de requête SELECT "Pluton", avg("U") AS U_moy, stddev("U") AS U_stdev , count("U") AS nb_U FROM mc_granites GROUP BY "Pluton" HAVING count("U") != 0 ORDER BY avg("U") DESC;
On veut ici calculer, en regroupant par pluton, la moyenne et l'écart-type de l'uranium, en écartant les cas où il n'y a pas d'analyses. Et on trie le résultat par teneur moyenne en uranium descendant (les plutons les plus "fertiles" en premier: Pluton | u_moy | u_stdev | nb_u ------------------------------+------------------+-------------------+-----Mont-Lozere/Pont de Montvert | 13.25 | | 1 Aigoual | 11.8333333333333 | 6.50243102196419 | 30 Mayet de Montagne | 10.875 | 6.75462804305315 | 8 PRAGNOT-VIMONT | 10.0694444444444 | 4.91426893073941 | 72 LES BOIS NOIRS | 10.013698630137 | 4.65746377788721 | 511 FAISCEAU SYNCLINAL | 10 | 4.24264068711928 | 2 BATHOLITE DES SETTONS | 9.38333333333333 | 10.3630563953453 | 60 MAYET-ARFEUILLES | 9.2991452991453 | 4.01562671122187 | 117 BATHOLITE DE LUZY | 8.56 | 6.61217183129194 | 50 LAMBEAUX VOLCANO-SEDIM. | 8.14285714285714 | 2.3682199240074 | 28 | 7.94915254237288 | 3.34470105263565 | 59 Borne | 7.93333333333333 | 2.60742529455144 | 6 MONTS DE LA MADELEINE | 7.83673469387755 | 3.3327301509099 | 98 Velay et enclaves | 6.90777202072539 | 3.68081904804099 | 193 R.VOLC.-TOIT DU GRANITE | 6.69230769230769 | 3.01329382373757 | 52 BASSIN DE L'ARDOISIERE | 6.5 | 3.89138242053607 | 8 Mont-Lozere/Finiels | 6.46 | 2.13731198003474 | 5 AVALLON | 6.25 | 2.98607881119482 | 4 R.VOLCANIQUES | 5.92307692307692 | 1.54720591837226 | 26 Porph_enclave_Velay | 5.75 | 1.76776695296637 | 2 MESVRES | 5.125 | 4.48608961123159 | 8 | 4.66749594594595 | 3.29851984249802 | 74 ST JULIEN-LA-VETRE | 4.640625 | 1.73997981652224 | 64 GENAY | 4.33333333333333 | 0.577350269189626 | 3 CHASTELLUS-SUR-CURE | 3.47368421052632 | 2.19515681678836 | 19 BASSIN DE FERRIERES | 3 | 1.54919333848297 | 6 MONTJEU | 2.81818181818182 | 1.60113596038449 | 11 R.VERTES-MAGMATISME INIT. | 2.8 | 2.16794833886788 | 5 SOCLE-ANATEXITES | 2 | 1.4142135623731 | 2 (29 lignes)
Interfaces: par qgis, pgadmin.
6.4.Implémentation de la base: mise en place de tables, requêtes, vues On continue le travail commencé les années précédentes. Dans un but pédagogique, on refait ce travail, en passant un peu plus rapidement sur certains points, pour se concentrer un peu plus sur l'évolution de la base. La première partie du travail commence par la définition de l'architecture de base de données. Cela se fait sur papier, en définissant les données, leur structure, les tables, les champs, etc.
a)Observations de terrain Faisons la table des observations de terrain: on nomme cette table field_observations 14. La structure de la table est d'abord définie en réfléchissant, sur une feuille de papier, puis implémentée. Pour ce, on se connecte à la base de données bdgeol: psql -h 161.3.7.249 -U etudiants -d bdgeol
Puis, en ligne de commande sql, ou via une fenêtre pgadmin, ou tout autre client: CREATE TABLE field_observations ( obs_id varchar NOT NULL, x numeric, y numeric, z numeric, srid integer, code_litho character(4), code_unit character(4), description varchar, geologist varchar, 14 On nomme les objets, variables, etc. en langue anglaise: cela est la coutume en informatique.
36 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique numauto serial, date date );
On voit ainsi la syntaxe SQL pour créer une table. Quelqu'un dans la salle a créé par inadvertance une table field_observation (au lieu de field_observations), cela nous donne l'occasion de voir la syntaxe pour détruire une table: DROP TABLE field_observation;
On met une clé primaire, une saine habitude: ALTER TABLE field_observations ADD CONSTRAINT field_observations_id PRIMARY KEY (obs_id);
Les données à insérer sont dans un fichier .csv, on va le verser dans la table: au shell: tail -n +2 field_observations.csv | psql -d bdgeol -c "\COPY field_observations FROM stdin WITH CSV"
Ceci est un enchaînement de plusieurs commandes unix; on décortique ce que cela veut dire: \COPY table FROM truc WITH CSV
est une instruction spécifique à postgresql qui va copier truc dans table, en prenant du CSV (comma delimited values). À la place de truc, on spécifie stdin, pour STandarD INput, l'interface standard d'entrée (que ce soit en C, C++, shell, tous unix, ceci est normalisé, tout comme stdout pour STandarD OUTput, ou stderr pour STandarD ERRor). On prend cette instruction, et on la met entre guillemets, pour la passer à psql, qui travaille alors en mode non interactif: psql va simplement prendre des instructions (la chaîne entre guillemets) qui lui disent de copier depuis l'entrée standard dans une table. Ensuite, on recueille les données contenues dans le fichier field_observations.csv, en omettant la première ligne (qui contient les noms de champs, que l'on ne veut pas; bien évidemment, il convient d'avoir EXACTEMENT les mêmes champs dans le fichier .csv et dans la table), à l'aide de tail15: tail -n +2 field_observations.csv
Et on « passe » le résultat de cette commande (tail) à l'autre commande (psql) par un « pipe » unix, symbolisé par | tail -n +2 field_observations.csv | psql -d bdgeol -c "\COPY field_observations FROM stdin WITH CSV"
Et voici la ligne complète reconstituée: une série de petites commandes qui se parlent les unes aux autres.
Il est important de distinguer, parmi ces instructions, deux types de commandes: 1.
ce qui relève du shell, c'est-à-dire de l'interface utilisateur en mode texte de la machine: il s'agit de commandes et de programmes; par exemple: dir
liste, sous MS-DOS, le contenu d'un répertoire, ls
fait la même chose sous unix ou MacOS, notepad fichier.txt
lance le programme notepad en ouvrant le fichier fichier.txt 2. les commandes SQL, qui doivent être entrées à l'invite du programme psql; par exemple: SELECT * FROM table1;
renvoie tous les enregistrements de la table table1 CREATE DATABASE exemple; 15 tail est un de ces petits programmes qui se trouvent sur tout système unix, qui sont hyperutiles au jour le jour; on peut aussi citer cat, colrm, grep, sort, wc, head, etc.
37 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
crée la base de données exemple. On continuera un peu plus tard de faire et remplir nos tables.
b)Lexique: Faisons un lexique pour la colonne des codes lithologiques de la table des observations de terrain. Voici la manière « normale » de le créer: CREATE TABLE lex_code_litho ( code_litho character(4), description varchar, count bigint );
L'an dernier, on avait préfèré recréer le lexique des codes lithologiques, au lieu d'avoir à le remplir à la main. Voici ce qui fut fait; comme les données ont changé entretemps, on ne va pas s'embêter à tout refaire, nous reprendrons simplement le résultat; voyons simplement les procédures qui ont été suivies, à titre indicatif: Un petit champ supplémentaire dans la table des descriptions: ALTER TABLE field_observations ADD COLUMN tmp_litho varchar; UPDATE field_observations SET tmp_litho = code_litho;
Maintenant, on crée la table de lexique, en requêtant et en redirigeant la sortie de la requête vers la création d'une table: CREATE TABLE lex_code_litho AS ( --SELECT code_litho, count(*) FROM --( SELECT upper(tmp_litho)::char(4) AS code_litho, tmp_litho AS description --,count(*) FROM field_observations GROUP BY tmp_litho ORDER BY tmp_litho --) --AS tmp --GROUP BY code_litho --HAVING count(*) > 1 );
On édite un peu ce lexique, et voilà: bdgeol=# SELECT * FROM lex_code_litho; code_litho | description ------------+--------------------------------------DOLE | dolerite ECLO | Eclogite GRAN | Granites GRES | Gres schiste Stephanien MICA | Micaschistes MYLO | Mylonite MYLO | Mylonite alpine ORTH | Orthogneiss ORTH | Orthogneiss diatexitique ORTH | Orthogneiss metatexitique ORTH | Orthogneiss mylonitique PARA | Paragneiss PARA | Paragneiss diatexitique PARA | Paragneiss diatexitique a amphibolite PARA | Paragneiss metatexitique PARA | paragneiss mylonitique PERM | Permien | (18 lignes)
Encore des éditions à faire: UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE UPDATE
'MYLA' WHERE description 'OG' WHERE description = 'OGDI' WHERE description 'OGME' WHERE description 'OGMY' WHERE description 'PG' WHERE description = 'PGDI' WHERE description
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique UPDATE lex_code_litho SET code_litho = 'PGDA' WHERE description = 'Paragneiss diatexitique a amphibolite'; UPDATE lex_code_litho SET code_litho = 'PGME' WHERE description = 'Paragneiss metatexitique'; UPDATE lex_code_litho SET code_litho = 'PGMY' WHERE description = 'paragneiss mylonitique';
Et ensuite, on rebascule le code_litho dans la table originale, en faisant une petite pirouette: UPDATE field_observations SET code_litho = lex_code_litho.code_litho FROM lex_code_litho WHERE field_observations.tmp_litho = lex_code_litho.description; ALTER TABLE field_observations DROP COLUMN tmp_litho;
Il y a eu quelques modifications nécessaires: UPDATE oc_samples SET sample_id = 'MA_008b' WHERE sample_id = 'MA008b'; ALTER TABLE sections ADD COLUMN comments varchar;
Voilà: reprenons le fil où on était resté: on va simplement reprendre les données de ce lexique, en important un fichier .csv, qui avait été généré à partir de la base de l'an dernier. tail -n +2 lex_code_litho.csv | psql -d bdgeol -c "\COPY lex_code_litho FROM stdin WITH CSV"
c)Échantillons: Les échantillons prélevés sur les affleurements: quand on prend un échantillons, on décrit l'affleurement. Donc ici, ce sera forcément au niveau d'un point d'observation 16: CREATE TABLE oc_samples ( sample_id varchar NOT NULL, obs_id varchar, sample_type character(4), sample_litho character(8), oriented boolean, section_thin integer, section_thick integer, analyses boolean, description varchar );
Nutrition: tail -n +2 oc_samples.csv | psql -d bdgeol -c "\COPY oc_samples FROM stdin WITH CSV"
ALTER TABLE oc_samples ADD CONSTRAINT oc_samples_id PRIMARY KEY (sample_id);
d)Analyses: Les échantillons, il s'agit parfois de les analyser: on fait donc une table d'analyses. Nous tergiverâmes longtemps, en 2010, quant à la structure la mieux adaptée; le modèle Entité-Attribut-Valeur est l'idéal, mais bon, ça requiert pas mal d'huile de coude, surtout lors de la maintenance de la bd. On a donc plutôt opté pour le modèle columnaire avec plein de colonnes. En revanche, c'est se réserver une bonne embûche pour le futur. Bah, on verra. CREATE TABLE analyses ( sample_id varchar, sio2_pc numeric, al2o3_pc numeric, fe2o3_pc numeric, mno_pc numeric, mgo_pc numeric, cao_pc numeric, na2o_pc numeric, k2o_pc numeric, tio2_pc numeric, p2o5_pc numeric, loi_pc numeric, h2o_pc numeric, y_ppm numeric, sr_ppm numeric, rb_ppm numeric, zr_ppm numeric, nb_ppm numeric, ga_ppm numeric, 16 Par la suite, nous devrons mettre en place une intégrité référentielle, de manière à ce que ce soit le moteur de la base de données qui soit le garant de cette restriction.
39 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique pb_ppm numeric, cu_ppm numeric, ni_ppm numeric, co_ppm numeric, cr_ppm numeric, v_ppm numeric, ba_ppm numeric, sc_ppm numeric, numauto serial PRIMARY KEY );
Nutrition: tail -n +2 analyses.csv | psql -d bdgeol -c "\COPY analyses FROM stdin WITH CSV"
e)Sections: Les sections, minces ou polies, faites sur échantillons: CREATE TABLE sections ( section_id varchar, sample_id varchar, types varchar, description varchar, comments varchar );
Nutrition: tail -n +2 sections.csv | psql -d bdgeol -c "\COPY sections FROM stdin WITH CSV"
Voilà les tables qu'on a: \dt Liste des relations Schéma | Nom | Type | Propriétaire --------+--------------------------+-------+-------------public | analyses | table | etudiants public | dh_collars | table | etudiants public | field_observations | table | etudiants public | field_photos | table | etudiants public | geometry_columns | table | postgres public | lex_code_litho | table | etudiants public | observ | table | etudiants public | oc_samples | table | etudiants public | sections | table | etudiants public | spatial_ref_sys | table | postgres public | structures_linear | table | etudiants public | structures_planar | table | etudiants
f)Vérifications de la cohérence des données, par des requêtes: Puis on a finalement fait quelques requêtes, d'abord des requêtes de vérifications, pour voir si tout était cohérent dans le jeu de données que nous introduisîmes. Eh oui, comme on a hérité de données, on ne peut pas mettre tout de suite d'intégrité référentielle: au préalable, il faut que les données soient propres, ou à tout le moins, cohérentes. On a vite vu que ce n'était pas le cas: SELECT --*, field_observations.obs_id AS obs_id, oc_samples.obs_id AS obs_id_samples FROM field_observations FULL OUTER JOIN oc_samples ON field_observations.obs_id = oc_samples.obs_id WHERE field_observations.obs_id IS NULL OR oc_samples.obs_id IS NULL ORDER BY field_observations.obs_id, oc_samples.obs_id;
C'est correct, on n'a pas d'échantillons orphelins. Les orphelins sont des enregistrements qui sont incohérents, qui n'ont pas de parent dans la table qui est parente. En l'occurrence, c'est la table des observations qui est parent de la table des échantillons.
40 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
En suivant la même logique, on traque les orphelins dans la jointure entre échantillons et analyses: SELECT oc_samples.sample_id AS oc_samples_sample_id, analyses.sample_id AS analyses_sample_id FROM oc_samples FULL OUTER JOIN analyses ON oc_samples.sample_id = analyses.sample_id WHERE oc_samples.sample_id IS NULL OR analyses.sample_id IS NULL ORDER BY coalesce(oc_samples.sample_id, analyses.sample_id);
La même requête mais qui liste uniquement les orphelins: SELECT oc_samples.sample_id AS oc_samples_sample_id, analyses.sample_id AS analyses_sample_id FROM oc_samples RIGHT OUTER JOIN analyses ON oc_samples.sample_id = analyses.sample_id WHERE oc_samples.sample_id IS NULL OR analyses.sample_id IS NULL ORDER BY coalesce(oc_samples.sample_id, analyses.sample_id);
Et voici le résulat, on a 7 échantillons problèmatiques: oc_samples_sample_id | analyses_sample_id ----------------------+-------------------| MA_027c | MA_033 | MA_041b | MA_155 | MA_171 | MA_224a | MA_247 (7 lignes)
L'an dernier, on vait traqué des orphelins, on avait dû faire quelques corrections: BEGIN TRANSACTION; UPDATE analyses SET sample_id = 'MA_035a' WHERE sample_id = 'MA_035'; INSERT INTO oc_samples (sample_id, obs_id) VALUES ('MA_089', '89'); COMMIT;
Cette année, il est bien sûr inutile de les refaire. Une autre requête de vérification qui renvoie des orphelins: SELECT oc_samples.sample_id AS oc_samples_sample_id, sections.sample_id AS sections_sample_id FROM oc_samples FULL OUTER JOIN sections ON oc_samples.sample_id = sections.sample_id WHERE oc_samples.sample_id IS NULL OR sections.sample_id IS NULL ORDER BY coalesce(oc_samples.sample_id, sections.sample_id);
Là encore, on a des orphelins, de toutes parts, à gauche comme à droite. Il reste à corriger les données incohérentes.
g)Encore des requêtes: On a requêté sur notre jeu de données. Bien que très modeste, on a pu commencer à le questionner, à voir ce qu'il "avait dans le ventre": Pour voir quel géologue a pas/mal fait son taff: SELECT geologist, count(*) /*, tmp_litho, **/ GROUP BY geologist ORDER BY geologist ;
FROM field_observations WHERE code_litho IS NULL
Les choses sont aujourd'hui corrigées, la requête ne renvoie aucune ligne. Mais à ce moment, on avait vu un aspect réellement dangereux des bases de données: en faisant ainsi, on vient de quantifier le travail d'un être humain. Mis dans des mains d'un supérieur hiérarchique, ceci peut être manié de manière tordue, à des fins d'évaluation, par exemple. On imagine aisément qu'en croisant ces données avec les tables du service de paye, on va faire quelques graphiques, et on va pouvoir se dire "tiens, Untel, qui n'a fait QUE n observations lors de cette campagne, est mieux payé qu'Autretel, qui a pourtant fait nnn observations..." Danger! On n'a aucune mesure de la Qualité de l'observation. C'est pas parce qu'on multiplie les points d'observations en n'observant pas bien qu'on est meilleur géologue, ou plus productif, ou qu'on doit être mieux rétribué. Moralité: se défier des utilisations potentiellement perverses des bases de données. Ça peut être tentant. Il faut plutôt voir ça sous une forme ludique, qui peut favoriser une saine émulation positive: "ouah, t'as décrit n affleurements, ben mon cochon! ouais, mais ils sont tous près des routes: attends un peu la prochaine campagne, tu vas voir ce que je vais te mettre: je vais te faire un de ces voronois de mes observations crapahutesques que tu vas pas voir le jour". Ce n'est qu'un exemple, qui est sous-tendu par l'utilisation de requêtes spatiales (le calcul de la distance du point d'observation à la route). Juste pour voir nos données: on met ça dans des fenêtres visibles, pour y réfléchir tranquillement pendant qu'on requête ailleurs: SELECT * FROM field_observations ORDER BY obs_id; SELECT * FROM field_photos; SELECT * FROM oc_samples;
41 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Traquons la donnée anomale. Eh oui, avant que de pouvoir utiliser des données, il faut que ces dernières soient propres. Et là, l'utilisation des outils de type base de données sont absolument impitoyables. En matière de Contrôle Qualité, c'est la panacée. Là, on cherche des duplicatas qui sont intolérables: SELECT SELECT SELECT SELECT
obs_id, count(*) FROM field_observations GROUP BY obs_id HAVING count(*) > 1; * FROM oc_samples; obs_id, count(*) FROM oc_samples GROUP BY obs_id HAVING count(*) > 1; sample_id, count(*) FROM oc_samples GROUP BY sample_id HAVING count(*) > 1;
Quand une requête nous plaît tant qu'on la rejoue tout le temps, on peut la "sauver" dans la base même, sous forme d'une vue, laquelle pourra se faire requêter à son tour. Notons que ce mécanisme (lourd) n'est pas nécessaire pour faire des requêtes imbriquées. CREATE VIEW coQuilles_analyses_a_voir AS SELECT oc_samples.sample_id AS oc_samples_sample_id, analyses.sample_id AS analyses_sample_id FROM oc_samples FULL OUTER JOIN analyses ON oc_samples.sample_id = analyses.sample_id WHERE oc_samples.sample_id IS NULL OR analyses.sample_id IS NULL ORDER BY coalesce(oc_samples.sample_id, analyses.sample_id);
La requête suivante est "démontée", de manière à lire, posément, comment se construit une expression. La fonction GeomFromewkt(), qui est fournie par postgis (l'extension spatiale de postgresql), renvoie une géométrie (c'est-àdire quelque chose qui sera "cartable" par un client SIG, un objet cartographique, doté de géométrie et système de coordonnées de référence). On peut construire ça à partir des données, de manière à ce que la carte soit dynamique à partir des données. Ceci s'oppose complètement à d'autres approches SIG. Dans les shapefiles, par exemple, la géométrie est disjointe physiquement de la table attributaire, il y a une sorte de jointure qui "colle" les attributs à un objet cartographique. Alors que dans une table postgis conventionnelle, l'objet cartographique n'est "que" l'attribut dans une relation. Pour l'utilisateur final, ça revient pratiquement au même, mais l'approche "philosophique" est intéressante. SELECT GeomFromewkt( 'SRID=' || srid || ';POINT (' || x || ' ' || y || ' ' || z || ')' ) FROM field_observations;
C'est cette requête, mise sous forme de vue, qui permet de faire des cartes dynamiques à partir des données. ALTER TABLE field_observations ADD CONSTRAINT numauto_unique UNIQUE (numauto);
Quelques manips, avortées, à des fins démonstratives. BEGIN TRANSACTION; ALTER TABLE field_observations ADD COLUMN toto numeric; UPDATE field_observations SET toto = y; UPDATE field_observations SET y = x; UPDATE field_observations SET x = toto; ALTER TABLE field_observations DROP COLUMN toto; ABORT; --COMMIT;
ALTER TABLE analyses ADD CONSTRAINT analyses_numauto_unique UNIQUE (numauto); DROP VIEW field_observations_analyses_points ; CREATE VIEW observations_analyses_points AS SELECT field_observations.obs_id, analyses.*, GeomFromewkt( 'SRID=' || field_observations.srid || '; POINT ('|| field_observations.x || ' ' || field_observations.y || ' ' || field_observations.z || ')' ) FROM field_observations JOIN oc_samples ON field_observations.obs_id = oc_samples.obs_id JOIN analyses ON oc_samples.sample_id = analyses.sample_id ;
On a un peu joué, avec les uns et les autres qui modifiaient les données, depuis des clients différents, et ça se répercutait sur le client SIG où les données bougeaient dynamiquement, sans avoir besoin de "rejouer" des projets ou des scripts.
42 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Voilà où on en était resté. On pouvait cartographier nos données, les requêter, les confronter, les exporter. Bref, la plupart des choses qu'on attend de faire avec des données. Car c'est là, la définition de l'informatique: les techniques de traitements de l'information. Les bases de données sont reines dans ce domaine, elles sont d'une puissance gigantesque, mais elles imposent une rigueur sans faille, comme on a pu le voir concrètement à plusieurs reprises lors de ces séances.
Ce n'était là qu'une ébauche de base de données géologique, dans un but pédagogique, mais en même temps se voulant utile. Elle n'est pas parfaite, ni complète. Lors de la prochaine séance (ou avant, pourquoi pas), on s'attachera à la rendre plus complète, et aussi plus générique, à savoir adaptable à plus de cas de figures, tout en gardant la même structure. Ce concept de généricité est souvent essentiel; y manquer fait basculer dans des travers qui peuvent avoir d'importantes conséquences.
h)Requêtes: un premier aperçu Faisons une requête, en regardant notre table, simplement en l'ordonnant: SELECT obs_id FROM field_observations ORDER BY obs_id;
On garde à l'esprit que les données d'une table sont forcément non-ordonnées, donc quand on requête, il convient le plus souvent de demander un ordre pour afficher les données. Une autre requête: SELECT obs_id, obs_id::numeric AS obs_id_nombre FROM field_observations ORDER BY obs_id::numeric;
Cette fois, on n'affiche que certains champs, et on convertit le champ obs_id (IDentifiant d'OBServation), qui est un caractère variant (varchar) en numérique, de manière à pouvoir le trier. En effet, de prime abord, le tri se fait de manière « asciibétique », ce qui paraît inapproprié à un lecteur humain. La conversion entre plusieurs types de données se fait à l'aide de « :: ». Une requête un peu plus sélective: Z est-il toujours à zéro? SELECT * FROM field_observations WHERE z 0;
Oui, on le dirait bien. Une requête un peu mieux, que l'on fabrique en plusieurs temps: D'abord, un simple tri: SELECT obs_id, x, y, code_litho, description FROM field_observations ORDER BY code_litho;
Puis une restriction sur la description, on s'intéresse aux métatexites orthodérivées: pour ce, on a recours à des métacaractères; en SQL, % est l'équivalent du joker * du MS-DOS, et _ équivaut au ? du MS-DOS. SELECT obs_id, x, y, code_litho, description FROM field_observations WHERE description ILIKE '%metatex%' AND description ILIKE '%ortho%' ORDER BY code_litho;
i)Appel à des fonctions dans une requête: appel à des fonctions de l'extension spatiale postgis; création de vues: Dans la foulée, on crée une vue cartographiable: on voit la notion de requête, de fonction, de vue. De même, on construit l'instruction en plusieurs étapes: 1/ SELECT 'SRID=' || srid || ';POINT ('|| x || ' ' || y || ' ' || z || ')' FROM field_observations;
43 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
« || » sert à concaténer des valeurs les unes aux autres. Là, on met ensemble les 3 coordonnées x, y et z, séparées par des blancs. On construit une chaîne qui sera du type 'SRID=3465 ;POINT (43.56 23.899 786.1)', pour chaque enregistrement de la table. 2/ SELECT GeomFromewkt( 'SRID=' || srid || ';POINT ('|| x || ' ' || y || ' ' || z || ')') FROM field_observations;
L'expression que l'on vient de construire se voit passée en argument entre parenthèses à la fonction GeomFromewkt. Cette dernière provient de PostGIS, l'extension spatiale de postgresql. Comme son nom l'indique un peu, elle fournit en sortie une GÉOMétrie à partir d'une expression en EWKT (Enhanced Well-Known Text). Le WKT est une manière de décrire des géométries, à partir de mot-clés assez simples. La géométrie ainsi générée sera directement interprétée par les logiciels de SIG, qui pourront cartographier ceci. En l'occurrence, on génère des points. Le SRID est un entier qui désigne le Coordinates Reference System (CRS), soit le système de coordonnées, projeté ou pas, tel que définit dans la table spatial_ref_sys, elle-même fournie par l'extension postgis. 3/ CREATE VIEW field_observations_points AS SELECT *, GeomFromewkt('SRID=' || srid || ';POINT ('|| x || ' ' || y || ' ' || z || ')' ) FROM field_observations ;
À partir de la requête générée, on crée une vue, c'est-à-dire une requête nommée, d'une certaine manière, qu'on pourra requêter à son tour. Qgis n'en veut pas, il faut une clé unique avec un champ entier, qui manque encore. Faisons-là: ALTER TABLE field_observations ADD CONSTRAINT numauto_unique UNIQUE (numauto);
Si une requête nous plaît, nous pouvons la sauver, sous la forme d'une vue, qui peut ensuite être requêtée, de la même manière qu'une table. Reprenons l'exemple de nos métatexites orthodérivées: Voilà la requête: SELECT obs_id, x, y, code_litho, description FROM field_observations WHERE description ILIKE '%metatex%' AND description ILIKE '%ortho%' ORDER BY code_litho;
Et l'instruction pour fabriquer la vue field_observations_points_metatex_ortho: CREATE VIEW field_observations_points_metatex_ortho AS SELECT *, GeomFromewkt( 'SRID=' || srid || ';POINT ('|| x || ' ' || y || ' ' || z || ')' ) FROM field_observations WHERE description ILIKE '%metatex%' AND description ILIKE '%ortho%';
Faisons d'autres requêtes: calculons, pour rire un peu, la moyenne des x et y de notre table d'observations: SELECT avg(x) AS moyenne_x, avg(y) AS moyenne_y FROM field_observations;
Cela est assez dénué de sens, notons. SELECT code_litho, avg(x) AS moyenne_x, avg(y), stddev(x) AS ecarttype_x FROM field_observations GROUP BY code_litho;
Là, on a regroupé les enregistrements en se basant sur le champ code_litho (instruction GROUP BY), et les opérations telles que moyenne, écart-type, compte, sont faites sur les sous-ensembles définis par les valeurs de code_litho. SELECT count(*), code_litho, avg(x) AS moyenne_x, avg(y) AS moyenne_y, stddev(x) as stddevx, stddev(y) as stddevy FROM field_observations GROUP BY code_litho;
44 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
j)Suite: création des autres tables: les photos de terrain: Quand on prend des photos sur le terrain, il faut bien les référencer quelque part. Pour cette table, on s'inspire de tables similaires générées par eVis, une extension de qgis: cela permet de visualiser directement les photos, une fois dans qgis. CREATE TABLE field_photos ( pho_id varchar NOT NULL, obs_id varchar, filepath varchar, filename varchar, x numeric, y numeric, z numeric, srid numeric, north varchar, direction numeric, az numeric, dip numeric, description varchar, author varchar, numauto serial PRIMARY KEY );
Notons au passage qu'une photo ne va pas forcément avec un point d'observation. Pour mettre les données de l'an dernier, ce sera un peu plus délicat, car nous avons changé la structure. Il faut donc explicitement spécifier les champs qui vont aller « en face » du contenu du .csv. tail -n +2 pictures.csv | psql -d bdgeol -c "\COPY field_photos (pho_id, obs_id, filename, description, az, dip, author) FROM stdin WITH CSV"
Et on vérifie dans la base que tout est passé: SELECT * FROM field_photos;
Ô déception; eh oui, tout ça pour rien, le .csv était malheureusement vide.
k)Mesures structurales: Les structures: d'abord, les plans: CREATE TABLE structures_planar ( obs_id varchar, pl_id varchar, convention varchar, direction numeric, dip numeric, dipdir character(2), struct_type varchar, comments varchar, movt_apparent varchar, numauto serial PRIMARY KEY );
Nutrition: tail -n +2 planar_structures.csv | psql -d bdgeol -c "\COPY structures_planar (obs_id,convention,direction,dip,dipdir,struct_type,comments,numauto,movt_apparent,pl_id) FROM stdin WITH CSV"
Puis les lignes, qui se rapportent éventuellement à un plan: CREATE TABLE structures_linear ( obs_id varchar, pl_id varchar, l_id integer NOT NULL, convention varchar, trend numeric, plunge numeric, pitch numeric, pitch_dir character(2), line_type varchar, movt varchar, comment varchar, numauto serial PRIMARY KEY
45 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique );
Nutrition: tail -n +2 linear_structures.csv | psql -d bdgeol -c "\COPY structures_linear (pl_id,obs_id,convention,trend,plunge,pitch,pitch_dir,line_type,movt,comment,l_id) FROM stdin WITH CSV"
l)Jointures entre tables: Comment jointurer les données de deux relations: voici la syntaxe SQL: SELECT FROM
JOIN ON (
. = .);
Faisons une parenthèse, la notion de jointure entre tables mérite d'être clarifiée, au moyen d'un exemple simpliste: on crée deux tables ultrasimples avec un seul champ chacun, qu'on remplit de quelques valeurs. Puis on fait des jointures, c'est-à-dire des intersections: CREATE TABLE table1 (num integer); CREATE TABLE table2 (num integer); INSERT INTO table1 VALUES (1), (2), (3), (5), (6), (34); INSERT INTO table2 VALUES (2), (3), (5), (6), (7), (8); bdgeol=> SELECT * FROM table1; num ----1 2 3 5 6 34 (6 lignes) bdgeol=> SELECT * FROM table2; num ----2 3 5 6 7 8 (6 lignes) SELECT * FROM table1 JOIN table2 ON (table1.num = table2.num); num | num -----+----2 | 2 3 | 3 5 | 5 6 | 6 (4 lignes) SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.num = table2.num); num | num -----+----1 | 2 | 2 3 | 3 5 | 5 6 | 6 34 | (6 lignes) SELECT * FROM table1 RIGHT OUTER JOIN table2 ON (table1.num = table2.num); num | num -----+----2 | 2 3 | 3 5 | 5 6 | 6 | 7 | 8 (6 lignes) SELECT * FROM table1 FULL OUTER JOIN table2 ON (table1.num = table2.num); num | num -----+----1 | 2 | 2 3 | 3 5 | 5 6 | 6
46 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique | 7 | 8 34 | (8 lignes) DROP TABLE table1; DROP TABLE table2;
On vient de voir les jointures naturelles, les jointures gauches et droites.
m)Exercice: faire une vue cartographiable dans qgis qui permette de carter les mesures structurales de plans. Solution: On a besoin de 2 tables: •
Les informations géographiques sont dans la table field_observations. On a besoin de jointurer: SELECT field_observations.obs_id, x, y, z, srid, direction, dip FROM ( field_observations JOIN structures_planar ON ( field_observations.obs_id = structures_planar.obs_id ) );
Reste à rendre ça cartographiable. Pour ce faire, nous fîmes appel, il y a une paire de semaines, à une fonction de postgis générant une géométrie à partir de coordonnées et d'un système de coordonnées: SELECT GeomFromewkt( 'SRID=' || srid || ';POINT (' || x || ' ' || y || ' ' || z || ')' ) FROM field_observations;
Faisons de même, cette fois avec nos structures rapportées aux points d'observations: DROP VIEW structures_planar_points; CREATE VIEW structures_planar_points AS SELECT field_observations.obs_id, x, y, z, srid, direction, dip, structures_planar.numauto, GeomFromewkt( 'SRID=' || srid || ';POINT (' || x || ' ' || y || ' ' || z || ')' ) FROM ( field_observations JOIN structures_planar ON ( field_observations.obs_id = structures_planar.obs_id ) );
47 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
6.5.Intégrité référentielle On va seulement commencer cet aspect cette année. On a vu que la jointure entre les tables field_observations et oc_samples était correcte, que les données étaient cohérentes avec un lien « parental » entre ces deux tables: tout échantillon correspond bien à une observation. Pour garantir que désormais les données satisfaissent toujours cette contrainte, demandons au moteur de la base de veiller à ce que cela soit toujours le cas: ALTER TABLE oc_samples ADD FOREIGN KEY (obs_id) REFERENCES field_observations;
Il conviendra ensuite d'implémenter de tels contrôles à l'ensemble de la base, de manière à garantir l'intégrité de son contenu.
6.6.Constitution d'une base de données "personnelle", basée sur fichier On était dans le domaine de la base de données client/serveur, on travaillait sur une des bases de données les plus costaud et avancées du monde du Logiciel Libre. Passons à son antithèse: une base de données orientée fichier. Mais redoutablement efficace. Bien plus simple à mettre en œuvre. sqlite est massivement utilisé par plein de programmes, entre autres sur les téléphones android, les iPhones; firefox utilise également des bases sqlite pour la persistance de ses données. Et encore un exemple d'une base de données omniprésente dans notre quotidien, à la M.Jourdain... Télécharger le logiciel sqlite, un monstre de petitesse et d'efficacité. http://www.sqlite.org/download.html Interfaces utilisateur: •
Extension spatiale: spatialite. http://www.gaia-gis.it/gaia-sins/ Doc en français: http://www.portailsig.org/content/sqlite-spatialite-le-pourquoi-du-comment Faites le tutoriel.
7.Pratique: consultation de bases de données Voilà le rôle le plus facile: celui de client, au bout de la chaîne.
7.1.Consultons la BSS Essayons de télécharger des données de la BSS et de les exploiter: ●
allons sur Infoterre: http://infoterre.brgm.fr/viewer/MainTileForward.do
●
à droite, il faut afficher les ouvrages de la BSS
●
on zoome sur une région de son choix, relativement serré, pour avoir un nombre "raisonnable" de points de la BSS;
●
on choisit, à gauche, le téléchargement: ○
essayons au format texte: ça crée un fichier .zip: InfoTerre-07.02.12-09:40:18.zip qui contient 3 fichiers: ●
CoupeValide.xls 48 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
●
CoupeInitiale.xls
●
BanqueDuSousSol.xls
ce ne sont en fait pas des fichiers excel, mais des fichiers ASCII délimités par des tabulations; ouvrons-les dans un tableur quelconque: il faut choisir la tabulation comme caractère de séparation, et un encodage ISO-8859-1 (latin1) ●
BanqueDuSousSol.xls contient les 'têtes' des ouvrages;
●
CoupeInitiale.xls contient les coupes géologiques initiales des ouvrages sélectionnés;
●
CoupeValide.xls contient les coupes géologiques .
Ceci n'est qu'un extrait de la BSS: en fait, la base de données contient nettement plus d'informations, qui sont structurées de manière différente: ce que l'on voit là est le résultat de requêtes. ○
essayons au format shapefile: ça crée un fichier .zip: InfoTerreData_12_02_07.zip qui contient 3 fichiers: ●
BSS_TOTAL_AVEC_LABEL.shx
●
BSS_TOTAL_AVEC_LABEL.shp
●
BSS_TOTAL_AVEC_LABEL.dbf
C'est une couche de type shapefile, qu'on ouvre avec le SIG bureautique de son choix.
Fig. 38: exemple de points de la BSS, téléchargés via un shapefile
7.2.Ajoutons des couches à notre SIG Le BRGM, en plus de la BSS, donne accès à un ensemble de données cartographiques. Pour y accéder, il faut faire une connexion, en indiquant une URL: http://geoservices.brgm.fr/geologie
49 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 39: configuration de la connexion au serveur WMS du BRGM: exemple dans qGIS Puis on explore ces ressources... Extensions de qGIS: il y en a pléthore, ça évolue (trop?) vite. OpenLayers est très utile. Sextante permet d'avoir accès à la boîte à outils de SAGA. Essayez-en plein. Participez, si une âme de développeur germe en vous.
8.Au-delà de la cartographie, au-delà du SIG: visualisation 3D; modélisation Plein d'outils, coûteux, propriétaires. Quelques libres. Blender, un incontournable pour la modélisation; pas adapté à la géologie, mais de peu s'en faudrait. ParaView (Parallel Visualization Application, http://www.paraview.org/) est un logiciel multi-plateformes permettant la visualisation interactive de gros volumes de données scientifiques 2D et 3D et leur traitement à l'aide de nombreux filtres (Delaunay etc...).
50 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 40: exemples de visualiations de données dans paraview Et il y a une adaptation géologique: ParaViewGeo (http://paraviewgeo.mirarco.org/index.php/Main_Page)
51 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
Fig. 41: exemple de visualisation 3D, mine de Brunswick: galeries, chantiers, lentilles minéralisées
52 / 53
PChGeol - rapport 2012-03 – université St-Etienne - France – cours géologie numérique
9.Exercice Il faut noter les élèves. Voici donc un petit sujet, pour terminer la session, le dernier jour. Autant mêler l'utile au désagréable, une excursion prochaine étant consacrée à Bédarieux, on va essayer de fabriquer quelques documents de base, qui pourront être utiles sur le terrain. Fournissons donc un petit jeu de données, qui comprennent des données nationales, et locales. Voici: pierre@autan:~/fac_st_etienne/donnees_pour_etudiants$ ls -R .: carte_geol_bedarieux carte_geol_france_1000000_raster carte_geol_france_1000000_vecteur img_sat_landsat kmz_brgm languedoc-roussillon_shp mnt34 nolinks ./carte_geol_bedarieux: 0988cl2e.aux 0988CL2E.TAB
À partir de toutes ces données, voire plus, en allant chercher sur le web (WMS, WFS, import de données, etc.), de concocter des documents utiles sur le terrain. Une minute, une synthèse bibliographique cartographique, etc. Notons que sur le terrain, les documents à l'échelle sont plus qu'utiles. Éviter les échelles du genre 1/34867, par exemple. Pour ce, dans qgis, il faut avoir recours au Composeur.
10.Ressources, bibliographie Livres: Gardarin, voir son site: http://georges.gardarin.free.fr/ Bases de données: ● ● ● ● ● ●
postgresql oracle sqlite firebird knoda oobase (liste très loin d'être limitative)
Google est votre ami, wikipedia aussi. La documentation officielle de postgresql est très bien faite.
s'agit d'un gisement de métal précieux. Quelques notions de base d'exploration minière et d'exploitation minière. Puis vues en carte, sondages, avec les ...
La combinaison la plus courante est LAMP, pour un serveur web combinant Linux (système d'exploitation), Apache (serveur http), Mysql (serveur de base de ...
En passant par une interface entre l'homme et la machine: on ... une interface en mode texte (ligne de commande), qui se destine tant aux humains qu'aux ...
Comme le disait si justement un Grand Sage: "ta main, c'est ton. Esprit". Le recours à ..... GRANT ALL ON DATABASE l3_2013 TO etudiants;. Il faut ensuite nous ...
GNU/Linux: use of a variety of distributions (Debian, Red Hat, Linux From Scratch, Mandrake, ... MS-DOS and Windows systems: daily maintenance of computing ... 1989 â 1992: IGAL (Geological Institute Albert de Lapparent, Paris, France) ..... 1989:
Organisation Internationale de Normalisation (ISO). 15. Gestion de la ... 5S - Les Normes dans un Bureau. 50. 5S - Les Exemples dans l'Usine & Bureau. 51.
2) He erected, in the first stages, six stores at the city's entrance. ...... interpretation thereof, and casting doubts about the transmitted narrarations in our ...... Concerning your latest letter, its torrent has been overflowing, overbrimming, .
must also spot all print traffic from multiple sources using different protocols. .... classification features available to customers between software releases with an ... addition, Packeteer also identifies Oracle's Java-based, front-end, client tra
understand their impact on end users and service providers. ... high-speed Internet access and wireless access ... what could they do to protect end users from.
holisticâ smart detection engine protect my network from any ... An IPS block anything that has nothing to do on the network ...... Depends on internal architecture.
entries Boolean and it represents the information about the number of nodes .... Figure 3 shows a ring-lattice regular network, in which all the nodes are ...
(TCP/UDP port number), the Barricade redirects the external service request to the appropriate .... risk of hacker attack, and defending against a wide array of common attacks. ... SPI and Anti-DoS firewall protection. > ... TCP SYN wait: sec. 5.
