Appendix

Test Queries for Benchmark

We used a set of 25 different test queries for the performance benchmarks discussed in §8.  These are the queries Q1-Q30 listed below (Q31 is a modified version of Q30).   The query numbering is not consecutive because this is a subset of 35 test queries that we devised to test the SQL Server’s data mining capabilities and query performance in a previous paper.  The queries from the original set that could not be converted to SXQL at all were dropped (e.g., Q7, Q12, Q13), and where possible SQL-specific features were excluded to make the SQL and SXQL versions equivalent.  In such cases, the excluded SQL code is shown highlighted below.  Both the SQL Server (SQL) and the OODBMS (SXQL) versions are shown side-by-side.  The SQL Server versions include a workaround for the SQL Server 2000 optimizer problem that causes the performance to degrade when BIGINT flags are included in the query predicate.  This is a known bug that will be fixed in the next version.

1.       Q1: Find all galaxies without saturated pixels within 1' of a given point (ra=75.327, dec=21.023).

Q1.SQL

SELECT G.objID, GN.distance 
FROM Galaxy as G,

   JOIN  dbo.fGetNearbyObjEq(200,-0.5,1) as GN
   on G.objID = GN.objID
WHERE

   (G.flags & dbo.fPhotoFlags(‘saturated’) = 0 

order by distance                           

Q1.SXQL

SELECT 
   objID,
   RA(), DEC(),
   objFlags & OBJECT_SATUR
FROM  Galaxy
WHERE
   PROX( J2000, 200, -0.5, 1.0)
   && (objFlags & OBJECT_SATUR) = 0

2.       Q2: Find all galaxies with blue surface brightness between and 23 and 25 mag per square arcseconds, and -10<super galactic latitude (sgb) <10,  and declination less than zero.

Q2.SQL

SELECT objID
FROM Galaxy 
WHERE 
 ra BETWEEN 160 AND 180 AND dec < 0
 AND (g+rho) BETWEEN 23 AND 25 

Q2.SXQL

SELECT objID
FROM Galaxy
WHERE 
  RA() BETWEEN 160 AND 180 && DEC() < 0.0
  && (g+rho) BETWEEN 23 AND 25

3.       Q3: Find all galaxies brighter than magnitude 22, where the local extinction is >0.175.                   

Q3.SQL

SELECT objID
FROM Galaxy 
WHERE 
  r + reddening_r < 22 AND reddening_r > 0.175

Q3.SXQL

SELECT objID
FROM Galaxy 
WHERE 
  r < 22 AND reddening[2] > 0.175  

4.       Q4: Find galaxies with an isophotal surface brightness (SB) > 24 in the red band, with an ellipticity >0.5, and with the major axis of the ellipse having a declination of between 30 and 60 arc seconds.

Q4.SQL

SELECT ObjID
FROM Galaxy
WHERE

  r + reddening_r + rho < 24
  AND  isoA_r BETWEEN 30 AND 60
  AND  (power(q_r,2) + power(u_r,2)) > 0.25 

Q4.SXQL

SELECT objID
FROM PhotoPrimary 
WHERE 
  objType == OBJECT_GALAXY &&
  modelMag[2] + rho < 24
  AND  isoA[2] BETWEEN 30 AND 60
  AND  (q[2]*q[2]) + (u[2]*u[2]) > 0.25


5.       Q5: Find all galaxies with a deVaucouleours profile (rĽ falloff of intensity on disk) and the photometric colors consistent with an elliptical galaxy.

Q5.SQL

DECLARE @binned    BIGINT;
SET     @binned = dbo.fPhotoFlags('BINNED1') +
        dbo.fPhotoFlags('BINNED2') +
        dbo.fPhotoFlags('BINNED4') ;
DECLARE @blended   BIGINT;
SET     @blended = dbo.fPhotoFlags('BLENDED');
DECLARE @noDeBlend BIGINT;
SET     @noDeBlend = dbo.fPhotoFlags('NODEBLEND');
DECLARE @child     BIGINT;
SET     @child =  dbo.fPhotoFlags('CHILD');
DECLARE @edge      BIGINT;
SET     @edge =  dbo.fPhotoFlags('EDGE');
DECLARE @saturated BIGINT;
SET     @saturated =  dbo.fPhotoFlags('SATURATED');
SELECT ObjID
 FROM Galaxy as G   -- count galaxies
 WHERE lDev_r > 1.1 * lExp_r -- red DeVaucouler fit likelihood greater than disk fit 
   AND  lExp_r > 0  -- exponential disk fit likelihood in red bAND > 0
 -- Color cut for an elliptical galaxy courtesy of James Annis of FermiLab
       AND (G.flags & @binned) > 0 
   AND (G.flags & ( @blended + @noDeBlend + @child)) != @blended
  AND (G.flags & (@edge + @saturated)) = 0 
    AND G.petroMag_i > 17.5
    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.reddening_r) < 19.2
        AND (G.petroMag_r - G.reddening_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.reddening_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 ) ) ) ) )

Q5.SXQL

SELECT objID
FROM Galaxy
WHERE
  lDeV_r > 1.1 * lExp_r
  && lExp_r > 0
// Color cut for an elipical galaxy courtesy of James Annis of Fermilab
  && (objFlags & (OBJECT_BINNED1 | OBJECT_BINNED2 | OBJECT_BINNED4)) > 0 
  && (objFlags & (OBJECT_BLENDED | OBJECT_NODEBLEND | OBJECT_CHILD)) != OBJECT_BLENDED
  && (objFlags & (OBJECT_EDGE | OBJECT_SATUR)) == 0 
  && petroMag[3] > 17.5
    && (petroMag[2] > 15.5 || petroR50_r > 2)
        && (petroMag[2] > 0 && g>0 && r>0 && i>0)
        && ((petroMag[2]- reddening[2]) < 19.2
        && (petroMag[2] - reddening[2] < (13.1 + 
                (7/3)*( g - reddening[1] - r + reddening[2] ) + 
                 4 *( r -reddening[2] - i + reddening[3] ) - 4 * 0.18 )
               ) 
           && ( ( r - reddening[2] - i + reddening[3] - 
       ( g - reddening[1] - r + reddening[2])/4 - 0.18 ) < 0.2 )
           && ( ( r - reddening[2] - i + reddening[3] - 
       ( g - reddening[1] - r + reddening[2])/4 - 0.18 ) > -0.2 )
           )
        || ( 
             (petroMag[2] - reddening[2] < 19.5) 
          &&( ( r - reddening[2] - i + reddening[3] - 
       ( g - reddening[1] - r + reddening[2])/4 - 0.18 ) > 
                        (0.45 - 4*( g - reddening[1] - r + reddening[2] ) 
                        )
               )
          && ( (g - reddening[1] - r + reddening[2] ) > 
                  ( 1.35 + 0.25 *( r - reddening[2] - i + reddening[3] ) ) 
              )   )

6.       Q8: Find galaxies that are blended with a star and return the deblended galaxy magnitudes.

Q8.SQL

SELECT G.ObjID,G.u,G.g,G.r,G.i,G.z
FROM  Galaxy G, Star S
WHERE

  G.parentID > 0
  AND G.parentID = S.parentID 

 

Q8.SXQL

SELECT objID,u,g,r,i,z
FROM Galaxy
WHERE 
   EXIST(parent) &&
   parent.child{?}.objType == OBJECT_STAR
 

 


7.       Q9: Find quasars with a line width >2000 km/s and 2.5<redshift<2.7.

Q9.SQL

DECLARE @qso int
SET @qso = dbo.fSpecClass('QSO')
DECLARE @hiZ_qso int
SET @hiZ_qso =dbo.fSpecClass('HIZ_QSO')
SELECT specObjID, z, zConf, SpecClass
FROM  SpecObj
WHERE 

   ( SpecClass = @qso OR 
     SpecClass = @hiZ_qso)
   AND  z BETWEEN 2.5 AND 2.7  
   AND  zConf > 0.90

Q9.SXQL

SELECT spec_ID, z, zConf, specClass
FROM  SpecObj
WHERE 
  ( specClass == SPEC_QSO ||
    specClass == SPEC_HIZ_QSO )
   AND  z BETWEEN 2.5 AND 2.7
   AND  zConf > 0.90

 

8.       Q10: Find galaxies with spectra that have an equivalent width in Ha >40Ĺ.

Q10.SQL

 SELECT G.ObjID
 FROM Galaxy   as G,
  SpecObj    as S,
  SpecLine   as L 
 WHERE G.ObjID = S.ObjID 
   AND S.SpecObjID = L.SpecObjID
   AND L.LineId = 6565
   AND L.ew > 40 

Q10.SXQL

SELECT  objID FROM (
   SELECT obj FROM (
      SELECT spec FROM SpecLine
      WHERE
         name.lineID == 6565 &&
         ew > 40 )
) WHERE
  objType == OBJECT_GALAXY

9.       Q11: Find all elliptical galaxies with spectra that have an anomalous emission line.

Q11.SQL

SELECT DISTINCT G.ObjID
FROM Galaxy as G,
  SpecObj  as S, 
  SpecLine as L,
  XCRedshift as XC 
WHERE G.ObjID = S.ObjID 
  AND S.SpecObjID = L.SpecObjID
  AND S.SpecObjID = XC.SpecObjID 
  AND XC.tempNo = 8
  AND L.lineID = 0 
  AND L.ew > 10
  AND S.SpecObjID not in (
SELECT S.SpecObjID 
FROM    SpecLine as L1 
WHERE S.SpecObjID = L1.SpecObjID 
  AND abs(L.wave - L1.wave) <.01
  AND L1.LineID != 0   

Q11.SXQL

SELECT objID
FROM (
 SELECT obj FROM (
   SELECT spec FROM (
      SELECT found FROM SpecObj
      WHERE
         xcorrz{?}.tempNo == 8
   ) WHERE
     ew > 10
     && (((restWave - spec.measured{?}.restWave) > -0.01) &&
         ((restWave - spec.measured{?}.restWave) < 0.01)) )
) WHERE
  objType == OBJECT_GALAXY

 

10.   Q15: Provide a list of moving objects consistent with an asteroid.

Q15.SQL

SELECT objID,
  sqrt( power(rowv,2) + power(colv, 2) )
FROM PhotoObj
WHERE

  (power(rowv,2) + power(colv, 2)) > 50 
   AND rowv >= 0 AND colv >=0  

Q15.SXQL

SELECT objID, 
   sqrt( rowv*rowv + colv*colv )
FROM PhotoObj
WHERE 
  (rowv*rowv + colv*colv) > 50
   AND rowv >= 0 AND colv >=0

 


11.   Q16: Search for Cataclysmic Variables and pre-CVs with White Dwarfs and very late secondaries.


Q16.SQL

SELECT 

 run, 
 camCol,
 rerun,
 field,
 objID,
 u,g,r,i,z,
 ra,  dec

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
WHERE 

 u - g < 0.4 AND
 g - r < 0.7 AND
 r - i > 0.4 AND
 i - z > 0.4

Q16.SXQL

SELECT 
       RUN(),
       CAMCOL(),
       RERUN(),
       FIELDID(),
       OBJID(),
       u,g,r,i,z,
       RA(), DEC()
FROM Primary
WHERE 
u - g < 0.4 &&
g - r < 0.7 &&
r - i > 0.4 && 
i - z > 0.4 

 

 

 

12.   Q17: Find all objects with velocities and errors (non-indexed quantities) in a given range.

Q17.SQL

SELECT 
 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  ((rowv * rowv) / (rowvErr * rowvErr) + 
       (colv * colv) / (colvErr * colvErr) > 4)

Q17.SXQL

SELECT 
 RUN(),
 CAMCOL(),
 FIELDID(),
 OBJID(),
 rowC,colC,
 rowV,colV,rowVErr,colVErr,
 objFlags,
 psfMag,
 psfMagErr
FROM PhotoPrimary
WHERE  ((rowv * rowv) / (rowvErr * rowvErr) + 
       (colv * colv) / (colvErr * colvErr) > 4)

13.   Q18: Find all objects within a given coordinate cut (RA and Dec).

Q18.SQL

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

Q18.SXQL

SELECT obj.col[1], obj.col[2]
FROM  PhotoTag
WHERE (-0.642788 * cx + 0.766044 * cy>=0) AND  
      (-0.984808 * cx - 0.173648 * cy <0) 

14.   Q19: Search for objects and fields by their non-indexed short IDs.

Q19.SQL

SELECT  objID, field, ra, dec
FROM PhotoObj
WHERE 
  obj = 14 AND  field = 11

 

Q19.SXQL

SELECT  objID, object, field.field, ra, dec
FROM (
  SELECT obj FROM field
  WHERE 
    field == 11
) WHERE
  object == 14

 


15.   Q20: Search for all galaxies with bluer centers: for all galaxies with Petrosian radius < 18 that are not saturated, not bright and not on the edge of the frame, 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;  return all objects which have (u_model - g_model) - (u_psf - g_psf) < -0.4.

Q20.SQL

DECLARE @flags  BIGINT;
SET @flags =  dbo.fPhotoFlags('SATURATED') +
  dbo.fPhotoFlags('BRIGHT')    +
  dbo.fPhotoFlags('EDGE') 
SELECT colc_u, colc_g,  objID  
FROM  Galaxy
WHERE

  (Flags &  @flags )= 0 
  AND petroRad_r < 18
  AND ((colc_u - colc_g) - (psfMag_u - psfMag_g)) < -0.4 

Q20.SXQL

SELECT col[0], col[1],  objID
       obj.col[0], obj.col[1], objID
FROM Galaxy
WHERE
  (objFlags & (OBJECT_SATUR | OBJECT_BRIGHT | OBJECT_EDGE) ) == 0 
  && petroRad[2] < 18
  && ((obj.col[0] - obj.col[1]) - (psfMag[0] - psfMag[1])) < -0.4 

 

16.   Q21: Retrieve the PSF colors of all stars brighter than 20th magnitude, rejecting on various flags, that have PSP_STATUS = 2.

Q21.SQL

SELECT 

 s.psfMag_g,
 s.run,
 s.camCol,
 s.rerun,
 s.field
FROM Star s, Field f
WHERE 
  s.fieldid = f.fielded

  AND  s.psfMag_g < 20 
  AND  f.pspStatus = 2 

 

Q21.SXQL

SELECT  psfMag[1],
 RUN(),
 CAMCOL(),
 RERUN(),
 FIELDID()
FROM (
  SELECT obj FROM Field
  WHERE 
    pspStatus == 2
) WHERE 
  objType == OBJECT_STAR && 
  ( (status & AR_OBJECT_STATUS_PRIMARY) > 0 ) &&
  psfMag[1] < 20 

17.   Q22: Cluster finding.

Q22.SQL

DECLARE @binned BIGINT
SET @binned =  dbo.fPhotoFlags('BINNED1') +
  dbo.fPhotoFlags('BINNED2') +
  dbo.fPhotoFlags('BINNED4') 
DECLARE @deblendedChild BIGINT
SET @deblendedChild = dbo.fPhotoFlags('BLENDED')   +
   dbo.fPhotoFlags('NODEBLEND') +
   dbo.fPhotoFlags('CHILD')
DECLARE @blended BIGINT
SET @blended = dbo.fPhotoFlags('BLENDED')
SELECT  camCol, run, rerun, field,
        objID, ra, dec
FROM Galaxy 
WHERE (flags &  @binned )> 0 
  AND (flags &  @deblendedChild ) !=  @blended 
  AND petroMag_i < 23 

Q22.SXQL

SELECT 
        CAMCOL(), RUN(), RERUN(), FIELDID(),
        OBJID(), RA(), DEC(),
        petroMag[3],
        objFlags
FROM Galaxy
WHERE 
  ((objFlags & (OBJECT_BINNED1 | OBJECT_BINNED2 | OBJECT_BINNED4)) > 0) 
  &&
  ((objFlags & (OBJECT_BLENDED | OBJECT_NODEBLEND | OBJECT_CHILD)) != OBJECT_BLENDED) 
  &&
  petroMag[3] < 23

 

 


18.   Q23: Diameter-limited sample of galaxies.


Q23.SQL

DECLARE @binned BIGINT
SET @binned =  dbo.fPhotoFlags('BINNED1') |
  dbo.fPhotoFlags('BINNED2') |
  dbo.fPhotoFlags('BINNED4') 
DECLARE @deblendedChild BIGINT
SET @deblendedChild = dbo.fPhotoFlags('BLENDED')   |
   dbo.fPhotoFlags('NODEBLEND') |
   dbo.fPhotoFlags('CHILD')
DECLARE @blended BIGINT
SET @blended = dbo.fPhotoFlags('BLENDED')
DECLARE @noPetro BIGINT
SET @noPetro = dbo.fPhotoFlags('NOPETRO')
DECLARE @tooLarge BIGINT
SET @tooLarge = dbo.fPhotoFlags('TOO_LARGE')
DECLARE @saturated BIGINT
SET @saturated = dbo.fPhotoFlags('SATURATED')
SELECT run,camCol,rerun,field,objID,ra,dec
FROM Galaxy
WHERE (flags &  @binned )> 0 
  AND (flags &  @deblendedChild ) !=  @blended
  AND (  (( flags & @noPetro = 0) 
          AND petroRad_i > 15)
      OR ((flags & @noPetro > 0) 
         AND petroRad_i > 7.5)
      OR ((flags & @tooLarge > 0) 
         AND petroRad_i > 2.5)
      OR
         ((flags & @saturated  = 0 )
          AND petroRad_i > 17.5)
      ) 

Q23.SXQL

SELECT
 RUN(),
 CAMCOL(),
 RERUN(),
 FIELDID(),
 OBJID(),
 RA(),
 DEC()
FROM Galaxy WHERE (

  (objFlags & (OBJECT_BINNED1 | OBJECT_BINNED2 | OBJECT_BINNED4)) > 0 &&
  (objFlags & (OBJECT_BLENDED | OBJECT_NODEBLEND | OBJECT_CHILD)) != OBJECT_BLENDED  &&
  ( ((objFlags & OBJECT_NOPETRO == 0) && petroRad[3] > 15) ||
    ((objFlags & OBJECT_NOPETRO > 0) && petroRad[3] > 7.5)
  ) ||
  ( objFlags & OBJECT_TOO_LARGE > 0 && petroRad[3] > 2.5 )  ||
  ( objFlags & OBJECT_SATUR == 0 && petroRad[3] > 17.5 )
)

 

 

 

 

19.   Q24: Search for extremely red galaxies.

Q24.SQL

DECLARE @binned BIGINT
SET @binned =  dbo.fPhotoFlags(‘BINNED1’) |
  dbo.fPhotoFlags(‘BINNED2’) |
  dbo.fPhotoFlags(‘BINNED4’) 
DECLARE @deblendedChild BIGINT
SET @deblendedChild = dbo.fPhotoFlags(‘BLENDED’)   |
   dbo.fPhotoFlags(‘NODEBLEND’) |
   dbo.fPhotoFlags(‘CHILD’)
DECLARE @blended BIGINT
SET @blended = dbo.fPhotoFlags(‘BLENDED’)
DECLARE @crIntrp BIGINT
SET @crIntrp =  dbo.fPhotoFlags(‘COSMIC_RAY’) |
  dbo.fPhotoFlags(‘INTERP’)

SELECT 
  g.run, g.camCol, g.rerun, g.field, g.objID,

  g.ra, g.dec
FROM Field f, Galaxy g
WHERE 
  g.fieldid = f.fieldid
  AND (g.flags & @binned ) > 0 
  AND (g.flags & @deblendedChild ) !=  @blended
  AND (g.flags &  @crIntrp ) = 0
  AND f.psfWidth_r < 1.5 AND (g.i – g.z > 1.0) 

Q24.SXQL

SELECT
 RUN(),
 RERUN(),
 CAMCOL(),
 FIELDID(),
 OBJID(),
 RA(),
 DEC()
FROM Galaxy 
WHERE (
  ( (objFlags & (OBJECT_BINNED1 | OBJECT_BINNED2 | OBJECT_BINNED4)) > 0 ) &&
  ( (objFlags & (OBJECT_BLENDED | OBJECT_NODEBLEND | OBJECT_CHILD)) != OBJECT_BLENDED ) &&
  ( (objFlags & (OBJECT_CR | OBJECT_INTERP)) == 0 ) && 
  field.psfWidth[2] < 1.5 && 
  ( I – z – (reddening[3] – reddening[4]) > 1.0 )
)        

 

 


20.   Q25: The BRG (Bright Red Galaxy) sample.

Q25.SQL

DECLARE @binned BIGINT
SET @binned =  dbo.fPhotoFlags('BINNED1') |
  dbo.fPhotoFlags('BINNED2') |
  dbo.fPhotoFlags('BINNED4') 
DECLARE @deblendedChild BIGINT
SET @deblendedChild = dbo.fPhotoFlags('BLENDED')   |
   dbo.fPhotoFlags('NODEBLEND') |
   dbo.fPhotoFlags('CHILD')
DECLARE @blended BIGINT
SET @blended = dbo.fPhotoFlags('BLENDED')
DECLARE @edgedSaturated BIGINT
SET @edgedSaturated = dbo.fPhotoFlags('EDGE') |
    dbo.fPhotoFlags('SATURATED')
SELECT 
 run, camCol, rerun, field, objID, ra, dec
FROM Galaxy
WHERE (
  ( flags &  @binned ) > 0 
  AND ( flags &  @deblendedChild ) !=  @blended
  AND ( flags &  @edgedSaturated ) = 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-reddening_r) < 19.2
  AND ( petroMag_r - reddening_r < 
   (13.1 + (7/3) * (g-r) + 4 * (r-i) - 4*0.18) )
 AND ( (r - i - (g - r)/4 - 0.18) < 0.2 ) 
   AND ( (r - i - (g - r)/4 - 0.18) > -0.2 )
   AND ( (petroMag_r - reddening_r +  2.5 *

      LOG10(2 * 3.1415 * petroR50_r *

                   petroR50_r)) < 24.2  ) )
   OR  ( (petroMag_r - reddening_r < 19.5) 
     AND ( (r-i-(g-r)/4-0.18) > (0.45-4*(g-r))

     AND ( (g - r) > (1.35 + 0.25 * (r - i))) )
  AND ( (petroMag_r - reddening_r  + 
         2.5 * LOG10( 2 * 3.1415 * petroR50_r * petroR50_r )) < 23.3 ) )

Q25.SXQL

SELECT
 RUN(),CAMCOL(),RERUN(),FIELDID(),OBJID(),
 RA(),DEC()
FROM Galaxy 
WHERE (
  ( (objFlags & (OBJECT_BINNED1 | OBJECT_BINNED2 | OBJECT_BINNED4)) > 0 ) &&
  ( (objFlags & (OBJECT_BLENDED | OBJECT_NODEBLEND | OBJECT_CHILD)) != OBJECT_BLENDED ) &&
  ( (objFlags & (OBJECT_EDGE | OBJECT_SATUR)) == 0 ) && 
  petroMag[3] > 17.65 &&
  ( petroMag[2] > 15.5 || petroR50_r > 2 ) &&
  ( petroMag[2] > 0 && g>0 && r>0 && i>0 ) &&
  ( (petroMag[2] - reddening[2] < 19.2) &&
    (petroMag[2] - reddening[2] < 13.1 +    
     (7/3)*(g-reddening[1]-r+reddening[2]) + 
      4*(r - reddening[2] - i + reddening[3])-

      4 * 0.18) && ((r - reddening[2] - i +

        reddening[3] - (g - reddening[1] - 
        r + reddening[2])/4 - 0.18) < 0.2) &&
     ((r - reddening[2] - i + reddening[3] - (g - reddening[1] - 
     r + reddening[2])/4 - 0.18) > -0.2) &&
     ((petroMag[2] - reddening[2]) + 2.5*

     LOG(2*3.1415*petroR50_r*petroR50_r)<24.2)
  ) ||
  ( (petroMag[2] - reddening[2] < 19.5) &&
    ((r - reddening[2] - i + reddening[3] -   
    (g-reddening[1]-r+reddening[2])/4-0.18) > 
   0.45-4*(g-reddening[1]-r+reddening[2])) &&
    (g - reddening[1] - r + reddening[2]  > 
     1.35 + 0.25 * (r - reddening[2] - i + reddening[3])) &&
    ((petroMag[2]-reddening[2]) + 2.5 *

    LOG(2*3.1415*petroR50_r*petroR50_r)<23.3) 
  ))

21.   Q26: Search for low redshift (z) QSO candidates.

Q26.SQL

SELECT 
       g, 
       run,
       rerun,
       camcol,
       field,
       objID
FROM Galaxy 
WHERE (
  (modelMag_g <= 22) AND
  (modelMag_u - modelMag_g >= -0.27) AND (modelMag_u - modelMag_g < 0.71) AND
  (modelMag_g - modelMag_r >= -0.24) AND (modelMag_g - modelMag_r < 0.35) AND
  (modelMag_r - modelMag_i >= -0.27) AND (modelMag_r - modelMag_i < 0.57) AND
  (modelMag_i - modelMag_z >= -0.35) AND (modelMag_i - modelMag_z < 0.70)

Q26.SXQL

SELECT 
 g,
 RUN(),
 RERUN(),
 CAMCOL(),
 FIELDID(),
 OBJID() 
FROM Galaxy
WHERE (
  (g <= 22) &&
  (u-g >= -0.27) && (u-g < 0.71) &&
  (g-r >= -0.24) && (g-r < 0.35) &&
  (r-i >= -0.27) && (r-i < 0.57) &&
  (i-z >= -0.35) && (i-z < 0.70) 
)

 

 

 


22.   Q27: Check the errors on moving objects – compare the velocity to the error in velocity and see if the object is flagged as a moving object.


Q27.SQL

DECLARE @moved BIGINT
SET @moved = dbo.fPhotoFlags(‘MOVED’) 
DECLARE @badMove BIGINT
SET @badMove = dbo.fPhotoFlags(‘BAD_MOVING_FIT’)
SELECT 
       run, rerun, camcol, field, objID,
       ra, dec,
       rowv, colv, rowvErr, colvErr,
       i,
       (flags & @moved) as MOVED,
       (flags & @badMove) as BAD_MOVING_FIT
FROM Galaxy
WHERE 
  (flags & (@moved + @badMove)) > 0
  AND (rowv * rowv + colv * colv) >= 
                     (rowvErr * rowvErr + colvErr * colvErr) 

Q27.SXQL

SELECT 
 RUN(),RERUN(),CAMCOL(),FIELDID(),OBJID(),
 RA(),DEC(),
 obj.rowv, obj.colv, obj.rowvErr, obj.colvErr,
 i,
 objFlags & OBJECT_MOVED,
 objFlags & BAD_MOVING_FIT
FROM Galaxy
WHERE (
  ((objFlags & (OBJECT_MOVED | OBJECT_BAD_MOVING_FIT)) > 0) && 
  (((obj.rowv * obj.rowv) + (obj.colv * obj.colv)) >= 
    ((obj.rowvErr * obj.rowvErr) + (obj.colvErr * obj.colvErr)))
)

23.   Q28: Extract a random sample of the data – get the colors of 100,000 random objects from all fields that are survey quality so that color-color diagrams can be made of them..

Q28.SQL

SELECT u,g,r,i,z 

FROM Galaxy 
WHERE 

  (obj  %100 )= 1

Q28.SXQL

SELECT u,g,r,i,z
FROM Galaxy
WHERE 
  (obj.object – (obj.object/100) * 100) == 1

24.   Q29: Find quasars.

Q29.SQL

SELECT  run,
        camCol,
        rerun,
        field,
        objID,
        u,g,r,i,z,
        ra,
        dec
FROM Star      -- or sxGalaxy 
WHERE ( modelMag_u - modelMag_g > 2.0 OR u > 22.3 ) 
  AND ( modelMag_i < 19 ) 
  AND ( modelMag_i > 0 ) 
  AND ( modelMag_g - modelMag_r > 1.0 ) 
  AND ( modelMag_r - modelMag_i < (0.08 + 0.42 * (modelMag_g - modelMag_r - 0.96))
     OR modelMag_g - modelMag_r > 2.26 ) 
 AND  ( modelMag_i - modelMag_z < 0.25 ) 

Q29.SXQL

SELECT 
        RUN(),
        RERUN(),
        CAMCOL(),
        FIELDID(),
        OBJID(),
        u,g,r,i,z,
        RA(),
        DEC()
FROM Star
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 )   

 


25.   Q30: Search for objects and fields by their non-indexed quantities.

Q30.SQL

SELECT 
g.run,
g.rerun,
g.camCol,
f.field,
p.objID,
 p.u,
p.modelMagErr_u ,
 p.petroMag_r - p.reddening_r,
 p.petroMagErr_r,
 p.status & 0x00002000,
f.psfWidth_r
FROM  photoobj p, field f, segment g  -- , tag t
WHERE
  f.fieldid = p.fieldid
  AND f.segmentid = g.segmentid
  AND f.psfWidth_r > 2
  AND p.colc > 1300.0

Q30.SXQL

SELECT 
       RUN(),
       RERUN(),
       CAMCOL(),
       FIELDID(),
       OBJID(),
       modelMag[0] - reddening[0],
       modelMagErr[0] ,
       petroMag[2] - reddening[2],
       petroMagErr[2],
       status & 0x00002000,
       field.psfWidth[2]
FROM (
   SELECT obj 
   FROM Field
   WHERE
      psfWidth[2] > 2
      && obj.colC > 1300.0
)

26.   Q31: Different version of Q30 with a much wider search (less limiting non-indexed constraint).

Q31.SQL

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.reddening_r,
 p.petroMagErr_r,
 p.i, p.modelMagErr_i,
 p.z,
 p.status & 0x00002000,
 f.psfWidth_r
FROM 
 photoobj p, field f, segment g 
WHERE
  f.fieldid = p.fieldid
  AND f.segmentid = g.segmentid
  AND p.colc > 400.0

Q31.SXQL

SELECT 
       RUN(),
       RERUN(),
       CAMCOL(),
       FIELDID(),
       OBJID(),
       RA(), DEC(),
       rowC, colC,
       u - reddening[0], obj.modelMagErr[0] ,
       g - reddening[1], obj.modelMagErr[1],
       r - reddening[2], obj.modelMagErr[2],
       petroMag[2] - reddening[2],
       obj.petroMagErr[2],
       i - reddening[3], obj.modelMagErr[3],
       z - reddening[4],
       status & 0x00002000,
       field.psfWidth[2]
FROM PhotoTag
WHERE
  colC > 400.0

 

 

Differences between SQL and SXQL query versions

  1. The primary object tables in the MS-SQL version have de-reddened u,g,r,i,z, i.e. the correction for interstellar reddening of the light has been applied to the magnitudes recorded in the tables.  However, the u,g,r,i,z magnitudes in the corresponding classes in the OODBMS have not been de-reddened. This makes most queries between the two not strictly equivalent.  In order to make them equivalent, either the reddening correction is removed from the SQL version or added to the SXQL version as applicable.
  2. Sometimes it was necessary to add the DISTINCT qualifier in the SELECT clause in the SQL version to avoid duplicates (e.g. Q11).
  3. Some queries from the original set could not be translated to SXQL, for the reasons listed below for each of the queries.  They highlight the limitations in the query language and data model that we implemented for the OODBMS.

·         Q6: Parent/child links were not present in object database, hence SQL queries that contained joins on the parented field could not be translated.

·         Q7: There is no GROUP BY clause or way to sort into buckets in SXQL.

·         Q12: It is not possible to do a grided count in SXQL.

·         Q13: There is no AVERAGE function or binning possible in SXQL.

·         Q14: There is no pre-computed nearest neighbors list in the object database.

·         Q16: Aggregate functions could not be reproduced in SXQL.

·         Q17-20: Same as Q14 – no nearest neighbors list in SXQL.