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
|
- 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.
- Sometimes it was necessary to add the DISTINCT
qualifier in the SELECT clause in the SQL version to avoid duplicates
(e.g. Q11).
- 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.