UW LSST Database

Introduction
Tables
Files

Introduction

We created a database to hold image metadata and object data. The intent of the database is to allow us to compare photometry and clustering algorithms, measuring accuracy and performance, and to help us choose, adapt or design photometry code for LSST.

The database is named "lsst". It is implemented in MySQL and runs on ostrogoth.astro.washington.edu.

Here is an overview of the primary tables in the database.

Image Table

Metadata about images. Presently we have some SDSS data ingested (two runs from stripe 82). We intend to eventually ingest some other LSST Precursor Data. WCS information is kept in a separate set of tables (one per image source).

Object Table

Information about astronomical "objects", including detections from photometry calculations and clusters of those detections. A cluster is simply a combination of one or more entries from the Object table; this many to many relation is maintained by the TieClusterToObject Table.

AlgRun Table

Information about a particular run of a component, including the parameters used for that run and (in some cases) a measure of CPU and memory usage. (Accurately measuring CPU and memory usage of code tends to slow down that code. Thus we anticipate that most of our runs will be made without such monitoring.)

Tables

AlgRun

AlgRunID	BIGINT	ID of this run of this algorithm
PipelineRunID	BIGINT	PipelineRunID for the pipeline that executed this run of this algorithm. Null if the algorithm is run independently of a pipeline.
AlgIndex	SMALLINT	Index of algorithm within the run of the pipeline, starting from 0
AlgName	VARCHAR(50)	Name of algorithm, one of Photo, DAOPhot, etc. To find the corresponding algorithm parameter table, append "Alg" to the name.
AlgVersion	VARCHAR(50)	Version of algorithm.
StartDate	DATETIME	Starting UTC date and time.
Comments	VARCHAR(255)	Comments about the algorithm run
CmdLine	VARCHAR(255)	Command line used to execute algorithm.
Params1	TEXT	Parameters used to execute algorithm (i.e. a copy of the params file).
Params2	TEXT	A second set of parameters used to execute algorithm, if any
Params3	TEXT	A third set of parameters used to execute algorithm, if any
ParamTable1	VARCHAR(50)	Name of parameter table, if any.
ParamTable2	VARCHAR(50)	Name of second parameter table, if any.
ParamTable3	VARCHAR(50)	Name of third parameter table, if any.
Computer	VARCHAR(255)	Name of computer(s) on which the algorithm was run. If more than one, comma-separated.
Processor	VARCHAR(255)	CPU info about the computer(s).
OS	VARCHAR(255)	Operating systems(s) on the computer(s).
NImages	BIGINT	Number of images processed by this algorithm. If images are both input and output, use input.
ImSize	FLOAT(0)	Size of image processed by this algorithm (Mb). If images are both input and output, use input. If more than one size of image is processed, specify the average.
NObjects	BIGINT	Number of objects (detections) input or output. If objects are input and output, then specify the number input.
ElapsedSec	FLOAT(0)	Elapsed clock time (seconds) to execute the algorithm.
IngestSec	FLOAT(0)	Time required to ingest the data (sec).
CPUCycles	FLOAT(0)	Number of cpu cycles used to execute the algorithm.
CPUCyclesErr	FLOAT(0)	Estimated std. dev. of CPUCycles
Memory	FLOAT(0)	Total memory used to process the data (Mb)
MemoryErr	FLOAT(0)	Uncertainty in measure of memory (Mb)
InList	MEDIUMTEXT	List of files input to the algorithm.
OutList	MEDIUMTEXT	List of files successfully processed by the algorithm (possibly with errors; see ErrList for that info).
ErrList	MEDIUMTEXT	List of files for which non-fatal errors occured, and the associated errors.
NoSuccess	MEDIUMTEXT	List of files for which fatal errors occurred. These files do not appear in OutList.
Log	MEDIUMTEXT	Log from photpipe''s running of this algorithm.
ProcList	MEDIUMTEXT	List of files processed with start date and elapsed time.

APO2_5mWCS

ImageID	BIGINT	Each Image has a WCS in this table which can be tracked using the ImageID.
Filter	CHAR(1)	One of u g r i z
Incl	DOUBLE	Inclination of scan
MJD	DOUBLE	TAI (in MJD seconds) of readout of row 0. Note that is a different time than TAI in the Image table.
Airmass	DOUBLE	Airmass at center of frame.

DaophotPSFModel

PSFData

MEDIUMTEXT

Contents of psf file generated by DAOPhot

DophotPSFModel

sigma_x	FLOAT(0)	PSF shape parameter.
sigma_y	FLOAT(0)	PSF shape parameter.
sigma_xy	FLOAT(0)	PSF shape parameter.

Image

ImageID	BIGINT	Unique identification number of the information from each Image header.
ImageName	VARCHAR(255)	The LSST formatted file name, as an absolute path.
Filter	VARCHAR(50)	Filter designation for the FitsImage.
Amp	TINYINT	The amplifier number for the SuperMACHO FitsImages.
CCD	TINYINT	The CCD number for the SuperMACHO FitsImages.
Field	VARCHAR(50)	Name of the field. The meaning depends on the data source. For SDSS this is the Frame. For SuperMACHO this is the Field.
Run	MEDIUMINT	Run number from SDSS images.
ReRun	MEDIUMINT	The rerun number for SDSS images.
CamCol	TINYINT	The camcol number for the SDSS FitsImages.
Airmass	FLOAT(0)	The airmass of the observation. If possible, this should be at the center of the field and at the middle of the exposure time, but this is not guaranteed.
TAI	DOUBLE	TAI (MJD) date of the observation (time at center of integration). For drift-scan data, this is the time for row 0. The TAI of row r is given by TAI + RowTime * r * days/sec. This works for all images because; RowTime is 0 for non-drift-scanned images.
UT1	DOUBLE	UT1 (MJD) date of observation (time at center of integration). For drift-scan data, this is the time for row 0. Null if unknown.
MJD	MEDIUMINT	MJD day (TAI or UTC) of the beginning of the night
ExpTime	DOUBLE	Exposure time of the observation (in TAI seconds).
RowTime	DOUBLE	The time between each row for drift-scanned data (in TAI seconds). 0 for non-drift-scanned data.
DataSource	VARCHAR(50)	The DataSource lists the survey name from which the test Images were obtained (SM: SuperMACHO, SDSS: Sloan Digital Sky Survey, W: Essence, etc.). This is related to the WCS tables for each survey.
RawImageName	VARCHAR(255)	Name of raw (uncalibrated) sky image.
MaskImageName	VARCHAR(255)	The name of the Mask Image.
VarianceImageName	VARCHAR(255)	Name of the Variance Image.
BiasImageName	VARCHAR(255)	Name of the final Bias Image used to correct the science Image.
DarkImageName	VARCHAR(255)	Name of the Dark Image used to correct the science Image.
FlatFieldName	VARCHAR(255)	Name of the final flat field Image used to correct the science Image.
IllumImageName	VARCHAR(255)	Name of the Illumination Correction Image used to correct the science Image.
FringeImageName	VARCHAR(255)	Name of the Fringe Image used to correct the science Image.
UnremappedImageName	VARCHAR(255)	Name of image from which this image was remapped
RemapType	TINYINT	Type of remap performed, one of: 0: none, 1: Swarp, 2: WCSRemap
WCSTable	VARCHAR(50)	Name of table containing WCS information for this image
HTMIDLL	BIGINT	HTM ID corresponding to the LL corner of the data portion of the original science image area. The HTM keeps track of the RA,DEC positions of these four corners to define the science Image area.
HTMIDLR	BIGINT	HTM ID corresponding to the LR corner of the data portion of the original science image area. The HTM keeps track of the RA,DEC positions of these four corners to define the science Image area.
HTMIDUL	BIGINT	HTM ID corresponding to the UL corner of the data portion of the original science image area. The HTM keeps track of the RA,DEC positions of these four corners to define the science Image area.
HTMIDUR	BIGINT	HTM ID corresponding to the UR corner of the data portion of the original science image area. The HTM keeps track of the RA,DEC positions of these four corners to define the science Image area.
BinX	MEDIUMINT	Bin factor along x
BinY	MEDIUMINT	Bin factor along y
SizeX	MEDIUMINT	Size of the image in the x-direction (along rows) (binned pixels). Ignores overscan but includes regions that may be considered outside of the data portion of the image.
SizeY	MEDIUMINT	Size of the image in the y-direction (along colums) (binned pixels). Ignores overscan but includes regions that may be considered outside of the data portion of the image.
Bias	FLOAT(0)	Bias of the calibrated image (in ADUs).
ReadNoise	FLOAT(0)	Read noise of the CCD (in e-).
Gain	FLOAT(0)	Inverse gain of the CCD (in e-/ADU).
Az	DOUBLE	Azimuth of observation (deg), preferably at center of exposure at center of image and including refraction correction, but none of this is guaranteed.
Alt	DOUBLE	Altitude of observation (deg), preferably at center of exposure at center of image and including refraction correction, but none of this is guaranteed.

MSO50inWCS

ImageID	BIGINT	Each Image has a WCS in this table which can be tracked using the ImageID.
RADESYS	VARCHAR(5)	RA/Dec coordinate system - either FK5 or ICRS.
Equinox	FLOAT(0)	Date of equinox of coordinate system (if not ICRS)
CTYPE1	VARCHAR(50)	WCS projection type for this axis.
CUNIT	VARCHAR(5)	Axis unit.
CRVAL1	DOUBLE	World coordinate on this axis.
CRPIX1	DOUBLE	Reference pixel on this axis.
CDELT1	DOUBLE	Pixel step along this axis.
CD1_1	DOUBLE	Linear projection matrix.
CD1_2	DOUBLE	Linear projection matrix.
CTYPE2	VARCHAR(50)	WCS projection type for this axis.
CUNIT2	VARCHAR(50)	Axis unit.
CRVAL2	DOUBLE	World coordinate on this axis.
CRPIX2	DOUBLE	Reference pixel on this axis.
CDELT2	DOUBLE	Pixel step along this axis.
CD2_1	DOUBLE	Linear projection matrix.
CD2_2	DOUBLE	Linear projection matrix.

NOAO4mWCS

ImageID	BIGINT	Each Image has a WCS in this table which can be tracked using the ImageID.
RADESYS	VARCHAR(5)	RA/Dec coordinate system - either FK5 or ICRS.
Equinox	FLOAT(0)	Date of equinox of coordinate system (if not ICRS)
CTYPE1	VARCHAR(50)	WCS projection type for this axis.
CUNIT	VARCHAR(5)	Axis unit.
CRVAL1	DOUBLE	World coordinate on this axis.
CRPIX1	DOUBLE	Reference pixel on this axis.
CDELT1	DOUBLE	Pixel step along this axis.
CD1_1	DOUBLE	Linear projection matrix.
CD1_2	DOUBLE	Linear projection matrix.
CTYPE2	VARCHAR(255)	WCS projection type for this axis.
CUNIT2	VARCHAR(255)	Axis unit.
CRVAL2	DOUBLE	World coordinate on this axis.
CRPIX2	DOUBLE	Reference pixel on this axis.
CDELT2	DOUBLE	Pixel step along this axis.
CD2_1	DOUBLE	Linear projection matrix.
CD2_2	DOUBLE	Linear projection matrix.
WAT0_001	VARCHAR(255)	Coordinate system

Object

ObjectID	BIGINT	ID number of Object.
AlgRunID	BIGINT	ID of algorithm run that generated this Detection. Null if not a Detection.
ImageID	BIGINT	ID of Image on which this Detection was found or this Missing Detection was not found. Null if not a Detection or a Missing Detection.
ObjName	VARCHAR(50)	Source-specific identifier for this object. If from a reference catalog, then the object name in that catalog. If from a file of photometry results, then any idenitifier that algorithm outputs, else the number of the object in the file (e.g. 1 through # of objects in file).
Category	TINYINT	The category of object. One of: -1: Missing source; 0: Reference object; 1: Source from an individual image; 2: Cluster of one or more child Objects.
Class	TINYINT	Classification: star or other unresolved object=0, galaxy or other resolved object=1, unknown=Null
OrigClass	VARCHAR(50)	Classification as output by the photometry algorithm. The meaning depends on the photometry algorithm.
HTMID	BIGINT	Hierarchical Triangular Mesh ID.
RA	DOUBLE	J2000 right ascension (deg)
Decl	DOUBLE	J2000 declination (deg). Note: named Decl because DEC is a reserved word in SQL.
UnitVecX	DOUBLE	X component of unit vector representation of RA, Dec
UnitVecY	DOUBLE	Y component of unit vector representation of RA, Dec
UnitVecZ	DOUBLE	Z component of unit vector representation of RA, Dec
CtrX	FLOAT(0)	Centroid in pixels in x (colc).
CtrXErr	FLOAT(0)	Uncertainty in Centroid X value.
CtrY	FLOAT(0)	Centroid, in pixels in y (rowc).
CtrYErr	FLOAT(0)	Unvertainty in centroid Y value.
PSFMag	FLOAT(0)	PSF flux.
PSFMagErr	FLOAT(0)	PSF flux error.
PetroMag	FLOAT(0)	Petrosian flux.
PetroMagErr	FLOAT(0)	Petrosian flux error.
ApDia	FLOAT(0)	Diameter of aperture for ApMag, in pixels.
ApMag	FLOAT(0)	Aperture magnitude.
ApMagErr	FLOAT(0)	Uncertainty in aperature magnitude.
ModelMag	FLOAT(0)	Model Flux.
ModelMagErr	FLOAT(0)	Model Flux Uncertainty.
Sky	FLOAT(0)	Sky flux.
SkyErr	FLOAT(0)	Uncertainty in Sky Flux.
Ixx	FLOAT(0)	Adaptive Second Moment 1.
IxxErr	FLOAT(0)	Uncertainty in adaptive second moment Ixx.
Iyy	FLOAT(0)	Adaptive Second Moment 2.
IyyErr	FLOAT(0)	Uncertainty in adaptive second moment Iyy.
Ixy	FLOAT(0)	Adaptive Second Moment 3.
IxyErr	FLOAT(0)	Uncertainty in adaptive second moment Ixy.
Flags1	BIGINT	Error flags.
Flags2	BIGINT	More error flags, if relevant.
PSFChiSq	FLOAT(0)	Likelyhood object is a star (0-1). SDSS calls this ''star_L''.
NumChildren	INTEGER	Number of children this Object has. Only relevant for a cluster (in which case it must not be null).
ReferenceCatalog	VARCHAR(50)	Name of the Reference Catalog from which this Object came. Null if not a Reference Object.

PhotoPSFModel

PSP_FIELD_UNKNOWN	INTEGER	this should never happen
PSP_FIELD_OK	INTEGER	everything OK
PSP_FIELD_PSF22	INTEGER	forced to take linear PSF across field
PSP_FIELD_PSF11	INTEGER	forced to take constant PSF across field
PSP_FIELD_NOPSF	INTEGER	forced to take default PSF
PSP_FIELD_ABORTED	INTEGER	aborted processing
PSP_FIELD_MISSING	INTEGER	missing (dummy) field
PSP_FIELD_OE_TRANSIENT	INTEGER	field with odd/even bias level transient
PSP_FIELD_STATUS_MASK	INTEGER	ask defining which bits are used for status values; higher bits are available to be set with extra information
PSP_FIELD_EXTENDED_KL	VARCHAR(50)	Window for KL stars was extended (HEX value).
PSP_FIELD_SPARSE	VARCHAR(50)	field is sparsely populated with KL stars (HEX value)
PSP_FIELD_MAX	VARCHAR(50)	Maximum possible bit value in a +ve int (HEX value).
field	INTEGER	Field sequence number within the run.
psp_status	INTEGER	he maximum value of "status" over all 5 filters. FILTER
sky	FLOAT(0)	The average sky value in the frame.
skySig	FLOAT(0)	Sigma of distribution of sky values.
skyErr	FLOAT(0)	The average sky value in the frame. (error)
skySlope	FLOAT(0)	The slope in the sky value along the columns.
lbias	FLOAT(0)	Left-hand bias level.
rbias	FLOAT(0)	Right-hand bias level.
psf_nstar	INTEGER	Number of stars used in psf measurement.
psf_ap_correctionErr	FLOAT(0)	Photometric error due to imperfect PSF model
psf_sigma1	FLOAT(0)	Inner gaussian sigma for the composite fit
psf_sigma2	FLOAT(0)	Outer gaussian sigma for the composite fit
psf_b	FLOAT(0)	Ratio of the inner PSF to the outer PSF at the
psf_p0	FLOAT(0)	The value of the power law at the origin.
psf_beta	FLOAT(0)	Slope of power law.
psf_sigmap	FLOAT(0)	Width parameter for power law.
psf_width	FLOAT(0)	Effective PSF width, based on 2-Gaussian fit
psf_psfCounts	FLOAT(0)	Flux via fit to the PSF.
psf_b_2G	FLOAT(0)	Ratio of gaussian 2 to gaussian 1 at origin
prof_nprof	INTEGER	Number of profile bins.
gain	FLOAT(0)	Gain averaged over all amplifiers.
dark_variance	VARCHAR(0)	Dark variance? (is this a number or character (y,n?))???

PipelineRun

PipelineRunID	BIGINT	Unique identification number associated with the pipeline configuration for the Run that produced the values for this Detection/Object/Object_Cluster.
PipelineName	VARCHAR(50)	Name of the pipeline used for this Run. This will likely be either PHOTPIPE (SM/W pipeline) or PHOTO (SDSS pipeline).
PipelineVersion	VARCHAR(50)	Version of the pipeline used in this Run.
StartDate	DATETIME	Start UTC date and time.
Comments	VARCHAR(255)	Notes, if any, about the pipeline run.
CmdLine	VARCHAR(255)	Command line that initiated the pipeline run.

TangentWCS

ImageID	BIGINT	Each Image has a WCS in this table which can be tracked using the ImageID.
RADESYS	VARCHAR(5)	RA/Dec coordinate system - either FK5 or ICRS.
Equinox	FLOAT(0)	Date of equinox of coordinate system (if not ICRS)
CTYPE1	VARCHAR(50)	WCS projection type for this axis.
CUNIT	VARCHAR(5)	Axis unit.
CRVAL1	DOUBLE	World coordinate on this axis.
CRPIX1	DOUBLE	Reference pixel on this axis.
CDELT1	DOUBLE	Pixel step along this axis.
CD1_1	DOUBLE	Linear projection matrix.
CD1_2	DOUBLE	Linear projection matrix.
CTYPE2	VARCHAR(255)	WCS projection type for this axis.
CUNIT2	VARCHAR(255)	Axis unit.
CRVAL2	DOUBLE	World coordinate on this axis.
CRPIX2	DOUBLE	Reference pixel on this axis.
CDELT2	DOUBLE	Pixel step along this axis.
CD2_1	DOUBLE	Linear projection matrix.
CD2_2	DOUBLE	Linear projection matrix.

TieClusterToObject

ChildID	BIGINT	ObjectID of child Object.
ClusterID	BIGINT	ObjectID of Cluster

TieImageToAlgRun

ImageID	BIGINT	Unique identification number of the information from each Image header.
AlgRunID	BIGINT	ID of pipeline Run.
WasInput	BOOL	Was this an input image? (true-input, false=output)
NumDetectionsFound	BIGINT	Number of Detections found on this image. This is duplicate info, but allows us to throw away Detections and still retain the info.
NumArtifactsRejected	BIGINT	The number of artifacts rejected on this run. Null if this info is not available from the Object Detection Algorithm.
ElapsedSec	FLOAT(0)	Elapsed clock time (seconds) to execute the algorithm on this image.
PSFModelTable	VARCHAR(50)	Name of table that contains the PSF model for this particular photometry run of this particular image. Null if irrelevant (e.g. not a photometry run or an algorithm that has no psf model).

Files

The following files are available for viewing or download:

DataModel.SQL the SQL code used to generate the database. Warning: due to a bug in Enterprise Architect the drop tables are in the wrong order. A corrected copy can be obtained by running the python script cleanModel.py.
makeHTML.py the python script used to generate this HTML document from DataModel.SQL.
DatabaseModel.EAP. The data model, an Enterprise Architect file.
databaseDataModel.png an image of the data model diagram from EA.