SkyServer Sample SQL Queries
 
DR7 Help
 Site News
 Introduction
 Cooking with Sloan
 FAQ
 
 Query Form Guide
 SQL Tutorial
 SQL in SkyServer
 Sample SQL Queries
 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 that
    -- are 'survey quality' so that I could plot up colour-colour 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.

    SELECT 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

    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 PlateX AS plate ON plate.plate = specObjAll.plate
    WHERE plate.programName LIKE 'lowz%' AND specClass = 2 AND z < 0.01

    Repeated high-z objects      Back to TopTop

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

    Spatial Queries with HTM functions      Back to TopTop

    -- 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. Some examples:

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

    -- This query can be rewritten as follows to use the HTM function that returns a
    -- rectangular search area:
    SELECT p.objID, p.ra, p.dec
    FROM SpecObjAll p, dbo.fGetObjFromRectEq(179.5, -1.0, 182.3, 1.8) r
    WHERE p.objID = r.objID

    -- 2) Radial search for objects near a given position (cone search):
    SELECT objid, ra, dec
    FROM PhotoTag p, dbo.fGetNearbyObjEq(179.5, -0.5, 2.0) n
    WHERE p.objID = n.objID

    Uniform Quasar Sample      Back to TopTop

    -- Determine area of sky targeted by v3_1_0 or later of the target selection algorithm
    -- Note that the "min" just happens to work, it is not robust to changes in the min value.
    -- (from Gordon Richards). This query also contains examples of setting the output
    -- precision of columns with STR.

    select sum(area)
    FROM Region
    where regionid in (
    select b.boxid
    FROM region2box b JOIN tilinggeometry g on b.id = g.tilinggeometryid
    where b.boxtype = 'SECTOR'
    and b.regiontype = 'TIPRIMARY'
    group by b.boxid
    having min(g.targetversion) >= 'v3_1_0'
    )

    -- Extract all quasars and quasar candidates from that area

    SELECT
    dbo.fSDSS(p.objId) as oid,
    p.objId,
    str(p.ra,10,6) as ra,
    str(p.dec,10,6) as dec,
    dbo.fHMS(p.ra) as raHMS,
    dbo.fDMS(p.dec) as decDMS,
    str(s.z,6,4) as z,
    r.area as regarea,
    r.regionid,
    ti.targetID,
    seg.segmentid,
    seg.photoVersion
    -- Start with list of all DR7 targeted objects (BESTDR7..Target)
    FROM Target AS t
    -- Need the region info to restrict to >=v3_1_0
    inner JOIN Region as r on t.regionid = r.regionid
    -- Need targetobjid to get photometry. Must get this from targetInfo table
    inner JOIN TargetInfo as ti on t.targetid = ti.targetid
    inner join TARGDR7..PhotoTag as p on ti.targetobjid = p.objid
    -- Pull out spectral information
    left outer join SpecObj as s on s.targetid = t.targetid
    -- I want to know the photoVersion, so we need to join the next two tables
    inner join TARGDR7..field as f on p.fieldid = f.fieldid
    inner join TARGDR7..segment as seg on f.segmentid = seg.segmentid
    WHERE
    (
    -- restrict objects to be in regions where target selection was >=v3_1_0
    r.regionid in (
    select b.boxid
    FROM region2box b JOIN tilinggeometry g on b.id = g.tilinggeometryid
    where b.boxtype = 'SECTOR'
    and b.regiontype = 'TIPRIMARY'
    group by b.boxid
    having min(g.targetversion) >= 'v3_1_0'
    )
    AND
    -- primary objects only, since we are selecting from PhotoTag
    ( (p.mode = 1) AND ((p.status & 0x10) > 0) AND ((p.status & 0x2000) > 0) )
    AND
    -- quasar candidates only
    ((p.primTarget & 0x0000001f) > 0)
    )
    ORDER by p.ra

    Merging Galaxy Pairs      Back to TopTop

    -- A query to search for merging galaxy pairs, as per the prescription in
    -- Allam et al. 2004.
    -- (from Jordan Raddick and Ani Thakar).
    -- Note: this query takes more than an hour to run without a top <n> or count(*).
    select count(*)
    /*
    g1.objid as g1_id, 
    g1.ra as g1_ra, g1.dec as g1_dec, 
    g1.modelmag_u as g1_u,  
    g1.modelmag_g as g1_g,  
    g1.modelmag_r as g1_r,  
    g1.modelmag_i as g1_i,  
    g1.modelmag_z as g1_z,
    g2.objid as g2_id, 
    g2.ra as g2_ra, g2.dec as g2_dec, 
    g2.modelmag_u as g2_u,  
    g2.modelmag_g as g2_g,  
    g2.modelmag_r as g2_r,  
    g2.modelmag_i as g2_i,  
    g2.modelmag_z as g2_z, 
    g1.petroR50_r as g1_radius, 
    g2.petroR50_r as g2_radius, 
    n.distance as separation
    */
    FROM Galaxyg1
        JOIN Neighbors n on g1.objID = N.objID
        JOIN Galaxy g2 on g2.objID = N.NeighborObjID
    where 
       g1.objId < g2.ObjID
       and N.NeighborType = 3
       and g1.petrorad_u > 0 and g2.petrorad_u > 0
       and g1.petrorad_g > 0 and g2.petrorad_g > 0
       and g1.petrorad_r > 0 and g2.petrorad_r > 0
       and g1.petrorad_i > 0 and g2.petrorad_i > 0
       and g1.petrorad_z > 0 and g2.petrorad_z > 0
       and g1.petroradErr_g > 0 and g2.petroradErr_g > 0
       and g1.petroMag_g BETWEEN 16 and 21
       and g2.petroMag_g BETWEEN 16 and 21
       and g1.modelmag_u > -9999
       and g1.modelmag_g > -9999
       and g1.modelmag_r > -9999
       and g1.modelmag_i > -9999
       and g1.modelmag_z > -9999
       and g2.modelmag_u > -9999
       and g2.modelmag_g > -9999
       and g2.modelmag_r > -9999
       and g2.modelmag_i > -9999
       and g2.modelmag_z > -9999
       and abs(g1.modelmag_g - g2.modelmag_g) > 3
       and (g1.petroR50_r BETWEEN 0.25*g2.petroR50_r AND 4.0*g2.petroR50_r)
       and (g2.petroR50_r BETWEEN 0.25*g1.petroR50_r AND 4.0*g1.petroR50_r)
       and (n.distance <= (g1.petroR50_r + g2.petroR50_r))
    

    Combining Ubercal and standard photometry     Back to TopTop

    -- Verify that ubercalibration was done on photometric data
    -- and obtain standard and ubercalibrated r band model magnitudes.

    select top 10 u.modelMag_r as urmag,p.modelMag_r as prmag
    from SpecObjAll p
    JOIN UberCal u
    where p.objID=u.objID AND
    u.calibStatus_r & dbo.fUberCalibStatus('PHOTOMETRIC') > 0

    Using the Spectro Parameter Pipeline sppLines table      Back to TopTop

    -- Select red stars (spectral type K) that are bright (g0 < 17), with
    -- large CaII triplet eq widths, with low errors on the CaII triplet eq
    -- widths.

    select sp.plate,sp.mjd,sp.fiberid,sp.g0,sp.gmr0,
    sl.caIIK,sl.caIIKerr,sl.caIIKmask,
    sp.feha,sp.fehaerr,sp.fehan,sp.logga,sp.loggaerr,sp.loggan
    FROM sppLines AS sl
    join sppParams as sp on sl.specobjid = sp.specobjid
    where
    feha < -3.5 and fehaerr between 0.01 and 0.5 and fehan > 3
    and g0 < 19

    Using the Spectro Parameter Pipeline sppParams table      Back to TopTop

    -- Select low metallicity stars ([Fe/H] < -3.5) which have relatively
    -- small error bars on the abundance (err < 0.5 dex) and which are brighter
    -- than 19th mag (dereddened) and where more than three different measures
    -- of feh are ok and are averaged.

    select sp.plate,sp.mjd,sp.fiberid,sp.g0,sp.gmr0,
    sl.caII,sl.caIIerr,sl.caIIa,sl.caIIaerr,
    sl.caIIb,sl.caIIberr,sl.caII+sl.caIIa+sl.caIIb as caIItripsum,
    sp.feha,sp.fehaerr,sp.fehan,sp.logga,sp.loggaerr,sp.loggan
    FROM sppLines AS sl
    join sppParams as sp on sl.specobjid = sp.specobjid
    where
    sl.CaII + sl.CaIIa + sl.CaIIb > 12 and
    sl.CaIImask = 1 and sl.CaIIamask =1 and
    sl.CaIIbmask = 1 and sl.caIIerr < 2 and
    sl.caIIaerr < 2 and sl.caIIberr < 2 and
    sl.caII > 0 and sl.caIIa > 0 and sl.caIIb > 0 and
    gmr0 between 0.8 and 1.1
    and g0 < 17

    Completeness of spectroscopic survey      Back to TopTop

    -- The following query can be used to find the completeness (in percent) of the
    -- spectroscopic survey by region.

    SELECT
    r.regionid,
    COUNT(CASE WHEN t.targetid != 0 THEN 1 ELSE NULL END) AS ntargets,
    COUNT(CASE WHEN s.specobjid != 0 THEN 1 ELSE NULL END) AS nspectra,
    ( CAST(COUNT(CASE WHEN s.specobjid != 0 THEN 1 ELSE NULL END) AS real) /
    CAST(COUNT(CASE WHEN t.targetid != 0 THEN 1 ELSE NULL END) AS real) ) AS pctCompleteness
    FROM Target AS t
    INNER JOIN TiledTarget AS tt ON t.targetid=tt.targetid
    INNER JOIN Region AS r ON t.regionid = r.regionid
    LEFT OUTER JOIN SpecObj AS s ON s.targetid = t.targetid
    WHERE
    r.regionid IN (
       SELECT b.boxid
       FROM region2box b
          JOIN tilinggeometry g ON b.id = g.tilinggeometryid
       WHERE b.boxtype = 'SECTOR'
           AND b.regiontype = 'TIPRIMARY'
       GROUP BY b.boxid
       HAVING MIN(g.targetversion) >= 'v3_1_0'
    )
    GROUP BY r.regionid

    Checking if objects are in SDSS footprint      Back to TopTop

    -- This query uses the new SQL Server 2005 CROSS APPLY and OUTER APPLY
    -- constructs to apply a table-valued function to the results of a query.
    -- In this example, we use the fFootPrintEq function which returns a list
    -- of SDSS regions that intersect the area specified by the RA, dec and
    -- radius, or NULL if the area is outside 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.

    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)

    Splitting 64-bit values into two 32-bit values      Back to TopTop

    -- 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 get upper 32 bits
    (flags/power(cast(2 as bigint),32)) & 0x00000000FFFFFFFF AS flags_hi
    FROM SpecObjAll
     
    -- Hexadecimal version of above query - use for debugging.
    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 SpecObjAll

    Variability Queries      Back to TopTop

    -- Several sample queries on searching for variable objects with the Match tables.
    -- (from Robert Lupton and Zeljko Ivezic).
    Zeljko put together a number of queries about variable objects.
    
    I've implemented them in SQL and appended my solutions to this
    message; I could do all except three; one (query8) because I couldn't
    think of a solution, and two because some of the fields in the matchHead
    tables are not set.
    
    I do NOT guarantee that these are optimal; in fact I don't quite
    guarantee that they are even correct.
    
    They are significantly complicated by three problems:
    	0. SQL doesn't include any operators to calculate
    order statistics such as medians
    
    	1.  The match tables only give the matches. This means
    that they include all except one of the observations of a given
    object.  I have a workaround in the queries, but maybe we should
    add the self-match rows too?  I've flagged them as distance == -1"
    
    	2. The matchHead tables do not guarantee that the objId
    of the head is a PRIMARY.  This is a problem when using some of the
    the functions that return matches, as some (but not all) only return
    primaries. For example, fGetNearbyObjEq() does but fGetObjFromRect()
    doesn't,
    
    I think that the best solution would be to make all such dbo.functions
    return all objects -- it's easy to say e.g.
    	(select objId from dbo.fGetObjFromRect(ra1, ra2, dec1, dec2)
    					where mode = @primaryMode) as N
    
    	3. The issues with matchHead.missCount; but this is a separate
    issue (which is why I said three problems, not four).
    
    					R
    
    -- N.b. This is designed to be used with my emacs interface; other users
    -- may have to evaluate the set statements for @variables, and
    -- substitute them by hand.
    
    /*
    
    The crucial table for variability is the MATCH table:
    
    PhotoObj pairs from different runs (times) that probably are the same
    object. SDSS primary and secondary objects of type STAR, GALAXY, and
    UNKNOWN within 1 arcsec of one another and from different runs (so two
    different observations) Table also holds type, mode and distance
    information.  See also MatchHead Table.
    
    name		     |unit        |description
    ---------------------------------------------------------------------------
    objID1               |            | The unique objId of the center object
    mode1                |            | is object primary,secondary,family
                         |            |    outside
    type1                |            | Object type of the start object
    objID2               |            | The objId of the matching object
    mode2                |            | is neighbor primary,secondary,family or 
                         |            |    outside
    type2                |            | Object type of the matching object
    distance             | arcmin     | Distance between center and neighbor
    ---------------------------------------------------------------------------
    */
    
    -- Note: the following variable declarations can be used in CasJobs or
    -- the emacs interface, but not in SkyServer.  If you are running the
    -- queries in SkyServer, you will need to replace the variables with
    -- their actual values.
    
    declare @r numeric        set @r = select 1/60.0 -- search radius, arcmin
    
    declare @ra numeric       set @ra =  217.924624	 -- a QSO
    declare @dec numeric      set @dec = -1.2251894
    
    declare @box numeric      set @box = 0.1
    declare @ra1 numeric      set @ra1 = select @ra - @box/2.0
    declare @dec1 numeric     set @dec1 = select @dec - @box/2.0
    declare @ra2 numeric      set @ra2 = select @ra + @box/2.0
    declare @dec2 numeric     set @dec2 = select @dec + @box/2.0
    
    declare @bad_flags bigint   set @bad_flags = (dbo.fPhotoFlags('SATURATED') | 
    		dbo.fPhotoFlags('BRIGHT') | dbo.fPhotoFlags('EDGE') | 
                    dbo.fPhotoFlags('PSF_FLUX_INTERP'))
    declare @star int	  set @star = dbo.fPhotoType('star')
    declare @galaxy int	  set @galaxy = dbo.fPhotoType('galaxy')
    
    declare @primaryMode int   set @primaryMode = dbo.fPhotoMode('primary')
    
    -- query1
    
    -- 1) Return all detections within R arcsec from a position given
    --    by (RA,Dec). Motivation: light curve for a given object
    
    --
    -- I assume that R is small enough to lie within the match table radius,
    -- so I can first find the object, then use the match table to get the
    -- light curve
    --
    
    select
       dbo.fSDSS(obj.objId) as ID,
       ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
       ltrim(str(mjd_r,20,2)) as MJD,
       modelMag_g, modelMagErr_g,
       modelMag_r, modelMagErr_r,
       modelMag_i, modelMagErr_i
    from
       (
          select objId1, objId2, -1/60. as distance FROM match
          union all
          select objId1, objId2, distance FROM match
       ) as M
       JOIN PhotoObj as Obj on (obj.objId = M.objId2)
       JOIN Field as F on F.fieldId = obj.fieldId
    where
       M.ObjId1 = dbo.fGetNearestObjIdEq(@ra, @dec, @r)
    
    /*
    Notes:
    
    The basic query is:
    
    select
       dbo.fSDSS(obj.objId) as ID
    from
       match as M join
       PhotoObj as Obj on (obj.objId = M.objId2)
    where
       M.objId1 = dbo.fGetNearestObjIdEq(@ra, @dec, @r)
    
    This is simple enough; find the object nearest to @ra, @dec, look it
    up in the match table, and then show us all of its matches. There's a
    problem with this approach, namely that it doesn't include the matched
    object itself, only its matches.
    
    The solution to this is to use not MATCH but a home-brewed temporary
    table (what's the proper word for these?) that includes an entry with
    the objid2 equal to the objID.  We should probably fix the
    database to do this for us.  With this change, the query becomes:
    
    select
       dbo.fSDSS(obj.objId) as ID
    from
       (
          select objId1, objId2, -1/60. as distance FROM match
          union all
          select objId1, objId2, distance FROM match
       ) as M join
       PhotoObj as Obj on (obj.objId = M.objId2)
    where
       M.objId1 = dbo.fGetNearestObjIdEq(@ra, @dec, @r)
    
    and the query given above is merely an elaboration of this approach.
    */
    
    --------------------------------------------------------------------------
    
    -- query2
    
    -- 2) Return all detections within a rectangle specified by equatorial
    --    or galactic coordinates; index all unique sources, provide the
    --    total number of detections for each source, index detections
    --    according to time (run number). Motivation: doing light curves
    --    for a large number of sources
    
    --
    -- I assume that R is small enough to lie within the match table radius,
    -- so I can first find the object, then use the match table to get the
    -- light curve
    --
    -- query2a
    
    select
       dbo.fSDSS(M.objId1) as UID,	 -- These are the unique object IDs
       dbo.fSDSS(obj.objId) as ID,
       ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
       ltrim(str(mjd_r,20,2)) as MJD,
       psfMag_r, psfMagErr_r,
       dbo.fPhotoTypeN(type) as type
    from
       (
          select objId1, objId2, -1/60. as distance FROM match
          union all
          select objId1, objId2, distance FROM match
       ) as M
       JOIN PhotoObj as Obj on (obj.objId = M.objId2)
       join (select objId 
             from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2) 
             where mode = @primaryMode) as N on (M.objId1 = N.objId1)
       JOIN Field as F on (F.fieldId = obj.fieldId)
    where
       0 = (flags & @bad_flags)
    order by M.objId1
    
    /*
    Without the selection on PRIMARY mode we'd double count matches
     */
    
    -- query2b
    
    /*
    Here's another way to do the same thing, using the matchHead table. Here
    the UID is the entry in the matchHead, and is in not in general a primary.
    
    This approach won't work using table-valued functions that only return
    primaries, such as fGetNearbyObjEq(), as not all the entries in
    matchHead are primaries
    */
    
    select
       dbo.fSDSS(MH.objId1) as UID,	 -- These are the unique object IDs
       dbo.fSDSS(obj.objId) as ID,
       ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
       ltrim(str(mjd_r,20,2)) as MJD,
       psfMag_r, psfMagErr_r,
       dbo.fPhotoTypeN(obj.type) as type
    from
       matchHead as MH
       join (
          select objId1, objId2, -1/60. as distance FROM match
          union all
          select objId1, objId2, distance FROM match
       ) as M on (M.objId1 = MH.objID1)
       JOIN PhotoObj as Obj on (obj.objId = M.objId2)
       JOIN Field as F on (F.fieldId = obj.fieldId)
    where
       MH.objId1 in (select objId 
                    from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2))
       and 0 = (flags & @bad_flags)
    order by MH.objId1
    
    -- query3
    -- query3a
    
    -- 3) Return all objects with difference between the brightest and
    --    faintest state (in a given band, or any band), or between
    --    one of the extreme states and the mean/median value, larger/smaller
    --    than X mag. Motivation: objects with impeccable photometry,
    --    eclipsing binaries, outbursts, etc.
    
    --
    -- I'll ask for the min value to differ from the mean by more than 0.1,
    -- and add another constraint that the mean magnitude be brighter than
    -- some limit. Only search a small region, and only consider stars
    
    -- The extra "select * from" is there so that I can use the calculated
    -- quantities in a "where" clause.
    
    select * from (
       select
          dbo.fSDSS(min(M.objId1)) as UID, -- These are the unique object IDs
          count(*) as n,
          min(obj.psfMag_r) as psfMagMin_r,
          avg(obj.psfMag_u) as psfMag_u,
          avg(obj.psfMag_g) as psfMag_g,
          avg(obj.psfMag_r) as psfMag_r,
          avg(obj.psfMag_i) as psfMag_i,
          avg(obj.psfMag_z) as psfMag_z,
          avg(obj.psfMagErr_r)/sqrt(count(*)) as psfMagErr_r,
          avg(obj.psfMag_r) - min(obj.psfMag_r) as psfDelta_r
       from
          (
             select objId1, type1, objId2, -1/60. as distance
    	 FROM match
    	 union all
    	 select objId1, type1, objId2, distance
    	 FROM match
          ) as M join
          PhotoObj as Obj on (obj.objId = M.objId2)
       where
          M.objId1 in (select objId 
                      from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
                      where mode = @primaryMode)
          and 0 = (flags & @bad_flags) and psfMagErr_r >= 0
          and M.type = @star
       group by M.objId1
    ) as RESULT
    where
       psfMag_r < 20 and
       psfMag_r - psfMagMin_r > 0.05
    order by psfMag_r - psfMagMin_r desc
    
    -- query3b
    
    /*
     * And here's the same query using a median. As Jim Gray says,
     * "The median SHOULD be in SQL but it isn't"; this causes real pain.
     *
     * The pain is made worse as correlated subqueries are only allowed with
     * a scalar value, and the natural way to write the median query uses a
     * table value; I circumvent this by generating an table with medians,
     * and joining it back.
     */
    select * from (
       select
          dbo.fSDSS(M.objId1) as UID,     -- These are the unique object IDs
          count(*) as n,
          min(obj.psfMag_r) as psfMagMin_r,
          avg(obj.psfMag_r) as psfMag_r,
          avg(obj.psfMagErr_r)/sqrt(count(*)) as psfMagErr_r,
          min(psfMagMed_r) as psfMagMed_r,
          min(psfMagMed_r) - min(obj.psfMag_r) as psfDelta_r
          ,dbo.fGetUrlNavId(M.objId) as URL
       from
          (
             select objId1, type1, objId2, -1/60. as distance
    	 FROM match
    	 union all
    	 select objId1, type1, objId2, distance
    	 FROM match
          ) as M join
          PhotoObj as Obj on (obj.objId = M.objId2) join
          (
             select
    	    M50.objId1,
    	    (select cast(min(psfMag_r) as float) 
                   -- the median, assuming n is odd
    	     from (
    		  select top 50 percent
    		     psfMag_r
    		  FROM PhotoObj AS iobj
    		  where iobj.objId in
    		     (select M50.objId1 union
    		     select objId2 FROM match AS MM 
                         where M50.objId1=MM.objId1) and
    		        0 = (iobj.flags & @bad_flags)
    		  order by psfMag_r desc
    	       ) as top50
    	    ) as psfMagMed_r
    	 from
    	    match as M50
          ) as med on med.objId1 = M.objId1
       where
          M.objId1 in (select objId 
    	          from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
              where mode = @primaryMode)
          and 0 = (flags & @bad_flags)
          and M.type = @star
       group by M.objId1
    ) as RESULT
    -- /*
    where
       psfMagMed_r < 20 and
       psfMagMed_r - psfMagMin_r > 0.05
    -- */
    order by psfMag_r - psfMagMin_r desc
    
    -- query4
    
    -- 4) Return all objects with rms in a given band greater/smaller
    --    than X mag. Motivation: a part of classic variability search
    
    --
    -- I'll ask for the rms to be less than 0.02, and only
    -- search a small region.
    --
    
    select * from (
       select
          dbo.fSDSS(M.objId1) as UID,  -- These are the unique object IDs
          count(*) as n,
          avg(psfMag_r) as psfMag_r,
          stdevp(psfMag_r) as rms_r,  -- Not in ANSI SQL 92
          avg(psfMagErr_r) as psfMagErr_r
       from
          (
             select objId1, objId2, -1/60. as distance FROM match
    	 union all
    	 select objId1, objId2, distance FROM match
          ) as M join
          PhotoObj as Obj on (obj.objId = M.objId2)
       where
          M.objId1 in (select objId 
    	          from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
            where 
              mode = @primaryMode)
          and 0 = (flags & @bad_flags)
          and psfMag_r < 20
       group by M.objId1
    ) as RESULT
    where
       rms_r > 0.05			 -- n.b. psfMagErr^2 isn't subtracted
    order by rms_r desc
    
    -- query5
    -- query5a
    
    -- 5) Return all objects with chi2 in a given band greater/smaller
    --    than Y. Motivation: a part of classic variability search
    --
    -- I'll only search a small region.
    --
    
    select * from (
       select
          dbo.fSDSS(UID) as UID,
          avg(ra) as ra, avg(dec) as dec,
          avg(mean.psfMag_r) as psfMag_r,
          count(*) as n,
          sum(square((obj.psfMag_r - mean.psfMag_r)/obj.psfMagErr_r))/count(*) 
             as rchi2_psfMag_r
       from
          (
             select
    	    iM.objId1 as UID,	-- These are the unique object IDs
    	    avg(psfMag_r) as psfMag_r
    	 from
    	    (
                   select objId1, type1,
                          objId2, -1/60. as distance 
                   FROM match
    	       union all
    	       select objId1, type1, objId2, distance FROM match
    	    ) as iM join
    	    PhotoObj as iobj on (iobj.objId = iM.objId2)
    	 where
    	    iM.objId1 in
    	    (select objId 
    	     from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
                 where mode = @primaryMode)
                and iM.type1 = @star
    	    and 0 = (iobj.flags & @bad_flags)
    	 group by iM.objId
          ) as mean join
          (
             select objId1, objId2, -1/60. as distance
    	 FROM matchwhere mode1 = @primaryMode
    	 union
    	 select objId1, objid2, distance
    	 FROM matchwhere mode1 = @primaryMode
          ) as M on mean.UID = M.objId1 join
          PhotoObj as Obj on (obj.objId = M.objId2)
       where
          0 = (flags & @bad_flags)
       group by UID
    ) as RESULT
    where
       psfMag_r < 20 and
       n > 1 and rchi2_psfMag_r > 2
       
    -- query5b
    --
    -- This query doesn't work:
    --	Cannot perform an aggregate function on an expression containing an
    --	aggregate or a subquery
    --
    select
       dbo.fSDSS(min(M.objId1)) as UID,
       count(*) as n,
       sum(square((obj.psfMag_r - avg(psfMag_r))/obj.psfMagErr_r))/count(*)
          as rchi2_psfMag_r
    from
       (
          select distinct objId1, objId2, -1/60. as distance
          FROM match
          union
          select objId1, objid2, distance FROM match
       ) as M join
       PhotoObj as Obj on (obj.objId = M.objid2)
    where
       M.objId1 in
       (select objId from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)) and
       0 = (flags & @bad_flags)
    group by M.objId1
    
    -- query6
    -- 6) Return all objects with (max-min) in a given band greater/smaller
    --   than k*rms. Motivation: another way to look for variables (using
    --    a different light curve statistic)
    --
    -- I'll only search a small region.
    --
    
    select * from (
       select
          dbo.fSDSS(M.objID1) as UID,
          avg(ra) as ra, avg(dec) as dec,
          avg(psfMag_r) as psfMagMean_r,
          min(psfMag_r) as psfMagMin_r,
          max(psfMag_r) as psfMagMax_r,
          -- Two alternative estimates of "RMS"; from the data, or
          -- from the quoted errors.
          -- stdevp(psfMag_r) as psfMagRMS_r, -- Not in ANSI SQL 92
          sqrt(sum(power(psfMagErr_r, 2))/count(*)) as psfMagRMS_r,
          count(*) as n
       from
          (
             select objId1, type1, objId2, -1/60. as distance 
             FROM match
    	 union all
    	 select objId1, type1, objId2, distance FROM match
          ) as M join
          PhotoObj as Obj on (obj.objId = M.objId2)
       where
          M.objId1 in
          (select objId from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
           where mode = @primaryMode)
          and M.type1 = @star
          and 0 = (flags & @bad_flags)
       group by M.objId1
    ) as RESULT
    where
       (psfMagMax_r - psfMagMin_r) > 2.5*psfMagRMS_r
       and psfMagMean_r < 22
    order by psfMagMean_r
    
    -- query7
    
    -- 7) Return all objects with (q75-q50) in a given band greater/smaller
    --    than k*(q50-q25), where qN are quartiles. Motivation: another
    --    way to look for variables (using a different light curve statistic)
    
    -- N.b. match.objId are not unique, so without both "select distinct"
    -- and "union" (not "union all") while looking for percentiles we'll
    -- get double counting.
    
    select * from (
       select
          dbo.fSDSS(M.objId1) as UID,    -- These are the unique object IDs
          count(*) as n,
          min(obj.psfMag_r) as psfMagMin_r,
          avg(obj.psfMag_r) as psfMag_r,
          avg(obj.psfMagErr_r)/sqrt(count(*)) as psfMagErr_r,
          min(psfMagQ25_r) as psfMagQ25_r,
          min(psfMagQ50_r) as psfMagQ50_r,
          min(psfMagQ75_r) as psfMagQ75_r,
          min(psfMagQ75_r) - min(psfMagQ25_r) as psfIQR_r
    --      ,dbo.fGetUrlNavId(M.objId) as URL
       from
          (
             select objId1, type1, objId2, -1/60. as distance 
             FROM match
    	 union all
    	 select objId1, type1, objId2, distance FROM match
          ) as M join
          PhotoObj as Obj on (obj.objId = M.objId2) join
          (
             select distinct
    	    M25.objId1,
    	    (  select
                      cast(min(psfMag_r) as float) -- Q75
    	       from (
    		  select top 75 percent	 -- not 25%
    		     psfMag_r
    		  FROM PhotoObj AS iobj
    		  where iobj.objId in
    		     (select M25.objId1 union
    		     select objId2 FROM match AS MM 
                         where M25.objId1=MM.objId1) and
    		        0 = (iobj.flags & @bad_flags)
    		  order by psfMag_r desc	 -- magnitudes!
    	       ) as top25
    	    ) as psfMagQ25_r
    	 from
    	    match as M25
          ) as Q25 on Q25.objId1 = M.objId1 join
          (
             select distinct
    	    M50.objId1,
    	    (  select
                      cast(min(psfMag_r) as float)	-- Q50
    	       from (
    		  select top 50 percent
    		     psfMag_r
    		  FROM PhotoObj AS iobj
    		  where iobj.objId in
    		     (select M50.objId union
    		     select matchObjId FROM match AS MM 
                         where M50.objId=MM.objId) and
    		        0 = (iobj.flags & @bad_flags)
    		  order by psfMag_r desc	 -- magnitudes!
    	       ) as top50
    	    ) as psfMagQ50_r
    	 from
    	    match as M50
          ) as Q50 on Q50.objId1 = M.objId1 join
          (
             select distinct
    	    M75.objId1,
    	    (  select
                      cast(min(psfMag_r) as float)	-- Q25
    	       from (
    		  select top 25 percent	 -- not 75%
    		     psfMag_r
    		  FROM PhotoObj as iobj
    		  where iobj.objId in
    		     (select M75.objId1 union
    		     select objId2 FROM match AS MM 
                         where M75.objId1=MM.objId2) and
    		        0 = (iobj.flags & @bad_flags)
    		  order by psfMag_r desc	 -- magnitudes!
    	       ) as top75
    	    ) as psfMagQ75_r
    	 from
    	    match as M75
          ) as Q75 on Q75.objId = M.objId
       where
          M.objId1 in (select objId 
                      from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
                      where mode = @primaryMode)
          and 0 = (flags & @bad_flags)
          and M.type = @star
       group by M.objId1
    ) as RESULT
    -- /*
    where
       psfMagQ50_r < 21 and
       (psfMagQ75_r - psfMagQ50_r) > 0.0*(psfMagQ50_r - psfMagQ25_r)
    -- */
    order by psfMag_r - psfMagMin_r desc
    
    -- query8
    
    -- 8) Return all objects with at least one brightness derivative
    --    (m2-m1)/(t2-t1) in a given band greater than X mag. Motivation:
    --    another way to look for variables (e.g. Mira variables have huge
    --    light curve amplitudes, but never a large time derivatives; RR Lyrae
    --    on the other hand have much smaller light curve amplitudes, but much
    --    larger time derivatives).
    
    --
    -- RHL can't think of a way to do this one. Ideas anyone?
    --
    
    -- query9
    
    -- 9) Return all objects that at least once were not detected (that is,
    --    their position was observed at least twice, and at least once
    --    there was no detection). Motivation: photo problems, eclipsing
    --    binaries
    
    --
    -- We can't do this (yet) as matchHead.missCount isn't set;
    -- well, not without understanding the HTM. The outline of
    -- a possible query is given below.
    --
    -- Even then we won't do a great job with the current schema; I think
    -- that we'd do better to include some sort of place-holder for these
    -- missing objects in the match tables.
    --
    select
       dbo.fSDSS(MH.objId1) as UID,	 -- These are the unique object IDs
       dbo.fSDSS(obj.objId) as ID,
       ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
       ltrim(str(mjd_r,20,2)) as MJD,
       psfMag_r, psfMagErr_r,
       dbo.fPhotoTypeN(obj.type) as type
    from
       matchHead as MH
       join (
          select objId1, objId2, -1/60. as distance FROM match
          union all
          select objId1, objId2, distance FROM match
       ) as M on (M.objId1 = MH.objID)
       JOIN PhotoObj as Obj on (obj.objId = M.objId2)
       JOIN Field as F on (F.fieldId = obj.fieldId)
    where
       MH.objId1 in (select objId 
                    from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2))
       and MH.missCount > 0
       and 0 = (flags & @bad_flags)
       and psfMag_r < 20
    order by MH.objId1
    
    -- query10
    
    -- 10) Return all objects that were detected only m times (or only within
    --     T1 days), while their position was observed at least N times
    --     (m < N ),  or longer than T2 days (T1 < T2).
    --     Motivation: afterglows, microlensing, SNe
    
    --
    -- Need matchHead.missCount. 
    --
    
    -- query11
    
    -- 11) Like 10, but object must be an unresolved child, and must have
    --     at least one resolved sibling. Motivation: SNe in nearby galaxies
    
    --
    -- Need matchHead.missCount
    --
    
    -- query12
    
    -- 12) Return all resolved objects whose rms for model magnitude in
    --     a given band is smaller than X mag, while rms for psf magnitude
    --     is larger than Y mag. Motivation: finding nearby galaxies with AGN
    --     by nuclear variability
    
    --
    -- This won't work, as the psf mag of an extended object depends
    -- on the seeing. But the query can be written.
    --
    -- I'll only search a small region, and correct the RMS errors for
    -- the measured errors
    --
    
    select * from (
       select
          dbo.fSDSS(M.objID1) as UID,
          --avg(ra) as ra, avg(dec) as dec,
          count(*) as n,
          --
          avg(psfMag_r) as psfMagMean_r,
          stdevp(psfMag_r) as psfMagRMS_r, -- Not in ANSI SQL 92
          sqrt(sum(power(psfMagErr_r, 2))/count(*)) as psfMagErr_r,
          --
          avg(modelMag_r) as modelMagMean_r,
          stdevp(modelMag_r) as modelMagRMS_r, -- Not in ANSI SQL 92
          sqrt(sum(power(modelMagErr_r, 2))/count(*)) as modelMagErr_r
       from
          (
             select objId1, type1, objId2, -1/60. as distance FROM match
    	 union all
    	 select objId1, type1, objId2, distance FROM match
          ) as M 
          JOIN PhotoObj as Obj on (obj.objId = M.objId2)
       where
          M.objId1 in
          (select objId from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2) 
           where mode = @primaryMode)
          and M.type = @galaxy
          and 0 = (flags & @bad_flags)
       group by M.objId1
    ) as RESULT
    where
       modelMagMean_r < 22 and
       (square(modelMagRMS_r) - square(modelMagErr_r)) < square(0.02) and
       (square(psfMagRMS_r) - square(psfMagErr_r)) > square(0.2)
    order by modelMagMean_r
    
    

    Back to TopBack to Top


    Ani Thakar
    Last Modified: Aug 04, 2008