SkyServer Sample SQL Queries
  Home       Tools      Schema    Projects     Astronomy    SDSS     Contact Us     Download    Site Search    Help    
DR6 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
Sample SQL Queries

The following is a selection of actual queries submitted by SDSS users, and some are in response to scientific questions posed by users. The queries are listed in increasing order of difficulty/complexity. Where applicable, query execution times for the latest SDSS data releases are noted.

NOTE: Please also read the Optimizing Queries and Bookmark Lookup Bug sections of the SQL Intro page to learn how to run faster queries, and the Query Limits page to see the timeouts and row limits on queries.

Click on the name of the query from the list below to go directly to that sample query. The queries are roughly in order of increasing complexity. You can cut and paste queries from here into your favorite search tool.

  • Basic SELECT-FROM-WHERE
  • Galaxies with two criteria
  • Unclassified spectra
  • Galaxies with multiple criteria
  • Spatial unit vectors
  • CVs using colors
  • Data subsample
  • Low z QSOs by colors
  • Velocities and errors
  • Using BETWEEN
  • Moving asteroids
  • Quasars in imaging
  • Selected neighbors in run
  • Multiple OUTER JOINs
  • Repeat spectra
  • Special program targets
  • Uniform Quasar Sample
  • Combining UberCal mags
  • Checking SDSS footprint
    		•
  • Repeated high-z objects
  • Object counts and logic
  • Galaxies blended with stars
  • Stars in specific fields
  • Using three tables
  • Objects close pairs
  • QSOs in spectroscopy
  • Errors using flags
  • Elliptical galaxies
  • Galaxies with blue centers
  • Diameter limited sample
  • Extremely red galaxies
  • LRG sample
  • Brightness of closest source
  • Multiple spectral lines
  • Special program data
  • Merging Galaxy Pairs
  • Using sppLines table
  • Splitting 64-bit values
    		•
  • Galaxies by spectra
  • Clean photometry
  • Binary stars colors
  • QSO broadlines near galaxy
  • Galaxies unsaturated
  • Ellipticals with odd lines
  • Broadest spectral lines
  • Gridded galaxy counts
  • Galaxy counts on HTM grid
  • Stars multiply measured
  • White Dwarf candidates
  • More quasar queries
  • Using LEFT OUTER JOIN
  • Galaxy counts in North
  • Counts by type and program
  • Spatial Queries using HTM
  • Using sppParams table
  • Spectroscopy Completeness
  • Variability Queries
    		•

    Some general hints:

    1. 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.
    2. 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.
    3. If a query takes much longer to run than you think it should, you should try it at a different time again to make sure that server load is not the main reason why it ran much slower the first time.
    4. A good way to find if there are any objects satisfying a given query is to use the "TOP <n>" SQL construct, e.g. "SELECT TOP 100 FROM ...", which will only return the up to the first 100 matching objects.
    5. If your query returns a lot of output (more than a few thousand objects), it is generally not a good idea to let the browser render the output by selecting the HTML output format (default) in the SQL Search page of SkyServer. You can try using the CSV output format instead of HTML in the browser for large outputs. However, you're much better off using one of the other interfaces (Emacs, sdssQA, sqlcl or CasJobs) to get large rowsets. Browsers are generally very slow in rendering large outputs, and this also slows down the webserver for other users.
    6. Be sure to exclude invalid values (unset or uncalculated quantities) as described in the SQL help under Excluding Invalid Data Values.
    7. 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 or the Uniform Quasar Sample sample queries below for examples of how to use STR.

    Basic SELECT-FROM-WHERE      Back to TopTop

    -- Returns 5261 objects in DR2 (5278 in DR1) in a few sec.

    -- Find objects in a particular field.
    -- A basic SELECT - FROM - WHERE query.

    SELECT objID, -- Get the unique object ID,
    field, ra, dec
    		-- the field number, and coordinates
    FROM PhotoObj -- From the photometric data
    WHERE run=1336 and field = 11 -- that matches our criteria

    Galaxies with two criteria      Back to TopTop

    -- Returns 1000 objects in a few sec.

    -- Find all galaxies brighter than r magnitude 22, where the local
    -- extinction is > 0.175. This is a simple query that uses a WHERE clause,
    -- but now two conditions that must be met simultaneously. However, this
    -- query returns a lot of galaxies (29 Million in DR2!), so it will take a
    -- long time to get the results back. The sample therefore includes a
    -- "TOP 1000" restriction to make it run quickly.
    -- This query also introduces the Galaxy view, which contains the
    -- photometric parameters (no redshifts or spectra) for unresolved objects.

    SELECT TOP 1000 objID
    FROM Galaxy
    WHERE
    r < 22
    		-- r IS NOT deredenned
    and extinction_r > 0.175
    		-- extinction more than 0.175

    Unclassified spectra      Back to TopTop

    -- Find all objects with unclassified spectra.
    -- A simple SELECT-FROM-WHERE query, using a function

    SELECT specObjID
    FROM SpecObj
    WHERE SpecClass = dbo.fSpecClass('UNKNOWN')

    Galaxies with multiple criteria      Back to TopTop

    -- Find all galaxies with blue surface brightness between 23 and 25
    -- mag per square arcseconds, and -10 < supergalactic latitude (sgb) < 10, and
    -- declination less than zero. Currently, we have to live with ra/dec until we
    -- get galactic coordinates. To calculate surface brightness per sq. arcsec,
    -- we use (g + rho), where g is the blue magnitude, and rho= 5*log(r). This
    -- query now has three requirements, one involving simple math.

    SELECT objID
    FROM Galaxy
    WHERE ra between 250 and 270
    and dec > 50
    and (g+rho) between 23 and 25
    		-- g is blue magnitude, and rho= 5*log(r)

    Spatial unit vectors      Back to TopTop

    -- Find galaxies in a given area of the sky, using a coordinate cut
    -- in the unit vector cx,cy,cz that corresponds to RA beteen 40 and 100.
    -- Another simple query that uses math in the WHERE clause.

    SELECT colc_g, colc_r
    FROM Galaxy
    WHERE (-0.642788 * cx + 0.766044 * cy>=0)
    and (-0.984808 * cx - 0.173648 * cy <0)

    Cataclysmic variables (CVs) using colors      Back to TopTop

    -- Search for Cataclysmic Variables and pre-CVs with White Dwarfs and
    -- very late secondaries. Just uses some simple color cuts from Paula Szkody.
    -- Another simple query that uses math in the WHERE clause

    SELECT run,
    camCol,
    rerun,
    field,
    objID,
    u, g, r, i, z,
    ra, dec
    		-- Just get some basic quantities
    FROM PhotoPrimary -- From all primary detections, regardless of class
    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

    Data subsample      Back to TopTop

    -- Give me the colours of a random 1% sample of objects from all fields
    -- which are 'survey quality' so that I could plot up colour-colour diagrams
    -- and play around with more sophisticated cuts. From Karl Glazebrook. Uses
    -- the HTM spatial index ID to apply the cut against. Replace the last '1' by
    -- a different number if you want to sample a different percentage of objects.

    SELECT u, g, r, i, z FROM Galaxy
    WHERE htmid*37 & 0x000000000000FFFF < (650 * 1)

    Low-z QSOs using colors      Back to TopTop

    -- Low-z QSO candidates using the color cuts from Gordon Richards.
    -- Also a simple query with a long WHERE clause.

    SELECT
    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) )

    Object velocities and errors      Back to TopTop

    -- Get object velocities and errors. This is also a simple query that uses a WHERE clause.
    -- However, we perform a more complex mathematical operation, using 'power' to
    -- exponentiate. (From Robert Lupton).

    -- NOTE: This query takes a long time to run without the "TOP 1000".

    SELECT TOP 1000
    run,
    camCol,
    field,
    objID,
    rowC, colC, rowV, colV, rowVErr, colVErr,
    flags,
    psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z,
    psfMagErr_u, psfMagErr_g, psfMagErr_r, psfMagErr_i, psfMagErr_z
    FROM PhotoPrimary
    WHERE
    -- where the velocities are reliable
    power(rowv, 2) / power(rowvErr, 2) +
    power(colv, 2) / power(colvErr, 2) > 4

    Using BETWEEN      Back to TopTop

    -- Find galaxies with an isophotal surface brightness (SB) larger
    -- than 24 in the red band, and with an ellipticity > 0.5, and with the major
    -- axis of the ellipse having a declination between 30" and 60" arc seconds.
    -- This is also a simple query that uses a WHERE clause with three conditions
    -- that must be met. We introduce the syntax 'between' to do a range search.

    SELECT TOP 10 objID, r, rho, isoA_r
    FROM Galaxy
    WHERE
    r + rho < 24
    		-- red surface brightness more than
    -- 24 mag/sq-arcsec
    and isoA_r between 30/0.4 and 60/0.4
    		-- major axis between 30" and 60"
    		-- (SDSS pixels = 0.4 arcsec)
    and (power(q_r,2) + power(u_r,2)) > 0.25
    		-- square of ellipticity > 0.5 squared

    Moving Asteroids      Back to TopTop

    -- Provide a list of moving objects consistent with an asteroid.
    -- Also a simple query, but we introduce the 'as' syntax, which allows us to
    -- name derived quantities in the result file.

    SELECT
    objID,
    sqrt( power(rowv,2) + power(colv, 2) ) as velocity
    FROM PhotoObj
    WHERE
    (power(rowv,2) + power(colv, 2)) > 50
    and rowv >= 0 and colv >=0

    Quasars in imaging      Back to TopTop

    -- Find quasars as specified by Xiaohui Fan et.al.
    -- A rather straightforward query, just with many conditions. It also introduces
    -- the Star view, which contains the photometric parameters for all primary point-like
    -- objects (including quasars).

    SELECT run,
    camCol,
    rerun,
    field,
    objID,
    u, g, r, i, z,
    ra, dec
    FROM Star -- or Galaxy
    WHERE ( u - g > 2.0 or u > 22.3 )
    and ( i < 19 )
    and ( i > 0 )
    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 )

    Object counting and logic      Back to TopTop

    -- 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 )

    Galaxies blended with stars      Back to TopTop

    -- 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.

    -- NOTE: This query takes a long time to run without the "TOP 10".

    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

    Stars in specific fields      Back to TopTop

    -- Give me the PSF colors of all stars brighter than g=20 that have PSP_STATUS = 2.
    -- Another simple multi-table query with a JOIN.

    SELECT
    s.psfMag_g,
    		-- or whatever you want from each object
    s.run,
    s.camCol,
    s.rerun,
    s.field
    FROM Star AS s
    JOIN Field AS f ON s.fieldID = f.fieldID
    WHERE s.psfMag_g < 20
    and f.pspStatus = 2

    Using three tables      Back to TopTop

    -- Find the parameters for all objects in fields with desired PSF width and range
    -- of columns. Now we are using three tables, but it is still a simple query.

    SELECT
    g.run,
    g.rerun,
    g.camCol,
    f.field,
    p.objID,
    p.ra,
    p.dec,
    p.Rowc,
    p.Colc,
    p.u,
    p.modelMagErr_u ,
    p.g,
    p.modelMagErr_g,
    p.r,
    p.modelMagErr_r,
    p.petroMag_r - p.extinction_r,
    p.petroMagErr_r,
    p.i,
    p.modelMagErr_i,
    p.z,
    p.status & 0x00002000,
    f.psfWidth_r
    FROM
    PhotoObj AS p
    JOIN Field AS f ON f.fieldid = p.fieldid
    JOIN Segment AS g ON f.segmentid = g.segmentid
    WHERE
    g.run = 1336 and g.camCol = 1
    and f.field between 11 and 13
    and f.psfWidth_r > 1.2
    and p.colc > 400.0

    Selected neighbors in run      Back to TopTop

    -- 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
    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,'new' as 'new'
    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)
    ORDER BY obj.run, obj.camCol, obj.field

    QSOs in spectroscopy      Back to TopTop

    -- Find quasars with 2.5 < redshift < 2.7. This will use the spectro tables,with a simple
    -- multi-constraint WHERE clause. We introduce the use of a function, in this case
    -- dbo.fSpecClass, to select objects by named types instead of using the bitwise flags.

    SELECT specObjID, -- get the spectroscopic object id
    z, zConf,
    		-- redshift, redshift confidence
    SpecClass
    		-- and spectral classification
    FROM SpecObj -- from the spectroscopic objects
    WHERE
    -- use a function to translate SpecClass bits to names; want quasars
    (SpecClass=dbo.fSpecClass('QSO')
    or SpecClass=dbo.fSpecClass('HIZ_QSO'))
    -- and the redshift is 2.5 to 2.7. Remember, z is redshift in SpecObj.
    and z between 2.5 and 2.7
    -- and we have a high confidence redshift estimate.
    and zConf > 0.90

    Objects close pairs      Back to TopTop

    -- 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

    Errors using flags      Back to TopTop

    -- Another useful query is to see if the errors on moving (or
    -- apparently moving) objects are correct. For example, it used to be that
    -- some known QSOs were being flagged as moving objects. One way to look for
    -- such objects is to compare the velocity to the error in velocity and see if
    -- the "OBJECT1_MOVED" or "OBJECT2_BAD_MOVING_FIT" is set.
    -- 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)

    -- NOTE: This query takes a long time to run without the "TOP 1000".

    SELECT TOP 1000
    run,
    rerun,
    camcol,
    field,
    objID,
    ra, dec,
    rowv, colv,
    rowvErr, colvErr,
    i,
    (flags & dbo.fPhotoFlags('MOVED')) as MOVED,
    (flags & dbo.fPhotoFlags('BAD_MOVING_FIT')) as BAD_MOVING_FIT
    FROM Galaxy
    WHERE
    (flags & (dbo.fPhotoFlags('MOVED') + dbo.fPhotoFlags('BAD_MOVING_FIT'))) > 0
    and (rowv * rowv + colv * colv) >=
    (rowvErr * rowvErr + colvErr * colvErr)

    Elliptical galaxies based on model fits      Back to TopTop

    -- Find all galaxies with a deVaucouleours profile and the
    -- photometric colors consistent with an elliptical galaxy. NOTE THAT THE
    -- NAMES AND VALUES OF THE LIKELIHOODS HAVE CHANGED SINCE THE EDR; they are
    -- now log likelihoods, and named accordingly (lDev is now lnlDev, etc.) to
    -- indicate these are log likelihoods. This query has many conditions, and
    -- also has the use of bitwise logic necessary for dealing with flags.

    SELECT ObjID
    FROM Galaxy as G
    WHERE
    G.lnlDev_r > G.lnlExp_r + 0.1
    -- the likelihood of the deVaucouleours profile fit is 10% greater than the
    -- likelihood of the exponential fit
    and G.lnlExp_r > -999
    -- and the likelihoods are actually meaningful
    and (G.flags & (dbo.fPhotoFlags('BINNED1') + dbo.fPhotoFlags('BINNED2') +
    dbo.fPhotoFlags('BINNED4'))) > 0
    -- and it is detected from at least one of the binned images
    and (G.flags & ( dbo.fPhotoFlags('BLENDED') + dbo.fPhotoFlags('NODEBLEND') +
    dbo.fPhotoFlags('CHILD'))) != dbo.fPhotoFlags('BLENDED')
    -- and, if it is blended, it is either a child or not deblended further
    and (G.flags & (dbo.fPhotoFlags('EDGE') + dbo.fPhotoFlags('SATURATED'))) = 0
    -- and it is not near a ccd edge or saturated, where measurements may be bad
    and G.petroMag_i > 17.5
    -- and it is fainter than 17.5 in i-band
    and (G.petroMag_r > 15.5 or G.petroR50_r > 2)
    and (G.petroMag_r > 0 and G.g > 0 and G.r > 0 and G.i > 0)
    and ( (G.petroMag_r - G.extinction_r) < 19.2
    and (G.petroMag_r - G.extinction_r <
    (13.1 + (7/3)*(G.g - G.r) + 4 *(G.r - G.i) - 4 * 0.18) )
    and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) < 0.2 )
    and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) > -0.2 ) )
    or ( (G.petroMag_r - G.extinction_r < 19.5)
    and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) >
    (0.45 - 4*(G.g - G.r) ) )
    and ( (G.g - G.r) > (1.35 + 0.25 *(G.r - G.i) ) ) )
    -- and many constraints on colors and mags to make it have elliptical-type colors.

    Galaxies with blue centers      Back to TopTop

    -- 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      Back to TopTop

    -- Diameter-limited sample of galaxies from James Annis.
    -- Another query showing the use of flags, now using the bitwise '|' (or).

    SELECT
    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 petroRad_i > 15)
    or ( (flags & dbo.fPhotoFlags('NOPETRO') > 0)
    and petroRad_i > 7.5)
    or ( (flags & dbo.fPhotoFlags('TOO_LARGE') > 0)
    and petroRad_i > 2.5)
    or ( (flags & dbo.fPhotoFlags('SATURATED') = 0)
    and petroRad_i > 17.5) )

    Extremely red galaxies      Back to TopTop

    -- Extremely red galaxies (from James Annis).
    -- Similar to the previous query.

    SELECT
    g.run,
    g.camCol,
    g.rerun,
    g.field,
    g.objID,
    g.ra, g.dec
    FROM Field f
    JOIN Galaxy g ON g.fieldID = f.fieldID
    WHERE
    ( g.flags & (dbo.fPhotoFlags('BINNED1')
    | dbo.fPhotoFlags('BINNED2')
    | dbo.fPhotoFlags('BINNED4')) ) > 0
    and ( g.flags & (dbo.fPhotoFlags('BLENDED')
    | dbo.fPhotoFlags('NODEBLEND')
    | dbo.fPhotoFlags('CHILD')) ) != dbo.fPhotoFlags('BLENDED')
    and ( g.flags & (dbo.fPhotoFlags('COSMIC_RAY')
    | dbo.fPhotoFlags('INTERP')) ) = 0
    and f.psfWidth_r < 1.5
    and (g.i - g.z > 1.0 )

    LRG sample      Back to TopTop

    -- A version of the LRG sample, by James Annis.
    -- Another query with many conditions and flag tests.

    SELECT
    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 ) )

    Brightness of closest source      Back to TopTop

    -- The query below was originally written by Andy Connolly to find the brightness of
    -- the closest source within 0.5arcmin. It involves a 3-way join of the PhotoObjAll table
    -- with itself and the Neighbors table. This is a huge join because the PhotoObjAll table
    -- is the largest table in the DB, and the Neighbors table has over a billion entries
    -- (although it is a thin table). The two versions of the query shown below illustrate
    -- how we can speed up the query a lot by using the (much thinner) PhotoTag table
    -- instead of the PhotoObjAll table. See also the Optimizing Queries section of the
    -- SQL Intro page for more on using the PhotoTag table. The query also illustrates the
    -- LEFT JOIN construct and the use of nested joins.

    -- The first (original) version of the query uses the PhotoObjAll table twice in the 3-way
    -- join because we need some of the columns that are only in the PhotoObjall table.
    -- Since this version literally takes days to run on the entire DR2 database, a TOP 100
    -- has been inserted into the SELECT to prevent the query from being submitted as is.

    SELECT TOP 100 o.ra,o.dec,o.flags, o.type,o.objid,
    o.psfMag_g,o.psfMag_r,o.psfMag_i,o.modelMag_g,o.modelMag_r,o.modelMag_i,
    o.petroRad_r,
    o.q_g,o.q_r,o.q_i,
    o.u_g,o.u_r,o.u_i,
    o.mE1_r,o.mE2_r,o.mRrCc_r,o.mCr4_r,
    o.isoA_r,o.isoB_r,o.isoAGrad_r,o.isoBGrad_r,o.isoPhi_r,
    n.distance,p.r,p.g
    FROM PhotoObjAll as o
    LEFT JOIN Neighbors as n on o.objid=n.objid
    JOIN PhotoObjAll p ON p.objId=n.neighborObjId
    WHERE
    (o.ra > 120) and (o.ra < 240)
    and (o.r > 16.) and (o.r<21.0)
    and n.neighborObjId=(select top 1 nn.neighborObjId
       from Neighbors nn join PhotoObjAll pp ON nn.neighborObjId = pp.objID
       where nn.objId=o.objId
       order by pp.r)

    -- The second version of this query demonstrates the advantage of using the PhotoTag
    -- table over the PhotoObjAll table. One of the PhotoObjAll joins in the main 3-way
    -- join is replaced with PhotoTag, and the nested PhotoObjAll join with Neighbors is
    -- also replaced with PhotoTag. This version runs in about 2-3 hours on the DR2 DB.
    -- Note that when you replace PhotoObjAll or its views by PhotoTag, you have to also
    -- replace any references to the shorthand (simplified) magnitudes (u,g,r,i,z) and errors
    -- by their full names (modelMag_u and modelMagErr_u etc.).

    SELECT o.ra,o.dec,o.flags, o.type,o.objid,
    o.psfMag_g,o.psfMag_r,o.psfMag_i,o.modelMag_g,o.modelMag_r,o.modelMag_i,
    o.petroRad_r,
    o.q_g,o.q_r,o.q_i,
    o.u_g,o.u_r,o.u_i,
    o.mE1_r,o.mE2_r,o.mRrCc_r,o.mCr4_r,
    o.isoA_r,o.isoB_r,o.isoAGrad_r,o.isoBGrad_r,o.isoPhi_r,
    n.distance,p.r,p.g
    FROM PhotoObjAll as o
    LEFT JOIN Neighbors as n on o.objid=n.objid
    JOIN PhotoTag p ON p.objId=n.neighborObjId   
      -- replace second PhotoObjAll by PhotoTag
    WHERE
    (o.ra > 120) and (o.ra < 240)
    and (o.r > 16.) and (o.r<21.0)
    and n.neighborObjId=(select top 1 nn.neighborObjId
       from Neighbors nn
       join PhotoTag pp ON nn.neighborObjId = pp.objID
         -- replace PhotoObjAll with PhotoTag here too
       where nn.objId=o.objId
       order by pp.modelMag_r)   -- PhotoTag doesnt have shorthand u,g,r,i,z mags

    Galaxies by spectra      Back to TopTop

    -- Two versions of a query to find galaxies with particular spectral lines.
    -- Version 1: Find galaxies with spectra that have an equivalent width in
    -- H_alpha > 40 Angstroms. We want object ID's from the photometry (Galaxy)
    -- but constraints from spectroscopy. The line widths and IDs are stored in
    -- SpecLine. This is a simple query, but now we are using three tables. The
    -- spectroscopy tables of measured lines are arranged non-intuitively, and we
    -- urge users to read about them on the DAS help pages. -- IMPORTANT NOTE:
    -- Each spectroscopic object now has a match to at least two photometric
    -- objects, one in Target and one in Best. Therefore, when performing a join
    -- between spectroscopic photometric objects, you must specify either
    -- PhotoObj.ObjID=SpecObj.bestObjID OR PhotoObj.ObjID = SpecObj.targetObjID.
    -- Normally, the default photometric database is BEST, so you will want to use
    -- SpecObj.bestObjID

    SELECT G.objId -- we want the photometric objID
    FROM Galaxy as G
       JOIN SpecObj as S ON G.objId=S.bestObjId -- this galaxy has a spectrum, and
       JOIN SpecLine as L ON S.specObjId=L.specObjId -- line L is detected in spectrum
    WHERE
         -- you could add a constraint that the spectral type is galaxy
         L.LineId = 6565 -- and line L is the H alpha line
         and L.ew > 40 -- and is > 40 angstrom wide

    -- Second version of this query finds galaxies with more specific spectra.
    -- This version also requires weak Hbeta line (Halpha/Hbeta > 20.)

    SELECT G.ObjID -- return qualifying galaxies
    FROM Galaxy as G -- G is the galaxy
       JOIN SpecObj as S ON G.ObjID = S.BestObjID -- S is the spectra of galaxy G
       JOIN SpecLine as L1 ON S.SpecObjID = L1.SpecObjID -- L1 is a line of S
       JOIN SpecLine as L2 ON S.SpecObjID = L2.SpecObjID -- L2 is a second line of S
       JOIN SpecLineNames as LN1 ON L1.LineId = LN1. value -- the names of the lines (Halpha)
       JOIN SpecLineNames as LN2 ON L2.LineId = LN2.value -- the names of the lines (Hbeta)
    WHERE LN1.name = 'Ha_6565' -- L1 is H-alpha
    and LN2.name = 'Hb_4863'
    		-- L2 is H-beta
    and L1.ew > 200
    		-- BIG Halpha
    and L2.ew > 10
    		-- significant Hbeta emission line
    and L2.ew * 20 < L1.ew
    		-- Hbeta is comparatively small

    Clean photometry with flags      Back to TopTop

    -- This query demonstrates the use of the photometry flags to select clean
    -- photometry for star and galaxy objects. Note that using these flag combinations
    -- may invoke the bookmark lookup bug if your query is searching a large fraction
    -- of the database. In that case, use the prescribed workaround for it as described on
    -- the SQL intro page.

    -- 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 below, you
    -- will get only primary objects, otherwise you will need to add a "mode=1" constraint.
    -- NOTE: The symbolic flag values are purposely replaced in the following examples by
    -- the hex values for the flag masks. This is for efficiency (see the Using dbo
    -- functions     section of the SQL Intro page).

    -- 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):

    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

    -- 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.

    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.

    Binary stars colors      Back to TopTop

    -- 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

    QSO broadlines near galaxy      Back to TopTop

    -- Find quasars with a broad absorption line and a nearby galaxy within 10arcsec.
    -- Return both the quasars and the galaxies.

    SELECT Q.BestObjID as Quasar_candidate_ID ,
    G.ObjID as Galaxy_ID, N.distance
    FROM SpecObj as Q -- Q is the specObj of the quasar candidate
       JOIN Neighbors as N ON Q.BestObjID = N.ObjID -- N is the Neighbors list of Q
       JOIN Galaxy as G ON G.ObjID = N.NeighborObjID -- G is the nearby galaxy
       JOIN SpecClass as SC ON Q.SpecClass =SC.value -- Spec Class
       JOIN SpecLine as L ON Q.SpecObjID = L.SpecObjID -- the broad line we are looking for
       JOIN SpecLineNames as LN ON L.LineID = LN.value -- Line Name
    WHERE
    SC.name in ('QSO', 'HIZ_QSO')
    		-- Spectrum says "QSO"
    and LN.Name != 'UNKNOWN'
    		-- and isn't not identified
    and L.ew < -10
    		-- but its a prominent absorption line
    and N.distance < 10.0/60
    		-- and it is within 10 arcseconds of the Q.

    Galaxies unsaturated near given location      Back to TopTop

    -- Find galaxies without saturated pixels within 1' of a given point (ra=185.0, dec=-0.5).
    -- This query uses a function fGetNearbyObjEq,which takes 3 arguments (ra,dec,
    -- distance in arcmin); this function uses the Neighbors table. The Neighbors and Galaxy
    -- tables have in common the objID, so we have to select objects from both where the
    -- objIDs are the same. The output of the function is a table with the Galaxy Object ID
    -- and distance in arcmin from the input. This query introduces the use of a JOIN to
    -- combine table contents. We also use the 'ORDER BY' syntax, which sorts the output.

    SELECT TOP 100 G.objID, GN.distance
    FROM Galaxy as G
    JOIN dbo.fGetNearbyObjEq(185.,-0.5, 1) AS GN -- this function outputs a table, so we have to do a join
    on G.objID = GN.objID -- get objects from neighbors table GN with desired ObjID
    WHERE (G.flags & dbo.fPhotoFlags('saturated')) = 0 -- and the object is not saturated. f.PhotoFlags is a function that interprets the flag bits.
    ORDER BY distance -- sort these by distance

    Ellipticals odd lines      Back to TopTop

    -- Find all elliptical galaxies with spectra that have an anomalous emission line.
    -- This query introduces the SQL syntax DISTINCT, which will return only one instance
    -- of the requested parameter (ObjID, in this case), because our query may return the
    -- same object more than once. This is also the first nested query, where we use one
    -- SELECT (the inner one) to get a group of objects we are not interested in. The outer
    -- SELECT includes the new syntax 'not in', which is used to perform the exclusion.

    SELECT DISTINCT G.ObjID
    FROM
       JOIN Galaxy as G
       JOIN SpecObj as S ON G.ObjID = S.bestObjID -- the galaxy has a spectrum
       JOIN SpecLine as L ON S.SpecObjID = L.SpecObjID -- L is a spectral line
       JOIN XCRedshift as XC ON S.SpecObjID = XC.SpecObjID -- cross-correlation redshift
    WHERE
    XC.tempNo = 8
    		-- template used is "elliptical"
    and L.lineID = 0
    		-- any line type is found
    and L.ew > 10
    		-- and the line is prominent by some
    -- definition; in this case, equivalent
    -- width is over 10 Angstroms
    and S.SpecObjID not in (
    		-- insist that there are no other lines
       SELECT S.SpecObjID
    		-- This is the chosen line.
       FROM SpecLine as L1
    		-- L1 is another line
       WHERE S.SpecObjID = L1.SpecObjID
    		-- for this object
          and abs(L.wave - L1.wave) <.01
    		-- at nearly the same wavelength
          and L1.LineID != 0
    		-- but with unknown line type
       )

    Broadest spectral lines      Back to TopTop

    -- What's the SQL to get the broadest line of each spectrum, together with its
    -- identification (or more generally, all the columns for the spectral line with the
    -- highest/lowest something)? (Sebastian Jester)
    -- Note: The previous version of this query was corrected by Gordon Richards.

    SELECT top 100
    sl.specObjId,
    sl.lineID,
    sMax.maxVel
    -- get the spectroscopic object ID, the line ID, and the max width (in velocity)
    FROM SpecLine AS sl
       JOIN (SELECT
          specObjID,
          lineID,
          Max(2.99e5*2.354*sigma/wave) as maxVel
       FROM SpecLine
       WHERE (
          -- only include measured lines
          category = 2 AND
          -- only include the most prominent lines
          lineID in (1215,1400,1549,1909,2799,4863,6565) AND
          -- make sure that this is a reaonably well measured line
          -- (these are fairly weak constraints)
          nSigma > 3 AND
          chisq/nu < 20 AND
          ew > 0 AND
          abs(ew - height*sigma*1.065*2.354/continuum)/ew < 0.25
       )
       GROUP BY specObjID, lineID) as sMax ON sl.specObjID = sMax.specObjID
    -- sMax contains a single specObjID, lineID and MaxWidth. Remember that for each
    -- line identified in a given object's spectrum, there is an entry in SpecLine.
    -- This means that each specObjID appears many times in SpecLine, once for each
    -- line found, and they must be aggregated before performing any operation such
    -- as Max. The GROUP BY is thus NECESSARY.
    --
    -- The upper FROM clause is telling us we will use the table SpecLine and this
    -- new output called sMax, which contains one entry with the SpecObjID, lineID,
    -- and maxVel. If we used some other function instead of 'Max', such as 'top 10',
    -- sMax would have more than row.
    WHERE
    sl.lineID = sMax.lineID
    -- Just as with the specObjID, each specLineID appears many times in specLine
    -- This final WHERE clause makes sure we get the one specLineID from SpecObj
    -- which matches the unique combination of specObjID and lineID in sMax.

    Gridded galaxy counts      Back to TopTop

    -- Gridded galaxy counts and masks. Actually consists of TWO queries:
    -- 1) Create a gridded count of galaxies with u-g > 1 and r < 21.5 over -1 < dec < 1,
    -- and 179 < R.A. < 181, on a grid, and create a map of masks over the same grid.
    -- Scan the table for galaxies and group them in cells 2 arc-minuteson a side. Provide
    -- predicates for the color restrictions on u-g and r and to limit the search to the
    -- portion of the sky defined by the right ascension and declination conditions. Return
    -- the count of qualifying galaxies in each cell.
    -- 2) Run another query with the same grouping, but with a predicate to include only
    -- objects such as satellites, planets, and airplanes that obscure the cell. The second
    -- query returns a list of cell coordinates that serve as a mask for the first query.

    --- First find the gridded galaxy count (with the color cut)
    --- In local tangent plane, ra/cos(dec) is a linear degree.

    SELECT cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0 as raCosDec,
    cast(dec*30 as int)/30.0 as dec,
    count(*) as pop
    FROM Galaxy as G
    JOIN dbo.fHTMCoverRegion('CHULL J2000 179 -1 179 1 181 1 181 -1') AS T
       ON G.htmID BETWEEN T.HTMIDstart AND T. HTMIDend
    WHERE ra between 179 and 181
    and dec between -1 and 1
    and u-g > 1
    and r < 21.5
    GROUP BY cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0,
    cast(dec*30 as int)/30.0

    -- now build mask grid.
    -- This is a separate query if no temp tables can be made

    SELECT cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0 as raCosDec,
    cast(dec*30 as int)/30.0 as dec,
    count(*) as pop
    FROM PhotoObj as PO
    JOIN dbo.fHTMCoverRegion('CHULL J2000 179 -1 179 1 181 1 181 -1') AS T
       ON PO.htmID BETWEEN T.HTMIDstart AND T.HTMIDend
    JOIN PhotoType as PT ON PO.type = PT.value
    WHERE
    ra between 179 and 181
    and dec between -1 and 1
    and PT.name in ('COSMIC_RAY', 'DEFECT', 'GHOST', 'TRAIL', 'UNKNOWN')
    GROUP BY cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0,
    cast(dec*30 as int)/30.0

    Galaxy counts on HTM grid      Back to TopTop

    -- 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.7*u - 0.5*g - 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.

    Stars multiply measured      Back to TopTop

    -- Find stars with multiple measurements with magnitude variations > 0.1. Note that
    -- this runs very slowly without the "TOP 100", so please see the Optimizing queries
    -- section of the SQL help page to learn how to speed up this query.

    SELECT TOP 100
    S1.objID as objID1, S2.objID as ObjID2
    		-- select object IDs of star and its pair
    FROM Neighbors as N -- the neighbor record
    JOIN Star as S1 ON S1.objID = N.objID
    		-- the primary star
    JOIN PhotoObj as S2 ON S2.objID = N.neighborObjID
    		-- the second observation of the star
    -- NOTE: We have used PhotoObj for the second star instead of the Star view,
    -- so it will match secondary observations too (Star view has primaries only).
    WHERE
    distance < 0.5/60
    		-- distance is 0.5 arc second or less
    and S1.run != S2.run
    		-- observations are two different runs
    and S2.type = dbo.fPhotoType('Star')
    		-- S2 is indeed a star
    and S1.u between 1 and 27
    		-- S1 magnitudes are reasonable
    and S1.g between 1 and 27
    and S1.r between 1 and 27
    and S1.i between 1 and 27
    and S1.z between 1 and 27
    and S2.u between 1 and 27
    		-- S2 magnitudes are reasonable.
    and S2.g between 1 and 27
    and S2.r between 1 and 27
    and S2.i between 1 and 27
    and S2.z between 1 and 27
    and (
    		-- and one of the colors is different.
       abs(S1.u-S2.u) > .1 + (abs(S1.Err_u) + abs(S2.Err_u))
       or abs(S1.g-S2.g) > .1 + (abs(S1.Err_g) + abs(S2.Err_g))
       or abs(S1.r-S2.r) > .1 + (abs(S1.Err_r) + abs(S2.Err_r))
       or abs(S1.i-S2.i) > .1 + (abs(S1.Err_i) + abs(S2.Err_i))
       or abs(S1.z-S2.z) > .1 + (abs(S1.Err_z) + abs(S2.Err_z))
    )

    White Dwarf candidates      Back to TopTop

    -- Select white dwarf candidates, returning the necessary photometric parameters,
    -- proper motion, spectroscopic information, and the distance to the nearest neighbor
    -- brighter than g=21. (From Jeff Munn)

    SELECT
    o.*, ISNULL(nbor.nearest,999) as nearest
    FROM
         (
    -- This selects the white dwarf candidates, meeting the following criteria
    -- 1) Stars with dereddened g magnitudes between 15 and 20
    -- 2) Proper motion > 2 arcsec/century
    -- 3) Meet either a reduced proper motion cut, or have dereddened g-i < 0
    -- A left outer join is also performed to fetch the spectroscopic information
    -- for those stars with spectra.
    SELECT p.objID,
          p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 AS rpm,
          p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) AS gi,
          p.psfMag_u, p.psfMag_g, p.psfMag_r, p.psfMag_i, p.psfMag_z,
          p.extinction_u, p.extinction_g, p.extinction_r, p.extinction_i,
          p.extinction_z,p.ra,p.dec,p.run,p.rerun,p.camcol,p.field,p.obj,
          p.status,p.flags,
          u.propermotion,
          ISNULL(s.specClass,0) as specClass, ISNULL(s.z,0) as z,
          ISNULL(s.zConf,0) as zConf, ISNULL(s.zWarning,0) as zWarning,
          ISNULL(s.plate,0) as plate, ISNULL(s.mjd,0) as mjd,
          ISNULL(s.fiberID,0) as fiberID
    FROM
          PhotoTag p JOIN USNO u ON p.objID = u.objID
          LEFT OUTER JOIN SpecObj s ON p.objID = s.bestObjID
    WHERE
          p.type = dbo.fPhotoType('Star')
          AND (p.flags & dbo.fPhotoFlags('EDGE')) = 0
          AND (p.psfMag_g - p.extinction_g) BETWEEN 15 AND 20
          AND u.propermotion > 2.
          AND (p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 >
               16.136 + 2.727 * (p.psfMag_g - p.extinction_g -
               (p.psfMag_i - p.extinction_i)) OR
          p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) < 0.)
          ) AS o
          LEFT OUTER JOIN
          (
    -- This fetches the distance to the nearest PRIMARY neighbor (limited to stars
    -- or galaxies) whose g magntiude is brighter than 21. To speed the query a bit,
    -- we limit the objects to bright PRIMARY stars brighter than 21, since that
    -- includes all the objects that we'll be joining to.
    SELECT n.objID, MIN(n.distance) AS nearest
    FROM Neighbors n JOIN PhotoTag x ON n.neighborObjID = x.objID
    WHERE n.type = dbo.fPhotoType('Star') AND
          n.mode = dbo.fPhotoMode('Primary') AND
          n.neighborMode = dbo.fPhotoMode('Primary') AND
          (x.type = dbo.fPhotoType('Star') OR x.type = dbo.fPhotoType('Galaxy'))
          AND x.modelMag_g BETWEEN 10 AND 21
    GROUP BY n.objID
          ) AS nbor ON o.objID = nbor.objID

    More quasar queries      Back to TopTop

    -- Here is a query to get object IDs and field MJDs (Modified Julian Dates) for quasars with secondary
    -- matches. (From Jordan Raddick)

    SELECT top 100
    p.objid as primary_objid,
    f.mjd_g as primary_mjd,
    q.objid as secondary_objid,
    g.mjd_g as secondary_mjd,
    p.ra, p.dec, p.modelmag_g as primary_g,
    q.modelmag_g as secondary_g, s.z as redshift
    FROM PhotoTag AS p
    JOIN Match AS m ON p.objid=m.objid1
    JOIN Phototag AS q ON q.objid=m.objid2
    JOIN SpecObj AS s ON p.objid=s.bestobjid
    JOIN Field f ON p.fieldID=f.fieldID
    JOIN Field g ON q.fieldID=g.fieldID
    WHERE
    (s.specclass=3 or s.specclass=4)
    ORDER BY p.modelmag_g

    -- Some more useful quasar queries (from Sebastian Jester).
    -- Getting magnitudes for spectroscopic quasars - retrieves BEST photometry.
    -- This query introduces 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.

    SELECT ra,dec,psfmag_i-extinction_i AS mag_i,psfmag_r-extinction_r AS mag_r,z
    FROM SpecPhoto
    WHERE zconf > 0.35
    AND (specclass = dbo.fSpecClass('QSO') OR specclass = dbo.fSpecClass('HIZ_QSO'))
    AND ra between 180 AND 210 AND dec between -10 AND 10
    -- Getting TARGET photometry for spectra

    SELECT sp.ra,sp.dec,sp.z,
    sp.psfmag_i-sp.extinction_i AS best_i,
    p.psfmag_i-p.extinction_i AS target_i
    FROM specphoto AS sp
    INNER JOIN TARGDR2..photoprimary AS p
    ON sp.targetobjid = p.objid
    WHERE sp.zconf > 0.35
    AND (specclass = dbo.fSpecClass('QSO') OR specclass = dbo.fSpecClass('HIZ_QSO'))
    -- Getting FIRST data for spectroscopic quasars - returns only those quasars that match

    SELECT sp.ra,sp.dec,sp.z,
    sp.psfmag_i-sp.extinction_i AS mag_i,
    match,peak,integr,rms,delta
    FROM SpecPhoto AS sp
    INNER JOIN first AS f ON sp.objid = f.objid
    WHERE sp.zconf > 0.35
    AND (specclass = dbo.fSpecClass('QSO') OR specclass = dbo.fSpecClass('HIZ_QSO'))
    -- Surface density of quasar targets and FIRST matches to them on a field-by-field basis
    -- restricted to some part of the sky.

    SELECT f.run,f.rerun,f.camcol,f.field,ra_avg,dec_avg,
    center_ra,center_dec,n_targets,n_match,area
    FROM (
    SELECT run,rerun,camcol,field,stripearea AS area,fieldid,
    (ramax+ramin)/2 AS center_ra, (decmax+decmin)/2 AS center_dec
    FROM Field
    WHERE (ramax+ramin)/2 between 160 AND 180
    AND (decmax+decmin)/2 between -10 AND 10
    AND quality = dbo.fFieldQuality('good')
    ) AS f
    INNER JOIN (
    SELECT count(*) AS n_targets, p.fieldid,
    AVG(p.ra) AS ra_avg, avg(p.dec) AS dec_avg,
    ISNULL(sum(fi.match),0) AS n_match
    FROM photoprimary AS p
    LEFT OUTER JOIN first AS fi
    ON p.objid = fi.objid
    WHERE ((p.primtarget & dbo.fPrimTarget('TARGET_QSO_CAP'))
    = dbo.fPrimTarget('TARGET_QSO_CAP'))
    GROUP BY fieldid
    ) AS p
    ON f.fieldid = p.fieldid
    ORDER BY ra_avg,dec_avg

    Using LEFT OUTER JOIN      Back to TopTop

    -- This query from Sebastian Jester demonstrates the use of the LEFT OUTER JOIN
    -- construct in order to include even rows that do not meet the JOIN condition. 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 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(stripearea) 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, sum(fm.match) 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

    Using multiple OUTER JOINs      Back to TopTop

    -- This query from Gordon Richards demonstrates the use of multiple OUTER JOINs
    -- It does take a few hours to run, hence the TOP 10 is added if you want to try it.

    SELECT TOP 10
    dbo.fSDSS(p.objId) as oid,
    p.objId,
    p.ra, p.dec,
    dbo.fHMS(p.ra) as raHMS,
    dbo.fDMS(p.dec) as decDMS,
    p.psfmag_u, p.psfmag_g, p.psfmag_r, p.psfmag_i, p.psfmag_z,
    p.psfmagerr_u, p.psfmagerr_g, p.psfmagerr_r, p.psfmagerr_i, p.psfmagerr_z,
    p.extinction_u, p.extinction_g, p.extinction_r, p.extinction_i,
    p.extinction_z,
    p.rowc,p.colc,
    p.type,
    p.mode,
    p.flags,
    p.flags_u,p.flags_g,p.flags_r,p.flags_i,p.flags_z,
    p.insideMask,
    p.primTarget,
    (p.primTarget & 0x00000001) as pthiz,
    (p.primTarget & 0x00000006) as ptlowz,
    (p.primTarget & 0x00000018) as ptfirst,
    s.z,
    s.zErr,
    s.zConf,
    s.zStatus,
    s.zWarning,
    s.specClass,
    s.plate,
    s.fiberID,
    s.mjd,
    s.sciencePrimary,
    str(fld.mjd_i,5,5) as mjdi,
    fld.quality,
    fld.culled,
    f.peak,
    r.cps
    FROM BESTDR3..SpecObjAll AS p WITH (index(0))
    LEFT OUTER JOIN SpecObj AS s ON p.objID = s.bestObjID
    LEFT OUTER JOIN FIRST AS f ON p.objID = f.objID
    LEFT OUTER JOIN ROSAT AS r ON p.objID = r.objID
    LEFT OUTER JOIN Field AS fld ON p.fieldID = fld.fieldID
    WHERE
    (
    (p.mode = 1) AND ((p.status & 0x10) > 0) AND
    (
    ((p.primTarget & 0x0000001f) > 0)
    OR
    ((s.specClass in (3,4)) AND (s.sciencePrimary = 1))
    ) )

    Searching for multiple spec lines     Back to TopTop

    -- A query from Tomo Goto that looks for several spec lines at once.
    SELECT TOP 100
    S.ObjID, S.ra, S.dec, S.z,
    'Ha_6565', L.ew, L.ewErr, L.continuum,
    'Hb_4863', L2.ew, L2.ewErr, L2.continuum,
    'OII_3727', L_OII.ew ,L_OII.ewErr,L_OII.continuum,
    'Hd_4103', L_Hd.ew ,L_Hd.ewErr,L_Hd.continuum
    FROM SpecPhoto AS S -- S is the spectra of galaxy G
    JOIN SpecLine AS L ON S.SpecObjID = L.SpecObjID
    		-- L is a line of S
    JOIN SpecLine AS L2 ON S.SpecObjID = L2.SpecObjID
    		-- L2 is a line of S
    JOIN SpecLine AS L_OII ON S.SpecObjID = L_OII.SpecObjID
    		-- L_OII is a line of S
    JOIN SpecLine AS L_Hd ON S.SpecObjID = L_Hd.SpecObjID
    		-- L_Hd is a line of S
    WHERE
    L.LineId = 6565
    		-- L is the H alpha line
    and L2.LineId = 4863
    		-- L2 is the H beta line
    and L_OII.LineId = 3727
    		-- L_OII is the O-II line
    and L_Hd.LineId = 4103
    		-- L_Hd is the H delta line

    Counting galaxies in North     Back to TopTop

    -- A query from Jon Loveday to count galaxies in the North.
    -- Galaxy number counts for northern Galactic hemisphere, ie. stripe < 50.
    --
    -- 262158 is the sum of the SATURATED, BLENDED, BRIGHT and EDGE flags,
    -- obtained with the query:
    --
    -- SELECT top 1 (dbo.fPhotoFlags('SATURATED')
    -- + dbo.fPhotoFlags('BLENDED')
    -- + dbo.fPhotoFlags('BRIGHT')
    -- + dbo.fPhotoFlags('EDGE')) from PhotoTag
    SELECT cast(2*(g.petroMag_r - g.extinction_r + 0.25) as int)/2.0 as r, 2*count(*) as N
    FROM galaxy g
    JOIN Field AS f ON f.fieldid=g.fieldid
    JOIN segment AS seg ON seg.segmentid=f.segmentid
    WHERE
    seg.segmentID = f.segmentID and f.fieldID = g.fieldID and
    seg.stripe < 50 and
    g.petroMag_r - g.extinction_r < 22 and
    (g.flags_r & 262158) = 0 and
    ((case when (g.type_g=3) then 1 else 0 end) +
    (case when (g.type_r=3) then 1 else 0 end) +
    (case when (g.type_i=3) then 1 else 0 end)) > 1
    GROUP BY cast(2*(g.petroMag_r - g.extinction_r + 0.25) as int)/2.0
    ORDER BY cast(2*(g.petroMag_r - g.extinction_r + 0.25) as int)/2.0

    Counts by type and program      Back to TopTop


    -- List the number of each type of object observed by each
    -- special program.
    SELECT plate.programname, dbo.fSpecClassN(specClass) AS Class,
    COUNT(specObjId) AS numObjs
    FROM SpecObjAll
    JOIN PlateX AS plate ON plate.plate = specObjAll.plate
    WHERE plate.programtype > 0
    GROUP BY plate.programname, specClass
    ORDER BY plate.programname, specClass

    Finding special plates that repeat spectra of objects in main survey      Back to TopTop


    -- A query to list the primary and special plates that have objects in common
    -- Returns the pairs of special and primary 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

    Special program targets      Back to TopTop


    -- A query to list the spec IDs and classifications of the primary
    -- targets of a special program, in this case fstar51.
    --
    -- Note that the flag may be different for other special programs
    SELECT specObjId, dbo.fSpecClassN(specClass) AS Class FROM SpecObjAll
    JOIN PlateX AS plate ON plate.plate = specObjAll.plate
    WHERE plate.programName LIKE 'fstar51' AND
    NOT ((primTarget & 0x80002000) = 0)

    Special program data      Back to TopTop


    -- Find redshifts and types of all galaxies
    -- in the lowz special program with z < 0.01
    SELECT specObjID, z, zErr, zConf, dbo.fSpecClassN(specClass)
    FROM SpecObjAll
    JOIN