-
Notifications
You must be signed in to change notification settings - Fork 13
Schemas of commonly used tables
Mario Juric edited this page Jan 1, 2017
·
2 revisions
... Back to [wiki:LargeSurveyDatabase Large Survey Database Introduction].
This document summarizes the schemas that can be found in {{{lsd/sdss.py}}} and {{{lsd/smf.py}}} files in the source distribution. If you notice any incongruence between what's here and what's in the actual table, we may have modified the files above and forgot to update this document.
The data types are the [http://docs.scipy.org/doc/numpy/reference/arrays.dtypes.html#index-6 Array-protocol type strings] (a character denoting the type, followed by its width in bytes).
Commonly used pseudocols. Use these when writing general-purpose queries and code that needs to be catalog-agnostic (e.g., positional cross-matching). If given without specifying the table (e.g., as just _LON, as opposed to ps1_det._LON), the columns refer to the primary ("root") table of the query.
- '''{{{_ID}}}''':: Primary key of the primary table. If given as {{{table._ID}}}, gives the primary key of table '{{{table}}}'
- '''{{{_LON}}}''':: Longitude key column, if any. This is typically an alias for R.A.
- '''{{{_LAT}}}''':: Latitude key column, if any. This is typically an alias for declination
- '''{{{_TIME}}}''':: Temporal key column, if any. This is typically an alias to {{{mjd_obs}}} or equivalent (see the PS1 tables below).
- '''{{{_EXP}}}''':: Exposure key column, if any. This is typically an alias to {{{exp_id}}} or equivalent (see the PS1 tables below).
Some "low level" pseudocols, not intended for general use, but sometimes useful when developing or debugging:
- '''{{{_ROWIDX}}}''':: 0-based index of the row as stored in a file.
- '''{{{_CELLID}}}''':: The 64bit cell id where the row of the result set was computed.
- '''{{{_ROWID}}}''':: A 64bit ID unique to a particular row within a table. Currently equal to _CELLID + _ROWIDX.
- '''{{{_ROWNUM}}}''':: Numbers the records within a result set '''''of each cell'''''. E.g., the first row has {{{_ROWNUM}}}=1, the second {{{_ROWNUM}}}=2, etc, but in the complete result set there will be as many {{{_ROWNUM}}}=1 rows as there are (non-empty) cells.
The SDSS table has the following columns:
# LSD column name Datatype FITS table column name (if direct mapping exists)
# column group 'main':
('sdss_id', 'u8', ''), # LSD ID
('ra', 'f8', 'ra'), # Right ascension
('dec', 'f8', 'dec'), # Declination
('l', 'f8', ''), # Galactic longitude
('b', 'f8', ''), # Galactic latitude
('type', 'i4', 'objc_type'), # Object type (see SDSS docs for interpretation)
('flags', 'i4', 'objc_flags'), # Flags (see SDSS docs for interpretation)
('flags2', 'i4', 'objc_flags2'), # Flags (see SDSS docs for interpretation)
('resolve_status', 'i2', 'resolve_status') # See SDSS docs for interpretation
# column group 'survey'
('run', 'i4', 'run'), # SDSS Run number
('camcol', 'i4', 'camcol'), # Camera column
('field', 'i4', 'field'), # SDSS field number
('objid', 'i4', 'id'), # SDSS object id (within a given field)
# column group 'photometry'
('u', 'f4', ''), # u-band photometry (AB mags)
('uErr', 'f4', ''), # u-band error estimate
('uExt', 'f4', ''), # u-band extinction (from SFD'98)
('uCalib', 'i2', ''), # u-band flags (see SDSS docs)
('g', 'f4', ''), # g-band photometry (AB mags)
('gErr', 'f4', ''), # g-band error estimate
('gExt', 'f4', ''), # g-band extinction (from SFD'98)
('gCalib', 'i2', ''), # g-band flags (see SDSS docs)
('r', 'f4', ''), # r-band photometry (AB mags)
('rErr', 'f4', ''), # r-band error estimate
('rExt', 'f4', ''), # r-band extinction (from SFD'98)
('rCalib', 'i2', ''), # r-band flags (see SDSS docs)
('i', 'f4', ''), # i-band photometry (AB mags)
('iErr', 'f4', ''), # i-band error estimate
('iExt', 'f4', ''), # i-band extinction (from SFD'98)
('iCalib', 'i2', ''), # i-band flags (see SDSS docs)
('z', 'f4', ''), # z-band photometry (AB mags)
('zErr', 'f4', ''), # z-band error estimate
('zExt', 'f4', ''), # z-band extinction (from SFD'98)
('zCalib', 'i2', ''), # z-band flags (see SDSS docs)
# Indexing
'primary_key' : 'sdss_id', # The unique key in the table, the index
'spatial_keys': ('ra', 'dec'), # The columns used for spatial indexing
'exposure_key': 'run', # The column with a unique per-exposure valueThe exposure table (typically named ps1_exp) is built out of the contents of primary .smf FITS header.
# LSD column name Type FITS keyword Description
# column group 'main'
('exp_id', 'u8', '', 'LSD primary key for this exposure' ),
('filterid', 'a6', 'filterid', 'Filter used (instrument name)' ),
('equinox', 'f4', 'EQUINOX' , 'Celestial coordinate system' ),
('ra', 'f8', 'RA' , 'Right Ascension of boresight' ),
('dec', 'f8', 'DEC' , 'Declination of boresight' ),
('l', 'f8', '' , 'Galactic lon of boresight' ),
('b', 'f8', '' , 'Galactic lat of boresight' ),
('mjd_obs', 'f8', 'MJD-OBS' , 'Time of observation' ),
('exptime', 'f4', 'EXPTIME' , 'Exposure time (sec)' ),
('airmass', 'f4', 'AIRMASS' , 'Observation airmass' ),
('cached', 'bool', '' , 'Set to True if this is a row cached from a different cell'),
# column group 'obsdet'
('m2z', 'f4', 'M2Z' , 'Telescope focus' ),
('obstype', 'a20', 'OBSTYPE' , 'Type of observation' ),
('object', 'a20', 'OBJECT' , 'Object of observation' ),
('expreq', 'f4', 'EXPREQ' , 'Exposure time (sec)' ),
('dettem', 'f4', 'DETTEM' , 'Temperature of focal plane' ),
('filename', 'a20', 'FILENAME', 'FPA observation identifier' ),
('nghosts', 'u4', 'NGHOSTS' , 'total expected ghosts' ),
# column group 'meta'
('notfound_keys', 'i8', '' , 'BLOB list listing header keys that were not found in source .smf file'),
('notfound_cols', '64i8', '' , 'BLOB list listing columns that were not found in the source .smf file'),
# column group 'zeropoints_ifa'
('mag_zp', 'f4', 'MAG_ZP' , 'Magnitude zero point' ),
('zpt_obs', 'f4', 'ZPT_OBS' , 'measured zero point' ),
('zpt_ref', 'f4', 'ZPT_REF' , 'reference zero point' ),
('zpt_err', 'f4', 'ZPT_ERR' , 'error on zero point' ),
('zpt_off', 'f4', 'ZPT_OFF' , 'zero point offset' ),
# column group 'pointing'
('posangle', 'f4', 'POSANGLE', 'Position angle of instrument' ),
('rot', 'f4', 'ROT' , 'Rotator angle of instrument' ),
('alt', 'f8', 'ALT' , 'Altitude of boresight' ),
('az', 'f8', 'AZ' , 'Azimuth of boresight' ),
('ast_r0', 'f8', 'AST_R0' , 'boresite offset in RA (TP units)' ),
('ast_d0', 'f8', 'AST_D0' , 'boresite offset in DEC (TP units)' ),
('ast_t0', 'f8', 'AST_T0' , 'boresite angle (degrees)' ),
('ast_s0', 'f8', 'AST_S0' , 'boresite scale correction' ),
('ast_rs', 'f8', 'AST_RS' , 'boresite scatter in RA (TP units)' ),
('ast_ds', 'f8', 'AST_DS' , 'boresite scatter in DEC (TP units)' ),
('dt_astr', 'f8', 'DT_ASTR' , 'elapsed psastro time' ),
# column group 'header':
('hdr', 'i8', '', 'Primary FITS header of .smf file' ), # (*)
('smf_fn', 'a40', '', 'Filename of the input smf file' ),
# column group 'chips':
('chip_hdr', '64i8', '', 'XY??.hdr FITS headers, one per chip' ) # (*)
# Indexing
'primary_key' : 'exp_id',
'exposure_key': 'exp_id',
'temporal_key': 'mjd_obs',
'spatial_keys': ('ra', 'dec'),(*) These columns contain URIs to full FITS headers, stored externally. Use {{{ffitskw(column, keyword)}}} to extract a particular keyword from these, from within a query. The {{{chip_hdr}}} column contains the (references to) full headers of {{{XY??.hdr}}} HDUs from the input smfs.
The detection table is built out of the contents of {{{XY??.psf}}} HDUs in the input {{{.smf}}} files. Please look at [http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/wiki/CMF_PS1_V3 Gene Magnier's CMF file format documentation] for the description of the columns that aren't documented below.
# LSD column name Type FITS column Description
# column group 'astrometry'
('det_id', 'u8', '', 'Unique LSD ID of this detection'),
('exp_id', 'u8', '', 'Exposure ID, joined to the image table'),
('chip_id', 'u1', '', 'Index of the OTA where this object was detected (integer, 0-63)'),
('ra', 'f8', 'ra_psf', ''),
('dec', 'f8', 'dec_psf', ''),
('mjd_obs', 'f8', '', 'Time of observation'), # Copied from image header, for convenience
('l', 'f8', '', ''),
('b', 'f8', '', ''),
('flags', 'u4', 'flags', ''),
('flags2', 'u4', 'flags2', ''),
('n_frames', 'i2', 'n_frames', ''),
('cached', 'bool', '', ''),
# column group 'detxy'
('ipp_idet', 'i4', 'ipp_idet', ''),
('x_psf', 'f4', 'x_psf', ''),
('y_psf', 'f4', 'y_psf', ''),
('x_psf_sig', 'f4', 'x_psf_sig', ''),
('y_psf_sig', 'f4', 'y_psf_sig', ''),
('posangle', 'f4', 'posangle', ''),
('pltscale', 'f4', 'pltscale', ''),
# column group 'photometry'
('filterid', 'a6', '', 'Filter ID, read from image header (here for convenience)'),
('psf_inst_mag', 'f4', 'psf_inst_mag', ''),
('psf_inst_mag_sig', 'f4', 'psf_inst_mag_sig', ''),
('psf_inst_flux', 'f4', 'psf_inst_flux' , ''),
('psf_inst_flux_sig', 'f4', 'psf_inst_flux_sig', ''),
('ap_mag', 'f4', 'ap_mag', ''),
('ap_mag_raw', 'f4', 'ap_mag_raw', ''),
('ap_mag_radius', 'f4', 'ap_mag_radius', ''),
('peak_flux_as_mag', 'f4', 'peak_flux_as_mag', ''),
('cal_psf_mag', 'f4', 'cal_psf_mag', ''),
('cal_psf_mag_sig', 'f4', 'cal_psf_mag_sig', ''),
# column group 'quality'
('sky', 'f4', 'sky', ''),
('sky_sigma', 'f4', 'sky_sigma', ''),
('psf_chisq', 'f4', 'psf_chisq', ''),
('cr_nsigma', 'f4', 'cr_nsigma', ''),
('ext_nsigma', 'f4', 'ext_nsigma', ''),
('psf_major', 'f4', 'psf_major', ''),
('psf_minor', 'f4', 'psf_minor', ''),
('psf_theta', 'f4', 'psf_theta', ''),
('psf_qf', 'f4', 'psf_qf', ''),
('psf_qf_perfect', 'f4', 'psf_qf_perfect', ''),
('psf_ndof', 'u4', 'psf_ndof', ''),
('psf_npix', 'u4', 'psf_npix', ''),
# column group 'moments'
('moments_xx', 'f4', 'moments_xx', ''),
('moments_xy', 'f4', 'moments_xy', ''),
('moments_yy', 'f4', 'moments_yy', ''),
('moments_m3c', 'f4', 'moments_m3c', ''),
('moments_m3s', 'f4', 'moments_m3s', ''),
('moments_m4c', 'f4', 'moments_m4c', ''),
('moments_m4s', 'f4', 'moments_m4s', ''),
('moments_r1', 'f4', 'moments_r1', ''),
('moments_rh', 'f4', 'moments_rh', ''),
# column group 'kron'
('kron_flux', 'f4', 'kron_flux', ''),
('kron_flux_err', 'f4', 'kron_flux_err', ''),
('kron_flux_inner', 'f4', 'kron_flux_inner', ''),
('kron_flux_outer', 'f4', 'kron_flux_outer', ''),
# Indexing
'primary_key': 'det_id',
'exposure_key': 'exp_id',
'temporal_key': 'mjd_obs',
'spatial_keys': ('ra', 'dec'),Object table gets constructed from the detections table by {{{lsd-make-object-catalog}}}. Right now it contains only the most basic information about the objects (where they are).
# LSD column name Type FITS column Description
# column group 'astrometry'
('obj_id', 'u8', '', 'Unique LSD ID of this object'),
('ra', 'f8', 'ra_psf', ''),
('dec', 'f8', 'dec_psf', ''),
('cached', 'bool', '', ''),
# Indexing
'primary_key': 'obj_id',
'spatial_keys': ('ra', 'dec'),These tables contain the average magnitudes, computed from observations, for the objects.
# LSD column name Type Description
# column group 'astrometry'
('obj_id', 'u8', 'Unique LSD ID of this object'),
('ndet', 'i2', 'The total number of detections of this object, in any band'),
('ndet_ok', 'f8', 'The total number of "good" detections, in any band'),
('nmag', '5i2', 'The number of detections, per band'),
('nmag_ok', '5i2', 'The number of "good" detections, per band'),
('mean', '5f4', 'The mean magnitude, one per band'),
('stdev', '5f4', 'The standard deviation of the mean'),
('err', '5f4', 'Mean magnitude error, computed as inverse sqrt(sum(1/sigma^2))'),
('median', '5f4', 'The median magnitude, one per band'),
('q25', '5f4', '25% quartile magnitude'),
('q75', '5f4', '75% quartile magnitude'),There are a number of tables that follow the above schema:
- cal_mags -- averages based on 2MASS calibrated magnitudes
- inst_mags -- averages based on instrumental magnitude
- recalib_mags -- averages based on per-night SDSS-recalibrated data, w/o extended objects
- recalib_mags2 -- averages based on per-night SDSS-recalibrated data, including objects flagged as extended
- recalib_mags -- averages based on per-night SDSS-recalibrated data, with no cuts based on IPP flags
- recalib_mags_ap -- same as recalib_mags2, but using aperture magnitudes
Note that all of the above are experimental; ask Mario for details on how they were made.
GALEX GR5 data are stored in galex_gr5 table.
# column group 'common'
# LSD column name type column in input csv. file description
('galex_id', 'u8', -1),
('objid', 'u8', 1), # The GALEX objid
('ra', 'f8', 2), # (in degrees)
('dec', 'f8', 3), # (in degrees)
('glon', 'f8', 4), # Galactic Longitude (in degrees)
('glat', 'f8', 5), # Galactic Latitude (in degrees)
('fov_radius', 'f4', 9), # distance from center of field-of-view in degrees
('objtype', 'i2', 14), # 0=galaxy, 1=star, -1=unknown
('e_bv', 'f4', 12), # e(B-V) Galactic reddening inferred from 100um dust emission maps
('fuv_mag', 'f4', 16), # same as fuv_mag_best
('fuv_magerr', 'f4', 17),
('nuv_mag', 'f4', 18),
('nuv_magerr', 'f4', 19),
('photoextractid', 'u8', 52), # Pointer to photoExtract Table
# column group 'survey'
('tilenum', 'i4', 6), # tile number
('img', 'i4', 7), # image number (exposure# for _visits)
('subvisit', 'i2', 8), # sub-visit number for ais
('type', 'i4', 10), # obs.type (0single,1multi)
('band', 'i4', 11), # band number (1nuv,2fuv,3both)
('istherespectrum', 'bool', 13), # Does this object have a (GALEX) spectrum? Yes (1), No (0)
('quality', 'u4', 15), # quality flag (undefined)
('mpstype', 'a3', 53), # survey type (e.g, "MIS")
('avaspra', 'f8', 54), # field center RA
('avaspdec', 'f8', 55), # field center Dec.
# column group 'photoextra'
('fuv_mag_best', 'f4', 20), # pipeline-chosen "best" magnitude (either mag_auto or mag_isocor)
('fuv_magerr_best', 'f4', 21),
('nuv_mag_best', 'f4', 22),
('nuv_magerr_best', 'f4', 23),
('fuv_mag_auto', 'f4', 24), # kron-like elliptical aperture magnitude
('fuv_magerr_auto', 'f4', 25),
('nuv_mag_auto', 'f4', 26),
('nuv_magerr_auto', 'f4', 27),
('fuv_mag_aper_4', 'f4', 28), # flux aperture (8.000)
('fuv_magerr_aper_4', 'f4', 29),
('nuv_mag_aper_4', 'f4', 30),
('nuv_magerr_aper_4', 'f4', 31),
('fuv_mag_aper_6', 'f4', 32), # flux aperture (17.000)
('fuv_magerr_aper_6', 'f4', 33),
('nuv_mag_aper_6', 'f4', 34),
('nuv_magerr_aper_6', 'f4', 35),
('fuv_artifact', 'f4', 36), # fuv artifact flag (logical or near source)
('nuv_artifact', 'f4', 37),
('fuv_flags', 'f4', 38), # extraction flags
('nuv_flags', 'f4', 39),
('fuv_flux', 'f4', 40), # fuv calibrated flux (micro jansky)
('fuv_fluxerr', 'f4', 41),
('nuv_flux', 'f4', 42),
('nuv_fluxerr', 'f4', 43),
# column group 'astrometry'
('fuv_x_image', 'f4', 44), # object position along x
('fuv_y_image', 'f4', 45), # object position along y
('nuv_x_image', 'f4', 46),
('nuv_y_image', 'f4', 47),
('fuv_fwhm_image', 'f4', 48), # fwhm assuming a gaussian core
('nuv_fwhm_image', 'f4', 49),
('fuv_fwhm_world', 'f4', 50), # fwhm assuming a gaussian core
('nuv_fwhm_world', 'f4', 51),- Exposure table is linked to the detection table via {{{exp_id}}}. This is a many-detections-to-one-exposure join.
- Detection table is linked to the object table with a many-detections-to-one-object join, using an indirection table (typically named _ps1_obj_to_ps1_det)
- Object table is linked to the SDSS table via a many-to-one join (because of how xmatching works, multiple PS1 objects may be matched to the same SDSS object), using an indirection table (typically named _ps1_obj_to_sdss).
- Magnitude tables are linked to the object table via a one-to-one join on {{{obj_id}}}