Presentation GeolLLibre PostGeol

POSTGEOL - GEOLLLIBRE

PostGeol: PostgreSQL for Geologists

within the GeolLLibre project

● Speaker: Pierre Chevalier

– Multi-purpose geologist, naturalist, mapper, structural, mostly specialised in mining exploration

– Computer scientist, developer, database administrator (DALIBO freelance partner), Libre/Free Software enthusiast, GNU/Linuxer

mailto:[email protected]

twitter: @PierreChTuxLicence Creative Commons CC-BY-NC-SA 4.0

You are free to:● Share — copy and redistribute the material in any medium

or format● Adapt — remix, transform, and build upon the materialThe licensor cannot revoke these freedoms as long as you follow the license terms.

mailto:[email protected]

https://twitter.com/PierreChTux

POSTGEOL - GEOLLLIBRE

Licence Creative Commons CC-BY-NC-SA 4.0

You are free to:

● Share — copy and redistribute the material in any medium or format● Adapt — remix, transform, and build upon the materialThe licensor cannot revoke these freedoms as long as you follow the license terms.

Under the following terms:

● Attribution — You must give appropriate credit, provide a link to the license, and indicate if changes were made. You may do so in any reasonable manner, but not in any way that suggests the licensor endorses you or your use.

● NonCommercial — You may not use the material for commercial purposes.● ShareAlike — If you remix, transform, or build upon the material, you must distribute your contributions

under the same license as the original.

No additional restrictions — You may not apply legal terms or technological measures that legally restrict others from doing anything the license permits.

Refer to: https://creativecommons.org/licenses/by-nc-sa/4.0/

https://creativecommons.org/licenses/by-nc-sa/4.0/

Map of our planet Earth, as seen through a geologist's eyes...

Maybe you don't imagine our Earth like this? Then you'd realize that a geologist's "inner wiring" isn't exactly like everybody's.

Mind you though, a computer scientist also; he would see the Earth as a large bunch of 0's and 1's. I slightly exagerate.

Map of our planet Earth, as seen through a geologist's eyes...

GeolLLibre project

● Acronym for GÉOLogie Logiciels LIBREs:GEOLogy Free/Libre Software

(obviously, some French influence)


● The project's target is to build a set of software tools, Free/Libre/Open-Source, focussing on Earth Sciences, particularly on geologists' work

GeolLLibre project

GeolLLibre project



● The project was initiated in 2008, year of the Planet Earth

http://yearofplanetearth.org/

GeolLLibre project



● The project was initiated in 2008, year of the Planet Earth

● It all started from a rather sad statement: few to no Free/Libre software for Earth Sciences...

(very recently, an architect was finding

the same -sad- situation!)

http://yearofplanetearth.org/

One of the main reasons for GeolLLibre

● A geologist's perception of time is measured in millions of years: this certainly allows him to take a step back and have a different perception of time and life...

● So many animals, some of them particularly exceptional, became extinct a long time ago; only their fossils (sometimes) remain.

● So many software packages, some of them particularly exceptional, just became extinct... Where have the dBase III+, Norton Utilities, HyperCard? and so many more? fossilised?...

● One radical remedy to avoid necrosis, extinction and (in best cases) fossilisation of software tools:

=> Free/Libre Licences !

GeolLLibre project => so let's go!

The initial foundational text:http://pierremariechevalier.free.fr/pierre_chevalier_geologue/geolllibre_annonce_fr.html

Same text, translated into English: http://pierremariechevalier.free.fr/pierre_chevalier_geologue/geolllibre_annonce_en.html

Abstract:

– facts:

● very few to no Free Software packages for us, geologists *

● waste of resources: wheels reinventing, moulinettes (data to data transformation utilities, abandonwares, everyone is making DIY (do-it-yourself) tools in a lonely way (very often, geologists have to manage to work on their own, in the field);

● need for data standards *: so much time is wasted to convert data, the misuse of general-purpose office programs for tasks that they don't do right, managing badly formed data; anarchy in the management of the priceless DATA;

– solutions:

● define standards, in the way RFC (Requests For Comments) are done, for instance, and

● build software bricks on top of them, step by step;

● teach to Earth Sciences students, especially database (significant deficiencies exist among young generations);

● possibility to free a software which used to be proprietary by the author;

– action: launch of the development of a tool to visualise underground data.

(*) Things have evolved, since 2008: particularly, standards have been defined, fortunately, and some Free software tools have appeared.

http://pierremariechevalier.free.fr/pierre_chevalier_geologue/geolllibre_annonce_fr.html

http://pierremariechevalier.free.fr/pierre_chevalier_geologue/geolllibre_annonce_en.html

Start of a project: a community

● A small dynamic community, mostly French-speaking, aggregated quickly, through various ways: colleagues, friends, direct and indirect links, word-of-mouth... Most people are geologists, enthusiasts.

Start of a project: a community, a communication tool


● In order to link all these people: a discussion list (not archived, unfortunately), provided by free.fr

to subscribe:mailto:[email protected]?subject=subscribe

(see next page...)

http://free.fr/

mailto:[email protected]?subject=subscribe


● In order to link all these people: a discussion list (not archived, unfortunately), provided by free.fr:

to subscribe:mailto:[email protected]?subject=subscribe

(see next page...)

Start of a project: a community, acommunication tool => correction: a better one!

Post-scriptum: we switched, as of May 2016, from free.fr to freelists.org.To subscribe:mailto:[email protected]?subject=subscribe

Historical archives from the former list have been imported there:http://www.freelists.org/archive/geolllibre/

http://free.fr/


http://free.fr/

https://www.freelists.org/


http://www.freelists.org/archive/geolllibre/



● In order to link all these people: a discussion list, provided by http://freelists.org

– to subscribe:mailto:[email protected]?subject=subscribe

– archives: http://www.freelists.org/archive/geolllibre/

http://freelists.org/






● In order to link all these people: a discussion list, provided by http://freelists.org

– to subscribe:mailto:[email protected]?subject=subscribe

– archives: http://www.freelists.org/archive/geolllibre/● And here is the code:

https://github.com/pierrechtux/geolllibre

http://freelists.org/





Here we go! Discussions, ideas flourish...

● Existing bricks, wishes, comparisons with existing software (re-implement such or such proprietary software package...)

● Compilation of initiatives, some Free/Libre, some free (cost-free) but not Free/Libre, open-source (refocus of debates upon these notions).

● Whining concerning regretted abandonwares...

● Plethora of ideas.

Liberation of TecTri source code● Program developed by the author, started in 1988, it used

to be commercialised.

● Helps the structural geologist to sort fault measurements populations.

● Licence equivalent to STOYL*.* Sort This Out Yourselves Licence

Liberation of TecTri source code

Structural analysis: processing using TecTri, a software package developed by me.

Commercialisation of a MS-Windows version.Regular use during professionnal work.

Open-sourced.

Project: make it multi-platform.

A geotechnician's point of view:

/*

Geotechnics: a specialised field where mechanical characteristics of rocks/soils are considered. Typically, a geotechnician is required whenever one wants to build something: a small house, a larger building, a highway, a bridge, a tunnel, etc.

*/

A geotechnician's point of view:

/*

Geotechnics: a specialised field where mechanical characteristics of rocks/soils are considered. Typically, a geotechnician is required whenever one wants to build something: a small house, a larger building, a highway, a bridge, a tunnel, etc.

*/

To build something that it supposed to last, so that it stays upright, it is necessary to start from a stable support, with foundations correctly designed.

-- common sense detected

... that we translate into computing terms:

● A properly designed data model is needed, with a thorough agreement on vocabularies and semantics of used words (whether they refer to variables, objects, methods or functions), right from the start.

... that we translate into computing terms:


● These data shall be used as a base to build up software tools; the more the data model would have been correctly defined, the easier these latter implementation steps will be easy.

... now we've reached -at last- the DMBS


● These data shall be used as a base to build up software tools; the more the data model would have been correctly defined, the easier these latter implementation steps will be easy.

● We give in to the fashion of the moment: the relational model, served by a RDMBS (with a slight regret for hierarchical databases, out

of fashion, but so efficient for some geological data).

Now comes the time to implement things: quick, we need an engine!

● I will not offend you (the initial public of this presentation is attending a PgDay ) to repeat here all PostgreSQL's qualities. The choice of this marvellous tool was very pragmatically dictated by a few imbricated test queries, not very complicated, showing astonishingly fast performances, when compared to "concurrence". That was done in the Sudanese Sahara.

● TOL (Trumpeting Out Loud).

● I have never regretted this choice.

Original notes (just translated from French to English) leading to final choice:

06/08/2007 21:55:14temperature: 51 C

I test postgresql and mysql, to compare: definetly no doubt! Using the same data:mysql:

mysql> select count(id) from collars where id in (select id from collars group by id having count(id)>1) ;

+-----------+| count(id) |+-----------+| 251 | +-----------+1 row in set (46.16 sec)

postgresql:pierre=# \timingChronométrage activé.pierre=# select count(id) from collars

where id in (select id from collars group by id having count(id)>1) ;

count ------- 251(1 ligne)

Temps : 15,906 ms

Order of magnitude 1000 times faster. All right, I made up my mind!

Geology encompasses a vast, very vast spectrum...

● Impossible to address all specialities simultaneously.

● As opportunities make thieves, we starting implementing upon real-world operations in our daily work:

– field observations, which concerns any scope of geology (and many other disciplines, by the way);

– geology applied to mining exploration and, to a lesser extent, to mining exploitation.

● Fairly standard data architecture, very pragmatic: a quarter of a century of experience, experiments and "wisdom" => not storing any kind of Data for the very simple "joy" to just store it (such Data will eventually very rarely be neither checked nor validated nor even used, but it took much-much tim€ to gather).

● Geological objects modelling: what is to be modelled, up to which granularity should we go, according to which ultimate targets:

– LARGE debate!!

– choices to be made, sometimes drastically!

Field work => surveys: observations, measurements, sampling, etc.

Nota: this way of working is strictly common to all naturalist jobs: we must forecast and anticipate some mutualisation of means, in terms of concepts and therefore software bricks.

● What does a geologist do on Earth?

● He walks, watches, visits, makes observations, just as any naturalist scientist.

● He notes all his observations, as notes, maps, sketches, measurements.

Field work => surveys: observations, measurements, sampling, etc.

Field surveys => GeolPDA tool

● What does a geologist do on Earth?

● He walks, watches, visits, makes observations, just as any naturalist scientist.

● He notes all his observations, as notes, maps, sketches, measurements.

● Another related project: Geologist's Personal Digital Assistant:

– field data collect, using an Android compuphone ,

– not Free/Libre (sorry),

– data directly stored into an sqlite database,

– fully operational since end of 2011.

Field surveys => GeolPDA => PostGeol● sqlite database

=> data transfer towards PostGeol database (at last!)

● through a Rebol script which uses an sqlite (btn) driver and a PostgreSQL driver (written by Nenad Rakocevic (author of Red language))#!/usr/bin/rebol -qsREBOL [

Title: "Gets information from GeolPDA android device, and uploads it to a database"--------------------------8<---------------------------------------------------------------------

]License: {

This file is part of GeolLLibre software suite: FLOSS dedicated to Earth Sciences.############################################################################# ____ ___/_ ____ __ __ __ _()____ ____ _____ #### / ___\/ ___// _ |/ / / / / / / _/ _ \ / __ \/ ___/ #### / /___/ /_ / / | / / / / / / / // /_/_/ /_/ / /_ #### / /_/ / /___| \/ / /__/ /__/ /___/ // /_/ / _, _/ /___ #### \____/_____/ \___/_____/___/_____/__/_____/_/ |_/_____/ #### ############################################################################# Copyright (C) 2013 Pierre Chevalier <[email protected]>--------------------------8<---------------------------------------------------------------------db: open to-url rejoin [{btn://localhost/} dir_geolpda_local {geolpda_copy.db}]--------------------------8<---------------------------------------------------------------------run_query (rejoin ["SELECT * FROM poi WHERE _id > " max_waypoint_name])--------------------------8<---------------------------------------------------------------------geolpda_observations: copy sql_result--------------------------8<---------------------------------------------------------------------append sql_string {INSERT INTO public.field_observations (device,opid,year,obs_id,date,waypoint_name,x,y,z,description,code_litho,code_unit,srid,geologist,icon_descr,comments,sample_id,datasource,photos,audio,timestamp_epoch_ms) VALUES }--------------------------8<---------------------------------------------------------------------insert db sql_stringprint "Done..."insert db "COMMIT;"

Underground work => surveys: observations, measurements, sampling, etc.

● What does a geologist do in Earth?

– either direct observations (by means of galleries, caves, wells, adits, etc.);

– or (most often) he is not (inside Earth), so he manages to get some rocks indirectly: for instance by drilling holes:

● core drilling

or● destructive drilling.

Underground work => surveys: observations, measurements, sampling, etc.

Underground observations => PostGeol

Modelling of drill-hole data types

● Modelling = representing things, necessarily through a degrading/simplifying process

● Drill holes can be thought of as:

– a point on Earth surface => mapping representation

– a 3D polyline => cross-section or three-dimensional representation

(SGBDR parenthesis)Data model, relational point of view (as implemented):

– tables:

● drill holes heads (collars) => starting points

id, x, y, z, srid, length, azim, dip– deviation measurements => traces

id, depth, azim, dip– geologist's lithological descriptions => "barrel"

id, depth_from, depth_to, description, code– samples

id, depth_from, depth_to, sample_id => grades● analyses:

sample_id, au_ppm, ag_ppm, w_pc, cu_pc, pb_pc, zn_pc– etc. : down-hole logging, mineralised intervals, porosities, penetrometer

measurements, magnetic susceptibility measurements...

(almost infinite list of what can be done in a hole)

<= important: table is synonym of relation, in the relational database (virtual) world!

(SGBDH parenthesis)

No data repetition while following drill hole hierarchy, IDs are implicit=> no data duplication=> closer to our instinctive perception of things,in our cartesian technical and scientific culture

Data duplication is fairly close to Absolute Evil, as far as data management is concerned

Data model, hierarchical point of view:

– tables:

● drill holes heads (collars) => starting points

id, x, y, z, srid, length, azim, dip– deviation measurements => traces

depth, azim, dip– geologist's lithological descriptions => "barrel"

depth_from, depth_to, description, code– samples

depth_from, depth_to, sample_id => grades● analyses:

au_ppm, ag_ppm, w_pc, cu_pc, pb_pc, zn_pc– etc. : down-hole logging, mineralised intervals, porosities, penetrometer

measurements, magnetic susceptibility measurements...

(almost infinite list of what can be done in a hole)

PostGeol: drill holes database: implementation in the real world

● Implementation on industrial operations.

● Database BDEXPLO (ancient name for PostGeol).

● MAJOR confidentiality concerns between different operations

=> single-database technical choice:

– within an instance, data separation through a master table operations, opid field

– and homonym views for each tables from public.* in user schema, according to the contents of operation_active table

=> watertight bulkheads, within a single database inside a single instance

● Implementation and modifications, evolution, as various operations live.

● Implementation on clients' sites: supply of the complete turnkey solution: preconfigured GNU/Linux Debian server + operational base containing collected historical data, configured clients.

PostGeol: a set of tables

--autan bdexplo=> \dt+ public.* Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+------------------------------------+-------+--------------+------------+------------------------------------------------------------------------------------------------------------------- public | ancient_workings | table | data_admin | 56 kB | Ancient workings, either historic or recent public | baselines | table | data_admin | 16 kB | Baselines, for each prospect, defined as a theoritical line between two points public | conversions_oxydes_elements | table | pierre | 16 kB | Molecular weights of some oxides and factors to convert them to elements by weight. public | dh_collars | table | data_admin | 2488 kB | Drill holes collars or trenches starting points public | dh_core_boxes | table | data_admin | 104 kB | Core drill holes boxes public | dh_density | table | data_admin | 1416 kB | Density measurements along drill holes or trenches public | dh_devia | table | data_admin | 2400 kB | Drill holes or trenches deviations measurements public | dh_followup | table | data_admin | 16 kB | Simple table for daily drill holes followup public | dh_litho | table | data_admin | 19 MB | Drill holes or trenches geological descriptions public | dh_mineralised_intervals | table | data_admin | 2000 kB | Drill holes mineralised intercepts: stretch values over mineralised intervals, along drill holes or trenches public | dh_quicklog | table | data_admin | 48 kB | Quick geological log, typically done on hole finish, for an A4 log plot public | dh_sampling_bottle_roll | table | data_admin | 16 kB | Mineralurgical samples, bottle-roll tests results public | dh_sampling_grades | table | data_admin | 32 MB | Samples along drill holes and trenches, with grades public | dh_struct_measures | table | data_admin | 432 kB | Structural measurements done on core, or in trenches public | dh_tech | table | data_admin | 4608 kB | Technical drilling data, and geotechnical parameters public | dh_thinsections | table | data_admin | 88 kB | Thin sections for petrological studies public | doc_bdexplo_table_categories | table | pierre | 16 kB | public | doc_bdexplo_tables_descriptions | table | pierre | 24 kB | public | field_observations | table | data_admin | 1240 kB | Field observations: geological observations, on outcrops, floats, or any other observations; coherent with GeolPDA public | field_observations_struct_measures | table | data_admin | 744 kB | Structural measurements, related to an observation; coherent with GeolPDA public | field_photos | table | data_admin | 8192 bytes | Photographs taken in field, related to an observation public | formations_group_lithos | table | data_admin | 16 kB | Groups of lithologies, for simplification, typically for drill holes sections public | geoch_ana | table | data_admin | 13 MB | Assay results from geochemistry samples public | geoch_sampling | table | data_admin | 1912 kB | Geochemistry samples, from soil or stream sediments, location and description public | geoch_sampling_grades | table | data_admin | 4248 kB | Geochemistry samples with grades; table inherits from geoch_sampling public | geometry_columns_old | table | postgres | 16 kB | public | gpy_mag_ground | table | data_admin | 728 kB | Geophysics: ground mag public | grade_ctrl | table | data_admin | 14 MB | Grade-control samples during mining exploitation public | index_geo_documentation | table | data_admin | 16 kB | Index for any documentation, with lat-lon rectangles, so that any documentation may be accessed geographically public | lab_ana_batches_expedition | table | data_admin | 80 kB | Batches of samples sent for analysis public | lab_ana_batches_reception | table | data_admin | 160 kB | Batches of samples results received from laboratory public | lab_ana_columns_definition | table | data_admin | 24 kB | Definition of columns; obsolete public | lab_ana_qaqc_results | table | data_admin | 136 kB | Quality control assay results, internal to analytical laboratory public | lab_ana_results | table | data_admin | 82 MB | Laboratory results table, after laboratory instructions, related to LIMS system public | lex_codes | table | data_admin | 48 kB | General look-up table with codes for various tables and coded fields public | lex_datasource | table | data_admin | 152 kB | Lexicon of data sources, keeping track of imported file, for reference public | lex_standard | table | data_admin | 8192 bytes | table contenant les valeurs des standards or et multi elements public | licences | table | data_admin | 24 kB | Licences, tenements public | mag_declination | table | data_admin | 16 kB | Magnetic declination public | occurrences | table | data_admin | 272 kB | Occurences table: targets, mines, showings, deposits, mines. Compiled from various tables, and updated. public | occurrences_recup_depuis_dump | table | data_admin | 112 kB | Occurences table: targets, mines, showings, deposits, mines. Compiled from various tables, and updated. public | operation_active | table | data_admin | 16 kB | public | operations | table | data_admin | 16 kB | Operations, projects, operator or client name public | qc_sampling | table | data_admin | 760 kB | Quality control samples: duplicates, blanks, standards public | qc_standards | table | data_admin | 16 kB | Quality Control standard samples, most of them are CRM (Certified Reference Materials) public | shift_reports | table | data_admin | 376 kB | Daily reports from rigsites: one report per shift/machine/tool public | spatial_ref_sys | table | postgres | 3184 kB | public | spatial_ref_sys_old | table | postgres | 2952 kB | public | surface_samples_grades | table | data_admin | 5512 kB | Ponctual samples taken from surface: stream sediments, alluvial sediments, till, soils, termite mounds, rock | | | | | outcrops, floats, etc. with grades public | survey_lines | table | pierre | 16 kB | Survey lines, for geophysics or other types of linear surveys; defined with start and end points. public | topo_points | table | data_admin | 1720 kB | topographical data public | units | table | pierre | 16 kB | Units, with multiplicator factor(52 lignes)

--autan bdexplo=>


--autan bdexplo=>

● about drill holes: dh_*



--autan bdexplo=>

● for field observations: field_observations*


--autan bdexplo=> \dv+ pierre.* Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+------------------------------------------------+------+--------------+---------+---------------------------------- pierre | ancient_workings | vue | pierre | 0 bytes | pierre | baselines | vue | pierre | 0 bytes | pierre | dh_collars | vue | pierre | 0 bytes | pierre | dh_core_boxes | vue | pierre | 0 bytes | pierre | dh_density | vue | pierre | 0 bytes | pierre | dh_devia | vue | pierre | 0 bytes | pierre | dh_followup | vue | pierre | 0 bytes | pierre | dh_litho | vue | pierre | 0 bytes | pierre | dh_mineralised_intervals | vue | pierre | 0 bytes | pierre | dh_quicklog | vue | pierre | 0 bytes | pierre | dh_sampling_bottle_roll | vue | pierre | 0 bytes | pierre | dh_sampling_grades | vue | pierre | 0 bytes | pierre | dh_struct_measures | vue | pierre | 0 bytes | pierre | dh_tech | vue | pierre | 0 bytes | pierre | dh_thinsections | vue | pierre | 0 bytes | pierre | field_observations | vue | pierre | 0 bytes | pierre | field_observations_struct_measures | vue | pierre | 0 bytes | pierre | field_photos | vue | pierre | 0 bytes | pierre | formations_group_lithos | vue | pierre | 0 bytes | pierre | geoch_ana | vue | pierre | 0 bytes | pierre | geoch_sampling | vue | pierre | 0 bytes | pierre | geoch_sampling_grades | vue | pierre | 0 bytes | pierre | gpy_mag_ground | vue | pierre | 0 bytes | pierre | grade_ctrl | vue | pierre | 0 bytes | pierre | index_geo_documentation | vue | pierre | 0 bytes | pierre | lab_ana_batches_expedition | vue | pierre | 0 bytes | ----------------8<----------------8<----------------8<----------------8<----------------8<----------------8<--- pierre | field_observations_points | vue | pierre | 0 bytes | pierre | gdm_baselines | vue | pierre | 0 bytes | pierre | gdm_dh_devia | vue | pierre | 0 bytes | pierre | gdm_dh_litho | vue | pierre | 0 bytes | pierre | gdm_dh_mine_0 | vue | pierre | 0 bytes | pierre | gdm_dh_mine_1 | vue | pierre | 0 bytes | pierre | gdm_dh_planned | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_tail | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_top | vue | pierre | 0 bytes | pierre | gdm_sections_array | vue | pierre | 0 bytes | pierre | gdm_selection | vue | pierre | 0 bytes | pierre | geoch_sampling_grades_points | vue | pierre | 0 bytes | pierre | licences_quadrangles | vue | pierre | 0 bytes | pierre | operations_quadrangles | vue | pierre | 0 bytes | pierre | petro_mineralo_study_dh_collars | vue | pierre | 0 bytes | pierre | petro_mineralo_study_field_observations_points | vue | pierre | 0 bytes | pierre | surface_samples_grades_points | vue | pierre | 0 bytes | pierre | surpac_survey | vue | pierre | 0 bytes | pierre | topo_points_points | vue | pierre | 0 bytes | (85 lignes)

--autan bdexplo=> \dv+ public.* Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+-------------------------------------------+------+--------------+---------+------------- public | dh_collars_points_latlon | vue | pierre | 0 bytes | public | field_observations_points | vue | pierre | 0 bytes | public | field_observations_struct_measures_points | vue | pierre | 0 bytes | public | licences_polygons | vue | pierre | 0 bytes | public | licences_quadrangles | vue | pierre | 0 bytes | public | survey_lines_plines | vue | pierre | 0 bytes | (10 lignes)

PostGeol: a set of views

Views per user schema (data separation)

--autan bdexplo=> \dv+ pierre.* Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+------------------------------------------------+------+--------------+---------+---------------------------------- pierre | ancient_workings | vue | pierre | 0 bytes | pierre | baselines | vue | pierre | 0 bytes | pierre | dh_collars | vue | pierre | 0 bytes | pierre | dh_core_boxes | vue | pierre | 0 bytes | pierre | dh_density | vue | pierre | 0 bytes | pierre | dh_devia | vue | pierre | 0 bytes | pierre | dh_followup | vue | pierre | 0 bytes | pierre | dh_litho | vue | pierre | 0 bytes | pierre | dh_mineralised_intervals | vue | pierre | 0 bytes | pierre | dh_quicklog | vue | pierre | 0 bytes | pierre | dh_sampling_bottle_roll | vue | pierre | 0 bytes | pierre | dh_sampling_grades | vue | pierre | 0 bytes | pierre | dh_struct_measures | vue | pierre | 0 bytes | pierre | dh_tech | vue | pierre | 0 bytes | pierre | dh_thinsections | vue | pierre | 0 bytes | pierre | field_observations | vue | pierre | 0 bytes | pierre | field_observations_struct_measures | vue | pierre | 0 bytes | pierre | field_photos | vue | pierre | 0 bytes | pierre | formations_group_lithos | vue | pierre | 0 bytes | pierre | geoch_ana | vue | pierre | 0 bytes | pierre | geoch_sampling | vue | pierre | 0 bytes | pierre | geoch_sampling_grades | vue | pierre | 0 bytes | pierre | gpy_mag_ground | vue | pierre | 0 bytes | pierre | grade_ctrl | vue | pierre | 0 bytes | pierre | index_geo_documentation | vue | pierre | 0 bytes | pierre | lab_ana_batches_expedition | vue | pierre | 0 bytes | pierre | lab_ana_batches_reception | vue | pierre | 0 bytes | pierre | lab_ana_columns_definition | vue | pierre | 0 bytes | pierre | lab_ana_qaqc_results | vue | pierre | 0 bytes | pierre | lab_ana_results | vue | pierre | 0 bytes | pierre | lex_codes | vue | pierre | 0 bytes | pierre | lex_datasource | vue | pierre | 0 bytes | pierre | lex_standard | vue | pierre | 0 bytes | pierre | licences | vue | pierre | 0 bytes | pierre | mag_declination | vue | pierre | 0 bytes | pierre | occurrences | vue | pierre | 0 bytes | pierre | qc_sampling | vue | pierre | 0 bytes | pierre | qc_standards | vue | pierre | 0 bytes | pierre | shift_reports | vue | pierre | 0 bytes | pierre | surface_samples_grades | vue | pierre | 0 bytes | pierre | topo_points | vue | pierre | 0 bytes | pierre | collars_selection | vue | pierre | 0 bytes | pierre | dh_collars_for_gpx | vue | pierre | 0 bytes | pierre | dh_collars_points | vue | pierre | 0 bytes | pierre | dh_collars_points_last_ana_results | vue | pierre | 0 bytes | pierre | dh_mineralised_intervals0_traces_3d | vue | pierre | 0 bytes | pierre | dh_sampling_avg_grades_3dpoints | vue | pierre | 0 bytes | pierre | dh_sampling | vue | pierre | 0 bytes | pierre | dh_sampling_grades_graph_au_6 | vue | pierre | 0 bytes | pierre | dh_sampling_grades_open_ended_au_tail | vue | pierre | 0 bytes | pierre | dh_sampling_grades_open_ended_au_top | vue | pierre | 0 bytes | pierre | dh_sampling_mineralised_intervals_graph_au6 | vue | pierre | 0 bytes | pierre | dh_traces_3d | vue | pierre | 0 bytes | pierre | field_observations_points | vue | pierre | 0 bytes | pierre | gdm_baselines | vue | pierre | 0 bytes | pierre | gdm_dh_devia | vue | pierre | 0 bytes | pierre | gdm_dh_litho | vue | pierre | 0 bytes | pierre | gdm_dh_mine_0 | vue | pierre | 0 bytes | pierre | gdm_dh_mine_1 | vue | pierre | 0 bytes | pierre | gdm_dh_planned | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_tail | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_top | vue | pierre | 0 bytes | pierre | gdm_sections_array | vue | pierre | 0 bytes | pierre | gdm_selection | vue | pierre | 0 bytes | pierre | geoch_sampling_grades_points | vue | pierre | 0 bytes | pierre | licences_quadrangles | vue | pierre | 0 bytes | pierre | operations_quadrangles | vue | pierre | 0 bytes | pierre | petro_mineralo_study_dh_collars | vue | pierre | 0 bytes | pierre | petro_mineralo_study_field_observations_points | vue | pierre | 0 bytes | pierre | surface_samples_grades_points | vue | pierre | 0 bytes | pierre | surpac_survey | vue | pierre | 0 bytes | pierre | topo_points_points | vue | pierre | 0 bytes | (85 lignes)


● within user schema: views with same names as tablesViews simply defined by a join on operation_active a bit like this:

tables: run_query {SELECT tablename FROM pg_tables WHERE schemaname = 'public';}

foreach tablename tables [append sql_string rejoin

["CREATE VIEW " tablename " AS SELECT " tablename ".* FROM " tablename " JOIN operation_active ON " tablename ".opid = operation_active.opid;" newline] ]

=> vues with same names as tables, restricted to active operation=> operations separation, using same SQL queries

Geographical views

--autan bdexplo=> \dv+ pierre.* Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+------------------------------------------------+------+--------------+---------+--------------------------------------------------8<----------------8<----------------8<----------------8<----------------8<----------------8<--- pierre | collars_selection | vue | pierre | 0 bytes | pierre | dh_collars_for_gpx | vue | pierre | 0 bytes | pierre | dh_collars_points | vue | pierre | 0 bytes | pierre | dh_collars_points_last_ana_results | vue | pierre | 0 bytes | pierre | dh_mineralised_intervals0_traces_3d | vue | pierre | 0 bytes | pierre | dh_sampling_avg_grades_3dpoints | vue | pierre | 0 bytes | pierre | dh_sampling | vue | pierre | 0 bytes | pierre | dh_sampling_grades_graph_au_6 | vue | pierre | 0 bytes | pierre | dh_sampling_grades_open_ended_au_tail | vue | pierre | 0 bytes | pierre | dh_sampling_grades_open_ended_au_top | vue | pierre | 0 bytes | pierre | dh_sampling_mineralised_intervals_graph_au6 | vue | pierre | 0 bytes | pierre | dh_traces_3d | vue | pierre | 0 bytes | pierre | field_observations_points | vue | pierre | 0 bytes | pierre | gdm_baselines | vue | pierre | 0 bytes | pierre | gdm_dh_devia | vue | pierre | 0 bytes | pierre | gdm_dh_litho | vue | pierre | 0 bytes | pierre | gdm_dh_mine_0 | vue | pierre | 0 bytes | pierre | gdm_dh_mine_1 | vue | pierre | 0 bytes | pierre | gdm_dh_planned | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_tail | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_top | vue | pierre | 0 bytes | pierre | gdm_sections_array | vue | pierre | 0 bytes | pierre | gdm_selection | vue | pierre | 0 bytes | pierre | geoch_sampling_grades_points | vue | pierre | 0 bytes | pierre | licences_quadrangles | vue | pierre | 0 bytes | pierre | operations_quadrangles | vue | pierre | 0 bytes | pierre | petro_mineralo_study_dh_collars | vue | pierre | 0 bytes | pierre | petro_mineralo_study_field_observations_points | vue | pierre | 0 bytes | pierre | surface_samples_grades_points | vue | pierre | 0 bytes | pierre | surpac_survey | vue | pierre | 0 bytes | pierre | topo_points_points | vue | pierre | 0 bytes | (85 lignes)


● dynamic geographical views:

– in user schema: for current operation

– in public schema: for all operations within the database

Views built by using PostGIS functions, with dynamic geometries:

CREATE VIEW dh_collars_pointsAS SELECT *, GeomFromewkt( 'SRID='|| srid || ';POINT('|| x || ' ' || y || ' ' || z || ')' ) FROM dh_collars;

Specialised views, for external software packages

--autan bdexplo=> \dv+ pierre.* Liste des relations Schéma | Nom | Type | Propriétaire | Taille | Description --------+------------------------------------------------+------+--------------+---------+--------------------------------------------------8<----------------8<----------------8<----------------8<----------------8<----------------8<--- pierre | collars_selection | vue | pierre | 0 bytes | pierre | dh_collars_for_gpx | vue | pierre | 0 bytes | pierre | dh_collars_points | vue | pierre | 0 bytes | pierre | dh_collars_points_last_ana_results | vue | pierre | 0 bytes | pierre | dh_mineralised_intervals0_traces_3d | vue | pierre | 0 bytes | pierre | dh_sampling_avg_grades_3dpoints | vue | pierre | 0 bytes | pierre | dh_sampling | vue | pierre | 0 bytes | pierre | dh_sampling_grades_graph_au_6 | vue | pierre | 0 bytes | pierre | dh_sampling_grades_open_ended_au_tail | vue | pierre | 0 bytes | pierre | dh_sampling_grades_open_ended_au_top | vue | pierre | 0 bytes | pierre | dh_sampling_mineralised_intervals_graph_au6 | vue | pierre | 0 bytes | pierre | dh_traces_3d | vue | pierre | 0 bytes | pierre | field_observations_points | vue | pierre | 0 bytes | pierre | gdm_baselines | vue | pierre | 0 bytes | pierre | gdm_dh_devia | vue | pierre | 0 bytes | pierre | gdm_dh_litho | vue | pierre | 0 bytes | pierre | gdm_dh_mine_0 | vue | pierre | 0 bytes | pierre | gdm_dh_mine_1 | vue | pierre | 0 bytes | pierre | gdm_dh_planned | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_tail | vue | pierre | 0 bytes | pierre | gdm_dh_sampling_grades_open_ended_au_top | vue | pierre | 0 bytes | pierre | gdm_sections_array | vue | pierre | 0 bytes | pierre | gdm_selection | vue | pierre | 0 bytes | pierre | geoch_sampling_grades_points | vue | pierre | 0 bytes | pierre | licences_quadrangles | vue | pierre | 0 bytes | pierre | operations_quadrangles | vue | pierre | 0 bytes | pierre | petro_mineralo_study_dh_collars | vue | pierre | 0 bytes | pierre | petro_mineralo_study_field_observations_points | vue | pierre | 0 bytes | pierre | surface_samples_grades_points | vue | pierre | 0 bytes | pierre | surpac_survey | vue | pierre | 0 bytes | pierre | topo_points_points | vue | pierre | 0 bytes | (85 lignes)


● specialised views, for specific softwares

● useful queries, just kept handy

Examples of some useful views

--autan bdexplo=> SELECT * FROM dh_sampling_grades_graph_au_6 WHERE id = 'S443'; opid | id | depfrom | depto | sample_id | au1_ppm | au2_ppm | au6_ppm | weight_kg | core_loss_cm | graph_au_6 ------+------+---------+-------+-----------+---------+---------+----------+-----------+--------------+-------------------------------------------------------------------------------------------------- 11 | S443 | 0.00 | 1.00 | S443_001 | | | 0.730 | | | #### 11 | S443 | 1.00 | 2.00 | S443_002 | | | 2.530 | | | ############# 11 | S443 | 2.00 | 3.00 | S443_003 | | | 1.590 | | | ######## 11 | S443 | 3.00 | 4.00 | S443_004 | | | 1.060 | | | ##### 11 | S443 | 4.00 | 5.00 | S443_005 | | | 0.670 | | | ### 11 | S443 | 5.00 | 6.00 | S443_006 | | | 0.680 | | | ### 11 | S443 | 6.00 | 7.00 | S443_007 | | | 0.800 | | | #### 11 | S443 | 7.00 | 8.00 | S443_008 | | | 0.730 | | | #### 11 | S443 | 8.00 | 9.00 | S443_009 | | | 0.500 | | | ### 11 | S443 | 9.00 | 10.00 | S443_010 | | | 0.300 | | | ## 11 | S443 | 10.00 | 11.00 | S443_011 | | | 1.110 | | | ###### 11 | S443 | 11.00 | 12.00 | S443_012 | | | 0.180 | | | # 11 | S443 | 12.00 | 13.00 | S443_013 | | | 0.410 | | | ## 11 | S443 | 13.00 | 14.00 | S443_014 | | | 0.660 | | | ### 11 | S443 | 14.00 | 15.00 | S443_015 | | | 164.680 | | | ################################################################################################# 11 | S443 | 15.00 | 16.00 | S443_016 | | | 23.770 | | | ################################################################################################# 11 | S443 | 16.00 | 17.00 | S443_017 | | | 33.860 | | | ################################################################################################# 11 | S443 | 17.00 | 18.00 | S443_018 | | | 17.810 | | | ######################################################################################### 11 | S443 | 18.00 | 19.00 | S443_019 | | | 210.070 | | | ################################################################################################# 11 | S443 | 19.00 | 20.00 | S443_020 | | | 14.490 | | | ######################################################################## 11 | S443 | 20.00 | 21.00 | S443_021 | | | 19.940 | | | ################################################################################################# 11 | S443 | 21.00 | 22.00 | S443_022 | | | 6.230 | | | ############################### 11 | S443 | 22.00 | 23.00 | S443_023 | | | 1.110 | | | ###### 11 | S443 | 23.00 | 24.00 | S443_024 | | | 1.830 | | | ######### 11 | S443 | 24.00 | 25.00 | S443_025 | | | 5.510 | | | ############################ 11 | S443 | 25.00 | 26.00 | S443_026 | | | 1.660 | | | ######## 11 | S443 | 26.00 | 27.00 | S443_027 | | | 4.210 | | | ##################### 11 | S443 | 27.00 | 28.00 | S443_028 | | | 0.870 | | | #### 11 | S443 | 28.00 | 29.00 | S443_029 | | | 5.770 | | | ############################# 11 | S443 | 29.00 | 30.00 | S443_030 | | | 4.750 | | | ######################## 11 | S443 | 30.00 | 31.00 | S443_031 | | | 0.780 | | | #### 11 | S443 | 31.00 | 32.00 | S443_032 | | | 89.150 | | | ################################################################################################# 11 | S443 | 32.00 | 33.00 | S443_033 | | | 67.380 | | | ################################################################################################# 11 | S443 | 33.00 | 34.00 | S443_034 | | | 62.310 | | | ################################################################################################# 11 | S443 | 34.00 | 35.00 | S443_035 | | | 3.980 | | | #################### 11 | S443 | 35.00 | 36.00 | S443_036 | | | 13.250 | | | ################################################################## 11 | S443 | 36.00 | 37.00 | S443_037 | | | 2.780 | | | ############## 11 | S443 | 37.00 | 38.00 | S443_038 | | | 74.190 | | | ################################################################################################# 11 | S443 | 38.00 | 39.00 | S443_039 | | | 44.400 | | | ################################################################################################# 11 | S443 | 39.00 | 40.00 | S443_040 | | | 14.210 | | | ####################################################################### 11 | S443 | 40.00 | 41.00 | S443_041 | | | 0.830 | | | #### 11 | S443 | 41.00 | 42.00 | S443_042 | | | 2.780 | | | ############## 11 | S443 | 42.00 | 43.00 | S443_043 | | | 0.820 | | | #### 11 | S443 | 43.00 | 44.00 | S443_044 | | | 0.270 | | | # 11 | S443 | 44.00 | 45.00 | S443_045 | | | 0.430 | | | ## 11 | S443 | 45.00 | 46.00 | S443_046 | | | 8.660 | | | ########################################### 11 | S443 | 46.00 | 47.00 | S443_047 | | | 93.270 | | | ################################################################################################# 11 | S443 | 47.00 | 48.00 | S443_048 | | | 1.180 | | | ###### 11 | S443 | 48.00 | 49.00 | S443_049 | | | 0.360 | | | ## 11 | S443 | 49.00 | 50.00 | S443_050 | | | 3.630 | | | ################## 11 | S443 | 50.00 | 51.00 | S443_051 | | | 0.940 | | | ##### 11 | S443 | 51.00 | 52.00 | S443_052 | | | 2.570 | | | ############# 11 | S443 | 52.00 | 53.00 | S443_053 | | | 3.800 | | | ################### 11 | S443 | 53.00 | 54.00 | S443_054 | | | 9.360 | | | ############################################### 11 | S443 | 54.00 | 55.00 | S443_055 | | | 4.130 | | | ##################### 11 | S443 | 55.00 | 56.00 | S443_056 | | | 0.440 | | | ## 11 | S443 | 56.00 | 57.00 | S443_057 | | | 0.330 | | | ## 11 | S443 | 57.00 | 58.00 | S443_058 | | | 0.110 | | | # 11 | S443 | 58.00 | 58.80 | S443_059 | | | 0.050 | | | 11 | S443 | 58.80 | 60.00 | S443_060 | | | -999.000 | | | 11 | S443 | 60.00 | 61.30 | S443_061 | | | 0.140 | | | # 11 | S443 | 61.30 | 62.00 | S443_062 | | | 0.040 | | | 11 | S443 | 62.00 | 63.00 | S443_063 | | | 0.070 | | | 11 | S443 | 63.00 | 68.40 | S443_068 | | | -999.000 | | | (64 lignes)

● dh_sampling_grades_graph_au_6: graph in "ascii-art" of gold grades within drill holes

--autan bdexplo=> SELECT * FROM dh_sampling_mineralised_intervals_graph_au6 WHERE id = 'S443'; opid | id | depfrom | depto | mineralised_interval | sample_id | weight_kg | core_loss_cm | aumaxi_ppm | graph_aumaxi------+------+---------+-------+----------------------------------------------------+-----------+-----------+--------------+------------+------------------------------------------------ 11 | S443 | 0.00 | 1.00 | >=== 55.00 m @ 18.80 g/t (accu: 1033.97) ========= | S443_001 | | | 0.730 | #### 11 | S443 | 1.00 | 2.00 | | | S443_002 | | | 2.530 | ############# 11 | S443 | 2.00 | 3.00 | | | S443_003 | | | 1.590 | ######## 11 | S443 | 3.00 | 4.00 | | | S443_004 | | | 1.060 | ##### 11 | S443 | 4.00 | 5.00 | | | S443_005 | | | 0.670 | ### 11 | S443 | 5.00 | 6.00 | | | S443_006 | | | 0.680 | ### 11 | S443 | 6.00 | 7.00 | | | S443_007 | | | 0.800 | #### 11 | S443 | 7.00 | 8.00 | | | S443_008 | | | 0.730 | #### 11 | S443 | 8.00 | 9.00 | | | S443_009 | | | 0.500 | ### 11 | S443 | 9.00 | 10.00 | | | S443_010 | | | 0.300 | ## 11 | S443 | 10.00 | 11.00 | | | S443_011 | | | 1.110 | ###### 11 | S443 | 11.00 | 12.00 | | | S443_012 | | | 0.180 | # 11 | S443 | 12.00 | 13.00 | | | S443_013 | | | 0.410 | ## 11 | S443 | 13.00 | 14.00 | | | S443_014 | | | 0.660 | ### 11 | S443 | 14.00 | 15.00 | | | S443_015 | | | 164.680 | ############################################### 11 | S443 | 15.00 | 16.00 | | | S443_016 | | | 23.770 | ############################################### 11 | S443 | 16.00 | 17.00 | | | S443_017 | | | 33.860 | ############################################### 11 | S443 | 17.00 | 18.00 | | | S443_018 | | | 17.810 | ############################################### 11 | S443 | 18.00 | 19.00 | | | S443_019 | | | 210.070 | ############################################### 11 | S443 | 19.00 | 20.00 | | | S443_020 | | | 14.490 | ############################################### 11 | S443 | 20.00 | 21.00 | | | S443_021 | | | 19.940 | ############################################### 11 | S443 | 21.00 | 22.00 | | | S443_022 | | | 6.230 | ############################### 11 | S443 | 22.00 | 23.00 | | | S443_023 | | | 1.110 | ###### 11 | S443 | 23.00 | 24.00 | | | S443_024 | | | 1.830 | ######### 11 | S443 | 24.00 | 25.00 | | | S443_025 | | | 5.510 | ############################ 11 | S443 | 25.00 | 26.00 | | | S443_026 | | | 1.660 | ######## 11 | S443 | 26.00 | 27.00 | | | S443_027 | | | 4.210 | ##################### 11 | S443 | 27.00 | 28.00 | | | S443_028 | | | 0.870 | #### 11 | S443 | 28.00 | 29.00 | | | S443_029 | | | 5.770 | ############################# 11 | S443 | 29.00 | 30.00 | | | S443_030 | | | 4.750 | ######################## 11 | S443 | 30.00 | 31.00 | | | S443_031 | | | 0.780 | #### 11 | S443 | 31.00 | 32.00 | | | S443_032 | | | 89.150 | ############################################### 11 | S443 | 32.00 | 33.00 | | | S443_033 | | | 67.380 | ############################################### 11 | S443 | 33.00 | 34.00 | | | S443_034 | | | 62.310 | ############################################### 11 | S443 | 34.00 | 35.00 | | | S443_035 | | | 3.980 | #################### 11 | S443 | 35.00 | 36.00 | | | S443_036 | | | 13.250 | ############################################### 11 | S443 | 36.00 | 37.00 | | | S443_037 | | | 2.780 | ############## 11 | S443 | 37.00 | 38.00 | | | S443_038 | | | 74.190 | ############################################### 11 | S443 | 38.00 | 39.00 | | | S443_039 | | | 44.400 | ############################################### 11 | S443 | 39.00 | 40.00 | | | S443_040 | | | 14.210 | ############################################### 11 | S443 | 40.00 | 41.00 | | | S443_041 | | | 0.830 | #### 11 | S443 | 41.00 | 42.00 | | | S443_042 | | | 2.780 | ############## 11 | S443 | 42.00 | 43.00 | | | S443_043 | | | 0.820 | #### 11 | S443 | 43.00 | 44.00 | | | S443_044 | | | 0.270 | # 11 | S443 | 44.00 | 45.00 | | | S443_045 | | | 0.430 | ## 11 | S443 | 45.00 | 46.00 | | | S443_046 | | | 8.660 | ########################################### 11 | S443 | 46.00 | 47.00 | | | S443_047 | | | 93.270 | ############################################### 11 | S443 | 47.00 | 48.00 | | | S443_048 | | | 1.180 | ###### 11 | S443 | 48.00 | 49.00 | | | S443_049 | | | 0.360 | ## 11 | S443 | 49.00 | 50.00 | | | S443_050 | | | 3.630 | ################## 11 | S443 | 50.00 | 51.00 | | | S443_051 | | | 0.940 | ##### 11 | S443 | 51.00 | 52.00 | | | S443_052 | | | 2.570 | ############# 11 | S443 | 52.00 | 53.00 | | | S443_053 | | | 3.800 | ################### 11 | S443 | 53.00 | 54.00 | | | S443_054 | | | 9.360 | ############################################### 11 | S443 | 54.00 | 55.00 | >================================================= | S443_055 | | | 4.130 | ##################### 11 | S443 | 55.00 | 56.00 | | S443_056 | | | 0.440 | ## 11 | S443 | 56.00 | 57.00 | | S443_057 | | | 0.330 | ## 11 | S443 | 57.00 | 58.00 | | S443_058 | | | 0.110 | # 11 | S443 | 58.00 | 58.80 | | S443_059 | | | 0.050 | 11 | S443 | 58.80 | 60.00 | | S443_060 | | | -999.000 | 11 | S443 | 60.00 | 61.30 | | S443_061 | | | 0.140 | # 11 | S443 | 61.30 | 62.00 | | S443_062 | | | 0.040 | 11 | S443 | 62.00 | 63.00 | | S443_063 | | | 0.070 | 11 | S443 | 63.00 | 68.40 | | S443_068 | | | -999.000 | (64 lignes)

--autan bdexplo=>

● dh_sampling_grades_graph_au_6: graph in "ascii-art" of gold grades within drill holes, and composite intervals

Composite mineralised interval, computed by a Python program

which updated PostGeol.Various parameters: cutoff grade,

dilutions, minimum accumulations, etc.

Examples of some useful views

Some procedures, triggers (few)

CREATE OR REPLACE FUNCTION lab_ana_results_sample_id_default() RETURNS trigger AS$BODY$BEGINUPDATE lab_ana_results SET sample_id = lab_pjc_sampleident;RETURN NULL;END;$BODY$LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER lab_ana_results_insert AFTER INSERT ON lab_ana_results FOR EACH STATEMENT EXECUTE PROCEDURE lab_ana_results_sample_id_default();

CREATE FUNCTION lab_ana_results_sample_id_default_value_num() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN--UPDATE public.lab_ana_results SET sample_id = lab_sampleid WHERE (sample_id IS NULL OR sample_id = '') AND (lab_sampleid IS NOT NULL OR lab_sampleid <> '');UPDATE public.lab_ana_results SET sample_id_lab = sample_id;UPDATE public.lab_ana_results SET sample_id = REPLACE(sample_id, 'STD:', '') WHERE sample_id ILIKE 'STD%';

UPDATE public.lab_ana_results SET value_num = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value, 'IS', '-999'), 'NSS', '-999'), 'LNR', '-9999'), 'NA', '-99'), '<', '-'), '>', ''), 'Not Received', '-9999'), 'Bag Empty', '-9999')::numeric WHERE value <> 'NULL' AND value IS NOT NULL AND value_num IS NULL;RETURN NULL;END;$$;

Data entry, production of cross-sections, logs, maps

Données de teneurs confidentielles masquées ou falsifiées

Evolution through time of PostGeol (and/or his ancestor)

● A cursor evolving with time from a "stupid" database towards an "intelligent" one, by progressively shifting logic inside the database:

– first implementation towards 1996: dBase III+, dBase IV

– around 2000: Access 2 (...)

– 2007: PostgreSQL● "dumb" data deposit de données "bête" (dépôtoir), then:● implementation of views, functions, triggers,● implementation of referential integrity (absolutely inapplicable

before, for uncanny reasons of data quality to be ingested by database),

● possibilities wider and wider (plpythonu, dream of plrebolu and plredu)

A set of utilities built around PostGeol● Generic tools:

– shell scripts for import-export: csv2sql and sql2csv

having to process external data with multiples versions: massive use of csv2sql, sql2csv and diff (and vimdiff, xxdiff, etc.)

(impossibility, during a long time, to implement referential integrity...)● Python programs:

● composite mineralised intervals: procedure_genere_passmin.py● automatic import of results:

procedure_import_fichier_veritas.py

procedure_import_fichier_veritas_bleg_bdexplosmi.py● Rebol programs:

– gll_geolpda_data_mgr.r

– gll_bdexplo_generation_checks_vapas.r

– gll_bdexplo_generation_doc.r

– gll_geolpda_report_generator.r

common routines in: gll_routines.r

Outputs from PostGeol

● Mapping

=> PostGIS, naturally

– through views which build dynamic geographical objects from data (cf. supra)

– to QGIS or any other:

● mapping of observations points on surface

● "flat" mapping of projected drill holes

● (BUT with a geometry (WKT) ready to be viewed in 3D (by means of tools such as itowns https://itowns.github.io/ or horao https://github.com/Oslandia/horao?...)

● Cross-section, for underground data:

– by means of proprietary software,

– for the time being...Proberb: History is not finished, and will never be.

https://itowns.github.io/

https://github.com/Oslandia/horao

Example: report generation about data inconsistencies

Automatic generation of a large HTML outlining all inconsistencies, incongruities, uncanny cases, etc.

Example: field mission automatic report generation

● Automatic generation of a large HTML with all data collected in the field in order: observations, pictures, structural measurements, etc.

● Some minor editions using LibreOffice or equivalent

=> comprehensive appendix for the final mission report

Plans, projected drill holes

● plan view: vertically projected drill holes, histograms representing gold grades; blue points outline open-ended drill holes

=> all this come from dynamic queries

Work maps, producedby QGIS

QGIS session, hooked into PostGeol

Plans, cross-sections and logs, live connection to PostGeol through ODBC

Work session in a specialised software package (GDM): plan, cross-section, and logs => everything is connected to PostGeol; design of additional drill holes, based on existing data.

Cross-sections and plans: trenches and drill holes

Drill hole log

Drill hole log:document showing a drill holes, with geological descriptions and assay results from systematic sampling.

Simple drill hole log

Drill holes data viewed in 3D

Still using GDM: 3D view of drill holes, des sondages, highlighting gold grades.

Geological bodies modelling

Exchanges back and forth: real world => modelling => DB => rendering => reality

(following pages)

● In the field: comparing:

– plans rendered from data coming from PostGeol

– cross-sections made from PostGeol data

– an example of cross-section with:● drill holes existing in database,● executed drill holes (data not yet completely entered in PostGeol, only

drill hole trace is plotted, curved (deviation data has been entered)),● planned drill holes,● drill hole core from the real world placed on top of cross-section

representing the drill hole where the core comes from (note the correct coincidence of real structures with the interpretations on the section).

This type of mineralisation is a VMS with Cu-Zn (Volcanogenic-hosted Massive Sulphides with copper and zinc).

Drill hole sectionswith down-hole probing

● Exploration for Uranium in Northern Saskatchewan, Canada

● Down-hole probing: very numerous daata along holes (red and green histograms)

● Hierarchical database

(a hyper-efficient software, written a few decades ago in fortran: it would be a perfect "target" for some lobbying in order to free its code (before it becomes a dead abandonware))

Project review, after... 8 years already!...● No hypocrisy: criticism, + and -

– A diverse, heterogeneous community (cultural gaps), to be animated;

– some emails (too often) out of the list.

– Discussion-list:● a marvelous and efficient mean of development in many cases...● but in our case, we realise that it did not work out very well...

why? => for cultural reasons?● Hosting the list on free.fr, non-archived, must have played a role

(google ranking < 0)

=> Fixed: switched to a better, more visible solution:

http://www.freelists.org/list/geolllibre– github: 2 coders (rather 1 and 1/2...)

– TecTri liberated... but no contributor...● Nostra culpa, mea culpa

http://www.freelists.org/list/geolllibre

● Free Software is now renowned and recognised, technically and in the society (we, Librists, are not seen any more as dreaming utopists).

● Restart of the project:

– decision to purposefully orient GeolLLibre more towards PostgreSQL:

– thus PostGeol, as PostgreSQL is becoming a stronger and stronger key pillar, appearing more and more stable and versatile that we could have ever thought

– (too bad for the database-agnostic side: we will try to keep this compatibility, but, um not very strongly (anyays, AFAIK, there was no other implementation than on PostgreSQL))

● Connection with other free/libre projects, neighbours/cousins: GCDkit, others...

● Other possible opportunities: some contacts, a non-free tool (...).

● Also: a project concerning the development of a Free/Libre software visualisation tool, within GeolLLibre, with the Pôle Technique Minier Guyanais, carried by the Collectivité Territoriale de Guyane (this could possibly concern a reviving of ParaviewGeo).

Project review, after... 8 years already!...

Thanks foryour attention!


PostGeol's mascot comes from Ghana, I brought it back in 1997.I'm using it as a bedside table, it supports proses from Olivier Auverlot and Guillaume Lelarge.I wanted to carry it to this conference, but I gave up, given its weight, the length of the travel and the size of my backpack.So instead, here is a photograph, with an eye from xeyes (mascot has lost its eyes since a long time).

Next time, I'll try to find another mascot, just a little bit smaller and lighter.
