Changeset 37763 for trunk/ippToPsps/jython/diffobjectbatch.py

Timestamp:

Dec 18, 2014, 6:00:15 PM (12 years ago)

Author:

heather

Message:

st/ob/p2/df/do batches all work but have minimal problems in teh columns

File:

• trunk/ippToPsps/jython/diffobjectbatch.py (modified) (16 diffs)

trunk/ippToPsps/jython/diffobjectbatch.py

@@ -134 +134 @@
             #  " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
 
-            sql = "UPDATE DiffObject JOIN \
-                   " + cpsTable + " AS cps ON (cps.row = (DiffObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
+            sql = "UPDATE DiffDetObject JOIN \
+                   " + cpsTable + " AS cps ON (cps.row = (DiffDetObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
                    SET \
-                    MeanObject." + filter[1] + "QfPerfect      = PSF_QF_PERF_MAX \
-                   ,MeanObject." + filter[1] + "MeanPSFMag     = MAG \
-                   ,MeanObject." + filter[1] + "MeanPSFMagErr  = MAG_ERR \
-                   ,MeanObject." + filter[1] + "MeanPSFMagStd  = MAG_STDEV \
-                   ,MeanObject." + filter[1] + "MeanPSFMagMin  = MAG_MIN \
-                   ,MeanObject." + filter[1] + "MeanPSFMagMax  = MAG_MAX \
-                   ,MeanObject." + filter[1] + "MeanPSFMagNpt  = NUSED \
-                   ,MeanObject." + filter[1] + "MeanKronMag    = MAG_KRON \
-                   ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \
-                   ,MeanObject." + filter[1] + "MeanKronMagStd = MAG_KRON_STDEV \
-                   ,MeanObject." + filter[1] + "MeanKronMagNpt = NUSED_KRON \
-                   ,MeanObject." + filter[1] + "MeanApMag      = MAG_AP \
-                   ,MeanObject." + filter[1] + "MeanApMagErr   = MAG_AP_ERR \
-                   ,MeanObject." + filter[1] + "MeanApMagStd   = MAG_AP_STDEV \
-                   ,MeanObject." + filter[1] + "MeanApMagNpt   = NUSED_AP \
-                   ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) "
-
+                    DiffDetObject." + filter[1] + "QfPerfect      = PSF_QF_PERF_MAX "
 
             try: self.scratchDb.execute(sql)
             except:
-                self.logger.errorPair("failed update MeanObject", sql)
+                self.logger.errorPair("failed update DiffDetObject", sql)
                 raise
-
-        # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12)
-        # XXX EAM 20140724 : keep this cut?
-        cut = 0.5
-        self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut)
-        for filter in filters:
-
-            sql = "UPDATE MeanObject \
-                   SET " + filter[1] + "MeanPSFMagErr = null \
-                   WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut)
-            self.scratchDb.execute(sql)
 
     '''
@@ -196 +169 @@
             # sqlLine = sqlUtility()
 
-            sql  = "UPDATE DiffObject JOIN "
+            sql  = "UPDATE DiffDetObject JOIN "
             sql += cpsTable + " AS cps "
-            sql += "ON (cps.row = (DiffObject.row * "
+            sql += "ON (cps.row = (DiffDetObject.row * "
             sql += str(filterCount) + ")-(" 
             sql += str(filterCount) + " - " 
@@ -204 +177 @@
             sql += ")) "
 
-            sql += "SET DiffObject.n" + filter[1] + " = NCODE, "
-            sql += "DiffObject.nDetections = DiffObject.nDetections + cps.NSTACK_DET"
+            sql += "SET DiffDetObject.n" + filter[1] + " = NCODE, "
+            sql += "DiffDetObject.nDetections = DiffDetObject.nDetections + cps.NSTACK_DET"
             self.logger.info(sql)
             self.scratchDb.execute(sql)
@@ -213 +186 @@
         for filter in filters:
             # now do a sum of n[filters], but do not include the ones with -999
-            sql  = "UPDATE DiffObject "
+            sql  = "UPDATE DiffDetObject "
             sql += "SET nDetections = nDetections + n" + filter[1]
             sql += " WHERE n" + filter[1] + " != -999"
@@ -227 +200 @@
         self.logger.infoPair("updating diffObjName", "using means")
     ## use mean ra and dec (dec >= 0)
-        sql = "update DiffObject set diffObjName =  concat('PS1.2 J', \
-        lpad(floor(raMean/15.),2,'0'), \
-        lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \
-        lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \
+        sql = "update DiffDetObject set diffObjName =  concat('PS1.2 J', \
+        lpad(floor(ra/15.),2,'0'), \
+        lpad((ra/15.-(floor(ra/15.)))*60. ,2,'0'), \
+        lpad(format(((ra/15.-(floor(ra/15.)))*60.-floor((ra/15.-(floor(ra/15.)))*60.))*60.,2),4,'0'), \
         '+', \
-        lpad(floor(decMean),2,'0'), \
-        lpad((decMean-(floor(decMean)))*60. ,2,'0'), \
-        lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \
-        ) where raMean > -999 and decMean > -999 and decMean >= 0"
+        lpad(floor(dec_),2,'0'), \
+        lpad((dec_-(floor(dec_)))*60. ,2,'0'), \
+        lpad(format(((dec_-(floor(dec_)))*60.-floor((dec_-(floor(dec_)))*60.))*60.,2),4,'0') \
+        ) where ra > -999 and dec_ > -999 and dec_ >= 0"
         try:
             self.scratchDb.execute(sql)
@@ -244 +217 @@
     ## use mean ra and dec (dec < 0)
 
-        sql = "update DiffObject set diffObjName =  concat('PS1.2 J', \
-        lpad(floor(raMean/15.),2,'0'), \
-        lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \
-        lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \
+        sql = "update DiffDetObject set diffObjName =  concat('PS1.2 J', \
+        lpad(floor(ra/15.),2,'0'), \
+        lpad((ra/15.-(floor(ra/15.)))*60. ,2,'0'), \
+        lpad(format(((ra/15.-(floor(ra/15.)))*60.-floor((ra/15.-(floor(ra/15.)))*60.))*60.,2),4,'0'), \
         '-', \
-        lpad(floor(decMean),2,'0'), \
-        lpad((decMean-(floor(decMean)))*60. ,2,'0'), \
-        lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \
-        ) where raMean > -999 and decMean > -999 and decMean < 0"
+        lpad(floor(dec_),2,'0'), \
+        lpad((dec_-(floor(dec_)))*60. ,2,'0'), \
+        lpad(format(((dec_-(floor(dec_)))*60.-floor((dec_-(floor(dec_)))*60.))*60.,2),4,'0') \
+        ) where ra > -999 and dec_ > -999 and dec_ < 0"
 
         try:
@@ -290 +263 @@
 
         
-        self.logger.info("Populating DiffObject")
+        self.logger.info("Populating DiffDetObject")
         self.logger.info("Inserting objects from cpt file")
 
@@ -297 +270 @@
         # INSERT IGNORE INTO ObjectThin
 
-        sqlLine = sqlUtility("INSERT INTO DiffObject (")
+        sqlLine = sqlUtility("INSERT INTO DiffDetObject (")
 
         sqlLine.group("diffObjID",           "EXT_ID") 
@@ -311 +284 @@
         sqlLine.group("consistencyFlag", "'0'") 
         sqlLine.group("ra",          "RA_MEAN")
-        sqlLine.group("dec",         "DEC_MEAN")
+        sqlLine.group("dec_",         "DEC_MEAN")
         sqlLine.group("raErr",       "RA_ERR")
         sqlLine.group("decErr",      "DEC_ERR")
@@ -320 +293 @@
             self.scratchDb.execute(sql)
         except:
-            self.logger.errorPair("Couldn't populate DiffObject table", sql)
+            self.logger.errorPair("Couldn't populate DiffDetObject table", sql)
             return False
  
         # add row count columns so we can perform joins to get colors
-        self.logger.infoPair("Adding 'row' columns to", "DiffObject and cps tables")
-        self.scratchDb.addRowCountColumn("DiffObject", "row")
+        self.logger.infoPair("Adding 'row' columns to", "DiffDetObject and cps tables")
+        self.scratchDb.addRowCountColumn("DiffDetObject", "row")
         self.scratchDb.addRowCountColumn(cpsTableName, "row")
 
 ##        self.insertMeanMags(cpsTableName)
 ## this is the new version
-        self.logger.infoPair("update diffObjName for ","DiffObject")
+        self.logger.infoPair("update diffObjName for ","DiffDetObject")
         self.updateDiffObjName()
 
-        self.logger.infoPair("update DiffObject from ","cps table")
+        self.logger.infoPair("update DiffDetObject from ","cps table")
 
         self.updateDiffObjectFromCps(cpsTableName)
@@ -353 +326 @@
         #count out of range
 
-        sql = "SELECT count(*) FROM DiffObject where \
-              DiffObject.dec > (" + str(decMax) + " + .0033) \
-              or DiffObject.dec < (" + str(decMin) + " - .0033) \
-              or DiffObject.ra > (" + str(raMax) + " + .0033) \
-              or DiffObject.ra < (" + str(raMin) + " - .0033)"       
+        sql = "SELECT count(*) FROM DiffDetObject where \
+              DiffDetObject.dec_ > (" + str(decMax) + " + .0033) \
+              or DiffDetObject.dec_ < (" + str(decMin) + " - .0033) \
+              or DiffDetObject.ra > (" + str(raMax) + " + .0033) \
+              or DiffDetObject.ra < (" + str(raMin) + " - .0033)"       
     
         rs = self.scratchDb.executeQuery(sql)
@@ -366 +339 @@
         
  
-        sql = "DELETE FROM DiffObject where \
-              DiffObject.decMean > (" + str(decMax) + " + .0033) or \
-              DiffObject.decMean < (" + str(decMin) + " - .0033) or \
-              DiffObject.raMean > (" + str(raMax) + " + .0033) or \
-              DiffObject.raMean < (" + str(raMin) + " - .0033)" 
+        sql = "DELETE FROM DiffDetObject where \
+              DiffDetObject.dec_ > (" + str(decMax) + " + .0033) or \
+              DiffDetObject.dec_ < (" + str(decMin) + " - .0033) or \
+              DiffDetObject.ra > (" + str(raMax) + " + .0033) or \
+              DiffDetObject.ra < (" + str(raMin) + " - .0033)" 
         self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range")
 
@@ -376 +349 @@
             self.scratchDb.execute(sql)
         except:
-            self.logger.errorPair("Couldn't cull outsiders from DiffObject table", sql)
+            self.logger.errorPair("Couldn't cull outsiders from DiffDetObject table", sql)
             return False
 
-        self.logger.infoPair("Dropping row column from", "DiffObject table")
-        self.scratchDb.dropColumn("DiffObject", "row")
+        self.logger.infoPair("Dropping row column from", "DiffDetObject table")
+        self.scratchDb.dropColumn("DiffDetObject", "row")
         ##self.logger.infoPair("Purging from scratch Db", self.region + " region")
 
@@ -386 +359 @@
         ##self.dvoObjects.purgeRegion(self.region)
 
-        self.setMinMaxDiffObjID(["DiffObject"])
+        self.setMinMaxDiffObjID(["DiffDetObject"])
 
         return True
+
+    '''
+    Updates table and generates pspsuniqueids
+    '''
+
+    def updatePspsUniqueIDs(self,table):
+        sql = "UPDATE "+table+" join (select @r:=@r+1 rownum, diffobjID from \
+        (select @r:=0) r, "+table+" t) as foo using (diffobjID) set \
+        uniquePspsDOid = ((" +str(self.batchID)+ "*1000000000 ) + rownum)"
+        try: self.scratchDb.execute(sql)
+        except:
+            self.logger.errorPair('failed sql',sql)
+            return
+
+
 
     '''
@@ -399 +387 @@
         # 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 DIffObject table")
-        rowCountBefore = self.scratchDb.getRowCount("DiffObject")
+        rowCountBefore = self.scratchDb.getRowCount("DiffDetObject")
 
         # XXX EAM : note that in mysql versions later than 5.1, this fails
@@ -410 +398 @@
             self.scratchDb.execute("set session old_alter_table=1")
             
-        self.scratchDb.execute("ALTER IGNORE TABLE DiffObject ADD UNIQUE INDEX(diffObjID)")
+        self.scratchDb.execute("ALTER IGNORE TABLE DiffDetObject ADD UNIQUE INDEX(diffObjID)")
         if self.scratchDb.version > 5.1:
             self.scratchDb.execute("set session old_alter_table=0")
 
-        rowCountAfter = self.scratchDb.getRowCount("DiffObject")
+        rowCountAfter = self.scratchDb.getRowCount("DiffDetObject")
         self.logger.infoPair("Number of duplicated diffobjIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
 
+        self.scratchDb.execute("ALTER TABLE DiffDetObject CHANGE dec_ `dec` double")
+
+        self.updatePspsUniqueIDs("DiffDetObject")
+
         self.dvoObjects.purgeRegion(self.region)
 
         #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")
+ 
+#        rowCountAfter = self.scratchDb.getRowCount("Object")
         return True
 #        return False