Nasa DB

use hitran;



CREATE TABLE `hitran`.`Mass` (
`LINE` VARCHAR( 62 ) NULL DEFAULT NULL ,
`MOL` VARCHAR( 12 ) NULL DEFAULT NULL,
`ISOL` VARCHAR(12) NULL DEFAULT NULL,
`IGAS` VARCHAR(2) NULL DEFAULT NULL,
`ISO` VARCHAR(1) NULL DEFAULT NULL,
`ABUNDANCE` FLOAT NULL DEFAULT NULL,
`g_j` INT(4) NULL DEFAULT NULL,
`MASS` FLOAT NULL DEFAULT NULL
) ENGINE = MYISAM COMMENT = 'Truncated parsum table';

LOAD DATA LOCAL INFILE 'molparam.txt' INTO TABLE Mass(@var1)
SET
LINE   = substr(@var1,  1,  62);
#update Mass set MOL = TRIM(" " FROM SUBSTRING_INDEX(LINE,'(',1));

update Mass set IGAS = SUBSTRING_INDEX(SUBSTRING_INDEX(LINE,')',1),'(',-1);
update Mass set ISOL = TRIM(" " FROM substr(LINE,1,13));

update Mass set ABUNDANCE = substr(LINE,14,12);
update Mass set g_j = substr(LINE,40,5);
update Mass set MASS = substr(LINE,45,14);

#should be able to do this to get MOL and ISO
alter table Mass add column `T296` FLOAT;
update Mass set T296 = substr(LINE,26,14);
# remove lines that are not data lines
delete from mass where `T296` = 0;
# fill in the MOL,IGAS and ISO fields
update Q,Mass set Mass.MOL = Q.MOL where Q.ISOL = Mass.ISOL and FORMAT(Q.T296,0) = FORMAT(Mass.T296,0);
update Q,Mass set Mass.IGAS = Q.IGAS where Q.ISOL = Mass.ISOL and FORMAT(Q.T296,0) = FORMAT(Mass.T296,0);
update Q,Mass set Mass.ISO = Q.ISO where Q.ISOL = Mass.ISOL and FORMAT(Q.T296,0) = FORMAT(Mass.T296,0);
# this did not quite get all of the entries in the mass table :(
# descrepancies exist with CO2 ISO>7, O3 ISO>7 CH4 ISO>3, HCN ISO < 3, C2H6 ISO >1
# I added new ISO tags for CO2 ISO>7 , O3 ISO>7 (inserted above), HCN just needed to be rerun

SELECT MOL, ISOL, ISO, IGAS FROM Mass;

# HOBr and 312 Methane and 838 CO2 and 837 CO2 and 1231 C2H6 seem to be missing from Q
#  O, CH3OH and HOBr did not work match to Mass

SELECT MOL, ISOL, ISO, IGAS FROM Mass where ISOL = 6; #check for unique return
update Mass set MOL = 'O' where ISOL = 6;
update Mass set ISO = 1 where ISOL = 6;
update Mass set IGAS = 34 where ISOL = 6;

SELECT MOL, ISOL, ISO, IGAS FROM Mass where ISOL = 2161; #check for unique return
update Mass set MOL = 'CH3OH' where ISOL = 2161;
update Mass set ISO = 1 where ISOL = 2161;
update Mass set IGAS = 39 where ISOL = 2161;

SELECT MOL, ISOL, ISO, IGAS FROM Mass where ISOL = 219; #check for unique return
update Mass set MOL = 'HOBr' where ISOL = 219;
update Mass set ISO = 1 where ISOL = 219;
update Mass set IGAS = 40 where ISOL = 219;

SELECT MOL, ISOL, ISO, IGAS FROM Mass where ISOL = 211; #check for unique return
#not unique!
SELECT MOL, ISOL, ISO, IGAS FROM Mass where ISOL = 211 and MASS > 90; #check for unique return
update Mass set MOL = 'HOBr' where ISOL = 211 and MASS > 90;
update Mass set ISO = 2 where ISOL = 211 and MASS > 90;
update Mass set IGAS = 40 where ISOL = 211 and MASS > 90;

update Mass set ISO = 1 where IGAS = 42 and ISOL = 29;

# HOBr and 312 Methane and 838 CO2 and 837 CO2 and 1231 C2H6 seem to be missing from Q - this should remove entries in Mass
SELECT MOL, ISOL, ISO, IGAS FROM Mass where ISO is NULL;
delete from Mass where ISO is NULL;

#this worked except for HOBr because of my manual entries for ISO - why no Q for HOBr?

#Try to rebuild parsum with rows and columns the same as table (and get all lines up to 3000 K)
CREATE TABLE `hitran`.`QT` (
`Q_T` VARCHAR( 2916 ) NULL DEFAULT NULL
) ENGINE = MYISAM COMMENT = 'Truncated parsum table';


LOAD DATA LOCAL INFILE 'parsum.dat' INTO TABLE QT(@var1)
SET
Q_T = substr(@var1, 1, 2916);

ALTER TABLE QT add COLUMN `T` int;

DROP PROCEDURE IF EXISTS PARSE_QT;
DELIMITER $$
CREATE PROCEDURE PARSE_QT(IN b INT, IN c INT, IN sqlstr VARCHAR(2916))
#input should be first field size and total field
   BEGIN
         DECLARE i INT Default 0 ;
         SET i=8-b;
         simple_loop: LOOP
          SET i=i+b;
          IF i>c-b THEN
            LEAVE simple_loop;
          END IF;
          SELECT i;
          SET @d:=CONCAT('alter table QT add column \`', TRIM(" " FROM substr(sqlstr,i,27)), '\` float');
          SELECT @d;
          PREPARE n_StrSQL FROM @d;
          EXECUTE n_StrSQL;
   END LOOP simple_loop;
END $$
delimiter ;
CALL PARSE_QT(27,2916,'Temp(K)            H2O_161                    H2O_181                    H2O_171                    H2O_162                    H2O_182                    H2O_172                    CO2_626                    CO2_636                    CO2_628                    CO2_627                    CO2_638                    CO2_637                    CO2_828                    CO2_827                     O3_666                     O3_668                     O3_686                     O3_667                     O3_676                     O3_678                     O3_767                     O3_768                     O3_776                     O3_777                     O3_778                     O3_786                     O3_787                     O3_868                     O3_878                     O3_886                     O3_887                     O3_888                    N2O_446                    N2O_456                    N2O_546                    N2O_448                    N2O_447                      CO_26                      CO_36                      CO_28                      CO_27                      CO_38                      CO_37                    CH4_211                    CH4_311                    CH4_212                      O2_66                      O2_68                      O2_67                      NO_46                      NO_56                      NO_48                    SO2_626                    SO2_646                    NO2_646                   NH3_4111                   NH3_5111                   HNO3_146                      OH_61                      OH_81                      OH_62                      HF_19                     HCl_15                     HCl_17                     HBr_19                     HBr_11                      HI_17                     ClO_56                     ClO_76                    OCS_622                    OCS_624                    OCS_632                    OCS_623                    OCS_822                   H2CO_126                   H2CO_136                   H2CO_128                   HOCl_165                   HOCl_167                      N2_44                    HCN_124                    HCN_134                    HCN_125                  CH3Cl_215                  CH3Cl_217                  H2O2_1661                  C2H2_1221                  C2H2_1231                  C2H6_1221                   PH3_1111                   COF2_269                     SF6_29                    H2S_121                    H2S_141                    H2S_131                  HCOOH_126                    HO2_166                        O_6                ClONO2_5646                ClONO2_7646                     NO+_46                   HOBr_169                   HOBr_161                   C2H4_221                   C2H4_231                 CH3OH_2161                 CH3CN_2124                     CF4_29');



DROP PROCEDURE IF EXISTS PARSE_QT2;
DELIMITER $$
CREATE PROCEDURE PARSE_QT2(IN a INT, IN b INT, IN c INT, IN sqlstr VARCHAR(2916))
#input should be first field size and total field
   BEGIN
         DECLARE i INT Default 0 ;
         UPDATE QT set T = substr(Q_T,1,8);
         SET i=a-b;
         simple_loop: LOOP
          SET i=i+b;
          IF i>c-b THEN
            LEAVE simple_loop;
          END IF;

         SET @d:=CONCAT('update QT set ', TRIM(" " FROM substr(sqlstr,i,27)), ' = substr(Q_T,', i,', 19)');
         SELECT @d;
          PREPARE n_StrSQL FROM @d;
          EXECUTE n_StrSQL;
          IF i>c-b THEN
            LEAVE simple_loop;
          END IF;
   END LOOP simple_loop;
END $$
delimiter ;
CALL PARSE_QT2(8,27,2916,'Temp(K)            H2O_161                    H2O_181                    H2O_171                    H2O_162                    H2O_182                    H2O_172                    CO2_626                    CO2_636                    CO2_628                    CO2_627                    CO2_638                    CO2_637                    CO2_828                    CO2_827                     O3_666                     O3_668                     O3_686                     O3_667                     O3_676                     O3_678                     O3_767                     O3_768                     O3_776                     O3_777                     O3_778                     O3_786                     O3_787                     O3_868                     O3_878                     O3_886                     O3_887                     O3_888                    N2O_446                    N2O_456                    N2O_546                    N2O_448                    N2O_447                      CO_26                      CO_36                      CO_28                      CO_27                      CO_38                      CO_37                    CH4_211                    CH4_311                    CH4_212                      O2_66                      O2_68                      O2_67                      NO_46                      NO_56                      NO_48                    SO2_626                    SO2_646                    NO2_646                   NH3_4111                   NH3_5111                   HNO3_146                      OH_61                      OH_81                      OH_62                      HF_19                     HCl_15                     HCl_17                     HBr_19                     HBr_11                      HI_17                     ClO_56                     ClO_76                    OCS_622                    OCS_624                    OCS_632                    OCS_623                    OCS_822                   H2CO_126                   H2CO_136                   H2CO_128                   HOCl_165                   HOCl_167                      N2_44                    HCN_124                    HCN_134                    HCN_125                  CH3Cl_215                  CH3Cl_217                  H2O2_1661                  C2H2_1221                  C2H2_1231                  C2H6_1221                   PH3_1111                   COF2_269                     SF6_29                    H2S_121                    H2S_141                    H2S_131                  HCOOH_126                    HO2_166                        O_6                ClONO2_5646                ClONO2_7646                     NO+_46                   HOBr_169                   HOBr_161                   C2H4_221                   C2H4_231                 CH3OH_2161                 CH3CN_2124                     CF4_29');
#This crashed at NO+ probably because of the special character
#+---------------------------------------------+
#| @d                                          |
#+---------------------------------------------+
#| update QT set NO+_46 = substr(Q_T,2708, 19) |
#+---------------------------------------------+
#1 row in set (49.20 sec)

#ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+_46 = substr(Q_T,2708, 19)' at line 1

CALL PARSE_QT2(2735,27,2916,'Temp(K)            H2O_161                    H2O_181                    H2O_171                    H2O_162                    H2O_182                    H2O_172                    CO2_626                    CO2_636                    CO2_628                    CO2_627                    CO2_638                    CO2_637                    CO2_828                    CO2_827                     O3_666                     O3_668                     O3_686                     O3_667                     O3_676                     O3_678                     O3_767                     O3_768                     O3_776                     O3_777                     O3_778                     O3_786                     O3_787                     O3_868                     O3_878                     O3_886                     O3_887                     O3_888                    N2O_446                    N2O_456                    N2O_546                    N2O_448                    N2O_447                      CO_26                      CO_36                      CO_28                      CO_27                      CO_38                      CO_37                    CH4_211                    CH4_311                    CH4_212                      O2_66                      O2_68                      O2_67                      NO_46                      NO_56                      NO_48                    SO2_626                    SO2_646                    NO2_646                   NH3_4111                   NH3_5111                   HNO3_146                      OH_61                      OH_81                      OH_62                      HF_19                     HCl_15                     HCl_17                     HBr_19                     HBr_11                      HI_17                     ClO_56                     ClO_76                    OCS_622                    OCS_624                    OCS_632                    OCS_623                    OCS_822                   H2CO_126                   H2CO_136                   H2CO_128                   HOCl_165                   HOCl_167                      N2_44                    HCN_124                    HCN_134                    HCN_125                  CH3Cl_215                  CH3Cl_217                  H2O2_1661                  C2H2_1221                  C2H2_1231                  C2H6_1221                   PH3_1111                   COF2_269                     SF6_29                    H2S_121                    H2S_141                    H2S_131                  HCOOH_126                    HO2_166                        O_6                ClONO2_5646                ClONO2_7646                     NO+_46                   HOBr_169                   HOBr_161                   C2H4_221                   C2H4_231                 CH3OH_2161                 CH3CN_2124                     CF4_29');


SELECT FORMAT(`T`,1) , FORMAT(`H2O_161`,6) , FORMAT(`H2O_181`,6) , FORMAT(`H2O_171`,6) , FORMAT(`H2O_162`,6) , FORMAT(`H2O_182`,6) , FORMAT(`H2O_172`,6)
FROM `QT`
WHERE 1
LIMIT 0 , 3000

#*******************************
#Try to rebuild parsum with rows and columns the same as table (and get all lines up to 3000 K) with decimal format
#The decimal format allows better csv output, the NO+ tag was changed to NOp
DROP TABLE `hitran`.`QTD`;

CREATE TABLE `hitran`.`QTD` (
`Q_T` VARCHAR( 2916 ) NULL DEFAULT NULL
) ENGINE = MYISAM COMMENT = 'Truncated parsum table';


LOAD DATA LOCAL INFILE 'parsum.dat' INTO TABLE QTD(@var1)
SET
Q_T = substr(@var1, 1, 2916);

ALTER TABLE QTD add COLUMN `T` int;
DROP PROCEDURE IF EXISTS PARSE_QTD;
DELIMITER $$
CREATE PROCEDURE PARSE_QTD(IN b INT, IN c INT, IN sqlstr VARCHAR(2916))
#input should be first field size and total field
   BEGIN
         DECLARE i INT Default 0 ;
         SET i=8-b;
         simple_loop: LOOP
          SET i=i+b;
          IF i>c-b THEN
            LEAVE simple_loop;
          END IF;
          SELECT i;
          SET @d:=CONCAT('alter table QTD add column \`', TRIM(" " FROM substr(sqlstr,i,27)), '\` decimal(19,6)');
          SELECT @d;
          PREPARE n_StrSQL FROM @d;
          EXECUTE n_StrSQL;
   END LOOP simple_loop;
END $$
delimiter ;
CALL PARSE_QTD(27,2916,'Temp(K)            H2O_161                    H2O_181                    H2O_171                    H2O_162                    H2O_182                    H2O_172                    CO2_626                    CO2_636                    CO2_628                    CO2_627                    CO2_638                    CO2_637                    CO2_828                    CO2_827                     O3_666                     O3_668                     O3_686                     O3_667                     O3_676                     O3_678                     O3_767                     O3_768                     O3_776                     O3_777                     O3_778                     O3_786                     O3_787                     O3_868                     O3_878                     O3_886                     O3_887                     O3_888                    N2O_446                    N2O_456                    N2O_546                    N2O_448                    N2O_447                      CO_26                      CO_36                      CO_28                      CO_27                      CO_38                      CO_37                    CH4_211                    CH4_311                    CH4_212                      O2_66                      O2_68                      O2_67                      NO_46                      NO_56                      NO_48                    SO2_626                    SO2_646                    NO2_646                   NH3_4111                   NH3_5111                   HNO3_146                      OH_61                      OH_81                      OH_62                      HF_19                     HCl_15                     HCl_17                     HBr_19                     HBr_11                      HI_17                     ClO_56                     ClO_76                    OCS_622                    OCS_624                    OCS_632                    OCS_623                    OCS_822                   H2CO_126                   H2CO_136                   H2CO_128                   HOCl_165                   HOCl_167                      N2_44                    HCN_124                    HCN_134                    HCN_125                  CH3Cl_215                  CH3Cl_217                  H2O2_1661                  C2H2_1221                  C2H2_1231                  C2H6_1221                   PH3_1111                   COF2_269                     SF6_29                    H2S_121                    H2S_141                    H2S_131                  HCOOH_126                    HO2_166                        O_6                ClONO2_5646                ClONO2_7646                     NOp_46                   HOBr_169                   HOBr_161                   C2H4_221                   C2H4_231                 CH3OH_2161                 CH3CN_2124                     CF4_29');


DROP PROCEDURE IF EXISTS PARSE_QT4;
DELIMITER $$
CREATE PROCEDURE PARSE_QT4(IN a INT, IN b INT, IN c INT, IN sqlstr VARCHAR(2916))
#input should be first field size and total field
   BEGIN
         DECLARE i INT Default 0 ;
         UPDATE QTD set T = substr(Q_T,1,8);
         SET i=a-b;
         simple_loop: LOOP
          SET i=i+b;
          IF i>c-b THEN
            LEAVE simple_loop;
          END IF;

         SET @d:=CONCAT('update QTD set ', TRIM(" " FROM substr(sqlstr,i,27)), ' = substr(Q_T\,', i,', 19)');
         SELECT @d;
          PREPARE n_StrSQL FROM @d;
          EXECUTE n_StrSQL;
          IF i>c-b THEN
            LEAVE simple_loop;
          END IF;
   END LOOP simple_loop;
END $$
delimiter ;
CALL PARSE_QT4(8,27,2916,'Temp(K)            H2O_161                    H2O_181                    H2O_171                    H2O_162                    H2O_182                    H2O_172                    CO2_626                    CO2_636                    CO2_628                    CO2_627                    CO2_638                    CO2_637                    CO2_828                    CO2_827                     O3_666                     O3_668                     O3_686                     O3_667                     O3_676                     O3_678                     O3_767                     O3_768                     O3_776                     O3_777                     O3_778                     O3_786                     O3_787                     O3_868                     O3_878                     O3_886                     O3_887                     O3_888                    N2O_446                    N2O_456                    N2O_546                    N2O_448                    N2O_447                      CO_26                      CO_36                      CO_28                      CO_27                      CO_38                      CO_37                    CH4_211                    CH4_311                    CH4_212                      O2_66                      O2_68                      O2_67                      NO_46                      NO_56                      NO_48                    SO2_626                    SO2_646                    NO2_646                   NH3_4111                   NH3_5111                   HNO3_146                      OH_61                      OH_81                      OH_62                      HF_19                     HCl_15                     HCl_17                     HBr_19                     HBr_11                      HI_17                     ClO_56                     ClO_76                    OCS_622                    OCS_624                    OCS_632                    OCS_623                    OCS_822                   H2CO_126                   H2CO_136                   H2CO_128                   HOCl_165                   HOCl_167                      N2_44                    HCN_124                    HCN_134                    HCN_125                  CH3Cl_215                  CH3Cl_217                  H2O2_1661                  C2H2_1221                  C2H2_1231                  C2H6_1221                   PH3_1111                   COF2_269                     SF6_29                    H2S_121                    H2S_141                    H2S_131                  HCOOH_126                    HO2_166                        O_6                ClONO2_5646                ClONO2_7646                     NOp_46                   HOBr_169                   HOBr_161                   C2H4_221                   C2H4_231                 CH3OH_2161                 CH3CN_2124                     CF4_29');


SELECT `T`, `H2O_161`, `H2O_181` , `H2O_171` , `H2O_162` , `H2O_182` , `H2O_172`
FROM `QTD`
WHERE 1
LIMIT 0 , 3000


SELECT `T`, `HNO3_146`
FROM `QTD`
WHERE 1
LIMIT 0 , 3000

SELECT `T`, `HNO3_146` INTO OUTFILE '/tmp/test.txt' FIELDS TERMINATED BY '\,' LINES TERMINATED by '\,\n' FROM `QTD` WHERE 1 LIMIT 0 , 3000;