wiki:dbSchemaUpdateApr09

Version 6 (modified by jbecla, 10 years ago) (diff)

added draft schema for Source

This document captures proposed changes to the key LSST Database tables (Object, Source, FaintSource, DIASource) to make these tables aligned with the Level 1 and Level 2 data products outlined in the document written for Science Collaboration after the FRS meeting at UC Davis.

Object

Identifiers

objectId BIGINT NOT NULL        -- primary key
procHistoryId INTEGER NOT NULL  -- for provenance tracking
iauId CHAR(34)

iauId is a IAU compliant name for the object. Example: "LSST-DR11 J001234.65-123456.18 GAL". The last 3 characters identify classification. Note that it will not accommodate multiple classifications.

Best fitting model type for the object

Several types of object model will be fit to the measurements. This column specifies which model type gives the best fit. The number of all models will be small (few). We will be adding new models very infrequently.

Proposed convention: a model will be identified by two characters. We currently have two models: slowly moving point source (“PS”), and small galaxies (“SG”)

bestModel char[2]

Multifit model parameters and covariance matrices

The model parameters include the most important object parameters - mean position, fluxes, shape parameters.

-- ra/dec and their errors are present in each model
-- expressed in degrees
raPS FLOAT
raSigmaPS FLOAT
decPS FLOAT
decSigmaPS FLOAT
raSG FLOAT
raSigmaSG FLOAT
decSG FLOAT
decSigmaSG FLOAT

-- only PS model has these
muRaPS DOUBLE, muRaSigmaPS FLOAT          -- expressed in miliarcsec per year
muDecPS DOUBLE, muDecSigmaPS FLOAT        -- expressed in miliarcsec per year
parallaxPS DOUBLE, parallaxSigmaPS FLOAT  -- expressed in miliarcsec

-- Small dimension of the position error ellipse, assuming gaussian scatter
-- These are per model and per filter.
-- Name is a bit awkward, but we will eventually rename them 
-- (they will become part of covariance matrix)
-- posErr expressed in arcsec. Theta expressed in degrees
uPosErrAPS FLOAT
uPosErrBPS FLOAT
uPosErrThetaPS FLOAT

uPosErrASG FLOAT
uPosErrBSG FLOAT
uPosErrThetaSG FLOAT

-- One flux + error per model per filter
uFluxPS FLOAT, uFluxSigmaPS FLOAT
uFluxSG FLOAT, uFluxSigmaSG FLOAT

-- First and second moments. These are per model per filter, all FLOATs
uIxPS, uIyPS, uIxyPS, uIxxPS, uIyyPS
uIxSG, uIySG, uIxySG, uIxxSG, uIyySG

Notice that we are proposing to use “dec” instead of “decl”. “Dec” is strongly preferred by astronomers. We will always have model type attached to the “dec/decl”, so the issue with “dec” being a reserved word in some RDBMSes goes away.

The moments are defined as:

  • Ix = integral_over_footprint[w(x-x0,y-y0)*f(x,y)*(x-x0)]
  • Iy= integral_over_footprint[w(x-x0,y-y0)*f(x,y)*(y-y0)]
  • Ixy = integral_over_footprint[w(x-x0,y-y0)*f(x,y)*(x-x0)*(y-y0)]
  • Ixx= integral_over_footprint[w(x-x0,y-y0)*f(x,y)*(x-x0)*(x-x0)]
  • Iyy = integral_over_footprint[w(x-x0,y-y0)*f(x,y)*(y-y0)*(y-y0)]

Where:

  • x0,y0 is the object center
  • w(x,y) is a weighting function
  • f(x,y) is the flux

Elliptical equivalent to object footprint

The ellipse which gives the same weighted moments as the object

raFootprint FLOAT
decFootprint FLOAT
aFootprint FLOAT
bFootprint FLOAT
thetaFootprint FLOAT

Bounding box for object

The smallest box on the sky that fully encloses the object footprint. This is what you need for a postage stamp. These ranges are for the center given by (ra, decl). Need to decide for which model. Best?

raRange FLOAT
decRange FLOAT

Summary statistics for light curve variability

Still TBD. At least give variance around best fitting constant. Likely include Welch-Stetson statistics, or similar.

-- this is per filter, number of measurements in light curve
uNumObs INTEGER

-- Probability of variability in % (100% = variable object). Per filter
uVarProb TINYINT

-- Characteristic timescale of flux variations (measured in days). Per filter.
-- Tim: have we decided to keep it or to remove it???
uTimescale FLOAT

Summary statistics for image model residuals

Still TBD. Goal is to provide useful parameters that can be selected on to find interesting objects such as lensing arcs

[TBD]

Extendedness parameter

The probability that the object is a point source.

[TBD]

Photometric redshift and associated PDF

The reported photo-Z will be the peak of the PDF. The full PDF will be included.

There will be a single Level 2 photo_z information

This section will include info about redshift and info about probability distribution function

Redshift: ~20 columns. Same readshift for many objects, so normalize into separate Redshift table.

Probability distribution function: Not sure yet how to encode it, also not clear how many columns will be needed. Need to go back to the old email thread [Tim will try to find it]

What is currently in placeholder_photoz is not very useful

Segmentation info

Relationship to other Objects which are part of the same segmentation (deblending) tree

-- same as SDSS
parentId
nChild -- number of children

Summary statistics

earliestObsTime
latestObsTime

Flags ==

TBD how many flags and how to call them.

flag BIGINT

Spatial indexes

zoneId SMALLINT
chunkId SMALLINT
subZoneId SMALLINT
subChunkId SMALLINT

Columns to be removed

The following columns will be removed from the current (DC3b) schema:

  • raFlux, declFlux + errors (The flux weighted position for (ra, decl), calculated from the first moment of the pixel values within the footprint.)
  • apMag, petroMag (replaced with 1 magnitude per model)
  • refrRaAstrom, refrDeclAstrom, errors (astrometric refraction)
  • primaryPeriod, errors (summary period for all filters)
  • all scalegrams 25 x 6 filters (they are currently in placeholder_Object table
  • xFlux, yFlux, errors (The flux weighted position for (x,y), calculated from the first moment of the pixel values within the * footprint)
  • xPeak, yPeak ((x,y) position with the peak value in the footprint.)
  • raPeak, declPeak
  • raAstrom, declAstrom, errors
  • xAstrom, yAstrom, errors
  • cx, cy, cz, errors (this was introduced to speed up near neighbor queries)
  • uAmplitude
  • uPeriod

FaintSource

We need to store ellipsed for faint sources generated for moving objects, which argues we should split the FaintSource table into separate tables as shown below.

CREATE TABLE FaintSourceForObject (
  objectId BIGINT, -- point to Object table
  exposureId INT,  -- point to FPA_Exposure
  flux INT,
  skyBackground INT,
  PRIMARY KEY(objectId, exposureId)
) 
CREATE TABLE FaintDiaSourceForObject (
  objectId BIGINT, -- point to Object table
  exposureId INT,  -- point to FPA_DIA_Exposure
  flux INT,
  skyBackground INT,
  PRIMARY KEY(objectId, exposureId)
) 
CREATE TABLE FaintDiaSourceForMovingObject (
  movingObjectId BIGINT, -- point to MovingObject table
  exposureId INT,  -- point to FPA_Exposure
  flux INT,
  skyBackground INT,
  PRIMARY KEY(movingObjectId, exposureId)
) 
CREATE TABLE FaintDiaSourceForMovingObject (
  movingObjectId BIGINT, -- point to MovingObject
  exposureId INT,  -- point to FPA_DIA_Exposure
  flux INT,
  skyBackground INT,
  -- 20 bytes for model predicted ellipse, columns TBD
  predictedEllipseDummy1 FLOAT,
  predictedEllipseDummy2 FLOAT,
  predictedEllipseDummy3 FLOAT,
  predictedEllipseDummy4 FLOAT,
  predictedEllipseDummy5 FLOAT,
  PRIMARY KEY(movingObjectId, exposureId)
)

Source !!!DRAFT!!!

Identifiers and foreign keys

sourceId BIGINT       -- primary key
ampExposureId BIGINT  -- corresponding AmpExposure from which this measurement was made
filterId TINYINT      -- filter in which this measurement was made
objectId BIGINT       -- point to Object or MovingObject
procHistoryId INTEGER -- provenance tracking

Multi-fit model parameters and covariance matrices for each model

-- ra/dec and their errors are present in each model
-- expressed in degrees
raPS FLOAT
raSigmaPS FLOAT
decPS FLOAT
decSigmaPS FLOAT
raSG FLOAT
raSigmaSG FLOAT
decSG FLOAT
decSigmaSG FLOAT


-- Model ellipse convolved with PSF for this Exposure, per model per filter.
-- Small dimension of the position error ellipse, assuming gaussian scatter
-- Name is a bit awkward, but we will eventually rename them 
-- (they will become part of covariance matrix)
-- posErr expressed in arcsec. Theta expressed in degrees
uPosErrAPS FLOAT
uPosErrBPS FLOAT
uPosErrThetaPS FLOAT

uPosErrASG FLOAT
uPosErrBSG FLOAT
uPosErrThetaSG FLOAT

-- One flux + error per model per filter
uFluxPS FLOAT, uFluxSigmaPS FLOAT
uFluxSG FLOAT, uFluxSigmaSG FLOAT

-- Sky background, per model 
skyPS FLOAT
skySG FLOAT

Measured ellipse independent of Multifit model

?

Raw measured flux independent of Multifit model

apFlux INTEGER
apFluxSigma INTEGER
instFlux INTEGER
instFluxSigma INTEGER
atmCorrFlux INTEGER
atmCorrFluxSigma INTEGER

Photometric calibration correction

Specific to source location and SED, but not to shape

?

Residual characterization

?

Time related statistics

timeMidPoint DOUBLE NOT NULL
timeRange FLOAT(0) NULL

Flags

TBD how many flags and how to call them.

flag BIGINT

Spatial indexes

zoneId SMALLINT
chunkId SMALLINT
subZoneId SMALLINT
subChunkId SMALLINT

Columns that go away

  • xFlux, yFlux + errors
  • raFlux, declFlux + errors
  • xPeak, yPeak
  • raPeak, declPeak
  • xAstrom, yAstrom + errors
  • raAstrom, declAstrom + errors
  • psfFlux, modelFlux, petroFlux, nonGrayCorrFlux + errors
  • apDia
  • second moments
  • snr
  • chi2

DIASource