Changeset 36736 for trunk/ippToPsps/jython/objectbatch.py

Timestamp:

May 9, 2014, 10:07:40 AM (12 years ago)

Author:

heather

Message:

ipptopsps: stacks are happy, objects are in progress

File:

• trunk/ippToPsps/jython/objectbatch.py (modified) (14 diffs)

trunk/ippToPsps/jython/objectbatch.py

@@ -91 +91 @@
 
         # dec is reserved in MySQL, so STILTS replaces if with dec_, which PSPS doesn't like. so, force it back again using ``
-        self.scratchDb.execute("ALTER TABLE Object CHANGE dec_ `dec` double")
+      #  self.scratchDb.execute("ALTER TABLE MeanObject CHANGE dec_ `dec` double")
+      #  self.scratchDb.execute("ALTER TABLE ObjectThin CHANGE dec_ `dec` double")
 
         return True
@@ -107 +108 @@
     Inserts stuff for all mags
     '''
-    def insertMags(self, cpsTable):
+    def insertMeanMags(self, cpsTable):
 
         # list of all filters PSPS is interested in
@@ -129 +130 @@
             # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags
 
-            sql = "UPDATE Object JOIN \
-                   " + cpsTable + " AS cps ON (cps.row = (Object.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
+            sql = "UPDATE MeanObject JOIN ObjectThin using (objID) JOIN \
+                   " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
                    SET \
-                   n" + filter[1] + " = NCODE \
-                   ," + filter[1] + "MeanPSFMag = MAG \
-                   ," + filter[1] + "MeanPSFMagErr = MAG_ERR \
-                   ," + filter[1] + "MeanKronMag = MAG_KRON \
-                   ," + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \
-                   ," + filter[1] + "StackPSFMag = STACK_PSF_MAG \
-                   ," + filter[1] + "StackPSFMagErr = STACK_PSF_MAG_ERR \
-                   ," + filter[1] + "StackKronMag = STACK_KRON_MAG \
-                   ," + filter[1] + "StackKronMagErr = STACK_KRON_MAG_ERR \
-                   ," + filter[1] + "20pct = MAG_20/1000 \
-                   ," + filter[1] + "80pct = MAG_80/1000 \
-                   ," + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) \
-                   ," + filter[1] + "StackDetectID = STACK_DETECT_ID"
-
-# old method for setting the Stack (PSF,Kron) Mags:
-#                  ," + filter[1] + "StackPSFMag = -2.5 * log10(FLUX_PSF) + 8.9 \
-#                  ," + filter[1] + "StackPSFMagErr = FLUX_PSF_ERR / FLUX_PSF \
-#                  ," + filter[1] + "StackKronMag = -2.5 * log10(FLUX_KRON) + 8.9 \
-#                  ," + filter[1] + "StackKronMagErr = FLUX_KRON_ERR / FLUX_KRON \
-
-            #self.logger.info(sql)
+                   ObjectThin.n" + filter[1] + " = NCODE \
+                   ,MeanObject." + filter[1] + "MeanPSFMag = MAG \
+                   ,MeanObject." + filter[1] + "MeanPSFMagErr = MAG_ERR \
+                   ,MeanObject." + filter[1] + "MeanKronMag = MAG_KRON \
+                   ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \
+                   ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) "
+
+
+            self.logger.info(sql)
             self.scratchDb.execute(sql)
 
@@ -158 +147 @@
         for filter in filters:
             # now do a sum of n[filters], but do not include the ones with -999
-            sql = "UPDATE Object \
+            sql = "UPDATE ObjectThin join MeanObject using (objID)  \
                    SET nDetections = nDetections + n" + filter[1] + " \
                    WHERE n" + filter[1] + " != -999"
@@ -171 +160 @@
         for filter in filters:
 
-            sql = "UPDATE Object \
+            sql = "UPDATE MeanObject \
                    SET " + filter[1] + "MeanPSFMagErr = null \
                    WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut)
             self.scratchDb.execute(sql)
 
-        self.logger.infoPair("Setting to NULL all x20pct =", "32.767" ) 
-        for filter in filters:
-            sql  = "UPDATE Object \
-                   SET " + filter[1] + "20pct = null \
-                   WHERE " + filter[1] + "20pct > 32.766"
-            self.scratchDb.execute(sql)
-        self.logger.infoPair("Setting to NULL all x80pct =", "32.767" )
-        for filter in filters:
-            sql  = "UPDATE Object \
-                    SET " + filter[1] + "80pct = null \
-                    WHERE " + filter[1] + "80pct > 32.766"
-            self.scratchDb.execute(sql)
-
-
-
-
-    '''
-    Inserts colors
-    '''
-    def updateColors(self):
-
-        self.logger.infoPair("Calculating", "colors")
-        sql = "UPDATE Object \
-               SET \
-               grMeanColor = (gMeanMag - rMeanMag) \
-               ,riMeanColor = (rMeanMag - iMeanMag) \
-               ,izMeanColor = (iMeanMag - zMeanMag) \
-               ,zyMeanColor = (zMeanMag - yMeanMag) \
-               ,grMeanColorErr = SQRT(POW(gMeanMagErr,2) + POW(rMeanMagErr,2)) \
-               ,riMeanColorErr = SQRT(POW(rMeanMagErr,2) + POW(iMeanMagErr,2)) \
-               ,izMeanColorErr = SQRT(POW(iMeanMagErr,2) + POW(zMeanMagErr,2)) \
-               ,zyMeanColorErr = SQRT(POW(zMeanMagErr,2) + POW(yMeanMagErr,2)) \
-                   "
-        try:
-            self.scratchDb.execute(sql)
-        except:
-            self.logger.errorPair("Couldn't calculate colors", sql)
 
     '''
     Populates the Object table
     '''
-    def populateObjectTable(self):
+    def populateObjectThinTable(self):
 
         cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
@@ -242 +194 @@
 
         
-        self.logger.infoPair("Populating", "Object s")
+        self.logger.infoPair("Populating", "ThinObject")
         self.logger.infoPair("Inserting objects from", "cpt file")
 
         # note `` around dec here, as this is a reserved word in MySQL
-        sql = "INSERT IGNORE INTO Object (\
+        sql = "INSERT IGNORE INTO ObjectThin (\
                objID \
+               ,gcobjID \
                ,ippObjID \
+               ,surveyID \
+               ,skyCellID \
+               ,randomID \
+               ,batchID \
+               ,dataRelease \
                ,objInfoFlag \
                ,qualityFlag \
-               ,surveyID \
-               ,ra \
-               ,`dec` \
-               ,raErr \
-               ,decErr \
+               ,raMean \
+               ,decMean \
+               ,raMeanErr \
+               ,decMeanErr \
                ,nDetections \
-               ,qfPerfect \
-               ,sgSep \
-               ,dataRelease \
-               ,Random \
-               ,batchID \
-               ) \
+                ) \
                SELECT \
                EXT_ID \
+               , -999 \
                ,CAT_ID*1000000000 + OBJ_ID \
+               ," + str(self.surveyID) + " \
+               ,-999 \
+               , RAND() \
+               , " + str(self.batchID) + "\
+               , " + str(self.skychunk.dataRelease) + "\
                ,FLAGS \
                ,FLAGS >> 24 & 0xFF \
-               ," + str(self.surveyID) + " \
                ,RA \
                ,DEC_ \
@@ -274 +231 @@
                ,DEC_ERR \
                ,0 \
-               ,PSF_QF_PERF \
-               ,STARGAL_SEP \
-               , " + str(self.skychunk.dataRelease) + "\
-               , RAND() \
-               , " + str(self.batchID) + "\
-               FROM " + cptTableName
+                FROM " + cptTableName
 
         try:
@@ -289 +241 @@
         # add row count columns so we can perform joins to get colors
         self.logger.infoPair("Adding 'row' columns to", "Object and cps tables")
-        self.scratchDb.addRowCountColumn("Object", "row")
+        self.scratchDb.addRowCountColumn("ObjectThin", "row")
         self.scratchDb.addRowCountColumn(cpsTableName, "row")
 
-        self.insertMags(cpsTableName)
+ #       self.insertMeanMags(cpsTableName)
 
 #don't do this as we removed those columns.. 
@@ -312 +264 @@
         #count out of range
 
-        sql = "SELECT count(*) FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin)       
+        sql = "SELECT count(*) FROM ObjectThin where \
+              ObjectThin.decMean > " + str(decMax) + " \
+              or ObjectThin.decMean < " + str(decMin) + " \
+              or ObjectThin.raMean > " + str(raMax) + " \
+              or ObjectThin.raMean < " + str(raMin)       
     
         rs = self.scratchDb.executeQuery(sql)
@@ -321 +277 @@
         
  
-        sql = "DELETE FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin) 
+        sql = "DELETE FROM ObjectThin where \
+              ObjectThin.decMean > " + str(decMax) + " or \
+              ObjectThin.decMean < " + str(decMin) + " or \
+              ObjectThin.raMean > " + str(raMax) + " or \
+              ObjectThin.raMean < " + str(raMin) 
         self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range")
 
@@ -327 +287 @@
             self.scratchDb.execute(sql)
         except:
-            self.logger.errorPair("Couldn't cull outsiders from Object table", sql)
+            self.logger.errorPair("Couldn't cull outsiders from ObjectThin table", sql)
             return False
 
-        self.logger.infoPair("Dropping row column from", "Object table")
-        self.scratchDb.dropColumn("Object", "row")
+        self.logger.infoPair("Dropping row column from", "ObjectThin table")
+        self.scratchDb.dropColumn("ObjectThin", "row")
         self.logger.infoPair("Purging from scratch Db", self.region + " region")
 
         self.dvoObjects.purgeRegion(self.region)
 
-        self.setMinMaxObjID(["Object"])
+        self.setMinMaxObjID(["ObjectThin"])
 
         return True
@@ -343 +303 @@
     Populates the ObjectCalColor table
     '''
-    def populateObjectCalColorTable(self):
-
-        self.logger.infoPair("Populating", "ObjectCalColor")
-
-        sql = "INSERT INTO ObjectCalColor ( \
+    def populateMeanObjectTable(self):
+
+        self.logger.infoPair("Populating", "MeanObject")
+
+        sql = "INSERT INTO MeanObject ( \
                objID \
-               ,ippObjID \
-               ,dataRelease \
+               ,gcobjID \
                ) \
                SELECT \
                objID \
-               ,ippObjID \
-               ,dataRelease \
-               FROM Object"
+               ,gcobjID \
+               FROM ObjectThin"
         try:
             self.scratchDb.execute(sql)
         except:
-            self.logger.errorPair("Couldn't populate ObjectCalColor table", sql)
+            self.logger.errorPair("Couldn't populate MeanObject table", sql)
             return False
 
+        self.insertMeanMags("MeanObject")
         return True
 
@@ -370 +329 @@
     def populatePspsTables(self):
 
-        if not self.populateObjectTable(): return False
+        if not self.populateObjectThinTable(): return False
         #if not self.populateObjectCalColorTable(): return False
+        if not self.populateMeanObjectTable(): return False
 
         # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number
-        self.logger.infoPair("Forcing uniqueness on", "objID in Object table")
-        rowCountBefore = self.scratchDb.getRowCount("Object")
+        self.logger.infoPair("Forcing uniqueness on", "objID in ObjectThin table")
+        rowCountBefore = self.scratchDb.getRowCount("ObjectThin")
 
         # XXX EAM : note that in mysql versions later than 5.1, this fails
@@ -386 +346 @@
             self.scratchDb.execute("set session old_alter_table=1")
             
-        self.scratchDb.execute("ALTER IGNORE TABLE Object ADD UNIQUE INDEX(objID)")
+        self.scratchDb.execute("ALTER IGNORE TABLE ObjectThin ADD UNIQUE INDEX(objID)")
         if self.scratchDb.version > 5.1:
             self.scratchDb.execute("set session old_alter_table=0")
 
+        rowCountAfter = self.scratchDb.getRowCount("ObjectThin")
+        self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
+
+        self.logger.infoPair("Forcing uniqueness on", "objID in MeanObject table")
+        rowCountBefore = self.scratchDb.getRowCount("MeanObject")
+
+        # XXX EAM : note that in mysql versions later than 5.1, this fails
+        # unless the following is called first: 
+        # set session old_alter_table=1
+        # follow the command with 
+        # set session old_alter_table=0
+        # OF COURSE, this fails for mysql version < 5.5...
+        if self.scratchDb.version > 5.1:
+            self.scratchDb.execute("set session old_alter_table=1")
+            
+        self.scratchDb.execute("ALTER IGNORE TABLE MeanObject ADD UNIQUE INDEX(objID)")
+        if self.scratchDb.version > 5.1:
+            self.scratchDb.execute("set session old_alter_table=0")
+
+        rowCountAfter = self.scratchDb.getRowCount("MeanObject")
+        self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
+
+
+        #this is abuse of something but this is how I get the object batches to crash to further investigate them
+        
         rowCountAfter = self.scratchDb.getRowCount("Object")
-        self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
-
-        return True
-
+        #return True
+        return False