Changes between Initial Version and Version 1 of SAS9DataIntegrity

Timestamp:

Oct 3, 2012, 9:00:32 PM (14 years ago)

Author:

heather

Comment:

--

SAS9DataIntegrity

@@ +1 @@
+
+
+ == 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)'''
+
+
+