mv_train_data

WITH train_molecule AS (
         SELECT mv_train_molecule.id,
            mv_train_molecule.molecule_name
           FROM champs_scalar_coupling.mv_train_molecule
        )
 SELECT train.id,
    train.molecule_name,
    train.type,
        CASE ("substring"(train.type, 1, 1))::integer
            WHEN 1 THEN 1
            ELSE 0
        END AS c_type_1,
        CASE ("substring"(train.type, 1, 1))::integer
            WHEN 2 THEN 1
            ELSE 0
        END AS c_type_2,
        CASE ("substring"(train.type, 1, 1))::integer
            WHEN 3 THEN 1
            ELSE 0
        END AS c_type_3,
    atom1.atom AS atom1,
    atom1_info.id AS atom1_id,
    atom0_bond.st_3ddistance AS bond0_length,
        CASE
            WHEN (train.type ~~ '1J%'::text) THEN atom0_bond.st_3ddistance
            WHEN (train.type ~~ '2J%'::text) THEN champs_scalar_coupling.st_3ddistance(atom0_bond.atom_1_coord, champs_scalar_coupling.st_makepoint(atom1.x, atom1.y, atom1.z))
            ELSE atom1_bond.st_3ddistance
        END AS bond1_length,
    champs_scalar_coupling.st_3ddistance(atom0_bond.atom_0_coord, champs_scalar_coupling.st_makepoint(atom1.x, atom1.y, atom1.z)) AS atom_distance,
        CASE
            WHEN (train.type ~~ '1J%'::text) THEN (0)::double precision
            WHEN (train.type ~~ '2J%'::text) THEN (0)::double precision
            ELSE (champs_scalar_coupling.line_angle(champs_scalar_coupling.st_angle(atom0_bond.atom_0_coord, atom0_bond.atom_1_coord, atom1_bond.atom_0_coord, atom1_bond.atom_1_coord)) / pi())
        END AS bonds_angle,
    train.scalar_coupling_constant
   FROM (((((train_molecule
     LEFT JOIN champs_scalar_coupling.train ON ((train_molecule.molecule_name = train.molecule_name)))
     LEFT JOIN champs_scalar_coupling.mv_bonds atom0_bond ON (((atom0_bond.molecule_name = train.molecule_name) AND (atom0_bond.atom_index_0 = train.atom_index_0))))
     LEFT JOIN champs_scalar_coupling.structures atom1 ON (((atom1.molecule_name = train.molecule_name) AND (atom1.atom_index = train.atom_index_1))))
     LEFT JOIN champs_scalar_coupling.mv_bonds atom1_bond ON (((train.type ~~ '3%'::text) AND (atom1_bond.molecule_name = train.molecule_name) AND (atom1_bond.atom_index_0 = train.atom_index_1) AND (EXISTS ( SELECT 1
           FROM champs_scalar_coupling.mv_bonds_old tmp
          WHERE ((tmp.molecule_name = train.molecule_name) AND (tmp.atom_index_0 = atom0_bond.atom_index_1) AND (tmp.atom_index_1 = atom1_bond.atom_index_1)))))))
     LEFT JOIN champs_scalar_coupling.atomic_radius atom1_info ON ((atom1_info.atom = atom1.atom)))
  ORDER BY train.id