== 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:

[[Image(ideal.png)]]
 == 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)'''