SkyServer Data Organization

The SDSS Catalog Data Model

The SDSS catalog data is stored in a commercial relational database management system (DBMS) - Microsoft's SQL Server. The data is therefore organized in several 2-dimensional tables. The tables and their relationships to each other are referred to as the schema in database jargon.

Diagrammatic view of the DR7 schema

There are 3 different types of data in the tables - imaging data is in the photo group of tables, spectroscopic and tiling data is in the spectro tables, and other data such as documentation or other information about the photo and spectro data, i.e. metadata, is in the meta tables. Some tables are also created specifically for speef or convenience, for example the SpecPhotoAll table, which contains a pre-computed join of relevant fields in the PhotoObjAll and SpecObjAll tables.

The important tables are described below, along with the views that are currently defined on each table. A view is a subset of the corresponding table that can be used instead of the table - in other words it is a virtual table. A view is usually faster than using the base table, since it only loads a subset of the objects, but more importantly, the views we have defined on the tables select only the objects that are important for science, and they filter out non-science objects such as sky, QA or defective observations. As such, even though we list the base tables for completeness below, in the vast majority of the cases, you should use the views defined on the tables instead of the tables themselves, e.g. use the PhotoObj and SpecObj views for science instead of the PhotoObjAll and SpecObjAll tables.

BESTDR7, TARGDR7 and other databases

There are two main datasets in the SDSS catalog archive server - the BEST and TARGET datasets that are contained in separate databases. Each contains different reprocessings of the same raw data:

the BEST dataset is contained in the BESTDR7 database, and represents the latest, greatest calibration of the raw data. BESTDR7 is the default database for queries. Other databases must be explicitly specified (see below for sample syntax).
The TARGET dataset is contained in the TARGDR7 database, and represents a snapshot of the data as it was when the target selection algorithm was run on it, i.e. it is the calibration upon which the spectroscopic targets were chosen. Preserving this data version is important for doing science with the data.

Both databases have the identical schema (tables), but different data. The BESTDR7 database also contains the spectroscopic and tiling data, whereas the TARGDR7 database only contains imaging data. The vast majority of queries are run on the BESTDR7 database.

To choose a database other than the default BESTDR7 in your query, you must specify it as <dbname>..<tablename>, e.g., TARGDR7..PhotoObj:

	SELECT TOP 10 objid,ra,dec FROM TARGDR7..PhotoObj

Please see the SQL Intro page for further help with SQL queries.

The Hierarchical Triangular Mesh (HTM)

We have build a spatial indexing scheme called the Hierarchical Triangular Mesh (HTM) that spatially decomposes the region of the sky that is covered by the SDSS data and enables much faster spatial searches of the data by coordinate cuts.

Database Indices

In addition to the HTM, which is an overall indexing scheme for multidimensional spatial data, the DBMS itself has the capability to define indices for fast searches on each table. We have defined indices on all the major tables.

An index is a tree representation of a subset of the columns in a table that enables much faster searches of the table (compared to sequential scans of the table data) when constraints involving those columns are included in the query. All tables have an index on their primary key (unique row identifier), but the larger tables have indexes in addition to the primary key index. In all there are 3 kinds of indices:

Primary Key Index - index on the unique primary key of a table.
Foreign Key Index - index on a foreignkey of a table, i.e. a column that is a primary key of another table.
Covering Index - an index that covers one or more columns of a table. This is a combined index on those fields, so it speeds up searches if any of those fields are included in the WHERE clause.

Click here to view a table of all the current indices defined on the data.

Imaging (Photo) Data Tables

The important tables and views are described below. For each table, the views and indices are defined on that table are described in brief. For more information, please see the Table Descriptions help page as well as the links to the Glossary entries that are incorporated in the table listing below.

PhotoObjAll - By far the largest table in the database, PhotoObjAll contains the 100+ parameters for each imaging (photo) object. For most of these parameters, there are actually 5 rows each, one for each wavelength band. This table includes data on all photo objects, not just science objects, hence the name PhotoObjAll. The view of this table that includes only science objects and excludes sky and other unknown objects is the PhotoObj view. The PhotoObjAll table is there for completeness, but science queries are usually done on the PhotoObj view.
PhotoObjAll Views:

View Name	Contents	Description
PhotoAuxAll	View for PhotoAuxAll for backward compatibility with DR5.	It selects the required columns from PhotoObjAll.
PhotoAuxAll	View for PhotoAuxAll for backward compatibility with DR5.	It selects the required columns from PhotoObjAll.
PhotoAuxAll	View for PhotoAuxAll for backward compatibility with DR5.	It selects the required columns from PhotoObjAll.
PhotoAuxAll	View for PhotoAuxAll for backward compatibility with DR5.	It selects the required columns from PhotoObjAll.
PhotoAuxAll	View for PhotoAuxAll for backward compatibility with DR5.	It selects the required columns from PhotoObjAll.
PhotoAuxAll	View for PhotoAuxAll for backward compatibility with DR5.	It selects the required columns from PhotoObjAll.
PhotoAuxAll	View for PhotoAuxAll for backward compatibility with DR5.	It selects the required columns from PhotoObjAll.
PhotoFamily	These are in PhotoObj, but neither PhotoPrimary or Photosecondary.	These objects are generated if they are neither primary nor secondary survey objects but a composite object that has been deblended or the part of an object that has been deblended wrongfully (like the spiral arms of a galaxy). These objects are kept to track how the deblender is working. It inherits all members of the PhotoObj class.
PhotoObj	All primary and secondary objects in the PhotoObjAll table, which contains all the attributes of each photometric (image) object.	It selects PhotoObj with mode=1 or 2.
PhotoPrimary	These objects are the primary survey objects.	Each physical object on the sky has only one primary object associated with it. Upon subsequent observations secondary objects are generated. Since the survey stripes overlap, there will be secondary objects for over 10% of all primary objects, and in the southern stripes there will be a multitude of secondary objects for each primary (i.e. reobservations).
PhotoSecondary	Secondary objects are reobservations of the same primary object.

PhotoObjAll Indices:

Index Type Key or Field List

primary key objID

foreign key Field(fieldID)

covering index mode, cy, cx, cz, htmID, type, flags, status, ra, dec, u, g, r, i, z, rho

covering index htmID, cx, cy, cz, type, mode, flags, status, ra, dec, u, g, r, i, z, rho

covering index htmID, run, camcol, field, rerun, type, mode, flags, status, cx, cy, cz, g, r, rho

covering index field, run, rerun, camcol, type, mode, flags, rowc, colc, ra, dec, u, g, r, i, z

covering index fieldID, objID, ra, dec, r, type, status, flags

covering index SpecObjID, cx, cy, cz, mode, type, flags, status, ra, dec, u, g, r, i, z, rho

covering index cx, cy, cz, htmID, mode, type, flags, status, ra, dec, u, g, r, i, z, rho

covering index run, mode, type, status, flags, u, g, r, i, z, Err_u, Err_g, Err_r, Err_i, Err_z

covering index run, camcol, rerun, type, mode, status, flags, ra, dec, fieldID, field, u, g, r, i, z

covering index run, camcol, field, mode, parentID, q_r, q_g, u_r, u_g, isoA_r, isoB_r, fiberMag_u, fiberMag_g, fiberMag_r, fiberMag_i, fiberMag_z

covering index run, camcol, type, mode, cx, cy, cz

covering index ra, [dec], type, mode, flags, u, g, r, i, z, psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z, primTarget

PhotoTag - This is a vertical partition of the PhotoObjAll table, and contains only those columns that are most often requested. Due to the smaller size of each row in the table, many more rows can be loaded into the memory cache at one time, hence searches on the PhotoTag table are much faster than searches on PhotoObjAll. Whenever possible, use the PhotoTag table instead of PhotoObjAll or PhotoObj.

PhotoTag Indices:

Index Type Key or Field List

primary key objID

foreign key PhotoObjAll(objID)

foreign key Field(fieldID)

covering index mode, cy, cx, cz, htmID, type, flags, status, ra, [dec], modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z, [size]

covering index htmID, cx, cy, cz, type, mode, flags, status, ra, [dec], modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z, [size]

covering index htmID, run, camcol, field, rerun, type, mode, flags, status, cx, cy, cz, modelMag_g, modelMag_r, probPSF

covering index field, run, rerun, camcol, type, mode, flags, ra, [dec], modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z

covering index fieldID, objID, ra, [dec], modelMag_r, type, status, flags

covering index SpecObjID, cx, cy, cz, mode, type, flags, status, ra, [dec], modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z, [size]

covering index cx, cy, cz, htmID, mode, type, flags, status, ra, [dec], modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z, [size]

covering index run, mode, type, status, flags, modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z, modelMagErr_u, modelMagErr_g, modelMagErr_r, modelMagErr_i, modelMagErr_z

covering index run, camcol, field, rerun, type, mode, status, flags, ra, [dec], fieldID, modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z

covering index ra, [dec], type, mode, flags, modelMag_u, modelMag_g, modelMag_r, modelMag_i, modelMag_z, psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z, primTarget

Segment - This table contains the basic parameters associated with a Segment, which is a unit of data corresponding to a single camera column within a chunk.

Segment Indices:

Index Type Key or Field List

primary key segmentID

foreign key Chunk(chunkId)

foreign key StripeDefs(stripe)

Field - This table contains all the measured parameters of each imaging field, along with relevant summary statistics, and astrometric and photometric information.

Field Indices:

Index Type Key or Field List

primary key fieldID

foreign key Segment(segmentID)

covering index field, camcol, run, rerun

covering index run, camcol, field, rerun

Chunk - A chunk is a contiguous section of imaging data within a stave (stripe for the south). It consists of complete North and South strips between lower and upper mu limits. A chunk is composed of a set of touching but non-overlapping primary segments (or parts of segments).

Chunk Indices:

Index Type Key or Field List

primary key chunkID

foreign key StripeDefs(stripe)

PhotoProfile - This table contains the light profiles of SDSS photo objects.

PhotoProfile Indices:

Index Type Key or Field List

primary key objID, bin, band

foreign key PhotoObjAll(objID)

FieldProfile - This table contains the light profiles of SDSS field objects.

FieldProfile Indices:

Index Type Key or Field List

primary key fieldID, bin, band

foreign key Field(fieldID)

Neighbors - SDSS objects within 0.5 arcmins and their match parameters are stored here. Make sure to filter out unwanted PhotoObj, like secondaries.

Neighbors Indices:

Index Type Key or Field List

primary key objID, NeighborObjID

foreign key PhotoObjAll(objID)

Match - This table contains PhotoObj (primary and secondary object) pairs from different runs (observations) that probably are the same object. In effect, this table records multiple observations of each object.

Match Indices:

Index Type Key or Field List

primary key objID1, objID2

foreign key PhotoObjAll(objID)

foreign key MatchHead(objid)

covering index matchHead

MatchHead - Thhis table records the canonical object from each match cluster, and cluster statistics. The observations for an object (as recorded in the Match table) form a cluster named by the minimum objID in the cluster. MatchHead has summary information about the cluster keyed by the objID.

MatchHead Indices:

Index Type Key or Field List

primary key objID

foreign key PhotoObjAll(objID)

First, Rosat and Usno - These tables contain matches between the FIRST, ROSAT and USNO survey objects and SDSS.

Spectro/Tiling Data Tables

PlateX - This table contains data as exported (the X is for exported) from a given plate used for spectroscopic observations. Each plate has 640 observed spectra and hence 640 corresponding entries in SpecObjAll.
PlateX Indices:

Index Type Key or Field List

primary key plateID

foreign key TileAll(tile)

covering index htmID, ra, dec, cx, cy, cz

SpecObjAll - This is a base table containing ALL the spectroscopic information, including a lot of duplicate and bad data. Use the SpecObj view instead (see below), which has the data properly filtered for cleanliness.

SpecObjAll Views:

View Name Contents Description

SpecObj A view of Spectro objects that just has the clean spectra. The view excludes QA and Sky and duplicates. Use this as the main way to access the spectro objects.

SpecObjAll Indices:

Index Type Key or Field List

primary key specObjID

foreign key PlateX(plateID)

covering index htmID, ra, dec, cx, cy, cz, sciencePrimary

covering index BestObjID, objType, objTypeName, sciencePrimary, specClass, htmID, ra, dec, fiberMag_u, fiberMag_g, fiberMag_r, fiberMag_i, fiberMag_z

covering index specClass, zStatus, zWarning, z, sciencePrimary, primTarget, secTarget, plateId, bestObjID, targetObjId, htmID, ra, dec

covering index targetObjID, objType, objTypeName, sciencePrimary, specClass, htmID, ra, dec, fiberMag_u, fiberMag_g, fiberMag_r, fiberMag_i, fiberMag_z

covering index ra, [dec], specClass, plate, tile, z, zErr, zConf, fiberMag_r, primTarget, sciencePrimary, plateID, bestObjID

SpecLineAll - A compendium of all the spectral lines found in all the spectroscopic objects in the SpecObjAll table. Contains all the parameters measured for each spectral line. There is a SpecLine view of this table that contains only the lines that have been measured.

SpecLineAll Views:

View Name Contents Description

SpecLine A view of SpecLines objects that have been measured The view excludes those SpecLine objects which have category=1, thus they have not been measured. This is the view you should use to access the SpecLine data.

SpecLineAll Indices:

Index Type Key or Field List

primary key specLineID

foreign key SpecObjAll(specObjID)

covering unique index specobjID, specLineID

SpecLineIndex - Precomputed spectral line indices. These are combinations of spectral line intensities used to determine various properties of galaxies, like age or metallicity.
SpecLineIndex Views:
None found
SpecLineIndex Indices:

Index Type Key or Field List

primary key specLineIndexID

foreign key SpecObjAll(specObjID)

covering unique index specobjID, speclineindexID

TileAll - Contains information about individual tiles on the sky.
TileAll Views:

View Name Contents Description

Tile A view of TileAll that have untiled=0 The view excludes those Tiles that have been untiled.

TileAll Indices:

Index Type Key or Field List

primary key tile

foreign key TilingRun(tileRun)

covering unique index tileRun, tile

covering index htmID, racen, deccen, cx, cy, cz

TiledTargetAll - This table stores information that keeps track of why a Target was assigned to a Tile.

TiledTargetAll Views:

View Name Contents Description

TiledTarget A view of TiledTargetAll objects that have untiled = 0 The view excludes those TiledTarget objects that have been untiled.

TiledTargetAll Indices:

Index Type Key or Field List

primary key targetID, tile

foreign key TileAll(tile)

covering unique index tile, targetID

covering index htmID, ra, dec, cx, cy, cz, objtype, untiled

covering unique index objType, targetID, tile

TilingGeometry - This table contains geometrical information about tiling regions, including tiling boundaries. The TileBoundary view serves up the boundaries.

TilingGeometry Views:

View Name Contents Description

TilingBoundary A view of TilingGeometry objects that have isMask = 0 The view excludes those TilingGeometry objects that have isMask = 1. See also TilingMask.

TilingMask A view of TilingGeometry objects that have isMask = 1 The view excludes those TilingGeometry objects that have isMask = 0. See also TilingBoundary.

TilingGeometry Indices:

Index Type Key or Field List

primary key tilingGeometryID

foreign key TilingRun(tileRun)

foreign key StripeDefs(stripe)

TilingRun - Contains basic information about each run of the tiling software.

TilingInfo - Contains information on what happened to a Target in a run of tiling software.

Metadata and Other Tables

DBObjects - An auto-generated documentation table that contains a description of every object (table, view, function, stored procedure) in the database.

DBViewCols - An auto-generated table containing a description of every column in every table in the database.

DBColumns - An auto-generated table containing a description of every column in every view in the database.

SpecPhotoAll - The combined spectro and photo parameters of an object in SpecObjAll. This is a precomputed join between the PhotoObjAll and SpecObjAll tables. The photo attibutes included cover about the same as PhotoTag. The table also includes certain attributes from the Tile table.

SpecPhotoAll Views:

View Name Contents Description

SpecPhoto A view of joined Spectro and Photo objects that have the clean spectra. The view includes only those pairs where the SpecObj is a sciencePrimary, and the BEST PhotoObj is a PRIMARY (mode=1).

SpecPhotoAll Indices:

Index Type Key or Field List

primary key specObjID

foreign key SpecObjAll(specObjID)

covering index objID, sciencePrimary, specClass, z, targetObjid, targetId

covering index targetObjID, sciencePrimary, specClass, z, objid, targetId

covering index targetID, sciencePrimary, specClass, z, objid, targetObjId

DR7 Help



Site News
Introduction
Cooking with Sloan
FAQ

Search Form Guide
SQL Tutorial
SQL in SkyServer
Sample SQL Queries
Graphing
Query Limits
Searching Advice

Archive Intro
Table Descriptions
Schema Browser
Glossary
Algorithms
Web Browsers

Download
Data Publications
API
SkyServer Sites

Contact Help Desk

Index Type	Key or Field List
primary key	objID
foreign key	Field(fieldID)
covering index	mode, cy, cx, cz, htmID, type, flags, status, ra, dec, u, g, r, i, z, rho
covering index	htmID, cx, cy, cz, type, mode, flags, status, ra, dec, u, g, r, i, z, rho
covering index	htmID, run, camcol, field, rerun, type, mode, flags, status, cx, cy, cz, g, r, rho
covering index	field, run, rerun, camcol, type, mode, flags, rowc, colc, ra, dec, u, g, r, i, z
covering index	fieldID, objID, ra, dec, r, type, status, flags
covering index	SpecObjID, cx, cy, cz, mode, type, flags, status, ra, dec, u, g, r, i, z, rho
covering index	cx, cy, cz, htmID, mode, type, flags, status, ra, dec, u, g, r, i, z, rho
covering index	run, mode, type, status, flags, u, g, r, i, z, Err_u, Err_g, Err_r, Err_i, Err_z
covering index	run, camcol, rerun, type, mode, status, flags, ra, dec, fieldID, field, u, g, r, i, z
covering index	run, camcol, field, mode, parentID, q_r, q_g, u_r, u_g, isoA_r, isoB_r, fiberMag_u, fiberMag_g, fiberMag_r, fiberMag_i, fiberMag_z
covering index	run, camcol, type, mode, cx, cy, cz
covering index	ra, [dec], type, mode, flags, u, g, r, i, z, psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z, primTarget