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:

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