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

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...