Data Integrity Tests and What We Expect

This was started using conrad's stuff here: ​http://ps1wiki.ifa.hawaii.edu/trac/wiki/PSPSODMTestQueries on SA9, until I abandoned it (I will explain)

• Data integrity test of the whole SAS will fail, I am not going to try it. We expect it to fail:
  • 1 OB and 1 P2 batch failed to load due to ra/dec range on PSPS loading
  • there are a dozen ST batches that failed to even get created (the quality of the skycell is bad)
  • there are OB batches that fail to be created due to ra/dec ranges on ipptopsps. detections for those areas are in the PSPS survey, because they are filtered based on the center of the exposure.

• What about data integrity on a subset of data? Lets look at ra between 333 and 335 and dec between -1 and 1. What do we expect for good data?
  • P2 has 0 items that are not in OB, and all P2 should be in OB (p2 detections should all have objects)
  • ST has 0 items that are not in OB, and all ST should be in OB (st detections should all have objects)
  • We don't care much about the intersections of P2 and ST. Why:
    • P2 can have items that should not show up in the stacks
      • real things like comets only exist on 1 exposure therefore not in stacks
      • imaginary things like false detections and camera gunk should only exist at a given ra/dec for 1 exposure, therefore not in stacks
    • ST can have items that do not show up in P2 because it goes fainter
    • ST batches can be missing (causing piles of ST items to be gone) for legitimate reasons:
      • not enough exposures to stack
      • bad quality flags in the stacks - we do not want these in there anyways

We want a venn diagram that looks like this:

PSPS Integrity Checks

Step 1. Make mydbs of distinct ippobjid for each of st, ob, p2 for that ra/dec range. I prefer this way because then you can do simple joins on the resulting tables without worrying about distincts and ra/dec ranges for all.

Objects: note that we want to avoid the -999 objects that come from stacks and detections but aren't filled by the object batches (they are placeholders of a sort, but they aren't useful for this, and that's another thing to investigate. i want objects filled by object batches). however I do both because I'm curious

SELECT distinct ippobjid INTO mydb.SAS9ob FROM object WHERE (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1) and ndetections > -999

SELECT distinct ippobjid INTO mydb.SAS9ob2 FROM object WHERE (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1) 

Detections and stacks are straightforward

SELECT distinct ippobjid INTO mydb.SAS9st FROM stackdetectionfull WHERE (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1)

SELECT distinct ippobjid INTO mydb.SAS9p2 FROM detectionfull WHERE (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1)

Step 2. Count things. I basically only care that all P2s are in OB and all STs are in OB. To find them, do these queries on the fast queue:

Find STs not in OB:

SELECT count(*) from sas9ob right join sas9st on sas9ob.ippobjid=sas9st.ippobjid where sas9ob.ippobjid is null 

Find P2s not in OB:

SELECT count(*) from sas9ob right join sas9p2 on sas9ob.ippobjid=sas9st.ippobjid where sas9ob.ippobjid is null 

Find STs in OB:

SELECT count(*) from sas9ob inner join sas9st on sas9ob2.ippobjid=sas9p2.ippobjid

Find P2s in OB:

SELECT count(*) from sas9ob inner join sas9p2 on sas9ob2.ippobjid=sas9p2.ippobjid

Verify OBs don't have multiple things with same ippobjid, these 2 queries should give the same answer

SELECT count(distinct ippobjid) FROM object WHERE (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1) and ndetections > -999

SELECT count(ippobjid) FROM object WHERE (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1) and ndetections > -999

Results

For my area of sky I picked (ra between 333 and 335, dec between -1 and 1), these are the results.

total number of OB vs distinct OB: both give 763575 

count of sas9OB: 763575
count of sas9P2: 651695  
count of sas9ST: 209218

Now for who's in both ST and OB and P2 and OB

count of sas9P2 in sas9OB : 651651
count of sas9ST in sas9OB : 209163

count of sas9P2 in sas9OB should be 651695 and count of sas9ST in sas9OB should be 209218

The following are the problem children (should be investigated separately)

sas9P2 not in sas9OB: 44
sas9ST not in sas9OB: 55

There are 763575 objects and 99 that I think should be objects (they have p2 or st detections)

Ahhh! But what about the difference between doing the checks on OB with all the detections between the ra and dec range and with excluding ndetections = -999? Turns out it's not that much different, but I didn't want to pollute things with objects that hadn't been filled with object batches. Basically, I get similar numbers, except I find 24 P2s that are not in OB (so, 20 of them were hiding in the fake objects), and 46 STs not in OBs (so, 9 are hiding in the fake objects). I also found 763575 vs 763601 for the object counts. There are 26 objects with ndetection= -999. Things to investigate:

• the 26 objects in the ra/dec range with ndetections = -999
• the 24 P2s that do not exist in objects (why not?)
• the 46 STs that do not exist in objects (why not?)

PSPS Integrity Checks II

These checks are similar to stsci but involve a small area of small area survey (this prevents edge problems).

first make 3 tables containing just the ra/dec range:

select ippobjid, ra, dec, ndetections, ng, nr, ni,nz, ny INTO mydb.sas9objects2 from Object where (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1) and ndetections > -999 
select ippobjid, filterID, ra, dec, calMag, calMagerr into mydb.sas9detects2 from DetectionFull where (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1)
select ippobjid, filterID, ra, dec, calMag, calMagerr into mydb.sas9stacks2 from stackDetectionFull where (ra >= 333 and ra <= 335 and dec >= -1 and dec <=1) 

do various queries to find counts:

select sum(ndetections), sum(ng), sum(nr), sum(ni), sum(nz), sum(ny) from sas9objects2
select count(*)  from sas9stacks2
select count(*), filterID  from sas9stacks2 group by filterID
select count(*) from sas9detects2 
select count(*), filterID  from sas9detects2 group by filterID

results:

filter	sum(nfilter) on objects	count(stacks)	count(p2)	count(stacks) + count(p2)	comments
all	4933667 sum(ndetection)	887726	3193652	4081378	we expect : sum(ng) + sum(nr) + sum(ni) + sum(nz) + sum(ny) = sum(ndetections) = count(stacks) + count(p2) => 4897069 != 4933667 != 4081378
g	686619	176180	454940	630670	we expect : sum(ng) = count(g stacks) + count(g p2) => 686619 != 630670
r	1229420	179934	701316	881250	1229420 != 881250
i	1214388	199140	898436	1097576	1214388!= 1097576
z	1097161	174063	685355	859418	1097161!=859418
y	669481	158409	453605	612014	669481!= 612014

This integrity fails on all levels. ng/nr/ni/nz/ny are all less than what we get from the sums of stacks and detections for those filters, and ndetection != ng + nr + ni + nz + ny. These are set in the dvo. Heather suspects this is a dvo problem.