SkyServer Sample SQL Queries

This page contains a sample queries designed to serve as templates for writing your own SQL queries. The first section, Basic SQL, serves as an introduction to the syntax of the SQL database access language. The sections that follow feature queries written to solve real scientific problems submitted by astronomers. Those queries are divided by scientific topic.

Click on the name of the query in the list below to go directly to that sample query. You can load the query into SkyServer's SQL Query tool by clicking on the button above each query. You can then modify the query to suit your needs. Alternatively, you can send each query to the database and see the results by clicking on the button.

NOTE: Please read the query hints below before you try any queries, especially if you are new to SQL or the SkyServer.

Basic SQL:
Basic SELECT-FROM-WHERE
Basic position search
Using PhotoTag
Search for a Range of Values
Rectangular position search
More than one table: JOIN...ON
Photometry & spectroscopy
Counting by type or category
Using flags

Stars:
Clean photometry - Stars
CVs using colors
Binary stars colors
Using sppLines table
Using sppParams table
Stars multiply measured
Proper motions

General Astronomy:
Only stars or galaxies
Clean photometry
Using Field MJD
Objects by spectral lines
Spectra by classification
Moving asteroids
Plates with repeat spectra
Galaxies blended with stars
Counts by type and program

Galaxies:
Clean photometry - Galaxies
Galaxies with blue centers
Diameter limited sample
LRG sample selection
Classifications from Galaxy Zoo
BOSS target selection

SQL Jujitsu and Misc.:
Data subsample
Objects in close pairs
Selected neighbors in run
Object counts and logic
Galaxy counts on HTM grid
Repeated high-z objects
Splitting 64-bit values
Using LEFT OUTER JOIN
Using Nested Queries
Checking SDSS footprint

Quasars and Photo-z's:
QSOs by spectroscopy
QSOs by colors
FIRST matches for quasars
Photometric Redshifts

Some hints on searching SkyServer:

For an introduction to SQL, see the interactive SQL tutorial. For more information on how to use SQL with the SkyServer database, see SQL in SkyServer.
Please read the Optimzing Queries section in the SQL in SkyServer help page as well as the Query Limits page (to see the timeouts and row limits on queries) before you attempt any complex queries of your own.
If you're not sure how many objects a query is going to return, it's always a good idea to first do a "count" query, e.g. "SELECT count(*) FROM Galaxy WHERE ..." so as to get an idea of how many objects will be returned, so you don't find yourself waiting a lot longer than you expected to.
If even a count takes a long time, this is a good indication that the actual query will take a much longer time to run, so you should check if you have formulated the query correctly and in the most efficient way. Some advice on how to write efficient queries can be found in the Optimzing Queries section of the SQL in SkyServer.
If you have optimized your query and it still takes much longer to run than you think it should, try again at a different time. Sometimes when many queries are being run simultaneously, the servers can take a longer time to return. This in turn can sometimes result in queries timing out, even though they would run perfectly well on the server when the server load is less.
A good way to find if there are any objects that meet the search criteria in a given query is to use the "TOP <n>" SQL construct. For example, "SELECT TOP 10 FROM ..." will only return the first 10 objects that meet your criteria. Note that this is NOT a deterministic ordering; the "TOP 10" objects may be different if you run the same query again.
If your query returns a lot of output (more than a few thousand objects), it is generally not a good idea to select the HTML output format (which is selected by default in the SQL Search tool). Instead, try selecting CSV (comma-separated value) output. However, for all queries that return many objects, you are much better off using the CasJobs batch query service or one of the command-line query interfaces (sqlcl or the emacs query interface).
Be sure to exclude invalid values (unset or uncalculated quantities), as described on the SQL in SkyServer page under Excluding Invalid Data Values.
SkyServer returns all values with a default precision. For some applications (such as doing high-precision astrometry with RA and Dec values), the default precision is not enough. For these applications, use the STR(column,n,d) SQL construct (where n is the total number of digits and d is the number of decimal places) to set the precision of the column that your query requests. The SkyServer returns values with a default precision that is set for each data type, and this may not be enough for columns like ra, dec etc. See the Selected neighbors in run sample query below for examples of how to use STR.

Basic SELECT-FROM-WHERE

-- This is the "Hello world" example of how to search for data in DR8. -- This query shows the basic structure of a SQL query: -- SELECT [variables] FROM [table] WHERE [constraints] -- Although many of your SQL queries will be more complex, -- they will all follow this same basic structure. -- This sample query finds unique objects in an RA/Dec box. -- For a more efficient way to find objects by position, see the next query, -- Searching around a sky position.
SELECT TOP 100
objID, ra ,dec	-- Get the unique object ID and coordinates
FROM
PhotoPrimary	-- From the table containing photometric data for unique objects
WHERE
ra > 185 and ra < 185.1
AND dec > 15 and dec < 15.1	-- that matches our criteria

Basic position search

-- Find galaxies within 1' of a given point (ra=185.0, dec=-0.5). -- This is a slightly more complex query, but it can be easily adapted to search -- around any point in the sky. -- To see how to limit the search only to objects with clean photometry, see the -- Clean imaging query.
SELECT TOP 100 G.objID, GN.distance
FROM Galaxy as G
JOIN dbo.fGetNearbyObjEq(185.,-0.5, 1) AS GN
ON G.objID = GN.objID
ORDER BY distance

Using the PhotoTag table

-- This query introduces the PhotoTag table, which contains the most frequently used columns -- of PhotoObj. Queries to PhotoTag will run more quickly than those to photoObj. -- This sample query finds data for all objects in fields with desired PSF width.
SELECT TOP 100
r.run,
r.rerun,
f.camCol,
f.field,
p.objID,
p.ra,
p.dec,
p.modelMag_r,
f.psfWidth_r
FROM
PhotoTag AS p
JOIN Field AS f ON f.fieldid = p.fieldid
JOIN Run AS r ON f.run = r.run
WHERE mode=1 -- select primary objects only
and f.psfWidth_r > 1.2
and p.modelMag_r < 21.
and r.stripe = 21

Search for a Range of Values: BETWEEN

-- The BETWEEN statement can be used to set constraints on a range of values. -- This sample query finds galaxies with g magnitudes between 18 and 19.
SELECT TOP 10 objID, cModelMag_g
FROM Galaxy
WHERE
cModelMag_g between 18 and 19	-- 18 < cModelMag_g < 19

Rectangular position search

-- There are several built-in functions available to CAS users that make spatial
-- queries, i.e., those with coordinate cuts, much more efficient than simply
-- including the coordinate constraints in the WHERE clause. This example
-- illustrates the use of the dbo.fGetObjFromRectEq function that invokes
-- the Hierarchical Triangular Mesh (HTM) functionality.

-- a) Rectangular search using straight coordinate constraints:
SELECT objID, ra, dec
FROM PhotoObj
WHERE (ra between 179.5 and 182.3) and (dec between -1.0 and 1.8)

-- b) This query can be rewritten as follows to use the HTM function that returns a
-- rectangular search area (The "Run this Query" button above will run this query):

SELECT p.objID, p.ra, p.dec
FROM PhotoObj p

JOIN dbo.fGetObjFromRectEq(179.5, -1.0, 182.3, 1.8) r ON p.objID = r.objID

Searching more than one table: JOIN...ON

-- When you need to search for data in two or more tables, you must "join" the tables -- together for the purposes of your query by using a JOIN...ON statement. -- JOIN...ON allows you to search for data and/or constraints in both tables. -- The syntax of the statement is: -- JOIN [the second table] ON [a variable the tables have in common]. -- The variable the tables have in common is called the "key" (think of it -- as the key that unlocks your ability to search two tables). -- The key variable(s) in each table are shown on the About the Database page. -- Find the two tables you want to join and look for a key variable they have in common. -- The sample query looks for spectra of quasars and shows the date and time at which -- each spectrum was taken.
SELECT TOP 100
sp.objID,
sp.ra,
sp.dec,
sp.mjd,
px.taiBegin,
px.taiEnd,
sp.fiberID,
sp.z
FROM specPhoto AS sp
JOIN plateX AS px
ON sp.plateID = px.plateID
WHERE
(sp.class='QSO')
AND sp.z > 3

Photometry and Spectroscopy: SpecPhoto

-- This query introduces the SpecPhoto view, which is a pre-computed join
-- of the most commonly-searched fields in both the SpecObj view (which
-- contains spectroscopy data) and the PhotoObj view (which
-- contains photometry data). This means that to combine
-- photometry and spectroscopy tables yourself using JOIN...ON,
-- the join already has been "precomputed" and done for you.

-- You should use specPhoto whenever your variables of interest
-- can be found there; using specPhoto means that your queries will
-- return results much faster than using JOIN...ON.
-- that your queries will return results much faster than using JOIN...ON.

-- This sample query retrieves both magnitudes (from photometry) and
-- redshifts (from spectroscopy) of 100 quasars. Note that this query
-- also introduces the use of mathematical operators (in this case subtraction)
-- in queries.

SELECT top 100

objid, ra, dec, psfmag_i-extinction_i AS mag_i,

psfmag_r-extinction_r AS mag_r, z -- In SpecPhoto, "z" is the redshift
FROM SpecPhoto
WHERE

(class = 'QSO')

Counting objects by type or category

-- This simple query introduces two important SQL structures while showing how to count the number of
-- spectra of each spectral classification (galaxy, quasar, star) in DR8.

-- When included in the SELECT block, the count(*) statement returns the number of objects that
-- meet your search criteria.
-- The GROUP BY statement sorts results into groups (categories) based on the value of a data column.

-- In this query, the grouping occurs on the 'class' column of the SpecObj view, which contains
-- the spectral classification of the object.
-- The query can be easily modified to find object counts sorted by other columns.

-- For another example, see the Counts by Type and Program sample query.

SELECT class, count(*)
FROM SpecObj
GROUP BY class

Using flags

-- Another useful query is to select stars that are not saturated. -- This query introduces bitwise logic for flags, and uses the 'as' syntax to -- make the query more readable. Note that if a flag is not set, the value -- will be zero. If you want to ensure multiple flags are not set, you can -- either check that each individually is zero, or their sum is zero. -- (From Gordon Richards) -- Important caveat: The 'flags' column is the union of the flag values in -- each band,and using it may produce unexpected results, for example if a -- bad flag bit is set in only one band (e.g. in the 'flags_r' value), that bit -- will be bad in the overall 'flags' value as well. For more information, -- please see the 'Photometric flags detail' entry in Algorithms. -- NOTE: This query takes a long time to run without the "TOP 1000".
SELECT TOP 1000
objID,
ra,
dec,
psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z,
dbo.fPhotoFlagsN(flags)
FROM Galaxy
WHERE
(flags & (dbo.fPhotoFlags('SATURATED'))) != 0

Photometric data only for stars or galaxies

-- SkyServer includes views called Star and Galaxy, which -- contain photometric data (but no spectral data such as -- redshift) for stars and galaxies respectively. -- This sample query shows how you can search for data -- for galaxies using the Galaxy view. Searching for stars -- using the Star view would work the same, but with -- "Star" in the FROM clause. -- This sample query finds 1000 galaxies -- brighter thanr-magnitude 22, extinction-corrected. -- You could remove the "TOP 1000" to find all galaxies brighter -- than r=22, but it would take a long time to run and might -- time out.
SELECT TOP 1000 objID
FROM Galaxy
WHERE
(r - extinction_r) < 22	-- extinction-corrected r magnitude

Clean photometry: the CLEAN flag

-- One of the most important requirements for selecting data
-- useful for research is to select only data that has passed
-- a certain standard of clean photometry. Starting with DR8,
-- the SDSS-III has made this easier by providing a flag called CLEAN
-- that indicates that an object's photometry has passed all tests
-- to bedeclared useful for science. A value of CLEAN=1 (true)
-- indicates that that object's photometry has been vetted as clean.
-- For more on using flags, see the Using Flags Sample Query.

-- In some contexts, it is important to know what conditions
-- are required for theimaging pipeline to set the CLEAN flag,
-- so that you can adjust these requirements to suit your needs.
-- The CLEAN flag works differently for stars and galaxies. For stars,
-- see the Clean photometry - stars sample query. For galaxies,
-- see the Clean photometry - galaxies sample query.

SELECT top 10 objID, ra, dec, g, clean
FROM PhotoObj
WHERE CLEAN=1

Using Field MJD (Modified Julian Date)

-- This sample query allows you to find the MJD (astronomical date)
-- on which a specific SDSS-III field was observed. (This query uses
-- the r-band, but you can specify other magnitude bands.
-- It also returns only fields observed after MJD 53140 (May 15, 2004).
-- There is an online calculator for converting between MJDs and
-- calendar dates.

-- This query uses the Field table, which contains data on SDSS fields.
-- It is also possible to search for the observation date of a specific
-- object by joining the Field table with the PhotoObj table or its
-- associated views.

SELECT top 100

run, rerun, camcol, field,

mjd_r as primary_mjd
FROM Field
WHERE

mjd_r > 53140

Finding objects by their spectral lines

-- DR8 handles searches by spectral lines differently than previous
-- releases. In addition, spectral lines for galaxies and stars are
-- identified through different processes.

-- Spectral lines for galaxies are calculated using the MPA-JHU
-- spectroscopic reanalysis (Tremonti et al. 2004; Brinchmann et al. 2004)
-- and are stored in the galSpecLine table. For more on how spectral lines
-- of galaxies are found, see the Galspec page of the sdss3.org website.
-- Spectral lines for stars are calculated using the SEGUE Stellar Parameter
-- Pipeline (SSPP; Lee et al. 2008) and are stored in the sppLines
-- table. For more on how spectral lines of stars are found, see
-- the SSPP page of the sdss3.org website.

-- The "Run this Query" button will run the second query, which finds stars
-- by searching CaII lines.

-- a) Finding galaxies by their emission lines:
-- This query selects galaxy spectra with high internal reddening,
-- as measured by the standard Balmer decrement technique. It
-- makes use of the galSpec tables for the measurements of
-- galaxy lines. In this case we use galSpecLine, which has
-- emission line measurements.

SELECT

s.plate, s.fiberid, s.mjd, s.z, s.zwarning,

g.h_beta_flux, g.h_beta_flux_err,

g.h_alpha_flux, g.h_alpha_flux_err
FROM GalSpecLine AS g
JOIN SpecObj AS s

ON s.specobjid = g.specobjid
WHERE

h_alpha_flux > h_alpha_flux_err*5

AND h_beta_flux > h_beta_flux_err*5

AND h_beta_flux_err > 0

AND h_alpha_flux > 10.*h_beta_flux

AND s.class = 'GALAXY'

AND s.zwarning = 0

-- b) This query selects red stars (spectral type K), with
-- large CaII triplet eq widths, with low errors on the CaII triplet
-- equivalent widths.

SELECT

sl.plate,sl.mjd,sl.fiber,

sl.caIIKside,sl.caIIKerr,sl.caIIKmask,

sp.fehadop,sp.fehadopunc,sp.fehadopn,
sp.loggadopn,sp.loggadopunc,sp.loggadopn
FROM sppLines AS sl
JOIN sppParams AS sp

ON sl.specobjid = sp.specobjid
WHERE

fehadop < -3.5

AND fehadopunc between 0.01

and 0.5 and fehadopn > 3

Finding spectra by classification (object type)

-- This sample query find all objects with spectra -- classified as stars. The query also checks that zWarning has no bits set, -- meaning that there are no known problems with the spectra. -- Other possible values with of class are 'QSO', 'GALAXY' and 'UNKNOWN'.
SELECT TOP 100 specObjID
FROM SpecObj
WHERE class = 'STAR' AND zWarning = 0

Moving Asteroids

-- Provide a list of moving objects consistent with an asteroid. -- This sample query uses the 'as' syntax, which allows you to -- give your own names to columns in the results. -- This query is an example of a situation where you must -- search the full PhotoObj view, since the -- columns rowv and colv are not in PhotoTag.
SELECT TOP 10
objID, ra, dec,
sqrt( power(rowv,2) + power(colv, 2) ) as velocity
FROM PhotoObj
WHERE
(power(rowv,2) + power(colv, 2)) > 50
AND rowv != -9999 and colv != -9999

Finding plates with repeat spectra

-- A query to list plates that have objects in common.
-- Returns the pairs of plates, the total number of nights
-- on which the objects they have in common have been observed, the progam to
-- which the special plate belongs, and the number of objects the plates
-- have in common.

SELECT first.plate, other.plate,

COUNT(DISTINCT other.mjd) + COUNT(DISTINCT first.mjd) AS nightsObserved,

otherPlate.programname, count(DISTINCT other.bestObjID) AS objects
FROM SpecObjAll first

JOIN SpecObjAll other ON first.bestObjID = other.bestObjID

JOIN PlateX AS firstPlate ON firstPlate.plate = first.plate

JOIN PlateX AS otherPlate ON otherPlate.plate = other.plate
WHERE first.scienceprimary = 1 AND other.scienceprimary = 0

AND other.bestObjID > 0
GROUP BY first.plate, other.plate, otherPlate.programname
ORDER BY nightsObserved DESC, otherPlate.programname,

first.plate, other.plate

Galaxies blended with stars

-- Find galaxies that are blended with a star, and output the -- deblended galaxy magnitudes. -- This query introduces the use of multiple tables or views with a table JOIN clause. -- You can assign nicknames to tables as in the FROM clause below. Since you are using -- multiple tables, you must specify which table each quantity in the SELECT clause -- comes from. The "ON " part of the JOIN clause specifies the joining -- condition between the two tables, which is achieved by requiring that a quantity -- present in both tables be equal.
SELECT TOP 10 G.ObjID, G.u, G.g, G.r, G.i, G.z	-- get the ObjID and final mags
FROM Galaxy AS G	-- use two Views, Galaxy and Star, as a
JOIN Star AS S	-- convenient way to compare objects
ON G.parentID = S.parentID	-- JOIN condition: star has same parent
WHERE G.parentID > 0	-- galaxy has a "parent", which tells us this
	-- object was deblended

Object counts by type and program

-- List the number of each type of object observed by each
-- special spectroscopic observation program.

SELECT plate.programname, class,

COUNT(specObjId) AS numObjs
FROM SpecObjAll

JOIN PlateX AS plate ON plate.plate = specObjAll.plate
GROUP BY plate.programname, class
ORDER BY plate.programname, class

Clean photometry with flags - Stars

-- The Clean Photometry sample query above showed how
-- to select only objects for which SDSS-III imaging pipeline has declared
-- the photometry clean. Usually, it is sufficient to search only the CLEAN flag;
-- but in some contexts, it is important to know what conditions
-- are required for the CLEAN flag to be set. The imaging pipeline sets the
-- CLEAN flag based on the values of other flags; these necessary values are
-- different for stars and galaxies. The version of this sample query for galaxies
-- can be found in the Clean photometry - Galaxies sample query.

-- For queries on star objects, when you use PSF mags, use only PRIMARY objects
-- and the flag combinations indicated below. If you use the Star view as this sample
-- query does, you will get only primary objects; otherwise you will need to add
-- a "mode=1" constraint. For example, if you are interested in r-band magnitudes
-- of objects, perform the following checks (add analogous checks with AND for
-- other bands if you are interested in multiple magnitudes or colors).

-- In this query, the human-readable flag names (through calls to the flag
-- functions have been replaced with explicit values. Using explicit values for flags
-- makes the queries run much faster.

SELECT TOP 10 u,g,r,i,z,ra,dec, flags_r
FROM Star
WHERE

ra BETWEEN 180 and 181 AND dec BETWEEN -0.5 and 0.5

AND ((flags_r & 0x10000000) != 0)

-- detected in BINNED1

AND ((flags_r & 0x8100000c00a4) = 0)

-- not EDGE, NOPROFILE, PEAKCENTER, NOTCHECKED, PSF_FLUX_INTERP,

-- SATURATED, or BAD_COUNTS_ERROR

AND (((flags_r & 0x400000000000) = 0) or (psfmagerr_r <= 0.2))

-- not DEBLEND_NOPEAK or small PSF error

-- (substitute psfmagerr in other band as appropriate)

AND (((flags_r & 0x100000000000) = 0) or (flags_r & 0x1000) = 0)

-- not INTERP_CENTER or not COSMIC_RAY

Cataclysmic variables (CVs) using colors

-- Search for Cataclysmic Variables and pre-CVs with White Dwarfs and -- very late secondaries, using simple color cuts from Paula Szkody. -- This is a simple query that uses math in the WHERE clause.
SELECT TOP 100 run,
camCol,
rerun,
field,
objID,
u, g, r, i, z,
ra, dec	-- Just get some basic quantities
FROM Star	-- From all stellar primary detections
WHERE u - g < 0.4
and g - r < 0.7
and r - i > 0.4
and i - z > 0.4	-- that meet the color criteria

Binary stars colors

-- Find binary stars with specific colors. -- At least one of them should have the colors of a white dwarf.
SELECT TOP 100 s1.objID as s1, s2.objID as s2
FROM Star AS S1	-- S1 is the white dwarf
JOIN Neighbors AS N ON S1.objID = N.objID	-- N is the precomputed neighbors lists
JOIN Star AS S2 ON S2.objID = N.NeighborObjID	-- S2 is the second star
WHERE
N.NeighborType = dbo.fPhotoType('Star')	-- and S2 is a star
and N.distance < .05	-- the 3 arcsecond test
and ((S1.u - S1.g) < 0.4 )	-- and S1 meets Paul Szkodys color cut for
and (S1.g - S1.r) < 0.7	-- white dwarfs.
and (S1.r - S1.i) > 0.4
and (S1.i - S1.z) > 0.4

Using the sppLines table

-- Another example of using the sppLines table generated by
-- the SEGUE Spectroscopic Parameter Pipeline. This sample query
-- selects low metallicity stars ([Fe/H] < -3.5) where more than three
-- different measures of feh are ok and are averaged.

SELECT sl.plate, sl.mjd, sl.fiber,

sl.caIIKside, sl.caIIKerr, sl.caIIKmask,

sp.fehadop, sp.fehadopunc,sp. fehadopn,

sp.loggadopn,sp.loggadopunc,sp.loggadopn

FROM sppLines AS sl

JOIN sppParams AS sp ON sl.specobjid = sp.specobjid

WHERE

fehadop < -3.5 and fehadopunc between 0.01 and 0.5

AND fehadopn > 3

Using the sppParams table

-- This sample query searches the sppParams table, which contains
-- stellar parameters calculated by the SEGUE Spectroscopic Parameter Pipeline.
-- The sample query selects spectroscopic stars in specific ranges of metallicity,
-- gravity and temperature (with some flag checks).

SELECT top 10 so.bestobjid, so.specobjid, so.plate, so.mjd, so.fiberid,

sp.teffadop, sp.fehadop, sp.loggadop, sp.snr, sp.flag
FROM specobjall so

-- get the stellar params for each spectrum.

-- the sciencePrimary=1 guarantees no repeats in this query, but not all

-- these spectra will be from SEGUE

JOIN sppparams sp ON so.specobjid = sp.specobjid and so.sciencePrimary=1

-- get the photometry info. note the "dr7" context prefix that will go

-- away when we get the dr8 imaging
WHERE sp.teffadop > 4500 and sp.teffadop < 5500

and fehadop > -2 and fehadop < -1.5

and loggadop > 2 and loggadop < 3

-- demand that the first two letters of the sspp flags be n (see web page docs)

-- the like and % are for sub-string comparisons

and sp.flag like '_n%'

and sp.snr > 30

Stars multiply measured

-- This query selects multiply-detected sources (stars) in photometry,
-- using the thingIndex and detectionIndex tables. thingIndex contains
-- a list of all unique sources, with a primary key defined called thingId,
-- and detectionIndex contains the objId of each observation of each
-- thingId. The objId can be used to join with the Star view of the photoObjAll table
-- in order to recover the photometric data associated with each stellar observation.

SELECT TOP 10 t.thingid, t.ndetect, d.objid, p.psfMag_r,

p.psfMagerr_r
FROM thingIndex AS t

JOIN detectionindex AS d ON t.thingid = d.thingid

JOIN Star AS p ON d.objid = p.objid
WHERE t.ndetect > 1

Proper motions in an open cluster

-- Select stars surrounding the open cluster M67, include positions, color mag -- and propermotion and pm errors. SELECT TOP 100 s.ra, s.dec, s.psfmag_g, (s.psfmag_g - s.psfmag_r) as gmr, pm.pmra, pm.pmdec, pm.pmraerr, pm.pmdecerr, pm.pml, pm.pmb FROM star s JOIN propermotions pm ON s.objid = pm.objid WHERE s.ra between 132.85-0.25 and 132.85+0.25 and s.dec between 11.82-0.25 and 11.82+0.25

Clean photometry with flags - Galaxies

-- The Clean Photometry sample query above showed how
-- to select only objects for which SDSS-III imaging pipeline has declared
-- the photometry clean. Usually, it is sufficient to search only the CLEAN flag;
-- but in some contexts, it is important to know what conditions
-- are required for the CLEAN flag to be set. The imaging pipeline sets the
-- CLEAN flag based on the values of other flags; these necessary values are
-- different for stars and galaxies. The version of this sample query for stars
-- can be found in the Clean photometry - Stars sample query.

-- For galaxies (i.e. not using PSF mags): Again use only PRIMARY objects. Other
-- cuts are nearly the same, but remove the cut on EDGE. Possibly also remove
-- the cut on INTERP flags.

-- In this query, the human-readable flag names (through calls to the flag
-- functions have been replaced with explicit values. Using explicit values for flags
-- makes the queries run much faster.

SELECT TOP 10 u,g,r,i,z,ra,dec, flags_r
FROM Galaxy
WHERE

ra BETWEEN 180 and 181 AND dec BETWEEN -0.5 and 0.5

AND ((flags_r & 0x10000000) != 0)

-- detected in BINNED1

AND ((flags_r & 0x8100000c00a0) = 0)

-- not NOPROFILE, PEAKCENTER, NOTCHECKED, PSF_FLUX_INTERP, SATURATED,

-- or BAD_COUNTS_ERROR.

-- if you want to accept objects with interpolation problems for PSF mags,

-- change this to: AND ((flags_r & 0x800a0) = 0)

AND (((flags_r & 0x400000000000) = 0) or (psfmagerr_r <= 0.2))

-- not DEBLEND_NOPEAK or small PSF error

-- (substitute psfmagerr in other band as appropriate)

AND (((flags_r & 0x100000000000) = 0) or (flags_r & 0x1000) = 0)

-- not INTERP_CENTER or not COSMIC_RAY - omit this AND clause if you want to

-- accept objects with interpolation problems for PSF mags.

Galaxies with blue centers

-- Galaxies with bluer centers, by Michael Strauss. For all galaxies with r_Petro < 18, -- not saturated, not bright, and not edge, give me those with centers appreciably bluer -- than their outer parts, i.e., define the center color as: u_psf - g_psf and define -- the outer color as: u_model - g_model; give me all objs which have -- (u_model - g_model) - (u_psf - g_psf) < -0.4 -- -- Another flags-based query. -- NOTE: This query takes a long time to run without the "TOP 1000".
SELECT TOP 1000
modelMag_u, modelMag_g, objID
FROM Galaxy
WHERE
( Flags & (dbo.fPhotoFlags('SATURATED') +
dbo.fPhotoFlags('BRIGHT') +
dbo.fPhotoFlags('EDGE')) ) = 0
and petroRad_r < 18
and ((modelMag_u - modelMag_g) - (psfMag_u - psfMag_g)) < -0.4

Diameter limited sample

-- Diameter-limited sample of galaxies from James Annis. -- Another query showing the use of flags, now using the bitwise '\|' (or). -- NOTE: This query takes a long time to run without the "TOP 10", please see below for a faster version.
SELECT TOP 10
run,
camCol,
rerun,
field,
objID,
ra,
dec
FROM Galaxy
WHERE ( flags & (dbo.fPhotoFlags('BINNED1')
\| dbo.fPhotoFlags('BINNED2')
\| dbo.fPhotoFlags('BINNED4')) ) > 0
and ( flags & (dbo.fPhotoFlags('BLENDED')
\| dbo.fPhotoFlags('NODEBLEND')
\| dbo.fPhotoFlags('CHILD')) ) != dbo.fPhotoFlags('BLENDED')
and ( (flags & dbo.fPhotoFlags('NOPETRO') = 0)
and petroR50_i > 15)
-- A much faster way to do this query is to substitute the actual flag values rather
-- than calling the dbo.fPhotoFlags functions for each row selected by the query.
-- Invoking these functions for potentially millions of rows is quite expensive. You
-- can avoid this by first running the 4 quick "pre-queries" shown below, with the
-- indicated values returned from each query. Then use these values to substitute
-- in the original query as shown in the last query below.

1) SELECT (dbo.fPhotoFlags('BINNED1')
\| dbo.fPhotoFlags('BINNED2')
\| dbo.fPhotoFlags('BINNED4'))

-- This returns the value 1879048192.

2) SELECT (dbo.fPhotoFlags('BLENDED')
\| dbo.fPhotoFlags('NODEBLEND')
\| dbo.fPhotoFlags('CHILD'))

-- This returns the value 88.

3) SELECT dbo.fPhotoFlags('BLENDED') -- This returns 8.
4) SELECT dbo.fPhotoFlags('NOPETRO') -- This returns 256.

-- Finally, here is the original query with these values instead of the function calls.
SELECT TOP 10
run,
camCol,
rerun,
field,
objID,
ra,
dec
FROM Galaxy
WHERE
( flags & 1879048192 ) > 0
and ( flags & 88 ) != 8
and ( (flags & 256 = 0)
and petroR50_i > 15)

SDSS luminous red galaxy selection

-- A version of the LRG sample, by James Annis. -- Not precisely the same version as that used in target selection. -- Another query with many conditions and flag tests. -- As with the previous example, this query will run much faster if -- the flag function calls are replaced with explicit values.
SELECT TOP 10
run,
camCol,
rerun,
field,
objID,
ra,
dec
FROM Galaxy
WHERE ( ( flags & (dbo.fPhotoFlags('BINNED1')
\| dbo.fPhotoFlags('BINNED2')
\| dbo.fPhotoFlags('BINNED4')) ) > 0
and ( flags & (dbo.fPhotoFlags('BLENDED')
\| dbo.fPhotoFlags('NODEBLEND')
\| dbo.fPhotoFlags('CHILD')) ) != dbo.fPhotoFlags('BLENDED')
and ( flags & (dbo.fPhotoFlags('EDGE')
\| dbo.fPhotoFlags('SATURATED')) ) = 0
and petroMag_i > 17.5
and (petroMag_r > 15.5 or petroR50_r > 2)
and (petroMag_r > 0 and g > 0 and r > 0 and i > 0)
and ( (petroMag_r-extinction_r) < 19.2
and (petroMag_r - extinction_r <
(13.1 + (7/3) * (dered_g - dered_r) + 4 * (dered_r - dered_i)
- 4 * 0.18) )
and ( (dered_r - dered_i - (dered_g - dered_r)/4 - 0.18) < 0.2)
and ( (dered_r - dered_i - (dered_g - dered_r)/4 - 0.18) > -0.2)
-- dered_ quantities already include reddening
and ( (petroMag_r - extinction_r +
2.5 * LOG10(2 * 3.1415 * petroR50_r * petroR50_r)) < 24.2) )
or ( (petroMag_r - extinction_r < 19.5)
and ( (dered_r - dered_i - (dered_g - dered_r)/4 - 0.18) > (0.45 - 4 *
(dered_g - dered_r)) )
and ( (dered_g - dered_r) > (1.35 + 0.25 * (dered_r - dered_i)) ) )
and ( (petroMag_r - extinction_r +
2.5 * LOG10(2 * 3.1415 * petroR50_r * petroR50_r) ) < 23.3 ) )

Classifications from Galaxy Zoo

-- DR8 includes data on visual galaxy morphologies from the -- Galaxy Zoo project. Galaxy Zoo is a web-based project -- that used the collective efforts of about 100,000 volunteer -- citizen scientists to classify more than one million -- SDSS DR7 Main Galaxy Sample (MGS) galaxies. -- Full details on the classification process, including the operation -- of the site and estimates of classification accurary, are given in -- Lintott et al. (2008). -- Galaxy Zoo data are contained in tables that begin with zoo -- in the DR8 schema. For each galaxy, available data includes its -- SDSS object ID and weighted probabilities for each of six different -- morphological classifications based on counts of volunteer "votes". -- Full details on what data the Galaxy Zoo catalog contains are given -- in Lintott et al. (2010). -- The first query below finds the weighted probability that a given galaxy -- has each of the six morphological classifications. -- The second query finds 100 galaxies that have clean photometry, -- at least 10 Galaxy Zoo volunteer votes and at least an 80% probability -- of being clockwise spirals.
SELECT
objid, nvote,
p_el as elliptical,
p_cw as spiralclock, p_acw as spiralanticlock, p_edge as edgeon,
p_dk as dontknow, p_mg as merger
FROM ZooNoSpec
WHERE objid = 1237656495650570395

-- Second query: find likely clockwise spirals.
SELECT TOP 100
g.objid, zns.nvote,
zns.p_el as elliptical,
zns.p_cw as spiralclock, zns.p_acw as spiralanticlock, zns.p_edge as edgeon,
zns.p_dk as dontknow, zns.p_mg as merger
FROM Galaxy as G
JOIN ZooNoSpec AS zns
ON G.objid = zns.objid
WHERE g.clean=1 and zns.nvote >= 10 and zns.p_cw > 0.8

BOSS Target Selection Sample

-- This query selects Luminous Red Galaxies between redshifts 0.4 and 0.65,
-- in a quasi-mass limited manner. This query is very similar to part of the
-- selection of BOSS targets (the so-called CMASS LRGs).

SELECT TOP 10 *
FROM photoprimary
WHERE

(dered_r-dered_i) < 2 AND

cmodelmag_i-extinction_i BETWEEN 17.5 AND 19.9 AND

(dered_r-dered_i) - (dered_g-dered_r)/8. > 0.55 AND

fiber2mag_i < 21.7 AND devrad_i < 20. AND

dered_i < 19.86 + 1.60*((dered_r-dered_i) - (dered_g-dered_r)/8. - 0.80)

Data subsample

-- Give me the colors of a random 1% sample of objects from all fields that -- are "survey quality" so that I could plot up color-color diagrams and play -- around with more sophisticated cuts. Query requested by Karl Glazebrook. -- Uses the HTM spatial index ID as a random number generator. The htmID is -- multiplied by a prime number (37) to remove bias and generate a random number -- from the lowermost 16 bits. This number is then constrained to be between 650 -- and 65000 to generate a random sample between 1 and 100% of the data -- respectively. So replacing the "1" by a different number between 1 and 99 -- will generate a sample of the required percentage of objects. -- Note that this is much faster than the standard "ORDER BY RANDOM()" SQL method.
SELECT TOP 100 u, g, r, i, z FROM Galaxy
WHERE
(htmid*37 & 0x000000000000FFFF)
< (650 * 1)	-- Replace the "1" with a number < 99 to get a higher percentage data subsample

Objects in close pairs using neighbors

-- This query introduces the Neighbors table, which contains each -- object's neighbors within 30 arcseconds. -- Find all objects within 30 arcseconds of one another -- that have very similar colors: that is where the color ratios -- u-g, g-r, r-I are less than 0.05m.
SELECT TOP 10 P.ObjID	-- distinct cases
FROM PhotoPrimary AS P	-- P is the primary object
JOIN Neighbors AS N ON P.ObjID = N.ObjID	-- N is the neighbor link
JOIN PhotoPrimary AS L ON L.ObjID = N.NeighborObjID
	-- L is the lens candidate of P
WHERE
P.ObjID < L. ObjID	-- avoid duplicates
and abs((P.u-P.g)-(L.u-L.g))<0.05	-- L and P have similar spectra.
and abs((P.g-P.r)-(L.g-L.r))<0.05
and abs((P.r-P.i)-(L.r-L.i))<0.05
and abs((P.i-P.z)-(L.i-L.z))<0.05

Selected neighbors in run

-- A more complex query that also uses the Neighbors table.
-- This is a query from Robert Lupton that finds selected neighbors in a given run and
-- camera column. It contains a nested query containing a join, and a join with the
-- results of the nested query to select only those neighbors from the list that meet
-- certain criteria. The nested queries are required because the Neighbors table does
-- not contain all the parameters for the neighbor objects. This query also contains
-- examples of setting the output precision of columns with STR.

SELECT TOP 10

obj.run, obj.camCol, str(obj.field, 3) as field,

str(obj.rowc, 6, 1) as rowc, str(obj.colc, 6, 1) as colc,

str(dbo.fObj(obj.objId), 4) as id,

str(obj.psfMag_g - 0*obj.extinction_g, 6, 3) as g,

str(obj.psfMag_r - 0*obj.extinction_r, 6, 3) as r,

str(obj.psfMag_i - 0*obj.extinction_i, 6, 3) as i,

str(obj.psfMag_z - 0*obj.extinction_z, 6, 3) as z,

str(60*distance, 3, 1) as D,

dbo.fField(neighborObjId) as nfield,

str(dbo.fObj(neighborObjId), 4) as nid
FROM

(SELECT obj.objId,

run, camCol, field, rowc, colc,

psfMag_u, extinction_u,

psfMag_g, extinction_g,

psfMag_r, extinction_r,

psfMag_i, extinction_i,

psfMag_z, extinction_z,

NN.neighborObjId, NN.distance

FROM PhotoObj as obj

JOIN neighbors as NN on obj.objId = NN.objId

WHERE

60*NN.distance between 0 and 15 and

NN.mode = 1 and NN.neighborMode = 1 and

run = 756 and camCol = 5 and

obj.type = 6 and (obj.flags & 0x40006) = 0 and

nchild = 0 and obj.psfMag_i < 20 and

(g - r between 0.3 and 1.1 and r - i between -0.1 and 0.6)

) as obj

JOIN PhotoObj as nobj on nobj.objId = obj.neighborObjId
WHERE

nobj.run = obj.run and

(abs(obj.psfMag_g - nobj.psfMag_g) < 0.5 or

abs(obj.psfMag_r - nobj.psfMag_r) < 0.5 or

abs(obj.psfMag_i - nobj.psfMag_i) < 0.5)

Object counting and logic

-- Using object counting and logic in a query. -- Find all objects similar to the colors of a quasar at 5.5
SELECT count(*) as 'total',
sum( case when (Type=3) then 1 else 0 end) as 'Galaxies',
sum( case when (Type=6) then 1 else 0 end) as 'Stars',
sum( case when (Type not in (3,6)) then 1 else 0 end) as 'Other'
FROM PhotoPrimary -- for each object
WHERE (( u - g > 2.0) or (u > 22.3) ) -- apply the quasar color cut.
and ( i between 0 and 19 )
and ( g - r > 1.0 )
and ( (r - i < 0.08 + 0.42 * (g - r - 0.96)) or (g - r > 2.26 ) )
and ( i - z < 0.25 )

Galaxy counts on HTM grid

-- Create a count of galaxies for each of the HTM triangles. -- Galaxies should satisfy a certain color cut, like -- 0.7u-0.5g-0.2i<1.25 && r<21.75, output it in a form -- adequate for visualization.
SELECT (htmID / power(2,24)) as htm_8 ,
-- group by 8-deep HTMID (rshift HTM by 12)
avg(ra) as ra,
avg(dec) as [dec],
count(*) as pop -- return center point and count for display
FROM Galaxy -- only look at galaxies
WHERE (0.7u - 0.5g - 0.2*i) < 1.25 -- meeting this color cut
and r < 21.75 -- brighter than 21.75 magnitude in red band.
group by (htmID /power(2,24)) -- group into 8-deep HTM buckets.

Repeated high-z objects

-- Compare different redshift measurements of the same object for objects
-- with high redshift

SELECT prim.bestObjId, prim.mjd AS PrimMJD, prim.plate AS PrimPlate,
    other.mjd AS OtherMJD, other.plate AS OtherPlate,
    prim.z AS PrimZ, other.z AS OtherZ, plate.programname
FROM SpecObjAll prim
    JOIN SpecObjAll other ON prim.bestObjId = other.bestObjId
    JOIN platex AS plate ON other.plate = plate.plate AND other.mjd = plate.mjd
WHERE other.bestObjId > 0
    AND prim.sciencePrimary = 1
    AND other.sciencePrimary = 0
    AND prim.z > 2.5
ORDER BY prim.bestObjId

Splitting 64-bit values into two 32-bit values

-- The flag fields in the SpecObjAll table are 64-bit, but some -- analysis tools (and FITS format) only accept 32-bit integers. -- Here is a way to split them up, using bitmasks to extract -- the higher and lower 32 bits, and dividing by a power of -- 2 to shift bits to the right (since there is no bit shift -- operator in SQL.) The hexadecimal version can be used for debugging -- to make sure you are splitting them up right.

SELECT TOP 10 objid,ra,dec,
flags,	-- output the whole bigint as a check
flags & 0x00000000FFFFFFFF AS flags_lo,	-- get the lower 32 bits with a mask
-- shift the bigint to the right 32 bits, then use the same mask to
-- sget upper 32 bits
(flags/power(cast(2 as bigint),32)) & 0x00000000FFFFFFFF AS flags_hi
FROM PhotoObj

-- Hexadecimal version of above query - use for debugging ("Run this Query" button will run this).
SELECT TOP 10 objid,ra,dec,
CAST(flags AS BINARY(8)) AS flags,
CAST(flags & 0x00000000FFFFFFFF AS BINARY(8)) AS flags_lo,
CAST((flags/POWER(CAST(2 AS BIGINT),32)) & 0x00000000FFFFFFFF AS BINARY(8)) AS flags_hi
FROM PhotoObj

Using LEFT OUTER JOIN

-- This query demonstrates the use of a LEFT OUTER JOIN to include rows from the joined table
-- that do not satisfy the JOIN condition. The OUTER JOIN (LEFT or RIGHT)
-- is an inclusive JOIN, whereas the INNER (default) JOIN is an exclusive JOIN.
-- In this case the query returns galaxies with or without spectra that meet the criteria
-- in the WHERE clause. The ISNULL operator is used to replace null
-- values with zeros for galaxies without spectra.

SELECT TOP 10 p.objid, p.ra, p.dec, ISNULL(s.specobjid,0) AS specobjid, ISNULL(s.z, 0) AS z
FROM Galaxy AS p

LEFT OUTER JOIN SpecObj s ON s.bestObjID = p.objID
WHERE

p.u BETWEEN 0 AND 19.6 AND p.g BETWEEN 0 AND 20

Using Nested Queries

-- This query demonstrates, among other tings, the use of a nested query (a query
-- inside another query) in which the outer query operates on the result of the inner query. The
-- query also gets the sky brighness and turns it into a flux, which illustrates the use of
-- the POWER() function and CAST to change the string representation into floating
-- point. The First table contains matches between SDSS and FIRST survey objects.

SELECT TOP 10 fld.run, fld.avg_sky_muJy, fld.runarea AS area, ISNULL(fp.nfirstmatch,0)
FROM (
--first part: for each run, get total area and average sky brightness

SELECT run, sum(primaryArea) AS runarea,

3631e6*avg(power(cast(10. as float),-0.4*sky_r)) as avg_sky_muJy

FROM Field

GROUP BY run
) AS fld
LEFT OUTER JOIN (
-- second part: for each run,get total number of FIRST matches. To get the run number
-- for each FIRST match, need to join FIRST with PHOTOPRIMARY. Some runs may have
-- 0 FIRST matches, so these runs will not appear in the result set of this subquery.
-- But we want to keep all runs from the first query in the final result, hence
-- we need a LEFT OUTER JOIN between the first and the second query.
-- The LEFT OUTER JOIN returns all the rows from the first subquery and matches
-- with the corresponding rows from the second query. Where the second query
-- has no corresponding row, a NULL is returned. The ISNULL() function in the
-- SELECT above converts this NULL into a 0 to say "0 FIRST matches in this run".

SELECT p.run, count(*) AS nfirstmatch

FROM FIRST AS fm

INNER JOIN photoprimary as p

ON p.objid=fm.objid

GROUP BY p.run
) AS fp
ON fld.run=fp.run
ORDER BY fld.run

Checking if objects are in SDSS footprint

-- This query uses the OUTER APPLY construct to apply a
-- table-valued function to the results of a query.
-- ( See also example below of checking whether a single point is in the SDSS footprint.)
-- In this example, we use the fFootPrintEq function which returns a
-- a non-NULL value ("POLYGON") if the area specified by the RA, dec and
-- radius is inside the SDSS footprint.
-- For each point in the input list, in this case the result of a query
-- on PhotoObj, return "yes" or "no" depending on whether the point is in
-- the SDSS footprint or not, along with any other needed columns.
-- Note that this is really a trivial example since every point in PhotoObj
-- is already in the SDSS footprint. To be really meaningful, a query like
-- this needs to be run on a list of RA, decs that may or may not be in the
-- in the SDSS footprint. This can be done using a MyDB table in CasJobs
-- containing the list of points, for example, or by uploading a list in
-- the Object Crossid tool.

SELECT top 100 objID, ra, dec,

(CASE WHEN q.type IS NULL THEN 'NO' ELSE 'YES' END) AS found
FROM PhotoObj AS p

OUTER APPLY dbo.fFootprintEq(ra,dec,0.1) AS q
WHERE (ra BETWEEN 179.5 AND 182.3) AND (dec BETWEEN -1.0 AND 1.8)

-- Checking the footprint for a single point with the fInFootprintEq scalar function.
SELECT dbo.fInFootprintEq(180, -0.5, 0.2)

-- Or if you have a table containing ra,dec values (for example a MyDB table
-- in CasJobs), you can do something lile:
SELECT dbo.fInFootprintEq(t.ra, t.dec, 0.1)
FROM MyTable_10 AS t

QSOs by spectroscopy

-- The easiest way to find quasars is by finding objects whose spectra have -- been classified as quasars. This sample query searches the SpecObj -- table for the IDs and redshifts of objects with the class column equal to 'QSO'
SELECT TOP 100 specObjID, z
FROM SpecObj
WHERE class = 'QSO' AND zWarning = 0

QSOs using colors

-- Low-z QSO candidates using the color cuts from Gordon Richards. -- Also a simple query with a long WHERE clause.
SELECT TOP 100
g,
run,
rerun,
camcol,
field,
objID
FROM Galaxy
WHERE ( (g <= 22)
and (u - g >= -0.27)
and (u - g < 0.71)
and (g - r >= -0.24)
and (g - r < 0.35)
and (r - i >= -0.27)
and (r - i < 0.57)
and (i - z >= -0.35)
and (i - z < 0.70) )

FIRST matches for quasars

-- This sample query is a useful quasar query (from Sebastian Jester).
-- Getting magnitudes for spectroscopic quasars - retrieves photometry.
-- This query uses the SpecPhoto view of the SpecPhotoAll table, which is a pre-computed join
-- of the important fields in the SpecObjAll and PhotoObjAll tables. It is very convenient and much
-- faster to use this when you can instead of doing the join yourself.
-- Getting FIRST data for spectroscopic quasars - returns only those quasars that have
-- matches in the FIRST (Far-InfraRed Survey Telescope) table.

SELECT sp.ra,sp.dec,sp.z,

sp.psfmag_i-sp.extinction_i AS mag_i,

peak,integr
FROM SpecPhoto AS sp

INNER JOIN FIRST AS f ON sp.objid = f.objid
WHERE

class = 'QSO'

Photometric Redshifts

-- Here is a simple query for objects with reliable redshift estimation in the 0.4 < z < 0.5 range -- The query uses the Photoz table that contains photometric redshift estimates for galaxies.

SELECT TOP 100 objId,z,zErr,absMagR FROM Photoz WHERE z BETWEEN 0.4 and 0.5
and nnIsInside=1	-- the estimated object has nearest neighbors in the reference set
and nnCount>95	-- note that zErr=-1000 indicates unreliable redshift estimate,
and zErr BETWEEN 0 and 0.03	-- that's why the lower bound on zErr is needed
-- A simple luminosity function with the same selection criteria as above -- Returns a histogram of absolute magnitudes for the given redshift range. -- (You will need to cut and paste this query if you want to run it in the SQL Search page, -- the above buttons will only load/run the first query). SELECT round(absMagR,1) as absMagR, COUNT(*) as cnt FROM Photoz WHERE z BETWEEN 0.4 and 0.5 and nnIsInside=1 and nnCount>95 and zErr BETWEEN 0 and 0.03 group by round(absMagR,1) order by round(absMagR,1)

DR8 Help



Start Here
FAQ
Glossary
Cooking with Sloan
Tool User Guides
Contact Help Desk

Searching SDSS-III
SQL Tutorial
SQL in SkyServer
About the Database
Table Descriptions
Sample SQL Queries
Schema Browser
Searching Advice
Query Limits

Details of the Data
Algorithms
Data Publications
API