== 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?!
=== 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
}}}