[[PageOutline]]

== PSPS SA9 objects/ndetections investigations ==

There are problems with ndetections in objects table in PSPS, there are several varieties of problems:

=== ndetections = -999 ===

How to find:
{{{
select count(*) from object where ndetections = -999
}}}

Heather did a slightly different version, to try to figure out where these things live:

{{{
select (ippobjid/1000000000), count (*) as n from objects where ndetections = -999 group by (ippobjid/1000000000) order by n desc
}}}

This gives the following ( there are a lot of them these are only the top 'interesting' ones):

|| ippobjid thing || count(*) || batch ||
||93293 ||	12300 || 360500 -- this is the failed ob batch  why do these go into the db?||
||93096 ||	7398 ||   no batch ||
||18740 ||	6601||  ... ||
||93092||	6577||
||18744	||6351||
||93116	||5752||
||93120||	5657||
||93088||	3902||
||93100||	3866||
||18768||	3552||
||18748||	3463||
||93112||	3323||
||18756||	3205||
||18764||	231||
||18986||	213 ||
||etc   ||   ||

The first one is interesting - it is the OB batch that failed to load. That should not be put into PSPS (heather thinks)

The others, what are they?  The ippobjid/1000000000 corresponds to the 'stage_id', but according to ipptopsps.batch, there is no batch with those stage ids.. I grabbed one of the fault ones:
 * objid = 104073276979184735	
 * ippobjid = 93096000007333
 * ra = 327.697918
 * dec = -3.27140108245

and asked which batches it could live in (based on min/max obj id). I get the following 20 batches which I downloaded:
{{{
mysql> select batch_id, stage_id from batch where min_obj_id < 104073276979184735 and
 max_obj_id > 104073276979184735 and dvo_db = 'SAS.20120905' and batch_type = 'OB';
+----------+----------+
| batch_id | stage_id |
+----------+----------+
|   359431 |    93354 | 
|   359433 |    93355 | 
|   359614 |    93157 | 
|   359615 |    93158 | 
|   359616 |    93159 | 
|   359617 |    93160 | 
|   360325 |    93259 | 
|   360332 |    93260 | 
|   360367 |    93305 | 
|   360373 |    93306 | 
|   361579 |    93307 | 
|   361582 |    93308 | 
|   361595 |    93353 | 
|   361676 |    93209 | 
|   361762 |    93257 | 
|   361781 |    93258 | 
|   362277 |    93356 | 
|   362355 |    93210 | 
|   362356 |    93211 | 
|   362362 |    93212 | 
+----------+----------+
20 rows in set (0.14 sec)
}}}
i looked into each batch and I can't find that stupid objid...   Where is it coming from??

i plotted ra dec of the -999 objects, see here:

[[Image(canvassas.png)]]

so it's looking (potentially) like the bulk of the stuff is on the edges... hmmm...

range of -999 detections

327.674428 	339.401002 	-5.56900272534 	6.29028233733

range of non -999 detections

328.125006286168 	339.785531622512 	-5.56905205605347 	6.24998288116431

ooookay... lets see how many items are out of range and what they are

{{{
select  ndetections, count(*) from object where ( ra < 328.125006286168  or ra > 339.785531622512 ) group by ndetections
}}}

which gives me 
[ndetections]:Int16 	[Column1]:Int32
-999 	60649

ok, so most of those guys are ugh...  out of range...

lets get the batch like thing:

{{{
select  ippobjid/1000000000 , ndetections, count(*) as n from object where 
( ra < 328.125006286168  or ra > 339.785531622512  
or dec > 6.24998288116431 or dec < -5.56905205605347) group by ndetections,ippobjid/1000000000  
order by n desc
}}}


ippobjid/thing || ndetections || count (*) ||
||93096 ||	-999 ||	7398||
||18740 ||	-999 	||6601||
||93092 ||	-999 ||	6577||
||18744 ||	-999 	||6351||
||93116 ||	-999 	||5752||
||93120 ||	-999 	||5657||
||93088 ||	-999 	||3902||
||93100 ||	-999 	||3866||
||18768 ||	-999 	||3552||
||18748 ||	-999 	||3463||
||93112 ||	-999 	||3323||
||18756 ||	-999 	||3205||
||18764 ||	-999 	||231||
||18986 ||	-999 	||213||
||93084 ||	-999 	||173||
||93104 ||	-999 	||121||
||93108 ||	-999 	||114||
||18985 ||	-999 	||77||
||18760 ||	-999 	||38||
||18752 ||	-999 	||35||

these are the exact same numbers as above - so - '''The -999s almost entirely come from 2 sources.  12300 come from the failed batch, 60649 come from being outside of some range that heather doesn't understand ''' Some further strange ness:

* ipptopsps loads from 328 - 340 and from -6 6 -- so some of this stuff is out of range for ipptopsps. Ipptopsps has no record of pushing it into the datastore, so how does psps know about this stuff? from stack/detection tables? YES! those stack/detection tables load fakey objects in there (ie, objects with ippobjid/ra/dec and -999 for everything else) (from email from Conrad).  


=== ndetections = very large number ===

this is done on a subset of sas (ra between 333 and 335 and dec  between -1 and 1) to avoid edges. I named this sas9objects2.  Here is a query to find the 10 worst offenders:

{{{
select top 10 ippobjid, ndetections, ng, nr, ni, nz, ny, ndetections -ng -nr -ni -nz -ny from sas9objects2 order by ndetections desc
}}}

Here is the result: 

{{{
[ippobjid] 	[ndet] 	[ng]	[nr] 	[ni] 	[nz] 	[ny] 	[diff]
93275000002053 	123 	19 	32 	33 	22 	17 	0
18914000000761 	122 	21 	31 	24 	27 	19 	0
18920000002724 	118 	26 	28 	24 	22 	18 	0
93279000002817 	116 	23 	24 	22 	25 	22 	0
18914000001122 	115 	18 	26 	25 	28 	18 	0
93277000000012 	107 	17 	26 	20 	23 	21 	0
18913000000215 	107 	17 	25 	19 	26 	20 	0
18916000003621 	106 	22 	24 	15 	24 	21 	0
18916000003381 	105 	21 	26 	18 	22 	18 	0
18918000000040 	105 	24 	24 	17 	16 	24 	0
}}}

123 detections!?!?! what?!

lets take a look at the first one (93275000002053):

stacks:
{{{
select filterId, count(*) from stackdetectionfull where  ippobjid = 93275000002053 group by filterid

1 	1
2 	1
3 	1
4 	1
5 	1
}}}

detections:
{{{
select filterId, count(*) from detectionfull where  ippobjid = 93275000002053 group by filterid

1 	15
2 	14
3 	15
4 	11
5 	10

}}}

these do not add up... the objects claim that there are 14 in r filter, but the detection table says 32...  What's going on here?!

latest idea is that ipptopsps is dropping detections/stack detections which alters the count. see here: http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/wiki/SAS9DataIntegrity#ipptopspsFilters

=== ndetections != ng + nr + ni + nz + ny ===

I made a subset of objects in ra between 333 and 335 and dec between -1 and 1 (to make certain I can map to everything I expect in detections and in stacks). This subset is called sas9objects2 ... Here is a query to find examples where ndetection != sum (n[x])

{{{
select (ndetections - ng - nr -ni -nz -ny), count(*) from sas9objects2 group by ((ndetections - ng - nr -ni -nz -ny) )
order by ((ndetections - ng - nr -ni -nz -ny) )
}}}

with the following result:

{{{
diff    count(diff)
0 	736412
1 	20040
2 	5478
3 	1142
4 	419
5 	33
6 	35
7 	3
8 	13
}}}

how do these get put into psps? ipptopsps grabs n[x] from NCODE in dvo, and ndetections from NMEASURE. This is something to ask Gene about (why NMEASURE sometimes is not equal to sum(NCODE))

Some more investigations:

{{{
select (ndetections - ng - nr -ni -nz -ny), count(*) from object where ndetections> -999 group by ((ndetections - ng - nr -ni -nz -ny) )
order by ((ndetections - ng - nr -ni -nz -ny) )
}}}

This gives a table like this: 

{{{
diff    count(diff)
0 	14332649
1 	436944
2 	113206
3 	31720
4 	12837
5 	729
6 	1337
7 	78
8 	299
9 	37
10 	5
11 	6
12 	15
13 	2
22 	1
}}}

lets investigate in my subset of objects. Lets look at where the difference = 8

{{{
select * from sas9objects2 where ((ndetections - ng - nr -ni -nz -ny) ) = 8 
}}}
{{{
ippobjid        ra                      dec                    ndet    ng      nr      ni       nz      ny
18915000068213 	334.003331264054 	0.173672694705413 	10 	0 	0 	0 	0 	2
18914000071139 	333.565131996048 	0.0149163575293166 	10 	0 	0 	0 	0 	2
93274000064190 	333.210589389621 	-0.823610922500866 	10 	0 	0 	0 	0 	2
93274000064238 	333.213851183943 	-0.820473251004263 	10 	0 	0 	0 	0 	2
93278000058694 	333.610383900768 	-0.178547334031098 	11 	0 	0 	0 	0 	3
18916000069421 	334.402330333111 	0.388832517325818 	19 	0 	0 	4 	4 	3
93275000078069 	333.805282911705 	-0.811373935148214 	10 	0 	0 	0 	0 	2
93274000064493 	333.229893419105 	-0.807992283581902 	10 	0 	0 	0 	0 	2
93273000067099 	333.017920578856 	-0.798191607076108 	10 	0 	0 	0 	0 	2
18916000071745 	334.794921756804 	0.621440169464687 	10 	0 	0 	0 	0 	2
18920000067165 	334.698473145177 	0.799924706194001 	10 	0 	0 	0 	0 	2
18920000068631 	334.80890851311 	0.808805008767286 	20 	4 	0 	0 	4 	4
93279000050876 	333.919744618416 	-0.0100643274020041 	11 	0 	0 	0 	0 	3
}}}

ok. I'm going to grab a couple of them (18915000068213 and 18920000068631) and see what they look like in stackdetectionfull and detectionfull:


for 18915000068213:

{{{
select ippobjid, filterid, instflux, instfluxerr from stackdetectionfull where ippobjid = 18915000068213  order by filterid
}}}

{{{
ippobjid       filterid instflux        instfluxerr
18915000068213 	1 	1.28959 	-999
18915000068213 	1 	1.31326 	-999
18915000068213 	2 	1.42652 	-999
18915000068213 	2 	1.42781 	-999
18915000068213 	3 	1.56402 	-999
18915000068213 	3 	1.66755 	-999
18915000068213 	4 	3.29652 	-999
18915000068213 	4 	3.29929 	-999
18915000068213 	5 	22.7659 	4.12512
18915000068213 	5 	23.4272 	4.06752
}}}

for 18920000068631

{{{
select ippobjid, filterid, instflux, instfluxerr from stackdetectionfull where ippobjid = 18920000068631  order by filterid
}}}

{{{
ippobjid       filterid instflux         instfluxerr
18920000068631 	1 	0.44921 	0.729298
18920000068631 	1 	0.857526 	0.686621
18920000068631 	1 	0.905281 	0.692741
18920000068631 	1 	1.10313 	0.753273
18920000068631 	2 	1.43996 	-999
18920000068631 	2 	1.50241 	-999
18920000068631 	2 	1.50626 	-999
18920000068631 	2 	1.759 	        -999
18920000068631 	3 	1.56143 	-999
18920000068631 	3 	1.60222 	-999
18920000068631 	3 	1.61214 	-999
18920000068631 	3 	1.66739 	-999
18920000068631 	4 	2.88612 	1.94558
18920000068631 	4 	3.2843 	        1.95199
18920000068631 	4 	3.54056 	1.91966
18920000068631 	4 	3.92929 	1.96794
18920000068631 	5 	28.1172 	4.92211
18920000068631 	5 	28.1232 	4.99574
18920000068631 	5 	28.8979 	5.30701
18920000068631 	5 	30.4016 	5.37306
}}}

checking detectionfull returns nothing for both of these objects, here are the queries to check: 

{{{
select ippobjid, filterid, instflux, instfluxerr from detectionfull where ippobjid = 18915000068213  order by filterid
select ippobjid, filterid, instflux, instfluxerr from detectionfull where ippobjid = 18920000068631  order by filterid

}}}

But, it appears that the missing detections do exist in stackdetectionfull as limiting magnitudes...