Difference between revisions of "ZEST v1.0"
Jump to navigation
Jump to search
(→SQL) |
(→UPDATE) |
||
| (5 intermediate revisions by 2 users not shown) | |||
| Line 2: | Line 2: | ||
Downloaded on 2019-02-18 from | Downloaded on 2019-02-18 from | ||
https://irsa.ipac.caltech.edu/data/COSMOS/tables/morphology/cosmos_morph_zurich_1.0.tbl | https://irsa.ipac.caltech.edu/data/COSMOS/tables/morphology/cosmos_morph_zurich_1.0.tbl | ||
| + | |||
| + | Converted to CSV using LibreOffice Calc. | ||
== SQL == | == SQL == | ||
| − | + | <pre> | |
| − | + | CREATE EXTERNAL TABLE tallada.zest ( | |
| − | + | `SequentialID` INT COMMENT "Identification number", | |
| − | + | `RA` FLOAT COMMENT "Right ascension (degrees)", | |
| − | + | `DEC` FLOAT COMMENT "Declination (degrees)", | |
| − | + | `CAPAK_ID` FLOAT COMMENT "ID based on ground-based photometric catalog of Capak 2006", | |
| − | + | `CAPAK_RA` FLOAT COMMENT "Closest matched right ascension to ground-based catalog within 0.6\" (degrees)", | |
| − | + | `CAPAK_DEC` FLOAT COMMENT "Closest matched declination to ground-based catalog within 0.6\" (degrees)", | |
| − | + | `ACS_MAG_AUTO` FLOAT COMMENT "Total magnitude of object- used for catalog selection", | |
| − | + | `ACS_MAGERR_AUTO` FLOAT COMMENT "Magnitude error", | |
| − | + | `ACS_X_IMAGE` FLOAT COMMENT "X-pixel position on ACS-tile", | |
| − | + | `ACS_Y_IMAGE` FLOAT COMMENT "Y-pixel position on ACS-tile", | |
| − | + | `ACS_XPEAK_IMAGE` FLOAT COMMENT "X-pixel position of object peak flux on ACS tile", | |
| − | + | `ACS_YPEAK_IMAGE` FLOAT COMMENT "Y-pixel position of object peak flux on ACS tile", | |
| − | + | `ACS_ALPHAPEAK_` FLOAT COMMENT "RA of object peak flux (degrees)", | |
| − | + | `ACS_DELTAPEAK_` FLOAT COMMENT "Dec of object peak flux (degrees)", | |
| − | + | `ACS_A_IMAGE` FLOAT COMMENT "SExtractor semi-major axis", | |
| − | + | `ACS_B_IMAGE` FLOAT COMMENT "SExtractor semi-minor axis", | |
| − | + | `ACS_THETA_IMAGE` FLOAT COMMENT "SExtractor position angle", | |
| − | + | `ACS_ELONGATION` FLOAT COMMENT "SExtractor a/b [semi-major axis / semi minor axis]", | |
| − | + | `ACS_CLASS_STAR` FLOAT COMMENT "SExtractor stellarity parameter", | |
| − | + | `ACS_IDENT` FLOAT COMMENT "Unique number that identifies object", | |
| − | + | `ACS_SE` FLOAT COMMENT "0 = Detection from 'cold' pass of Sextractor, 1 = Detection from 'hot' pass of Sextractor", | |
| − | + | `ACS_MU_CLASS` FLOAT COMMENT "Type of object (1 = galaxy, 2 = star, 3 = spurious)", | |
| − | + | `ACS_OVERLAP` FLOAT COMMENT "Object overlap flag. All objects here have overlap =1 i.e. no overlap.", | |
| − | + | `ACS_NEARSTAR` FLOAT COMMENT "Object in star mask flag. All the objects in this morphological catalog have \"nearstar = 1\" which means they are not in a star mask.", | |
| − | + | `ACS_MASK` FLOAT COMMENT "Object in automatic mask flag: 2 = star used to make automatic mask", | |
| − | + | `ACS_MASKED` FLOAT COMMENT "Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask", | |
| − | + | `ACS_CLEAN` FLOAT COMMENT "Object useable flag: 0 = do not use this object, 1 = use this object", | |
| − | + | `ACS_UNIQUE` FLOAT COMMENT "Object unique flag. All the objects in this morphological catalog have \"unique = 1\" which means they are all \"unique\" in the original ACS catalog.", | |
| − | + | `GG` FLOAT COMMENT "ZEST Gini coefficient", | |
| − | + | `M20` FLOAT COMMENT "ZEST second order moment of the 20% brightest pixels", | |
| − | + | `CC` FLOAT COMMENT "ZEST concentration", | |
| − | + | `AA` FLOAT COMMENT "ZEST asymmetry", | |
| − | + | `R20` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)", | |
| − | + | `R50` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)", | |
| − | + | `R80` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)", | |
| − | + | `RPET` FLOAT COMMENT "Zest semi-major axis length for Petrosian Radius ellipse (pixels)", | |
| − | + | `FLAGRPET` FLOAT COMMENT "Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)", | |
| − | + | `FLUX_GIM2D` FLOAT COMMENT "GIM2D total flux (counts)", | |
| − | + | `LE_FLUX_GIM2D` FLOAT COMMENT "LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)", | |
| − | + | `UE_FLUX_GIM2D` FLOAT COMMENT "UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)", | |
| − | + | `R_GIM2D` FLOAT COMMENT "GIM2D psf-convolved half-light radius of object (arcseconds)", | |
| − | + | `LE_R_GIM2D` FLOAT COMMENT "99% conf. lower error on R_GIM2D", | |
| − | + | `UE_R_GIM2D` FLOAT COMMENT "99% conf. upper error on R_GIM2D", | |
| − | + | `ELL_GIM2D` FLOAT COMMENT "GIM2D ellipticity = 1-b/a of object", | |
| − | + | `LE_ELL_GIM2D` FLOAT COMMENT "99% conf. lower error on ELL_GIM2D", | |
| − | + | `UE_ELL_GIM2D` FLOAT COMMENT "99% conf. upper error on ELL_GIM2D", | |
| − | + | `PA_GIM2D` FLOAT COMMENT "GIM2D position angle of object - cw from +y-axis (degrees)", | |
| − | + | `LE_PA_GIM2D` FLOAT COMMENT "99% conf. lower error on PA_GIM2D", | |
| − | + | `UE_PA_GIM2D` FLOAT COMMENT "99% conf. upper error on PA_GIM2D", | |
| − | + | `DX_GIM2D` FLOAT COMMENT "x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)", | |
| − | + | `LE_DX_GIM2D` FLOAT COMMENT "99% conf. lower error on DX_GIM2D", | |
| − | + | `UE_DX_GIM2D` FLOAT COMMENT "99% conf. upper error on DX_GIM2D", | |
| − | + | `DY_GIM2D` FLOAT COMMENT "y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)", | |
| − | + | `LE_DY_GIM2D` FLOAT COMMENT "99% conf. lower error on DY_GIM2D", | |
| − | + | `UE_DY_GIM2D` FLOAT COMMENT "99% conf. upper error on DY_GIM2D", | |
| − | + | `SERSIC_N_GIM2D` FLOAT COMMENT "GIM2D Sersic index", | |
| − | + | `LE_N_GIM2D` FLOAT COMMENT "99% conf. lower error on SERSIC_N_GIM2D", | |
| − | + | `UE_N_GIM2D` FLOAT COMMENT "99% conf. upper error on SERSIC_N_GIM2D", | |
| − | + | `R_0P5_GIM2D` FLOAT COMMENT "GIM2D half-light radius of object without PSF convolution (arcseconds)", | |
| − | + | `CHI_GIM2D` FLOAT COMMENT "reduced CHI2 of the best GIM2D model fit", | |
| − | + | `ITER_GIM2D` FLOAT COMMENT "no. of iterations required for GIM2D fit to converge", | |
| − | + | `PC_1` FLOAT COMMENT "ZEST first principal component", | |
| − | + | `PC_2` FLOAT COMMENT "ZEST second principal component", | |
| − | + | `PC_3` FLOAT COMMENT "ZEST third principal component", | |
| − | + | `TYPE` FLOAT COMMENT "ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)", | |
| − | + | `BULG` FLOAT COMMENT "ZEST \"Bulgeness\" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification", | |
| − | + | `IRRE` FLOAT COMMENT "ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value", | |
| − | + | `ELLI` FLOAT COMMENT "ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation", | |
| − | + | `STELLARITY` FLOAT COMMENT "Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)", | |
| − | + | `JUNKFLAG` FLOAT COMMENT "0 = good object, 1 = spurious", | |
| − | + | `ACSTile` STRING COMMENT "ACS TILE number from which the object is extracted" | |
| − | + | ) | |
| − | + | ROW FORMAT DELIMITED | |
| − | + | FIELDS TERMINATED BY ',' | |
| − | + | LOCATION '/user/tallada/data/zest' | |
| + | ; | ||
| + | </pre> | ||
| + | |||
| + | <pre> | ||
| + | CREATE TABLE cosmohub.zest_v1_0 ( | ||
| + | `SequentialID` INT COMMENT "Identification number", | ||
| + | `RA` FLOAT COMMENT "Right ascension (degrees)", | ||
| + | `DEC` FLOAT COMMENT "Declination (degrees)", | ||
| + | `CAPAK_ID` FLOAT COMMENT "ID based on ground-based photometric catalog of Capak 2006", | ||
| + | `CAPAK_RA` FLOAT COMMENT "Closest matched right ascension to ground-based catalog within 0.6\" (degrees)", | ||
| + | `CAPAK_DEC` FLOAT COMMENT "Closest matched declination to ground-based catalog within 0.6\" (degrees)", | ||
| + | `ACS_MAG_AUTO` FLOAT COMMENT "Total magnitude of object- used for catalog selection", | ||
| + | `ACS_MAGERR_AUTO` FLOAT COMMENT "Magnitude error", | ||
| + | `ACS_X_IMAGE` FLOAT COMMENT "X-pixel position on ACS-tile", | ||
| + | `ACS_Y_IMAGE` FLOAT COMMENT "Y-pixel position on ACS-tile", | ||
| + | `ACS_XPEAK_IMAGE` FLOAT COMMENT "X-pixel position of object peak flux on ACS tile", | ||
| + | `ACS_YPEAK_IMAGE` FLOAT COMMENT "Y-pixel position of object peak flux on ACS tile", | ||
| + | `ACS_ALPHAPEAK_` FLOAT COMMENT "RA of object peak flux (degrees)", | ||
| + | `ACS_DELTAPEAK_` FLOAT COMMENT "Dec of object peak flux (degrees)", | ||
| + | `ACS_A_IMAGE` FLOAT COMMENT "SExtractor semi-major axis", | ||
| + | `ACS_B_IMAGE` FLOAT COMMENT "SExtractor semi-minor axis", | ||
| + | `ACS_THETA_IMAGE` FLOAT COMMENT "SExtractor position angle", | ||
| + | `ACS_ELONGATION` FLOAT COMMENT "SExtractor a/b [semi-major axis / semi minor axis]", | ||
| + | `ACS_CLASS_STAR` FLOAT COMMENT "SExtractor stellarity parameter", | ||
| + | `ACS_IDENT` FLOAT COMMENT "Unique number that identifies object", | ||
| + | `ACS_SE` FLOAT COMMENT "0 = Detection from 'cold' pass of Sextractor, 1 = Detection from 'hot' pass of Sextractor", | ||
| + | `ACS_MU_CLASS` FLOAT COMMENT "Type of object (1 = galaxy, 2 = star, 3 = spurious)", | ||
| + | `ACS_OVERLAP` FLOAT COMMENT "Object overlap flag. All objects here have overlap =1 i.e. no overlap.", | ||
| + | `ACS_NEARSTAR` FLOAT COMMENT "Object in star mask flag. All the objects in this morphological catalog have \"nearstar = 1\" which means they are not in a star mask.", | ||
| + | `ACS_MASK` FLOAT COMMENT "Object in automatic mask flag: 2 = star used to make automatic mask", | ||
| + | `ACS_MASKED` FLOAT COMMENT "Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask", | ||
| + | `ACS_CLEAN` FLOAT COMMENT "Object useable flag: 0 = do not use this object, 1 = use this object", | ||
| + | `ACS_UNIQUE` FLOAT COMMENT "Object unique flag. All the objects in this morphological catalog have \"unique = 1\" which means they are all \"unique\" in the original ACS catalog.", | ||
| + | `GG` FLOAT COMMENT "ZEST Gini coefficient", | ||
| + | `M20` FLOAT COMMENT "ZEST second order moment of the 20% brightest pixels", | ||
| + | `CC` FLOAT COMMENT "ZEST concentration", | ||
| + | `AA` FLOAT COMMENT "ZEST asymmetry", | ||
| + | `R20` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)", | ||
| + | `R50` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)", | ||
| + | `R80` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)", | ||
| + | `RPET` FLOAT COMMENT "Zest semi-major axis length for Petrosian Radius ellipse (pixels)", | ||
| + | `FLAGRPET` FLOAT COMMENT "Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)", | ||
| + | `FLUX_GIM2D` FLOAT COMMENT "GIM2D total flux (counts)", | ||
| + | `LE_FLUX_GIM2D` FLOAT COMMENT "LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)", | ||
| + | `UE_FLUX_GIM2D` FLOAT COMMENT "UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)", | ||
| + | `R_GIM2D` FLOAT COMMENT "GIM2D psf-convolved half-light radius of object (arcseconds)", | ||
| + | `LE_R_GIM2D` FLOAT COMMENT "99% conf. lower error on R_GIM2D", | ||
| + | `UE_R_GIM2D` FLOAT COMMENT "99% conf. upper error on R_GIM2D", | ||
| + | `ELL_GIM2D` FLOAT COMMENT "GIM2D ellipticity = 1-b/a of object", | ||
| + | `LE_ELL_GIM2D` FLOAT COMMENT "99% conf. lower error on ELL_GIM2D", | ||
| + | `UE_ELL_GIM2D` FLOAT COMMENT "99% conf. upper error on ELL_GIM2D", | ||
| + | `PA_GIM2D` FLOAT COMMENT "GIM2D position angle of object - cw from +y-axis (degrees)", | ||
| + | `LE_PA_GIM2D` FLOAT COMMENT "99% conf. lower error on PA_GIM2D", | ||
| + | `UE_PA_GIM2D` FLOAT COMMENT "99% conf. upper error on PA_GIM2D", | ||
| + | `DX_GIM2D` FLOAT COMMENT "x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)", | ||
| + | `LE_DX_GIM2D` FLOAT COMMENT "99% conf. lower error on DX_GIM2D", | ||
| + | `UE_DX_GIM2D` FLOAT COMMENT "99% conf. upper error on DX_GIM2D", | ||
| + | `DY_GIM2D` FLOAT COMMENT "y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)", | ||
| + | `LE_DY_GIM2D` FLOAT COMMENT "99% conf. lower error on DY_GIM2D", | ||
| + | `UE_DY_GIM2D` FLOAT COMMENT "99% conf. upper error on DY_GIM2D", | ||
| + | `SERSIC_N_GIM2D` FLOAT COMMENT "GIM2D Sersic index", | ||
| + | `LE_N_GIM2D` FLOAT COMMENT "99% conf. lower error on SERSIC_N_GIM2D", | ||
| + | `UE_N_GIM2D` FLOAT COMMENT "99% conf. upper error on SERSIC_N_GIM2D", | ||
| + | `R_0P5_GIM2D` FLOAT COMMENT "GIM2D half-light radius of object without PSF convolution (arcseconds)", | ||
| + | `CHI_GIM2D` FLOAT COMMENT "reduced CHI2 of the best GIM2D model fit", | ||
| + | `ITER_GIM2D` FLOAT COMMENT "no. of iterations required for GIM2D fit to converge", | ||
| + | `PC_1` FLOAT COMMENT "ZEST first principal component", | ||
| + | `PC_2` FLOAT COMMENT "ZEST second principal component", | ||
| + | `PC_3` FLOAT COMMENT "ZEST third principal component", | ||
| + | `TYPE` FLOAT COMMENT "ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)", | ||
| + | `BULG` FLOAT COMMENT "ZEST \"Bulgeness\" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification", | ||
| + | `IRRE` FLOAT COMMENT "ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value", | ||
| + | `ELLI` FLOAT COMMENT "ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation", | ||
| + | `STELLARITY` FLOAT COMMENT "Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)", | ||
| + | `JUNKFLAG` FLOAT COMMENT "0 = good object, 1 = spurious", | ||
| + | `ACSTile` STRING COMMENT "ACS TILE number from which the object is extracted" | ||
| + | ) | ||
| + | CLUSTERED BY (`SequentialID`) | ||
| + | INTO 4 BUCKETS | ||
| + | STORED AS ORC | ||
| + | ; | ||
| + | </pre> | ||
| + | |||
| + | <pre> | ||
| + | INSERT OVERWRITE TABLE cosmohub.zest_v1_0 | ||
| + | SELECT * FROM tallada.zest | ||
| + | ; | ||
| + | </pre> | ||
| + | |||
| + | |||
| + | == UPDATE 1== | ||
| + | |||
| + | I update the catalog to include the cosmos_2015_dr_2_1 index to be able to make joins directly in COSMOHUB. | ||
| + | |||
| + | To do the matching I use a python notebook which can be found here: | ||
| + | |||
| + | > /nfs/pic.es/user/j/jcarrete/python_notebooks/Matching_ZEST_and_COSMOS2015.ipynb | ||
| + | |||
| + | I create a table with the "link" (`sequentialid`, `cosmos2015_id): | ||
| + | |||
| + | <pre> | ||
| + | CREATE EXTERNAL TABLE jcarrete.zest_cosmos2015_spatial_matching_csv ( | ||
| + | sequentialid int, | ||
| + | ra float, | ||
| + | dec float, | ||
| + | cosmos2015_id bigint, | ||
| + | cosmos2015_distance float | ||
| + | ) | ||
| + | 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://namenode01/user/jcarrete/data/zest_cosmos2015_spatial_matching/' | ||
| + | ; | ||
| + | </pre> | ||
| + | |||
| + | This is the ZEST table updated, which include the COSMOS2015 id: | ||
| + | |||
| + | <pre> | ||
| + | CREATE TABLE cosmohub.`zest_v1_0_cosmos2015_join`( | ||
| + | `sequentialid` int COMMENT 'Identification number', | ||
| + | `cosmos2015_id` bigint COMMENT 'id from COSMOS2015 DR2.1 catalog to be able to JOIN both catalogs', | ||
| + | `cosmos2015_distance` float COMMENT 'estimated distance from the nearest galaxy when using sklearn.neighbors.KDTree (ra, dec)', | ||
| + | `ra` float COMMENT 'Right ascension (degrees)', | ||
| + | `dec` float COMMENT 'Declination (degrees)', | ||
| + | `capak_id` float COMMENT 'ID based on ground-based photometric catalog of Capak 2006', | ||
| + | `capak_ra` float COMMENT 'Closest matched right ascension to ground-based catalog within 0.6" (degrees)', | ||
| + | `capak_dec` float COMMENT 'Closest matched declination to ground-based catalog within 0.6" (degrees)', | ||
| + | `acs_mag_auto` float COMMENT 'Total magnitude of object- used for catalog selection', | ||
| + | `acs_magerr_auto` float COMMENT 'Magnitude error', | ||
| + | `acs_x_image` float COMMENT 'X-pixel position on ACS-tile', | ||
| + | `acs_y_image` float COMMENT 'Y-pixel position on ACS-tile', | ||
| + | `acs_xpeak_image` float COMMENT 'X-pixel position of object peak flux on ACS tile', | ||
| + | `acs_ypeak_image` float COMMENT 'Y-pixel position of object peak flux on ACS tile', | ||
| + | `acs_alphapeak_` float COMMENT 'RA of object peak flux (degrees)', | ||
| + | `acs_deltapeak_` float COMMENT 'Dec of object peak flux (degrees)', | ||
| + | `acs_a_image` float COMMENT 'SExtractor semi-major axis', | ||
| + | `acs_b_image` float COMMENT 'SExtractor semi-minor axis', | ||
| + | `acs_theta_image` float COMMENT 'SExtractor position angle', | ||
| + | `acs_elongation` float COMMENT 'SExtractor a/b [semi-major axis / semi minor axis]', | ||
| + | `acs_class_star` float COMMENT 'SExtractor stellarity parameter', | ||
| + | `acs_ident` float COMMENT 'Unique number that identifies object', | ||
| + | `acs_se` float COMMENT '0 = Detection from \'cold\' pass of Sextractor, 1 = Detection from \'hot\' pass of Sextractor', | ||
| + | `acs_mu_class` float COMMENT 'Type of object (1 = galaxy, 2 = star, 3 = spurious)', | ||
| + | `acs_overlap` float COMMENT 'Object overlap flag. All objects here have overlap =1 i.e. no overlap.', | ||
| + | `acs_nearstar` float COMMENT 'Object in star mask flag. All the objects in this morphological catalog have "nearstar = 1" which means they are not in a star mask.', | ||
| + | `acs_mask` float COMMENT 'Object in automatic mask flag: 2 = star used to make automatic mask', | ||
| + | `acs_masked` float COMMENT 'Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask', | ||
| + | `acs_clean` float COMMENT 'Object useable flag: 0 = do not use this object, 1 = use this object', | ||
| + | `acs_unique` float COMMENT 'Object unique flag. All the objects in this morphological catalog have "unique = 1" which means they are all "unique" in the original ACS catalog.', | ||
| + | `gg` float COMMENT 'ZEST Gini coefficient', | ||
| + | `m20` float COMMENT 'ZEST second order moment of the 20% brightest pixels', | ||
| + | `cc` float COMMENT 'ZEST concentration', | ||
| + | `aa` float COMMENT 'ZEST asymmetry', | ||
| + | `r20` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)', | ||
| + | `r50` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)', | ||
| + | `r80` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)', | ||
| + | `rpet` float COMMENT 'Zest semi-major axis length for Petrosian Radius ellipse (pixels)', | ||
| + | `flagrpet` float COMMENT 'Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)', | ||
| + | `flux_gim2d` float COMMENT 'GIM2D total flux (counts)', | ||
| + | `le_flux_gim2d` float COMMENT 'LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', | ||
| + | `ue_flux_gim2d` float COMMENT 'UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', | ||
| + | `r_gim2d` float COMMENT 'GIM2D psf-convolved half-light radius of object (arcseconds)', | ||
| + | `le_r_gim2d` float COMMENT '99% conf. lower error on R_GIM2D', | ||
| + | `ue_r_gim2d` float COMMENT '99% conf. upper error on R_GIM2D', | ||
| + | `ell_gim2d` float COMMENT 'GIM2D ellipticity = 1-b/a of object', | ||
| + | `le_ell_gim2d` float COMMENT '99% conf. lower error on ELL_GIM2D', | ||
| + | `ue_ell_gim2d` float COMMENT '99% conf. upper error on ELL_GIM2D', | ||
| + | `pa_gim2d` float COMMENT 'GIM2D position angle of object - cw from +y-axis (degrees)', | ||
| + | `le_pa_gim2d` float COMMENT '99% conf. lower error on PA_GIM2D', | ||
| + | `ue_pa_gim2d` float COMMENT '99% conf. upper error on PA_GIM2D', | ||
| + | `dx_gim2d` float COMMENT 'x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', | ||
| + | `le_dx_gim2d` float COMMENT '99% conf. lower error on DX_GIM2D', | ||
| + | `ue_dx_gim2d` float COMMENT '99% conf. upper error on DX_GIM2D', | ||
| + | `dy_gim2d` float COMMENT 'y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', | ||
| + | `le_dy_gim2d` float COMMENT '99% conf. lower error on DY_GIM2D', | ||
| + | `ue_dy_gim2d` float COMMENT '99% conf. upper error on DY_GIM2D', | ||
| + | `sersic_n_gim2d` float COMMENT 'GIM2D Sersic index', | ||
| + | `le_n_gim2d` float COMMENT '99% conf. lower error on SERSIC_N_GIM2D', | ||
| + | `ue_n_gim2d` float COMMENT '99% conf. upper error on SERSIC_N_GIM2D', | ||
| + | `r_0p5_gim2d` float COMMENT 'GIM2D half-light radius of object without PSF convolution (arcseconds)', | ||
| + | `chi_gim2d` float COMMENT 'reduced CHI2 of the best GIM2D model fit', | ||
| + | `iter_gim2d` float COMMENT 'no. of iterations required for GIM2D fit to converge', | ||
| + | `pc_1` float COMMENT 'ZEST first principal component', | ||
| + | `pc_2` float COMMENT 'ZEST second principal component', | ||
| + | `pc_3` float COMMENT 'ZEST third principal component', | ||
| + | `type` float COMMENT 'ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)', | ||
| + | `bulg` float COMMENT 'ZEST "Bulgeness" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification', | ||
| + | `irre` float COMMENT 'ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value', | ||
| + | `elli` float COMMENT 'ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation', | ||
| + | `stellarity` float COMMENT 'Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)', | ||
| + | `junkflag` float COMMENT '0 = good object, 1 = spurious', | ||
| + | `acstile` string COMMENT 'ACS TILE number from which the object is extracted') | ||
| + | CLUSTERED BY ( | ||
| + | sequentialid) | ||
| + | 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' | ||
| + | ; | ||
| + | </pre> | ||
| + | |||
| + | I materialize the JOIN: | ||
| + | |||
| + | <pre> | ||
| + | INSERT OVERWRITE TABLE cosmohub.`zest_v1_0_cosmos2015_join` | ||
| + | SELECT zest.`sequentialid`, link.`cosmos2015_id`, link.`cosmos2015_distance`, zest.`ra`, zest.`dec`, zest.`capak_id`, zest.`capak_ra`, zest.`capak_dec`, zest.`acs_mag_auto`, zest.`acs_magerr_auto`, zest.`acs_x_image`, zest.`acs_y_image`, zest.`acs_xpeak_image`, zest.`acs_ypeak_image`, zest.`acs_alphapeak_`, zest.`acs_deltapeak_`, zest.`acs_a_image`, zest.`acs_b_image`, zest.`acs_theta_image`, zest.`acs_elongation`, zest.`acs_class_star`, zest.`acs_ident`, zest.`acs_se`, zest.`acs_mu_class`, zest.`acs_overlap`, zest.`acs_nearstar`, zest.`acs_mask`, zest.`acs_masked`, zest.`acs_clean`, zest.`acs_unique`, zest.`gg`, zest.`m20`, zest.`cc`, zest.`aa`, zest.`r20`, zest.`r50`, zest.`r80`, zest.`rpet`, zest.`flagrpet`, zest.`flux_gim2d`, zest.`le_flux_gim2d`, zest.`ue_flux_gim2d`, zest.`r_gim2d`, zest.`le_r_gim2d`, zest.`ue_r_gim2d`, zest.`ell_gim2d`, zest.`le_ell_gim2d`, zest.`ue_ell_gim2d`, zest.`pa_gim2d`, zest.`le_pa_gim2d`, zest.`ue_pa_gim2d`, zest.`dx_gim2d`, zest.`le_dx_gim2d`, zest.`ue_dx_gim2d`, zest.`dy_gim2d`, zest.`le_dy_gim2d`, zest.`ue_dy_gim2d`, zest.`sersic_n_gim2d`, zest.`le_n_gim2d`, zest.`ue_n_gim2d`, zest.`r_0p5_gim2d`, zest.`chi_gim2d`, zest.`iter_gim2d`, zest.`pc_1`, zest.`pc_2`, zest.`pc_3`, zest.`type`, zest.`bulg`, zest.`irre`, zest.`elli`, zest.`stellarity`, zest.`junkflag`, zest.`acstile` | ||
| + | FROM cosmohub.zest_v1_0 as zest | ||
| + | JOIN jcarrete.zest_cosmos2015_spatial_matching_csv as link | ||
| + | ON zest.`sequentialid` = link.`sequentialid` | ||
| + | ; | ||
| + | </pre> | ||
| + | |||
| + | |||
| + | And here is a test: | ||
| + | <pre> | ||
| + | SELECT zest.sequentialid, zest.cosmos2015_id, cosmos.id, zest.ra, cosmos.alpha_j2000, zest.dec, cosmos.delta_j2000, zest.cosmos2015_distance | ||
| + | FROM cosmohub.`zest_v1_0_cosmos2015_join` as zest | ||
| + | JOIN cosmohub.cosmos_2015_dr_2_1 as cosmos | ||
| + | ON zest.cosmos2015_id = cosmos.id | ||
| + | LIMIT 5; | ||
| + | </pre> | ||
| + | |||
| + | |||
| + | == UPDATE 2== | ||
| + | |||
| + | I take file from Kai. Spatial matching only produces some outliers. | ||
| + | He uses magnitudes at the same time and it works much better. | ||
| + | |||
| + | |||
| + | <pre> | ||
| + | CREATE EXTERNAL TABLE `zest_cosmos2015_spatial_matching_kai_file_csv`( | ||
| + | `id` int COMMENT '', | ||
| + | `sequentialid` int COMMENT '' | ||
| + | ) | ||
| + | ROW FORMAT SERDE | ||
| + | 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' | ||
| + | WITH SERDEPROPERTIES ( | ||
| + | 'escape.delim'='$', | ||
| + | 'field.delim'=',', | ||
| + | 'serialization.format'=',') | ||
| + | STORED AS INPUTFORMAT | ||
| + | 'org.apache.hadoop.mapred.TextInputFormat' | ||
| + | OUTPUTFORMAT | ||
| + | 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' | ||
| + | LOCATION | ||
| + | 'hdfs://namenode01//user/jcarrete/data/zest_cosmos2015_spatial_matching_kai_file/' | ||
| + | ; | ||
| + | </pre> | ||
| − | + | <pre> | |
| − | + | CREATE TABLE `zest_v1_0_cosmos2015_kai_join`( | |
| − | + | `sequentialid` int COMMENT 'Identification number', | |
| − | + | `cosmos2015_id` bigint COMMENT 'id from COSMOS2015 DR2.1 catalog to be able to JOIN both catalogs', | |
| − | + | `ra` float COMMENT 'Right ascension (degrees)', | |
| − | + | `dec` float COMMENT 'Declination (degrees)', | |
| − | + | `capak_id` float COMMENT 'ID based on ground-based photometric catalog of Capak 2006', | |
| − | + | `capak_ra` float COMMENT 'Closest matched right ascension to ground-based catalog within 0.6" (degrees)', | |
| − | + | `capak_dec` float COMMENT 'Closest matched declination to ground-based catalog within 0.6" (degrees)', | |
| − | + | `acs_mag_auto` float COMMENT 'Total magnitude of object- used for catalog selection', | |
| − | + | `acs_magerr_auto` float COMMENT 'Magnitude error', | |
| − | + | `acs_x_image` float COMMENT 'X-pixel position on ACS-tile', | |
| − | + | `acs_y_image` float COMMENT 'Y-pixel position on ACS-tile', | |
| − | + | `acs_xpeak_image` float COMMENT 'X-pixel position of object peak flux on ACS tile', | |
| − | + | `acs_ypeak_image` float COMMENT 'Y-pixel position of object peak flux on ACS tile', | |
| − | + | `acs_alphapeak_` float COMMENT 'RA of object peak flux (degrees)', | |
| − | + | `acs_deltapeak_` float COMMENT 'Dec of object peak flux (degrees)', | |
| − | + | `acs_a_image` float COMMENT 'SExtractor semi-major axis', | |
| − | + | `acs_b_image` float COMMENT 'SExtractor semi-minor axis', | |
| − | + | `acs_theta_image` float COMMENT 'SExtractor position angle', | |
| − | + | `acs_elongation` float COMMENT 'SExtractor a/b [semi-major axis / semi minor axis]', | |
| − | + | `acs_class_star` float COMMENT 'SExtractor stellarity parameter', | |
| − | + | `acs_ident` float COMMENT 'Unique number that identifies object', | |
| − | + | `acs_se` float COMMENT '0 = Detection from \'cold\' pass of Sextractor, 1 = Detection from \'hot\' pass of Sextractor', | |
| − | + | `acs_mu_class` float COMMENT 'Type of object (1 = galaxy, 2 = star, 3 = spurious)', | |
| − | + | `acs_overlap` float COMMENT 'Object overlap flag. All objects here have overlap =1 i.e. no overlap.', | |
| − | + | `acs_nearstar` float COMMENT 'Object in star mask flag. All the objects in this morphological catalog have "nearstar = 1" which means they are not in a star mask.', | |
| − | + | `acs_mask` float COMMENT 'Object in automatic mask flag: 2 = star used to make automatic mask', | |
| − | + | `acs_masked` float COMMENT 'Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask', | |
| − | + | `acs_clean` float COMMENT 'Object useable flag: 0 = do not use this object, 1 = use this object', | |
| − | + | `acs_unique` float COMMENT 'Object unique flag. All the objects in this morphological catalog have "unique = 1" which means they are all "unique" in the original ACS catalog.', | |
| − | + | `gg` float COMMENT 'ZEST Gini coefficient', | |
| − | + | `m20` float COMMENT 'ZEST second order moment of the 20% brightest pixels', | |
| − | + | `cc` float COMMENT 'ZEST concentration', | |
| − | + | `aa` float COMMENT 'ZEST asymmetry', | |
| − | + | `r20` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)', | |
| − | + | `r50` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)', | |
| − | + | `r80` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)', | |
| − | + | `rpet` float COMMENT 'Zest semi-major axis length for Petrosian Radius ellipse (pixels)', | |
| − | + | `flagrpet` float COMMENT 'Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)', | |
| − | + | `flux_gim2d` float COMMENT 'GIM2D total flux (counts)', | |
| − | + | `le_flux_gim2d` float COMMENT 'LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', | |
| − | + | `ue_flux_gim2d` float COMMENT 'UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', | |
| − | + | `r_gim2d` float COMMENT 'GIM2D psf-convolved half-light radius of object (arcseconds)', | |
| − | + | `le_r_gim2d` float COMMENT '99% conf. lower error on R_GIM2D', | |
| − | + | `ue_r_gim2d` float COMMENT '99% conf. upper error on R_GIM2D', | |
| − | + | `ell_gim2d` float COMMENT 'GIM2D ellipticity = 1-b/a of object', | |
| − | + | `le_ell_gim2d` float COMMENT '99% conf. lower error on ELL_GIM2D', | |
| − | + | `ue_ell_gim2d` float COMMENT '99% conf. upper error on ELL_GIM2D', | |
| − | + | `pa_gim2d` float COMMENT 'GIM2D position angle of object - cw from +y-axis (degrees)', | |
| − | + | `le_pa_gim2d` float COMMENT '99% conf. lower error on PA_GIM2D', | |
| − | + | `ue_pa_gim2d` float COMMENT '99% conf. upper error on PA_GIM2D', | |
| − | + | `dx_gim2d` float COMMENT 'x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', | |
| − | + | `le_dx_gim2d` float COMMENT '99% conf. lower error on DX_GIM2D', | |
| − | + | `ue_dx_gim2d` float COMMENT '99% conf. upper error on DX_GIM2D', | |
| − | + | `dy_gim2d` float COMMENT 'y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', | |
| − | + | `le_dy_gim2d` float COMMENT '99% conf. lower error on DY_GIM2D', | |
| − | + | `ue_dy_gim2d` float COMMENT '99% conf. upper error on DY_GIM2D', | |
| − | + | `sersic_n_gim2d` float COMMENT 'GIM2D Sersic index', | |
| − | + | `le_n_gim2d` float COMMENT '99% conf. lower error on SERSIC_N_GIM2D', | |
| − | + | `ue_n_gim2d` float COMMENT '99% conf. upper error on SERSIC_N_GIM2D', | |
| − | + | `r_0p5_gim2d` float COMMENT 'GIM2D half-light radius of object without PSF convolution (arcseconds)', | |
| − | + | `chi_gim2d` float COMMENT 'reduced CHI2 of the best GIM2D model fit', | |
| − | + | `iter_gim2d` float COMMENT 'no. of iterations required for GIM2D fit to converge', | |
| − | + | `pc_1` float COMMENT 'ZEST first principal component', | |
| − | + | `pc_2` float COMMENT 'ZEST second principal component', | |
| − | + | `pc_3` float COMMENT 'ZEST third principal component', | |
| − | + | `type` float COMMENT 'ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)', | |
| − | + | `bulg` float COMMENT 'ZEST "Bulgeness" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification', | |
| − | + | `irre` float COMMENT 'ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value', | |
| − | + | `elli` float COMMENT 'ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation', | |
| − | + | `stellarity` float COMMENT 'Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)', | |
| − | + | `junkflag` float COMMENT '0 = good object, 1 = spurious', | |
| − | + | `acstile` string COMMENT 'ACS TILE number from which the object is extracted') | |
| − | + | CLUSTERED BY ( | |
| − | + | sequentialid) | |
| − | + | 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' | ||
| + | ; | ||
| + | </pre> | ||
| − | + | <pre> | |
| − | + | INSERT OVERWRITE TABLE cosmohub.zest_v1_0_cosmos2015_kai_join | |
| − | + | SELECT zest.sequentialid, ids.id, zest.ra, zest.dec, zest.capak_id, zest.capak_ra, zest.capak_dec, zest.acs_mag_auto, zest.acs_magerr_auto, zest.acs_x_image, zest.acs_y_image, zest.acs_xpeak_image, zest.acs_ypeak_image, zest.acs_alphapeak_, zest.acs_deltapeak_, zest.acs_a_image, zest.acs_b_image, zest.acs_theta_image, zest.acs_elongation, zest.acs_class_star, zest.acs_ident, zest.acs_se, zest.acs_mu_class, zest.acs_overlap, zest.acs_nearstar, zest.acs_mask, zest.acs_masked, zest.acs_clean, zest.acs_unique, zest.gg, zest.m20, zest.cc, zest.aa, zest.r20, zest.r50, zest.r80, zest.rpet, zest.flagrpet, zest.flux_gim2d, zest.le_flux_gim2d, zest.ue_flux_gim2d, zest.r_gim2d, zest.le_r_gim2d, zest.ue_r_gim2d, zest.ell_gim2d, zest.le_ell_gim2d, zest.ue_ell_gim2d, zest.pa_gim2d, zest.le_pa_gim2d, zest.ue_pa_gim2d, zest.dx_gim2d, zest.le_dx_gim2d, zest.ue_dx_gim2d, zest.dy_gim2d, zest.le_dy_gim2d, zest.ue_dy_gim2d, zest.sersic_n_gim2d, zest.le_n_gim2d, zest.ue_n_gim2d, zest.r_0p5_gim2d, zest.chi_gim2d, zest.iter_gim2d, zest.pc_1, zest.pc_2, zest.pc_3, zest.type, zest.bulg, zest.irre, zest.elli, zest.stellarity, zest.junkflag, zest.acstile | |
| + | FROM cosmohub.zest_v1_0_cosmos2015_join as zest | ||
| + | JOIN jcarrete.zest_cosmos2015_spatial_matching_kai_file_csv as ids | ||
| + | ON zest.sequentialid = ids.sequentialid | ||
| + | WHERE ids.id IS NOT NULL; | ||
| + | </pre> | ||
Latest revision as of 15:31, 29 November 2019
Data
Downloaded on 2019-02-18 from https://irsa.ipac.caltech.edu/data/COSMOS/tables/morphology/cosmos_morph_zurich_1.0.tbl
Converted to CSV using LibreOffice Calc.
SQL
CREATE EXTERNAL TABLE tallada.zest (
`SequentialID` INT COMMENT "Identification number",
`RA` FLOAT COMMENT "Right ascension (degrees)",
`DEC` FLOAT COMMENT "Declination (degrees)",
`CAPAK_ID` FLOAT COMMENT "ID based on ground-based photometric catalog of Capak 2006",
`CAPAK_RA` FLOAT COMMENT "Closest matched right ascension to ground-based catalog within 0.6\" (degrees)",
`CAPAK_DEC` FLOAT COMMENT "Closest matched declination to ground-based catalog within 0.6\" (degrees)",
`ACS_MAG_AUTO` FLOAT COMMENT "Total magnitude of object- used for catalog selection",
`ACS_MAGERR_AUTO` FLOAT COMMENT "Magnitude error",
`ACS_X_IMAGE` FLOAT COMMENT "X-pixel position on ACS-tile",
`ACS_Y_IMAGE` FLOAT COMMENT "Y-pixel position on ACS-tile",
`ACS_XPEAK_IMAGE` FLOAT COMMENT "X-pixel position of object peak flux on ACS tile",
`ACS_YPEAK_IMAGE` FLOAT COMMENT "Y-pixel position of object peak flux on ACS tile",
`ACS_ALPHAPEAK_` FLOAT COMMENT "RA of object peak flux (degrees)",
`ACS_DELTAPEAK_` FLOAT COMMENT "Dec of object peak flux (degrees)",
`ACS_A_IMAGE` FLOAT COMMENT "SExtractor semi-major axis",
`ACS_B_IMAGE` FLOAT COMMENT "SExtractor semi-minor axis",
`ACS_THETA_IMAGE` FLOAT COMMENT "SExtractor position angle",
`ACS_ELONGATION` FLOAT COMMENT "SExtractor a/b [semi-major axis / semi minor axis]",
`ACS_CLASS_STAR` FLOAT COMMENT "SExtractor stellarity parameter",
`ACS_IDENT` FLOAT COMMENT "Unique number that identifies object",
`ACS_SE` FLOAT COMMENT "0 = Detection from 'cold' pass of Sextractor, 1 = Detection from 'hot' pass of Sextractor",
`ACS_MU_CLASS` FLOAT COMMENT "Type of object (1 = galaxy, 2 = star, 3 = spurious)",
`ACS_OVERLAP` FLOAT COMMENT "Object overlap flag. All objects here have overlap =1 i.e. no overlap.",
`ACS_NEARSTAR` FLOAT COMMENT "Object in star mask flag. All the objects in this morphological catalog have \"nearstar = 1\" which means they are not in a star mask.",
`ACS_MASK` FLOAT COMMENT "Object in automatic mask flag: 2 = star used to make automatic mask",
`ACS_MASKED` FLOAT COMMENT "Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask",
`ACS_CLEAN` FLOAT COMMENT "Object useable flag: 0 = do not use this object, 1 = use this object",
`ACS_UNIQUE` FLOAT COMMENT "Object unique flag. All the objects in this morphological catalog have \"unique = 1\" which means they are all \"unique\" in the original ACS catalog.",
`GG` FLOAT COMMENT "ZEST Gini coefficient",
`M20` FLOAT COMMENT "ZEST second order moment of the 20% brightest pixels",
`CC` FLOAT COMMENT "ZEST concentration",
`AA` FLOAT COMMENT "ZEST asymmetry",
`R20` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)",
`R50` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)",
`R80` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)",
`RPET` FLOAT COMMENT "Zest semi-major axis length for Petrosian Radius ellipse (pixels)",
`FLAGRPET` FLOAT COMMENT "Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)",
`FLUX_GIM2D` FLOAT COMMENT "GIM2D total flux (counts)",
`LE_FLUX_GIM2D` FLOAT COMMENT "LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)",
`UE_FLUX_GIM2D` FLOAT COMMENT "UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)",
`R_GIM2D` FLOAT COMMENT "GIM2D psf-convolved half-light radius of object (arcseconds)",
`LE_R_GIM2D` FLOAT COMMENT "99% conf. lower error on R_GIM2D",
`UE_R_GIM2D` FLOAT COMMENT "99% conf. upper error on R_GIM2D",
`ELL_GIM2D` FLOAT COMMENT "GIM2D ellipticity = 1-b/a of object",
`LE_ELL_GIM2D` FLOAT COMMENT "99% conf. lower error on ELL_GIM2D",
`UE_ELL_GIM2D` FLOAT COMMENT "99% conf. upper error on ELL_GIM2D",
`PA_GIM2D` FLOAT COMMENT "GIM2D position angle of object - cw from +y-axis (degrees)",
`LE_PA_GIM2D` FLOAT COMMENT "99% conf. lower error on PA_GIM2D",
`UE_PA_GIM2D` FLOAT COMMENT "99% conf. upper error on PA_GIM2D",
`DX_GIM2D` FLOAT COMMENT "x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)",
`LE_DX_GIM2D` FLOAT COMMENT "99% conf. lower error on DX_GIM2D",
`UE_DX_GIM2D` FLOAT COMMENT "99% conf. upper error on DX_GIM2D",
`DY_GIM2D` FLOAT COMMENT "y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)",
`LE_DY_GIM2D` FLOAT COMMENT "99% conf. lower error on DY_GIM2D",
`UE_DY_GIM2D` FLOAT COMMENT "99% conf. upper error on DY_GIM2D",
`SERSIC_N_GIM2D` FLOAT COMMENT "GIM2D Sersic index",
`LE_N_GIM2D` FLOAT COMMENT "99% conf. lower error on SERSIC_N_GIM2D",
`UE_N_GIM2D` FLOAT COMMENT "99% conf. upper error on SERSIC_N_GIM2D",
`R_0P5_GIM2D` FLOAT COMMENT "GIM2D half-light radius of object without PSF convolution (arcseconds)",
`CHI_GIM2D` FLOAT COMMENT "reduced CHI2 of the best GIM2D model fit",
`ITER_GIM2D` FLOAT COMMENT "no. of iterations required for GIM2D fit to converge",
`PC_1` FLOAT COMMENT "ZEST first principal component",
`PC_2` FLOAT COMMENT "ZEST second principal component",
`PC_3` FLOAT COMMENT "ZEST third principal component",
`TYPE` FLOAT COMMENT "ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)",
`BULG` FLOAT COMMENT "ZEST \"Bulgeness\" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification",
`IRRE` FLOAT COMMENT "ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value",
`ELLI` FLOAT COMMENT "ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation",
`STELLARITY` FLOAT COMMENT "Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)",
`JUNKFLAG` FLOAT COMMENT "0 = good object, 1 = spurious",
`ACSTile` STRING COMMENT "ACS TILE number from which the object is extracted"
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/user/tallada/data/zest'
;
CREATE TABLE cosmohub.zest_v1_0 (
`SequentialID` INT COMMENT "Identification number",
`RA` FLOAT COMMENT "Right ascension (degrees)",
`DEC` FLOAT COMMENT "Declination (degrees)",
`CAPAK_ID` FLOAT COMMENT "ID based on ground-based photometric catalog of Capak 2006",
`CAPAK_RA` FLOAT COMMENT "Closest matched right ascension to ground-based catalog within 0.6\" (degrees)",
`CAPAK_DEC` FLOAT COMMENT "Closest matched declination to ground-based catalog within 0.6\" (degrees)",
`ACS_MAG_AUTO` FLOAT COMMENT "Total magnitude of object- used for catalog selection",
`ACS_MAGERR_AUTO` FLOAT COMMENT "Magnitude error",
`ACS_X_IMAGE` FLOAT COMMENT "X-pixel position on ACS-tile",
`ACS_Y_IMAGE` FLOAT COMMENT "Y-pixel position on ACS-tile",
`ACS_XPEAK_IMAGE` FLOAT COMMENT "X-pixel position of object peak flux on ACS tile",
`ACS_YPEAK_IMAGE` FLOAT COMMENT "Y-pixel position of object peak flux on ACS tile",
`ACS_ALPHAPEAK_` FLOAT COMMENT "RA of object peak flux (degrees)",
`ACS_DELTAPEAK_` FLOAT COMMENT "Dec of object peak flux (degrees)",
`ACS_A_IMAGE` FLOAT COMMENT "SExtractor semi-major axis",
`ACS_B_IMAGE` FLOAT COMMENT "SExtractor semi-minor axis",
`ACS_THETA_IMAGE` FLOAT COMMENT "SExtractor position angle",
`ACS_ELONGATION` FLOAT COMMENT "SExtractor a/b [semi-major axis / semi minor axis]",
`ACS_CLASS_STAR` FLOAT COMMENT "SExtractor stellarity parameter",
`ACS_IDENT` FLOAT COMMENT "Unique number that identifies object",
`ACS_SE` FLOAT COMMENT "0 = Detection from 'cold' pass of Sextractor, 1 = Detection from 'hot' pass of Sextractor",
`ACS_MU_CLASS` FLOAT COMMENT "Type of object (1 = galaxy, 2 = star, 3 = spurious)",
`ACS_OVERLAP` FLOAT COMMENT "Object overlap flag. All objects here have overlap =1 i.e. no overlap.",
`ACS_NEARSTAR` FLOAT COMMENT "Object in star mask flag. All the objects in this morphological catalog have \"nearstar = 1\" which means they are not in a star mask.",
`ACS_MASK` FLOAT COMMENT "Object in automatic mask flag: 2 = star used to make automatic mask",
`ACS_MASKED` FLOAT COMMENT "Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask",
`ACS_CLEAN` FLOAT COMMENT "Object useable flag: 0 = do not use this object, 1 = use this object",
`ACS_UNIQUE` FLOAT COMMENT "Object unique flag. All the objects in this morphological catalog have \"unique = 1\" which means they are all \"unique\" in the original ACS catalog.",
`GG` FLOAT COMMENT "ZEST Gini coefficient",
`M20` FLOAT COMMENT "ZEST second order moment of the 20% brightest pixels",
`CC` FLOAT COMMENT "ZEST concentration",
`AA` FLOAT COMMENT "ZEST asymmetry",
`R20` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)",
`R50` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)",
`R80` FLOAT COMMENT "ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)",
`RPET` FLOAT COMMENT "Zest semi-major axis length for Petrosian Radius ellipse (pixels)",
`FLAGRPET` FLOAT COMMENT "Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)",
`FLUX_GIM2D` FLOAT COMMENT "GIM2D total flux (counts)",
`LE_FLUX_GIM2D` FLOAT COMMENT "LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)",
`UE_FLUX_GIM2D` FLOAT COMMENT "UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)",
`R_GIM2D` FLOAT COMMENT "GIM2D psf-convolved half-light radius of object (arcseconds)",
`LE_R_GIM2D` FLOAT COMMENT "99% conf. lower error on R_GIM2D",
`UE_R_GIM2D` FLOAT COMMENT "99% conf. upper error on R_GIM2D",
`ELL_GIM2D` FLOAT COMMENT "GIM2D ellipticity = 1-b/a of object",
`LE_ELL_GIM2D` FLOAT COMMENT "99% conf. lower error on ELL_GIM2D",
`UE_ELL_GIM2D` FLOAT COMMENT "99% conf. upper error on ELL_GIM2D",
`PA_GIM2D` FLOAT COMMENT "GIM2D position angle of object - cw from +y-axis (degrees)",
`LE_PA_GIM2D` FLOAT COMMENT "99% conf. lower error on PA_GIM2D",
`UE_PA_GIM2D` FLOAT COMMENT "99% conf. upper error on PA_GIM2D",
`DX_GIM2D` FLOAT COMMENT "x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)",
`LE_DX_GIM2D` FLOAT COMMENT "99% conf. lower error on DX_GIM2D",
`UE_DX_GIM2D` FLOAT COMMENT "99% conf. upper error on DX_GIM2D",
`DY_GIM2D` FLOAT COMMENT "y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)",
`LE_DY_GIM2D` FLOAT COMMENT "99% conf. lower error on DY_GIM2D",
`UE_DY_GIM2D` FLOAT COMMENT "99% conf. upper error on DY_GIM2D",
`SERSIC_N_GIM2D` FLOAT COMMENT "GIM2D Sersic index",
`LE_N_GIM2D` FLOAT COMMENT "99% conf. lower error on SERSIC_N_GIM2D",
`UE_N_GIM2D` FLOAT COMMENT "99% conf. upper error on SERSIC_N_GIM2D",
`R_0P5_GIM2D` FLOAT COMMENT "GIM2D half-light radius of object without PSF convolution (arcseconds)",
`CHI_GIM2D` FLOAT COMMENT "reduced CHI2 of the best GIM2D model fit",
`ITER_GIM2D` FLOAT COMMENT "no. of iterations required for GIM2D fit to converge",
`PC_1` FLOAT COMMENT "ZEST first principal component",
`PC_2` FLOAT COMMENT "ZEST second principal component",
`PC_3` FLOAT COMMENT "ZEST third principal component",
`TYPE` FLOAT COMMENT "ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)",
`BULG` FLOAT COMMENT "ZEST \"Bulgeness\" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification",
`IRRE` FLOAT COMMENT "ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value",
`ELLI` FLOAT COMMENT "ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation",
`STELLARITY` FLOAT COMMENT "Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)",
`JUNKFLAG` FLOAT COMMENT "0 = good object, 1 = spurious",
`ACSTile` STRING COMMENT "ACS TILE number from which the object is extracted"
)
CLUSTERED BY (`SequentialID`)
INTO 4 BUCKETS
STORED AS ORC
;
INSERT OVERWRITE TABLE cosmohub.zest_v1_0 SELECT * FROM tallada.zest ;
UPDATE 1
I update the catalog to include the cosmos_2015_dr_2_1 index to be able to make joins directly in COSMOHUB.
To do the matching I use a python notebook which can be found here:
> /nfs/pic.es/user/j/jcarrete/python_notebooks/Matching_ZEST_and_COSMOS2015.ipynb
I create a table with the "link" (`sequentialid`, `cosmos2015_id):
CREATE EXTERNAL TABLE jcarrete.zest_cosmos2015_spatial_matching_csv ( sequentialid int, ra float, dec float, cosmos2015_id bigint, cosmos2015_distance float ) 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://namenode01/user/jcarrete/data/zest_cosmos2015_spatial_matching/' ;
This is the ZEST table updated, which include the COSMOS2015 id:
CREATE TABLE cosmohub.`zest_v1_0_cosmos2015_join`( `sequentialid` int COMMENT 'Identification number', `cosmos2015_id` bigint COMMENT 'id from COSMOS2015 DR2.1 catalog to be able to JOIN both catalogs', `cosmos2015_distance` float COMMENT 'estimated distance from the nearest galaxy when using sklearn.neighbors.KDTree (ra, dec)', `ra` float COMMENT 'Right ascension (degrees)', `dec` float COMMENT 'Declination (degrees)', `capak_id` float COMMENT 'ID based on ground-based photometric catalog of Capak 2006', `capak_ra` float COMMENT 'Closest matched right ascension to ground-based catalog within 0.6" (degrees)', `capak_dec` float COMMENT 'Closest matched declination to ground-based catalog within 0.6" (degrees)', `acs_mag_auto` float COMMENT 'Total magnitude of object- used for catalog selection', `acs_magerr_auto` float COMMENT 'Magnitude error', `acs_x_image` float COMMENT 'X-pixel position on ACS-tile', `acs_y_image` float COMMENT 'Y-pixel position on ACS-tile', `acs_xpeak_image` float COMMENT 'X-pixel position of object peak flux on ACS tile', `acs_ypeak_image` float COMMENT 'Y-pixel position of object peak flux on ACS tile', `acs_alphapeak_` float COMMENT 'RA of object peak flux (degrees)', `acs_deltapeak_` float COMMENT 'Dec of object peak flux (degrees)', `acs_a_image` float COMMENT 'SExtractor semi-major axis', `acs_b_image` float COMMENT 'SExtractor semi-minor axis', `acs_theta_image` float COMMENT 'SExtractor position angle', `acs_elongation` float COMMENT 'SExtractor a/b [semi-major axis / semi minor axis]', `acs_class_star` float COMMENT 'SExtractor stellarity parameter', `acs_ident` float COMMENT 'Unique number that identifies object', `acs_se` float COMMENT '0 = Detection from \'cold\' pass of Sextractor, 1 = Detection from \'hot\' pass of Sextractor', `acs_mu_class` float COMMENT 'Type of object (1 = galaxy, 2 = star, 3 = spurious)', `acs_overlap` float COMMENT 'Object overlap flag. All objects here have overlap =1 i.e. no overlap.', `acs_nearstar` float COMMENT 'Object in star mask flag. All the objects in this morphological catalog have "nearstar = 1" which means they are not in a star mask.', `acs_mask` float COMMENT 'Object in automatic mask flag: 2 = star used to make automatic mask', `acs_masked` float COMMENT 'Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask', `acs_clean` float COMMENT 'Object useable flag: 0 = do not use this object, 1 = use this object', `acs_unique` float COMMENT 'Object unique flag. All the objects in this morphological catalog have "unique = 1" which means they are all "unique" in the original ACS catalog.', `gg` float COMMENT 'ZEST Gini coefficient', `m20` float COMMENT 'ZEST second order moment of the 20% brightest pixels', `cc` float COMMENT 'ZEST concentration', `aa` float COMMENT 'ZEST asymmetry', `r20` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)', `r50` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)', `r80` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)', `rpet` float COMMENT 'Zest semi-major axis length for Petrosian Radius ellipse (pixels)', `flagrpet` float COMMENT 'Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)', `flux_gim2d` float COMMENT 'GIM2D total flux (counts)', `le_flux_gim2d` float COMMENT 'LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', `ue_flux_gim2d` float COMMENT 'UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', `r_gim2d` float COMMENT 'GIM2D psf-convolved half-light radius of object (arcseconds)', `le_r_gim2d` float COMMENT '99% conf. lower error on R_GIM2D', `ue_r_gim2d` float COMMENT '99% conf. upper error on R_GIM2D', `ell_gim2d` float COMMENT 'GIM2D ellipticity = 1-b/a of object', `le_ell_gim2d` float COMMENT '99% conf. lower error on ELL_GIM2D', `ue_ell_gim2d` float COMMENT '99% conf. upper error on ELL_GIM2D', `pa_gim2d` float COMMENT 'GIM2D position angle of object - cw from +y-axis (degrees)', `le_pa_gim2d` float COMMENT '99% conf. lower error on PA_GIM2D', `ue_pa_gim2d` float COMMENT '99% conf. upper error on PA_GIM2D', `dx_gim2d` float COMMENT 'x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', `le_dx_gim2d` float COMMENT '99% conf. lower error on DX_GIM2D', `ue_dx_gim2d` float COMMENT '99% conf. upper error on DX_GIM2D', `dy_gim2d` float COMMENT 'y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', `le_dy_gim2d` float COMMENT '99% conf. lower error on DY_GIM2D', `ue_dy_gim2d` float COMMENT '99% conf. upper error on DY_GIM2D', `sersic_n_gim2d` float COMMENT 'GIM2D Sersic index', `le_n_gim2d` float COMMENT '99% conf. lower error on SERSIC_N_GIM2D', `ue_n_gim2d` float COMMENT '99% conf. upper error on SERSIC_N_GIM2D', `r_0p5_gim2d` float COMMENT 'GIM2D half-light radius of object without PSF convolution (arcseconds)', `chi_gim2d` float COMMENT 'reduced CHI2 of the best GIM2D model fit', `iter_gim2d` float COMMENT 'no. of iterations required for GIM2D fit to converge', `pc_1` float COMMENT 'ZEST first principal component', `pc_2` float COMMENT 'ZEST second principal component', `pc_3` float COMMENT 'ZEST third principal component', `type` float COMMENT 'ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)', `bulg` float COMMENT 'ZEST "Bulgeness" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification', `irre` float COMMENT 'ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value', `elli` float COMMENT 'ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation', `stellarity` float COMMENT 'Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)', `junkflag` float COMMENT '0 = good object, 1 = spurious', `acstile` string COMMENT 'ACS TILE number from which the object is extracted') CLUSTERED BY ( sequentialid) 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' ;
I materialize the JOIN:
INSERT OVERWRITE TABLE cosmohub.`zest_v1_0_cosmos2015_join` SELECT zest.`sequentialid`, link.`cosmos2015_id`, link.`cosmos2015_distance`, zest.`ra`, zest.`dec`, zest.`capak_id`, zest.`capak_ra`, zest.`capak_dec`, zest.`acs_mag_auto`, zest.`acs_magerr_auto`, zest.`acs_x_image`, zest.`acs_y_image`, zest.`acs_xpeak_image`, zest.`acs_ypeak_image`, zest.`acs_alphapeak_`, zest.`acs_deltapeak_`, zest.`acs_a_image`, zest.`acs_b_image`, zest.`acs_theta_image`, zest.`acs_elongation`, zest.`acs_class_star`, zest.`acs_ident`, zest.`acs_se`, zest.`acs_mu_class`, zest.`acs_overlap`, zest.`acs_nearstar`, zest.`acs_mask`, zest.`acs_masked`, zest.`acs_clean`, zest.`acs_unique`, zest.`gg`, zest.`m20`, zest.`cc`, zest.`aa`, zest.`r20`, zest.`r50`, zest.`r80`, zest.`rpet`, zest.`flagrpet`, zest.`flux_gim2d`, zest.`le_flux_gim2d`, zest.`ue_flux_gim2d`, zest.`r_gim2d`, zest.`le_r_gim2d`, zest.`ue_r_gim2d`, zest.`ell_gim2d`, zest.`le_ell_gim2d`, zest.`ue_ell_gim2d`, zest.`pa_gim2d`, zest.`le_pa_gim2d`, zest.`ue_pa_gim2d`, zest.`dx_gim2d`, zest.`le_dx_gim2d`, zest.`ue_dx_gim2d`, zest.`dy_gim2d`, zest.`le_dy_gim2d`, zest.`ue_dy_gim2d`, zest.`sersic_n_gim2d`, zest.`le_n_gim2d`, zest.`ue_n_gim2d`, zest.`r_0p5_gim2d`, zest.`chi_gim2d`, zest.`iter_gim2d`, zest.`pc_1`, zest.`pc_2`, zest.`pc_3`, zest.`type`, zest.`bulg`, zest.`irre`, zest.`elli`, zest.`stellarity`, zest.`junkflag`, zest.`acstile` FROM cosmohub.zest_v1_0 as zest JOIN jcarrete.zest_cosmos2015_spatial_matching_csv as link ON zest.`sequentialid` = link.`sequentialid` ;
And here is a test:
SELECT zest.sequentialid, zest.cosmos2015_id, cosmos.id, zest.ra, cosmos.alpha_j2000, zest.dec, cosmos.delta_j2000, zest.cosmos2015_distance FROM cosmohub.`zest_v1_0_cosmos2015_join` as zest JOIN cosmohub.cosmos_2015_dr_2_1 as cosmos ON zest.cosmos2015_id = cosmos.id LIMIT 5;
UPDATE 2
I take file from Kai. Spatial matching only produces some outliers. He uses magnitudes at the same time and it works much better.
CREATE EXTERNAL TABLE `zest_cosmos2015_spatial_matching_kai_file_csv`( `id` int COMMENT '', `sequentialid` int COMMENT '' ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ( 'escape.delim'='$', 'field.delim'=',', 'serialization.format'=',') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://namenode01//user/jcarrete/data/zest_cosmos2015_spatial_matching_kai_file/' ;
CREATE TABLE `zest_v1_0_cosmos2015_kai_join`( `sequentialid` int COMMENT 'Identification number', `cosmos2015_id` bigint COMMENT 'id from COSMOS2015 DR2.1 catalog to be able to JOIN both catalogs', `ra` float COMMENT 'Right ascension (degrees)', `dec` float COMMENT 'Declination (degrees)', `capak_id` float COMMENT 'ID based on ground-based photometric catalog of Capak 2006', `capak_ra` float COMMENT 'Closest matched right ascension to ground-based catalog within 0.6" (degrees)', `capak_dec` float COMMENT 'Closest matched declination to ground-based catalog within 0.6" (degrees)', `acs_mag_auto` float COMMENT 'Total magnitude of object- used for catalog selection', `acs_magerr_auto` float COMMENT 'Magnitude error', `acs_x_image` float COMMENT 'X-pixel position on ACS-tile', `acs_y_image` float COMMENT 'Y-pixel position on ACS-tile', `acs_xpeak_image` float COMMENT 'X-pixel position of object peak flux on ACS tile', `acs_ypeak_image` float COMMENT 'Y-pixel position of object peak flux on ACS tile', `acs_alphapeak_` float COMMENT 'RA of object peak flux (degrees)', `acs_deltapeak_` float COMMENT 'Dec of object peak flux (degrees)', `acs_a_image` float COMMENT 'SExtractor semi-major axis', `acs_b_image` float COMMENT 'SExtractor semi-minor axis', `acs_theta_image` float COMMENT 'SExtractor position angle', `acs_elongation` float COMMENT 'SExtractor a/b [semi-major axis / semi minor axis]', `acs_class_star` float COMMENT 'SExtractor stellarity parameter', `acs_ident` float COMMENT 'Unique number that identifies object', `acs_se` float COMMENT '0 = Detection from \'cold\' pass of Sextractor, 1 = Detection from \'hot\' pass of Sextractor', `acs_mu_class` float COMMENT 'Type of object (1 = galaxy, 2 = star, 3 = spurious)', `acs_overlap` float COMMENT 'Object overlap flag. All objects here have overlap =1 i.e. no overlap.', `acs_nearstar` float COMMENT 'Object in star mask flag. All the objects in this morphological catalog have "nearstar = 1" which means they are not in a star mask.', `acs_mask` float COMMENT 'Object in automatic mask flag: 2 = star used to make automatic mask', `acs_masked` float COMMENT 'Object in manual mask flag: -1 = manual mask (astrophysical obj), 0 = manual mask (artifact or image defect), 1 = not in manual mask', `acs_clean` float COMMENT 'Object useable flag: 0 = do not use this object, 1 = use this object', `acs_unique` float COMMENT 'Object unique flag. All the objects in this morphological catalog have "unique = 1" which means they are all "unique" in the original ACS catalog.', `gg` float COMMENT 'ZEST Gini coefficient', `m20` float COMMENT 'ZEST second order moment of the 20% brightest pixels', `cc` float COMMENT 'ZEST concentration', `aa` float COMMENT 'ZEST asymmetry', `r20` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 20% of total light (pixels)', `r50` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 50% of total light (pixels)', `r80` float COMMENT 'ZEST semi-major axis length of ellipse encompassing 80% of total light (pixels)', `rpet` float COMMENT 'Zest semi-major axis length for Petrosian Radius ellipse (pixels)', `flagrpet` float COMMENT 'Petrosian radius flag: 0 = single Petrosian radius measured, 2 = multiple radii measured (noisy SB profile), 5 = SB profile was corrected for diffuse light contribution from neighboring object(s)', `flux_gim2d` float COMMENT 'GIM2D total flux (counts)', `le_flux_gim2d` float COMMENT 'LE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', `ue_flux_gim2d` float COMMENT 'UE_FLUX_GIM2D (99% conf. lower error on R_GIM2D)', `r_gim2d` float COMMENT 'GIM2D psf-convolved half-light radius of object (arcseconds)', `le_r_gim2d` float COMMENT '99% conf. lower error on R_GIM2D', `ue_r_gim2d` float COMMENT '99% conf. upper error on R_GIM2D', `ell_gim2d` float COMMENT 'GIM2D ellipticity = 1-b/a of object', `le_ell_gim2d` float COMMENT '99% conf. lower error on ELL_GIM2D', `ue_ell_gim2d` float COMMENT '99% conf. upper error on ELL_GIM2D', `pa_gim2d` float COMMENT 'GIM2D position angle of object - cw from +y-axis (degrees)', `le_pa_gim2d` float COMMENT '99% conf. lower error on PA_GIM2D', `ue_pa_gim2d` float COMMENT '99% conf. upper error on PA_GIM2D', `dx_gim2d` float COMMENT 'x-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', `le_dx_gim2d` float COMMENT '99% conf. lower error on DX_GIM2D', `ue_dx_gim2d` float COMMENT '99% conf. upper error on DX_GIM2D', `dy_gim2d` float COMMENT 'y-offset of GIM2D-model center from ACS-coordinate center (arcseconds)', `le_dy_gim2d` float COMMENT '99% conf. lower error on DY_GIM2D', `ue_dy_gim2d` float COMMENT '99% conf. upper error on DY_GIM2D', `sersic_n_gim2d` float COMMENT 'GIM2D Sersic index', `le_n_gim2d` float COMMENT '99% conf. lower error on SERSIC_N_GIM2D', `ue_n_gim2d` float COMMENT '99% conf. upper error on SERSIC_N_GIM2D', `r_0p5_gim2d` float COMMENT 'GIM2D half-light radius of object without PSF convolution (arcseconds)', `chi_gim2d` float COMMENT 'reduced CHI2 of the best GIM2D model fit', `iter_gim2d` float COMMENT 'no. of iterations required for GIM2D fit to converge', `pc_1` float COMMENT 'ZEST first principal component', `pc_2` float COMMENT 'ZEST second principal component', `pc_3` float COMMENT 'ZEST third principal component', `type` float COMMENT 'ZEST Type CLASS (1 = Early type, 2 = Disk, 3 = Irregular Galaxy, 9 = no classification)', `bulg` float COMMENT 'ZEST "Bulgeness" CLASS - only for Type 2 (disk) galaxies: 0 = bulge dominated galaxy, 1,2 = INTermediate-bulge galaxies, 3 = pure disk galaxy, 9 = no classification', `irre` float COMMENT 'ZEST Irregularity CLASS - only for Type 1 (elliptical) galaxies: 0 = regular, 1 = irregular, 9 = no value', `elli` float COMMENT 'ZEST Ellipticity CLASS, 0 = fac-on, 1,2 = increasingly higher elongation, 3 = edge-on, 9 = no classification of elongation', `stellarity` float COMMENT 'Visual Stellarity flag: 0 if ACS_CLASS_STAR<0.6 (object is ASSUMED to be a galaxy, no visual inspection), 0 if ACS_CLASS_STAR>=0.6 AND object visually identified as a galaxy, 1 if ACS_CLASS_STAR>=0.6 AND visually identified as a star, 2 if ACS_CLASS_STAR>=0.8 (object is assumed to be a star and was not visually inspected), 3 if ACS_CLASS_STAR<0.6 but object is visually identified as a star (e.g. saturated star, etc)', `junkflag` float COMMENT '0 = good object, 1 = spurious', `acstile` string COMMENT 'ACS TILE number from which the object is extracted') CLUSTERED BY ( sequentialid) 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 cosmohub.zest_v1_0_cosmos2015_kai_join SELECT zest.sequentialid, ids.id, zest.ra, zest.dec, zest.capak_id, zest.capak_ra, zest.capak_dec, zest.acs_mag_auto, zest.acs_magerr_auto, zest.acs_x_image, zest.acs_y_image, zest.acs_xpeak_image, zest.acs_ypeak_image, zest.acs_alphapeak_, zest.acs_deltapeak_, zest.acs_a_image, zest.acs_b_image, zest.acs_theta_image, zest.acs_elongation, zest.acs_class_star, zest.acs_ident, zest.acs_se, zest.acs_mu_class, zest.acs_overlap, zest.acs_nearstar, zest.acs_mask, zest.acs_masked, zest.acs_clean, zest.acs_unique, zest.gg, zest.m20, zest.cc, zest.aa, zest.r20, zest.r50, zest.r80, zest.rpet, zest.flagrpet, zest.flux_gim2d, zest.le_flux_gim2d, zest.ue_flux_gim2d, zest.r_gim2d, zest.le_r_gim2d, zest.ue_r_gim2d, zest.ell_gim2d, zest.le_ell_gim2d, zest.ue_ell_gim2d, zest.pa_gim2d, zest.le_pa_gim2d, zest.ue_pa_gim2d, zest.dx_gim2d, zest.le_dx_gim2d, zest.ue_dx_gim2d, zest.dy_gim2d, zest.le_dy_gim2d, zest.ue_dy_gim2d, zest.sersic_n_gim2d, zest.le_n_gim2d, zest.ue_n_gim2d, zest.r_0p5_gim2d, zest.chi_gim2d, zest.iter_gim2d, zest.pc_1, zest.pc_2, zest.pc_3, zest.type, zest.bulg, zest.irre, zest.elli, zest.stellarity, zest.junkflag, zest.acstile FROM cosmohub.zest_v1_0_cosmos2015_join as zest JOIN jcarrete.zest_cosmos2015_spatial_matching_kai_file_csv as ids ON zest.sequentialid = ids.sequentialid WHERE ids.id IS NOT NULL;