Legacy Survey DR9

Original DR9

Raw data

Downloaded from NERSC

/global/cfs/cdirs/cosmo/work/legacysurvey/dr9m

Directories:

south/tractor
north/tractor

Downloaded using bbcp from services01.euclid into CephFS scratch:

bbcp -s 16 -a -A -v -z -r -S "ssh -x -a -oFallBackToRsh=no %I -l %U %H /usr/common/usg/bin/bbcp" \
"jcarrete@dtn01.nersc.gov:/global/cfs/cdirs/cosmo/work/legacysurvey/dr9m/north/tractor" \
/cephfs/pic.es/astro/scratch/tallada/dr9m/north/tractor
bbcp -s 16 -a -A -v -z -r -S "ssh -x -a -oFallBackToRsh=no %I -l %U %H /usr/common/usg/bin/bbcp" \
"jcarrete@dtn01.nersc.gov:/global/cfs/cdirs/cosmo/work/legacysurvey/dr9m/south/tractor" \
/cephfs/pic.es/astro/scratch/tallada/dr9m/south/tractor

Checksums provided through sha256sum files, and checked using command:

find * -type d | xargs -t -L1 -P24 sh -c 'cd $1; sha256sum --quiet -c *.sha256sum' cksum

Parquet

The following code snippet was used in spark to convert fits files to parquet:

path_base = '/cephfs/pic.es/astro/scratch/tallada/dr9m'
hdfs_base = '/user/tallada/data/legacysurvey_dr9'

hs = ['h=north', 'h=south']
rs = ['r={0:03d}'.format(x) for x in range(360)]

for h in hs:
    for r in rs:
        p = os.path.join(path_base, h, r)
        if not os.path.exists(p):
            continue
        
        size = sum(os.path.getsize(os.path.join(p,f)) for f in os.listdir(p) if os.path.isfile(os.path.join(p,f)))
       
        print h, r, size        
        
        df = spark.read.format("fits").option("hdu", 1).load(
            "file://" + p
        )
        
        df = df.repartition(int(math.ceil(size / (256*1024*1024.0))))
                
        df.write.parquet(os.path.join(hdfs_base, h, r))

External table

CREATE EXTERNAL TABLE `tallada`.`legacysurvey_dr9`(
 `release` smallint, 
 `brickid` int, 
 `brickname` string, 
 `objid` int, 
 `brick_primary` boolean, 
 `maskbits` smallint, 
 `fitbits` smallint, 
 `type` string, 
 `ra` double, 
 `dec` double, 
 `ra_ivar` float, 
 `dec_ivar` float, 
 `bx` float, 
 `by` float, 
 `dchisq` array<float>, 
 `ebv` float, 
 `mjd_min` double, 
 `mjd_max` double, 
 `ref_cat` string, 
 `ref_id` bigint, 
 `pmra` float, 
 `pmdec` float, 
 `parallax` float, 
 `pmra_ivar` float, 
 `pmdec_ivar` float, 
 `parallax_ivar` float, 
 `ref_epoch` float, 
 `gaia_phot_g_mean_mag` float, 
 `gaia_phot_g_mean_flux_over_error` float, 
 `gaia_phot_g_n_obs` smallint, 
 `gaia_phot_bp_mean_mag` float, 
 `gaia_phot_bp_mean_flux_over_error` float, 
 `gaia_phot_bp_n_obs` smallint, 
 `gaia_phot_rp_mean_mag` float, 
 `gaia_phot_rp_mean_flux_over_error` float, 
 `gaia_phot_rp_n_obs` smallint, 
 `gaia_phot_variable_flag` boolean, 
 `gaia_astrometric_excess_noise` float, 
 `gaia_astrometric_excess_noise_sig` float, 
 `gaia_astrometric_n_obs_al` smallint, 
 `gaia_astrometric_n_good_obs_al` smallint, 
 `gaia_astrometric_weight_al` float, 
 `gaia_duplicated_source` boolean, 
 `gaia_a_g_val` float, 
 `gaia_e_bp_min_rp_val` float, 
 `gaia_phot_bp_rp_excess_factor` float, 
 `gaia_astrometric_sigma5d_max` float, 
 `gaia_astrometric_params_solved` tinyint, 
 `flux_g` float, 
 `flux_r` float, 
 `flux_z` float, 
 `flux_w1` float, 
 `flux_w2` float, 
 `flux_w3` float, 
 `flux_w4` float, 
 `flux_ivar_g` float, 
 `flux_ivar_r` float, 
 `flux_ivar_z` float, 
 `flux_ivar_w1` float, 
 `flux_ivar_w2` float, 
 `flux_ivar_w3` float, 
 `flux_ivar_w4` float, 
 `fiberflux_g` float, 
 `fiberflux_r` float, 
 `fiberflux_z` float, 
 `fibertotflux_g` float, 
 `fibertotflux_r` float, 
 `fibertotflux_z` float, 
 `apflux_g` array<float>, 
 `apflux_r` array<float>, 
 `apflux_z` array<float>, 
 `apflux_resid_g` array<float>, 
 `apflux_resid_r` array<float>, 
 `apflux_resid_z` array<float>, 
 `apflux_blobresid_g` array<float>, 
 `apflux_blobresid_r` array<float>, 
 `apflux_blobresid_z` array<float>, 
 `apflux_ivar_g` array<float>, 
 `apflux_ivar_r` array<float>, 
 `apflux_ivar_z` array<float>, 
 `apflux_masked_g` array<float>, 
 `apflux_masked_r` array<float>, 
 `apflux_masked_z` array<float>, 
 `apflux_w1` array<float>, 
 `apflux_w2` array<float>, 
 `apflux_w3` array<float>, 
 `apflux_w4` array<float>, 
 `apflux_resid_w1` array<float>, 
 `apflux_resid_w2` array<float>, 
 `apflux_resid_w3` array<float>, 
 `apflux_resid_w4` array<float>, 
 `apflux_ivar_w1` array<float>, 
 `apflux_ivar_w2` array<float>, 
 `apflux_ivar_w3` array<float>, 
 `apflux_ivar_w4` array<float>, 
 `mw_transmission_g` float, 
 `mw_transmission_r` float, 
 `mw_transmission_z` float, 
 `mw_transmission_w1` float, 
 `mw_transmission_w2` float, 
 `mw_transmission_w3` float, 
 `mw_transmission_w4` float, 
 `nobs_g` smallint, 
 `nobs_r` smallint, 
 `nobs_z` smallint, 
 `nobs_w1` smallint, 
 `nobs_w2` smallint, 
 `nobs_w3` smallint, 
 `nobs_w4` smallint, 
 `rchisq_g` float, 
 `rchisq_r` float, 
 `rchisq_z` float, 
 `rchisq_w1` float, 
 `rchisq_w2` float, 
 `rchisq_w3` float, 
 `rchisq_w4` float, 
 `fracflux_g` float, 
 `fracflux_r` float, 
 `fracflux_z` float, 
 `fracflux_w1` float, 
 `fracflux_w2` float, 
 `fracflux_w3` float, 
 `fracflux_w4` float, 
 `fracmasked_g` float, 
 `fracmasked_r` float, 
 `fracmasked_z` float, 
 `fracin_g` float, 
 `fracin_r` float, 
 `fracin_z` float, 
 `anymask_g` smallint, 
 `anymask_r` smallint, 
 `anymask_z` smallint, 
 `allmask_g` smallint, 
 `allmask_r` smallint, 
 `allmask_z` smallint, 
 `wisemask_w1` tinyint, 
 `wisemask_w2` tinyint, 
 `psfsize_g` float, 
 `psfsize_r` float, 
 `psfsize_z` float, 
 `psfdepth_g` float, 
 `psfdepth_r` float, 
 `psfdepth_z` float, 
 `galdepth_g` float, 
 `galdepth_r` float, 
 `galdepth_z` float, 
 `nea_g` float, 
 `nea_r` float, 
 `nea_z` float, 
 `blob_nea_g` float, 
 `blob_nea_r` float, 
 `blob_nea_z` float, 
 `psfdepth_w1` float, 
 `psfdepth_w2` float, 
 `psfdepth_w3` float, 
 `psfdepth_w4` float, 
 `wise_coadd_id` string, 
 `wise_x` float, 
 `wise_y` float, 
 `lc_flux_w1` array<float>, 
 `lc_flux_w2` array<float>, 
 `lc_flux_ivar_w1` array<float>, 
 `lc_flux_ivar_w2` array<float>, 
 `lc_nobs_w1` array<smallint>, 
 `lc_nobs_w2` array<smallint>, 
 `lc_fracflux_w1` array<float>, 
 `lc_fracflux_w2` array<float>, 
 `lc_rchisq_w1` array<float>, 
 `lc_rchisq_w2` array<float>, 
 `lc_mjd_w1` array<double>, 
 `lc_mjd_w2` array<double>, 
 `lc_epoch_index_w1` array<smallint>, 
 `lc_epoch_index_w2` array<smallint>, 
 `sersic` float, 
 `sersic_ivar` float, 
 `shape_r` float, 
 `shape_r_ivar` float, 
 `shape_e1` float, 
 `shape_e1_ivar` float, 
 `shape_e2` float, 
 `shape_e2_ivar` float)
PARTITIONED BY (
  h STRING,
  r STRING
)
STORED AS PARQUET
LOCATION '/user/tallada/data/legacysurvey_dr9'
;

Internal Table

CREATE TABLE `cosmohub`.`legacy_survey_dr9`(
 `release` smallint,
 `brickid` int,
 `brickname` string,
 `objid` int,
 `brick_primary` boolean,
 `maskbits` smallint,
 `fitbits` smallint,
 `type` string,
 `ra` double,
 `dec` double,
 `ra_ivar` float,
 `dec_ivar` float,
 `bx` float,
 `by` float,
 `dchisq` array<float>,
 `ebv` float,
 `mjd_min` double,
 `mjd_max` double,
 `ref_cat` string,
 `ref_id` bigint,
 `pmra` float,
 `pmdec` float,
 `parallax` float,
 `pmra_ivar` float,
 `pmdec_ivar` float,
 `parallax_ivar` float,
 `ref_epoch` float,
 `gaia_phot_g_mean_mag` float,
 `gaia_phot_g_mean_flux_over_error` float,
 `gaia_phot_g_n_obs` smallint,
 `gaia_phot_bp_mean_mag` float,
 `gaia_phot_bp_mean_flux_over_error` float,
 `gaia_phot_bp_n_obs` smallint,
 `gaia_phot_rp_mean_mag` float,
 `gaia_phot_rp_mean_flux_over_error` float,
 `gaia_phot_rp_n_obs` smallint,
 `gaia_phot_variable_flag` boolean,
 `gaia_astrometric_excess_noise` float,
 `gaia_astrometric_excess_noise_sig` float,
 `gaia_astrometric_n_obs_al` smallint,
 `gaia_astrometric_n_good_obs_al` smallint,
 `gaia_astrometric_weight_al` float,
 `gaia_duplicated_source` boolean,
 `gaia_a_g_val` float,
 `gaia_e_bp_min_rp_val` float,
 `gaia_phot_bp_rp_excess_factor` float,
 `gaia_astrometric_sigma5d_max` float,
 `gaia_astrometric_params_solved` tinyint,
 `flux_g` float,
 `flux_r` float,
 `flux_z` float,
 `flux_w1` float,
 `flux_w2` float,
 `flux_w3` float,
 `flux_w4` float,
 `flux_ivar_g` float,
 `flux_ivar_r` float,
 `flux_ivar_z` float,
 `flux_ivar_w1` float,
 `flux_ivar_w2` float,
 `flux_ivar_w3` float,
 `flux_ivar_w4` float,
 `fiberflux_g` float,
 `fiberflux_r` float,
 `fiberflux_z` float,
 `fibertotflux_g` float,
 `fibertotflux_r` float,
 `fibertotflux_z` float,
 `apflux_g` array<float>,
 `apflux_r` array<float>,
 `apflux_z` array<float>,
 `apflux_resid_g` array<float>,
 `apflux_resid_r` array<float>,
 `apflux_resid_z` array<float>,
 `apflux_blobresid_g` array<float>,
 `apflux_blobresid_r` array<float>,
 `apflux_blobresid_z` array<float>,
 `apflux_ivar_g` array<float>,
 `apflux_ivar_r` array<float>,
 `apflux_ivar_z` array<float>,
 `apflux_masked_g` array<float>,
 `apflux_masked_r` array<float>,
 `apflux_masked_z` array<float>,
 `apflux_w1` array<float>,
 `apflux_w2` array<float>,
 `apflux_w3` array<float>,
 `apflux_w4` array<float>,
 `apflux_resid_w1` array<float>,
 `apflux_resid_w2` array<float>,
 `apflux_resid_w3` array<float>,
 `apflux_resid_w4` array<float>,
 `apflux_ivar_w1` array<float>,
 `apflux_ivar_w2` array<float>,
 `apflux_ivar_w3` array<float>,
 `apflux_ivar_w4` array<float>,
 `mw_transmission_g` float,
 `mw_transmission_r` float,
 `mw_transmission_z` float,
 `mw_transmission_w1` float,
 `mw_transmission_w2` float,
 `mw_transmission_w3` float,
 `mw_transmission_w4` float,
 `nobs_g` smallint,
 `nobs_r` smallint,
 `nobs_z` smallint,
 `nobs_w1` smallint,
 `nobs_w2` smallint,
 `nobs_w3` smallint,
 `nobs_w4` smallint,
 `rchisq_g` float,
 `rchisq_r` float,
 `rchisq_z` float,
 `rchisq_w1` float,
 `rchisq_w2` float,
 `rchisq_w3` float,
 `rchisq_w4` float,
 `fracflux_g` float,
 `fracflux_r` float,
 `fracflux_z` float,
 `fracflux_w1` float,
 `fracflux_w2` float,
 `fracflux_w3` float,
 `fracflux_w4` float,
 `fracmasked_g` float,
 `fracmasked_r` float,
 `fracmasked_z` float,
 `fracin_g` float,
 `fracin_r` float,
 `fracin_z` float,
 `anymask_g` smallint,
 `anymask_r` smallint,
 `anymask_z` smallint,
 `allmask_g` smallint,
 `allmask_r` smallint,
 `allmask_z` smallint,
 `wisemask_w1` tinyint,
 `wisemask_w2` tinyint,
 `psfsize_g` float,
 `psfsize_r` float,
 `psfsize_z` float,
 `psfdepth_g` float,
 `psfdepth_r` float,
 `psfdepth_z` float,
 `galdepth_g` float,
 `galdepth_r` float,
 `galdepth_z` float,
 `nea_g` float,
 `nea_r` float,
 `nea_z` float,
 `blob_nea_g` float,
 `blob_nea_r` float,
 `blob_nea_z` float,
 `psfdepth_w1` float,
 `psfdepth_w2` float,
 `psfdepth_w3` float,
 `psfdepth_w4` float,
 `wise_coadd_id` string,
 `wise_x` float,
 `wise_y` float,
 `lc_flux_w1` array<float>,
 `lc_flux_w2` array<float>,
 `lc_flux_ivar_w1` array<float>,
 `lc_flux_ivar_w2` array<float>,
 `lc_nobs_w1` array<smallint>,
 `lc_nobs_w2` array<smallint>,
 `lc_fracflux_w1` array<float>,
 `lc_fracflux_w2` array<float>,
 `lc_rchisq_w1` array<float>,
 `lc_rchisq_w2` array<float>,
 `lc_mjd_w1` array<double>,
 `lc_mjd_w2` array<double>,
 `lc_epoch_index_w1` array<smallint>,
 `lc_epoch_index_w2` array<smallint>,
 `sersic` float,
 `sersic_ivar` float,
 `shape_r` float,
 `shape_r_ivar` float,
 `shape_e1` float,
 `shape_e1_ivar` float,
 `shape_e2` float,
 `shape_e2_ivar` float)
CLUSTERED BY (
 release, brickid, objid
)
SORTED BY (
 release, brickid, objid
)
INTO 4096 BUCKETS
STORED AS ORC
;

INSERT INTO TABLE cosmohub.legacy_survey_dr9
SELECT  release, brickid, brickname, objid, brick_primary, maskbits, fitbits, type, ra, `dec`, ra_ivar, dec_ivar,
bx, `by`, dchisq, ebv, mjd_min, mjd_max, ref_cat, ref_id, pmra, pmdec, parallax, pmra_ivar, pmdec_ivar,
parallax_ivar, ref_epoch, gaia_phot_g_mean_mag, gaia_phot_g_mean_flux_over_error, gaia_phot_g_n_obs,
gaia_phot_bp_mean_mag, gaia_phot_bp_mean_flux_over_error, gaia_phot_bp_n_obs, gaia_phot_rp_mean_mag,
gaia_phot_rp_mean_flux_over_error, gaia_phot_rp_n_obs, gaia_phot_variable_flag, gaia_astrometric_excess_noise,
gaia_astrometric_excess_noise_sig, gaia_astrometric_n_obs_al, gaia_astrometric_n_good_obs_al,
gaia_astrometric_weight_al, gaia_duplicated_source, gaia_a_g_val, gaia_e_bp_min_rp_val,
gaia_phot_bp_rp_excess_factor, gaia_astrometric_sigma5d_max, gaia_astrometric_params_solved, flux_g, flux_r,
flux_z, flux_w1, flux_w2, flux_w3, flux_w4, flux_ivar_g, flux_ivar_r, flux_ivar_z, flux_ivar_w1, flux_ivar_w2,
flux_ivar_w3, flux_ivar_w4, fiberflux_g, fiberflux_r, fiberflux_z, fibertotflux_g, fibertotflux_r, fibertotflux_z,
apflux_g, apflux_r, apflux_z, apflux_resid_g, apflux_resid_r, apflux_resid_z, apflux_blobresid_g,
apflux_blobresid_r, apflux_blobresid_z, apflux_ivar_g, apflux_ivar_r, apflux_ivar_z, apflux_masked_g,
apflux_masked_r, apflux_masked_z, apflux_w1, apflux_w2, apflux_w3, apflux_w4, apflux_resid_w1, apflux_resid_w2,
apflux_resid_w3, apflux_resid_w4, apflux_ivar_w1, apflux_ivar_w2, apflux_ivar_w3, apflux_ivar_w4,
mw_transmission_g, mw_transmission_r, mw_transmission_z, mw_transmission_w1, mw_transmission_w2,
mw_transmission_w3, mw_transmission_w4, nobs_g, nobs_r, nobs_z, nobs_w1, nobs_w2, nobs_w3, nobs_w4, rchisq_g,
rchisq_r, rchisq_z, rchisq_w1, rchisq_w2, rchisq_w3, rchisq_w4, fracflux_g, fracflux_r, fracflux_z, fracflux_w1,
fracflux_w2, fracflux_w3, fracflux_w4, fracmasked_g, fracmasked_r, fracmasked_z, fracin_g, fracin_r, fracin_z,
anymask_g, anymask_r, anymask_z, allmask_g, allmask_r, allmask_z, wisemask_w1, wisemask_w2, psfsize_g, psfsize_r,
psfsize_z, psfdepth_g, psfdepth_r, psfdepth_z, galdepth_g, galdepth_r, galdepth_z, nea_g, nea_r, nea_z,
blob_nea_g, blob_nea_r, blob_nea_z, psfdepth_w1, psfdepth_w2, psfdepth_w3, psfdepth_w4, wise_coadd_id, wise_x,
wise_y, lc_flux_w1, lc_flux_w2, lc_flux_ivar_w1, lc_flux_ivar_w2, lc_nobs_w1, lc_nobs_w2, lc_fracflux_w1,
lc_fracflux_w2, lc_rchisq_w1, lc_rchisq_w2, lc_mjd_w1, lc_mjd_w2, lc_epoch_index_w1, lc_epoch_index_w2, sersic,
sersic_ivar, shape_r, shape_r_ivar, shape_e1, shape_e1_ivar, shape_e2, shape_e2_ivar
FROM cosmohub.legacy_survey_dr9_p
;

Comments

ALTER TABLE legacy_survey_dr9 CHANGE release release smallint COMMENT "Unique integer denoting the camera and filter set used" ;
ALTER TABLE legacy_survey_dr9 CHANGE brickid brickid int COMMENT "Brick ID [1,662174]" ;
ALTER TABLE legacy_survey_dr9 CHANGE brickname brickname string COMMENT "Name of brick, encoding the brick sky position, eg \u00221126p222\u0022 near RA=112.6, Dec=+22.2" ;
ALTER TABLE legacy_survey_dr9 CHANGE objid objid int COMMENT "Catalog object number within this brick\u003b a unique identifier hash is release,brickid,objid\u003b objid spans [0,N-1] and is contiguously enumerated within each brick" ;
ALTER TABLE legacy_survey_dr9 CHANGE brick_primary brick_primary boolean COMMENT "True if the object is within the brick boundary" ;
ALTER TABLE legacy_survey_dr9 CHANGE maskbits maskbits smallint COMMENT "Bitwise mask indicating that an object touches a pixel in the coadd/*/*/*maskbits* maps, as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE fitbits fitbits smallint COMMENT "Bitwise mask detailing pecularities of how an object was fit, as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE type type string COMMENT "Morphological model: \u0022PSF\u0022=stellar, \u0022REX\u0022=\u0022round exponential galaxy\u0022, \u0022DEV\u0022=deVauc, \u0022EXP\u0022=exponential, \u0022SER\u0022=Sersic, \u0022DUP\u0022=Gaia source fit by different model." ;
ALTER TABLE legacy_survey_dr9 CHANGE ra ra double COMMENT "Right ascension at equinox J2000" ;
ALTER TABLE legacy_survey_dr9 CHANGE `dec` `dec` double COMMENT "Declination at equinox J2000" ;
ALTER TABLE legacy_survey_dr9 CHANGE ra_ivar ra_ivar float COMMENT "Inverse variance of RA (no cosine term!), excluding astrometric calibration errors" ;
ALTER TABLE legacy_survey_dr9 CHANGE dec_ivar dec_ivar float COMMENT "Inverse variance of DEC, excluding astrometric calibration errors" ;
ALTER TABLE legacy_survey_dr9 CHANGE bx bx float COMMENT "X position (0-indexed) of coordinates in the brick image stack (i.e. in the e.g. legacysurvey-<brick>-image-g.fits.fz coadd file)" ;
ALTER TABLE legacy_survey_dr9 CHANGE `by` `by` float COMMENT "Y position (0-indexed) of coordinates in brick image stack" ;
ALTER TABLE legacy_survey_dr9 CHANGE dchisq dchisq array<float> COMMENT "Difference in χ² between successively more-complex model fits: PSF, REX, DEV, EXP, SER. The difference is versus no source." ;
ALTER TABLE legacy_survey_dr9 CHANGE ebv ebv float COMMENT "Galactic extinction E(B-V) reddening from SFD98, used to compute the mw_transmission_ columns" ;
ALTER TABLE legacy_survey_dr9 CHANGE mjd_min mjd_min double COMMENT "Minimum Modified Julian Date of observations used to construct the model of this object" ;
ALTER TABLE legacy_survey_dr9 CHANGE mjd_max mjd_max double COMMENT "Maximum Modified Julian Date of observations used to construct the model of this object" ;
ALTER TABLE legacy_survey_dr9 CHANGE ref_cat ref_cat string COMMENT "Reference catalog source for this star: \u0022T2\u0022 for Tycho-2, \u0022G2\u0022 for Gaia DR2, \u0022L3\u0022 for the SGA, empty otherwise" ;
ALTER TABLE legacy_survey_dr9 CHANGE ref_id ref_id bigint COMMENT "Reference catalog identifier for this star\u003b Tyc1*1,000,000+Tyc2*10+Tyc3 for Tycho2\u003b \u0022sourceid\u0022 for Gaia DR2 and SGA" ;
ALTER TABLE legacy_survey_dr9 CHANGE pmra pmra float COMMENT "Reference catalog proper motion in the RA direction" ;
ALTER TABLE legacy_survey_dr9 CHANGE pmdec pmdec float COMMENT "Reference catalog proper motion in the Dec direction" ;
ALTER TABLE legacy_survey_dr9 CHANGE parallax parallax float COMMENT "Reference catalog parallax" ;
ALTER TABLE legacy_survey_dr9 CHANGE pmra_ivar pmra_ivar float COMMENT "Reference catalog inverse-variance on pmra" ;
ALTER TABLE legacy_survey_dr9 CHANGE pmdec_ivar pmdec_ivar float COMMENT "Reference catalog inverse-variance on pmdec" ;
ALTER TABLE legacy_survey_dr9 CHANGE parallax_ivar parallax_ivar float COMMENT "Reference catalog inverse-variance on parallax" ;
ALTER TABLE legacy_survey_dr9 CHANGE ref_epoch ref_epoch float COMMENT "Reference catalog reference epoch (eg, 2015.5 for Gaia DR2)" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_g_mean_mag gaia_phot_g_mean_mag float COMMENT "Gaia G band mag" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_g_mean_flux_over_error gaia_phot_g_mean_flux_over_error float COMMENT "Gaia G band signal-to-noise" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_g_n_obs gaia_phot_g_n_obs smallint COMMENT "Gaia G band number of observations" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_bp_mean_mag gaia_phot_bp_mean_mag float COMMENT "Gaia BP mag" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_bp_mean_flux_over_error gaia_phot_bp_mean_flux_over_error float COMMENT "Gaia BP signal-to-noise" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_bp_n_obs gaia_phot_bp_n_obs smallint COMMENT "Gaia BP number of observations" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_rp_mean_mag gaia_phot_rp_mean_mag float COMMENT "Gaia RP mag" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_rp_mean_flux_over_error gaia_phot_rp_mean_flux_over_error float COMMENT "Gaia RP signal-to-noise" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_rp_n_obs gaia_phot_rp_n_obs smallint COMMENT "Gaia RP number of observations" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_variable_flag gaia_phot_variable_flag boolean COMMENT "Gaia photometric variable flag" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_astrometric_excess_noise gaia_astrometric_excess_noise float COMMENT "Gaia astrometric excess noise" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_astrometric_excess_noise_sig gaia_astrometric_excess_noise_sig float COMMENT "Gaia astrometric excess noise uncertainty" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_astrometric_n_obs_al gaia_astrometric_n_obs_al smallint COMMENT "Gaia number of astrometric observations along scan direction" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_astrometric_n_good_obs_al gaia_astrometric_n_good_obs_al smallint COMMENT "Gaia number of good astrometric observations along scan direction" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_astrometric_weight_al gaia_astrometric_weight_al float COMMENT "Gaia astrometric weight along scan direction" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_duplicated_source gaia_duplicated_source boolean COMMENT "Gaia duplicated source flag" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_a_g_val gaia_a_g_val float COMMENT "Gaia line-of-sight extinction in the G band" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_e_bp_min_rp_val gaia_e_bp_min_rp_val float COMMENT "Gaia line-of-sight reddening E(BP-RP)" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_phot_bp_rp_excess_factor gaia_phot_bp_rp_excess_factor float COMMENT "Gaia BP/RP excess factor" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_astrometric_sigma5d_max gaia_astrometric_sigma5d_max float COMMENT "Gaia longest semi-major axis of the 5-d error ellipsoid" ;
ALTER TABLE legacy_survey_dr9 CHANGE gaia_astrometric_params_solved gaia_astrometric_params_solved tinyint COMMENT "which astrometric parameters were estimated for a Gaia source" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_g flux_g float COMMENT "model flux in gg" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_r flux_r float COMMENT "model flux in rr" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_z flux_z float COMMENT "model flux in zz" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_w1 flux_w1 float COMMENT "WISE model flux in W1W1 (AB system)" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_w2 flux_w2 float COMMENT "WISE model flux in W2W2 (AB)" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_w3 flux_w3 float COMMENT "WISE model flux in W3W3 (AB)" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_w4 flux_w4 float COMMENT "WISE model flux in W4W4 (AB)" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_ivar_g flux_ivar_g float COMMENT "Inverse variance of flux_g" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_ivar_r flux_ivar_r float COMMENT "Inverse variance of flux_r" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_ivar_z flux_ivar_z float COMMENT "Inverse variance of flux_z" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_ivar_w1 flux_ivar_w1 float COMMENT "Inverse variance of flux_w1 (AB system)" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_ivar_w2 flux_ivar_w2 float COMMENT "Inverse variance of flux_w2 (AB)" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_ivar_w3 flux_ivar_w3 float COMMENT "Inverse variance of flux_w3 (AB)" ;
ALTER TABLE legacy_survey_dr9 CHANGE flux_ivar_w4 flux_ivar_w4 float COMMENT "Inverse variance of flux_w4 (AB)" ;
ALTER TABLE legacy_survey_dr9 CHANGE fiberflux_g fiberflux_g float COMMENT "Predicted gg-band flux within a fiber of diameter 1.5 arcsec from this object in 1 arcsec Gaussian seeing" ;
ALTER TABLE legacy_survey_dr9 CHANGE fiberflux_r fiberflux_r float COMMENT "Predicted rr-band flux within a fiber of diameter 1.5 arcsec from this object in 1 arcsec Gaussian seeing" ;
ALTER TABLE legacy_survey_dr9 CHANGE fiberflux_z fiberflux_z float COMMENT "Predicted zz-band flux within a fiber of diameter 1.5 arcsec from this object in 1 arcsec Gaussian seeing" ;
ALTER TABLE legacy_survey_dr9 CHANGE fibertotflux_g fibertotflux_g float COMMENT "Predicted gg-band flux within a fiber of diameter 1.5 arcsec from all sources at this location in 1 arcsec Gaussian seeing" ;
ALTER TABLE legacy_survey_dr9 CHANGE fibertotflux_r fibertotflux_r float COMMENT "Predicted rr-band flux within a fiber of diameter 1.5 arcsec from all sources at this location in 1 arcsec Gaussian seeing" ;
ALTER TABLE legacy_survey_dr9 CHANGE fibertotflux_z fibertotflux_z float COMMENT "Predicted zz-band flux within a fiber of diameter 1.5 arcsec from all sources at this location in 1 arcsec Gaussian seeing" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_g apflux_g array<float> COMMENT "Aperture fluxes on the co-added images in apertures of radius [0.5, 0.75, 1.0, 1.5, 2.0, 3.5, 5.0, 7.0] arcsec in gg, masked by invvar=0invvar=0 (inverse variance of zero [1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_r apflux_r array<float> COMMENT "Aperture fluxes on the co-added images in apertures of radius [0.5, 0.75, 1.0, 1.5, 2.0, 3.5, 5.0, 7.0] arcsec in rr, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_z apflux_z array<float> COMMENT "Aperture fluxes on the co-added images in apertures of radius [0.5, 0.75, 1.0, 1.5, 2.0, 3.5, 5.0, 7.0] arcsec in zz, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_resid_g apflux_resid_g array<float> COMMENT "Aperture fluxes on the co-added residual images in gg, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_resid_r apflux_resid_r array<float> COMMENT "Aperture fluxes on the co-added residual images in rr, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_resid_z apflux_resid_z array<float> COMMENT "Aperture fluxes on the co-added residual images in zz, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_blobresid_g apflux_blobresid_g array<float> COMMENT "Aperture fluxes on image−blobmodelimage−blobmodel residual maps in gg [2], masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_blobresid_r apflux_blobresid_r array<float> COMMENT "Aperture fluxes on image−blobmodelimage−blobmodel residual maps in rr, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_blobresid_z apflux_blobresid_z array<float> COMMENT "Aperture fluxes on image−blobmodelimage−blobmodel residual maps in zz, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_ivar_g apflux_ivar_g array<float> COMMENT "Inverse variance of apflux_resid_g, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_ivar_r apflux_ivar_r array<float> COMMENT "Inverse variance of apflux_resid_r, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_ivar_z apflux_ivar_z array<float> COMMENT "Inverse variance of apflux_resid_z, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_masked_g apflux_masked_g array<float> COMMENT "Fraction of pixels masked in gg-band aperture flux measurements\u003b 1 means fully masked (ie, fully ignored\u003b contributing zero to the measurement)" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_masked_r apflux_masked_r array<float> COMMENT "Fraction of pixels masked in rr-band aperture flux measurements\u003b 1 means fully masked (ie, fully ignored\u003b contributing zero to the measurement)" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_masked_z apflux_masked_z array<float> COMMENT "Fraction of pixels masked in zz-band aperture flux measurements\u003b 1 means fully masked (ie, fully ignored\u003b contributing zero to the measurement)" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_w1 apflux_w1 array<float> COMMENT "Aperture fluxes on the co-added images in apertures of radius [3, 5, 7, 9, 11] [3] arcsec in W1W1, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_w2 apflux_w2 array<float> COMMENT "Aperture fluxes on the co-added images in apertures of radius [3, 5, 7, 9, 11] arcsec in W2W2, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_w3 apflux_w3 array<float> COMMENT "Aperture fluxes on the co-added images in apertures of radius [3, 5, 7, 9, 11] arcsec in W3W3, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_w4 apflux_w4 array<float> COMMENT "Aperture fluxes on the co-added images in apertures of radius [3, 5, 7, 9, 11] arcsec in W4W4, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_resid_w1 apflux_resid_w1 array<float> COMMENT "Aperture fluxes on the co-added residual images in W1W1, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_resid_w2 apflux_resid_w2 array<float> COMMENT "Aperture fluxes on the co-added residual images in W2W2, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_resid_w3 apflux_resid_w3 array<float> COMMENT "Aperture fluxes on the co-added residual images in W3W3, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_resid_w4 apflux_resid_w4 array<float> COMMENT "Aperture fluxes on the co-added residual images in W4W4, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_ivar_w1 apflux_ivar_w1 array<float> COMMENT "Inverse variance of apflux_resid_w1, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_ivar_w2 apflux_ivar_w2 array<float> COMMENT "Inverse variance of apflux_resid_w2, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_ivar_w3 apflux_ivar_w3 array<float> COMMENT "Inverse variance of apflux_resid_w3, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE apflux_ivar_w4 apflux_ivar_w4 array<float> COMMENT "Inverse variance of apflux_resid_w4, masked by invvar=0invvar=0" ;
ALTER TABLE legacy_survey_dr9 CHANGE mw_transmission_g mw_transmission_g float COMMENT "Galactic transmission in gg filter in linear units [0, 1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE mw_transmission_r mw_transmission_r float COMMENT "Galactic transmission in rr filter in linear units [0, 1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE mw_transmission_z mw_transmission_z float COMMENT "Galactic transmission in zz filter in linear units [0, 1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE mw_transmission_w1 mw_transmission_w1 float COMMENT "Galactic transmission in W1W1 filter in linear units [0, 1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE mw_transmission_w2 mw_transmission_w2 float COMMENT "Galactic transmission in W2W2 filter in linear units [0, 1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE mw_transmission_w3 mw_transmission_w3 float COMMENT "Galactic transmission in W3W3 filter in linear units [0, 1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE mw_transmission_w4 mw_transmission_w4 float COMMENT "Galactic transmission in W4W4 filter in linear units [0, 1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE nobs_g nobs_g smallint COMMENT "Number of images that contribute to the central pixel in gg: filter for this object (not profile-weighted)" ;
ALTER TABLE legacy_survey_dr9 CHANGE nobs_r nobs_r smallint COMMENT "Number of images that contribute to the central pixel in rr: filter for this object (not profile-weighted)" ;
ALTER TABLE legacy_survey_dr9 CHANGE nobs_z nobs_z smallint COMMENT "Number of images that contribute to the central pixel in zz: filter for this object (not profile-weighted)" ;
ALTER TABLE legacy_survey_dr9 CHANGE nobs_w1 nobs_w1 smallint COMMENT "Number of images that contribute to the central pixel in W1W1: filter for this object (not profile-weighted)" ;
ALTER TABLE legacy_survey_dr9 CHANGE nobs_w2 nobs_w2 smallint COMMENT "Number of images that contribute to the central pixel in W2W2: filter for this object (not profile-weighted)" ;
ALTER TABLE legacy_survey_dr9 CHANGE nobs_w3 nobs_w3 smallint COMMENT "Number of images that contribute to the central pixel in W3W3: filter for this object (not profile-weighted)" ;
ALTER TABLE legacy_survey_dr9 CHANGE nobs_w4 nobs_w4 smallint COMMENT "Number of images that contribute to the central pixel in W4W4: filter for this object (not profile-weighted)" ;
ALTER TABLE legacy_survey_dr9 CHANGE rchisq_g rchisq_g float COMMENT "Profile-weighted χ² of model fit normalized by the number of pixels in gg" ;
ALTER TABLE legacy_survey_dr9 CHANGE rchisq_r rchisq_r float COMMENT "Profile-weighted χ² of model fit normalized by the number of pixels in rr" ;
ALTER TABLE legacy_survey_dr9 CHANGE rchisq_z rchisq_z float COMMENT "Profile-weighted χ² of model fit normalized by the number of pixels in zz" ;
ALTER TABLE legacy_survey_dr9 CHANGE rchisq_w1 rchisq_w1 float COMMENT "Profile-weighted χ² of model fit normalized by the number of pixels in W1W1" ;
ALTER TABLE legacy_survey_dr9 CHANGE rchisq_w2 rchisq_w2 float COMMENT "Profile-weighted χ² of model fit normalized by the number of pixels in W2W2" ;
ALTER TABLE legacy_survey_dr9 CHANGE rchisq_w3 rchisq_w3 float COMMENT "Profile-weighted χ² of model fit normalized by the number of pixels in W3W3" ;
ALTER TABLE legacy_survey_dr9 CHANGE rchisq_w4 rchisq_w4 float COMMENT "Profile-weighted χ² of model fit normalized by the number of pixels in W4W4" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracflux_g fracflux_g float COMMENT "Profile-weighted fraction of the flux from other sources divided by the total flux in gg (typically [0,1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracflux_r fracflux_r float COMMENT "Profile-weighted fraction of the flux from other sources divided by the total flux in rr (typically [0,1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracflux_z fracflux_z float COMMENT "Profile-weighted fraction of the flux from other sources divided by the total flux in zz (typically [0,1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracflux_w1 fracflux_w1 float COMMENT "Profile-weighted fraction of the flux from other sources divided by the total flux in W1W1 (typically [0,1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracflux_w2 fracflux_w2 float COMMENT "Profile-weighted fraction of the flux from other sources divided by the total flux in W2W2 (typically [0,1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracflux_w3 fracflux_w3 float COMMENT "Profile-weighted fraction of the flux from other sources divided by the total flux in W3W3 (typically [0,1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracflux_w4 fracflux_w4 float COMMENT "Profile-weighted fraction of the flux from other sources divided by the total flux in W4W4 (typically [0,1])" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracmasked_g fracmasked_g float COMMENT "Profile-weighted fraction of pixels masked from all observations of this object in gg, strictly between [0,1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracmasked_r fracmasked_r float COMMENT "Profile-weighted fraction of pixels masked from all observations of this object in rr, strictly between [0,1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracmasked_z fracmasked_z float COMMENT "Profile-weighted fraction of pixels masked from all observations of this object in zz, strictly between [0,1]" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracin_g fracin_g float COMMENT "Fraction of a source's flux within the blob in gg, near unity for real sources" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracin_r fracin_r float COMMENT "Fraction of a source's flux within the blob in rr, near unity for real sources" ;
ALTER TABLE legacy_survey_dr9 CHANGE fracin_z fracin_z float COMMENT "Fraction of a source's flux within the blob in zz, near unity for real sources" ;
ALTER TABLE legacy_survey_dr9 CHANGE anymask_g anymask_g smallint COMMENT "Bitwise mask set if the central pixel from any image satisfies each condition in gg as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE anymask_r anymask_r smallint COMMENT "Bitwise mask set if the central pixel from any image satisfies each condition in rr as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE anymask_z anymask_z smallint COMMENT "Bitwise mask set if the central pixel from any image satisfies each condition in zz as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE allmask_g allmask_g smallint COMMENT "Bitwise mask set if the central pixel from all images satisfy each condition in gg as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE allmask_r allmask_r smallint COMMENT "Bitwise mask set if the central pixel from all images satisfy each condition in rr as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE allmask_z allmask_z smallint COMMENT "Bitwise mask set if the central pixel from all images satisfy each condition in zz as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE wisemask_w1 wisemask_w1 tinyint COMMENT "W1 bitmask as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE wisemask_w2 wisemask_w2 tinyint COMMENT "W2 bitmask as cataloged on the DR9 bitmasks page" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfsize_g psfsize_g float COMMENT "Weighted average PSF FWHM in the gg band" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfsize_r psfsize_r float COMMENT "Weighted average PSF FWHM in the rr band" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfsize_z psfsize_z float COMMENT "Weighted average PSF FWHM in the zz band" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfdepth_g psfdepth_g float COMMENT "For a 5σ5σ point source detection limit in gg, 5/(√psfdepth_g)5/(psfdepth_g) gives flux in nanomaggies and −2.5[log10(5/(√psfdepth_g))−9]−2.5[log10⁡(5/(psfdepth_g))−9] gives corresponding AB magnitude" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfdepth_r psfdepth_r float COMMENT "For a 5σ5σ point source detection limit in gg, 5/(√psfdepth_r)5/(psfdepth_r) gives flux in nanomaggies and −2.5[log10(5/(√psfdepth_r))−9]−2.5[log10⁡(5/(psfdepth_r))−9] gives corresponding AB magnitude" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfdepth_z psfdepth_z float COMMENT "For a 5σ5σ point source detection limit in gg, 5/(√psfdepth_z)5/(psfdepth_z) gives flux in nanomaggies and −2.5[log10(5/(√psfdepth_z))−9]−2.5[log10⁡(5/(psfdepth_z))−9] gives corresponding AB magnitude" ;
ALTER TABLE legacy_survey_dr9 CHANGE galdepth_g galdepth_g float COMMENT "As for psfdepth_g but for a galaxy (0.45\u0022 exp, round) detection sensitivity" ;
ALTER TABLE legacy_survey_dr9 CHANGE galdepth_r galdepth_r float COMMENT "As for psfdepth_r but for a galaxy (0.45\u0022 exp, round) detection sensitivity" ;
ALTER TABLE legacy_survey_dr9 CHANGE galdepth_z galdepth_z float COMMENT "As for psfdepth_z but for a galaxy (0.45\u0022 exp, round) detection sensitivity" ;
ALTER TABLE legacy_survey_dr9 CHANGE nea_g nea_g float COMMENT "Noise equivalent area in gg." ;
ALTER TABLE legacy_survey_dr9 CHANGE nea_r nea_r float COMMENT "Noise equivalent area in rr." ;
ALTER TABLE legacy_survey_dr9 CHANGE nea_z nea_z float COMMENT "Noise equivalent area in zz." ;
ALTER TABLE legacy_survey_dr9 CHANGE blob_nea_g blob_nea_g float COMMENT "Blob-masked noise equivalent area in gg." ;
ALTER TABLE legacy_survey_dr9 CHANGE blob_nea_r blob_nea_r float COMMENT "Blob-masked noise equivalent area in rr." ;
ALTER TABLE legacy_survey_dr9 CHANGE blob_nea_z blob_nea_z float COMMENT "Blob-masked noise equivalent area in zz." ;
ALTER TABLE legacy_survey_dr9 CHANGE psfdepth_w1 psfdepth_w1 float COMMENT "As for psfdepth_g (and also on the AB system) but for WISE W1" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfdepth_w2 psfdepth_w2 float COMMENT "As for psfdepth_g (and also on the AB system) but for WISE W2" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfdepth_w3 psfdepth_w3 float COMMENT "As for psfdepth_g (and also on the AB system) but for WISE W3" ;
ALTER TABLE legacy_survey_dr9 CHANGE psfdepth_w4 psfdepth_w4 float COMMENT "As for psfdepth_g (and also on the AB system) but for WISE W4" ;
ALTER TABLE legacy_survey_dr9 CHANGE wise_coadd_id wise_coadd_id string COMMENT "unWISE coadd brick name (corresponding to the, e.g., legacysurvey-<brick>-image-W1.fits.fz coadd file) for the center of each object" ;
ALTER TABLE legacy_survey_dr9 CHANGE wise_x wise_x float COMMENT "X position of coordinates in the brick image stack that corresponds to wise_coadd_id (see the DR9 updates page for transformations between wise_x and bx)" ;
ALTER TABLE legacy_survey_dr9 CHANGE wise_y wise_y float COMMENT "Y position of coordinates in the brick image stack that corresponds to wise_coadd_id (see the DR9 updates page for transformations between wise_y and by)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_flux_w1 lc_flux_w1 array<float> COMMENT "flux_w1 in each of up to fifteen unWISE coadd epochs (AB system\u003b defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_flux_w2 lc_flux_w2 array<float> COMMENT "flux_w2 in each of up to fifteen unWISE coadd epochs (AB\u003b defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_flux_ivar_w1 lc_flux_ivar_w1 array<float> COMMENT "Inverse variance of lc_flux_w1 (AB system\u003b defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_flux_ivar_w2 lc_flux_ivar_w2 array<float> COMMENT "Inverse variance of lc_flux_w2 (AB\u003b defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_nobs_w1 lc_nobs_w1 array<smallint> COMMENT "nobs_w1 in each of up to fifteen unWISE coadd epochs" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_nobs_w2 lc_nobs_w2 array<smallint> COMMENT "nobs_w2 in each of up to fifteen unWISE coadd epochs" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_fracflux_w1 lc_fracflux_w1 array<float> COMMENT "fracflux_w1 in each of up to fifteen unWISE coadd epochs (defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_fracflux_w2 lc_fracflux_w2 array<float> COMMENT "fracflux_w2 in each of up to fifteen unWISE coadd epochs (defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_rchisq_w1 lc_rchisq_w1 array<float> COMMENT "rchisq_w1 in each of up to fifteen unWISE coadd epochs (defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_rchisq_w2 lc_rchisq_w2 array<float> COMMENT "rchisq_w2 in each of up to fifteen unWISE coadd epochs (defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_mjd_w1 lc_mjd_w1 array<double> COMMENT "mjd_w1 in each of up to fifteen unWISE coadd epochs (defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_mjd_w2 lc_mjd_w2 array<double> COMMENT "mjd_w2 in each of up to fifteen unWISE coadd epochs (defaults to zero for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_epoch_index_w1 lc_epoch_index_w1 array<smallint> COMMENT "Index number of unWISE epoch for W1 (defaults to -1 for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE lc_epoch_index_w2 lc_epoch_index_w2 array<smallint> COMMENT "Index number of unWISE epoch for W2 (defaults to -1 for unused entries)" ;
ALTER TABLE legacy_survey_dr9 CHANGE sersic sersic float COMMENT "Power-law index for the Sersic profile model (type=\u0022SER\u0022)" ;
ALTER TABLE legacy_survey_dr9 CHANGE sersic_ivar sersic_ivar float COMMENT "Inverse variance of sersic" ;
ALTER TABLE legacy_survey_dr9 CHANGE shape_r shape_r float COMMENT "Half-light radius of galaxy model for galaxy type type (>0)" ;
ALTER TABLE legacy_survey_dr9 CHANGE shape_r_ivar shape_r_ivar float COMMENT "Inverse variance of shape_r" ;
ALTER TABLE legacy_survey_dr9 CHANGE shape_e1 shape_e1 float COMMENT "Ellipticity component 1 of galaxy model for galaxy type type" ;
ALTER TABLE legacy_survey_dr9 CHANGE shape_e1_ivar shape_e1_ivar float COMMENT "Inverse variance of shape_e1" ;
ALTER TABLE legacy_survey_dr9 CHANGE shape_e2 shape_e2 float COMMENT "Ellipticity component 2 of galaxy model for galaxy type type" ;
ALTER TABLE legacy_survey_dr9 CHANGE shape_e2_ivar shape_e2_ivar float COMMENT "Inverse variance of shape_e2" ;

Sweep photo-z

Raw data

wget -c --no-clobber --no-parent -r https://portal.nersc.gov/project/cosmo/data/legacysurvey/dr8/north/sweep/
wget -c --no-clobber --no-parent -r https://portal.nersc.gov/project/cosmo/data/legacysurvey/dr8/south/sweep/

Checksums checked:

$ cd north/sweep/8.0
$ ls | wc -l
287
$ wc -l legacysurvey_dr8_north_sweep_8.0.sha256sum
286 (+ checksum file)
$ sha256sum -c legacysurvey_dr8_north_sweep_8.0.sha256sum
OK

$ cd north/sweep/8.0-photo-z
$ ls | wc -l
287
$ wc -l legacysurvey_dr8_north_sweep_8.0-photo-z.sha256sum
286 (+ checksum file)
$ sha256sum -w --quiet --strict -c legacysurvey_dr8_north_sweep_8.0-photo-z.sha256sum
OK

$ cd south/sweep/8.0
$ ls | wc -l
438
$ wc -l legacysurvey_dr8_south_sweep_8.0.sha256sum
437 (+ checksum file)
$ sha256sum -w --quiet --strict -c legacysurvey_dr8_south_sweep_8.0.sha256sum
OK

$ cd south/sweep/8.0-photo-z
$ ls | wc -l
438
$ wc -l legacysurvey_dr8_south_sweep_8.0-photo-z.sha256sum
437 (+ checksum file)
$ sha256sum -w --quiet --strict -c legacysurvey_dr8_south_sweep_8.0-photo-z.sha256sum
OK

Parquet

The following code snippet was used in spark to merge fits files and convert to parquet:

path_base = '/cephfs/pic.es/astro/scratch/tallada/dr8-phz/'
hdfs_base = '/user/tallada/data/legacysurvey-sweep'

hs = ['south/sweep'] #, 'north/sweep']
dir_src = '8.0'
dir_phz = '8.0-photo-z'
dir_dst = '8.0-merged'

my_files = sorted(os.listdir(os.path.join(path_base, hs[0], dir_src)))

def merge_phz(file_name):
    file_src = os.path.join(path_base, hs[0], dir_src, file_name)
    file_phz = os.path.join(path_base, hs[0], dir_phz, file_name[:-5]+'-pz.fits')
    file_dst = os.path.join(path_base, hs[0], dir_dst, file_name[:-5]+'-merged.fits')

    t_src = table.Table.read(file_src)
    t_phz = table.Table.read(file_phz)
    t_dst = table.hstack([t_src, t_phz], join_type='exact')

    os.unlink(file_dst)
    t_dst.write(file_dst)
    
    return file_name

df = sc.parallelize(my_files, len(my_files))

df.map(merge_phz).count()

df = spark.read.format("fits").option("hdu", 1).load(
    "file://" + os.path.join(path_base, hs[0], dir_dst)
) 

df.write.parquet(os.path.join(hdfs_base, hs[0]))

External table

CREATE EXTERNAL TABLE tallada.dr8_sweep (
   `RELEASE` SMALLINT,
   `BRICKID` INT,
   `BRICKNAME` STRING,
   `OBJID` INT,
   `TYPE` STRING,
   `RA` DOUBLE,
   `DEC` DOUBLE,
   `RA_IVAR` FLOAT,
   `DEC_IVAR` FLOAT,
   `DCHISQ` ARRAY<FLOAT>,
   `EBV` FLOAT,
   `FLUX_G` FLOAT,
   `FLUX_R` FLOAT,
   `FLUX_Z` FLOAT,
   `FLUX_W1` FLOAT,
   `FLUX_W2` FLOAT,
   `FLUX_W3` FLOAT,
   `FLUX_W4` FLOAT,
   `FLUX_IVAR_G` FLOAT,
   `FLUX_IVAR_R` FLOAT,
   `FLUX_IVAR_Z` FLOAT,
   `FLUX_IVAR_W1` FLOAT,
   `FLUX_IVAR_W2` FLOAT,
   `FLUX_IVAR_W3` FLOAT,
   `FLUX_IVAR_W4` FLOAT,
   `MW_TRANSMISSION_G` FLOAT,
   `MW_TRANSMISSION_R` FLOAT,
   `MW_TRANSMISSION_Z` FLOAT,
   `MW_TRANSMISSION_W1` FLOAT,
   `MW_TRANSMISSION_W2` FLOAT,
   `MW_TRANSMISSION_W3` FLOAT,
   `MW_TRANSMISSION_W4` FLOAT,
   `NOBS_G` INT,
   `NOBS_R` INT,
   `NOBS_Z` INT,
   `NOBS_W1` INT,
   `NOBS_W2` INT,
   `NOBS_W3` INT,
   `NOBS_W4` INT,
   `RCHISQ_G` FLOAT,
   `RCHISQ_R` FLOAT,
   `RCHISQ_Z` FLOAT,
   `RCHISQ_W1` FLOAT,
   `RCHISQ_W2` FLOAT,
   `RCHISQ_W3` FLOAT,
   `RCHISQ_W4` FLOAT,
   `FRACFLUX_G` FLOAT,
   `FRACFLUX_R` FLOAT,
   `FRACFLUX_Z` FLOAT,
   `FRACFLUX_W1` FLOAT,
   `FRACFLUX_W2` FLOAT,
   `FRACFLUX_W3` FLOAT,
   `FRACFLUX_W4` FLOAT,
   `FRACMASKED_G` FLOAT,
   `FRACMASKED_R` FLOAT,
   `FRACMASKED_Z` FLOAT,
   `FRACIN_G` FLOAT,
   `FRACIN_R` FLOAT,
   `FRACIN_Z` FLOAT,
   `ANYMASK_G` SMALLINT,
   `ANYMASK_R` SMALLINT,
   `ANYMASK_Z` SMALLINT,
   `ALLMASK_G` SMALLINT,
   `ALLMASK_R` SMALLINT,
   `ALLMASK_Z` SMALLINT,
   `WISEMASK_W1` TINYINT,
   `WISEMASK_W2` TINYINT,
   `PSFSIZE_G` FLOAT,
   `PSFSIZE_R` FLOAT,
   `PSFSIZE_Z` FLOAT,
   `PSFDEPTH_G` FLOAT,
   `PSFDEPTH_R` FLOAT,
   `PSFDEPTH_Z` FLOAT,
   `GALDEPTH_G` FLOAT,
   `GALDEPTH_R` FLOAT,
   `GALDEPTH_Z` FLOAT,
   `PSFDEPTH_W1` FLOAT,
   `PSFDEPTH_W2` FLOAT,
   `WISE_COADD_ID` STRING,
   `FRACDEV` FLOAT,
   `FRACDEV_IVAR` FLOAT,
   `SHAPEDEV_R` FLOAT,
   `SHAPEDEV_R_IVAR` FLOAT,
   `SHAPEDEV_E1` FLOAT,
   `SHAPEDEV_E1_IVAR` FLOAT,
   `SHAPEDEV_E2` FLOAT,
   `SHAPEDEV_E2_IVAR` FLOAT,
   `SHAPEEXP_R` FLOAT,
   `SHAPEEXP_R_IVAR` FLOAT,
   `SHAPEEXP_E1` FLOAT,
   `SHAPEEXP_E1_IVAR` FLOAT,
   `SHAPEEXP_E2` FLOAT,
   `SHAPEEXP_E2_IVAR` FLOAT,
   `FIBERFLUX_G` FLOAT,
   `FIBERFLUX_R` FLOAT,
   `FIBERFLUX_Z` FLOAT,
   `FIBERTOTFLUX_G` FLOAT,
   `FIBERTOTFLUX_R` FLOAT,
   `FIBERTOTFLUX_Z` FLOAT,
   `REF_CAT` STRING,
   `REF_ID` BIGINT,
   `REF_EPOCH` FLOAT,
   `GAIA_PHOT_G_MEAN_MAG` FLOAT,
   `GAIA_PHOT_G_MEAN_FLUX_OVER_ERROR` FLOAT,
   `GAIA_PHOT_BP_MEAN_MAG` FLOAT,
   `GAIA_PHOT_BP_MEAN_FLUX_OVER_ERROR` FLOAT,
   `GAIA_PHOT_RP_MEAN_MAG` FLOAT,
   `GAIA_PHOT_RP_MEAN_FLUX_OVER_ERROR` FLOAT,
   `GAIA_ASTROMETRIC_EXCESS_NOISE` FLOAT,
   `GAIA_DUPLICATED_SOURCE` BOOLEAN,
   `GAIA_PHOT_BP_RP_EXCESS_FACTOR` FLOAT,
   `GAIA_ASTROMETRIC_SIGMA5D_MAX` FLOAT,
   `GAIA_ASTROMETRIC_PARAMS_SOLVED` TINYINT,
   `PARALLAX` FLOAT,
   `PARALLAX_IVAR` FLOAT,
   `PMRA` FLOAT,
   `PMRA_IVAR` FLOAT,
   `PMDEC` FLOAT,
   `PMDEC_IVAR` FLOAT,
   `MASKBITS` SMALLINT,
   `z_phot_mean` FLOAT,
   `z_phot_median` FLOAT,
   `z_phot_std` FLOAT,
   `z_phot_l68` FLOAT,
   `z_phot_u68` FLOAT,
   `z_phot_l95` FLOAT,
   `z_phot_u95` FLOAT,
   `z_spec` FLOAT,
   `survey` STRING,
   `training` BOOLEAN
)
STORED AS PARQUET
LOCATION '/user/tallada/data/legacysurvey-sweep'
;

Combined table

CREATE TABLE cosmohub.legacysurvey_dr8_phz (
   release smallint COMMENT 'Unique integer denoting the camera and filter set used',
   brickid int COMMENT 'Brick ID [1,662174]',
   brickname string COMMENT 'Name of brick, encoding the brick sky position, eg "1126p222" near RA=112.6, Dec=+22.2',
   objid int COMMENT 'Catalog object number within this brick\u003b a unique identifier hash is release,brickid,objid\u003b objid spans [0,N-1] and is contiguously enumerated within each brick',
   brick_primary boolean COMMENT 'True if the object is within the brick boundary',
   brightblob smallint COMMENT 'bitwise mask indicating that an object is near a bright foreground source, as cataloged on the DR8 bitmasks page',
   maskbits smallint COMMENT 'bitwise mask indicating that an object touches a pixel in the coadd/*/*/*maskbits* maps, as cataloged on the DR8 bitmasks page',
   type string COMMENT 'Morphological model: "PSF"=stellar, "REX"="round exponential galaxy", "DEV"=deVauc, "EXP"=exponential, "COMP"=composite, "DUP"=Gaia source fit by different model. Note that in some FITS readers, a trailing space may be appended for "PSF ", "DEV " and "EXP " since the column data type is a 4-character string',
   ra double COMMENT 'Right ascension at equinox J2000',
   dec double COMMENT 'Declination at equinox J2000',
   ra_ivar float COMMENT 'Inverse variance of RA (no cosine term!), excluding astrometric calibration errors',
   dec_ivar float COMMENT 'Inverse variance of DEC, excluding astrometric calibration errors',
   bx float COMMENT 'X position (0-indexed) of coordinates in brick image stack',
   `by` float COMMENT 'Y position (0-indexed) of coordinates in brick image stack',
   dchisq array<float> COMMENT 'Difference in χ² between successively more-complex model fits: PSF, REX, DEV, EXP, COMP. The difference is versus no source.',
   ebv float COMMENT 'Galactic extinction E(B-V) reddening from SFD98, used to compute the mw_transmission_ columns',
   mjd_min double COMMENT 'Minimum Modified Julian Date of observations used to construct the model of this object',
   mjd_max double COMMENT 'Maximum Modified Julian Date of observations used to construct the model of this object',
   ref_cat string COMMENT 'Reference catalog source for this star: "T2" for Tycho-2, "G2" for Gaia DR2, "L2" for the LSLGA, empty otherwise',
   ref_id bigint COMMENT 'Reference catalog identifier for this star\u003b Tyc1*1,000,000+Tyc2*10+Tyc3 for Tycho2\u003b "sourceid" for Gaia-DR2 and LSLGA',
   pmra float COMMENT 'Reference catalog proper motion in the RA direction',
   pmdec float COMMENT 'Reference catalog proper motion in the Dec direction',
   parallax float COMMENT 'Reference catalog parallax',
   pmra_ivar float COMMENT 'Reference catalog inverse-variance on pmra',
   pmdec_ivar float COMMENT 'Reference catalog inverse-variance on pmdec',
   parallax_ivar float COMMENT 'Reference catalog inverse-variance on parallax',
   ref_epoch float COMMENT 'Reference catalog reference epoch (eg, 2015.5 for Gaia DR2)',
   gaia_pointsource boolean COMMENT 'This Gaia DR2 source is believed to be a star, not a galaxy',
   gaia_phot_g_mean_mag float COMMENT 'Gaia G band mag',
   gaia_phot_g_mean_flux_over_error float COMMENT 'Gaia G band signal-to-noise',
   gaia_phot_g_n_obs smallint COMMENT 'Gaia G band number of observations',
   gaia_phot_bp_mean_mag float COMMENT 'Gaia BP mag',
   gaia_phot_bp_mean_flux_over_error float COMMENT 'Gaia BP signal-to-noise',
   gaia_phot_bp_n_obs smallint COMMENT 'Gaia BP number of observations',
   gaia_phot_rp_mean_mag float COMMENT 'Gaia RP mag',
   gaia_phot_rp_mean_flux_over_error float COMMENT 'Gaia RP signal-to-noise',
   gaia_phot_rp_n_obs smallint COMMENT 'Gaia RP number of observations',
   gaia_phot_variable_flag boolean COMMENT 'Gaia photometric variable flag',
   gaia_astrometric_excess_noise float COMMENT 'Gaia astrometric excess noise',
   gaia_astrometric_excess_noise_sig float COMMENT 'Gaia astrometric excess noise uncertainty',
   gaia_astrometric_n_obs_al smallint COMMENT 'Gaia number of astrometric observations along scan direction',
   gaia_astrometric_n_good_obs_al smallint COMMENT 'Gaia number of good astrometric observations along scan direction',
   gaia_astrometric_weight_al float COMMENT 'Gaia astrometric weight along scan direction',
   gaia_duplicated_source boolean COMMENT 'Gaia duplicated source flag',
   gaia_a_g_val float COMMENT 'Gaia line-of-sight extinction in the G band',
   gaia_e_bp_min_rp_val float COMMENT 'Gaia line-of-sight reddening E(BP-RP)',
   gaia_phot_bp_rp_excess_factor float COMMENT 'Gaia BP/RP excess factor',
   gaia_astrometric_sigma5d_max float COMMENT 'Gaia longest semi-major axis of the 5-d error ellipsoid',
   gaia_astrometric_params_solved tinyint COMMENT 'which astrometric parameters were estimated for a Gaia source',
   flux_g float COMMENT 'model flux in gg',
   flux_r float COMMENT 'model flux in rr',
   flux_z float COMMENT 'model flux in zz',
   flux_w1 float COMMENT 'WISE model flux in W1W1 (AB system)',
   flux_w2 float COMMENT 'WISE model flux in W2W2 (AB)',
   flux_w3 float COMMENT 'WISE model flux in W3W3 (AB)',
   flux_w4 float COMMENT 'WISE model flux in W4W4 (AB)',
   flux_ivar_g float COMMENT 'Inverse variance of flux_g',
   flux_ivar_r float COMMENT 'Inverse variance of flux_r',
   flux_ivar_z float COMMENT 'Inverse variance of flux_z',
   flux_ivar_w1 float COMMENT 'Inverse variance of flux_w1 (AB system)',
   flux_ivar_w2 float COMMENT 'Inverse variance of flux_w2 (AB)',
   flux_ivar_w3 float COMMENT 'Inverse variance of flux_w3 (AB)',
   flux_ivar_w4 float COMMENT 'Inverse variance of flux_w4 (AB)',
   fiberflux_g float COMMENT 'Predicted gg-band flux within a fiber from this object in 1 arcsec Gaussian seeing',
   fiberflux_r float COMMENT 'Predicted rr-band flux within a fiber from this object in 1 arcsec Gaussian seeing',
   fiberflux_z float COMMENT 'Predicted zz-band flux within a fiber from this object in 1 arcsec Gaussian seeing',
   fibertotflux_g float COMMENT 'Predicted gg-band flux within a fiber from all sources at this location in 1 arcsec Gaussian seeing',
   fibertotflux_r float COMMENT 'Predicted rr-band flux within a fiber from all sources at this location in 1 arcsec Gaussian seeing',
   fibertotflux_z float COMMENT 'Predicted zz-band flux within a fiber from all sources at this location in 1 arcsec Gaussian seeing',
   apflux_g array<float> COMMENT 'aperture fluxes on the co-added images in apertures of radius [0.5, 0.75, 1.0, 1.5, 2.0, 3.5, 5.0, 7.0] arcsec in gg',
   apflux_r array<float> COMMENT 'aperture fluxes on the co-added images in apertures of radius [0.5, 0.75, 1.0, 1.5, 2.0, 3.5, 5.0, 7.0] arcsec in rr',
   apflux_z array<float> COMMENT 'aperture fluxes on the co-added images in apertures of radius [0.5, 0.75, 1.0, 1.5, 2.0, 3.5, 5.0, 7.0] arcsec in zz',
   apflux_resid_g array<float> COMMENT 'aperture fluxes on the co-added residual images in gg',
   apflux_resid_r array<float> COMMENT 'aperture fluxes on the co-added residual images in rr',
   apflux_resid_z array<float> COMMENT 'aperture fluxes on the co-added residual images in zz',
   apflux_ivar_g array<float> COMMENT 'Inverse variance of apflux_resid_g',
   apflux_ivar_r array<float> COMMENT 'Inverse variance of apflux_resid_r',
   apflux_ivar_z array<float> COMMENT 'Inverse variance of apflux_resid_z',
   mw_transmission_g float COMMENT 'Galactic transmission in gg filter in linear units [0, 1]',
   mw_transmission_r float COMMENT 'Galactic transmission in rr filter in linear units [0, 1]',
   mw_transmission_z float COMMENT 'Galactic transmission in zz filter in linear units [0, 1]',
   mw_transmission_w1 float COMMENT 'Galactic transmission in W1W1 filter in linear units [0, 1]',
   mw_transmission_w2 float COMMENT 'Galactic transmission in W2W2 filter in linear units [0, 1]',
   mw_transmission_w3 float COMMENT 'Galactic transmission in W3W3 filter in linear units [0, 1]',
   mw_transmission_w4 float COMMENT 'Galactic transmission in W4W4 filter in linear units [0, 1]',
   nobs_g smallint COMMENT 'Number of images that contribute to the central pixel in gg: filter for this object (not profile-weighted)',
   nobs_r smallint COMMENT 'Number of images that contribute to the central pixel in rr: filter for this object (not profile-weighted)',
   nobs_z smallint COMMENT 'Number of images that contribute to the central pixel in zz: filter for this object (not profile-weighted)',
   nobs_w1 smallint COMMENT 'Number of images that contribute to the central pixel in W1W1: filter for this object (not profile-weighted)',
   nobs_w2 smallint COMMENT 'Number of images that contribute to the central pixel in W2W2: filter for this object (not profile-weighted)',
   nobs_w3 smallint COMMENT 'Number of images that contribute to the central pixel in W3W3: filter for this object (not profile-weighted)',
   nobs_w4 smallint COMMENT 'Number of images that contribute to the central pixel in W4W4: filter for this object (not profile-weighted)',
   rchisq_g float COMMENT 'Profile-weighted χ² of model fit normalized by the number of pixels in gg',
   rchisq_r float COMMENT 'Profile-weighted χ² of model fit normalized by the number of pixels in rr',
   rchisq_z float COMMENT 'Profile-weighted χ² of model fit normalized by the number of pixels in zz',
   rchisq_w1 float COMMENT 'Profile-weighted χ² of model fit normalized by the number of pixels in W1W1',
   rchisq_w2 float COMMENT 'Profile-weighted χ² of model fit normalized by the number of pixels in W2W2',
   rchisq_w3 float COMMENT 'Profile-weighted χ² of model fit normalized by the number of pixels in W3W3',
   rchisq_w4 float COMMENT 'Profile-weighted χ² of model fit normalized by the number of pixels in W4W4',
   fracflux_g float COMMENT 'Profile-weighted fraction of the flux from other sources divided by the total flux in gg (typically [0,1])',
   fracflux_r float COMMENT 'Profile-weighted fraction of the flux from other sources divided by the total flux in rr (typically [0,1])',
   fracflux_z float COMMENT 'Profile-weighted fraction of the flux from other sources divided by the total flux in zz (typically [0,1])',
   fracflux_w1 float COMMENT 'Profile-weighted fraction of the flux from other sources divided by the total flux in W1W1 (typically [0,1])',
   fracflux_w2 float COMMENT 'Profile-weighted fraction of the flux from other sources divided by the total flux in W2W2 (typically [0,1])',
   fracflux_w3 float COMMENT 'Profile-weighted fraction of the flux from other sources divided by the total flux in W3W3 (typically [0,1])',
   fracflux_w4 float COMMENT 'Profile-weighted fraction of the flux from other sources divided by the total flux in W4W4 (typically [0,1])',
   fracmasked_g float COMMENT 'Profile-weighted fraction of pixels masked from all observations of this object in gg, strictly between [0,1]',
   fracmasked_r float COMMENT 'Profile-weighted fraction of pixels masked from all observations of this object in rr, strictly between [0,1]',
   fracmasked_z float COMMENT 'Profile-weighted fraction of pixels masked from all observations of this object in zz, strictly between [0,1]',
   fracin_g float COMMENT 'Fraction of a source\'s flux within the blob in gg, near unity for real sources',
   fracin_r float COMMENT 'Fraction of a source\'s flux within the blob in rr, near unity for real sources',
   fracin_z float COMMENT 'Fraction of a source\'s flux within the blob in zz, near unity for real sources',
   anymask_g smallint COMMENT 'Bitwise mask set if the central pixel from any image satisfies each condition in gg as cataloged on the DR8 bitmasks page',
   anymask_r smallint COMMENT 'Bitwise mask set if the central pixel from any image satisfies each condition in rr as cataloged on the DR8 bitmasks page',
   anymask_z smallint COMMENT 'Bitwise mask set if the central pixel from any image satisfies each condition in zz as cataloged on the DR8 bitmasks page',
   allmask_g smallint COMMENT 'Bitwise mask set if the central pixel from all images satisfy each condition in gg as cataloged on the DR8 bitmasks page',
   allmask_r smallint COMMENT 'Bitwise mask set if the central pixel from all images satisfy each condition in rr as cataloged on the DR8 bitmasks page',
   allmask_z smallint COMMENT 'Bitwise mask set if the central pixel from all images satisfy each condition in zz as cataloged on the DR8 bitmasks page',
   wisemask_w1 tinyint COMMENT 'W1 bitmask as cataloged on the DR8 bitmasks page',
   wisemask_w2 tinyint COMMENT 'W2 bitmask as cataloged on the DR8 bitmasks page',
   psfsize_g float COMMENT 'Weighted average PSF FWHM in the gg band',
   psfsize_r float COMMENT 'Weighted average PSF FWHM in the rr band',
   psfsize_z float COMMENT 'Weighted average PSF FWHM in the zz band',
   psfdepth_g float COMMENT 'For a 5σ5σ point source detection limit in gg, 5/(√psfdepth_g)5/(psfdepth_g) gives flux in nanomaggies and −2.5[log10(5/(√psfdepth_g))−9]−2.5[log10⁡(5/(psfdepth_g))−9] gives corresponding AB magnitude',
   psfdepth_r float COMMENT 'For a 5σ5σ point source detection limit in gg, 5/(√psfdepth_r)5/(psfdepth_r) gives flux in nanomaggies and −2.5[log10(5/(√psfdepth_r))−9]−2.5[log10⁡(5/(psfdepth_r))−9] gives corresponding AB magnitude',
   psfdepth_z float COMMENT 'For a 5σ5σ point source detection limit in gg, 5/(√psfdepth_z)5/(psfdepth_z) gives flux in nanomaggies and −2.5[log10(5/(√psfdepth_z))−9]−2.5[log10⁡(5/(psfdepth_z))−9] gives corresponding AB magnitude',
   galdepth_g float COMMENT 'As for psfdepth_g but for a galaxy (0.45" exp, round) detection sensitivity',
   galdepth_r float COMMENT 'As for psfdepth_r but for a galaxy (0.45" exp, round) detection sensitivity',
   galdepth_z float COMMENT 'As for psfdepth_z but for a galaxy (0.45" exp, round) detection sensitivity',
   psfdepth_w1 float COMMENT 'As for psfdepth_g (and also on the AB system) but for WISE W1',
   psfdepth_w2 float COMMENT 'As for psfdepth_g (and also on the AB system) but for WISE W2',
   psfdepth_w3 float COMMENT 'As for psfdepth_g (and also on the AB system) but for WISE W3',
   psfdepth_w4 float COMMENT 'As for psfdepth_g (and also on the AB system) but for WISE W4',
   wise_coadd_id string COMMENT 'unWISE coadd file name for the center of each object',
   lc_flux_w1 array<float> COMMENT 'flux_w1 in each of up to eleven unWISE coadd epochs (AB system)',
   lc_flux_w2 array<float> COMMENT 'flux_w2 in each of up to eleven unWISE coadd epochs (AB)',
   lc_flux_ivar_w1 array<float> COMMENT 'Inverse variance of lc_flux_w1 (AB system)',
   lc_flux_ivar_w2 array<float> COMMENT 'Inverse variance of lc_flux_w2 (AB)',
   lc_nobs_w1 array<smallint> COMMENT 'nobs_w1 in each of up to eleven unWISE coadd epochs',
   lc_nobs_w2 array<smallint> COMMENT 'nobs_w2 in each of up to eleven unWISE coadd epochs',
   lc_fracflux_w1 array<float> COMMENT 'fracflux_w1 in each of up to eleven unWISE coadd epochs',
   lc_fracflux_w2 array<float> COMMENT 'fracflux_w2 in each of up to eleven unWISE coadd epochs',
   lc_rchisq_w1 array<float> COMMENT 'rchisq_w1 in each of up to eleven unWISE coadd epochs',
   lc_rchisq_w2 array<float> COMMENT 'rchisq_w2 in each of up to eleven unWISE coadd epochs',
   lc_mjd_w1 array<double> COMMENT 'mjd_w1 in each of up to eleven unWISE coadd epochs',
   lc_mjd_w2 array<double> COMMENT 'mjd_w2 in each of up to eleven unWISE coadd epochs',
   fracdev float COMMENT 'Fraction of model in deVauc [0,1]',
   fracdev_ivar float COMMENT 'Inverse variance of fracdev',
   shapeexp_r float COMMENT 'Half-light radius of exponential model (>0)',
   shapeexp_r_ivar float COMMENT 'Inverse variance of shapeexp_r',
   shapeexp_e1 float COMMENT 'Ellipticity component 1',
   shapeexp_e1_ivar float COMMENT 'Inverse variance of shapeexp_e1',
   shapeexp_e2 float COMMENT 'Ellipticity component 2',
   shapeexp_e2_ivar float COMMENT 'Inverse variance of shapeexp_e2',
   shapedev_r float COMMENT 'Half-light radius of deVaucouleurs model (>0)',
   shapedev_r_ivar float COMMENT 'Inverse variance of shapedev_r',
   shapedev_e1 float COMMENT 'Ellipticity component 1',
   shapedev_e1_ivar float COMMENT 'Inverse variance of shapedev_e1',
   shapedev_e2 float COMMENT 'Ellipticity component 2',
   shapedev_e2_ivar float COMMENT 'Inverse variance of shapedev_e2',
   z_phot_mean FLOAT COMMENT 'photo-z derived from the mean of the photo-z PDF ',
   z_phot_median FLOAT COMMENT 'photo-z derived from the median of the photo-z PDF',
   z_phot_std FLOAT COMMENT 'standard deviation of the photo-z\'s derived from the photo-z PDF',
   z_phot_l68 FLOAT COMMENT 'lower bound of the 68% confidence region, derived from the photo-z PDF',
   z_phot_u68 FLOAT COMMENT 'upper bound of the 68% confidence region, derived from the photo-z PDF',
   z_phot_l95 FLOAT COMMENT 'lower bound of the 95% confidence region, derived from the photo-z PDF',
   z_phot_u95 FLOAT COMMENT 'upper bound of the 68% confidence region, derived from the photo-z PDF',
   z_spec FLOAT COMMENT 'spectroscopic redshift, if available',
   survey STRING COMMENT 'source of the spectroscopic redshift',
   training BOOLEAN COMMENT 'whether or not the spectroscopic redshift is used in photometric redshift training'
)
CLUSTERED BY (
   release,
   brickid,
   objid)
SORTED BY (
   release ASC,
   brickid ASC,
   objid ASC)
INTO 4096 BUCKETS
STORED AS ORC
;

Join insert

INSERT OVERWRITE TABLE cosmohub.legacy_survey_dr8_phz
SELECT
  dr8.*,
  phz.z_phot_mean,
  phz.z_phot_median,
  phz.z_phot_std,
  phz.z_phot_l68,
  phz.z_phot_u68,
  phz.z_phot_l95,
  phz.z_phot_u95,
  phz.z_spec,
  phz.survey,
  phz.training
FROM cosmohub.legacy_survey_dr8 AS dr8
LEFT JOIN tallada.dr8_sweep AS phz
  ON dr8.release = phz.release
    AND dr8.brickid = phz.brickid
    AND dr8.objid = phz.objid
;

Queries for checking

SELECT COUNT(c), SUM(c) 
FROM (
  SELECT COUNT(release) AS c
  FROM cosmohub.legacy_survey_dr8
  GROUP BY release, brickid, objid
) AS t

SELECT COUNT(c), SUM(c) 
FROM (
  SELECT COUNT(brickid) AS c
  FROM cosmohub.legacy_survey_dr8
  GROUP BY brickid, objid
) AS t

SELECT COUNT(c), SUM(c) 
FROM (
  SELECT COUNT(release) AS c
  FROM tallada.dr8_sweep
  GROUP BY release, brickid, objid
) AS t

SELECT COUNT(c), SUM(c) 
FROM (
  SELECT COUNT(brickid) AS c
  FROM tallada.dr8_sweep
  GROUP BY brickid, objid
) AS t

SELECT release, brickid, objid, COUNT(release)
FROM tallada.dr8_sweep
GROUP BY release, brickid, objid
HAVING COUNT(release) > 1

DR8 release brickid objid: DISTINCT(1.694.230.368) TOTAL(1.694.230.368)
DR8 brickid objid:         DISTINCT(1.654.429.115) TOTAL(1.694.230.368)

PHZ release brickid objid: DISTINCT(1.646.664.729) TOTAL(1.646.664.897)
PHZ brickid objid:         DISTINCT(1.608.841.582) TOTAL(1.646.664.897)

DR8: 							 1.694.230.368
PHZ: 							 1.646.664.897
----------------------------------------------------------------------
      			  				   -47.565.471‬