Index: trunk/ippToPsps/jython/objectbatch.py =================================================================== --- trunk/ippToPsps/jython/objectbatch.py (revision 36736) +++ trunk/ippToPsps/jython/objectbatch.py (revision 36744) @@ -108,5 +108,5 @@ Inserts stuff for all mags ''' - def insertMeanMags(self, cpsTable): + def updateMeanObjectFromCps(self, cpsTable): # list of all filters PSPS is interested in @@ -130,12 +130,23 @@ # 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,15 +154,4 @@ 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,4 +164,125 @@ 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,11 +327,21 @@ ,randomID \ ,batchID \ + ,dvoRegionID \ ,dataRelease \ ,objInfoFlag \ ,qualityFlag \ + ,consistencyFlag \ + ,raStack \ + ,decStack \ + ,raStackErr \ + ,decStackErr \ ,raMean \ ,decMean \ ,raMeanErr \ ,decMeanErr \ + ,raMeanStd \ + ,decMeanStd \ + ,nStackObjectRows \ + ,nStackDetections \ ,nDetections \ ) \ @@ -220,14 +351,24 @@ ,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,8 +385,13 @@ 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,7 +438,8 @@ 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,6 +451,7 @@ ''' 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,5 +469,15 @@ 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,8 +530,9 @@ 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