wiki:db/queries/029
Last modified 7 years ago Last modified on 02/01/2013 06:23:56 AM

Find the brightness of the closest source within ? arcmin

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.decl, o.flags, o.type, o.objid,
          o.psfMag_g, o.psfMag_r, o.psfMag_i, o.gMag, o.rMag, o.iMag, 
          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      Object as o 
LEFT JOIN Neighbors as n on o.objid=n.objid, 
JOIN      Object 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   Object pp ON (nn.neighborObjId = pp.objectId)
               WHERE  nn.objectId = o.objectId 
               ORDER BY pp.r
                          )

This query originally came from SDSS (http://cas.sdss.org/dr5/en/help/docs/realquery.asp)

SDSS later used a smaller table PhotoTag instead of PhotoObjAll table to speed it up.