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

Timestamp:

May 12, 2014, 5:37:01 PM (12 years ago)

Author:

heather

Message:

Ipptopsps: new Schema, old dvo - first attempt to run sas

File:

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

trunk/ippToPsps/jython/objectbatch.py

@@ -108 +108 @@
     Inserts stuff for all mags
     '''
-    def insertMeanMags(self, cpsTable):
+    def updateMeanObjectFromCps(self, cpsTable):
 
         # list of all filters PSPS is interested in
@@ -130 +130 @@
             # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags
 
-            sql = "UPDATE MeanObject JOIN ObjectThin using (objID) JOIN \
-                   " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
+            sql = "UPDATE MeanObject JOIN \
+                   " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
                    SET \
-                   ObjectThin.n" + filter[1] + " = NCODE \
+                   MeanObject." + filter[1] + "QfPerfect = -999 \
                    ,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] + "nIncPSFMag = -999 \
+                   ,MeanObject." + filter[1] + "MeanPSFMagStd = -999 \
+                   ,MeanObject." + filter[1] + "MinPSFMag = -999 \
+                   ,MeanObject." + filter[1] + "MaxPSFMag = -999 \
+                   ,MeanObject." + filter[1] + "nIncKronMag = -999 \
+                   ,MeanObject." + filter[1] + "MeanKronMag = -999 \
+                   ,MeanObject." + filter[1] + "MeanKronMagStd = -999 \
+                   ,MeanObject." + filter[1] + "MeanApMag = MAG_AP \
+                   ,MeanObject." + filter[1] + "MeanApMagErr = MAG_ERR \
+                   ,MeanObject." + filter[1] + "MeanApMagStd = MAG_STDEV \
+                   ,MeanObject." + filter[1] + "nIncApMag = -999 \
                    ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) "
 
@@ -143 +154 @@
             self.logger.info(sql)
             self.scratchDb.execute(sql)
-
-        self.logger.infoPair("Calculating nDetections from", "n[filters]")
-        for filter in filters:
-            # now do a sum of n[filters], but do not include the ones with -999
-            sql = "UPDATE ObjectThin join MeanObject using (objID)  \
-                   SET nDetections = nDetections + n" + filter[1] + " \
-                   WHERE n" + filter[1] + " != -999"
-            self.scratchDb.execute(sql)
-            
-
-
 
         # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12)
@@ -164 +164 @@
                    WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut)
             self.scratchDb.execute(sql)
+    '''
+    Inserts stuff for all mags
+    '''
+    def updateObjectThinFromCps(self, cpsTable):
+
+        # list of all filters PSPS is interested in
+        interestedFilters = ['g', 'r', 'i', 'z', 'y']
+        
+        filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters)
+    
+        # get a count of the available filters
+        filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable()
+        # filterCount = len(filters)
+
+        self.logger.infoPair("Available filters in Photcodes", filters)
+
+        # the 'code' now defines the order in the cps file that the mags are listed for a given filter
+        self.logger.infoPair("Adding magnitudes from", "cps table")
+        for filter in filters:
+
+            filterID = self.scratchDb.getFilterID(filter[1])
+
+            # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000)
+            # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags
+
+            sql = "UPDATE ObjectThin JOIN \
+                   " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
+                   SET \
+                   ObjectThin.n" + filter[1] + " = NCODE "
+            self.logger.info(sql)
+            self.scratchDb.execute(sql)
+
+        self.logger.infoPair("Calculating nDetections from", "n[filters]")
+        for filter in filters:
+            # now do a sum of n[filters], but do not include the ones with -999
+            sql = "UPDATE ObjectThin   \
+                   SET nDetections = nDetections + n" + filter[1] + " \
+                   WHERE n" + filter[1] + " != -999"
+            self.scratchDb.execute(sql)
+            
+
+
+    '''
+    give objectName to objectThin
+    '''
+    def updateObjName(self):
+    ##this is a 4 part update:
+        self.logger.infoPair("updating objName", "using stacks")
+    ## if we have a stackra and dec, use stackra and dec , dec >=0
+
+        sql = "update ObjectThin set objName =  concat('PS1.2 J', \
+        lpad(floor(raStack/15.),2,'0'), \
+        lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \
+        lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \
+        '+', \
+        lpad(floor(decStack),2,'0'), \
+        lpad((decStack-(floor(decStack)))*60. ,2,'0'), \
+        lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \
+        ) where raStack > -999 and decStack > -999 and decStack >= 0"
+        try:
+            self.scratchDb.execute(sql)
+        except:
+            self.logger.errorPair("failed sql", sql)
+            return False
+
+
+    ## if we have a stackra and dec, use stackra and dec, dec < 0
+
+        sql = "update ObjectThin set objName =  concat('PS1.2 J', \
+        lpad(floor(raStack/15.),2,'0'), \
+        lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \
+        lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \
+        '-', \
+        lpad(floor(decStack),2,'0'), \
+        lpad((decStack-(floor(decStack)))*60. ,2,'0'), \
+        lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \
+        ) where raStack > -999 and decStack > -999 and decStack <= 0"
+        try:
+            self.scratchDb.execute(sql)
+        except:
+            self.logger.errorPair("failed sql", sql)
+            return False
+        self.logger.infoPair("updating objName", "using means")
+    ## use mean ra and dec (dec >= 0)
+        sql = "update ObjectThin set objName =  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'), \
+        '+', \
+        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"
+        try:
+            self.scratchDb.execute(sql)
+        except:
+            self.logger.errorPair("failed sql", sql)
+            return False
+ 
+    ## use mean ra and dec (dec < 0)
+
+        sql = "update ObjectThin set objName =  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'), \
+        '-', \
+        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"
+
+        try:
+            self.scratchDb.execute(sql)
+        except:
+            self.logger.errorPair("failed sql", sql)
+            return False
+
+
+
+
+
 
 
@@ -206 +327 @@
                ,randomID \
                ,batchID \
+               ,dvoRegionID \
                ,dataRelease \
                ,objInfoFlag \
                ,qualityFlag \
+               ,consistencyFlag \
+               ,raStack \
+               ,decStack \
+               ,raStackErr \
+               ,decStackErr \
                ,raMean \
                ,decMean \
                ,raMeanErr \
                ,decMeanErr \
+               ,raMeanStd \
+               ,decMeanStd \
+               ,nStackObjectRows \
+               ,nStackDetections \
                ,nDetections \
                 ) \
@@ -220 +351 @@
                ,CAT_ID*1000000000 + OBJ_ID \
                ," + str(self.surveyID) + " \
-               ,-999 \
-               , RAND() \
+               , -999 \
+               , FLOOR(RAND()*9223372036854775807) \
                , " + str(self.batchID) + "\
+               , -999 \
                , " + str(self.skychunk.dataRelease) + "\
                ,FLAGS \
                ,FLAGS >> 24 & 0xFF \
+               , 0 \
+               , -999 \
+               , -999 \
+               , -999 \
+               , -999 \
                ,RA \
                ,DEC_ \
                ,RA_ERR \
                ,DEC_ERR \
+               , -999 \
+               , -999 \
+               ,0 \
+               ,0 \
                ,0 \
                 FROM " + cptTableName
@@ -244 +385 @@
         self.scratchDb.addRowCountColumn(cpsTableName, "row")
 
- #       self.insertMeanMags(cpsTableName)
-
-#don't do this as we removed those columns.. 
-#        self.updateColors()
+##        self.insertMeanMags(cpsTableName)
+## this is the new version
+        self.logger.infoPair("update objName for ","objectThin")
+        self.updateObjName()
+
+
+        self.logger.infoPair("update ObjectThin from ","cps table")
+
+        self.updateObjectThinFromCps(cpsTableName)
 
         #objects can have out of range ra dec in dvo - need to find and kill them at the end
@@ -292 +438 @@
         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.logger.infoPair("Purging from scratch Db", self.region + " region")
+
+        ##Don't do this till after MeanObject
+        ##self.dvoObjects.purgeRegion(self.region)
 
         self.setMinMaxObjID(["ObjectThin"])
@@ -304 +451 @@
     '''
     def populateMeanObjectTable(self):
-
-        self.logger.infoPair("Populating", "MeanObject")
+        cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
+        cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps")
+        self.logger.infoPair("Populating MeanObject from ", "ObjectThin")
 
         sql = "INSERT INTO MeanObject ( \
@@ -321 +469 @@
             return False
 
-        self.insertMeanMags("MeanObject")
+        self.logger.infoPair("Adding 'row' columns to", "MeanObject table")
+        self.scratchDb.addRowCountColumn("MeanObject", "row")
+ 
+
+        ##self.scratchDb.addRowCountColumn(cpsTableName, "row")
+        self.logger.infoPair("update MeanObjects from ","cps table")
+        self.updateMeanObjectFromCps(cpsTableName)
+
+        self.logger.infoPair("Dropping row column from", "MeanObject table")
+        self.scratchDb.dropColumn("MeanObject", "row")
+
         return True
 
@@ -372 +530 @@
         self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
 
+        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")
-        #return True
-        return False
+#        rowCountAfter = self.scratchDb.getRowCount("Object")
+        return True
+#        return False