Pandaaaa906

mv_train_data

Jul 28th, 2019
182
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH train_molecule AS (
  2.          SELECT mv_train_molecule.id,
  3.             mv_train_molecule.molecule_name
  4.            FROM champs_scalar_coupling.mv_train_molecule
  5.         )
  6.  SELECT train.id,
  7.     train.molecule_name,
  8.     train.type,
  9.         CASE ("substring"(train.type, 1, 1))::integer
  10.             WHEN 1 THEN 1
  11.             ELSE 0
  12.         END AS c_type_1,
  13.         CASE ("substring"(train.type, 1, 1))::integer
  14.             WHEN 2 THEN 1
  15.             ELSE 0
  16.         END AS c_type_2,
  17.         CASE ("substring"(train.type, 1, 1))::integer
  18.             WHEN 3 THEN 1
  19.             ELSE 0
  20.         END AS c_type_3,
  21.     atom1.atom AS atom1,
  22.     atom1_info.id AS atom1_id,
  23.     atom0_bond.st_3ddistance AS bond0_length,
  24.         CASE
  25.             WHEN (train.type ~~ '1J%'::text) THEN atom0_bond.st_3ddistance
  26.             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))
  27.             ELSE atom1_bond.st_3ddistance
  28.         END AS bond1_length,
  29.     champs_scalar_coupling.st_3ddistance(atom0_bond.atom_0_coord, champs_scalar_coupling.st_makepoint(atom1.x, atom1.y, atom1.z)) AS atom_distance,
  30.         CASE
  31.             WHEN (train.type ~~ '1J%'::text) THEN (0)::double precision
  32.             WHEN (train.type ~~ '2J%'::text) THEN (0)::double precision
  33.             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())
  34.         END AS bonds_angle,
  35.     train.scalar_coupling_constant
  36.    FROM (((((train_molecule
  37.      LEFT JOIN champs_scalar_coupling.train ON ((train_molecule.molecule_name = train.molecule_name)))
  38.      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))))
  39.      LEFT JOIN champs_scalar_coupling.structures atom1 ON (((atom1.molecule_name = train.molecule_name) AND (atom1.atom_index = train.atom_index_1))))
  40.      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
  41.            FROM champs_scalar_coupling.mv_bonds_old tmp
  42.           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)))))))
  43.      LEFT JOIN champs_scalar_coupling.atomic_radius atom1_info ON ((atom1_info.atom = atom1.atom)))
  44.   ORDER BY train.id
RAW Paste Data