source: trunk/DataCheck/Tools/get_data.sh@ 19032

Last change on this file since 19032 was 19032, checked in by Daniela Dorner, 7 years ago
added readme
  • Property svn:executable set to *
File size: 17.8 KB
Line 
1#!/bin/bash
2
3# ----------------------------------------------------------------
4# README README README README README README README README README
5# ----------------------------------------------------------------
6# #
7# To use this script, you need #
8# - a computer with access to the FACT database in La Palma #
9# - a file with the password of a valid mysql-user #
10# - to define the setup below for #
11# a) the DB access #
12# b) the data you want to have #
13# #
14# To define the setup, search for SETUP in this script and #
15# read the details there (starting roughly line 295) #
16# #
17# Per data request, you get up to 3 files: #
18# *_internal.dat #
19# *_collaborators.dat #
20# *_external.dat (only if binning is 20min or nightly) #
21# #
22# Please have in mind that this started as a tool for myself, #
23# then others started using it. Also the script is not yet #
24# finalized. In case you find problems and/or have a feature #
25# request, please send and email to dorner@astro.uni-wuerzburg.de #
26# #
27# ----------------------------------------------------------------
28# README README README README README README README README README
29# ----------------------------------------------------------------
30
31
32
33
34
35# ToDo (notes DD):
36# ----------------
37# - add file for collaborators
38# - update columns and content for 3 types of files
39# - limit creation of file for externals to
40# - update function for correction
41# - update CU for QLA
42# - add CU for ISDC analysis
43# - add zd, th for internal
44# - add < 20121212 data for QLA
45# - check crab flux
46# - add E2dNdE?
47# - functionality to determine start time for seaon-binning
48# - can get_data.sh / Send_Data*.sh be combined?
49# get_data.sh should be able to run stand-alone and be kept simple for any user
50
51#
52# content of files (wish list):
53# -----------------------------
54# REMARK: keep order of columns to allow for reading with TGraph directly from file: X Y EX EY
55#
56# internal
57# --------
58# time: time, delta time, start, stop, ontime
59# flux: excrate, excerr, corrate, corerr, CU CUerr, flux, fluxerr,
60# other info on flux: signif, cu-factor, num exc, num sig, num bg
61# other info: zd th R750cor R750ref
62#
63# external (allow only 20min and nightly binning)
64# --------
65# time: time, delta time, start, stop
66# flux: excrate, excerr
67#
68# collaborators
69# -------------
70# time: time, delta time, start, stop, ontime
71# flux: excrate, excerr, corrate, corerr, flux, flux-err, significance
72#
73# additional information to put:
74# ------------------------------
75# timestamp of creation
76# query (for debugging / answering questions)
77# policy (adapted for internal/collaborators/external) [define in files to be used also by Send_Data*.sh
78#
79
80
81
82function get_results()
83{
84 # some basic query parts
85
86 # data check based on artificial trigger rate
87 #dch=" AND fR750Cor/fR750Ref >0.93 "
88 dch=" AND fR750Cor/fR750Ref BETWEEN 0.93 AND 1.3 "
89 # ontime
90 ontime1=" TIME_TO_SEC(TIMEDIFF(fRunStop,fRunStart))*fEffectiveOn "
91 ontime2=" fOnTimeAfterCuts "
92 ontimeif=" IF(ISNULL(fEffectiveOn), "$ontime2", "$ontime1") "
93 from=" FROM RunInfo LEFT JOIN "$table" USING (fNight, fRunID) "
94 # time range and source
95 where=" WHERE fSourceKey="$source" AND fNight BETWEEN "$nightmin" AND "$nightmax
96 where=$where" AND NOT ISNULL(fNumExcEvts) "
97 # some sanity checks
98 where=$where" AND fRunTypeKey=1 "
99 # zd cut
100 where=$where" AND fZenithDistanceMax < "$zdmax
101 # th cut
102 where=$where" AND fThresholdMedian < "$thmax
103 where=$where" "$dch
104
105 #
106 cufactor=" Avg(CUQLA(fNight)) "
107 crabflux="3.37e-11"
108 fluxprec=13
109 crabflux="3.37"
110 fluxprec=2
111
112 case $timeunit in
113 mjd) start=" Mjd(Min(fRunStart)) "
114 stop=" Mjd(MAX(fRunStop)) "
115 deltat=" (Mjd(MAX(fRunStop))-Mjd(Min(fRunStart)))/2 "
116 time=" Mjd(Min(fRunStart))+"$deltat
117 start2=" Mjd(MIN(o.start)) "
118 stop2=" Mjd(MAX(o.stop)) "
119 deltat2=" (Mjd(MAX(o.stop))-Mjd(MIN(o.start)))/2 "
120 time2=" Mjd(MIN(o.start))+"$deltat2
121 ;;
122 unix) start="Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM')) "
123 stop="Unix_timestamp(CONVERT_TZ(Max(fRunStop), '+00:00', 'SYSTEM')) "
124 deltat=" (Unix_timestamp(CONVERT_TZ(Max(fRunStop), '+00:00', 'SYSTEM')) - Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM')))/2 "
125 time=" Unix_timestamp(CONVERT_TZ(Min(fRunStart), '+00:00', 'SYSTEM'))+"$deltat
126 startstop2=" Unix_timestamp(CONVERT_TZ(MIN(o.start), '+00:00', 'SYSTEM')) AS start, "
127 startstop2=$starstop2" Unix_timestamp(CONVERT_TZ(MAX(o.stop), '+00:00', 'SYSTEM')) AS stop, "
128 time2=" (Unix_timestamp(CONVERT_TZ(Max(o.stop), '+00:00', 'SYSTEM')) - Unix_timestamp(CONVERT_TZ(Min(o.start), '+00:00', 'SYSTEM')))/2 "
129 time2=" Unix_timestamp(CONVERT_TZ(Min(o.start), '+00:00', 'SYSTEM'))+"$deltat2
130 ;;
131 *) start=" MIN(fRunStart) "
132 stop=" MAX(fRunStop) "
133 deltat=" sec_to_time(time_to_sec(timediff(MAX(fRunStop), Min(fRunStart)))/2) "
134 time=" addtime(Min(fRunStart), "$deltat") "
135 start2=" MIN(o.start) "
136 stop2=" MAX(o.stop) "
137 deltat2=" sec_to_time(time_to_sec(timediff(MAX(o.stop), Min(o.start)))/2) "
138 time2=" addtime(Min(o.start), "$deltat2") "
139 ;;
140 esac
141 ontime=" SUM("$ontimeif")/60."
142 ontime2=" SUM(o.ot)/60. "
143
144 excrate=" SUM(fNumExcEvts)/SUM("$ontimeif")*3600 "
145 excerr="ExcErr(Sum(fNumSigEvts), SUM(fNumBgEvts))"
146 significance="LiMa(Sum(fNumSigEvts), SUM(fNumBgEvts))"
147 numexc="Sum(fNumExcEvts)"
148 numsig="Sum(fNumSigEvts)"
149 numbg="Sum(fNumBgEvts)"
150 excrateerr=" "$excerr"/SUM("$ontimeif")*3600 "
151 # thomas correction factor
152 correvts=" fNumExcEvts*(pow(cos(fZenithDistanceMean*PI()/180),3)+14.8/21.9*pow(sin(2*fZenithDistanceMean*PI()/180),5))/((1-0.00124/1.21*(if(isnull(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)-500)*(if(isnull(fThresholdMinSet),fThresholdMedian,fThresholdMinSet)>=500))) "
153 correxcrate=" SUM("$correvts")/SUM("$ontimeif")*3600 "
154 # corerr = MMath::ErrorExc(excevtssum+bgevtssum, bgevtssum*5, 0.2)/ontimesum*3600.*corrate/excrate;
155 correxcrateerr=" "$excerr"/SUM("$ontimeif")*3600*SUM("$correvts")/SUM(fNumExcEvts) "
156 # correction on run basis (not relevant for hess)
157 #cu=$correxcrate"/"$cufactor
158 cu=" SUM("$correvts"/CUQLA(fNight))/SUM("$ontimeif")*3600 "
159 #cuerr=$correxcrateerr"/"$cufactor
160 cuerr=" "$excerr"/SUM("$ontimeif")*3600*SUM("$correvts"/CUQLA(fNight))/SUM(fNumExcEvts) "
161 flux=$cu" * "$crabflux
162 fluxerr=$cuerr" * "$crabflux
163
164
165 excrate2=" (SUM(o.sigevts)-SUM(o.bgevts))/SUM(o.ot)*3600 "
166 excerr2="ExcErr(SUM(o.sigevts),SUM(o.bgevts))"
167 significance2="LiMa(SUM(o.sigevts),SUM(o.bgevts))"
168 numexc2="Sum(o.sigevts-o.bgevts)"
169 numsig2="Sum(o.sigevts)"
170 numbg2="Sum(o.bgevts)"
171 excrateerr2=" "$excerr2"/SUM(o.ot)*3600 "
172 correxcrate2=" SUM(o.corevts)/SUM(o.ot)*3600 "
173 correxcrateerr2=" "$excerr2"/SUM(o.ot)*3600*SUM(o.corevts)/(SUM(o.sigevts)-SUM(o.bgevts)) "
174 #cu2=$correxcrate2"/"$cufactor
175 cu2=" SUM(o.corevts/o.cu)/SUM(o.ot)*3600 "
176 #cuerr2=$correxcrateerr2"/"$cufactor
177 cuerr2=" "$excerr2"/SUM(o.ot)*3600*SUM(o.corevts/o.cu)/(SUM(o.sigevts)-SUM(o.bgevts)) "
178 flux2="$cu2*"$crabflux
179 fluxerr2="$cuerr2*"$crabflux
180
181
182 if [ $bin -le 0 ]
183 then
184 queryint="SELECT "
185 if [ $bin -eq 0 ]
186 then
187 queryint=$queryint" fPeriod as num, "
188 else
189 queryint=$queryint" FLOOR((Mjd(fRunStart)-Mjd("$nightmin")-0.5)/"`echo $bin | sed -e 's/-//'`".) as num, "
190 fi
191 queryint=$queryint" "$time" as time, "$start" as start, "$stop" as stop, "
192 queryint=$queryint" round("$excrate", 1) as excrate, round("$correxcrate", 1) as correxcrate, "
193 queryint=$queryint" round("$cu", 2) as cu, "$flux" as flux, "
194 queryint=$queryint" "$deltat" as deltat, round("$ontime", 1) as ontime, "
195 queryint=$queryint" round("$excrateerr", 1) as excrateerr, round("$correxcrateerr", 1) as correxcrateerr, "
196 queryint=$queryint" round("$cuerr", 2) as cuerr, "$fluxerr" as fluxerr, "
197 queryint=$queryint" round("$significance", 1) as significance, "
198 queryint=$queryint" Min(fNight) as nightmin, "
199 queryint=$queryint" Max(fNight) as nightmax, "
200 queryint=$queryint" "$numexc" as numexc, "
201 queryint=$queryint" "$numsig" as numsig, "
202 queryint=$queryint" "$numbg" as numbg "
203
204 queryext="SELECT "
205 if [ $bin -eq 0 ]
206 then
207 queryext=$queryext" fPeriod as num, "
208 else
209 queryext=$queryext" FLOOR((Mjd(fRunStart)-Mjd("$nightmin")-0.5)/"`echo $bin | sed -e 's/-//'`".) as num, "
210 fi
211 queryext=$queryext" "$time" as time, "$start" as start, "$stop" as stop, "
212 queryext=$queryext" round("$correxcrate", 1) as correxcrate, round("$flux", "$fluxprec") as flux, "
213 queryext=$queryext" "$deltat" as deltat, round("$ontime", 1) as ontime, "
214 queryext=$queryext" round("$correxcrateerr", 1) as correxcrateerr, round("$fluxerr", "$fluxprec") as fluxerr, "
215 queryext=$queryext" round("$significance", 1) as significance "
216
217 querybase=$from$where
218 querybase=$querybase" GROUP BY num "
219 if [ "$ontimelimit" = "" ]
220 then
221 querybase=$querybase" HAVING SUM("$ontimeif")>1200 ORDER BY num " # 20 min
222 else
223 querybase=$querybase" HAVING SUM("$ontimeif")>"$ontimelimit" ORDER BY num "
224 fi
225
226 queryint=$queryint" "$querybase
227 queryext=$queryext" "$querybase
228 else
229 queryint="SELECT "
230 queryint=$queryint" "$time2" as time, "$start2" as start, "$stop2" as stop, "
231 queryint=$queryint" round("$excrate2", 1) as excrate, round("$correxcrate2", 1) as correxcrate, "
232 queryint=$queryint" round("$cu2", 1) as cu, round("$flux2", "$fluxprec") as flux, "
233 queryint=$queryint" round("$excrateerr2", 1) as excrateerr, round("$correxcrateerr2", 1) as correxcrateerr, "
234 queryint=$queryint" "$deltat2" as deltat, round("$ontime2", 1) as ontime, "
235 queryint=$queryint" round("$cuerr2", 1) as cuerr, round("$fluxerr2", "$fluxprec") as fluxerr, "
236 queryint=$queryint" round("$significance2", 1) as significance, "
237 queryint=$queryint" avg(o.night) as night, "
238 queryint=$queryint" "$numexc2" as numexc, "
239 queryint=$queryint" "$numsig2" as numsig, "
240 queryint=$queryint" "$numbg2" as numbg "
241
242 queryext="SELECT "
243 queryext=$queryext" "$time2" as time, "$start2" as start, "$stop2" as stop, "
244 queryext=$queryext" round("$correxcrate2", 1) as correxcrate, round("$flux2", "$fluxprec") as flux, "
245 queryext=$queryext" "$deltat2" as deltat, round("$ontime2", 1) as ontime, "
246 queryext=$queryext" round("$correxcrateerr2", 1) as correxcrateerr, round("$fluxerr2", "$fluxprec") as fluxerr, "
247 queryext=$queryext" round("$significance2", 1) as significance "
248
249 querybase=" FROM (SELECT fNight, @ot:="$ontimeif" AS ot, fRunStart AS start, fRunStop AS stop, fNumSigEvts AS sigevts, fNumBgEvts AS bgevts, "
250 querybase=$querybase" "$correvts" AS corevts, CUQLA(fNight) AS cu, "
251 querybase=$querybase" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @bl, @bl := @bl + 1) AS block, "
252 querybase=$querybase" IF (@night=fNight AND FLOOR((@os+@ot)/"$bin"./60.)<1, @os:=@os + @ot, @os := @ot) AS os, @night :=fNight AS night "
253 querybase=$querybase$from" CROSS JOIN (SELECT @night :=0, @ot :=0, @os :=0, @bl:=0) PARAMS "
254 querybase=$querybase$where" ORDER BY fRunStart) o GROUP BY block HAVING ontime>0.75*"$bin
255
256 queryint=$queryint" "$querybase" order by 'time'"
257 queryext=$queryext" "$querybase" order by 'time'"
258 fi
259
260
261 fileint=$datapath"/FACT_preliminary_"$name"_internal.dat"
262 if [ "$overwrite" = "yes" ]
263 then
264 echo "internal: "$fileint
265 echo "# this file was created at "`date` > $fileint
266 fi
267 if [ $bin -le 0 ]
268 then
269 echo "# numbin time[mjd] start[mjd] stop[mjd] excrate[evts/h] corr.excrate[evts/h] flux[CU] flux[e-11/cm2/s] delta_time[mjd] ontime[min] excrate_err[evts/h] corr.excrate_err[evts/h] flux_err[CU] flux_err[e-11/cm2/s] significance nightmin, nightmax num_exc num_sig num_bg " >> $fileint
270 else
271 echo "# time[mjd] start[mjd] stop[mjd] excrate[evts/h] corr.excrate[evts/h] flux[CU] flux[e-11/cm2/s] delta_time[mjd] ontime[min] excrate_err[evts/h] corr.excrate_err[evts/h] flux_err[CU] flux_err[e-11/cm2/s] significance nightmin, nightmax num_exc num_sig num_bg " >> $fileint
272 fi
273 #echo "$queryint"
274 mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -s -e "$queryint" >> $fileint
275 #mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -e "$queryint"
276
277
278 fileext=$datapath"/FACT_preliminary_"$name".dat"
279 if [ "$overwrite" = "yes" ]
280 then
281 echo "external: "$fileext
282 echo "# this file was created at "`date` > $fileext
283 fi
284 if [ $bin -lt 0 ]
285 then
286 echo "# numbin time[mjd] start[mjd] stop[mjd] corr.excrate[evts/h] flux[e-11/cm2/s] delta_time[mjd] ontime[min] corr.excrate_err[evts/h] flux_err[e-11/cm2/s] significance " >> $fileext
287 else
288 echo "# time[mjd] start[mjd] stop[mjd] corr.excrate[evts/h] flux[e-11/cm2/s] delta_time[mjd] ontime[min] corr.excrate_err[evts/h] flux_err[e-11/cm2/s] significance " >> $fileext
289 fi
290 #echo "$queryext"
291 mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -s -e "$queryext" >> $fileext
292 #mysql --defaults-file=$sqlpw -u factread --host=$host $dbname -e "$queryext"
293}
294
295# SETUP:
296# ------
297# DB SETUP:
298# ---------
299# path to file with mysql password
300sqlpw=/home/$USER/.mysql.pw
301# host of mysql server with FACT DB
302#host=lp-fact # ISDC
303host=10.0.100.21 # LP or LP via vpn
304#host=localhost # your local machine in case you have a copy of DB
305# name of database
306dbname=factdata
307# defaults for zd and threshold
308zdmax=90 # all data
309thmax=1500 # all data
310#
311# SETUP for your data:
312# --------------------
313# output path
314path=`dirname $0`
315datapath=$path"/data"
316# create directory for data files
317if ! [ -e $datapath ]
318then
319 mkdir $datapath
320fi
321# time unit
322#timeunit=timestamp # default
323#timeunit=unix
324timeunit=mjd
325# time binning
326# positive values: minutes
327# negative values: days
328# special case 0: period
329# for season binning choose -365 and according start date
330#bin=20 # minutes
331#bin=0 # period
332bin=-1 # nightly
333#bin=-365 # yearly
334# choose analysis
335#table="AnalysisResultsAllQLA" # N/A
336table="AnalysisResultsRunLP" # QLA
337#table="AnalysisResultsRunISDC" # ISDC
338# time range
339nightmin=20111115
340nightmax=20171231
341# overwrite dataset file?
342# (useful to combine different binnings in one file -> set to "no")
343overwrite="yes"
344
345
346# example (adapt to your needs)
347
348# 501 MAGIC
349source=2
350name="Mrk501_2014_QLA"
351bin=-1
352nightmin=20140501
353nightmax=20140930
354get_results
355table="AnalysisResultsRunISDC" # ISDC
356name="Mrk501_2014_ISDC"
357get_results
358
359
360
361# end script here
362exit
363
364
365
366#
367# more examples
368#
369
370# Mrk 421
371source=1
372name="Mrk421_nightly"
373bin=-1
374get_results
375name="Mrk421_20min"
376bin=20
377get_results
378name="Mrk421_3d"
379bin=-3
380get_results
381name="Mrk421_10d"
382bin=-10
383get_results
384name="Mrk421_period"
385bin=0
386get_results
387
388
389
390# Mrk 501
391source=2
392name="Mrk501_nightly"
393bin=-1
394get_results
395name="Mrk501_20min"
396bin=20
397get_results
398name="Mrk501_3d"
399bin=-3
400get_results
401name="Mrk501_10d"
402bin=-10
403get_results
404name="Mrk501_period"
405bin=0
406get_results
407
408
409
410# 2344
411source=3
412name="2344_nightly"
413bin=-1
414get_results
415name="2344_20min"
416bin=20
417get_results
418name="2344_period"
419bin=0
420get_results
421
422
423
424# 1959
425source=7
426name="1959_nightly"
427bin=-1
428get_results
429name="1959_20min"
430bin=20
431get_results
432name="1959_period"
433bin=0
434get_results
435
436
437
438# 0323
439source=12
440name="0323_nightly"
441bin=-1
442get_results
443name="0323_20min"
444bin=20
445get_results
446name="0323_period"
447bin=0
448get_results
449
450
451
452# crab
453source=5
454name="Crab_nightly"
455bin=-1
456get_results
457name="Crab_20min"
458bin=20
459get_results
460name="Crab_period"
461bin=0
462get_results
463name="Crab_season"
464bin=-365
465nightmin=20110716
466nightmax=20180716
467get_results
468
469
470
471name="1959_2016"
472source=7
473bin=-1
474nightmin=20160201
475nightmax=20161105
476get_results
477
478name="1959_all_variable"
479overwrite="no"
480source=7
481bin=-365
482nightmin=20120201
483nightmax=20130131
484get_results
485nightmin=20130201
486nightmax=20140131
487get_results
488nightmin=20140201
489nightmax=20150131
490get_results
491bin=0
492nightmin=20150201
493nightmax=20160131
494get_results
495bin=-1
496nightmin=20160201
497nightmax=20170131
498get_results
499bin=0
500nightmin=20170201
501nightmax=20180131
502get_results
503
504
505
506overwrite="yes"
507name="1959_all_variable2"
508overwrite="no"
509source=7
510bin=-365
511nightmin=20120201
512nightmax=20130131
513get_results
514nightmin=20130201
515nightmax=20140131
516get_results
517nightmin=20140201
518nightmax=20150131
519get_results
520bin=0
521nightmin=20150201
522nightmax=20160131
523get_results
524bin=-1
525nightmin=20160201
526nightmax=20160817
527get_results
528bin=0
529nightmin=20160818
530nightmax=20180131
531get_results
532
533
534
535overwrite="yes"
536bin=0
537source=3
538name="2344period"
539get_results
540
541
542
543# flare night (HESS)
544name="Mrk501_10min_flarenight"
545source=2
546bin=10
547nightmin=20140623
548nightmax=20140623
549get_results
550
551
552
553# flare night (HESS)
554name="Mrk501_5min_flarenight"
555source=2
556bin=5
557nightmin=20140623
558nightmax=20140623
559get_results
560
561
562
563
564# full sample
565name="Mrk421_all_nightly"
566source=1
567get_results
568
569name="Mrk501_all_nightly"
570source=2
571get_results
572
573name="1959_all_nightly"
574source=7
575get_results
576
577name="2344_all_nightly"
578source=3
579get_results
580
581
582
583name="HESE20160427"
584source=19
585nightmin=20160425
586bin=-10
587get_results
588
589name="AMON20160731"
590source=21
591nightmin=20160730
592bin=-10
593get_results
594
595
596
Note: See TracBrowser for help on using the repository browser.