save calibration results to the database

[ZPYin]

Picasso saved all the calibration results to the ASCII files, which leads to a confusing data structure as plenty of old/reprocessed results kept loading to the same ASCII file. Now, we start to think about to replace it with MySQL database, because the records can be easily updated in the database.

Till now, there are three different calibration results to be saved: lidar calibration constants; water vapor calibration constants; depol calibration constants. I think it would be better to separate them into 3 tables, the structure is as below:

Detailed information of the variables is listed as below (deprecated):

lidar calibration constants

name	Type	description	example	Unit
id	int(11)	index of the record	10	-
cali_start_time	timestamp	start time of the lidar calibration period	'2011-01-01 00:00:00'	-
cali_stop_time	timestamp	stop time of the lidar calibration period	'2011-01-01 01:00:00'	-
liconst_FR_355	double	lidar constant for the far-range 355 nm channel	1.4e15	photon_count * m^3 * sr
uncertainty_liconst_FR_355	double	uncertainty of the lidar constant for the far-range 355 nm channel	2e14	photon_count * m^3 * sr
cali_method_FR_355	varchar(255)	lidar calibration method	'Raman method'	-
liconst_FR_532	double	lidar constant for the far-range 532 nm channel	1.4e15	photon_count * m^3 * sr
uncertainty_liconst_FR_532	double	uncertainty of the lidar constant for the far-range 532 nm channel	2e14	photon_count * m^3 * sr
cali_method_FR_532	varchar(255)	lidar calibration method	'Raman method'	-
liconst_FR_1064	double	lidar constant for the far-range 1064 nm channel	1.4e15	photon_count * m^3 * sr
uncertainty_liconst_FR_1064	double	uncertainty of the lidar constant for the far-range 1064 nm channel	2e14	photon_count * m^3 * sr
cali_method_FR_1064	varchar(255)	lidar calibration method	'Raman method'	-
liconst_FR_387	double	lidar constant for the far-range 387 nm channel	1.4e15	photon_count * m^3 * sr
uncertainty_liconst_FR_387	double	uncertainty of the lidar constant for the far-range 387 nm channel	2e14	photon_count * m^3 * sr
cali_method_FR_387	varchar(255)	lidar calibration method	'Raman method'	-
liconst_FR_607	double	lidar constant for the far-range 607 nm channel	1.4e15	photon_count * m^3 * sr
uncertainty_liconst_FR_607	double	uncertainty of the lidar constant for the far-range 607 nm channel	2e14	photon_count * m^3 * sr
cali_method_FR_607	varchar(255)	lidar calibration method	'Raman method'	-
lidar_data_fk	int(11)	foreign key to lidar data id	10	-
lidar_fk	int(11)	foreign key to lidar id	11	-

water vapor calibration constants

name	Type	description	example	Unit
id	int(11)	index of the record	10	-
cali_start_time	timestamp	start time of the calibration period	'2011-01-01 01:05:00'	-
cali_stop_time	timestamp	stop time of the calibration period	'2011-01-01 02:05:00'	-
standard_instrument	varchar(255)	comparison instrument served as the standard results	'AERONET'	-
standard_instrument_meas_time	timestamp		measurment time of the standard instrument	'2011-01-01 04:00:00'
wv_const	double	water vapor calibration constant	21.00	g*kg^{-1}
uncertainty_wv_const	double	uncertainty of the water vapor calibration constant	1.24	g*kg^{-1}
lidar_data_fk	int(11)	foreign key to lidar data id	10	-
lidar_fk	int(11)	foreign key to lidar id	11	-

depol calibration constants

name	Type	description	example	Unit
id	int(11)	index of the record	10	-
cali_start_time	timestamp	start time of the lidar calibration period	'2011-01-01 00:00:00'	-
cali_stop_time	timestamp	stop time of the lidar calibration period	'2011-01-01 01:00:00'	-
depol_const_355	double	depolarization calibration constant for 355 nm depolarization channel	0.021	-
uncertainty_depol_const_355	double	uncertainty of the depolarization constant	0.001	-
depol_const_532	double	depolarization calibration constant for 532 nm depolarization channel	0.021	-
uncertainty_depol_const_532	double	uncertainty of the depolarization constant	0.001	-
lidar_data_fk	int(11)	foreign key to lidar data id	10	-
lidar_fk	int(11)	foreign key to lidar id	11	-

These are the draft version. Any comments are welcomed.

[HolgerPollyNet]

I also thought about this topic,

my original idea was to not to change to much in the DB. Therefore my proposal would have been to use the current data base structure and implement it there, but having 3 new tables would also be good and probably more efficient in terms of overview.
R, what is your point of view?
One very important thing is that the actually retrieved water vapor constant and depol constant and lidar constant is also stored in the db. Probably adding 3 columns in the lidar_data table or in the respective lidar product.
If I consider the done-file-list.txt is could be stored instead of lidar ratio and level for each product, or we just add this information as 3 new lines:

lidar=PollyXT_LACROS
location=Punta_Arenas
starttime=20190311 00:00:00
stoptime=20190311 05:59:30
last_update=20191121 01:31:33
lambda=355
image=....png
level=0 --> not needed anymore
info=data based on laserlogbook.
nc_zip_file=....nc.zip
nc_zip_file_size=239666052
active=1
GDAS=1
GDAS_timestamp=20190311 03:00:00
lidar_ratio=50 --> could be replace by purpose for different products
software_version=1.3
product_type=monitor
product_starttime=20190311 00:00:00
product_stoptime=20190311 05:59:30
OR
water_vapor_cal_used= 11
lidar_constant _used= 12
depol_cali_used_355=11
depol_cali_used_532=14

[ZPYin]

my original idea was to not to change to much in the DB. Therefore my proposal would have been to use the current data base structure and implement it there, but having 3 new tables would also be good and probably more efficient in terms of overview.
R, what is your point of view?

For Picasso, both creating new tables and appending new columns in the lidar_data table work. The point is which way is easier to be implemented in pollyAPP and brings less cost for future extension. This might need to be carefully considered by Rico(@rico-hengst).

[HolgerPollyNet]

Hi, I concluded with Rico that additionally the nc-zip-file name should be added in the data base table. Furthermore, we thought that it might be handy to write our 3 new "done_file_list.txt" which includes the information for the respective table. This could be similar to the files wie have already to store the caliabration results. What do you think?

[ZPYin]

Create SQLite DB for storing calibration constants, for both on-line and off-line version of Picasso. The structure of the tables is like what mentioned here.

[ZPYin]

This feature has been added in 'zhenping' branch and will be merged to 'dev' when the code is all generalized.

Below is the updated structure about the db:

name	Type	description	example	Unit
id	INTEGER	index of the record	10	-
cali_start_time	TEXT	start time of the calibration period	'2011-01-01 01:05:00'	-
cali_stop_time	TEXT	stop time of the calibration period	'2011-01-01 02:05:00'	-
standard_instrument	TEXT	comparison instrument served as the standard results	'AERONET'	-
standard_instrument_meas_time	TEXT		measurment time of the standard instrument	'2011-01-01 04:00:00'
wv_const	REAL	water vapor calibration constant	21.00	g*kg^{-1}
uncertainty_wv_const	REAL	uncertainty of the water vapor calibration constant	1.24	g*kg^{-1}
nc_zip_file	TEXT	filename of zipped polly data	'2018_05_14_Mon_ARI_12_00_03.nc'	-
polly_type	TEXT	polly type	'arielle'	-

name	Type	description	example	Unit
id	INTEGER	index of the record	10	-
cali_start_time	TEXT	start time of the lidar calibration period	'2011-01-01 00:00:00'	-
cali_stop_time	TEXT	stop time of the lidar calibration period	'2011-01-01 01:00:00'	-
liconst	REAL	lidar constant for the far-range 355 nm channel	1.4e15	30 MHz * m^3 * sr
uncertainty_liconst	REAL	uncertainty of the lidar constant for the far-range 355 nm channel	2e14	30 MHz * m^3 * sr
cali_method	TEXT	lidar calibration method	'Raman_Method'	-
wavelength	TEXT	central wavelength of the channel	'355'	-
nc_zip_file	TEXT	filename of zipped polly data	'2018_05_14_Mon_ARI_12_00_03.nc'	-
polly_type	TEXT	polly type	'arielle'	-

name	Type	description	example	Unit
id	INTEGER	index of the record	10	-
cali_start_time	TEXT	start time of the lidar calibration period	'2011-01-01 00:00:00'	-
cali_stop_time	TEXT	stop time of the lidar calibration period	'2011-01-01 01:00:00'	-
depol_const	REAL	depolarization calibration constant depolarization channel	0.021	-
uncertainty_depol_const	REAL	uncertainty of the depolarization constant	0.001	-
wavelength	TEXT	central wavelength of the channel	'355'	-
nc_zip_file	TEXT	filename of zipped polly data	'2018_05_14_Mon_ARI_12_00_03.nc'	-
polly_type	TEXT	polly type	'arielle'	-

[HolgerPollyNet]

Hey Zhenping, Looks great so far! Thanks! One more issue, Ronny finished a new Polly sytem (PollyXT_CYP). I would like to include it in Picasso. Shall I simply copy the PollyXT_TROPOS folder and rename all function etc…? Or is this an unfavourite moment to do due to the many ongoing developments?? From: ZPYin <notifications@github.com> Sent: Sunday, April 19, 2020 6:22 AM To: PollyNET/Pollynet_Processing_Chain <Pollynet_Processing_Chain@noreply.github.com> Cc: Holger Baars <baars@tropos.de>; Comment <comment@noreply.github.com> Subject: Re: [PollyNET/Pollynet_Processing_Chain] save calibration results to the database (#20) This feature has been added in 'zhenping' branch and will be merged to 'dev' when the code is all generalized. Below is the updated structure about the db: [Picasso_cali_const_database_struture]<https://user-images.githubusercontent.com/7610928/79679287-390a2600-8237-11ea-905b-44a1bb6588b0.png> name Type description example Unit id INTEGER index of the record 10 - cali_start_time TEXT start time of the calibration period '2011-01-01 01:05:00' - cali_stop_time TEXT stop time of the calibration period '2011-01-01 02:05:00' - standard_instrument TEXT comparison instrument served as the standard results 'AERONET' - standard_instrument_meas_time TEXT measurment time of the standard instrument '2011-01-01 04:00:00' wv_const REAL water vapor calibration constant 21.00 g*kg^{-1} uncertainty_wv_const REAL uncertainty of the water vapor calibration constant 1.24 g*kg^{-1} nc_zip_file TEXT filename of zipped polly data '2018_05_14_Mon_ARI_12_00_03.nc' - polly_type TEXT polly type 'arielle' - name Type description example Unit id INTEGER index of the record 10 - cali_start_time TEXT start time of the lidar calibration period '2011-01-01 00:00:00' - cali_stop_time TEXT stop time of the lidar calibration period '2011-01-01 01:00:00' - liconst REAL lidar constant for the far-range 355 nm channel 1.4e15 photon_count * m^3 * sr uncertainty_liconst REAL uncertainty of the lidar constant for the far-range 355 nm channel 2e14 photon_count * m^3 * sr cali_method TEXT lidar calibration method 'Raman_Method' - wavelength TEXT central wavelength of the channel '355' - nc_zip_file TEXT filename of zipped polly data '2018_05_14_Mon_ARI_12_00_03.nc' - polly_type TEXT polly type 'arielle' - name Type description example Unit id INTEGER index of the record 10 - cali_start_time TEXT start time of the lidar calibration period '2011-01-01 00:00:00' - cali_stop_time TEXT stop time of the lidar calibration period '2011-01-01 01:00:00' - depol_const REAL depolarization calibration constant depolarization channel 0.021 - uncertainty_depol_const REAL uncertainty of the depolarization constant 0.001 - wavelength TEXT central wavelength of the channel '355' - nc_zip_file TEXT filename of zipped polly data '2018_05_14_Mon_ARI_12_00_03.nc' - polly_type TEXT polly type 'arielle' - — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#20 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ANJXHY6OQSAMOEM3J5X77N3RNJ4AHANCNFSM4MHSZVLQ>.

[ZPYin]

Yes, it's better to wait a few days. I'm still testing the previous commits in our server. Because I did quite huge changes to the code. I'm still uncertain what it will behave, althought I tested it on a small piece of data on my PC.

After the test was finished, implementing a new polly will be much easier than before, since the code was largely simplified. You will only need to add one matlab function to do this and it can be copied from other pollyxt (i.e., pollyxt_tjk), assuming it has the same hardware configruation like other pollyxt.

---

@HolgerPollyNet You can have an overview of what's planned and going on for Picasso in Picasso board.