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

example:

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