-
Notifications
You must be signed in to change notification settings - Fork 4
/
PS1features.query
120 lines (118 loc) · 7.13 KB
/
PS1features.query
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
/*This Query is designed to select relevant features for
star-galaxy separation from the PS1 stackObjectView table.
Note - below ZZZYYYXXX is inserted as a variable to captrue the
range of declinations that will be captured in each slice.
*/
select *,
(wwPSFFlux - (0.91375*wwKronFlux))/sqrt(1 + power(0.91375,2)) as wwPSFKronDist,
wwPSFFlux/wwApFlux as wwPSFApRatio,
wwPSFFlux/wwKronFlux as wwPSFKronRatio
from
(select sov.objid, sov.raStack, sov.decStack, sov.nDetections, sov.qualityFlag,
(sov.iPSFMag - sov.iKronMag)*iKronWeight/nullif(iKronWeight,0) as iPSFminusKron,
(sov.gPSFFlux*gPSFWeight + sov.rPSFFlux*rPSFWeight + sov.iPSFFlux*iPSFWeight +
sov.zPSFFlux*zPSFWeight + sov.yPSFFlux*yPSFWeight)/nullif(
gPSFWeight + rPSFWeight + iPSFWeight + zPSFWeight + yPSFWeight, 0)
as wwPSFFlux,
(sov.gKronFlux*gKronWeight + sov.rKronFlux*rKronWeight + sov.iKronFlux*iKronWeight +
sov.zKronFlux*zKronWeight + sov.yKronFlux*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwKronFlux,
(sov.gApFlux*gPSFWeight + sov.rApFlux*rPSFWeight + sov.iApFlux*iPSFWeight +
sov.zApFlux*zPSFWeight + sov.yApFlux*yPSFWeight)/nullif(
gPSFWeight + rPSFWeight + iPSFWeight + zPSFWeight + yPSFWeight, 0)
as wwApFlux,
(sov.gExtNSigma*gKronWeight + sov.rExtNSigma*rKronWeight + sov.iExtNSigma*iKronWeight +
sov.zExtNSigma*zKronWeight + sov.yExtNSigma*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwExtNSigma,
(sov.gmomentYY/power(gSeeing, 2)*gKronWeight +
sov.rmomentYY/power(rSeeing, 2)*rKronWeight +
sov.imomentYY/power(iSeeing, 2)*iKronWeight +
sov.zmomentYY/power(zSeeing, 2)*zKronWeight +
sov.ymomentYY/power(ySeeing, 2)*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwmomentYY,
(sov.gmomentXY/power(gSeeing, 2)*gKronWeight +
sov.rmomentXY/power(rSeeing, 2)*rKronWeight +
sov.imomentXY/power(iSeeing, 2)*iKronWeight +
sov.zmomentXY/power(zSeeing, 2)*zKronWeight +
sov.ymomentXY/power(ySeeing, 2)*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwmomentXY,
(sov.gmomentXX/power(gSeeing, 2)*gKronWeight +
sov.rmomentXX/power(rSeeing, 2)*rKronWeight +
sov.imomentXX/power(iSeeing, 2)*iKronWeight +
sov.zmomentXX/power(zSeeing, 2)*zKronWeight +
sov.ymomentXX/power(ySeeing, 2)*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwmomentXX,
(sov.gKronRad/power(gSeeing, 1)*gKronWeight +
sov.rKronRad/power(rSeeing, 1)*rKronWeight +
sov.iKronRad/power(iSeeing, 1)*iKronWeight +
sov.zKronRad/power(zSeeing, 1)*zKronWeight +
sov.yKronRad/power(ySeeing, 1)*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwKronRad,
(sov.gmomentRH/power(gSeeing, 0.5)*gKronWeight +
sov.rmomentRH/power(rSeeing, 0.5)*rKronWeight +
sov.imomentRH/power(iSeeing, 0.5)*iKronWeight +
sov.zmomentRH/power(zSeeing, 0.5)*zKronWeight +
sov.ymomentRH/power(ySeeing, 0.5)*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwmomentRH,
(sov.gpsfChiSq*gKronWeight + sov.rpsfChiSq*rKronWeight + sov.ipsfChiSq*iKronWeight +
sov.zpsfChiSq*zKronWeight + sov.ypsfChiSq*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwpsfChiSq,
(sov.gpsfLikelihood*gKronWeight + sov.rpsfLikelihood*rKronWeight + sov.ipsfLikelihood*iKronWeight +
sov.zpsfLikelihood*zKronWeight + sov.ypsfLikelihood*yKronWeight)/nullif(
gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight, 0)
as wwpsfLikelihood
from
(select sov.objid,
(case when (sov.gpsfMajorFWHM > 0 and sov.gpsfMinorFWHM > 0)
then (sov.gpsfMajorFWHM + sov.gpsfMinorFWHM)/2 else 1e8 end) as gSeeing,
(case when (sov.rpsfMajorFWHM > 0 and sov.rpsfMinorFWHM > 0)
then (sov.rpsfMajorFWHM + sov.rpsfMinorFWHM)/2 else 1e8 end) as rSeeing,
(case when (sov.ipsfMajorFWHM > 0 and sov.ipsfMinorFWHM > 0)
then (sov.ipsfMajorFWHM + sov.ipsfMinorFWHM)/2 else 1e8 end) as iSeeing,
(case when (sov.zpsfMajorFWHM > 0 and sov.zpsfMinorFWHM > 0)
then (sov.zpsfMajorFWHM + sov.zpsfMinorFWHM)/2 else 1e8 end) as zSeeing,
(case when (sov.ypsfMajorFWHM > 0 and sov.ypsfMinorFWHM > 0)
then (sov.ypsfMajorFWHM + sov.ypsfMinorFWHM)/2 else 1e8 end) as ySeeing,
(case when (sov.gPSFFlux > 0 AND sov.gKronFlux > 0 AND sov.gApFlux > 0)
then power(sov.gPSFFlux,2)/power(sov.gPSFFluxErr,2) else 0 end) as gPSFWeight,
(case when (sov.rPSFFlux > 0 AND sov.rKronFlux > 0 AND sov.rApFlux > 0)
then power(sov.rPSFFlux,2)/power(sov.rPSFFluxErr,2) else 0 end) as rPSFWeight,
(case when (sov.iPSFFlux > 0 AND sov.iKronFlux > 0 AND sov.iApFlux > 0)
then power(sov.iPSFFlux,2)/power(sov.iPSFFluxErr,2) else 0 end) as iPSFWeight,
(case when (sov.zPSFFlux > 0 AND sov.zKronFlux > 0 AND sov.zApFlux > 0)
then power(sov.zPSFFlux,2)/power(sov.zPSFFluxErr,2) else 0 end) as zPSFWeight,
(case when (sov.yPSFFlux > 0 AND sov.yKronFlux > 0 AND sov.yApFlux > 0)
then power(sov.yPSFFlux,2)/power(sov.yPSFFluxErr,2) else 0 end) as yPSFWeight,
(case when (sov.gPSFFlux > 0 AND sov.gKronFlux > 0 AND sov.gApFlux > 0)
then power(sov.gKronFlux,2)/power(sov.gKronFluxErr,2) else 0 end) as gKronWeight,
(case when (sov.rPSFFlux > 0 AND sov.rKronFlux > 0 AND sov.rApFlux > 0)
then power(sov.rKronFlux,2)/power(sov.rKronFluxErr,2) else 0 end) as rKronWeight,
(case when (sov.iPSFFlux > 0 AND sov.iKronFlux > 0 AND sov.iApFlux > 0)
then power(sov.iKronFlux,2)/power(sov.iKronFluxErr,2) else 0 end) as iKronWeight,
(case when (sov.zPSFFlux > 0 AND sov.zKronFlux > 0 AND sov.zApFlux > 0)
then power(sov.zKronFlux,2)/power(sov.zKronFluxErr,2) else 0 end) as zKronWeight,
(case when (sov.yPSFFlux > 0 AND sov.yKronFlux > 0 AND sov.yApFlux > 0)
then power(sov.yKronFlux,2)/power(sov.yKronFluxErr,2) else 0 end) as yKronWeight,
(case when (sov.iPSFFlux > 0 AND sov.iKronFlux > 0 AND sov.iApFlux > 0)
then 1 else null end) as iDet
from
(select sov.objid
from stackobjectview as sov
where sov.primaryDetection = 1 and
sov.nDetections > 2 and
sov.decStack between cast(ZZZYYYXXX as float)/3 and cast(ZZZYYYXXX as float)/3
group by sov.objid
having count(sov.objid) = 1) s join stackobjectview as sov on s.objid = sov.objid
where sov.primaryDetection = 1
) dummy1
join stackobjectview as sov on dummy1.objid = sov.objid
where gKronWeight + rKronWeight + iKronWeight + zKronWeight + yKronWeight > 0
and sov.primaryDetection = 1) dummy2