Standard galaxies
Jump to navigation
Jump to search
Standard galaxies process for SC8
Using the full_pipeline.py with the whole first octant (flagship_rockstar_octant1_c)
Los datos raw de este catálogo están en:
/pnfs/pic.es/data/astro/euclid/disk/newrock
Los campos bx, by, bz, los hemos añadido nosotros de la estructura de directorios/ficheros que nos llega de Zurich.
Los directorios van desde el 01 hasta el 62 (62 "boxes").
Hemos puesto float en las posiciones!!!!! Tenemos que reingestar los halos y volver a calcularlo todo de nuevo. Abrir un ticket.
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_0_pq (
`kind` BIGINT,
`luminosity_r01` DOUBLE,
`halo_x` DOUBLE,
`halo_y` DOUBLE,
`halo_z` DOUBLE,
`halo_vx` DOUBLE,
`halo_vy` DOUBLE,
`halo_vz` DOUBLE,
`halo_r` DOUBLE,
`true_redshift_halo` DOUBLE,
`halo_lm` DOUBLE,
`halo_n_sats` BIGINT,
`n_gals` BIGINT,
`abs_mag_r01` DOUBLE,
`abs_mag_r01_evolved` DOUBLE,
`luminosity_r01_evolved` DOUBLE,
`gr_restframe` DOUBLE,
`color_kind` INT,
`x_gal` DOUBLE,
`y_gal` DOUBLE,
`z_gal` DOUBLE,
`r_gal` DOUBLE,
`true_redshift_gal` DOUBLE,
`ra_gal` DOUBLE,
`dec_gal` DOUBLE,
`hpix_nest` BIGINT,
`vx_gal` DOUBLE,
`vy_gal` DOUBLE,
`vz_gal` DOUBLE,
`vrad_gal` DOUBLE,
`delta_r` DOUBLE,
`observed_redshift_gal` DOUBLE,
`sed_ke` DOUBLE,
`gr_cosmos` DOUBLE,
`sed_cosmos` DOUBLE,
`ext_curve_cosmos` BIGINT,
`ebv_cosmos` DOUBLE,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` DOUBLE,
`abs_mag_uv_dereddened` DOUBLE,
`log_ml_r01` DOUBLE,
`log_stellar_mass` DOUBLE,
`metallicity` DOUBLE,
`log_sfr` DOUBLE,
`logf_halpha_ext` DOUBLE,
`logf_halpha` DOUBLE,
`Halpha_scatter` DOUBLE,
`loglum_halpha` DOUBLE,
`loglum_halpha_ext` DOUBLE,
`logf_dummy` DOUBLE,
`z_dummy` DOUBLE,
`logf_halpha_model1_ext` DOUBLE,
`logf_halpha_model1` DOUBLE,
`loglum_halpha_model1_ext` DOUBLE,
`loglum_halpha_model1` DOUBLE,
`logf_halpha_model3_ext` DOUBLE,
`logf_halpha_model3` DOUBLE,
`loglum_Halpha_model3_ext` DOUBLE,
`loglum_Halpha_model3` DOUBLE,
`logf_hbeta_model1_ext` DOUBLE,
`logf_hbeta_model1` DOUBLE,
`logf_o2_model1_ext` DOUBLE,
`logf_o2_model1` DOUBLE,
`logf_n2_model1_ext` DOUBLE,
`logf_n2_model1` DOUBLE,
`logf_o3_model1_ext` DOUBLE,
`logf_o3_model1` DOUBLE,
`logf_s2_model1_ext` DOUBLE,
`logf_s2_model1` DOUBLE,
`logf_hbeta_model3_ext` DOUBLE,
`logf_hbeta_model3` DOUBLE,
`logf_o2_model3_ext` DOUBLE,
`logf_o2_model3` DOUBLE,
`logf_n2_model3_ext` DOUBLE,
`logf_n2_model3` DOUBLE,
`logf_o3_model3_ext` DOUBLE,
`logf_o3_model3` DOUBLE,
`logf_s2_model3_ext` DOUBLE,
`logf_s2_model3` DOUBLE,
`blanco_decam_i` DOUBLE,
`sdss_r01` DOUBLE,
`euclid_nisp_h` DOUBLE,
`l_gal` DOUBLE,
`b_gal` DOUBLE,
`mw_extinction` DOUBLE,
`blanco_decam_i_odonnell_ext` DOUBLE,
`euclid_nisp_h_odonnell_ext` DOUBLE,
`sdss_r01_odonnell_ext` DOUBLE,
`blanco_decam_i_el_model1_odonnell_ext` DOUBLE,
`blanco_decam_i_el_model3_odonnell_ext` DOUBLE,
`sdss_r01_el_model1_odonnell_ext` DOUBLE,
`sdss_r01_el_model3_odonnell_ext` DOUBLE,
`euclid_nisp_h_el_model1_odonnell_ext` DOUBLE,
`euclid_nisp_h_el_model3_odonnell_ext` DOUBLE,
`dominant_shape` BIGINT,
`median_major_axis` DOUBLE,
`scale_length` DOUBLE,
`bulge_fraction` DOUBLE,
`disk_scalelength` DOUBLE,
`disk_nsersic` DOUBLE,
`disk_r50` DOUBLE,
`bulge_r50` DOUBLE,
`bulge_nsersic` DOUBLE,
`inclination_angle` DOUBLE,
`disk_ellipticity` DOUBLE,
`bulge_ellipticity` DOUBLE,
`disk_axis_ratio` DOUBLE,
`bulge_axis_ratio` DOUBLE,
`step` BIGINT,
`random_index` DOUBLE,
`halo_id` BIGINT,
`galaxy_id` BIGINT
)
STORED AS PARQUET
LOCATION '/user/jcarrete/data/euclid/flagship_mock_1_10_0'
;
Clusterizo:
CREATE TABLE jcarrete.flagship_mock_1_10_0_c (
`kind` TINYINT,
`luminosity_r01` FLOAT,
`halo_x` FLOAT,
`halo_y` FLOAT,
`halo_z` FLOAT,
`halo_vx` FLOAT,
`halo_vy` FLOAT,
`halo_vz` FLOAT,
`halo_r` FLOAT,
`true_redshift_halo` FLOAT,
`halo_lm` FLOAT,
`halo_n_sats` INT,
`n_gals` INT,
`abs_mag_r01` FLOAT,
`abs_mag_r01_evolved` FLOAT,
`luminosity_r01_evolved` FLOAT,
`gr_restframe` FLOAT,
`color_kind` TINYINT,
`x_gal` FLOAT,
`y_gal` FLOAT,
`z_gal` FLOAT,
`r_gal` FLOAT,
`true_redshift_gal` FLOAT,
`ra_gal` DOUBLE,
`dec_gal` DOUBLE,
`hpix_29_nest` BIGINT,
`hpix_13_nest` BIGINT,
`vx_gal` FLOAT,
`vy_gal` FLOAT,
`vz_gal` FLOAT,
`vrad_gal` FLOAT,
`delta_r` FLOAT,
`observed_redshift_gal` FLOAT,
`sed_ke` FLOAT,
`gr_cosmos` FLOAT,
`sed_cosmos` FLOAT,
`ext_curve_cosmos` TINYINT,
`ebv_cosmos` FLOAT,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` FLOAT,
`abs_mag_uv_dereddened` FLOAT,
`log_ml_r01` FLOAT,
`log_stellar_mass` FLOAT,
`metallicity` FLOAT,
`log_sfr` FLOAT,
`logf_halpha_ext` FLOAT,
`logf_halpha` FLOAT,
`Halpha_scatter` FLOAT,
`loglum_halpha` FLOAT,
`loglum_halpha_ext` FLOAT,
`logf_dummy` FLOAT,
`z_dummy` FLOAT,
`logf_halpha_model1_ext` FLOAT,
`logf_halpha_model1` FLOAT,
`loglum_halpha_model1_ext` FLOAT,
`loglum_halpha_model1` FLOAT,
`logf_halpha_model3_ext` FLOAT,
`logf_halpha_model3` FLOAT,
`loglum_Halpha_model3_ext` FLOAT,
`loglum_Halpha_model3` FLOAT,
`logf_hbeta_model1_ext` FLOAT,
`logf_hbeta_model1` FLOAT,
`logf_o2_model1_ext` FLOAT,
`logf_o2_model1` FLOAT,
`logf_n2_model1_ext` FLOAT,
`logf_n2_model1` FLOAT,
`logf_o3_model1_ext` FLOAT,
`logf_o3_model1` FLOAT,
`logf_s2_model1_ext` FLOAT,
`logf_s2_model1` FLOAT,
`logf_hbeta_model3_ext` FLOAT,
`logf_hbeta_model3` FLOAT,
`logf_o2_model3_ext` FLOAT,
`logf_o2_model3` FLOAT,
`logf_n2_model3_ext` FLOAT,
`logf_n2_model3` FLOAT,
`logf_o3_model3_ext` FLOAT,
`logf_o3_model3` FLOAT,
`logf_s2_model3_ext` FLOAT,
`logf_s2_model3` FLOAT,
`blanco_decam_i` FLOAT,
`sdss_r01` FLOAT,
`euclid_nisp_h` FLOAT,
`l_gal` FLOAT,
`b_gal` FLOAT,
`mw_extinction` FLOAT,
`blanco_decam_i_odonnell_ext` FLOAT,
`euclid_nisp_h_odonnell_ext` FLOAT,
`sdss_r01_odonnell_ext` FLOAT,
`blanco_decam_i_el_model1_odonnell_ext` FLOAT,
`blanco_decam_i_el_model3_odonnell_ext` FLOAT,
`sdss_r01_el_model1_odonnell_ext` FLOAT,
`sdss_r01_el_model3_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model1_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model3_odonnell_ext` FLOAT,
`dominant_shape` TINYINT,
`median_major_axis` FLOAT,
`scale_length` FLOAT,
`bulge_fraction` FLOAT,
`disk_scalelength` FLOAT,
`disk_nsersic` FLOAT,
`disk_r50` FLOAT,
`bulge_r50` FLOAT,
`bulge_nsersic` FLOAT,
`inclination_angle` FLOAT,
`disk_ellipticity` FLOAT,
`bulge_ellipticity` FLOAT,
`disk_axis_ratio` FLOAT,
`bulge_axis_ratio` FLOAT,
`step` SMALLINT,
`random_index` FLOAT,
`halo_id` BIGINT,
`galaxy_id` INT
)
CLUSTERED BY (
hpix_13_nest)
SORTED BY (
hpix_13_nest ASC)
INTO 4096 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;
INSERT OVERWRITE TABLE jcarrete.flagship_mock_1_10_0_c SELECT kind, luminosity_r01, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, abs_mag_r01, abs_mag_r01_evolved, luminosity_r01_evolved, gr_restframe, color_kind, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, ra_gal, dec_gal, hpix_nest as hpix_29_nest, SHIFTRIGHT(hpix_nest, (29-13)*2) as hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, observed_redshift_gal, sed_ke, gr_cosmos, sed_cosmos, ext_curve_cosmos, ebv_cosmos, is_within_cosmos, cosmos_distance, abs_mag_uv_dereddened, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha_ext, logf_halpha, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, blanco_decam_i, sdss_r01, euclid_nisp_h, l_gal, b_gal, mw_extinction, blanco_decam_i_odonnell_ext, euclid_nisp_h_odonnell_ext, sdss_r01_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, dominant_shape, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, step, random_index, halo_id, galaxy_id FROM jcarrete.flagship_mock_1_10_0_pq;
ANALYZE TABLE jcarrete.flagship_mock_1_10_0_c COMPUTE STATISTICS FOR COLUMNS;
Cambio el esquema:
ALTER TABLE jcarrete.flagship_mock_1_10_0_c RENAME TO cosmohub.flagship_mock_1_10_0_c;
CREO OTRA VEZ LA TABLA CON EL CLUSTERING CORRECTO:
CREATE TABLE cosmohub.flagship_mock_1_10_0_c2 (
`kind` TINYINT,
`luminosity_r01` FLOAT,
`halo_x` FLOAT,
`halo_y` FLOAT,
`halo_z` FLOAT,
`halo_vx` FLOAT,
`halo_vy` FLOAT,
`halo_vz` FLOAT,
`halo_r` FLOAT,
`true_redshift_halo` FLOAT,
`halo_lm` FLOAT,
`halo_n_sats` INT,
`n_gals` INT,
`abs_mag_r01` FLOAT,
`abs_mag_r01_evolved` FLOAT,
`luminosity_r01_evolved` FLOAT,
`gr_restframe` FLOAT,
`color_kind` TINYINT,
`x_gal` FLOAT,
`y_gal` FLOAT,
`z_gal` FLOAT,
`r_gal` FLOAT,
`true_redshift_gal` FLOAT,
`ra_gal` DOUBLE,
`dec_gal` DOUBLE,
`hpix_29_nest` BIGINT,
`hpix_13_nest` BIGINT,
`vx_gal` FLOAT,
`vy_gal` FLOAT,
`vz_gal` FLOAT,
`vrad_gal` FLOAT,
`delta_r` FLOAT,
`observed_redshift_gal` FLOAT,
`sed_ke` FLOAT,
`gr_cosmos` FLOAT,
`sed_cosmos` FLOAT,
`ext_curve_cosmos` TINYINT,
`ebv_cosmos` FLOAT,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` FLOAT,
`abs_mag_uv_dereddened` FLOAT,
`log_ml_r01` FLOAT,
`log_stellar_mass` FLOAT,
`metallicity` FLOAT,
`log_sfr` FLOAT,
`logf_halpha_ext` FLOAT,
`logf_halpha` FLOAT,
`Halpha_scatter` FLOAT,
`loglum_halpha` FLOAT,
`loglum_halpha_ext` FLOAT,
`logf_dummy` FLOAT,
`z_dummy` FLOAT,
`logf_halpha_model1_ext` FLOAT,
`logf_halpha_model1` FLOAT,
`loglum_halpha_model1_ext` FLOAT,
`loglum_halpha_model1` FLOAT,
`logf_halpha_model3_ext` FLOAT,
`logf_halpha_model3` FLOAT,
`loglum_Halpha_model3_ext` FLOAT,
`loglum_Halpha_model3` FLOAT,
`logf_hbeta_model1_ext` FLOAT,
`logf_hbeta_model1` FLOAT,
`logf_o2_model1_ext` FLOAT,
`logf_o2_model1` FLOAT,
`logf_n2_model1_ext` FLOAT,
`logf_n2_model1` FLOAT,
`logf_o3_model1_ext` FLOAT,
`logf_o3_model1` FLOAT,
`logf_s2_model1_ext` FLOAT,
`logf_s2_model1` FLOAT,
`logf_hbeta_model3_ext` FLOAT,
`logf_hbeta_model3` FLOAT,
`logf_o2_model3_ext` FLOAT,
`logf_o2_model3` FLOAT,
`logf_n2_model3_ext` FLOAT,
`logf_n2_model3` FLOAT,
`logf_o3_model3_ext` FLOAT,
`logf_o3_model3` FLOAT,
`logf_s2_model3_ext` FLOAT,
`logf_s2_model3` FLOAT,
`blanco_decam_i` FLOAT,
`sdss_r01` FLOAT,
`euclid_nisp_h` FLOAT,
`l_gal` FLOAT,
`b_gal` FLOAT,
`mw_extinction` FLOAT,
`blanco_decam_i_odonnell_ext` FLOAT,
`euclid_nisp_h_odonnell_ext` FLOAT,
`sdss_r01_odonnell_ext` FLOAT,
`blanco_decam_i_el_model1_odonnell_ext` FLOAT,
`blanco_decam_i_el_model3_odonnell_ext` FLOAT,
`sdss_r01_el_model1_odonnell_ext` FLOAT,
`sdss_r01_el_model3_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model1_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model3_odonnell_ext` FLOAT,
`dominant_shape` TINYINT,
`median_major_axis` FLOAT,
`scale_length` FLOAT,
`bulge_fraction` FLOAT,
`disk_scalelength` FLOAT,
`disk_nsersic` FLOAT,
`disk_r50` FLOAT,
`bulge_r50` FLOAT,
`bulge_nsersic` FLOAT,
`inclination_angle` FLOAT,
`disk_ellipticity` FLOAT,
`bulge_ellipticity` FLOAT,
`disk_axis_ratio` FLOAT,
`bulge_axis_ratio` FLOAT,
`step` SMALLINT,
`random_index` FLOAT,
`halo_id` BIGINT,
`galaxy_id` INT
)
CLUSTERED BY (
step,
hpix_13_nest
)
SORTED BY (
step ASC,
hpix_13_nest ASC
)
INTO 4096 BUCKETS
STORED AS ORC
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_0_c2 SELECT * FROM cosmohub.flagship_mock_1_10_0_c;
Todo esto lo hago para probar el JOIN eficiente
Some tips:
SELECT COUNT(DISTINTC ) better performance: https://prantik.github.io/blog/Query-Optimization-in-Hive-Count-Distinct-with-Brickhouse-Group-Count
CREATE TABLE tallada.dup_flagship_mock_1_10_0_c
STORED AS ORC AS
SELECT t.* FROM (
SELECT halo_id, galaxy_id, kind, ra_gal, dec_gal, true_redshift_gal,
COUNT(*) OVER (PARTITION BY ra_gal, dec_gal) AS cnt
FROM flagship_mock_1_10_0_c AS s
) AS t
WHERE t.cnt > 1;
/software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_1/0.pq -t jcarrete.flagship_mock_1_10_1_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_1_pq (
`kind` BIGINT,
`luminosity_r01` DOUBLE,
`halo_x` DOUBLE,
`halo_y` DOUBLE,
`halo_z` DOUBLE,
`halo_vx` DOUBLE,
`halo_vy` DOUBLE,
`halo_vz` DOUBLE,
`halo_r` DOUBLE,
`true_redshift_halo` DOUBLE,
`halo_lm` DOUBLE,
`halo_n_sats` BIGINT,
`n_gals` BIGINT,
`abs_mag_r01` DOUBLE,
`abs_mag_r01_evolved` DOUBLE,
`luminosity_r01_evolved` DOUBLE,
`gr_restframe` DOUBLE,
`color_kind` INT,
`x_gal` DOUBLE,
`y_gal` DOUBLE,
`z_gal` DOUBLE,
`r_gal` DOUBLE,
`true_redshift_gal` DOUBLE,
`ra_gal` DOUBLE,
`dec_gal` DOUBLE,
`hpix_nest` BIGINT,
`vx_gal` DOUBLE,
`vy_gal` DOUBLE,
`vz_gal` DOUBLE,
`vrad_gal` DOUBLE,
`delta_r` DOUBLE,
`observed_redshift_gal` DOUBLE,
`sed_ke` DOUBLE,
`gr_cosmos` DOUBLE,
`sed_cosmos` DOUBLE,
`ext_curve_cosmos` BIGINT,
`ebv_cosmos` DOUBLE,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` DOUBLE,
`abs_mag_uv_dereddened` DOUBLE,
`log_ml_r01` DOUBLE,
`log_stellar_mass` DOUBLE,
`metallicity` DOUBLE,
`log_sfr` DOUBLE,
`logf_halpha_ext` DOUBLE,
`logf_halpha` DOUBLE,
`Halpha_scatter` DOUBLE,
`loglum_halpha` DOUBLE,
`loglum_halpha_ext` DOUBLE,
`logf_dummy` DOUBLE,
`z_dummy` DOUBLE,
`logf_halpha_model1_ext` DOUBLE,
`logf_halpha_model1` DOUBLE,
`loglum_halpha_model1_ext` DOUBLE,
`loglum_halpha_model1` DOUBLE,
`logf_halpha_model3_ext` DOUBLE,
`logf_halpha_model3` DOUBLE,
`loglum_Halpha_model3_ext` DOUBLE,
`loglum_Halpha_model3` DOUBLE,
`logf_hbeta_model1_ext` DOUBLE,
`logf_hbeta_model1` DOUBLE,
`logf_o2_model1_ext` DOUBLE,
`logf_o2_model1` DOUBLE,
`logf_n2_model1_ext` DOUBLE,
`logf_n2_model1` DOUBLE,
`logf_o3_model1_ext` DOUBLE,
`logf_o3_model1` DOUBLE,
`logf_s2_model1_ext` DOUBLE,
`logf_s2_model1` DOUBLE,
`logf_hbeta_model3_ext` DOUBLE,
`logf_hbeta_model3` DOUBLE,
`logf_o2_model3_ext` DOUBLE,
`logf_o2_model3` DOUBLE,
`logf_n2_model3_ext` DOUBLE,
`logf_n2_model3` DOUBLE,
`logf_o3_model3_ext` DOUBLE,
`logf_o3_model3` DOUBLE,
`logf_s2_model3_ext` DOUBLE,
`logf_s2_model3` DOUBLE,
`blanco_decam_i` DOUBLE,
`sdss_r01` DOUBLE,
`euclid_nisp_h` DOUBLE,
`l_gal` DOUBLE,
`b_gal` DOUBLE,
`mw_extinction` DOUBLE,
`blanco_decam_i_odonnell_ext` DOUBLE,
`euclid_nisp_h_odonnell_ext` DOUBLE,
`sdss_r01_odonnell_ext` DOUBLE,
`blanco_decam_i_el_model1_odonnell_ext` DOUBLE,
`blanco_decam_i_el_model3_odonnell_ext` DOUBLE,
`sdss_r01_el_model1_odonnell_ext` DOUBLE,
`sdss_r01_el_model3_odonnell_ext` DOUBLE,
`euclid_nisp_h_el_model1_odonnell_ext` DOUBLE,
`euclid_nisp_h_el_model3_odonnell_ext` DOUBLE,
`dominant_shape` BIGINT,
`median_major_axis` DOUBLE,
`scale_length` DOUBLE,
`bulge_fraction` DOUBLE,
`disk_scalelength` DOUBLE,
`disk_nsersic` DOUBLE,
`disk_r50` DOUBLE,
`bulge_r50` DOUBLE,
`bulge_nsersic` DOUBLE,
`inclination_angle` DOUBLE,
`disk_ellipticity` DOUBLE,
`bulge_ellipticity` DOUBLE,
`disk_axis_ratio` DOUBLE,
`bulge_axis_ratio` DOUBLE,
`step` BIGINT,
`random_index` DOUBLE,
`halo_id` BIGINT,
`galaxy_id` BIGINT
)
STORED AS PARQUET
LOCATION '/user/jcarrete/data/euclid/flagship_mock_1_10_1'
;
Clustered como la del lensing. OJO PORQUE el HPIX_13_NEST TIENE QUE SER INT!
CREATE TABLE cosmohub.flagship_mock_1_10_1_c (
`kind` TINYINT,
`luminosity_r01` FLOAT,
`halo_x` FLOAT,
`halo_y` FLOAT,
`halo_z` FLOAT,
`halo_vx` FLOAT,
`halo_vy` FLOAT,
`halo_vz` FLOAT,
`halo_r` FLOAT,
`true_redshift_halo` FLOAT,
`halo_lm` FLOAT,
`halo_n_sats` INT,
`n_gals` INT,
`abs_mag_r01` FLOAT,
`abs_mag_r01_evolved` FLOAT,
`luminosity_r01_evolved` FLOAT,
`gr_restframe` FLOAT,
`color_kind` TINYINT,
`x_gal` FLOAT,
`y_gal` FLOAT,
`z_gal` FLOAT,
`r_gal` FLOAT,
`true_redshift_gal` FLOAT,
`ra_gal` DOUBLE,
`dec_gal` DOUBLE,
`hpix_29_nest` BIGINT,
`hpix_13_nest` INT,
`vx_gal` FLOAT,
`vy_gal` FLOAT,
`vz_gal` FLOAT,
`vrad_gal` FLOAT,
`delta_r` FLOAT,
`observed_redshift_gal` FLOAT,
`sed_ke` FLOAT,
`gr_cosmos` FLOAT,
`sed_cosmos` FLOAT,
`ext_curve_cosmos` TINYINT,
`ebv_cosmos` FLOAT,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` FLOAT,
`abs_mag_uv_dereddened` FLOAT,
`log_ml_r01` FLOAT,
`log_stellar_mass` FLOAT,
`metallicity` FLOAT,
`log_sfr` FLOAT,
`logf_halpha_ext` FLOAT,
`logf_halpha` FLOAT,
`Halpha_scatter` FLOAT,
`loglum_halpha` FLOAT,
`loglum_halpha_ext` FLOAT,
`logf_dummy` FLOAT,
`z_dummy` FLOAT,
`logf_halpha_model1_ext` FLOAT,
`logf_halpha_model1` FLOAT,
`loglum_halpha_model1_ext` FLOAT,
`loglum_halpha_model1` FLOAT,
`logf_halpha_model3_ext` FLOAT,
`logf_halpha_model3` FLOAT,
`loglum_Halpha_model3_ext` FLOAT,
`loglum_Halpha_model3` FLOAT,
`logf_hbeta_model1_ext` FLOAT,
`logf_hbeta_model1` FLOAT,
`logf_o2_model1_ext` FLOAT,
`logf_o2_model1` FLOAT,
`logf_n2_model1_ext` FLOAT,
`logf_n2_model1` FLOAT,
`logf_o3_model1_ext` FLOAT,
`logf_o3_model1` FLOAT,
`logf_s2_model1_ext` FLOAT,
`logf_s2_model1` FLOAT,
`logf_hbeta_model3_ext` FLOAT,
`logf_hbeta_model3` FLOAT,
`logf_o2_model3_ext` FLOAT,
`logf_o2_model3` FLOAT,
`logf_n2_model3_ext` FLOAT,
`logf_n2_model3` FLOAT,
`logf_o3_model3_ext` FLOAT,
`logf_o3_model3` FLOAT,
`logf_s2_model3_ext` FLOAT,
`logf_s2_model3` FLOAT,
`blanco_decam_i` FLOAT,
`sdss_r01` FLOAT,
`euclid_nisp_h` FLOAT,
`l_gal` FLOAT,
`b_gal` FLOAT,
`mw_extinction` FLOAT,
`blanco_decam_i_odonnell_ext` FLOAT,
`euclid_nisp_h_odonnell_ext` FLOAT,
`sdss_r01_odonnell_ext` FLOAT,
`blanco_decam_i_el_model1_odonnell_ext` FLOAT,
`blanco_decam_i_el_model3_odonnell_ext` FLOAT,
`sdss_r01_el_model1_odonnell_ext` FLOAT,
`sdss_r01_el_model3_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model1_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model3_odonnell_ext` FLOAT,
`dominant_shape` TINYINT,
`median_major_axis` FLOAT,
`scale_length` FLOAT,
`bulge_fraction` FLOAT,
`disk_scalelength` FLOAT,
`disk_nsersic` FLOAT,
`disk_r50` FLOAT,
`bulge_r50` FLOAT,
`bulge_nsersic` FLOAT,
`inclination_angle` FLOAT,
`disk_ellipticity` FLOAT,
`bulge_ellipticity` FLOAT,
`disk_axis_ratio` FLOAT,
`bulge_axis_ratio` FLOAT,
`step` SMALLINT,
`random_index` FLOAT,
`halo_id` BIGINT,
`galaxy_id` INT
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_1_c SELECT kind, luminosity_r01, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, abs_mag_r01, abs_mag_r01_evolved, luminosity_r01_evolved, gr_restframe, color_kind, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, ra_gal, dec_gal, hpix_nest as hpix_29_nest, SHIFTRIGHT(hpix_nest, (29-13)*2) as hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, observed_redshift_gal, sed_ke, gr_cosmos, sed_cosmos, ext_curve_cosmos, ebv_cosmos, is_within_cosmos, cosmos_distance, abs_mag_uv_dereddened, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha_ext, logf_halpha, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, blanco_decam_i, sdss_r01, euclid_nisp_h, l_gal, b_gal, mw_extinction, blanco_decam_i_odonnell_ext, euclid_nisp_h_odonnell_ext, sdss_r01_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, dominant_shape, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, step, random_index, halo_id, galaxy_id FROM jcarrete.flagship_mock_1_10_1_pq;
CREATE TABLE cosmohub.flagship_mock_1_10_1_s (
`kind` TINYINT,
`luminosity_r01` FLOAT,
`halo_x` FLOAT,
`halo_y` FLOAT,
`halo_z` FLOAT,
`halo_vx` FLOAT,
`halo_vy` FLOAT,
`halo_vz` FLOAT,
`halo_r` FLOAT,
`true_redshift_halo` FLOAT,
`halo_lm` FLOAT,
`halo_n_sats` INT,
`n_gals` INT,
`abs_mag_r01` FLOAT,
`abs_mag_r01_evolved` FLOAT,
`luminosity_r01_evolved` FLOAT,
`gr_restframe` FLOAT,
`color_kind` TINYINT,
`x_gal` FLOAT,
`y_gal` FLOAT,
`z_gal` FLOAT,
`r_gal` FLOAT,
`true_redshift_gal` FLOAT,
`ra_gal` FLOAT,
`dec_gal` FLOAT,
`ra_mag_gal` FLOAT,
`dec_mag_gal` FLOAT,
`kappa` FLOAT,
`gamma1` FLOAT,
`gamma2` FLOAT,
`defl1` FLOAT,
`defl2` FLOAT,
`hpix_29_nest` BIGINT,
`hpix_13_nest` INT,
`vx_gal` FLOAT,
`vy_gal` FLOAT,
`vz_gal` FLOAT,
`vrad_gal` FLOAT,
`delta_r` FLOAT,
`observed_redshift_gal` FLOAT,
`sed_ke` FLOAT,
`gr_cosmos` FLOAT,
`sed_cosmos` FLOAT,
`ext_curve_cosmos` TINYINT,
`ebv_cosmos` FLOAT,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` FLOAT,
`abs_mag_uv_dereddened` FLOAT,
`log_ml_r01` FLOAT,
`log_stellar_mass` FLOAT,
`metallicity` FLOAT,
`log_sfr` FLOAT,
`logf_halpha_ext` FLOAT,
`logf_halpha` FLOAT,
`Halpha_scatter` FLOAT,
`loglum_halpha` FLOAT,
`loglum_halpha_ext` FLOAT,
`logf_dummy` FLOAT,
`z_dummy` FLOAT,
`logf_halpha_model1_ext` FLOAT,
`logf_halpha_model1` FLOAT,
`loglum_halpha_model1_ext` FLOAT,
`loglum_halpha_model1` FLOAT,
`logf_halpha_model3_ext` FLOAT,
`logf_halpha_model3` FLOAT,
`loglum_Halpha_model3_ext` FLOAT,
`loglum_Halpha_model3` FLOAT,
`logf_hbeta_model1_ext` FLOAT,
`logf_hbeta_model1` FLOAT,
`logf_o2_model1_ext` FLOAT,
`logf_o2_model1` FLOAT,
`logf_n2_model1_ext` FLOAT,
`logf_n2_model1` FLOAT,
`logf_o3_model1_ext` FLOAT,
`logf_o3_model1` FLOAT,
`logf_s2_model1_ext` FLOAT,
`logf_s2_model1` FLOAT,
`logf_hbeta_model3_ext` FLOAT,
`logf_hbeta_model3` FLOAT,
`logf_o2_model3_ext` FLOAT,
`logf_o2_model3` FLOAT,
`logf_n2_model3_ext` FLOAT,
`logf_n2_model3` FLOAT,
`logf_o3_model3_ext` FLOAT,
`logf_o3_model3` FLOAT,
`logf_s2_model3_ext` FLOAT,
`logf_s2_model3` FLOAT,
`blanco_decam_i` FLOAT,
`sdss_r01` FLOAT,
`euclid_nisp_h` FLOAT,
`l_gal` FLOAT,
`b_gal` FLOAT,
`mw_extinction` FLOAT,
`blanco_decam_i_odonnell_ext` FLOAT,
`euclid_nisp_h_odonnell_ext` FLOAT,
`sdss_r01_odonnell_ext` FLOAT,
`blanco_decam_i_el_model1_odonnell_ext` FLOAT,
`blanco_decam_i_el_model3_odonnell_ext` FLOAT,
`sdss_r01_el_model1_odonnell_ext` FLOAT,
`sdss_r01_el_model3_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model1_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model3_odonnell_ext` FLOAT,
`dominant_shape` TINYINT,
`median_major_axis` FLOAT,
`scale_length` FLOAT,
`bulge_fraction` FLOAT,
`disk_scalelength` FLOAT,
`disk_nsersic` FLOAT,
`disk_r50` FLOAT,
`bulge_r50` FLOAT,
`bulge_nsersic` FLOAT,
`inclination_angle` FLOAT,
`disk_ellipticity` FLOAT,
`bulge_ellipticity` FLOAT,
`disk_axis_ratio` FLOAT,
`bulge_axis_ratio` FLOAT,
`step` SMALLINT,
`random_index` FLOAT,
`halo_id` BIGINT,
`galaxy_id` INT
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
To assign lensing:
SET hive.auto.convert.join.noconditionaltask.size=1000;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_1_s SELECT kind, luminosity_r01, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, abs_mag_r01, abs_mag_r01_evolved, luminosity_r01_evolved, gr_restframe, color_kind, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, ra_gal, dec_gal, mag_pos.ra_mag, mag_pos.dec_mag, kappa, gamma1, gamma2, defl1, defl2, hpix_29_nest, hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, observed_redshift_gal, sed_ke, gr_cosmos, sed_cosmos, ext_curve_cosmos, ebv_cosmos, is_within_cosmos, cosmos_distance, abs_mag_uv_dereddened, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha_ext, logf_halpha, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, blanco_decam_i, sdss_r01, euclid_nisp_h, l_gal, b_gal, mw_extinction, blanco_decam_i_odonnell_ext, euclid_nisp_h_odonnell_ext, sdss_r01_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, dominant_shape, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, step, random_index, halo_id, galaxy_id FROM (SELECT kind, luminosity_r01, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, abs_mag_r01, abs_mag_r01_evolved, luminosity_r01_evolved, gr_restframe, color_kind, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, ra_gal, dec_gal, udf.magnified_positions(CAST(ra_gal AS DOUBLE) , CAST(dec_gal AS DOUBLE), CAST(l.defl1 AS DOUBLE), CAST(l.defl2 AS DOUBLE)) as mag_pos, l.kappa, l.gamma1, l.gamma2, l.defl1, l.defl2, hpix_29_nest, l.hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, observed_redshift_gal, sed_ke, gr_cosmos, sed_cosmos, ext_curve_cosmos, ebv_cosmos, is_within_cosmos, cosmos_distance, abs_mag_uv_dereddened, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha_ext, logf_halpha, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, blanco_decam_i, sdss_r01, euclid_nisp_h, l_gal, b_gal, mw_extinction, blanco_decam_i_odonnell_ext, euclid_nisp_h_odonnell_ext, sdss_r01_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, dominant_shape, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, l.step, random_index, halo_id, galaxy_id FROM cosmohub.flagship_mock_1_10_1_c as m JOIN cosmohub.flagship_lensing2 as l ON m.step = l.step AND m.hpix_13_nest = l.hpix_13_nest) as t ;
ANALYZE TABLE cosmohub.flagship_mock_1_10_1_s COMPUTE STATISTICS FOR COLUMNS;
Creo la tabla con el footprint que me he generado yo:
CREATE EXTERNAL TABLE jcarrete.sc8_footprint_nest_nside512_csv(
`hpix_9_nest` int,
`value` boolean
)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim'=',',
'serialization.format'=',')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'hdfs://AntNest/user/jcarrete/data/euclid/footprint_SC8/'
;
CREATE TABLE jcarrete.sc8_footprint_nest_nside512_c(
`hpix_9_nest` int,
`value` boolean
)
CLUSTERED BY (
hpix_9_nest)
SORTED BY (
hpix_9_nest ASC)
INTO 4 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;
INSERT OVERWRITE TABLE jcarrete.sc8_footprint_nest_nside512_c SELECT * FROM jcarrete.sc8_footprint_nest_nside512_csv;
CAMBIO EL ESQUEMA DEL FOOTPRINT PARA QUE LA GENTE PUEDA USARLO DESDE COSMOHUB.
cosmohub.sc8_footprint_nest_nside512_c
CREATE TABLE cosmohub.flagship_mock_1_10_1_s_sc8 (
`kind` TINYINT,
`luminosity_r01` FLOAT,
`halo_x` FLOAT,
`halo_y` FLOAT,
`halo_z` FLOAT,
`halo_vx` FLOAT,
`halo_vy` FLOAT,
`halo_vz` FLOAT,
`halo_r` FLOAT,
`true_redshift_halo` FLOAT,
`halo_lm` FLOAT,
`halo_n_sats` INT,
`n_gals` INT,
`abs_mag_r01` FLOAT,
`abs_mag_r01_evolved` FLOAT,
`luminosity_r01_evolved` FLOAT,
`gr_restframe` FLOAT,
`color_kind` TINYINT,
`x_gal` FLOAT,
`y_gal` FLOAT,
`z_gal` FLOAT,
`r_gal` FLOAT,
`true_redshift_gal` FLOAT,
`ra_gal` FLOAT,
`dec_gal` FLOAT,
`ra_mag_gal` FLOAT,
`dec_mag_gal` FLOAT,
`kappa` FLOAT,
`gamma1` FLOAT,
`gamma2` FLOAT,
`defl1` FLOAT,
`defl2` FLOAT,
`hpix_29_nest` BIGINT,
`hpix_13_nest` INT,
`vx_gal` FLOAT,
`vy_gal` FLOAT,
`vz_gal` FLOAT,
`vrad_gal` FLOAT,
`delta_r` FLOAT,
`observed_redshift_gal` FLOAT,
`sed_ke` FLOAT,
`gr_cosmos` FLOAT,
`sed_cosmos` FLOAT,
`ext_curve_cosmos` TINYINT,
`ebv_cosmos` FLOAT,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` FLOAT,
`abs_mag_uv_dereddened` FLOAT,
`log_ml_r01` FLOAT,
`log_stellar_mass` FLOAT,
`metallicity` FLOAT,
`log_sfr` FLOAT,
`logf_halpha_ext` FLOAT,
`logf_halpha` FLOAT,
`Halpha_scatter` FLOAT,
`loglum_halpha` FLOAT,
`loglum_halpha_ext` FLOAT,
`logf_dummy` FLOAT,
`z_dummy` FLOAT,
`logf_halpha_model1_ext` FLOAT,
`logf_halpha_model1` FLOAT,
`loglum_halpha_model1_ext` FLOAT,
`loglum_halpha_model1` FLOAT,
`logf_halpha_model3_ext` FLOAT,
`logf_halpha_model3` FLOAT,
`loglum_Halpha_model3_ext` FLOAT,
`loglum_Halpha_model3` FLOAT,
`logf_hbeta_model1_ext` FLOAT,
`logf_hbeta_model1` FLOAT,
`logf_o2_model1_ext` FLOAT,
`logf_o2_model1` FLOAT,
`logf_n2_model1_ext` FLOAT,
`logf_n2_model1` FLOAT,
`logf_o3_model1_ext` FLOAT,
`logf_o3_model1` FLOAT,
`logf_s2_model1_ext` FLOAT,
`logf_s2_model1` FLOAT,
`logf_hbeta_model3_ext` FLOAT,
`logf_hbeta_model3` FLOAT,
`logf_o2_model3_ext` FLOAT,
`logf_o2_model3` FLOAT,
`logf_n2_model3_ext` FLOAT,
`logf_n2_model3` FLOAT,
`logf_o3_model3_ext` FLOAT,
`logf_o3_model3` FLOAT,
`logf_s2_model3_ext` FLOAT,
`logf_s2_model3` FLOAT,
`blanco_decam_i` FLOAT,
`sdss_r01` FLOAT,
`euclid_nisp_h` FLOAT,
`l_gal` FLOAT,
`b_gal` FLOAT,
`mw_extinction` FLOAT,
`blanco_decam_i_odonnell_ext` FLOAT,
`euclid_nisp_h_odonnell_ext` FLOAT,
`sdss_r01_odonnell_ext` FLOAT,
`blanco_decam_i_el_model1_odonnell_ext` FLOAT,
`blanco_decam_i_el_model3_odonnell_ext` FLOAT,
`sdss_r01_el_model1_odonnell_ext` FLOAT,
`sdss_r01_el_model3_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model1_odonnell_ext` FLOAT,
`euclid_nisp_h_el_model3_odonnell_ext` FLOAT,
`dominant_shape` TINYINT,
`median_major_axis` FLOAT,
`scale_length` FLOAT,
`bulge_fraction` FLOAT,
`disk_scalelength` FLOAT,
`disk_nsersic` FLOAT,
`disk_r50` FLOAT,
`bulge_r50` FLOAT,
`bulge_nsersic` FLOAT,
`inclination_angle` FLOAT,
`disk_ellipticity` FLOAT,
`bulge_ellipticity` FLOAT,
`disk_axis_ratio` FLOAT,
`bulge_axis_ratio` FLOAT,
`step` SMALLINT,
`random_index` FLOAT,
`halo_id` BIGINT,
`galaxy_id` INT
)
CLUSTERED BY (
step,
hpix_13_nest)
SORTED BY (
step ASC,
hpix_13_nest ASC)
INTO 4096 BUCKETS
STORED AS ORC
;
I materialize the JOIN between the first octant with the SC8 mask:
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_1_s_sc8 SELECT kind, luminosity_r01, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, abs_mag_r01, abs_mag_r01_evolved, luminosity_r01_evolved, gr_restframe, color_kind, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, ra_gal, dec_gal, ra_mag_gal, dec_mag_gal, kappa, gamma1, gamma2, defl1, defl2, hpix_29_nest, hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, observed_redshift_gal, sed_ke, gr_cosmos, sed_cosmos, ext_curve_cosmos, ebv_cosmos, is_within_cosmos, cosmos_distance, abs_mag_uv_dereddened, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha_ext, logf_halpha, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, blanco_decam_i, sdss_r01, euclid_nisp_h, l_gal, b_gal, mw_extinction, blanco_decam_i_odonnell_ext, euclid_nisp_h_odonnell_ext, sdss_r01_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, dominant_shape, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, step, random_index, halo_id, galaxy_id FROM cosmohub.flagship_mock_1_10_1_s JOIN jcarrete.sc8_footprint_nest_nside512_c ON hpix_9_nest = udf.ang2pix(9, ra_gal + 180, dec_gal, True, True) AND value = 1 ;
I generate a very small patch, with "_prime" values to check the rotation module included in the flux pipeline
The catalog is stored in:
/user/jcarrete/data/euclid/flagship_mock_1_10_1_s_flux
Then I generate a new catalog including "everything".
I create the catalog with the flux_pipeline.py. I include two more steps in the pipeline to rotate all the different fields.
The catalog is stored in parquet:
/user/jcarrete/data/euclid/flagship_mock_1_10_2_s_sc8_flux'
[jcarrete@data ~]$ /software/astro/scripts/create_table_from_parquet.sh -p /hdfs/user/jcarrete/data/euclid/flagship_mock_1_10_2_s_sc8_flux/0.pq -t jcarrete.flagship_mock_1_10_2_s_sc8_pq -e
CREATE EXTERNAL TABLE jcarrete.flagship_mock_1_10_2_s_sc8_pq (
`kind` BIGINT,
`luminosity_r01` DOUBLE,
`halo_x` DOUBLE,
`halo_y` DOUBLE,
`halo_z` DOUBLE,
`halo_vx` DOUBLE,
`halo_vy` DOUBLE,
`halo_vz` DOUBLE,
`halo_r` DOUBLE,
`true_redshift_halo` DOUBLE,
`halo_lm` DOUBLE,
`halo_n_sats` BIGINT,
`n_gals` BIGINT,
`abs_mag_r01` DOUBLE,
`abs_mag_r01_evolved` DOUBLE,
`luminosity_r01_evolved` DOUBLE,
`gr_restframe` DOUBLE,
`color_kind` BIGINT,
`x_gal` DOUBLE,
`y_gal` DOUBLE,
`z_gal` DOUBLE,
`r_gal` DOUBLE,
`true_redshift_gal` DOUBLE,
`ra_gal` DOUBLE,
`dec_gal` DOUBLE,
`ra_mag_gal` DOUBLE,
`dec_mag_gal` DOUBLE,
`kappa` DOUBLE,
`gamma1` DOUBLE,
`gamma2` DOUBLE,
`defl1` DOUBLE,
`defl2` DOUBLE,
`hpix_29_nest` BIGINT,
`hpix_13_nest` BIGINT,
`vx_gal` DOUBLE,
`vy_gal` DOUBLE,
`vz_gal` DOUBLE,
`vrad_gal` DOUBLE,
`delta_r` DOUBLE,
`observed_redshift_gal` DOUBLE,
`sed_ke` DOUBLE,
`gr_cosmos` DOUBLE,
`sed_cosmos` DOUBLE,
`ext_curve_cosmos` BIGINT,
`ebv_cosmos` DOUBLE,
`is_within_cosmos` BOOLEAN,
`cosmos_distance` DOUBLE,
`abs_mag_uv_dereddened` DOUBLE,
`log_ml_r01` DOUBLE,
`log_stellar_mass` DOUBLE,
`metallicity` DOUBLE,
`log_sfr` DOUBLE,
`logf_halpha_ext` DOUBLE,
`logf_halpha` DOUBLE,
`halpha_scatter` DOUBLE,
`loglum_halpha` DOUBLE,
`loglum_halpha_ext` DOUBLE,
`logf_dummy` DOUBLE,
`z_dummy` DOUBLE,
`logf_halpha_model1_ext` DOUBLE,
`logf_halpha_model1` DOUBLE,
`loglum_halpha_model1_ext` DOUBLE,
`loglum_halpha_model1` DOUBLE,
`logf_halpha_model3_ext` DOUBLE,
`logf_halpha_model3` DOUBLE,
`loglum_halpha_model3_ext` DOUBLE,
`loglum_halpha_model3` DOUBLE,
`logf_hbeta_model1_ext` DOUBLE,
`logf_hbeta_model1` DOUBLE,
`logf_o2_model1_ext` DOUBLE,
`logf_o2_model1` DOUBLE,
`logf_n2_model1_ext` DOUBLE,
`logf_n2_model1` DOUBLE,
`logf_o3_model1_ext` DOUBLE,
`logf_o3_model1` DOUBLE,
`logf_s2_model1_ext` DOUBLE,
`logf_s2_model1` DOUBLE,
`logf_hbeta_model3_ext` DOUBLE,
`logf_hbeta_model3` DOUBLE,
`logf_o2_model3_ext` DOUBLE,
`logf_o2_model3` DOUBLE,
`logf_n2_model3_ext` DOUBLE,
`logf_n2_model3` DOUBLE,
`logf_o3_model3_ext` DOUBLE,
`logf_o3_model3` DOUBLE,
`logf_s2_model3_ext` DOUBLE,
`logf_s2_model3` DOUBLE,
`dominant_shape` BIGINT,
`median_major_axis` DOUBLE,
`scale_length` DOUBLE,
`bulge_fraction` DOUBLE,
`disk_scalelength` DOUBLE,
`disk_nsersic` DOUBLE,
`disk_r50` DOUBLE,
`bulge_r50` DOUBLE,
`bulge_nsersic` DOUBLE,
`inclination_angle` DOUBLE,
`disk_ellipticity` DOUBLE,
`bulge_ellipticity` DOUBLE,
`disk_axis_ratio` DOUBLE,
`bulge_axis_ratio` DOUBLE,
`step` BIGINT,
`random_index` DOUBLE,
`blanco_decam_g` DOUBLE,
`blanco_decam_i` DOUBLE,
`blanco_decam_r` DOUBLE,
`blanco_decam_z` DOUBLE,
`subaru_hsc_z` DOUBLE,
`jst_jpcam_g` DOUBLE,
`kids_g` DOUBLE,
`kids_i` DOUBLE,
`kids_r` DOUBLE,
`kids_u` DOUBLE,
`lsst_g` DOUBLE,
`lsst_i` DOUBLE,
`lsst_r` DOUBLE,
`lsst_u` DOUBLE,
`lsst_y` DOUBLE,
`lsst_z` DOUBLE,
`cfht_megacam_r` DOUBLE,
`cfht_megacam_u` DOUBLE,
`pan_starrs_i` DOUBLE,
`pan_starrs_z` DOUBLE,
`2mass_h` DOUBLE,
`2mass_j` DOUBLE,
`2mass_ks` DOUBLE,
`sdss_r01` DOUBLE,
`gaia_bp` DOUBLE,
`gaia_g` DOUBLE,
`gaia_rp` DOUBLE,
`euclid_nisp_h` DOUBLE,
`euclid_nisp_j` DOUBLE,
`euclid_nisp_y` DOUBLE,
`euclid_vis` DOUBLE,
`l_gal` DOUBLE,
`b_gal` DOUBLE,
`mw_extinction` DOUBLE,
`2mass_h_odonnell_ext` DOUBLE,
`2mass_j_odonnell_ext` DOUBLE,
`2mass_ks_odonnell_ext` DOUBLE,
`blanco_decam_g_odonnell_ext` DOUBLE,
`blanco_decam_i_odonnell_ext` DOUBLE,
`blanco_decam_r_odonnell_ext` DOUBLE,
`blanco_decam_z_odonnell_ext` DOUBLE,
`cfht_megacam_r_odonnell_ext` DOUBLE,
`cfht_megacam_u_odonnell_ext` DOUBLE,
`euclid_nisp_h_odonnell_ext` DOUBLE,
`euclid_nisp_j_odonnell_ext` DOUBLE,
`euclid_nisp_y_odonnell_ext` DOUBLE,
`euclid_vis_odonnell_ext` DOUBLE,
`gaia_bp_odonnell_ext` DOUBLE,
`gaia_g_odonnell_ext` DOUBLE,
`gaia_rp_odonnell_ext` DOUBLE,
`jst_jpcam_g_odonnell_ext` DOUBLE,
`kids_g_odonnell_ext` DOUBLE,
`kids_i_odonnell_ext` DOUBLE,
`kids_r_odonnell_ext` DOUBLE,
`kids_u_odonnell_ext` DOUBLE,
`lsst_g_odonnell_ext` DOUBLE,
`lsst_i_odonnell_ext` DOUBLE,
`lsst_r_odonnell_ext` DOUBLE,
`lsst_u_odonnell_ext` DOUBLE,
`lsst_y_odonnell_ext` DOUBLE,
`lsst_z_odonnell_ext` DOUBLE,
`pan_starrs_i_odonnell_ext` DOUBLE,
`pan_starrs_z_odonnell_ext` DOUBLE,
`sdss_r01_odonnell_ext` DOUBLE,
`subaru_hsc_z_odonnell_ext` DOUBLE,
`blanco_decam_g_el_model1_odonnell_ext` DOUBLE,
`blanco_decam_g_el_model3_odonnell_ext` DOUBLE,
`blanco_decam_i_el_model1_odonnell_ext` DOUBLE,
`blanco_decam_i_el_model3_odonnell_ext` DOUBLE,
`blanco_decam_r_el_model1_odonnell_ext` DOUBLE,
`blanco_decam_r_el_model3_odonnell_ext` DOUBLE,
`blanco_decam_z_el_model1_odonnell_ext` DOUBLE,
`blanco_decam_z_el_model3_odonnell_ext` DOUBLE,
`subaru_hsc_z_el_model1_odonnell_ext` DOUBLE,
`subaru_hsc_z_el_model3_odonnell_ext` DOUBLE,
`jst_jpcam_g_el_model1_odonnell_ext` DOUBLE,
`jst_jpcam_g_el_model3_odonnell_ext` DOUBLE,
`kids_g_el_model1_odonnell_ext` DOUBLE,
`kids_g_el_model3_odonnell_ext` DOUBLE,
`kids_i_el_model1_odonnell_ext` DOUBLE,
`kids_i_el_model3_odonnell_ext` DOUBLE,
`kids_r_el_model1_odonnell_ext` DOUBLE,
`kids_r_el_model3_odonnell_ext` DOUBLE,
`kids_u_el_model1_odonnell_ext` DOUBLE,
`kids_u_el_model3_odonnell_ext` DOUBLE,
`lsst_g_el_model1_odonnell_ext` DOUBLE,
`lsst_g_el_model3_odonnell_ext` DOUBLE,
`lsst_i_el_model1_odonnell_ext` DOUBLE,
`lsst_i_el_model3_odonnell_ext` DOUBLE,
`lsst_r_el_model1_odonnell_ext` DOUBLE,
`lsst_r_el_model3_odonnell_ext` DOUBLE,
`lsst_u_el_model1_odonnell_ext` DOUBLE,
`lsst_u_el_model3_odonnell_ext` DOUBLE,
`lsst_y_el_model1_odonnell_ext` DOUBLE,
`lsst_y_el_model3_odonnell_ext` DOUBLE,
`lsst_z_el_model1_odonnell_ext` DOUBLE,
`lsst_z_el_model3_odonnell_ext` DOUBLE,
`cfht_megacam_r_el_model1_odonnell_ext` DOUBLE,
`cfht_megacam_r_el_model3_odonnell_ext` DOUBLE,
`cfht_megacam_u_el_model1_odonnell_ext` DOUBLE,
`cfht_megacam_u_el_model3_odonnell_ext` DOUBLE,
`pan_starrs_i_el_model1_odonnell_ext` DOUBLE,
`pan_starrs_i_el_model3_odonnell_ext` DOUBLE,
`pan_starrs_z_el_model1_odonnell_ext` DOUBLE,
`pan_starrs_z_el_model3_odonnell_ext` DOUBLE,
`2mass_h_el_model1_odonnell_ext` DOUBLE,
`2mass_h_el_model3_odonnell_ext` DOUBLE,
`2mass_j_el_model1_odonnell_ext` DOUBLE,
`2mass_j_el_model3_odonnell_ext` DOUBLE,
`2mass_ks_el_model1_odonnell_ext` DOUBLE,
`2mass_ks_el_model3_odonnell_ext` DOUBLE,
`sdss_r01_el_model1_odonnell_ext` DOUBLE,
`sdss_r01_el_model3_odonnell_ext` DOUBLE,
`gaia_bp_el_model1_odonnell_ext` DOUBLE,
`gaia_bp_el_model3_odonnell_ext` DOUBLE,
`gaia_g_el_model1_odonnell_ext` DOUBLE,
`gaia_g_el_model3_odonnell_ext` DOUBLE,
`gaia_rp_el_model1_odonnell_ext` DOUBLE,
`gaia_rp_el_model3_odonnell_ext` DOUBLE,
`euclid_nisp_h_el_model1_odonnell_ext` DOUBLE,
`euclid_nisp_h_el_model3_odonnell_ext` DOUBLE,
`euclid_nisp_j_el_model1_odonnell_ext` DOUBLE,
`euclid_nisp_j_el_model3_odonnell_ext` DOUBLE,
`euclid_nisp_y_el_model1_odonnell_ext` DOUBLE,
`euclid_nisp_y_el_model3_odonnell_ext` DOUBLE,
`euclid_vis_el_model1_odonnell_ext` DOUBLE,
`euclid_vis_el_model3_odonnell_ext` DOUBLE,
`halo_id` BIGINT,
`galaxy_id` BIGINT
)
STORED AS PARQUET
LOCATION '/user/jcarrete/data/euclid/flagship_mock_1_10_2_s_sc8_flux'
;
OJO !!! PREGUNTAR CÓMO CLUSTEREAMOS! Ahora mismo está como la vez anterior para poner el lensing. Partiotioned by step, hpix_13_nest HE PUESTO INT para el hpix_13_nest
CREATE TABLE cosmohub.flagship_mock_1_10_2_s_sc8_c(
`halo_id` bigint COMMENT 'unique halo id coming from the Flagship dark matter halo catalog',
`galaxy_id` int COMMENT 'combined with the unique_halo_id they uniquely identify each galaxy',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`random_index` float COMMENT 'random number [0 - 1) for subsampling',
`ra_gal` float COMMENT 'galaxy right ascension (degrees)',
`dec_gal` float COMMENT 'galaxy declination (degrees)',
`ra_mag_gal` float COMMENT 'galaxy magnified right ascension (degree)',
`dec_mag_gal` float COMMENT 'galaxy magnified declination (degree)',
`kappa` float COMMENT 'convergence',
`gamma1` float COMMENT 'shear',
`gamma2` float COMMENT 'shear',
`defl1` float COMMENT 'deflection1',
`defl2` float COMMENT 'deflection2',
`l_gal` float COMMENT 'galactic longitude (degrees)',
`b_gal` float COMMENT 'galactic latitude (degrees)',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`x_gal` float COMMENT 'galaxy comoving x coordinate (Mpc/h)',
`y_gal` float COMMENT 'galaxy comoving y coordinate (Mpc/h)',
`z_gal` float COMMENT 'galaxy comoving z coordinate (Mpc/h)',
`r_gal` float COMMENT 'galaxy comoving distance (Mpc/h)',
`true_redshift_gal` float COMMENT 'true galaxy redshift',
`observed_redshift_gal` float COMMENT 'observed galaxy redshift (including peculiar velocity)',
`hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format',
`hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format',
`vx_gal` float COMMENT 'physical (peculiar) velocity in x-component (km/s)',
`vy_gal` float COMMENT 'physical (peculiar) velocity in y-component (km/s)',
`vz_gal` float COMMENT 'physical (peculiar) velocity in z-component (km/s)',
`vrad_gal` float COMMENT 'missing comment',
`delta_r` float COMMENT 'missing comment',
`abs_mag_r01` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h)',
`luminosity_r01` float COMMENT 'luminosity in the 0.1-r-band',
`abs_mag_r01_evolved` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h) including MICECATv2.0 magnitude evolution',
`sdss_r01` float COMMENT 'observed flux in sdss r-band redshifted to z=0.1',
`abs_mag_uv_dereddened` float COMMENT 'absolute magnitude ultraviolet',
`gr_restframe` float COMMENT '(g-r) rest-frame at z=0',
`color_kind` tinyint COMMENT 'galaxy color kind: 0=red sequence 1=green valley 2=blue cloud',
`sed_ke` float COMMENT 'K-correction for each SED',
`gr_cosmos` float COMMENT 'interpolated COSMOS g-r color',
`sed_cosmos` float COMMENT 'interpolated basic COSMOS SED [0 30]',
`is_within_cosmos` boolean COMMENT 'missing comment',
`cosmos_distance` float COMMENT 'missing comment',
`ext_curve_cosmos` tinyint COMMENT 'COSMOS extinction curve [0-4]',
`ebv_cosmos` float COMMENT 'interpolated extinction strength for COSMOS extinction curve',
`log_ml_r01` float COMMENT 'logarithm of stellar_mass/luminosity ratio in the r01 SDSS band',
`log_stellar_mass` float COMMENT 'logarithm of the stellar mass (Msun/h)',
`metallicity` float COMMENT 'oxygen metallicity in units 12+logO/H',
`log_sfr` float COMMENT 'logarithm of star formation rate in (Msun/h)/year',
`logf_halpha` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: DOES NOT include extinction)',
`logf_halpha_ext` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: includes extinction)',
`halpha_scatter` float COMMENT 'missing comment',
`loglum_halpha` float COMMENT 'DEPRECATED logarithm of Halpha luminosity (erg/s/h^2) using empirical relation with UV magnitude (DOES NOT include extinction)',
`loglum_halpha_ext` float COMMENT 'missing comment',
`logf_dummy` float COMMENT 'missing comment',
`z_dummy` float COMMENT 'missing comment',
`logf_halpha_model1_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: includes extinction)',
`logf_halpha_model1` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: DOES NOT include extinction)',
`loglum_halpha_model1_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (includes extinction)',
`loglum_halpha_model1` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (DOES NOT include extinction)',
`logf_halpha_model3_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: includes extinction)',
`logf_halpha_model3` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: DOES NOT include extinction)',
`loglum_halpha_model3_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (includes extinction)',
`loglum_halpha_model3` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (DOES NOT include extinction)',
`logf_hbeta_model1_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_hbeta_model1` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_o2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_o2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_n2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_n2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_o3_model1_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_o3_model1` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_s2_model1_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_s2_model1` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_hbeta_model3_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_hbeta_model3` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_o2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_o2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_n2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_n2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_o3_model3_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_o3_model3` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_s2_model3_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_s2_model3` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`2mass_h` float COMMENT 'observed flux in 2MASS H-band (Euclid provided)',
`2mass_j` float COMMENT 'observed flux in 2MASS J-band (Euclid provided)',
`2mass_ks` float COMMENT 'observed flux in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'observed flux in DES g-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'observed flux in DES i-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'observed flux in DES r-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'observed flux in DES z-band (Euclid provided)',
`cfht_megacam_r` float COMMENT 'observed flux in CFHT r-band (Euclid provided)',
`cfht_megacam_u` float COMMENT 'observed flux in CFHT u-band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_vis` float COMMENT 'observed flux in Euclid VIS band (Euclid provided)',
`gaia_bp` float COMMENT 'observed flux in Gaia-BP band (Euclid provided)',
`gaia_g` float COMMENT 'observed flux in Gaia-G band (Euclid provided)',
`gaia_rp` float COMMENT 'observed flux in Gaia-RP band (Euclid provided)',
`jst_jpcam_g` float COMMENT 'observed flux in JST g-band (Euclid provided)',
`kids_g` float COMMENT 'observed flux in KIDS g-band (Euclid provided)',
`kids_i` float COMMENT 'observed flux in KIDS i-band (Euclid provided)',
`kids_r` float COMMENT 'observed flux in KIDS r-band (Euclid provided)',
`kids_u` float COMMENT 'observed flux in KIDS u-band (Euclid provided)',
`lsst_g` float COMMENT 'observed flux in LSST g-band (Euclid provided)',
`lsst_i` float COMMENT 'observed flux in LSST i-band (Euclid provided)',
`lsst_r` float COMMENT 'observed flux in LSST r-band (Euclid provided)',
`lsst_u` float COMMENT 'observed flux in LSST u-band (Euclid provided)',
`lsst_y` float COMMENT 'observed flux in LSST Y-band (Euclid provided)',
`lsst_z` float COMMENT 'observed flux in LSST z-band (Euclid provided)',
`pan_starrs_i` float COMMENT 'observed flux in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z` float COMMENT 'observed flux in Pan-STARRS z-band (Euclid provided)',
`subaru_hsc_z` float COMMENT 'observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`2mass_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS H-band (Euclid provided)',
`2mass_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES z-band (Euclid provided)',
`cfht_megacam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT u-band (Euclid provided)',
`euclid_nisp_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid VIS band (Euclid provided)',
`gaia_bp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-RP band (Euclid provided)',
`jst_jpcam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in JST g-band (Euclid provided)',
`kids_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS g-band (Euclid provided)',
`kids_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS i-band (Euclid provided)',
`kids_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS r-band (Euclid provided)',
`kids_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST g-band (Euclid provided)',
`lsst_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST i-band (Euclid provided)',
`lsst_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST r-band (Euclid provided)',
`lsst_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST u-band (Euclid provided)',
`lsst_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST Y-band (Euclid provided)',
`lsst_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST z-band (Euclid provided)',
`pan_starrs_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS z-band (Euclid provided)',
`sdss_r01_odonnell_ext` float COMMENT 'observed flux in sdss r-band redshifted to z=0.1 with MW extinction',
`subaru_hsc_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`2mass_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction 2MASS H-band (Euclid provided)',
`2mass_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction 2MASS H-band (Euclid provided)',
`2mass_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS Ks-band (Euclid provided)',
`2mass_ks_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES z-band (Euclid provided)',
`blanco_decam_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES z-band (Euclid provided)',
`cfht_megacam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT u-band (Euclid provided)',
`cfht_megacam_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT u-band (Euclid provided)',
`euclid_nisp_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid VIS band (Euclid provided)',
`euclid_vis_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid VIS band (Euclid provided)',
`gaia_bp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_bp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-G band (Euclid provided)',
`gaia_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-RP band (Euclid provided)',
`gaia_rp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-RP band (Euclid provided)',
`jst_jpcam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in JST g-band (Euclid provided)',
`jst_jpcam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in JST g-band (Euclid provided)',
`kids_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS g-band (Euclid provided)',
`kids_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS g-band (Euclid provided)',
`kids_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS i-band (Euclid provided)',
`kids_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS i-band (Euclid provided)',
`kids_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS r-band (Euclid provided)',
`kids_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS r-band (Euclid provided)',
`kids_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS u-band (Euclid provided)',
`kids_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST g-band (Euclid provided)',
`lsst_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST g-band (Euclid provided)',
`lsst_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST i-band (Euclid provided)',
`lsst_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST i-band (Euclid provided)',
`lsst_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST r-band (Euclid provided)',
`lsst_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST r-band (Euclid provided)',
`lsst_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST u-band (Euclid provided)',
`lsst_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST u-band (Euclid provided)',
`lsst_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST Y-band (Euclid provided)',
`lsst_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST Y-band (Euclid provided)',
`lsst_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST z-band (Euclid provided)',
`lsst_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST z-band (Euclid provided)',
`pan_starrs_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS z-band (Euclid provided)',
`pan_starrs_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS z-band (Euclid provided)',
`sdss_r01_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in sdss r-band redshifted to z=0.1',
`sdss_r01_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in sdss r-band redshifted to z=0.1',
`subaru_hsc_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`subaru_hsc_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`dominant_shape` tinyint COMMENT 'flag to define whether the galaxy is BULGE-dominated (0) or DISK-dominated (1)',
`median_major_axis` float COMMENT 'median semi-major axis exponential scalength. Profile defined as: I = I_o x exp (-radius/scalelength)',
`scale_length` float COMMENT 'disc and bulge scalelength prior',
`bulge_fraction` float COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)',
`disk_scalelength` float COMMENT 'scalength of the disk, profile defined as: I = I_o x exp (-radius/disk_scalelength)',
`disk_nsersic` float COMMENT 'Sersic index of the disk component',
`disk_r50` float COMMENT 'half light radius of the disk. For an exponential profile (or Sersec profile with index n=1), disk_r50 = disk_scalelength * 1.678',
`bulge_r50` float COMMENT 'half light radius of the bulge',
`bulge_nsersic` float COMMENT 'Sersic index of the bulge component',
`inclination_angle` float COMMENT 'Galaxy inclination angle (where 0 degrees = face-on and 90 degrees = edge-on). Galaxy ellipticity for disk and bulge components are computed following the recipe in https://euclid.roe.ac.uk/projects/sgsshear/wiki/SHE-SIM',
`disk_ellipticity` float COMMENT 'ellipticity of the disk defined as disk_ellipticity = (1 - disk_axis_ratio) / (1 + disk_axis_ratio)',
`bulge_ellipticity` float COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)',
`disk_axis_ratio` float COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`bulge_axis_ratio` float COMMENT 'bulge projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`halo_x` float COMMENT 'halo comoving x coordinate (Mpc/h)',
`halo_y` float COMMENT 'halo comoving y coordinate (Mpc/h)',
`halo_z` float COMMENT 'halo comoving z coordinate (Mpc/h)',
`halo_vx` float COMMENT 'halo x-centre velocity (km/h)',
`halo_vy` float COMMENT 'halo y-centre velocity (km/h)',
`halo_vz` float COMMENT 'halo z-centre velocity (km/h)',
`halo_r` float COMMENT 'halo comoving distance (Mpc/h)',
`true_redshift_halo` float COMMENT 'true redshift of the host halo',
`halo_lm` float COMMENT 'log10 of the FoF halo mass in Msum/h. This halo mass is computed as the particule mass multiplied by the number of particles of the FoF halo. At low masses it is interpolated to obtain a smooth distribution of halo masses',
`halo_n_sats` int COMMENT 'number of satellite galaxies from HOD',
`n_gals` int COMMENT 'DEPRECATED',
`luminosity_r01_evolved` float COMMENT 'missing comment',
step smallint
)
CLUSTERED BY (
step, hpix_13_nest)
SORTED BY (
step ASC, hpix_13_nest ASC)
INTO 4096 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_2_s_sc8_c SELECT halo_id, galaxy_id, kind, random_index, ra_gal, dec_gal, ra_mag_gal, dec_mag_gal, kappa, gamma1, gamma2, defl1, defl2, l_gal, b_gal, mw_extinction, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, observed_redshift_gal, hpix_29_nest, hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, abs_mag_r01, luminosity_r01, abs_mag_r01_evolved, sdss_r01, abs_mag_uv_dereddened, gr_restframe, color_kind, sed_ke, gr_cosmos, sed_cosmos, is_within_cosmos, cosmos_distance, ext_curve_cosmos, ebv_cosmos, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha, logf_halpha_ext, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_h_odonnell_ext, 2mass_j_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_r_odonnell_ext, cfht_megacam_u_odonnell_ext, euclid_nisp_h_odonnell_ext, euclid_nisp_j_odonnell_ext, euclid_nisp_y_odonnell_ext, euclid_vis_odonnell_ext, gaia_bp_odonnell_ext, gaia_g_odonnell_ext, gaia_rp_odonnell_ext, jst_jpcam_g_odonnell_ext, kids_g_odonnell_ext, kids_i_odonnell_ext, kids_r_odonnell_ext, kids_u_odonnell_ext, lsst_g_odonnell_ext, lsst_i_odonnell_ext, lsst_r_odonnell_ext, lsst_u_odonnell_ext, lsst_y_odonnell_ext, lsst_z_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, sdss_r01_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_i_el_model3_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_y_el_model3_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_z_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext, dominant_shape, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, luminosity_r01_evolved, step FROM jcarrete.flagship_mock_1_10_2_s_sc8_pq;
ANALYZE TABLE cosmohub.flagship_mock_1_10_2_s_sc8_c COMPUTE STATISTICS FOR COLUMNS;
CREATE TABLE jcarrete.dup_flagship_mock_1_10_2_s_sc8_c
STORED AS ORC AS
SELECT t.* FROM (
SELECT halo_id, galaxy_id, kind, ra_gal, dec_gal, true_redshift_gal,
COUNT(*) OVER (PARTITION BY ra_gal, dec_gal) AS cnt
FROM cosmohub.flagship_mock_1_10_2_s_sc8_c AS s
) AS t
WHERE t.cnt > 1;
SELECT halo_id, COUNT(*) as count FROM jcarrete.dup_flagship_mock_1_10_2_s_sc8_c GROUP BY halo_id ORDER BY count DESC LIMIT 10;
RA, DEC, redshift
CREATE TABLE jcarrete.dup_3d_flagship_mock_1_10_2_s_sc8_c
STORED AS ORC AS
SELECT t.* FROM (
SELECT halo_id, galaxy_id, kind, ra_gal, dec_gal, true_redshift_gal,
COUNT(*) OVER (PARTITION BY ra_gal, dec_gal, true_redshift_gal) AS cnt
FROM cosmohub.flagship_mock_1_10_2_s_sc8_c AS s
) AS t
WHERE t.cnt > 1;
Dark matter halo input catalog from flagship_rockstar_octant1_c, which is the Flagship dark matter halo catalog v1.1 with correct velocities.
The catalog simulates Scientific Challenge 8 region (see the following link for more details:[1](https://euclid.roe.ac.uk/projects/sgv/wiki/SC8_FoV)).
Improvements:
- Shape distributions have been updated from GSIR release. See the following [url](https://docs.google.com/document/d/1FRERcl5Lv2IDB6ZChTK6gcEHI2FWA9gSgYKcaDUA9EM/edit?usp=sharing) for more details.
- New deflection maps for magnified positions
- Duplicated galaxy positions issue is fixed
Note that **we provide fluxes instead of magnitudes**. In order **to get magnitudes** you can enter in the "Expert mode" and estimate the magnitude as follows:
m = -2.5 * log10(flux) - 48.6
And in order to estimate magnified magnitudes:
m_mag = m + 2.5 * log10((1-kappa)*(1-kappa) - gamma1 * gamma1 - gamma2 * gamma2)
Missing disk_angle field:
CREATE TABLE cosmohub.flagship_mock_1_10_3_s_sc8_c(
`halo_id` bigint COMMENT 'unique halo id coming from the Flagship dark matter halo catalog',
`galaxy_id` int COMMENT 'combined with the unique_halo_id they uniquely identify each galaxy',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`random_index` float COMMENT 'random number [0 - 1) for subsampling',
`ra_gal` float COMMENT 'galaxy right ascension (degrees)',
`dec_gal` float COMMENT 'galaxy declination (degrees)',
`ra_mag_gal` float COMMENT 'galaxy magnified right ascension (degree)',
`dec_mag_gal` float COMMENT 'galaxy magnified declination (degree)',
`kappa` float COMMENT 'convergence',
`gamma1` float COMMENT 'shear',
`gamma2` float COMMENT 'shear',
`defl1` float COMMENT 'deflection1',
`defl2` float COMMENT 'deflection2',
`l_gal` float COMMENT 'galactic longitude (degrees)',
`b_gal` float COMMENT 'galactic latitude (degrees)',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`x_gal` float COMMENT 'galaxy comoving x coordinate (Mpc/h)',
`y_gal` float COMMENT 'galaxy comoving y coordinate (Mpc/h)',
`z_gal` float COMMENT 'galaxy comoving z coordinate (Mpc/h)',
`r_gal` float COMMENT 'galaxy comoving distance (Mpc/h)',
`true_redshift_gal` float COMMENT 'true galaxy redshift',
`observed_redshift_gal` float COMMENT 'observed galaxy redshift (including peculiar velocity)',
`hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format',
`hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format',
`vx_gal` float COMMENT 'physical (peculiar) velocity in x-component (km/s)',
`vy_gal` float COMMENT 'physical (peculiar) velocity in y-component (km/s)',
`vz_gal` float COMMENT 'physical (peculiar) velocity in z-component (km/s)',
`vrad_gal` float COMMENT 'missing comment',
`delta_r` float COMMENT 'missing comment',
`abs_mag_r01` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h)',
`luminosity_r01` float COMMENT 'luminosity in the 0.1-r-band',
`abs_mag_r01_evolved` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h) including MICECATv2.0 magnitude evolution',
`sdss_r01` float COMMENT 'observed flux in sdss r-band redshifted to z=0.1',
`abs_mag_uv_dereddened` float COMMENT 'absolute magnitude ultraviolet',
`gr_restframe` float COMMENT '(g-r) rest-frame at z=0',
`color_kind` tinyint COMMENT 'galaxy color kind: 0=red sequence 1=green valley 2=blue cloud',
`sed_ke` float COMMENT 'K-correction for each SED',
`gr_cosmos` float COMMENT 'interpolated COSMOS g-r color',
`sed_cosmos` float COMMENT 'interpolated basic COSMOS SED [0 30]',
`is_within_cosmos` boolean COMMENT 'missing comment',
`cosmos_distance` float COMMENT 'missing comment',
`ext_curve_cosmos` tinyint COMMENT 'COSMOS extinction curve [0-4]',
`ebv_cosmos` float COMMENT 'interpolated extinction strength for COSMOS extinction curve',
`log_ml_r01` float COMMENT 'logarithm of stellar_mass/luminosity ratio in the r01 SDSS band',
`log_stellar_mass` float COMMENT 'logarithm of the stellar mass (Msun/h)',
`metallicity` float COMMENT 'oxygen metallicity in units 12+logO/H',
`log_sfr` float COMMENT 'logarithm of star formation rate in (Msun/h)/year',
`logf_halpha` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: DOES NOT include extinction)',
`logf_halpha_ext` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: includes extinction)',
`halpha_scatter` float COMMENT 'missing comment',
`loglum_halpha` float COMMENT 'DEPRECATED logarithm of Halpha luminosity (erg/s/h^2) using empirical relation with UV magnitude (DOES NOT include extinction)',
`loglum_halpha_ext` float COMMENT 'missing comment',
`logf_dummy` float COMMENT 'missing comment',
`z_dummy` float COMMENT 'missing comment',
`logf_halpha_model1_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: includes extinction)',
`logf_halpha_model1` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: DOES NOT include extinction)',
`loglum_halpha_model1_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (includes extinction)',
`loglum_halpha_model1` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (DOES NOT include extinction)',
`logf_halpha_model3_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: includes extinction)',
`logf_halpha_model3` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: DOES NOT include extinction)',
`loglum_halpha_model3_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (includes extinction)',
`loglum_halpha_model3` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (DOES NOT include extinction)',
`logf_hbeta_model1_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_hbeta_model1` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_o2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_o2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_n2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_n2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_o3_model1_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_o3_model1` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_s2_model1_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_s2_model1` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_hbeta_model3_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_hbeta_model3` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_o2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_o2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_n2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_n2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_o3_model3_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_o3_model3` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_s2_model3_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_s2_model3` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`2mass_h` float COMMENT 'observed flux in 2MASS H-band (Euclid provided)',
`2mass_j` float COMMENT 'observed flux in 2MASS J-band (Euclid provided)',
`2mass_ks` float COMMENT 'observed flux in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'observed flux in DES g-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'observed flux in DES i-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'observed flux in DES r-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'observed flux in DES z-band (Euclid provided)',
`cfht_megacam_r` float COMMENT 'observed flux in CFHT r-band (Euclid provided)',
`cfht_megacam_u` float COMMENT 'observed flux in CFHT u-band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_vis` float COMMENT 'observed flux in Euclid VIS band (Euclid provided)',
`gaia_bp` float COMMENT 'observed flux in Gaia-BP band (Euclid provided)',
`gaia_g` float COMMENT 'observed flux in Gaia-G band (Euclid provided)',
`gaia_rp` float COMMENT 'observed flux in Gaia-RP band (Euclid provided)',
`jst_jpcam_g` float COMMENT 'observed flux in JST g-band (Euclid provided)',
`kids_g` float COMMENT 'observed flux in KIDS g-band (Euclid provided)',
`kids_i` float COMMENT 'observed flux in KIDS i-band (Euclid provided)',
`kids_r` float COMMENT 'observed flux in KIDS r-band (Euclid provided)',
`kids_u` float COMMENT 'observed flux in KIDS u-band (Euclid provided)',
`lsst_g` float COMMENT 'observed flux in LSST g-band (Euclid provided)',
`lsst_i` float COMMENT 'observed flux in LSST i-band (Euclid provided)',
`lsst_r` float COMMENT 'observed flux in LSST r-band (Euclid provided)',
`lsst_u` float COMMENT 'observed flux in LSST u-band (Euclid provided)',
`lsst_y` float COMMENT 'observed flux in LSST Y-band (Euclid provided)',
`lsst_z` float COMMENT 'observed flux in LSST z-band (Euclid provided)',
`pan_starrs_i` float COMMENT 'observed flux in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z` float COMMENT 'observed flux in Pan-STARRS z-band (Euclid provided)',
`subaru_hsc_z` float COMMENT 'observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`2mass_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS H-band (Euclid provided)',
`2mass_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES z-band (Euclid provided)',
`cfht_megacam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT u-band (Euclid provided)',
`euclid_nisp_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid VIS band (Euclid provided)',
`gaia_bp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-RP band (Euclid provided)',
`jst_jpcam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in JST g-band (Euclid provided)',
`kids_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS g-band (Euclid provided)',
`kids_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS i-band (Euclid provided)',
`kids_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS r-band (Euclid provided)',
`kids_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST g-band (Euclid provided)',
`lsst_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST i-band (Euclid provided)',
`lsst_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST r-band (Euclid provided)',
`lsst_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST u-band (Euclid provided)',
`lsst_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST Y-band (Euclid provided)',
`lsst_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST z-band (Euclid provided)',
`pan_starrs_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS z-band (Euclid provided)',
`sdss_r01_odonnell_ext` float COMMENT 'observed flux in sdss r-band redshifted to z=0.1 with MW extinction',
`subaru_hsc_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`2mass_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction 2MASS H-band (Euclid provided)',
`2mass_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction 2MASS H-band (Euclid provided)',
`2mass_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS Ks-band (Euclid provided)',
`2mass_ks_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES z-band (Euclid provided)',
`blanco_decam_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES z-band (Euclid provided)',
`cfht_megacam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT u-band (Euclid provided)',
`cfht_megacam_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT u-band (Euclid provided)',
`euclid_nisp_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid VIS band (Euclid provided)',
`euclid_vis_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid VIS band (Euclid provided)',
`gaia_bp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_bp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-G band (Euclid provided)',
`gaia_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-RP band (Euclid provided)',
`gaia_rp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-RP band (Euclid provided)',
`jst_jpcam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in JST g-band (Euclid provided)',
`jst_jpcam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in JST g-band (Euclid provided)',
`kids_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS g-band (Euclid provided)',
`kids_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS g-band (Euclid provided)',
`kids_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS i-band (Euclid provided)',
`kids_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS i-band (Euclid provided)',
`kids_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS r-band (Euclid provided)',
`kids_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS r-band (Euclid provided)',
`kids_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS u-band (Euclid provided)',
`kids_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST g-band (Euclid provided)',
`lsst_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST g-band (Euclid provided)',
`lsst_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST i-band (Euclid provided)',
`lsst_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST i-band (Euclid provided)',
`lsst_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST r-band (Euclid provided)',
`lsst_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST r-band (Euclid provided)',
`lsst_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST u-band (Euclid provided)',
`lsst_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST u-band (Euclid provided)',
`lsst_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST Y-band (Euclid provided)',
`lsst_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST Y-band (Euclid provided)',
`lsst_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST z-band (Euclid provided)',
`lsst_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST z-band (Euclid provided)',
`pan_starrs_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS z-band (Euclid provided)',
`pan_starrs_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS z-band (Euclid provided)',
`sdss_r01_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in sdss r-band redshifted to z=0.1',
`sdss_r01_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in sdss r-band redshifted to z=0.1',
`subaru_hsc_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`subaru_hsc_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`dominant_shape` tinyint COMMENT 'flag to define whether the galaxy is BULGE-dominated (0) or DISK-dominated (1)',
`disk_angle`float COMMENT 'position of the disk rotation axis (degrees). TU Galaxy stamps convention for OU-SIM: the standard is to set the position angle from North to East, with the major axis aligned in Declination',
`median_major_axis` float COMMENT 'median semi-major axis exponential scalength. Profile defined as: I = I_o x exp (-radius/scalelength)',
`scale_length` float COMMENT 'disc and bulge scalelength prior',
`bulge_fraction` float COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)',
`disk_scalelength` float COMMENT 'scalength of the disk, profile defined as: I = I_o x exp (-radius/disk_scalelength)',
`disk_nsersic` float COMMENT 'Sersic index of the disk component',
`disk_r50` float COMMENT 'half light radius of the disk. For an exponential profile (or Sersec profile with index n=1), disk_r50 = disk_scalelength * 1.678',
`bulge_r50` float COMMENT 'half light radius of the bulge',
`bulge_nsersic` float COMMENT 'Sersic index of the bulge component',
`inclination_angle` float COMMENT 'Galaxy inclination angle (where 0 degrees = face-on and 90 degrees = edge-on). Galaxy ellipticity for disk and bulge components are computed following the recipe in https://euclid.roe.ac.uk/projects/sgsshear/wiki/SHE-SIM',
`disk_ellipticity` float COMMENT 'ellipticity of the disk defined as disk_ellipticity = (1 - disk_axis_ratio) / (1 + disk_axis_ratio)',
`bulge_ellipticity` float COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)',
`disk_axis_ratio` float COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`bulge_axis_ratio` float COMMENT 'bulge projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`halo_x` float COMMENT 'halo comoving x coordinate (Mpc/h)',
`halo_y` float COMMENT 'halo comoving y coordinate (Mpc/h)',
`halo_z` float COMMENT 'halo comoving z coordinate (Mpc/h)',
`halo_vx` float COMMENT 'halo x-centre velocity (km/h)',
`halo_vy` float COMMENT 'halo y-centre velocity (km/h)',
`halo_vz` float COMMENT 'halo z-centre velocity (km/h)',
`halo_r` float COMMENT 'halo comoving distance (Mpc/h)',
`true_redshift_halo` float COMMENT 'true redshift of the host halo',
`halo_lm` float COMMENT 'log10 of the FoF halo mass in Msum/h. This halo mass is computed as the particule mass multiplied by the number of particles of the FoF halo. At low masses it is interpolated to obtain a smooth distribution of halo masses',
`halo_n_sats` int COMMENT 'number of satellite galaxies from HOD',
`n_gals` int COMMENT 'DEPRECATED',
`luminosity_r01_evolved` float COMMENT 'missing comment',
step smallint
)
CLUSTERED BY (
step, hpix_13_nest)
SORTED BY (
step ASC, hpix_13_nest ASC)
INTO 4096 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_3_s_sc8_c SELECT halo_id, galaxy_id, kind, random_index, ra_gal, dec_gal, ra_mag_gal, dec_mag_gal, kappa, gamma1, gamma2, defl1, defl2, l_gal, b_gal, mw_extinction, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, observed_redshift_gal, hpix_29_nest, hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, abs_mag_r01, luminosity_r01, abs_mag_r01_evolved, sdss_r01, abs_mag_uv_dereddened, gr_restframe, color_kind, sed_ke, gr_cosmos, sed_cosmos, is_within_cosmos, cosmos_distance, ext_curve_cosmos, ebv_cosmos, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha, logf_halpha_ext, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_h_odonnell_ext, 2mass_j_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_r_odonnell_ext, cfht_megacam_u_odonnell_ext, euclid_nisp_h_odonnell_ext, euclid_nisp_j_odonnell_ext, euclid_nisp_y_odonnell_ext, euclid_vis_odonnell_ext, gaia_bp_odonnell_ext, gaia_g_odonnell_ext, gaia_rp_odonnell_ext, jst_jpcam_g_odonnell_ext, kids_g_odonnell_ext, kids_i_odonnell_ext, kids_r_odonnell_ext, kids_u_odonnell_ext, lsst_g_odonnell_ext, lsst_i_odonnell_ext, lsst_r_odonnell_ext, lsst_u_odonnell_ext, lsst_y_odonnell_ext, lsst_z_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, sdss_r01_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_i_el_model3_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_y_el_model3_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_z_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext, dominant_shape, (rand()*360)%360, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, luminosity_r01_evolved, step FROM cosmohub.flagship_mock_1_10_2_s_sc8_c;
ANALYZE TABLE cosmohub.flagship_mock_1_10_3_s_sc8_c COMPUTE STATISTICS FOR COLUMNS;
Fits files in CosmoHub:
{
"sql": "SELECT CAST(((halo_id * 10000) + galaxy_id) AS bigint) AS SOURCE_ID, \nCAST(halo_id AS bigint) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra_gal AS double) AS RA, \nCAST(dec_gal AS double) AS `DEC`, \nCAST(ra_mag_gal AS double) AS RA_MAG, \nCAST(dec_mag_gal AS double) AS DEC_MAG, \nCAST(observed_redshift_gal AS float) AS Z_OBS, \nCAST(abs_mag_r01_evolved AS float) AS REF_MAG_ABS, \nCAST(-2.5*log10(sdss_r01) - 48.6 AS float) AS REF_MAG, \nCAST(bulge_fraction AS float) AS BULGE_FRACTION, \nCAST(bulge_r50 AS float) AS BULGE_R50, \nCAST(disk_r50 AS float) AS DISK_R50, \nCAST(bulge_nsersic AS float) AS BULGE_NSERSIC, \nCAST(bulge_axis_ratio AS float) AS BULGE_AXIS_RATIO, \nCAST(inclination_angle AS float) AS INCLINATION_ANGLE, \nCAST(disk_angle AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(sed_cosmos AS float) AS SED_TEMPLATE, \nCAST(ROUND(ext_curve_cosmos) AS smallint) AS EXT_LAW, \nCAST(ebv_cosmos AS float) AS EBV, \nCAST(logf_halpha_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS HALPHA_LOGFLAM_EXT_MAG, \nCAST(logf_hbeta_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS HBETA_LOGFLAM_EXT_MAG, \nCAST(logf_o2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS O2_LOGFLAM_EXT_MAG, \nCAST(logf_o3_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS O3_LOGFLAM_EXT_MAG, \nCAST(logf_n2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS N2_LOGFLAM_EXT_MAG, \nCAST(logf_s2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS S2_LOGFLAM_EXT_MAG, \nCAST(mw_extinction AS float) AS AV,\nCAST(euclid_vis_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, \nCAST(euclid_nisp_y_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, \nCAST(euclid_nisp_j_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, \nCAST(euclid_nisp_h_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, \nCAST(blanco_decam_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, \nCAST(blanco_decam_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, \nCAST(blanco_decam_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, \nCAST(blanco_decam_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, \nCAST(cfht_megacam_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, \nCAST(cfht_megacam_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, \nCAST(jst_jpcam_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, \nCAST(pan_starrs_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, \nCAST(pan_starrs_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, \nCAST(subaru_hsc_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, \nCAST(gaia_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, \nCAST(gaia_bp_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, \nCAST(gaia_rp_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, \nCAST(lsst_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, \nCAST(lsst_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, \nCAST(lsst_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, \nCAST(lsst_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, \nCAST(lsst_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, \nCAST(lsst_y_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, \nCAST(kids_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, \nCAST(kids_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, \nCAST(kids_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, \nCAST(kids_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, \nCAST(2mass_j_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, \nCAST(2mass_h_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, \nCAST(2mass_ks_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG \nFROM cosmohub.flagship_mock_1_10_3_s_sc8_c \nWHERE (logf_halpha_model3_ext > -16 OR -2.5*log10(euclid_nisp_h) - 48.6 < 26) \nAND disk_axis_ratio > 0.10865 \nAND SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063"
}
Test para las emission lines. Some NaNs found. Issue opened in Euclid Redmine:
SELECT COUNT(*) FROM cosmohub.flagship_mock_1_10_3_s_sc8_c WHERE (logf_halpha IS NULL) OR (logf_halpha_ext IS NULL) OR (halpha_scatter IS NULL) OR (loglum_halpha IS NULL) OR (loglum_halpha_ext IS NULL) OR (logf_dummy IS NULL) OR (z_dummy IS NULL) OR (logf_halpha_model1_ext IS NULL) OR (logf_halpha_model1 IS NULL) OR (loglum_halpha_model1_ext IS NULL) OR (loglum_halpha_model1 IS NULL) OR (logf_halpha_model3_ext IS NULL) OR (logf_halpha_model3 IS NULL) OR (loglum_halpha_model3_ext IS NULL) OR (loglum_halpha_model3 IS NULL) OR (logf_hbeta_model1_ext IS NULL) OR (logf_hbeta_model1 IS NULL) OR (logf_o2_model1_ext IS NULL) OR (logf_o2_model1 IS NULL) OR (logf_n2_model1_ext IS NULL) OR (logf_n2_model1 IS NULL) OR (logf_o3_model1_ext IS NULL) OR (logf_o3_model1 IS NULL) OR (logf_s2_model1_ext IS NULL) OR (logf_s2_model1 IS NULL) OR (logf_hbeta_model3_ext IS NULL) OR (logf_hbeta_model3 IS NULL) OR (logf_o2_model3_ext IS NULL) OR (logf_o2_model3 IS NULL) OR (logf_n2_model3_ext IS NULL) OR (logf_n2_model3 IS NULL) OR (logf_o3_model3_ext IS NULL) OR (logf_o3_model3 IS NULL) OR (logf_s2_model3_ext IS NULL) OR (logf_s2_model3 IS NULL);
SELECT COUNT(*) FROM cosmohub.flagship_mock_1_10_3_s_sc8_c WHERE (2mass_h < 0) OR ( 2mass_j < 0) OR ( 2mass_ks < 0) OR ( blanco_decam_g < 0) OR ( blanco_decam_i < 0) OR ( blanco_decam_r < 0) OR ( blanco_decam_z < 0) OR ( cfht_megacam_r < 0) OR ( cfht_megacam_u < 0) OR ( euclid_nisp_h < 0) OR ( euclid_nisp_j < 0) OR ( euclid_nisp_y < 0) OR ( euclid_vis < 0) OR ( gaia_bp < 0) OR ( gaia_g < 0) OR ( gaia_rp < 0) OR ( jst_jpcam_g < 0) OR ( kids_g < 0) OR ( kids_i < 0) OR ( kids_r < 0) OR ( kids_u < 0) OR ( lsst_g < 0) OR ( lsst_i < 0) OR ( lsst_r < 0) OR ( lsst_u < 0) OR ( lsst_y < 0) OR ( lsst_z < 0) OR ( pan_starrs_i < 0) OR ( pan_starrs_z < 0) OR ( subaru_hsc_z < 0) OR ( 2mass_h_odonnell_ext < 0) OR ( 2mass_j_odonnell_ext < 0) OR ( 2mass_ks_odonnell_ext < 0) OR ( blanco_decam_g_odonnell_ext < 0) OR ( blanco_decam_i_odonnell_ext < 0) OR ( blanco_decam_r_odonnell_ext < 0) OR ( blanco_decam_z_odonnell_ext < 0) OR ( cfht_megacam_r_odonnell_ext < 0) OR ( cfht_megacam_u_odonnell_ext < 0) OR ( euclid_nisp_h_odonnell_ext < 0) OR ( euclid_nisp_j_odonnell_ext < 0) OR ( euclid_nisp_y_odonnell_ext < 0) OR ( euclid_vis_odonnell_ext < 0) OR ( gaia_bp_odonnell_ext < 0) OR ( gaia_g_odonnell_ext < 0) OR ( gaia_rp_odonnell_ext < 0) OR ( jst_jpcam_g_odonnell_ext < 0) OR ( kids_g_odonnell_ext < 0) OR ( kids_i_odonnell_ext < 0) OR ( kids_r_odonnell_ext < 0) OR ( kids_u_odonnell_ext < 0) OR ( lsst_g_odonnell_ext < 0) OR ( lsst_i_odonnell_ext < 0) OR ( lsst_r_odonnell_ext < 0) OR ( lsst_u_odonnell_ext < 0) OR ( lsst_y_odonnell_ext < 0) OR ( lsst_z_odonnell_ext < 0) OR ( pan_starrs_i_odonnell_ext < 0) OR ( pan_starrs_z_odonnell_ext < 0) OR ( sdss_r01_odonnell_ext < 0) OR ( subaru_hsc_z_odonnell_ext < 0) OR ( 2mass_h_el_model1_odonnell_ext < 0) OR ( 2mass_h_el_model3_odonnell_ext < 0) OR ( 2mass_j_el_model1_odonnell_ext < 0) OR ( 2mass_j_el_model3_odonnell_ext < 0) OR ( 2mass_ks_el_model1_odonnell_ext < 0) OR ( 2mass_ks_el_model3_odonnell_ext < 0) OR ( blanco_decam_g_el_model1_odonnell_ext < 0) OR ( blanco_decam_g_el_model3_odonnell_ext < 0) OR ( blanco_decam_i_el_model1_odonnell_ext < 0) OR ( blanco_decam_i_el_model3_odonnell_ext < 0) OR ( blanco_decam_r_el_model1_odonnell_ext < 0) OR ( blanco_decam_r_el_model3_odonnell_ext < 0) OR ( blanco_decam_z_el_model1_odonnell_ext < 0) OR ( blanco_decam_z_el_model3_odonnell_ext < 0) OR ( cfht_megacam_r_el_model1_odonnell_ext < 0) OR ( cfht_megacam_r_el_model3_odonnell_ext < 0) OR ( cfht_megacam_u_el_model1_odonnell_ext < 0) OR ( cfht_megacam_u_el_model3_odonnell_ext < 0) OR ( euclid_nisp_h_el_model1_odonnell_ext < 0) OR ( euclid_nisp_h_el_model3_odonnell_ext < 0) OR ( euclid_nisp_j_el_model1_odonnell_ext < 0) OR ( euclid_nisp_j_el_model3_odonnell_ext < 0) OR ( euclid_nisp_y_el_model1_odonnell_ext < 0) OR ( euclid_nisp_y_el_model3_odonnell_ext < 0) OR ( euclid_vis_el_model1_odonnell_ext < 0) OR ( euclid_vis_el_model3_odonnell_ext < 0) OR ( gaia_bp_el_model1_odonnell_ext < 0) OR ( gaia_bp_el_model3_odonnell_ext < 0) OR ( gaia_g_el_model1_odonnell_ext < 0) OR ( gaia_g_el_model3_odonnell_ext < 0) OR ( gaia_rp_el_model1_odonnell_ext < 0) OR ( gaia_rp_el_model3_odonnell_ext < 0) OR ( jst_jpcam_g_el_model1_odonnell_ext < 0) OR ( jst_jpcam_g_el_model3_odonnell_ext < 0) OR ( kids_g_el_model1_odonnell_ext < 0) OR ( kids_g_el_model3_odonnell_ext < 0) OR ( kids_i_el_model1_odonnell_ext < 0) OR ( kids_i_el_model3_odonnell_ext < 0) OR ( kids_r_el_model1_odonnell_ext < 0) OR ( kids_r_el_model3_odonnell_ext < 0) OR ( kids_u_el_model1_odonnell_ext < 0) OR ( kids_u_el_model3_odonnell_ext < 0) OR ( lsst_g_el_model1_odonnell_ext < 0) OR ( lsst_g_el_model3_odonnell_ext < 0) OR ( lsst_i_el_model1_odonnell_ext < 0) OR ( lsst_i_el_model3_odonnell_ext < 0) OR ( lsst_r_el_model1_odonnell_ext < 0) OR ( lsst_r_el_model3_odonnell_ext < 0) OR ( lsst_u_el_model1_odonnell_ext < 0) OR ( lsst_u_el_model3_odonnell_ext < 0) OR ( lsst_y_el_model1_odonnell_ext < 0) OR ( lsst_y_el_model3_odonnell_ext < 0) OR ( lsst_z_el_model1_odonnell_ext < 0) OR ( lsst_z_el_model3_odonnell_ext < 0) OR ( pan_starrs_i_el_model1_odonnell_ext < 0) OR ( pan_starrs_i_el_model3_odonnell_ext < 0) OR ( pan_starrs_z_el_model1_odonnell_ext < 0) OR ( pan_starrs_z_el_model3_odonnell_ext < 0) OR ( sdss_r01_el_model1_odonnell_ext < 0) OR ( sdss_r01_el_model3_odonnell_ext < 0) OR ( subaru_hsc_z_el_model1_odonnell_ext < 0) OR (subaru_hsc_z_el_model3_odonnell_ext < 0);
Pruebas del catálogo anterior GSIR: flagship_mock_1_9_14_swf1_swf2_deep_swf3_s
FLUJOS NEGATIVOS:
SELECT COUNT(*) FROM cosmohub.flagship_mock_1_9_14_swf1_swf2_deep_swf3_s WHERE (2mass_h < 0) OR ( 2mass_j < 0) OR ( 2mass_ks < 0) OR ( blanco_decam_g < 0) OR ( blanco_decam_i < 0) OR ( blanco_decam_r < 0) OR ( blanco_decam_z < 0) OR ( cfht_megacam_r < 0) OR ( cfht_megacam_u < 0) OR ( euclid_nisp_h < 0) OR ( euclid_nisp_j < 0) OR ( euclid_nisp_y < 0) OR ( euclid_vis < 0) OR ( gaia_bp < 0) OR ( gaia_g < 0) OR ( gaia_rp < 0) OR ( jst_jpcam_g < 0) OR ( kids_g < 0) OR ( kids_i < 0) OR ( kids_r < 0) OR ( kids_u < 0) OR ( lsst_g < 0) OR ( lsst_i < 0) OR ( lsst_r < 0) OR ( lsst_u < 0) OR ( lsst_y < 0) OR ( lsst_z < 0) OR ( pan_starrs_i < 0) OR ( pan_starrs_z < 0) OR ( subaru_hsc_z < 0) OR ( 2mass_h_odonnell_ext < 0) OR ( 2mass_j_odonnell_ext < 0) OR ( 2mass_ks_odonnell_ext < 0) OR ( blanco_decam_g_odonnell_ext < 0) OR ( blanco_decam_i_odonnell_ext < 0) OR ( blanco_decam_r_odonnell_ext < 0) OR ( blanco_decam_z_odonnell_ext < 0) OR ( cfht_megacam_r_odonnell_ext < 0) OR ( cfht_megacam_u_odonnell_ext < 0) OR ( euclid_nisp_h_odonnell_ext < 0) OR ( euclid_nisp_j_odonnell_ext < 0) OR ( euclid_nisp_y_odonnell_ext < 0) OR ( euclid_vis_odonnell_ext < 0) OR ( gaia_bp_odonnell_ext < 0) OR ( gaia_g_odonnell_ext < 0) OR ( gaia_rp_odonnell_ext < 0) OR ( jst_jpcam_g_odonnell_ext < 0) OR ( kids_g_odonnell_ext < 0) OR ( kids_i_odonnell_ext < 0) OR ( kids_r_odonnell_ext < 0) OR ( kids_u_odonnell_ext < 0) OR ( lsst_g_odonnell_ext < 0) OR ( lsst_i_odonnell_ext < 0) OR ( lsst_r_odonnell_ext < 0) OR ( lsst_u_odonnell_ext < 0) OR ( lsst_y_odonnell_ext < 0) OR ( lsst_z_odonnell_ext < 0) OR ( pan_starrs_i_odonnell_ext < 0) OR ( pan_starrs_z_odonnell_ext < 0) OR ( sdss_r01_odonnell_ext < 0) OR ( subaru_hsc_z_odonnell_ext < 0) OR ( 2mass_h_el_model1_odonnell_ext < 0) OR ( 2mass_h_el_model3_odonnell_ext < 0) OR ( 2mass_j_el_model1_odonnell_ext < 0) OR ( 2mass_j_el_model3_odonnell_ext < 0) OR ( 2mass_ks_el_model1_odonnell_ext < 0) OR ( 2mass_ks_el_model3_odonnell_ext < 0) OR ( blanco_decam_g_el_model1_odonnell_ext < 0) OR ( blanco_decam_g_el_model3_odonnell_ext < 0) OR ( blanco_decam_i_el_model1_odonnell_ext < 0) OR ( blanco_decam_i_el_model3_odonnell_ext < 0) OR ( blanco_decam_r_el_model1_odonnell_ext < 0) OR ( blanco_decam_r_el_model3_odonnell_ext < 0) OR ( blanco_decam_z_el_model1_odonnell_ext < 0) OR ( blanco_decam_z_el_model3_odonnell_ext < 0) OR ( cfht_megacam_r_el_model1_odonnell_ext < 0) OR ( cfht_megacam_r_el_model3_odonnell_ext < 0) OR ( cfht_megacam_u_el_model1_odonnell_ext < 0) OR ( cfht_megacam_u_el_model3_odonnell_ext < 0) OR ( euclid_nisp_h_el_model1_odonnell_ext < 0) OR ( euclid_nisp_h_el_model3_odonnell_ext < 0) OR ( euclid_nisp_j_el_model1_odonnell_ext < 0) OR ( euclid_nisp_j_el_model3_odonnell_ext < 0) OR ( euclid_nisp_y_el_model1_odonnell_ext < 0) OR ( euclid_nisp_y_el_model3_odonnell_ext < 0) OR ( euclid_vis_el_model1_odonnell_ext < 0) OR ( euclid_vis_el_model3_odonnell_ext < 0) OR ( gaia_bp_el_model1_odonnell_ext < 0) OR ( gaia_bp_el_model3_odonnell_ext < 0) OR ( gaia_g_el_model1_odonnell_ext < 0) OR ( gaia_g_el_model3_odonnell_ext < 0) OR ( gaia_rp_el_model1_odonnell_ext < 0) OR ( gaia_rp_el_model3_odonnell_ext < 0) OR ( jst_jpcam_g_el_model1_odonnell_ext < 0) OR ( jst_jpcam_g_el_model3_odonnell_ext < 0) OR ( kids_g_el_model1_odonnell_ext < 0) OR ( kids_g_el_model3_odonnell_ext < 0) OR ( kids_i_el_model1_odonnell_ext < 0) OR ( kids_i_el_model3_odonnell_ext < 0) OR ( kids_r_el_model1_odonnell_ext < 0) OR ( kids_r_el_model3_odonnell_ext < 0) OR ( kids_u_el_model1_odonnell_ext < 0) OR ( kids_u_el_model3_odonnell_ext < 0) OR ( lsst_g_el_model1_odonnell_ext < 0) OR ( lsst_g_el_model3_odonnell_ext < 0) OR ( lsst_i_el_model1_odonnell_ext < 0) OR ( lsst_i_el_model3_odonnell_ext < 0) OR ( lsst_r_el_model1_odonnell_ext < 0) OR ( lsst_r_el_model3_odonnell_ext < 0) OR ( lsst_u_el_model1_odonnell_ext < 0) OR ( lsst_u_el_model3_odonnell_ext < 0) OR ( lsst_y_el_model1_odonnell_ext < 0) OR ( lsst_y_el_model3_odonnell_ext < 0) OR ( lsst_z_el_model1_odonnell_ext < 0) OR ( lsst_z_el_model3_odonnell_ext < 0) OR ( pan_starrs_i_el_model1_odonnell_ext < 0) OR ( pan_starrs_i_el_model3_odonnell_ext < 0) OR ( pan_starrs_z_el_model1_odonnell_ext < 0) OR ( pan_starrs_z_el_model3_odonnell_ext < 0) OR ( sdss_r01_el_model1_odonnell_ext < 0) OR ( sdss_r01_el_model3_odonnell_ext < 0) OR ( subaru_hsc_z_el_model1_odonnell_ext < 0) OR (subaru_hsc_z_el_model3_odonnell_ext < 0);
LINEAS DE EMISION:
SELECT COUNT(*) FROM cosmohub.flagship_mock_1_9_14_swf1_swf2_deep_swf3_s WHERE (logf_halpha IS NULL) OR (logf_halpha_ext IS NULL) OR (halpha_scatter IS NULL) OR (loglum_halpha IS NULL) OR (loglum_halpha_ext IS NULL) OR (logf_dummy IS NULL) OR (z_dummy IS NULL) OR (logf_halpha_model1_ext IS NULL) OR (logf_halpha_model1 IS NULL) OR (loglum_halpha_model1_ext IS NULL) OR (loglum_halpha_model1 IS NULL) OR (logf_halpha_model3_ext IS NULL) OR (logf_halpha_model3 IS NULL) OR (loglum_halpha_model3_ext IS NULL) OR (loglum_halpha_model3 IS NULL) OR (logf_hbeta_model1_ext IS NULL) OR (logf_hbeta_model1 IS NULL) OR (logf_o2_model1_ext IS NULL) OR (logf_o2_model1 IS NULL) OR (logf_n2_model1_ext IS NULL) OR (logf_n2_model1 IS NULL) OR (logf_o3_model1_ext IS NULL) OR (logf_o3_model1 IS NULL) OR (logf_s2_model1_ext IS NULL) OR (logf_s2_model1 IS NULL) OR (logf_hbeta_model3_ext IS NULL) OR (logf_hbeta_model3 IS NULL) OR (logf_o2_model3_ext IS NULL) OR (logf_o2_model3 IS NULL) OR (logf_n2_model3_ext IS NULL) OR (logf_n2_model3 IS NULL) OR (logf_o3_model3_ext IS NULL) OR (logf_o3_model3 IS NULL) OR (logf_s2_model3_ext IS NULL) OR (logf_s2_model3 IS NULL);
Estas son las galaxias que dice Francesc:
halo_id * 10000 + galaxy_id 7673890635490000, (halo_id = 767389063549 AND galaxy_id = 0) 7572872963390000, (halo_id = 757287296339 AND galaxy_id = 0) 7572872963390001, (halo_id = 757287296339 AND galaxy_id = 1) 7572860342450000, (halo_id = 757286034245 AND galaxy_id = 0) 7773901128270000, (halo_id = 777390112827 AND galaxy_id = 0) 7773902983940000, (halo_id = 777390298394 AND galaxy_id = 0)
SELECT halo_id, galaxy_id, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_h_odonnell_ext, 2mass_j_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_r_odonnell_ext, cfht_megacam_u_odonnell_ext, euclid_nisp_h_odonnell_ext, euclid_nisp_j_odonnell_ext, euclid_nisp_y_odonnell_ext, euclid_vis_odonnell_ext, gaia_bp_odonnell_ext, gaia_g_odonnell_ext, gaia_rp_odonnell_ext, jst_jpcam_g_odonnell_ext, kids_g_odonnell_ext, kids_i_odonnell_ext, kids_r_odonnell_ext, kids_u_odonnell_ext, lsst_g_odonnell_ext, lsst_i_odonnell_ext, lsst_r_odonnell_ext, lsst_u_odonnell_ext, lsst_y_odonnell_ext, lsst_z_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, sdss_r01_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_i_el_model3_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_y_el_model3_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_z_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext FROM cosmohub.flagship_mock_1_9_14_swf1_swf2_deep_swf3_s WHERE (halo_id = 767389063549 AND galaxy_id = 0) OR (halo_id = 757287296339 AND galaxy_id = 0) OR (halo_id = 757287296339 AND galaxy_id = 1) OR (halo_id = 757286034245 AND galaxy_id = 0) OR (halo_id = 777390112827 AND galaxy_id = 0) OR (halo_id = 777390298394 AND galaxy_id = 0);
SELECT COUNT(*) FROM cosmohub.flagship_mock_1_9_14_swf1_swf2_deep_swf3_s WHERE (1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) < 0;
Hay 95 galaxias con el factor de magnificación negativo! Sin embargo solo encuentran 6
SELECT halo_id, galaxy_id, kappa, gamma1, gamma2 FROM cosmohub.flagship_mock_1_9_14_swf1_swf2_deep_swf3_s WHERE (1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) < 0 ORDER BY halo_id, galaxy_id;
halo_id,galaxy_id,kappa,gamma1,gamma2 676879394673,0,0.9466088,0.02323233,-0.060423296 676982166157,0,1.0387074,0.025682965,-0.068247266 676982166157,2,1.0387074,0.025682965,-0.068247266 676982166271,0,1.0387074,0.025682965,-0.068247266 677184194401,0,0.9513193,-0.03966238,-0.047309168 677184194401,4,0.9513193,-0.03966238,-0.047309168 677184194401,7,0.9513193,-0.03966238,-0.047309168 677184194406,0,0.9513193,-0.03966238,-0.047309168 677184194406,1,0.9513193,-0.03966238,-0.047309168 677184194406,2,0.9513193,-0.03966238,-0.047309168 677184194406,5,0.9513193,-0.03966238,-0.047309168 677184194415,0,0.9513193,-0.03966238,-0.047309168 677184194415,3,0.9513193,-0.03966238,-0.047309168 677185134264,0,0.96165824,-0.03949758,-0.04908125 677286007778,0,0.9879744,-0.038911454,-0.053498607 677286114461,0,1.0034856,-0.039175782,-0.056355134 677286114461,2,1.0034856,-0.039175782,-0.056355134 677286114461,4,1.0034856,-0.039175782,-0.056355134 677286114461,5,1.0034856,-0.039175782,-0.056355134 677287273111,0,1.0188354,-0.039985895,-0.05913097 677390125612,0,0.74372226,-0.23093514,-0.1602958 677390193502,0,1.0139244,6.1350537E-4,-0.11684739 677390392716,0,1.0186903,1.9916013E-4,-0.117867604 677390402291,0,1.0210575,2.955524E-5,-0.1183663 677391220160,0,0.75466365,-0.23584487,-0.16477706 677493384265,0,1.0594596,-0.003204944,-0.12704094 686793204205,0,0.8913755,0.091431834,0.06434807 686794151624,0,0.90164036,0.09371243,0.06348447 686794151624,5,0.90164036,0.09371243,0.06348447 686983166832,0,0.8923236,0.07975021,-0.14492047 686984139053,0,0.82944196,0.092613876,-0.17306094 686984139053,1,0.82944196,0.092613876,-0.17306094 686984140236,0,0.9085991,0.0816312,-0.14823902 687084072300,0,0.8391783,0.09400141,-0.17545418 687086423750,0,0.8688338,0.09819017,-0.18254882 687086423778,4,0.8688338,0.09819017,-0.18254882 687087372131,0,0.8743341,0.098826095,-0.18377748 687087422073,0,0.879577,0.09948337,-0.18496072 687087422133,0,0.879577,0.09948337,-0.18496072 687088349763,0,0.88488966,0.10017477,-0.18570945 687088349763,1,0.88488966,0.10017477,-0.18570945 697189004392,0,0.976608,0.091767944,-0.16726859 697189027977,0,0.89996403,0.10210493,-0.18850929 697189028634,0,0.9794448,0.09240139,-0.16852666 697190028610,0,0.9100632,0.10305308,-0.19027744 697190028975,0,0.98767036,0.09403203,-0.17268464 697190057656,0,0.9890718,0.0942223,-0.17344803 697190330717,0,0.99341464,0.09481884,-0.1757113 697191397102,0,1.0004786,0.09560449,-0.17945859 697191397103,0,1.0004786,0.09560449,-0.17945859 697191407787,0,1.0004786,0.09560449,-0.17945859 697292119621,0,0.9375106,0.10099155,-0.1940854 697293123754,0,0.94402236,0.10001097,-0.19534084 697293128964,0,1.0146831,0.09644287,-0.18746652 757286034245,0,0.8942544,-0.019768905,0.121749535 757287296339,0,0.9141435,-0.019492676,0.12516093 757287296339,1,0.9141435,-0.019492676,0.12516093 757287450779,0,0.9256603,-0.019027261,0.12735774 767389063549,0,0.9488826,-0.018420406,0.13083303 777390112827,0,0.9717463,-0.017343609,0.13601938 777390298394,0,0.97396046,-0.017396858,0.1366708 807290077083,0,0.88874286,-0.112461865,0.060309496 877174126179,0,0.7981354,-0.07409639,-0.2010945 887174438906,0,0.83578795,-0.07605727,-0.21040344 897172194377,0,0.68061197,-0.0077906228,0.33081418 897172194389,0,0.6918681,0.07897722,0.32934424 897172202439,0,0.69263846,0.079069786,0.33000728 897274230377,0,0.8795665,0.034588233,-0.18920599 897274230377,2,0.8795665,0.034588233,-0.18920599 897275085331,1,0.8606613,-0.077470824,-0.21694827 907275056500,0,0.8797541,-0.05927137,-0.10642435 907275384729,0,0.6678807,0.15230387,-0.30582303 907275395634,0,0.8908671,-0.07935878,-0.22461277 917272367393,0,0.68939537,-0.007870857,0.3381617 917275174575,0,0.9283903,0.038862277,-0.19855052 917276334605,0,0.91225433,-0.061048068,-0.10879808 927272277013,0,0.7042289,0.080178425,0.33709815 927272322780,0,0.6772663,0.10276971,0.30726635 927273087800,0,0.7047322,0.080168694,0.3373871 927273087800,1,0.7047322,0.080168694,0.3373871 927273089148,0,0.7052939,0.080140576,0.33764392 927273089159,0,0.6778103,0.10285571,0.3074809 927273090157,0,0.69149524,-0.008165443,0.34014022 927273090157,1,0.69149524,-0.008165443,0.34014022 927273090160,0,0.69149524,-0.008165443,0.34014022 927273318878,0,0.6925413,-0.008584624,0.34105986 927273319115,0,0.70684147,0.079995915,0.338217 927273319116,0,0.6925413,-0.008584624,0.34105986 927376012522,0,0.91684175,-0.0807871,-0.23109823 927376012524,0,0.6874639,0.15851408,-0.31708482 927376012524,1,0.6874639,0.15851408,-0.31708482 927376043337,0,0.8242437,0.040258806,-0.19030684 927376044666,0,0.8242437,0.040258806,-0.19030684 927376044666,2,0.8242437,0.040258806,-0.19030684 937273151829,0,0.7077444,0.079914704,0.33856288
Fist files in CosmoHub:
{
"sql": "SELECT * FROM (SELECT CAST(((halo_id * 10000) + galaxy_id) AS bigint) AS SOURCE_ID, \nCAST(halo_id AS bigint) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra_gal AS double) AS RA, \nCAST(dec_gal AS double) AS `DEC`, \nCAST(ra_mag_gal AS double) AS RA_MAG, \nCAST(dec_mag_gal AS double) AS DEC_MAG, \nCAST(observed_redshift_gal AS float) AS Z_OBS, \nCAST(abs_mag_r01_evolved AS float) AS REF_MAG_ABS, \nCAST(-2.5*log10(sdss_r01) - 48.6 AS float) AS REF_MAG, \nCAST(bulge_fraction AS float) AS BULGE_FRACTION, \nCAST(bulge_r50 AS float) AS BULGE_R50, \nCAST(disk_r50 AS float) AS DISK_R50, \nCAST(bulge_nsersic AS float) AS BULGE_NSERSIC, \nCAST(bulge_axis_ratio AS float) AS BULGE_AXIS_RATIO, \nCAST(inclination_angle AS float) AS INCLINATION_ANGLE, \nCAST(disk_angle AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(sed_cosmos AS float) AS SED_TEMPLATE, \nCAST(ROUND(ext_curve_cosmos) AS smallint) AS EXT_LAW, \nCAST(ebv_cosmos AS float) AS EBV, \nCAST(logf_halpha_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS HALPHA_LOGFLAM_EXT_MAG, \nCAST(logf_hbeta_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS HBETA_LOGFLAM_EXT_MAG, \nCAST(logf_o2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS O2_LOGFLAM_EXT_MAG, \nCAST(logf_o3_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS O3_LOGFLAM_EXT_MAG, \nCAST(logf_n2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS N2_LOGFLAM_EXT_MAG, \nCAST(logf_s2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS S2_LOGFLAM_EXT_MAG, \nCAST(mw_extinction AS float) AS AV,\nCAST(euclid_vis_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, \nCAST(euclid_nisp_y_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, \nCAST(euclid_nisp_j_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, \nCAST(euclid_nisp_h_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, \nCAST(blanco_decam_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, \nCAST(blanco_decam_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, \nCAST(blanco_decam_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, \nCAST(blanco_decam_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, \nCAST(cfht_megacam_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, \nCAST(cfht_megacam_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, \nCAST(jst_jpcam_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, \nCAST(pan_starrs_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, \nCAST(pan_starrs_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, \nCAST(subaru_hsc_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, \nCAST(gaia_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, \nCAST(gaia_bp_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, \nCAST(gaia_rp_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, \nCAST(lsst_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, \nCAST(lsst_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, \nCAST(lsst_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, \nCAST(lsst_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, \nCAST(lsst_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, \nCAST(lsst_y_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, \nCAST(kids_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, \nCAST(kids_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, \nCAST(kids_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, \nCAST(kids_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, \nCAST(2mass_j_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, \nCAST(2mass_h_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, \nCAST(2mass_ks_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG \nFROM cosmohub.flagship_mock_1_10_3_s_sc8_c \nWHERE (logf_halpha_model3_ext > -16 OR -2.5*log10(euclid_nisp_h) - 48.6 < 26) \nAND disk_axis_ratio > 0.10865 \nAND SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) a\nUNION ALL \nSELECT * FROM (SELECT CAST(unique_id AS bigint) AS SOURCE_ID, \nCAST(-1 AS float) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra AS double) AS RA, \nCAST(`dec` AS double) AS `DEC`, \nCAST(ra_mag AS double) AS RA_MAG, \nCAST(dec_mag AS double) AS DEC_MAG, \nCAST(z AS float) AS Z_OBS, \nCAST(m1450 AS float) AS REF_MAG_ABS, \nCAST(m1450 AS float) AS REF_MAG, \nCAST(-1 AS float) AS BULGE_FRACTION, \nCAST(-1 AS float) AS BULGE_R50, \nCAST(-1 AS float) AS DISK_R50, \nCAST(-1 AS float) AS BULGE_NSERSIC, \nCAST(-1 AS float) AS BULGE_AXIS_RATIO, \nCAST(-1 AS float) AS INCLINATION_ANGLE, \nCAST(-1 AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(template_int AS float) AS SED_TEMPLATE, \nCAST(0 AS smallint) AS EXT_LAW, \nCAST(-1 AS float) AS EBV, \nCAST(-1 AS float) AS HALPHA_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS HBETA_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS O2_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS O3_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS N2_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS S2_LOGFLAM_EXT_MAG, \nCAST(mw_extinction AS float) AS AV,\nCAST(euclid_vis*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, \nCAST(euclid_nisp_y*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, \nCAST(euclid_nisp_j*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, \nCAST(euclid_nisp_h*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, \nCAST(blanco_decam_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, \nCAST(blanco_decam_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, \nCAST(blanco_decam_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, \nCAST(blanco_decam_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, \nCAST(cfht_megacam_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, \nCAST(cfht_megacam_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, \nCAST(jst_jpcam_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, \nCAST(pan_starrs_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, \nCAST(pan_starrs_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, \nCAST(subaru_hsc_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, \nCAST(gaia_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, \nCAST(gaia_bp*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, \nCAST(gaia_rp*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, \nCAST(lsst_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, \nCAST(lsst_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, \nCAST(lsst_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, \nCAST(lsst_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, \nCAST(lsst_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, \nCAST(lsst_y*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, \nCAST(kids_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, \nCAST(kids_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, \nCAST(kids_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, \nCAST(kids_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, \nCAST(2mass_j*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, \nCAST(2mass_h*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, \nCAST(2mass_ks*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG \nFROM cosmohub.flagship_mock_1_10_4_qso_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) b \nUNION ALL \nSELECT * FROM (SELECT CAST(unique_id AS bigint) AS SOURCE_ID, \nCAST(-1 AS float) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra AS double) AS RA, \nCAST(`dec` AS double) AS `DEC`, \nCAST(ra_mag AS double) AS RA_MAG, \nCAST(dec_mag AS double) AS DEC_MAG, \nCAST(z AS float) AS Z_OBS, \nCAST(abs_muv AS float) AS REF_MAG_ABS, \nCAST(abs_muv AS float) AS REF_MAG, \nCAST(-1 AS float) AS BULGE_FRACTION, \nCAST(rhalf_arcsec AS float) AS BULGE_R50, \nCAST(rhalf_arcsec AS float) AS DISK_R50, \nCAST(-1 AS float) AS BULGE_NSERSIC, \nCAST(-1 AS float) AS BULGE_AXIS_RATIO, \nCAST(-1 AS float) AS INCLINATION_ANGLE, \nCAST(-1 AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(sedname_int AS float) AS SED_TEMPLATE, \nCAST(0 AS smallint) AS EXT_LAW, \nCAST(-1 AS float) AS EBV, \nCAST(-1 AS float) AS HALPHA_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS HBETA_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS O2_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS O3_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS N2_LOGFLAM_EXT_MAG, \nCAST(-1 AS float) AS S2_LOGFLAM_EXT_MAG, \nCAST(mw_extinction AS float) AS AV,\nCAST(euclid_vis*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, \nCAST(euclid_nisp_y*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, \nCAST(euclid_nisp_j*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, \nCAST(euclid_nisp_h*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, \nCAST(blanco_decam_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, \nCAST(blanco_decam_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, \nCAST(blanco_decam_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, \nCAST(blanco_decam_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, \nCAST(cfht_megacam_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, \nCAST(cfht_megacam_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, \nCAST(jst_jpcam_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, \nCAST(pan_starrs_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, \nCAST(pan_starrs_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, \nCAST(subaru_hsc_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, \nCAST(gaia_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, \nCAST(gaia_bp*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, \nCAST(gaia_rp*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, \nCAST(lsst_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, \nCAST(lsst_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, \nCAST(lsst_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, \nCAST(lsst_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, \nCAST(lsst_z*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, \nCAST(lsst_y*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, \nCAST(kids_u*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, \nCAST(kids_g*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, \nCAST(kids_r*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, \nCAST(kids_i*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, \nCAST(2mass_j*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, \nCAST(2mass_h*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, \nCAST(2mass_ks*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG \nFROM cosmohub.flagship_mock_1_10_5_highz_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) c"
}
Smaller example:
select * from ( select * from tabName where col1='val1' and col2 = 'val2' limit 10 ) a union all select * from ( select * from tabName where col1='val1' and col2 = 'val3' limit 10 ) b;
{
"sql": "SELECT * FROM (SELECT CAST(((halo_id * 10000) + galaxy_id) AS bigint) AS SOURCE_ID, \nCAST(halo_id AS bigint) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra_gal AS double) AS RA, \nCAST(dec_gal AS double) AS `DEC`, \nCAST(ra_mag_gal AS double) AS RA_MAG, \nCAST(dec_mag_gal AS double) AS DEC_MAG, \nCAST(observed_redshift_gal AS float) AS Z_OBS, \nCAST(abs_mag_r01_evolved AS float) AS REF_MAG_ABS, \nCAST(-2.5*log10(sdss_r01) - 48.6 AS float) AS REF_MAG, \nCAST(bulge_fraction AS float) AS BULGE_FRACTION, \nCAST(bulge_r50 AS float) AS BULGE_R50, \nCAST(disk_r50 AS float) AS DISK_R50, \nCAST(bulge_nsersic AS float) AS BULGE_NSERSIC, \nCAST(bulge_axis_ratio AS float) AS BULGE_AXIS_RATIO, \nCAST(inclination_angle AS float) AS INCLINATION_ANGLE, \nCAST(disk_angle AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(sed_cosmos AS float) AS SED_TEMPLATE, \nCAST(ROUND(ext_curve_cosmos) AS smallint) AS EXT_LAW, \nCAST(ebv_cosmos AS float) AS EBV \nFROM cosmohub.flagship_mock_1_10_3_s_sc8_c \nWHERE (logf_halpha_model3_ext > -16 OR -2.5*log10(euclid_nisp_h) - 48.6 < 26) \nAND disk_axis_ratio > 0.10865 \nAND SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) a \nUNION ALL \nSELECT * FROM (SELECT CAST(unique_id AS bigint) AS SOURCE_ID, \nCAST(-1 AS float) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra AS double) AS RA, \nCAST(`dec` AS double) AS `DEC`, \nCAST(ra_mag AS double) AS RA_MAG, \nCAST(dec_mag AS double) AS DEC_MAG, \nCAST(z AS float) AS Z_OBS, \nCAST(m1450 AS float) AS REF_MAG_ABS, \nCAST(m1450 AS float) AS REF_MAG, \nCAST(-1 AS float) AS BULGE_FRACTION, \nCAST(-1 AS float) AS BULGE_R50, \nCAST(-1 AS float) AS DISK_R50, \nCAST(-1 AS float) AS BULGE_NSERSIC, \nCAST(-1 AS float) AS BULGE_AXIS_RATIO, \nCAST(-1 AS float) AS INCLINATION_ANGLE, \nCAST(-1 AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(template_int AS float) AS SED_TEMPLATE, \nCAST(0 AS smallint) AS EXT_LAW, \nCAST(-1 AS float) AS EBV \nFROM cosmohub.flagship_mock_1_10_4_qso_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) b \nUNION ALL \nSELECT * FROM (SELECT CAST(unique_id AS bigint) AS SOURCE_ID, \nCAST(-1 AS float) AS HALO_ID, \nCAST(kind AS smallint) AS KIND, \nCAST(ra AS double) AS RA, \nCAST(`dec` AS double) AS `DEC`, \nCAST(ra_mag AS double) AS RA_MAG, \nCAST(dec_mag AS double) AS DEC_MAG, \nCAST(z AS float) AS Z_OBS, \nCAST(abs_muv AS float) AS REF_MAG_ABS, \nCAST(abs_muv AS float) AS REF_MAG, \nCAST(-1 AS float) AS BULGE_FRACTION, \nCAST(rhalf_arcsec AS float) AS BULGE_R50, \nCAST(rhalf_arcsec AS float) AS DISK_R50, \nCAST(-1 AS float) AS BULGE_NSERSIC, \nCAST(-1 AS float) AS BULGE_AXIS_RATIO, \nCAST(-1 AS float) AS INCLINATION_ANGLE, \nCAST(-1 AS float) AS DISK_ANGLE, \nCAST(kappa AS float) AS KAPPA, \nCAST(gamma1 AS float) AS GAMMA1, \nCAST(gamma2 AS float) AS GAMMA2, \nCAST(sedname_int AS float) AS SED_TEMPLATE, \nCAST(0 AS smallint) AS EXT_LAW, \nCAST(-1 AS float) AS EBV \nFROM cosmohub.flagship_mock_1_10_5_highz_s \nWHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) c"
}
El campo disk_angle que puse entre 0 y 360 grados tiene que ir entre -180 y 180 grados. Así que lo reescribo.
CREATE TABLE cosmohub.flagship_mock_1_10_6_s_sc8_c(
`halo_id` bigint COMMENT 'unique halo id coming from the Flagship dark matter halo catalog',
`galaxy_id` int COMMENT 'combined with the unique_halo_id they uniquely identify each galaxy',
`kind` tinyint COMMENT 'Galaxy type: 0: CENTRAL, 1: SATELLITE, 2: QSO, 3: HIGH REDSHIFT',
`random_index` float COMMENT 'random number [0 - 1) for subsampling',
`ra_gal` float COMMENT 'galaxy right ascension (degrees)',
`dec_gal` float COMMENT 'galaxy declination (degrees)',
`ra_mag_gal` float COMMENT 'galaxy magnified right ascension (degree)',
`dec_mag_gal` float COMMENT 'galaxy magnified declination (degree)',
`kappa` float COMMENT 'convergence',
`gamma1` float COMMENT 'shear',
`gamma2` float COMMENT 'shear',
`defl1` float COMMENT 'deflection1',
`defl2` float COMMENT 'deflection2',
`l_gal` float COMMENT 'galactic longitude (degrees)',
`b_gal` float COMMENT 'galactic latitude (degrees)',
`mw_extinction` float COMMENT 'EBV from file: HFI_CompMap_ThermalDustModel_2048_R1.20.fits',
`x_gal` float COMMENT 'galaxy comoving x coordinate (Mpc/h)',
`y_gal` float COMMENT 'galaxy comoving y coordinate (Mpc/h)',
`z_gal` float COMMENT 'galaxy comoving z coordinate (Mpc/h)',
`r_gal` float COMMENT 'galaxy comoving distance (Mpc/h)',
`true_redshift_gal` float COMMENT 'true galaxy redshift',
`observed_redshift_gal` float COMMENT 'observed galaxy redshift (including peculiar velocity)',
`hpix_29_nest` bigint COMMENT 'healpix pixel id using order = 29 in NESTED format',
`hpix_13_nest` int COMMENT 'healpix pixel id using order = 13 in NESTED format',
`vx_gal` float COMMENT 'physical (peculiar) velocity in x-component (km/s)',
`vy_gal` float COMMENT 'physical (peculiar) velocity in y-component (km/s)',
`vz_gal` float COMMENT 'physical (peculiar) velocity in z-component (km/s)',
`vrad_gal` float COMMENT 'missing comment',
`delta_r` float COMMENT 'missing comment',
`abs_mag_r01` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h)',
`luminosity_r01` float COMMENT 'luminosity in the 0.1-r-band',
`abs_mag_r01_evolved` float COMMENT 'absolute magnitude in the 0.1-r-band: abs_mag_r01 = Mr-5log(h) including MICECATv2.0 magnitude evolution',
`sdss_r01` float COMMENT 'observed flux in sdss r-band redshifted to z=0.1',
`abs_mag_uv_dereddened` float COMMENT 'absolute magnitude ultraviolet',
`gr_restframe` float COMMENT '(g-r) rest-frame at z=0',
`color_kind` tinyint COMMENT 'galaxy color kind: 0=red sequence 1=green valley 2=blue cloud',
`sed_ke` float COMMENT 'K-correction for each SED',
`gr_cosmos` float COMMENT 'interpolated COSMOS g-r color',
`sed_cosmos` float COMMENT 'interpolated basic COSMOS SED [0 30]',
`is_within_cosmos` boolean COMMENT 'missing comment',
`cosmos_distance` float COMMENT 'missing comment',
`ext_curve_cosmos` tinyint COMMENT 'COSMOS extinction curve [0-4]',
`ebv_cosmos` float COMMENT 'interpolated extinction strength for COSMOS extinction curve',
`log_ml_r01` float COMMENT 'logarithm of stellar_mass/luminosity ratio in the r01 SDSS band',
`log_stellar_mass` float COMMENT 'logarithm of the stellar mass (Msun/h)',
`metallicity` float COMMENT 'oxygen metallicity in units 12+logO/H',
`log_sfr` float COMMENT 'logarithm of star formation rate in (Msun/h)/year',
`logf_halpha` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: DOES NOT include extinction)',
`logf_halpha_ext` float COMMENT 'DEPRECATED logarithm of flux of Halpha (erg/cm**2/s) using empirical relation with UV magnitude (observed: includes extinction)',
`halpha_scatter` float COMMENT 'missing comment',
`loglum_halpha` float COMMENT 'DEPRECATED logarithm of Halpha luminosity (erg/s/h^2) using empirical relation with UV magnitude (DOES NOT include extinction)',
`loglum_halpha_ext` float COMMENT 'missing comment',
`logf_dummy` float COMMENT 'missing comment',
`z_dummy` float COMMENT 'missing comment',
`logf_halpha_model1_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: includes extinction)',
`logf_halpha_model1` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 1 (observed: DOES NOT include extinction)',
`loglum_halpha_model1_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (includes extinction)',
`loglum_halpha_model1` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 1 (DOES NOT include extinction)',
`logf_halpha_model3_ext` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: includes extinction)',
`logf_halpha_model3` float COMMENT 'logarithm of flux of Halpha (erg/cm**2/s) calibrated using Pozzetti model 3 (observed: DOES NOT include extinction)',
`loglum_halpha_model3_ext` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (includes extinction)',
`loglum_halpha_model3` float COMMENT 'logarithm of Halpha luminosity (erg/s/h^2) calibrated using Pozzetti model 3 (DOES NOT include extinction)',
`logf_hbeta_model1_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_hbeta_model1` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_o2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_o2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_n2_model1_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_n2_model1` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_o3_model1_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_o3_model1` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_s2_model1_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: includes extinction)',
`logf_s2_model1` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model1 (observed: DOES NOT include extinction)',
`logf_hbeta_model3_ext` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_hbeta_model3` float COMMENT 'logarithm of flux of Hbeta (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_o2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_o2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_n2_model3_ext` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_n2_model3` float COMMENT 'logarithm of flux of [NII] 6584 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_o3_model3_ext` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_o3_model3` float COMMENT 'logarithm of flux of [OIII] 5007 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`logf_s2_model3_ext` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: includes extinction)',
`logf_s2_model3` float COMMENT 'logarithm of flux of [SII] 6716 (erg/cm**2/s) using as input model3 (observed: DOES NOT include extinction)',
`2mass_h` float COMMENT 'observed flux in 2MASS H-band (Euclid provided)',
`2mass_j` float COMMENT 'observed flux in 2MASS J-band (Euclid provided)',
`2mass_ks` float COMMENT 'observed flux in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g` float COMMENT 'observed flux in DES g-band (Euclid provided)',
`blanco_decam_i` float COMMENT 'observed flux in DES i-band (Euclid provided)',
`blanco_decam_r` float COMMENT 'observed flux in DES r-band (Euclid provided)',
`blanco_decam_z` float COMMENT 'observed flux in DES z-band (Euclid provided)',
`cfht_megacam_r` float COMMENT 'observed flux in CFHT r-band (Euclid provided)',
`cfht_megacam_u` float COMMENT 'observed flux in CFHT u-band (Euclid provided)',
`euclid_nisp_h` float COMMENT 'observed flux in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j` float COMMENT 'observed flux in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y` float COMMENT 'observed flux in Euclid NISP-Y band (Euclid provided)',
`euclid_vis` float COMMENT 'observed flux in Euclid VIS band (Euclid provided)',
`gaia_bp` float COMMENT 'observed flux in Gaia-BP band (Euclid provided)',
`gaia_g` float COMMENT 'observed flux in Gaia-G band (Euclid provided)',
`gaia_rp` float COMMENT 'observed flux in Gaia-RP band (Euclid provided)',
`jst_jpcam_g` float COMMENT 'observed flux in JST g-band (Euclid provided)',
`kids_g` float COMMENT 'observed flux in KIDS g-band (Euclid provided)',
`kids_i` float COMMENT 'observed flux in KIDS i-band (Euclid provided)',
`kids_r` float COMMENT 'observed flux in KIDS r-band (Euclid provided)',
`kids_u` float COMMENT 'observed flux in KIDS u-band (Euclid provided)',
`lsst_g` float COMMENT 'observed flux in LSST g-band (Euclid provided)',
`lsst_i` float COMMENT 'observed flux in LSST i-band (Euclid provided)',
`lsst_r` float COMMENT 'observed flux in LSST r-band (Euclid provided)',
`lsst_u` float COMMENT 'observed flux in LSST u-band (Euclid provided)',
`lsst_y` float COMMENT 'observed flux in LSST Y-band (Euclid provided)',
`lsst_z` float COMMENT 'observed flux in LSST z-band (Euclid provided)',
`pan_starrs_i` float COMMENT 'observed flux in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z` float COMMENT 'observed flux in Pan-STARRS z-band (Euclid provided)',
`subaru_hsc_z` float COMMENT 'observed flux in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`2mass_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS H-band (Euclid provided)',
`2mass_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_odonnell_ext` float COMMENT 'observed flux with MW extinction in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in DES z-band (Euclid provided)',
`cfht_megacam_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in CFHT u-band (Euclid provided)',
`euclid_nisp_h_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_odonnell_ext` float COMMENT 'observed flux with MW extinction in Euclid VIS band (Euclid provided)',
`gaia_bp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_odonnell_ext` float COMMENT 'observed flux with MW extinction in Gaia-RP band (Euclid provided)',
`jst_jpcam_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in JST g-band (Euclid provided)',
`kids_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS g-band (Euclid provided)',
`kids_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS i-band (Euclid provided)',
`kids_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS r-band (Euclid provided)',
`kids_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST g-band (Euclid provided)',
`lsst_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST i-band (Euclid provided)',
`lsst_r_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST r-band (Euclid provided)',
`lsst_u_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST u-band (Euclid provided)',
`lsst_y_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST Y-band (Euclid provided)',
`lsst_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in LSST z-band (Euclid provided)',
`pan_starrs_i_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in Pan-STARRS z-band (Euclid provided)',
`sdss_r01_odonnell_ext` float COMMENT 'observed flux in sdss r-band redshifted to z=0.1 with MW extinction',
`subaru_hsc_z_odonnell_ext` float COMMENT 'observed flux with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`2mass_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction 2MASS H-band (Euclid provided)',
`2mass_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction 2MASS H-band (Euclid provided)',
`2mass_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS J-band (Euclid provided)',
`2mass_ks_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in 2MASS Ks-band (Euclid provided)',
`2mass_ks_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in 2MASS Ks-band (Euclid provided)',
`blanco_decam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES g-band (Euclid provided)',
`blanco_decam_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES i-band (Euclid provided)',
`blanco_decam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES r-band (Euclid provided)',
`blanco_decam_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in DES z-band (Euclid provided)',
`blanco_decam_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in DES z-band (Euclid provided)',
`cfht_megacam_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT r-band (Euclid provided)',
`cfht_megacam_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in CFHT u-band (Euclid provided)',
`cfht_megacam_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in CFHT u-band (Euclid provided)',
`euclid_nisp_h_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_h_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-H band (Euclid provided)',
`euclid_nisp_j_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_j_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-J band (Euclid provided)',
`euclid_nisp_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_nisp_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid NISP-Y band (Euclid provided)',
`euclid_vis_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Euclid VIS band (Euclid provided)',
`euclid_vis_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Euclid VIS band (Euclid provided)',
`gaia_bp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_bp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-BP band (Euclid provided)',
`gaia_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-G band (Euclid provided)',
`gaia_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-G band (Euclid provided)',
`gaia_rp_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Gaia-RP band (Euclid provided)',
`gaia_rp_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Gaia-RP band (Euclid provided)',
`jst_jpcam_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in JST g-band (Euclid provided)',
`jst_jpcam_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in JST g-band (Euclid provided)',
`kids_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS g-band (Euclid provided)',
`kids_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS g-band (Euclid provided)',
`kids_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS i-band (Euclid provided)',
`kids_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS i-band (Euclid provided)',
`kids_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS r-band (Euclid provided)',
`kids_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS r-band (Euclid provided)',
`kids_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in KIDS u-band (Euclid provided)',
`kids_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in KIDS u-band (Euclid provided)',
`lsst_g_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST g-band (Euclid provided)',
`lsst_g_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST g-band (Euclid provided)',
`lsst_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST i-band (Euclid provided)',
`lsst_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST i-band (Euclid provided)',
`lsst_r_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST r-band (Euclid provided)',
`lsst_r_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST r-band (Euclid provided)',
`lsst_u_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST u-band (Euclid provided)',
`lsst_u_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST u-band (Euclid provided)',
`lsst_y_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST Y-band (Euclid provided)',
`lsst_y_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST Y-band (Euclid provided)',
`lsst_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in LSST z-band (Euclid provided)',
`lsst_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in LSST z-band (Euclid provided)',
`pan_starrs_i_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_i_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS i-band (Euclid provided)',
`pan_starrs_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in Pan-STARRS z-band (Euclid provided)',
`pan_starrs_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in Pan-STARRS z-band (Euclid provided)',
`sdss_r01_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in sdss r-band redshifted to z=0.1',
`sdss_r01_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in sdss r-band redshifted to z=0.1',
`subaru_hsc_z_el_model1_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model1 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`subaru_hsc_z_el_model3_odonnell_ext` float COMMENT 'observed flux including E.L. fluxes using as input model3 with MW extinction in HYPER SUPRIME CAMERA SUBARU z-band (Euclid provided)',
`dominant_shape` tinyint COMMENT 'flag to define whether the galaxy is BULGE-dominated (0) or DISK-dominated (1)',
`disk_angle`float COMMENT 'position of the disk rotation axis (degrees). TU Galaxy stamps convention for OU-SIM: the standard is to set the position angle from North to East, with the major axis aligned in Declination',
`median_major_axis` float COMMENT 'median semi-major axis exponential scalength. Profile defined as: I = I_o x exp (-radius/scalelength)',
`scale_length` float COMMENT 'disc and bulge scalelength prior',
`bulge_fraction` float COMMENT 'ratio of the flux in the bulge component to the total flux (often written B/T)',
`disk_scalelength` float COMMENT 'scalength of the disk, profile defined as: I = I_o x exp (-radius/disk_scalelength)',
`disk_nsersic` float COMMENT 'Sersic index of the disk component',
`disk_r50` float COMMENT 'half light radius of the disk. For an exponential profile (or Sersec profile with index n=1), disk_r50 = disk_scalelength * 1.678',
`bulge_r50` float COMMENT 'half light radius of the bulge',
`bulge_nsersic` float COMMENT 'Sersic index of the bulge component',
`inclination_angle` float COMMENT 'Galaxy inclination angle (where 0 degrees = face-on and 90 degrees = edge-on). Galaxy ellipticity for disk and bulge components are computed following the recipe in https://euclid.roe.ac.uk/projects/sgsshear/wiki/SHE-SIM',
`disk_ellipticity` float COMMENT 'ellipticity of the disk defined as disk_ellipticity = (1 - disk_axis_ratio) / (1 + disk_axis_ratio)',
`bulge_ellipticity` float COMMENT 'ellipticity of the bulge defined as bulge_ellipticity = (1 - bulge_axis_ratio) / (1 + bulge_axis_ratio)',
`disk_axis_ratio` float COMMENT 'disk projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`bulge_axis_ratio` float COMMENT 'bulge projected axis ratio defined as b/a (b: semi-minor axis, a: semi-major axis)',
`halo_x` float COMMENT 'halo comoving x coordinate (Mpc/h)',
`halo_y` float COMMENT 'halo comoving y coordinate (Mpc/h)',
`halo_z` float COMMENT 'halo comoving z coordinate (Mpc/h)',
`halo_vx` float COMMENT 'halo x-centre velocity (km/h)',
`halo_vy` float COMMENT 'halo y-centre velocity (km/h)',
`halo_vz` float COMMENT 'halo z-centre velocity (km/h)',
`halo_r` float COMMENT 'halo comoving distance (Mpc/h)',
`true_redshift_halo` float COMMENT 'true redshift of the host halo',
`halo_lm` float COMMENT 'log10 of the FoF halo mass in Msum/h. This halo mass is computed as the particule mass multiplied by the number of particles of the FoF halo. At low masses it is interpolated to obtain a smooth distribution of halo masses',
`halo_n_sats` int COMMENT 'number of satellite galaxies from HOD',
`n_gals` int COMMENT 'DEPRECATED',
`luminosity_r01_evolved` float COMMENT 'missing comment',
step smallint
)
CLUSTERED BY (
step, hpix_13_nest)
SORTED BY (
step ASC, hpix_13_nest ASC)
INTO 4096 BUCKETS
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;
INSERT OVERWRITE TABLE cosmohub.flagship_mock_1_10_6_s_sc8_c SELECT halo_id, galaxy_id, kind, random_index, ra_gal, dec_gal, ra_mag_gal, dec_mag_gal, kappa, gamma1, gamma2, defl1, defl2, l_gal, b_gal, mw_extinction, x_gal, y_gal, z_gal, r_gal, true_redshift_gal, observed_redshift_gal, hpix_29_nest, hpix_13_nest, vx_gal, vy_gal, vz_gal, vrad_gal, delta_r, abs_mag_r01, luminosity_r01, abs_mag_r01_evolved, sdss_r01, abs_mag_uv_dereddened, gr_restframe, color_kind, sed_ke, gr_cosmos, sed_cosmos, is_within_cosmos, cosmos_distance, ext_curve_cosmos, ebv_cosmos, log_ml_r01, log_stellar_mass, metallicity, log_sfr, logf_halpha, logf_halpha_ext, halpha_scatter, loglum_halpha, loglum_halpha_ext, logf_dummy, z_dummy, logf_halpha_model1_ext, logf_halpha_model1, loglum_halpha_model1_ext, loglum_halpha_model1, logf_halpha_model3_ext, logf_halpha_model3, loglum_halpha_model3_ext, loglum_halpha_model3, logf_hbeta_model1_ext, logf_hbeta_model1, logf_o2_model1_ext, logf_o2_model1, logf_n2_model1_ext, logf_n2_model1, logf_o3_model1_ext, logf_o3_model1, logf_s2_model1_ext, logf_s2_model1, logf_hbeta_model3_ext, logf_hbeta_model3, logf_o2_model3_ext, logf_o2_model3, logf_n2_model3_ext, logf_n2_model3, logf_o3_model3_ext, logf_o3_model3, logf_s2_model3_ext, logf_s2_model3, 2mass_h, 2mass_j, 2mass_ks, blanco_decam_g, blanco_decam_i, blanco_decam_r, blanco_decam_z, cfht_megacam_r, cfht_megacam_u, euclid_nisp_h, euclid_nisp_j, euclid_nisp_y, euclid_vis, gaia_bp, gaia_g, gaia_rp, jst_jpcam_g, kids_g, kids_i, kids_r, kids_u, lsst_g, lsst_i, lsst_r, lsst_u, lsst_y, lsst_z, pan_starrs_i, pan_starrs_z, subaru_hsc_z, 2mass_h_odonnell_ext, 2mass_j_odonnell_ext, 2mass_ks_odonnell_ext, blanco_decam_g_odonnell_ext, blanco_decam_i_odonnell_ext, blanco_decam_r_odonnell_ext, blanco_decam_z_odonnell_ext, cfht_megacam_r_odonnell_ext, cfht_megacam_u_odonnell_ext, euclid_nisp_h_odonnell_ext, euclid_nisp_j_odonnell_ext, euclid_nisp_y_odonnell_ext, euclid_vis_odonnell_ext, gaia_bp_odonnell_ext, gaia_g_odonnell_ext, gaia_rp_odonnell_ext, jst_jpcam_g_odonnell_ext, kids_g_odonnell_ext, kids_i_odonnell_ext, kids_r_odonnell_ext, kids_u_odonnell_ext, lsst_g_odonnell_ext, lsst_i_odonnell_ext, lsst_r_odonnell_ext, lsst_u_odonnell_ext, lsst_y_odonnell_ext, lsst_z_odonnell_ext, pan_starrs_i_odonnell_ext, pan_starrs_z_odonnell_ext, sdss_r01_odonnell_ext, subaru_hsc_z_odonnell_ext, 2mass_h_el_model1_odonnell_ext, 2mass_h_el_model3_odonnell_ext, 2mass_j_el_model1_odonnell_ext, 2mass_j_el_model3_odonnell_ext, 2mass_ks_el_model1_odonnell_ext, 2mass_ks_el_model3_odonnell_ext, blanco_decam_g_el_model1_odonnell_ext, blanco_decam_g_el_model3_odonnell_ext, blanco_decam_i_el_model1_odonnell_ext, blanco_decam_i_el_model3_odonnell_ext, blanco_decam_r_el_model1_odonnell_ext, blanco_decam_r_el_model3_odonnell_ext, blanco_decam_z_el_model1_odonnell_ext, blanco_decam_z_el_model3_odonnell_ext, cfht_megacam_r_el_model1_odonnell_ext, cfht_megacam_r_el_model3_odonnell_ext, cfht_megacam_u_el_model1_odonnell_ext, cfht_megacam_u_el_model3_odonnell_ext, euclid_nisp_h_el_model1_odonnell_ext, euclid_nisp_h_el_model3_odonnell_ext, euclid_nisp_j_el_model1_odonnell_ext, euclid_nisp_j_el_model3_odonnell_ext, euclid_nisp_y_el_model1_odonnell_ext, euclid_nisp_y_el_model3_odonnell_ext, euclid_vis_el_model1_odonnell_ext, euclid_vis_el_model3_odonnell_ext, gaia_bp_el_model1_odonnell_ext, gaia_bp_el_model3_odonnell_ext, gaia_g_el_model1_odonnell_ext, gaia_g_el_model3_odonnell_ext, gaia_rp_el_model1_odonnell_ext, gaia_rp_el_model3_odonnell_ext, jst_jpcam_g_el_model1_odonnell_ext, jst_jpcam_g_el_model3_odonnell_ext, kids_g_el_model1_odonnell_ext, kids_g_el_model3_odonnell_ext, kids_i_el_model1_odonnell_ext, kids_i_el_model3_odonnell_ext, kids_r_el_model1_odonnell_ext, kids_r_el_model3_odonnell_ext, kids_u_el_model1_odonnell_ext, kids_u_el_model3_odonnell_ext, lsst_g_el_model1_odonnell_ext, lsst_g_el_model3_odonnell_ext, lsst_i_el_model1_odonnell_ext, lsst_i_el_model3_odonnell_ext, lsst_r_el_model1_odonnell_ext, lsst_r_el_model3_odonnell_ext, lsst_u_el_model1_odonnell_ext, lsst_u_el_model3_odonnell_ext, lsst_y_el_model1_odonnell_ext, lsst_y_el_model3_odonnell_ext, lsst_z_el_model1_odonnell_ext, lsst_z_el_model3_odonnell_ext, pan_starrs_i_el_model1_odonnell_ext, pan_starrs_i_el_model3_odonnell_ext, pan_starrs_z_el_model1_odonnell_ext, pan_starrs_z_el_model3_odonnell_ext, sdss_r01_el_model1_odonnell_ext, sdss_r01_el_model3_odonnell_ext, subaru_hsc_z_el_model1_odonnell_ext, subaru_hsc_z_el_model3_odonnell_ext, dominant_shape, rand()*360 - 180, median_major_axis, scale_length, bulge_fraction, disk_scalelength, disk_nsersic, disk_r50, bulge_r50, bulge_nsersic, inclination_angle, disk_ellipticity, bulge_ellipticity, disk_axis_ratio, bulge_axis_ratio, halo_x, halo_y, halo_z, halo_vx, halo_vy, halo_vz, halo_r, true_redshift_halo, halo_lm, halo_n_sats, n_gals, luminosity_r01_evolved, step FROM cosmohub.flagship_mock_1_10_3_s_sc8_c;
ANALYZE TABLE cosmohub.flagship_mock_1_10_6_s_sc8_c COMPUTE STATISTICS FOR COLUMNS;
SELECT MIN(disk_angle) as min_disk_angle, MAX(disk_angle) as max_disk_angle FROM cosmohub.flagship_mock_1_10_6_s_sc8_c
After different approaches we decided to materialize the UNION ALL into a FITS table (there is a problem in Hive with the UNION ALL command)
NOTE THAT I INCLUDE HERE IN THE EXAMPLE THE NEW TABLES:
- cosmohub.flagship_mock_1_10_6_s_sc8_c: in which I modify the disk_angle range from -180 to 180 compared to cosmohub.flagship_mock_1_10_3_s_sc8_c
- cosmohub.flagship_mock_1_10_7_highz_s: where the changes from Eric in the email are included.
ALSO NOTE THAT fluxes in the new catalogs are not _TRUE, which is the "correct" one field to use!
CREATE TABLE cosmohub.sc8_test1_fits STORED AS ORC AS SELECT * FROM (SELECT CAST(((halo_id * 10000) + galaxy_id) AS bigint) AS SOURCE_ID, CAST(halo_id AS bigint) AS HALO_ID, CAST(kind AS smallint) AS KIND, CAST(ra_gal AS double) AS RA, CAST(dec_gal AS double) AS `DEC`, CAST(ra_mag_gal AS double) AS RA_MAG, CAST(dec_mag_gal AS double) AS DEC_MAG, CAST(observed_redshift_gal AS float) AS Z_OBS, CAST(abs_mag_r01_evolved AS float) AS REF_MAG_ABS, CAST(-2.5*log10(sdss_r01) - 48.6 AS float) AS REF_MAG, CAST(bulge_fraction AS float) AS BULGE_FRACTION, CAST(bulge_r50 AS float) AS BULGE_R50, CAST(disk_r50 AS float) AS DISK_R50, CAST(bulge_nsersic AS float) AS BULGE_NSERSIC, CAST(bulge_axis_ratio AS float) AS BULGE_AXIS_RATIO, CAST(inclination_angle AS float) AS INCLINATION_ANGLE, CAST(disk_angle AS float) AS DISK_ANGLE, CAST(kappa AS float) AS KAPPA, CAST(gamma1 AS float) AS GAMMA1, CAST(gamma2 AS float) AS GAMMA2, CAST(sed_cosmos AS float) AS SED_TEMPLATE, CAST(ROUND(ext_curve_cosmos) AS smallint) AS EXT_LAW, CAST(ebv_cosmos AS float) AS EBV, CAST(logf_halpha_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS HALPHA_LOGFLAM_EXT_MAG, CAST(logf_hbeta_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS HBETA_LOGFLAM_EXT_MAG, CAST(logf_o2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS O2_LOGFLAM_EXT_MAG, CAST(logf_o3_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS O3_LOGFLAM_EXT_MAG, CAST(logf_n2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS N2_LOGFLAM_EXT_MAG, CAST(logf_s2_model3_ext + log10((1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2))) AS float) AS S2_LOGFLAM_EXT_MAG, CAST(mw_extinction AS float) AS AV, CAST(euclid_vis_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, CAST(euclid_nisp_y_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, CAST(euclid_nisp_j_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, CAST(euclid_nisp_h_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, CAST(blanco_decam_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, CAST(blanco_decam_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, CAST(blanco_decam_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, CAST(blanco_decam_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, CAST(cfht_megacam_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, CAST(cfht_megacam_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, CAST(jst_jpcam_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, CAST(pan_starrs_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, CAST(pan_starrs_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, CAST(subaru_hsc_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, CAST(gaia_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, CAST(gaia_bp_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, CAST(gaia_rp_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, CAST(lsst_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, CAST(lsst_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, CAST(lsst_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, CAST(lsst_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, CAST(lsst_z_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, CAST(lsst_y_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, CAST(kids_u_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, CAST(kids_g_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, CAST(kids_r_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, CAST(kids_i_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, CAST(2mass_j_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, CAST(2mass_h_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, CAST(2mass_ks_el_model3_odonnell_ext*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG FROM cosmohub.flagship_mock_1_10_6_s_sc8_c WHERE (logf_halpha_model3_ext > -16 OR -2.5*log10(euclid_nisp_h) - 48.6 < 26) AND disk_axis_ratio > 0.10865 AND SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) a UNION ALL SELECT * FROM (SELECT CAST(unique_id AS bigint) AS SOURCE_ID, CAST(-1 AS bigint) AS HALO_ID, CAST(kind AS smallint) AS KIND, CAST(ra AS double) AS RA, CAST(`dec` AS double) AS `DEC`, CAST(ra_mag AS double) AS RA_MAG, CAST(dec_mag AS double) AS DEC_MAG, CAST(z AS float) AS Z_OBS, CAST(m1450 AS float) AS REF_MAG_ABS, CAST(m1450 AS float) AS REF_MAG, CAST(-1 AS float) AS BULGE_FRACTION, CAST(-1 AS float) AS BULGE_R50, CAST(-1 AS float) AS DISK_R50, CAST(-1 AS float) AS BULGE_NSERSIC, CAST(-1 AS float) AS BULGE_AXIS_RATIO, CAST(-1 AS float) AS INCLINATION_ANGLE, CAST(-1 AS float) AS DISK_ANGLE, CAST(kappa AS float) AS KAPPA, CAST(gamma1 AS float) AS GAMMA1, CAST(gamma2 AS float) AS GAMMA2, CAST(template_int AS float) AS SED_TEMPLATE, CAST(0 AS smallint) AS EXT_LAW, CAST(-1 AS float) AS EBV, CAST(-1 AS float) AS HALPHA_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS HBETA_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS O2_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS O3_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS N2_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS S2_LOGFLAM_EXT_MAG, CAST(mw_extinction AS float) AS AV, CAST(euclid_vis_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, CAST(euclid_nisp_y_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, CAST(euclid_nisp_j_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, CAST(euclid_nisp_h_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, CAST(blanco_decam_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, CAST(blanco_decam_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, CAST(blanco_decam_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, CAST(blanco_decam_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, CAST(cfht_megacam_u_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, CAST(cfht_megacam_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, CAST(jst_jpcam_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, CAST(pan_starrs_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, CAST(pan_starrs_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, CAST(subaru_hsc_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, CAST(gaia_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, CAST(gaia_bp_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, CAST(gaia_rp_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, CAST(lsst_u_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, CAST(lsst_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, CAST(lsst_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, CAST(lsst_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, CAST(lsst_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, CAST(lsst_y_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, CAST(kids_u_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, CAST(kids_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, CAST(kids_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, CAST(kids_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, CAST(2mass_j_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, CAST(2mass_h_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, CAST(2mass_ks_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG FROM cosmohub.flagship_mock_1_10_4_qso_s WHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) b UNION ALL SELECT * FROM (SELECT CAST(unique_id AS bigint) AS SOURCE_ID, CAST(-1 AS bigint) AS HALO_ID, CAST(kind AS smallint) AS KIND, CAST(ra AS double) AS RA, CAST(`dec` AS double) AS `DEC`, CAST(ra_mag AS double) AS RA_MAG, CAST(dec_mag AS double) AS DEC_MAG, CAST(z AS float) AS Z_OBS, CAST(abs_muv AS float) AS REF_MAG_ABS, CAST(abs_muv AS float) AS REF_MAG, CAST(bulge_fraction AS float) AS BULGE_FRACTION, CAST(rhalf_arcsec AS float) AS BULGE_R50, CAST(rhalf_arcsec AS float) AS DISK_R50, CAST(bulge_nsersic AS float) AS BULGE_NSERSIC, CAST(bulge_axis_ratio AS float) AS BULGE_AXIS_RATIO, CAST(-1 AS float) AS INCLINATION_ANGLE, CAST(disk_angle AS float) AS DISK_ANGLE, CAST(kappa AS float) AS KAPPA, CAST(gamma1 AS float) AS GAMMA1, CAST(gamma2 AS float) AS GAMMA2, CAST(sedname_int AS float) AS SED_TEMPLATE, CAST(0 AS smallint) AS EXT_LAW, CAST(-1 AS float) AS EBV, CAST(-1 AS float) AS HALPHA_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS HBETA_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS O2_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS O3_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS N2_LOGFLAM_EXT_MAG, CAST(-1 AS float) AS S2_LOGFLAM_EXT_MAG, CAST(mw_extinction AS float) AS AV, CAST(euclid_vis_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_VIS_MAG, CAST(euclid_nisp_y_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_NISP_MAG, CAST(euclid_nisp_j_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_NISP_MAG, CAST(euclid_nisp_h_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_NISP_MAG, CAST(blanco_decam_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_DECAM_MAG, CAST(blanco_decam_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_DECAM_MAG, CAST(blanco_decam_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_DECAM_MAG, CAST(blanco_decam_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_DECAM_MAG, CAST(cfht_megacam_u_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_MEGACAM_MAG, CAST(cfht_megacam_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_MEGACAM_MAG, CAST(jst_jpcam_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_JPCAM_MAG, CAST(pan_starrs_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_PANSTARRS_MAG, CAST(pan_starrs_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_PANSTARRS_MAG, CAST(subaru_hsc_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_HSC_MAG, CAST(gaia_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_GAIA_MAG, CAST(gaia_bp_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_BP_GAIA_MAG, CAST(gaia_rp_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_RP_GAIA_MAG, CAST(lsst_u_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_LSST_MAG, CAST(lsst_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_LSST_MAG, CAST(lsst_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_LSST_MAG, CAST(lsst_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_LSST_MAG, CAST(lsst_z_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Z_LSST_MAG, CAST(lsst_y_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_Y_LSST_MAG, CAST(kids_u_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_U_KIDS_MAG, CAST(kids_g_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_G_KIDS_MAG, CAST(kids_r_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_R_KIDS_MAG, CAST(kids_i_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_I_KIDS_MAG, CAST(2mass_j_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_J_2MASS_MAG, CAST(2mass_h_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_H_2MASS_MAG, CAST(2mass_ks_true*1.e23*(1/((1 - kappa)*(1 - kappa) - gamma1*gamma1 - gamma2*gamma2)) AS float) AS TU_FNU_KS_2MASS_MAG FROM cosmohub.flagship_mock_1_10_7_highz_s WHERE SHIFTRIGHT(hpix_29_nest, (29-5)*2) = 2063 LIMIT 5) c