-
Notifications
You must be signed in to change notification settings - Fork 5
/
gll_bdexplo_doc_generation.r
18975 lines (18894 loc) · 303 KB
/
gll_bdexplo_doc_generation.r
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
(en vrac)
Je révise la structure des tables, et je vois pour re-ordonner les champs:{{{
(commencé le 06_09_2013)
_______________ENCOURS_______________ 3
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------------------------+-------+----------+------------+--------------------------------------------------------------------------------------------------------------
public | operations | table | pierre | 16 kB | Operations, projects, operator or client name {{{
Table "public.operations"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+--------------------------------------------------------------+----------+-------------------------------------------------------------
opid | integer | not null default nextval('operations_opid_seq'::regclass) | plain | Operation identifier
operation | character varying(4) | | extended | Operation code
full_name | character varying(50) | | extended | Complete operation name
operator | character varying(50) | | extended | Operator: mining operator, exploration company, client name
year | integer | | plain | Year of operation activity
confidentiality | boolean | default true | plain | Confidentiality flag, true or false; default is true
lat_min | numeric(10,5) | not null | main | South latitude, decimal degrees, WGS84
lon_min | numeric(10,5) | not null | main | West longitude, decimal degrees, WGS84
lat_max | numeric(10,5) | | main | North latitude, decimal degrees, WGS84
lon_max | numeric(10,5) | | main | East latitude, decimal degrees, WGS84
comments | character varying(500) | | extended |
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
numauto | integer | not null default nextval('operations_numauto_seq'::regclass) | plain | Automatic integer
Indexes:
"opid" PRIMARY KEY, btree (opid)
Referenced by:
TABLE "public.dh_collars" CONSTRAINT "dh_collars_opid_fkey" FOREIGN KEY (opid) REFERENCES operations(opid)
Has OIDs: no
}}}
public | occurrences | table | pierre | 72 kB | Occurences table: targets, mines, showings, deposits, mines. Compiled from various tables, and updated. {{{
Table "public.occurrences"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+----------------------------------------------------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------
numauto | integer | | plain | Automatic integer primary key
name | character varying(50) | | extended | Occurence name
status | character varying(10) | | extended | Status: OCCUR = occurence ; OREB = orebody ; MINE = active mine ; MINED = exploited, depleted mine
x | double precision | | plain | UTM coordinate; kept as reference; the actual position is stored in the geom field
y | double precision | | plain | UTM coordinate; kept as reference; the actual position is stored in the geom field
description | character varying(254) | | extended | Occurence description: geological context, significant figures at current stage of exploration or exploitation
w_done | character varying(254) | | extended | Exploration work done, codified field: PROSPection (rock sampling on surface), SOIL geochemistry, MAPping, DECAPage, TRenches, Drill Holes
w_todo | character varying(254) | | extended | Exploration work to be done, codified field: PROSPection (rock sampling on surface), SOIL geochemistry, MAPping, DECAPage, TRenches, Drill Holes
geol_poten | character varying(22) | | extended | Geological potential (quite meaningless; field to be dropped)
grade | character varying(6) | | extended |
type | character varying(23) | | extended |
code_typ | character varying(6) | | extended |
size | integer | | plain |
au | character varying(4) | | extended |
trenches | character varying(9) | | extended |
coredrill | character varying(9) | | extended |
pdrill | character varying(6) | | extended |
max_grade | character varying(10) | | extended |
length | character varying(14) | | extended |
thickness | character varying(10) | | extended |
code_indic | character varying(10) | | extended |
geom | geometry | | main |
num_code | integer | | plain |
code | character varying(20) | | extended |
opid | integer | | plain | Operation identifier
srid | integer | | plain |
zone | character varying | | extended |
z | numeric | | main |
id | integer | not null default nextval('occurrences_id_seq'::regclass) | plain |
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
Indexes:
"occurrences_pkey" PRIMARY KEY, btree (id)
Check constraints:
"enforce_dims_geom" CHECK (ndims(geom) = 2)
"enforce_geotype_geom" CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL)
"enforce_srid_geom" CHECK (srid(geom) = 4326)
Has OIDs: no
(édition dans oobase, gros ménage à faire)
Ménage des champs à la noix:{{{
SELECT * FROM public.occurrences WHERE geol_poten IS NOT NULL;
14_09_2013__08_39_06 J'ajoute un champ comments dans lequel je sérialise les champs à la noix:{{{
--ALTER TABLE public.occurrences DROP COLUMN comments;
ALTER TABLE public.occurrences ADD COLUMN comments varchar;
--UPDATE public.occurrences SET comments = coalesce(comments, '') || 'geol_poten: '|| geol_poten::text || ', ' WHERE geol_poten IS NOT NULL;
--UPDATE 78
=> je fais ça pour tous les champs à la noix: {{{
rebol ;{{{
champsalanoix: [
geol_poten
grade
type
code_typ
size
au
trenches
coredrill
pdrill
max_grade
length
thickness
code_indic
num_code
srid
x
y
z
]
foreach c champsalanoix [
print rejoin [{UPDATE public.occurrences SET comments = coalesce(comments, '') || '} to-string c {: "'|| } to-string c {::text || '" ' WHERE } to-string c { IS NOT NULL;}]
]
print "UPDATE public.occurrences SET comments = '[' || substr(comments, 1, (length(comments) - 1)) || ']' WHERE comments IS NOT NULL;"
;}}}
=> premier essai: {{{
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'geol_poten: '|| geol_poten::text || ', ' WHERE geol_poten IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'grade: '|| grade::text || ', ' WHERE grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'type: '|| type::text || ', ' WHERE type IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_typ: '|| code_typ::text || ', ' WHERE code_typ IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'size: '|| size::text || ', ' WHERE size IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'au: '|| au::text || ', ' WHERE au IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'trenches: '|| trenches::text || ', ' WHERE trenches IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'coredrill: '|| coredrill::text || ', ' WHERE coredrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'pdrill: '|| pdrill::text || ', ' WHERE pdrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'max_grade: '|| max_grade::text || ', ' WHERE max_grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'length: '|| length::text || ', ' WHERE length IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'thickness: '|| thickness::text || ', ' WHERE thickness IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_indic: '|| code_indic::text || ', ' WHERE code_indic IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'num_code: '|| num_code::text || ', ' WHERE num_code IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'srid: '|| srid::text || ', ' WHERE srid IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'x: '|| x::text || ', ' WHERE x IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'y: '|| y::text || ', ' WHERE y IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'z: '|| z::text || ', ' WHERE z IS NOT NULL;
UPDATE public.occurrences SET comments = '[' || comments || ']' WHERE comments IS NOT NULL;
=> {{{
--autan bdexplo=>
abort TRANSACTION ; begin TRANSACTION ;
ROLLBACK
BEGIN
--autan bdexplo=>
ALTER TABLE public.occurrences ADD COLUMN comments varchar;
ALTER TABLE
--autan bdexplo=>
ULL;l_poten: '|| geol_poten::text || ', ' WHERE geol_poten IS NOT N
UPDATE 78
--autan bdexplo=>
'grade: '|| grade::text || ', ' WHERE grade IS NOT NULL;ts, '') ||
UPDATE 29
--autan bdexplo=>
'type: '|| type::text || ', ' WHERE type IS NOT NULL;ments, '') ||
UPDATE 201
--autan bdexplo=>
'code_typ: '|| code_typ::text || ', ' WHERE code_typ IS NOT NULL;|
UPDATE 0
--autan bdexplo=>
'size: '|| size::text || ', ' WHERE size IS NOT NULL;ments, '') ||
UPDATE 263
--autan bdexplo=>
'au: '|| au::text || ', ' WHERE au IS NOT NULL;ce(comments, '') ||
UPDATE 256
--autan bdexplo=>
'trenches: '|| trenches::text || ', ' WHERE trenches IS NOT NULL;|
UPDATE 14
--autan bdexplo=>
;coredrill: '|| coredrill::text || ', ' WHERE coredrill IS NOT NULL
UPDATE 52
--autan bdexplo=>
'pdrill: '|| pdrill::text || ', ' WHERE pdrill IS NOT NULL; '') ||
UPDATE 12
--autan bdexplo=>
;max_grade: '|| max_grade::text || ', ' WHERE max_grade IS NOT NULL
UPDATE 24
--autan bdexplo=>
'length: '|| length::text || ', ' WHERE length IS NOT NULL; '') ||
UPDATE 14
--autan bdexplo=>
;thickness: '|| thickness::text || ', ' WHERE thickness IS NOT NULL
UPDATE 24
--autan bdexplo=>
ULL;e_indic: '|| code_indic::text || ', ' WHERE code_indic IS NOT N
UPDATE 30
--autan bdexplo=>
'num_code: '|| num_code::text || ', ' WHERE num_code IS NOT NULL;|
UPDATE 256
--autan bdexplo=>
'srid: '|| srid::text || ', ' WHERE srid IS NOT NULL;ments, '') ||
UPDATE 310
--autan bdexplo=>
'x: '|| x::text || ', ' WHERE x IS NOT NULL;lesce(comments, '') ||
UPDATE 281
--autan bdexplo=>
'y: '|| y::text || ', ' WHERE y IS NOT NULL;lesce(comments, '') ||
UPDATE 281
--autan bdexplo=>
'z: '|| z::text || ', ' WHERE z IS NOT NULL;lesce(comments, '') ||
UPDATE 21
--autan bdexplo=>
RE comments IS NOT NULL;s SET comments = '[' || comments || ']' WHE
UPDATE 360
--autan bdexplo=>
}}}
}}}
=> second essai:{{{
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'geol_poten: "'|| geol_poten::text || '", ' WHERE geol_poten IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'grade: "'|| grade::text || '", ' WHERE grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'type: "'|| type::text || '", ' WHERE type IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_typ: "'|| code_typ::text || '", ' WHERE code_typ IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'size: "'|| size::text || '", ' WHERE size IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'au: "'|| au::text || '", ' WHERE au IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'trenches: "'|| trenches::text || '", ' WHERE trenches IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'coredrill: "'|| coredrill::text || '", ' WHERE coredrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'pdrill: "'|| pdrill::text || '", ' WHERE pdrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'max_grade: "'|| max_grade::text || '", ' WHERE max_grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'length: "'|| length::text || '", ' WHERE length IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'thickness: "'|| thickness::text || '", ' WHERE thickness IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_indic: "'|| code_indic::text || '", ' WHERE code_indic IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'num_code: "'|| num_code::text || '", ' WHERE num_code IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'srid: "'|| srid::text || '", ' WHERE srid IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'x: "'|| x::text || '", ' WHERE x IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'y: "'|| y::text || '", ' WHERE y IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'z: "'|| z::text || '", ' WHERE z IS NOT NULL;
>> print "UPDATE public.occurrences SET comments = '[' || comments || ']' WHERE comments IS NOT NULL;"
UPDATE public.occurrences SET comments = '[' || comments || ']' WHERE comments IS NOT NULL;
}}}
=> troisième essai:{{{
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'geol_poten: "'|| geol_poten::text || '", ' WHERE geol_poten IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'grade: "'|| grade::text || '", ' WHERE grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'type: "'|| type::text || '", ' WHERE type IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_typ: "'|| code_typ::text || '", ' WHERE code_typ IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'size: "'|| size::text || '", ' WHERE size IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'au: "'|| au::text || '", ' WHERE au IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'trenches: "'|| trenches::text || '", ' WHERE trenches IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'coredrill: "'|| coredrill::text || '", ' WHERE coredrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'pdrill: "'|| pdrill::text || '", ' WHERE pdrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'max_grade: "'|| max_grade::text || '", ' WHERE max_grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'length: "'|| length::text || '", ' WHERE length IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'thickness: "'|| thickness::text || '", ' WHERE thickness IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_indic: "'|| code_indic::text || '", ' WHERE code_indic IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'num_code: "'|| num_code::text || '", ' WHERE num_code IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'srid: "'|| srid::text || '", ' WHERE srid IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'x: "'|| x::text || '", ' WHERE x IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'y: "'|| y::text || '", ' WHERE y IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'z: "'|| z::text || '", ' WHERE z IS NOT NULL;
UPDATE public.occurrences SET comments = '[' || substr(comments, 1, (length(comments) - 2)) || ']' WHERE comments IS NOT NULL;
=> {{{
--autan bdexplo=>
NULL;poten: "'|| geol_poten::text || '", ' WHERE geol_poten IS NOT
UPDATE 78
--autan bdexplo=>
'grade: "'|| grade::text || '", ' WHERE grade IS NOT NULL;, '') ||
UPDATE 29
--autan bdexplo=>
'type: "'|| type::text || '", ' WHERE type IS NOT NULL;nts, '') ||
UPDATE 201
--autan bdexplo=> ;
UPDATE 0p: "'|| code_typ::text || '", ' WHERE code_typ IS NOT NULL;
--autan bdexplo=>
'size: "'|| size::text || '", ' WHERE size IS NOT NULL;nts, '') ||
UPDATE 263
--autan bdexplo=>
'au: "'|| au::text || '", ' WHERE au IS NOT NULL;(comments, '') ||
UPDATE 256
--autan bdexplo=> ;
UPDATE 14: "'|| trenches::text || '", ' WHERE trenches IS NOT NULL;
--autan bdexplo=>
LL;redrill: "'|| coredrill::text || '", ' WHERE coredrill IS NOT NU
UPDATE 52
--autan bdexplo=>
'pdrill: "'|| pdrill::text || '", ' WHERE pdrill IS NOT NULL;') ||
UPDATE 12
--autan bdexplo=>
LL;x_grade: "'|| max_grade::text || '", ' WHERE max_grade IS NOT NU
UPDATE 24
--autan bdexplo=>
'length: "'|| length::text || '", ' WHERE length IS NOT NULL;') ||
UPDATE 14
--autan bdexplo=>
LL;ickness: "'|| thickness::text || '", ' WHERE thickness IS NOT NU
UPDATE 24
--autan bdexplo=>
NULL;indic: "'|| code_indic::text || '", ' WHERE code_indic IS NOT
UPDATE 30
--autan bdexplo=> ;
UPDATE 256 "'|| num_code::text || '", ' WHERE num_code IS NOT NULL;
--autan bdexplo=>
'srid: "'|| srid::text || '", ' WHERE srid IS NOT NULL;nts, '') ||
UPDATE 310
--autan bdexplo=>
'x: "'|| x::text || '", ' WHERE x IS NOT NULL;sce(comments, '') ||
UPDATE 281
--autan bdexplo=>
'y: "'|| y::text || '", ' WHERE y IS NOT NULL;sce(comments, '') ||
UPDATE 281
--autan bdexplo=>
'z: "'|| z::text || '", ' WHERE z IS NOT NULL;sce(comments, '') ||
UPDATE 21
--autan bdexplo=>
(length(comments) - 2)) || ']' WHERE comments IS NOT NULL;ents, 1,
UPDATE 360
--autan bdexplo=>
}}}
}}}
=> quatrième essai:{{{
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'geol_poten: "'|| geol_poten::text || '" ' WHERE geol_poten IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'grade: "'|| grade::text || '" ' WHERE grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'type: "'|| type::text || '" ' WHERE type IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_typ: "'|| code_typ::text || '" ' WHERE code_typ IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'size: "'|| size::text || '" ' WHERE size IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'au: "'|| au::text || '" ' WHERE au IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'trenches: "'|| trenches::text || '" ' WHERE trenches IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'coredrill: "'|| coredrill::text || '" ' WHERE coredrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'pdrill: "'|| pdrill::text || '" ' WHERE pdrill IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'max_grade: "'|| max_grade::text || '" ' WHERE max_grade IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'length: "'|| length::text || '" ' WHERE length IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'thickness: "'|| thickness::text || '" ' WHERE thickness IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_indic: "'|| code_indic::text || '" ' WHERE code_indic IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'num_code: "'|| num_code::text || '" ' WHERE num_code IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'srid: "'|| srid::text || '" ' WHERE srid IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'x: "'|| x::text || '" ' WHERE x IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'y: "'|| y::text || '" ' WHERE y IS NOT NULL;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'z: "'|| z::text || '" ' WHERE z IS NOT NULL;
UPDATE public.occurrences SET comments = '[' || substr(comments, 1, (length(comments) - 1)) || ']' WHERE comments IS NOT NULL;
}}}
}}}
2013_09_25__09_02_12: je fais ça sur bdexplo_smi:{{{
# pierre@autan: ~/smi/transferts/to/marie_cecile < 2013_09_25__09_02_12 >
vi ~/smi/transferts/to/marie_cecile/a_faire_faire.sql
{{{
ALTER TABLE public.occurrences ADD COLUMN comments varchar;
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'geol_poten: "'|| geol_poten::text || '" ' WHERE geol_poten IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'grade: "'|| grade::text || '" ' WHERE grade IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'type: "'|| type::text || '" ' WHERE type IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_typ: "'|| code_typ::text || '" ' WHERE code_typ IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'size: "'|| size::text || '" ' WHERE size IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'au: "'|| au::text || '" ' WHERE au IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'trenches: "'|| trenches::text || '" ' WHERE trenches IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'coredrill: "'|| coredrill::text || '" ' WHERE coredrill IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'pdrill: "'|| pdrill::text || '" ' WHERE pdrill IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'max_grade: "'|| max_grade::text || '" ' WHERE max_grade IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'length: "'|| length::text || '" ' WHERE length IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'thickness: "'|| thickness::text || '" ' WHERE thickness IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'code_indic: "'|| code_indic::text || '" ' WHERE code_indic IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'num_code: "'|| num_code::text || '" ' WHERE num_code IS NOT NULL;
--UPDATE 0
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'srid: "'|| srid::text || '" ' WHERE srid IS NOT NULL;
--UPDATE 21
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'x: "'|| x::text || '" ' WHERE x IS NOT NULL;
--UPDATE 21
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'y: "'|| y::text || '" ' WHERE y IS NOT NULL;
--UPDATE 21
UPDATE public.occurrences SET comments = coalesce(comments, '') || 'z: "'|| z::text || '" ' WHERE z IS NOT NULL;
--UPDATE 21
UPDATE public.occurrences SET comments = '[' || substr(comments, 1, (length(comments) - 1)) || ']' WHERE comments IS NOT NULL;
--UPDATE 21
}}}
}}}
Je droppe les dépendances:
\d+ pierre.occurrences
Définition de la vue :
SELECT occurrences.numauto, occurrences.name, occurrences.status, occurrences.x, occurrences.y, occurrences.description, occurrences.w_done, occurrences.w_todo, occurren
FROM public.occurrences
JOIN operation_active ON occurrences.opid = operation_active.opid;
2013_09_17__11_22_21 => repris, le 2013_10_01__11_11_03
DROP VIEW pierre.occurrences;
Auquai, je droppe les champs:{{{
rebol ;{{{
foreach c champsalanoix [
print rejoin [{ALTER TABLE public.occurrences DROP COLUMN } to-string c {;}]
]
=> {{{
ALTER TABLE public.occurrences DROP COLUMN geol_poten;
ALTER TABLE public.occurrences DROP COLUMN grade;
ALTER TABLE public.occurrences DROP COLUMN type;
ALTER TABLE public.occurrences DROP COLUMN code_typ;
ALTER TABLE public.occurrences DROP COLUMN size;
ALTER TABLE public.occurrences DROP COLUMN au;
ALTER TABLE public.occurrences DROP COLUMN trenches;
ALTER TABLE public.occurrences DROP COLUMN coredrill;
ALTER TABLE public.occurrences DROP COLUMN pdrill;
ALTER TABLE public.occurrences DROP COLUMN max_grade;
ALTER TABLE public.occurrences DROP COLUMN length;
ALTER TABLE public.occurrences DROP COLUMN thickness;
ALTER TABLE public.occurrences DROP COLUMN code_indic;
ALTER TABLE public.occurrences DROP COLUMN num_code;
ALTER TABLE public.occurrences DROP COLUMN srid;
ALTER TABLE public.occurrences DROP COLUMN x;
ALTER TABLE public.occurrences DROP COLUMN y;
ALTER TABLE public.occurrences DROP COLUMN z;
}}}
print "UPDATE public.occurrences SET comments = '[' || substr(comments, 1, (length(comments) - 2)) || ']' WHERE comments IS NOT NULL;"
;}}}
--=>{{{
ALTER TABLE public.occurrences DROP COLUMN geol_poten;
ALTER TABLE public.occurrences DROP COLUMN grade;
ALTER TABLE public.occurrences DROP COLUMN type;
ALTER TABLE public.occurrences DROP COLUMN code_typ;
ALTER TABLE public.occurrences DROP COLUMN size;
ALTER TABLE public.occurrences DROP COLUMN au;
ALTER TABLE public.occurrences DROP COLUMN trenches;
ALTER TABLE public.occurrences DROP COLUMN coredrill;
ALTER TABLE public.occurrences DROP COLUMN pdrill;
ALTER TABLE public.occurrences DROP COLUMN max_grade;
ALTER TABLE public.occurrences DROP COLUMN length;
ALTER TABLE public.occurrences DROP COLUMN thickness;
ALTER TABLE public.occurrences DROP COLUMN code_indic;
ALTER TABLE public.occurrences DROP COLUMN num_code;
ALTER TABLE public.occurrences DROP COLUMN srid;
ALTER TABLE public.occurrences DROP COLUMN x;
ALTER TABLE public.occurrences DROP COLUMN y;
ALTER TABLE public.occurrences DROP COLUMN z;
2013_09_25__09_02_12: => je fais ça sur bdexplo_smi: {{{
BEGIN
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN geol_poten;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN grade;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN type;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN code_typ;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN size;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN au;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN trenches;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN coredrill;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN pdrill;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN max_grade;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN length;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN thickness;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN code_indic;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN num_code;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN srid;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN x;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN y;
ALTER TABLE
--autan bdexplo_smi=#
ALTER TABLE public.occurrences DROP COLUMN z;
ALTER TABLE
}}}
--}}}
}}}
Et je refais les objets dépendants:
CREATE VIEW pierre.occurrences AS SELECT occurrences.* FROM public.occurrences JOIN operation_active ON occurrences.opid = operation_active.opid;
}}}
}}}
}}}
public | mag_declination | table | pierre | 16 kB | Magnetic declination{{{ Table "public.mag_declination"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+-------------------------------------------------------------------+----------+----------------------------------------------------------
opid | integer | | plain | Operation identifier
mag_decl | numeric | | main |
numauto | integer | not null default nextval('mag_declination_numauto_seq'::regclass) | plain | Automatic integer primary key
date | date | | plain |
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
Indexes:
"mag_declination_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
_______________ENCOURS_______________ ^
public | dh_collars | table | pierre | 2368 kB | Drill holes collars or trenches starting points {{{
Table "public.dh_collars"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+--------------------------------------------------------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | character varying(20) | not null | extended | Full identifier for borehole or trench, may include including zone code, type and sequential number
shid | character varying(10) | | extended | Short identifier: type _ sequential number
location | character varying | | extended | Investigated area code, refers to occurrences table
profile | character varying(10) | | extended | Profile number
srid | integer | | plain | Spatial Reference Identifier, or coordinate reference system: see spatial_ref_sys from postgis extension
x | numeric(12,3) | | main | X coordinate (Easting), in coordinate system srid
y | numeric(12,3) | | main | Y coordinate (Northing), in coordinate system srid
z | numeric(12,3) | | main | Z coordinate
azim_ng | numeric(10,2) | | main | Hole or trench azimuth (°) relative to geographic North
azim_nm | numeric(10,2) | | main | Hole or trench azimuth (°) relative to Magnetic North
dip_hz | numeric(10,2) | | main | Drill hole or trench dip relative to horizontal (°)
dh_type | character varying(10) | | extended | Type: D for Diamond drill hole, R for RC drill hole, T for Trench, A for Auger drill hole
date_start | date | | plain | Work start date
contractor | character varying(20) | | extended | Drilling contractor
geologist | character varying | | extended | Geologist name
length | numeric(10,2) | | main | Total length (m)
nb_samples | integer | | plain | Number of samples
comments | character varying | | extended | Comments
completed | boolean | default false | plain | True: completed; False: planned
datasource | character varying | | extended | Datasource identifier, refers to lex_datasource
numauto | integer | not null default nextval('dh_collars_numauto_seq'::regclass) | plain | Automatic integer primary key
date_completed | date | | plain | Work finish date
opid | integer | not null | plain | Operation identifier
purpose | character varying | default 'EXPLO'::character varying | extended | Purpose of hole: exploration, delineation, estimation, grade control, etc.
x_local | numeric(12,3) | | main | Local x coordinate
y_local | numeric(12,3) | | main | Local y coordinate
z_local | numeric(12,3) | | main | Local z coordinate
accusum | numeric(10,2) | | main | Accumulation sum over various mineralised intervals intersected by drill hole or trench (purpose: quick visualisation on maps (at wide scale ONLY), quick ranking of interesting holes)
id_pject | character varying | | extended | PJ for ProJect identifier: provisional identifier; aka peg number
x_pject | numeric(10,3) | | main | Planned x coordinate
y_pject | numeric(10,3) | | main | Planned y coordinate
z_pject | numeric(10,3) | | main | Planned z coordinate
topo_survey_type | character varying | | extended | Topographic collar survey type: GPS, GPSD, geometry, theodolite, relative, computed from local coordinate system, etc.
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"opid_id" PRIMARY KEY, btree (opid, id)
"dh_collars_numauto_key" UNIQUE CONSTRAINT, btree (numauto)
"dh_collars_id" btree (id)
Foreign-key constraints:
"dh_collars_opid_fkey" FOREIGN KEY (opid) REFERENCES operations(opid)
Referenced by:
TABLE "public.dh_devia" CONSTRAINT "dh_devia_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
TABLE "public.dh_litho" CONSTRAINT "dh_litho_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
TABLE "public.dh_sampling_grades" CONSTRAINT "dh_sampling_grades_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
TABLE "public.shift_reports" CONSTRAINT "shift_reports_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
Has OIDs: no
}}}
public | dh_devia | table | pierre | 2568 kB | Drill holes or trenches deviations measurements {{{
Table "public.dh_devia"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------------------------------------
id | character varying(20) | | extended | Drill hole identification related to the collars table
depto | numeric(10,2) | | main | Depth of deviation measurement
azim_nm | numeric(10,2) | | main | Hole azimuth (°) relative to magnetic North (?)
dip_hz | numeric(10,2) | | main | Drill hole dip relative to horizontal (°), positive down
temperature | numeric(10,2) | | main | temperature
magnetic | numeric(10,2) | | main | Magnetic field intensity measurement
date | date | | plain | Date of deviation measurement
roll | numeric(10,2) | | main | Roll angle
time | integer | | plain | Time of deviation measurement
comments | character varying | | extended | Various comments; concerning measurements done with Reflex Gyro, all parameters are concatened as a json-like structure
opid | integer | | plain | Operation identifier
numauto | integer | not null default nextval('dh_devia_numauto_seq'::regclass) | plain | Automatic integer primary key
valid | boolean | default true | plain | True when a deviation measurement is usable; queries should take into account only valid records
azim_ng | numeric(10,2) | | main | Hole azimuth (°) relative to geographic North
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
device | character varying | | extended | Device used for deviation measurement
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_devia_pkey" PRIMARY KEY, btree (numauto)
Foreign-key constraints:
"dh_devia_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
Has OIDs: no
}}}
public | dh_tech | table | pierre | 5056 kB | Technical drilling data, and geotechnical parameters{{{ Table "public.dh_tech"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+-----------------------------------------------------------+----------+----------------------------------------------------------
id | character varying(20) | | extended | Drill hole identification
depfrom | numeric(10,2) | | main | Interval begining depth
depto | numeric(10,2) | | main | Interval ending depth
drilled_len | numeric(10,2) | | main | Interval length
reco_len | numeric(10,2) | | main | Recovery length
rqd_len | numeric(10,2) | | main | Rock Quality Designation "length"
diam | character varying(10) | | extended | core diameter
numauto | integer | not null default nextval('dh_tech_numauto_seq'::regclass) | plain | Automatic integer primary key
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
opid | integer | | plain | Operation identifier
comments | character varying | | extended |
drillers_depto | numeric(10,2) | | main |
core_loss_cm | integer | | plain |
joints_description | character varying | | extended |
nb_joints | integer | | plain |
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_tech_pkey" PRIMARY KEY, btree (numauto)
"id_depto_dh_tech" btree (id, depto)
Has OIDs: no
}}}
public | dh_sampling_grades | table | pierre | 36 MB | Samples along drill holes and trenches, with grades{{{ Table "public.dh_sampling_grades"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+----------------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------
id | character varying(20) | | extended |
depfrom | numeric(10,2) | | main |
depto | numeric(10,2) | | main |
core_loss_cm | numeric(5,1) | | main |
weight_kg | numeric(6,2) | | main |
sample_type | character varying(8) | | extended |
sample_id | character varying(20) | | extended |
comments | character varying | | extended |
opid | integer | | plain | Operation identifier
batch_id | integer | | plain |
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
au1_ppm | numeric(8,3) | | main |
au2_ppm | numeric(8,3) | | main |
au3_ppm | numeric(8,3) | | main |
au4_ppm | numeric(8,3) | | main |
au5_ppm | numeric(8,3) | | main |
au6_ppm | numeric(8,3) | | main |
ph | numeric(4,2) | | main |
moisture | numeric(8,4) | | main |
numauto | integer | not null default nextval('dh_sampling_grades_numauto_seq'::regclass) | plain | Automatic integer primary key
au_specks | integer | | plain | Number of gold specks seen in drill hole or trench; typically, after panning destructive drilling chips, also gold specks seen in core drilling
quartering | integer | | plain |
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_sampling_grades_pkey" PRIMARY KEY, btree (numauto)
Foreign-key constraints:
"dh_sampling_grades_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
Has OIDs: no
}}}
public | dh_mineralised_intervals | table | pierre | 1736 kB | Drill holes mineralised intercepts: stretch values over mineralised intervals, along drill holes or trenches {{{
Table "public.dh_mineralised_intervals"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+-----------------------------------------------------------+----------+--------------------------------------------------------------------
id | character varying(20) | | extended | Full identifier for borehole or trench
depfrom | numeric(10,2) | | main | Mineralised interval starting depth
depto | numeric(10,2) | | main | Mineralised interval ending depth
mine | integer | default 1 | plain | Take-out interval class: 1=normal interval, 2=high-grade interval
avau | numeric(10,2) | | main | Average grade (g/t)
stva | character varying(150) | | extended | Stretch value, X m at Y g/t
accu | numeric(10,2) | | main | Accumulation in m.g/t over mineralised interval
recu | numeric(10,2) | | main | recovery
dens | numeric(10,2) | | main | density
datasource | character varying(500) | | extended | Datasource identifier, refers to lex_datasource
numauto | integer | not null default nextval('dh_mine_numauto_seq'::regclass) | plain | Automatic integer primary key
comments | character varying(100) | | extended |
opid | integer | | plain | Operation identifier
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_mine_pkey" PRIMARY KEY, btree (numauto)
"id_depto_dh_mine" btree (id, depto)
Has OIDs: no
}}}
public | dh_quicklog | table | pierre | 48 kB | Quick geological log, typically done on hole finish, for an A4 log plot {{{
Table "public.dh_quicklog"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+---------------------------------------------------------------+----------+----------------------------------------------------------
opid | integer | | plain | Operation identifier
id | text | | extended |
depfrom | numeric(10,2) | | main |
depto | numeric(10,2) | | main |
description | character varying(254) | | extended |
oxid | character varying(4) | | extended |
alt | smallint | | plain |
def | smallint | | plain |
numauto | integer | not null default nextval('dh_quicklog_numauto_seq'::regclass) | plain | Automatic integer primary key
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
Indexes:
"dh_quicklog_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
public | dh_litho | table | pierre | 20 MB | Drill holes or trenches geological descriptions {{{
Table "public.dh_litho"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------
id | character varying(20) | | extended | Identifier, refers to dh_collars
depfrom | numeric(10,2) | | main | Interval beginning depth
depto | numeric(10,2) | | main | Interval ending depth
description | character varying | | extended | Geological description, naturalist style
code1 | character varying(4) | | extended | Conventional use is lithology code, 4 characters, uppercase. Refer to lex_codes table
code2 | character varying(4) | | extended | Conventional use is supergene oxidation, 1 character, uppercase. Refer to lex_codes table
code3 | character varying(4) | | extended | Conventional use is stratigraphy code, 4 characters, uppercase. Refer to lex_codes table
code4 | character varying(4) | | extended | 4 characters code. Refer to lex_codes table
value1 | integer | | plain | Integer value. Refer to lex_codes table
value2 | integer | | plain | Integer value. Refer to lex_codes table
value3 | integer | | plain | Integer value. Refer to lex_codes table
value4 | integer | | plain | Integer value. Refer to lex_codes table
opid | integer | | plain | Operation identifier
colour | character varying | | extended |
numauto | integer | not null default nextval('dh_litho_numauto_seq'::regclass) | plain | Automatic integer primary key
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
description1 | character varying | | extended | Complement to main geological description: metallic minerals
description2 | character varying | | extended | Complement to main geological description: alterations
value5 | integer | | plain | Integer value. Refer to lex_codes table
value6 | integer | | plain | Integer value. Refer to lex_codes table
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_litho_pkey" PRIMARY KEY, btree (numauto)
"dh_litho_id_depto" btree (id, depto)
Foreign-key constraints:
"dh_litho_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
Has OIDs: no
}}}
public | dh_density | table | pierre | 1504 kB | Density measurements along drill holes or trenches {{{
Table "public.dh_density"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+--------------------------------------------------------------+----------+--------------------------------------------------------------------------------------------------------------------
id | character varying(20) | | extended | Identifier, refers to dh_collars
depfrom | numeric(10,2) | | main | Interval beginning depth: if not empty, density measured along an interval; otherwise, density measured on a point
depto | numeric(10,2) | | main | Interval ending depth: if depfrom is empty, depth of poncutal density measurement
density | numeric(10,2) | | main | Density, unitless, or considered as kg/l, or t/m3
opid | integer | | plain | Operation identifier
density_humid | numeric | | main | Density, unitless, or considered as kg/l, or t/m3, determined on humid sample
moisture | numeric | | main | Moisture contents
method | character varying | | extended | Procedure used to determine specific gravity
numauto | integer | not null default nextval('dh_density_numauto_seq'::regclass) | plain | Automatic integer primary key
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
Indexes:
"dh_density_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
public | dh_struct_measures | table | pierre | 352 kB | Structural measurements done on core, or in trenches{{{ Table "public.dh_struct_measures"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+----------------------------------------------------------------------+----------+----------------------------------------------------------
opid | integer | | plain | Operation identifier
id | character varying(20) | | extended |
depto | numeric(10,2) | | main |
measure_type | character varying | | extended |
structure_type | character varying | | extended |
alpha_tca | numeric | | main |
beta | numeric | | main |
gamma | numeric | | main |
north_ref | character varying | | extended |
direction | integer | | plain |
dip | integer | | plain |
dip_quadrant | character varying | | extended |
pitch | integer | | plain |
pitch_quadrant | character varying | | extended |
movement | character varying | | extended |
valid | boolean | | plain |
struct_description | character varying | | extended |
sortgroup | character(1) | | extended |
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
numauto | integer | not null default nextval('dh_struct_measures_numauto_seq'::regclass) | plain | Automatic integer primary key
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_struct_measures_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
public | dh_core_boxes | table | pierre | 112 kB | Core drill holes boxes {{{
Table "public.dh_core_boxes"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+-----------------------------------------------------------------+----------+----------------------------------------------------------
id | character varying(20) | | extended | Identifier, refers to dh_collars
depfrom | numeric(10,2) | | main | Core box contents beginning depth
depto | numeric(10,2) | | main | Core box contents ending depth
box_number | integer | | plain | Core box number
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
opid | integer | | plain | Operation identifier
numauto | integer | not null default nextval('dh_core_boxes_numauto_seq'::regclass) | plain | Automatic integer primary key
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_core_boxes_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
public | dh_sampling_bottle_roll | table | pierre | 24 kB | Mineralurgical samples, bottle-roll tests results{{{ Table "public.dh_sampling_bottle_roll"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+---------------------------------------------------------------------------+----------+----------------------------------------------------------
opid | integer | | plain | Operation identifier
id | text | | extended |
depfrom | numeric(10,2) | | main |
depto | numeric(10,2) | | main |
sample_id | character varying | | extended |
au_total | numeric(10,2) | | main |
au_24h | numeric(10,2) | | main |
au_48h | numeric(10,2) | | main |
au_72h | numeric(10,2) | | main |
au_residu | numeric(10,2) | | main |
rec_24h_pc | numeric(10,2) | | main |
rec_48h_pc | numeric(10,2) | | main |
rec_72h_pc | numeric(10,2) | | main |
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
numauto | integer | not null default nextval('dh_sampling_bottle_roll_numauto_seq'::regclass) | plain | Automatic integer primary key
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"dh_sampling_bottle_roll_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
public | dh_thinsections | table | pierre | 88 kB | Thin sections for petrological studies{{{ Table "public.dh_thinsections"
Column | Type | Modifiers | Storage | Description
---------------------------+-----------------------------+-------------------------------------------------------------------+----------+----------------------------------------------------------
opid | integer | | plain | Operation identifier
id | character varying | | extended |
depto | numeric(10,2) | | main |
core_quarter | character varying | | extended |
questions | character varying | | extended |
name | character varying | | extended |
texture | character varying | | extended |
mineralogy | character varying | | extended |
metamorphism_deformations | character varying | | extended |
mineralisations | character varying | | extended |
origin | character varying | | extended |
numauto | integer | not null default nextval('dh_thinsections_numauto_seq'::regclass) | plain | Automatic integer primary key
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
Indexes:
"dh_thinsections_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
public | dh_followup | table | pierre | 16 kB | Simple table for daily drill holes followup {{{
Table "public.dh_followup"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+---------------------------------------------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------
opid | integer | | plain | Operation identifier
id | character varying | | extended | Identifier, refers to dh_collars
devia | character varying(3) | | extended | Deviation survey (x: done; xx: done, data entered; xxx: data verified)
quick_log | character varying(3) | | extended | Quick geological log, typically done on hole finish, for an A4 log plot (x: done; xx: done, data entered; xxx: data verified)
log_tech | character varying(3) | | extended | Core fitting, core measurement, meters marking, RQD, fracture counts, etc. (x: done; xx: done, data entered; xxx: data verified)
log_lith | character varying(3) | | extended | Full geological log (x: done; xx: done, data entered; xxx: data verified)
sampling | character varying(3) | | extended | Hole sampling (x: done; xx: done, data entered; xxx: data verified)
results | character varying(3) | | extended | Assay results back from laboratory (x: received; xx: entered; xxx: verified)
relogging | character varying(3) | | extended | Geological log done afterwards on mineralised intervals (x: done; xx: done, data entered; xxx: data verified)
beacon | character varying(3) | | extended | Beacon or any other permanent hole marker on field (PVC pipe, concrete beacon, cement, etc.) (x: done)
in_gdm | character varying(1) | | extended | Data exported to GDM; implicitely: data clean, checked by GDM procedures (x: done)
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
numauto | integer | not null default nextval('dh_followup_numauto_seq'::regclass) | plain | Automatic integer primary key
Indexes:
"dh_followup_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
}}}
public | shift_reports | table | pierre | 440 kB | Daily reports from rigsites: one report per shift/machine/tool{{{ Table "public.shift_reports"
Column | Type | Modifiers | Storage | Description
-----------------------------+-----------------------------+-----------------------------------------------------------------+----------+----------------------------------------------------------------------------------------------------------------------------------------
opid | integer | not null | plain | Operation identifier
date | date | | plain | Date of drilling
shift | character varying(1) | | extended | Day or night shift
no_fichette | integer | not null | plain | Number of fichette = field form filled on a shift and borehole basis
rig | character varying(50) | | extended | Name/id of drilling (or digging) machine
geologist | character varying(50) | | extended | Geologist(s) following the drill hole on the rig site, doing the logging.
time_start | time without time zone | | plain | Drilling starting time
time_end | time without time zone | | plain | Drilling ending time
id | character varying(20) | | extended | Drill hole identifier, must match collars.id field, e.g. UMA_R086
peg_number | character varying(5) | | extended | Peg number: provisional identifier/number; aka PJ for ProJect identifier
planned_length | numeric(10,2) | | main | Length of the borehole, as initially planned
tool | character varying(20) | | extended | Drilling (digging) tool/size, diameter: RC, RAB, percussion, core, SQ, PQ, HQ, NQ, BQ, AQ, mechanical shovel, hand shovel, banka, etc.
drilled_length_during_shift | numeric(10,2) | | main | Length of borehole drilled during the shift
drilled_length | numeric(10,2) | | main | Total length of the borehole drilled at the end of the shift
completed | boolean | | plain | Borehole finished or not
profile | character varying(10) | | extended | Section identifier
comments | character varying(254) | | extended | Comments on drilling (events, presence of water, difficulties, major facies, etc.)
invoice_nr | integer | | plain | Subcontractor invoice number
drilled_shift_destr | numeric | | main |
drilled_shift_pq | numeric | | main |
drilled_shift_hq | numeric | | main |
drilled_shift_nq | numeric | | main |
recovered_length_shift | numeric | | main |
stdby_time1_h | numeric | | main | standby time AFM
stdby_time2_h | numeric | | main | standby time SFM
stdby_time3_h | numeric | | main | intempéries
moving_time_h | numeric | | main | moving
driller_name | character varying | | extended |
geologist_supervisor | character varying | | extended |
db_update_timestamp | timestamp without time zone | default now() | plain | Current date and time stamp when data is loaded in table
username | character varying | default "current_user"() | extended | User (role) which created data record
numauto | integer | not null default nextval('shift_reports_numauto_seq'::regclass) | plain | Automatic integer
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
Indexes:
"fichette_pkey" PRIMARY KEY, btree (opid, no_fichette)
Foreign-key constraints:
"shift_reports_opid_fkey" FOREIGN KEY (opid, id) REFERENCES public.dh_collars(opid, id)
Has OIDs: no
}}}
public | topo_points | table | pierre | 1864 kB | topographical data {{{ Table "public.topo_points"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+---------------------------------------------------------------+----------+---------------------------------------------------------------------------------------------
location | character varying(20) | | extended | Topographical zone
num | numeric(10,0) | | main | Topographical point number
x | numeric(10,3) | | main | X coordinate, projected in UTM (m)
y | numeric(10,3) | | main | Y coordinate, projected in UTM (m)
z | numeric(10,3) | | main | Z coordinate, projected in UTM (m)
numauto | integer | not null default nextval('topo_points_numauto_seq'::regclass) | plain | Automatic integer primary key
id | character varying(20) | | extended | Full identifier for borehole or trench, including zone code with type and sequential number
datasource | integer | | plain | Datasource identifier, refers to lex_datasource
opid | integer | | plain | Operation identifier
survey_date | date | | plain |
topo_survey_type | character varying | | extended |
coordsys | character varying | | extended |
surveyor | character varying | | extended |
db_update_timestamp | timestamp without time zone | default now() | plain |
username | character varying | default "current_user"() | extended | User (role) which created data record
Indexes:
"topo_points_pkey" PRIMARY KEY, btree (numauto)
Has OIDs: no
2013_09_12__21_26_44: corrections: {{{
kk: issu de sélection dans qgis:{{{
UPDATE public.topo_points SET
x = x + 700000, y = y + 2000000
WHERE numauto IN (
16409
16410
16411
16412
16413
16414
16415
16416
16417
16418
16419
16420
16421
16422
16423
16424
16425
16426
16427
16428
16429
16430
16431
16432
16433
16434
16435
16436
16437
16438
16439
16440
16441
16442
16443
16444
16445
16446
16447
16448
16449
16450
16451
16452
16453
16454