db/queries: lsstdb_commonQueries.sql

File lsstdb_commonQueries.sql, 11.1 KB (added by jbecla, 11 years ago)

query 012 fixed

Line 
1
2-- known issues:
3-- 006: image related, do not have it
4-- 007: how to express variability?
5-- 017: htmID
6-- 021: MOPS
7-- 023: SDSS flags --> type, fGetNearbyObjEq()
8-- 028: extended params --> check w/Robert, SDSS flags --> type
9-- 029: extended params --> check w/Robert
10-- 030: extended params --> check w/Robert, SDSS flags --> type
11-- 032: extended params --> check w/Robert
12-- 033: extended params --> check w/Robert, SDSS flags --> type
13-- 034: extended params --> check w/Robert
14-- 038: extended params --> check w/Robert
15-- 039: MOPS
16-- 040: MOPS
17-- 041: extended params --> check w/Robert, SDSS flags --> type
18-- 042: extended params --> check w/Robert, SDSS flags --> type
19-- 046: extended params --> check w/Robert
20-- 049: MOPS
21-- 050: MOPS
22-- 051: MOPS
23-- 052: MOPS
24
25
26-- the queries assume the following views and table
27-- exist in addition to Precursor Schema:
28
29DROP VIEW Galaxy;
30CREATE VIEW Galaxy AS
31    SELECT Object.*, 
32           ObjectPhotoZ.redshift, ObjectPhotoZ.redshiftErr
33    FROM   Object
34    JOIN   _Object2Type USING (objectId) 
35    JOIN   ObjectType   USING (typeId)
36    JOIN   ObjectPhotoZ ON (Object.objectId=ObjectPhotoZ.objectId)
37    WHERE  ObjectType.description = "galaxy"
38      AND  _Object2Type.probability > 90;  -- 0-100%
39
40DROP VIEW Star;
41CREATE VIEW Star AS
42    SELECT Object.*, 
43           ObjectPhotoZ.redshift, ObjectPhotoZ.redshiftErr
44    FROM   Object
45    JOIN   _Object2Type USING (objectId) 
46    JOIN   ObjectType   USING (typeId)
47    JOIN   ObjectPhotoZ ON (Object.objectId=ObjectPhotoZ.objectId)
48    WHERE  ObjectType.description = "star"
49      AND  _Object2Type.probability > 90;  -- 0-100%
50
51DROP TABLE IF EXISTS Neighbors;
52CREATE TABLE Neighbors (
53  objectId         BIGINT,
54  neighborObjectId BIGINT,
55  distance         FLOAT,
56  neighborType     TINYINT
57);
58
59
60
61----- 001 -----
62SELECT taiMidPoint, modelMag, modelMagErr
63FROM   Source
64WHERE  objectId = 123
65   AND filterId = 3;
66
67
68----- 002 -----
69SELECT *
70FROM   VarObject
71JOIN   _Object2Type USING(objectId)
72WHERE  typeId = 2
73AND    probability = 0.34;
74
75
76----- 003 -----
77SELECT  *
78FROM    Object
79WHERE   ra      BETWEEN 1 AND 2
80   AND  decl    BETWEEN 1 AND 2
81   AND  zMag    BETWEEN 1 AND 2
82   AND  grColor BETWEEN 1 AND 2
83   AND  izColor BETWEEN 1 AND 2;
84
85
86----- 004 -----
87SELECT uMag, gMag, rMag, iMag, zMag, yMag
88FROM   Object 
89WHERE  (objectId  % 100 )= 0.01;
90
91
92----- 005 -----
93SELECT objectId
94FROM   Alert
95JOIN   _Alert2Type USING (alertId) 
96JOIN   AlertType USING (alertTypeId)
97WHERE  alertTypeDescr = 'newTransients'
98  AND  Alert.timeGenerated BETWEEN 1 AND 2;
99
100
101----- 007 -----
102SELECT * 
103FROM   Source
104WHERE  ra BETWEEN 1 + cos(2)*(0.5) 
105              AND 1 - cos(2)*(0.5)
106  AND  decl BETWEEN 2 +0.5 
107                AND 2 -0.5
108  AND  filterId = 4
109--  AND  variability BETWEEN 0.1 AND 0.2
110ORDER BY objectId, taiMidPoint ASC;
111
112
113----- 008 -----
114SELECT *
115FROM   VarObject
116WHERE  ra BETWEEN 1 AND 2
117  AND  decl BETWEEN 2 AND 3;
118
119
120----- 009 -----
121SELECT ampExposureId, ra, decl
122FROM   Object
123JOIN   Source USING(objectId)
124WHERE  objectId = 123
125   AND ampExposureId = 3;
126
127
128----- 010 -----
129SELECT  s1.objectId AS objectId1,
130        s2.objectId AS objectId2
131FROM    Star      AS s1
132JOIN    Star      AS s2 USING (objectId)
133JOIN    Neighbors AS N  ON (s2.objectId = N.neighborObjectId)
134WHERE   N.distance < 0.5/60       -- distance is 1/2 arc second or less
135--  AND   s1.run != s2.run -- observations are from two different runs (SDSS)
136  AND   s1.uMag BETWEEN 1 AND 27  -- magnitudes are reasonable
137  AND   s1.gMag BETWEEN 1 AND 27
138  AND   s1.rMag BETWEEN 1 AND 27
139  AND   s1.iMag BETWEEN 1 AND 27
140  AND   s1.zMag BETWEEN 1 AND 27
141  AND   s1.yMag BETWEEN 1 AND 27
142  AND   s2.uMag BETWEEN 1 AND 27
143  AND   s2.gMag BETWEEN 1 AND 27
144  AND   s2.rMag BETWEEN 1 AND 27
145  AND   s2.iMag BETWEEN 1 AND 27
146  AND   s2.zMag BETWEEN 1 AND 27
147  AND   s2.yMag BETWEEN 1 AND 27
148  AND (                           -- and one of the colors is  different.
149         ABS(s1.uMag-s2.uMag) > .1 + (ABS(s1.uMagErr) + ABS(s2.uMagErr))
150      OR ABS(s1.gMag-s2.gMag) > .1 + (ABS(s1.gMagErr) + ABS(s2.gMagErr))
151      OR ABS(s1.rMag-s2.rMag) > .1 + (ABS(s1.rMagErr) + ABS(s2.rMagErr))
152      OR ABS(s1.iMag-s2.iMag) > .1 + (ABS(s1.iMagErr) + ABS(s2.iMagErr))
153      OR ABS(s1.zMag-s2.zMag) > .1 + (ABS(s1.zMagErr) + ABS(s2.zMagErr))
154      OR ABS(s1.yMag-s2.yMag) > .1 + (ABS(s1.yMagErr) + ABS(s2.yMagErr)));
155
156
157----- 011 -----
158SELECT *
159FROM   VarObject
160WHERE  uTimescale < 1
161   AND gTimescale < 1
162   AND rTimescale < 1
163   AND iTimescale < 1
164   AND zTimescale < 1
165   AND yTimescale < 1
166    OR primaryPeriod BETWEEN 2 AND 2
167    OR uAmplitude > 1
168    OR gAmplitude > 1
169    OR rAmplitude > 1
170    OR iAmplitude > 1
171    OR zAmplitude > 1
172    OR yAmplitude > 1;
173
174
175---- 012 -----
176SELECT  COUNT(*)                                                AS total,     
177        SUM( CASE WHEN (typeId=3) THEN 1 ELSE 0 END)            AS galaxies,
178        SUM( CASE WHEN (typeId=6) THEN 1 ELSE 0 END)            AS stars,
179        SUM( CASE WHEN (typeId NOT IN (3,6)) THEN 1 ELSE 0 END) AS other
180FROM    Object
181JOIN    _Object2Type USING(objectId)
182WHERE  (ugColor > 2.0 OR uMag > 22.3) -- apply the quasar color cut.
183   AND iMag BETWEEN 0 AND 19 
184   AND grColor > 1.0 
185   AND ( (riColor < 0.08 + 0.42 * (grColor - 0.96)) OR (grColor > 2.26 ) )
186   AND izColor < 0.25;
187
188
189----- 013 -----
190SELECT  DISTINCT o.objectId
191FROM    Object     o
192JOIN    Neighbors  n USING (objectId)
193JOIN    Object     x ON (x.objectId = n.neighborObjectId)
194  AND o.objectId <> x.objectId
195  AND ABS(o.ugColor - x.ugColor) < 0.05  -- o and x have similar spectra
196  AND ABS(o.grColor - x.grColor) < 0.05
197  AND ABS(o.riColor - x.riColor) < 0.05 
198  AND ABS(o.izColor - x.izColor) < 0.05;
199
200
201----- 014 -----
202SELECT *
203FROM   Galaxy
204WHERE  ra   BETWEEN 1 AND 2
205   AND decl BETWEEN 1 AND 2;
206
207
208----- 015 -----
209SELECT v.ra, v.decl
210FROM   VarObject v, Object o
211WHERE  abs(v.ra-o.ra) < 2
212   AND abs(v.decl-o.decl) < 3
213   AND o.objectId = 1234;
214
215
216----- 016 -----
217SELECT  objectId,
218        uMag, gMag, rMag, iMag, zMag, yMag
219        ra,
220        decl
221FROM    Star       -- or Galaxy
222WHERE  ( ugColor > 2.0 OR uMag > 22.3 ) 
223AND    iMag BETWEEN 0 AND 19 
224AND    grColor > 1.0 
225AND    ( riColor < (0.08 + 0.42 * (grColor - 0.96)) OR grColor > 2.26 ) 
226AND    izColor < 0.25;
227
228
229----- 018 -----
230SELECT objectId,
231       uMag, gMag, rMag, iMag, zMag, yMag
232       ra, decl
233FROM   Star       -- or Galaxy
234WHERE  ( ugColor > 2.0 OR uMag > 22.3 ) 
235AND    iMag BETWEEN 0 AND 19 
236AND    grColor > 1.0 
237AND    ( riColor < (0.08 + 0.42 * (grColor - 0.96)) OR grColor > 2.26 ) 
238AND    izColor < 0.25;
239
240
241----- 019 -----
242SELECT objectId
243FROM   Object
244JOIN   DIASource USING(objectId)
245WHERE  latestObsTime > '2007-01-01'
246GROUP BY (objectId)
247HAVING COUNT(objectId) = 1;
248
249
250----- 020 -----
251SELECT gMag, objectId
252FROM   Galaxy
253WHERE  gMag <= 22
254   AND ugColor >= -0.27 AND ugColor < 0.71
255   AND grColor >= -0.24 AND grColor < 0.35
256   AND riColor >= -0.27 AND riColor < 0.57
257   AND izColor >= -0.35 AND izColor < 0.70;
258
259
260----- 022 -----
261INSERT INTO Neighbors(objectId, neighborObjectId, distance)
262  SELECT o1.objectId, o2.objectId,
263         3600*DEGREES( ACOS(o1.cx*o2.cx+o1.cy*o2.cy+o1.cz*o2.cz) ) AS dist
264  FROM   Object o1, Object o2
265  WHERE  o1.objectId <> o2.objectId
266    AND  o2.zone BETWEEN o1.zone-1 AND o1.zone+1
267    AND  o2.ra BETWEEN o1.ra-0.0003/COS(RADIANS(o2.decl))
268    AND  o1.ra+0.0003/COS(RADIANS(o2.decl))
269  HAVING dist<1.0;
270
271
272----- 024 -----
273SELECT  G.objectId,                               -- output galaxy
274        G.uMag,G.gMag,G.rMag,G.iMag,G.zMag,G.yMag -- and magnitudes
275FROM    Galaxy G
276JOIN    _Source2Object M1 ON (G.objectId = M1.objectId)
277JOIN    _Source2Object M2 ON (M1.sourceId = M2.sourceId)
278JOIN    Star S ON (M2.objectId = S.objectId);
279
280
281----- 025 -----
282SELECT DISTINCT o1.objectId, o1.ra, o1.decl, ps.url
283FROM   Galaxy o1, PostageStampJpegs ps
284WHERE  ABS(ps.ra   - o1.ra  ) < ps.sizeRa/(2*COS(RADIANS(o1.decl)))
285   AND ABS(ps.decl - o1.decl) < ps.sizeDecl/2 
286   AND (
287        SELECT COUNT(o2.objectId)
288        FROM   Galaxy o2
289        WHERE  o1.objectId <> o2.objectId
290          AND  ABS(o1.ra   - o2.ra  ) < 0.1/COS(RADIANS(o2.decl))
291          AND  ABS(o1.decl - o2.decl) < 0.1
292       ) > 10000;
293
294
295----- 026 -----
296SELECT objectId, ra, decl, uMag
297FROM   Star
298WHERE  (muRa > 0.5 OR muDecl > 0.5)
299   AND rMag > 18.0
300   AND grColor < 0.2;
301
302
303----- 027 -----
304SELECT *
305FROM   Object
306WHERE  ABS(muRa)   > 1 * muRaErr
307    OR ABS(muDecl) > 1 * muDeclErr;
308
309
310----- 031 -----
311SELECT *
312FROM   Object
313WHERE  parallax BETWEEN 1 AND 2;
314
315
316----- 032 -----
317SELECT objectId
318FROM   Galaxy
319WHERE  rMag < 3
320--  AND  extinction_r > 12
321;
322
323----- 035 -----
324SELECT  *
325FROM    Object
326WHERE   ugColor < 0.4
327   AND  grColor < 0.7
328   AND  riColor > 0.4
329   AND  izColor > 0.4;
330
331
332----- 036 -----
333SELECT   ROUND(ugColor,0) AS UG, 
334         ROUND(grColor,0) AS GR, 
335         ROUND(riColor,0) AS RI, 
336         ROUND(izColor,0) AS IZ,
337         ROUND(zyColor,0) AS ZY,
338         COUNT(*) AS pop
339FROM     Star
340WHERE    (uMag+gMag+rMag+iMag+zMag+yMag) < 150 -- exclude bogus magnitudes (== 999)
341GROUP BY UG, GR, RI, IZ, ZY
342HAVING pop > 500
343ORDER BY pop;
344
345
346----- 037 -----
347
348SELECT objectId, ra, decl, 
349       uMag, gMag, rMag, iMag, zMag, yMag
350FROM   Galaxy
351WHERE  izColor > 1.0;
352
353
354----- 043 -----
355SELECT  S1.objectId AS s1, S2.objectId AS s2
356FROM    Star S1                                       -- S1 is the white dwarf
357JOIN    Neighbors N USING (objectId)
358JOIN    Star S2 ON (S2.objectId = N.neighborObjectId) -- S2 is the second star
359   AND  N.NeighborType = 'star'
360   AND  N.distance < .05        -- the 5 arcsecond test
361   AND  S1.ugColor < 0.4        -- and S1 meets Paul Szkody's color cut
362   AND  S1.grColor < 0.7        -- for white dwarfs
363   AND  S1.riColor > 0.4 
364   AND  S1.izColor > 0.4;
365
366
367----- 044 -----
368SELECT v.objectId, v.ra, v.decl, 
369       o.objectId, o.ra, o.decl
370FROM   VarObject v, Star o
371JOIN   _Object2Type USING (objectId) 
372JOIN   ObjectType   ON (_Object2Type.typeId = ObjectType.typeId)
373WHERE  v.objectId <> o.objectId
374   AND ABS(v.ra - o.ra) < 0.003 / COS(RADIANS(o.decl))
375   AND ABS(v.decl - o.decl) < 0.003
376   AND ObjectType.description = "star"
377   AND (o.grColor < 0.0 OR o.riColor < 0.0 OR o.ugColor < 1.0);
378
379
380----- 045 -----
381SELECT objectId
382FROM   Galaxy
383WHERE  uVarProb = 100
384   OR  gVarProb = 100
385   OR  rVarProb = 100
386   OR  iVarProb = 100
387   OR  zVarProb = 100
388   OR  yVarProb = 100;
389
390
391----- 047 -----
392SELECT v.objectId
393FROM   Galaxy g
394JOIN   Neighbors n USING (objectId)
395JOIN   VarObject v ON (n.neighborObjectId = v.objectId)
396WHERE  n.distance < 0.01;
397
398
399----- 048 -----
400SELECT objectId
401FROM   (SELECT v.objectId AS objectId, 
402               COUNT(n.neighborObjectId) AS neighbors
403        FROM   VarObject v
404        JOIN   Neighbors n USING (objectId)
405        WHERE  n.distance < 0.1
406        GROUP BY v.objectId) AS C
407WHERE  neighbors > 0.4;
408
409
410----- 053 -----
411SELECT parallax, uMag, gMag, rMag, iMag, zMag, yMag, muRA, muDecl,
412       uVarProb, gVarProb, rVarProb, iVarProb, zVarProb, yVarProb
413FROM   Star
414WHERE  ra BETWEEN 1 AND 2
415  AND  decl BETWEEN 5 AND 6;
416
417
418----- 054 -----
419SELECT objectId
420FROM   Star
421WHERE  ra BETWEEN 1 AND 2
422  AND  decl BETWEEN 2 AND 3
423  AND  grColor > 0.1
424  AND  (muRa * muRa + muDecl * muDecl) < 0.34
425  AND  redshift BETWEEN 2 AND 3;
426
427
428----- 055 -----
429SELECT COUNT(*)
430FROM   Star
431WHERE  ra BETWEEN 1 AND 2
432  AND  decl BETWEEN 2 AND 3;