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

PSPS Integrity Checks

Step 1. Make mydbs of distinct ippobjid for each of st, ob, p2 for that ra/dec range:

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)