IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 37755


Ignore:
Timestamp:
Dec 17, 2014, 7:22:48 PM (12 years ago)
Author:
heather
Message:

diffobjectbatch - first attempt, not all columns full but should be most of the framework

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/ippToPsps/jython/diffobjectbatch.py

    r37577 r37755  
    4949               dvoID,
    5050               batchID,
    51                "OB",
     51               "DO",
    5252               None)
    5353
    … …  
    134134            #  " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
    135135
    136             sql = "UPDATE MeanObject JOIN \
    137                    " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
     136            sql = "UPDATE DiffObject JOIN \
     137                   " + cpsTable + " AS cps ON (cps.row = (DiffObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \
    138138                   SET \
    139139                    MeanObject." + filter[1] + "QfPerfect      = PSF_QF_PERF_MAX \
    … …  
    174174    Inserts stuff for all mags
    175175    '''
    176     def updateObjectThinFromCps(self, cpsTable):
     176    def updateDiffObjectFromCps(self, cpsTable):
    177177
    178178        # list of all filters PSPS is interested in
    … …  
    196196            # sqlLine = sqlUtility()
    197197
    198             sql  = "UPDATE ObjectThin JOIN "
     198            sql  = "UPDATE DiffObject JOIN "
    199199            sql += cpsTable + " AS cps "
    200             sql += "ON (cps.row = (ObjectThin.row * "
     200            sql += "ON (cps.row = (DiffObject.row * "
    201201            sql += str(filterCount) + ")-("
    202202            sql += str(filterCount) + " - "
    … …  
    204204            sql += ")) "
    205205
    206             sql += "SET ObjectThin.n" + filter[1] + " = NCODE, "
    207             sql += "ObjectThin.nStackDetections = ObjectThin.nStackDetections + cps.NSTACK_DET"
     206            sql += "SET DiffObject.n" + filter[1] + " = NCODE, "
     207            sql += "DiffObject.nDetections = DiffObject.nDetections + cps.NSTACK_DET"
    208208            self.logger.info(sql)
    209209            self.scratchDb.execute(sql)
    … …  
    213213        for filter in filters:
    214214            # now do a sum of n[filters], but do not include the ones with -999
    215             sql  = "UPDATE ObjectThin "
     215            sql  = "UPDATE DiffObject "
    216216            sql += "SET nDetections = nDetections + n" + filter[1]
    217217            sql += " WHERE n" + filter[1] + " != -999"
    … …  
    222222    XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps?
    223223    '''
    224     def updateObjName(self):
    225     ##this is a 4 part update:
    226         self.logger.infoPair("updating objName", "using stacks")
    227     ## if we have a stackra and dec, use stackra and dec , dec >=0
    228 
    229         sql = "update ObjectThin set objName =  concat('PS1.2 J', \
    230         lpad(floor(raStack/15.),2,'0'), \
    231         lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \
    232         lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \
    233         '+', \
    234         lpad(floor(decStack),2,'0'), \
    235         lpad((decStack-(floor(decStack)))*60. ,2,'0'), \
    236         lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \
    237         ) where raStack > -999 and decStack > -999 and decStack >= 0"
    238         try:
    239             self.scratchDb.execute(sql)
    240         except:
    241             self.logger.errorPair("failed sql", sql)
    242             return False
    243 
    244 
    245     ## if we have a stackra and dec, use stackra and dec, dec < 0
    246 
    247         sql = "update ObjectThin set objName =  concat('PS1.2 J', \
    248         lpad(floor(raStack/15.),2,'0'), \
    249         lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \
    250         lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \
    251         '-', \
    252         lpad(floor(decStack),2,'0'), \
    253         lpad((decStack-(floor(decStack)))*60. ,2,'0'), \
    254         lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \
    255         ) where raStack > -999 and decStack > -999 and decStack <= 0"
    256         try:
    257             self.scratchDb.execute(sql)
    258         except:
    259             self.logger.errorPair("failed sql", sql)
    260             return False
    261         self.logger.infoPair("updating objName", "using means")
     224    def updateDiffObjName(self):
     225    ##this is a 2 part update:
     226
     227        self.logger.infoPair("updating diffObjName", "using means")
    262228    ## use mean ra and dec (dec >= 0)
    263         sql = "update ObjectThin set objName =  concat('PS1.2 J', \
     229        sql = "update DiffObject set diffObjName =  concat('PS1.2 J', \
    264230        lpad(floor(raMean/15.),2,'0'), \
    265231        lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \
    … …  
    278244    ## use mean ra and dec (dec < 0)
    279245
    280         sql = "update ObjectThin set objName =  concat('PS1.2 J', \
     246        sql = "update DiffObject set diffObjName =  concat('PS1.2 J', \
    281247        lpad(floor(raMean/15.),2,'0'), \
    282248        lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \
    … …  
    295261
    296262    '''
    297     Populates the Object table
    298     '''
    299     def populateObjectThinTable(self):
     263    Populates the DiffObject table
     264    '''
     265    def populateDiffObjectTable(self):
    300266
    301267        cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
    … …  
    324290
    325291       
    326         self.logger.info("Populating ThinObject")
     292        self.logger.info("Populating DiffObject")
    327293        self.logger.info("Inserting objects from cpt file")
    328294
    … …  
    331297        # INSERT IGNORE INTO ObjectThin
    332298
    333         sqlLine = sqlUtility("INSERT INTO ObjectThin (")
    334 
    335         sqlLine.group("objID",           "EXT_ID")
     299        sqlLine = sqlUtility("INSERT INTO DiffObject (")
     300
     301        sqlLine.group("diffObjID",           "EXT_ID")
    336302        sqlLine.group("ippObjID",        "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly
    337303        sqlLine.group("surveyID",        "'" + str(self.surveyID) + "'")
    … …  
    339305        sqlLine.group("batchID",         "'" + str(self.batchID) + "'")
    340306        sqlLine.group("dvoRegionID",     "CAT_ID")
    341         sqlLine.group("tessID",          "TESS_ID")
    342         sqlLine.group("projectionID",    "PROJECTION_ID")
    343307        sqlLine.group("skycellID",       "SKYCELL_ID")
    344308        sqlLine.group("dataRelease",     "'" + str(self.skychunk.dataRelease) + "'")
    … …  
    346310        sqlLine.group("qualityFlag",     "FLAGS >> 24 & 0xFF")
    347311        sqlLine.group("consistencyFlag", "'0'")
    348         sqlLine.group("raStack",         "RA_STK")
    349         sqlLine.group("decStack",        "DEC_STK")
    350         sqlLine.group("raStackErr",      "RA_STK_ERR")
    351         sqlLine.group("decStackErr",     "DEC_STK_ERR")
    352         sqlLine.group("raMean",          "RA_MEAN")
    353         sqlLine.group("decMean",         "DEC_MEAN")
    354         sqlLine.group("raMeanErr",       "RA_ERR")
    355         sqlLine.group("decMeanErr",      "DEC_ERR")
    356         sqlLine.group("posMeanChisq",    "CHISQ_POS")
    357         sqlLine.group("nStackObjectRows", "'0'") # XXX I need to add / define this in dvopsps
    358         sqlLine.group("nStackDetections", "'0'")
     312        sqlLine.group("ra",          "RA_MEAN")
     313        sqlLine.group("dec",         "DEC_MEAN")
     314        sqlLine.group("raErr",       "RA_ERR")
     315        sqlLine.group("decErr",      "DEC_ERR")
    359316        sqlLine.group("nDetections",      "'0'")
    360317        sql = sqlLine.makeRaw(") SELECT ", " FROM " + cptTableName)
    … …  
    363320            self.scratchDb.execute(sql)
    364321        except:
    365             self.logger.errorPair("Couldn't populate Object table", sql)
     322            self.logger.errorPair("Couldn't populate DiffObject table", sql)
    366323            return False
    367324 
    368325        # add row count columns so we can perform joins to get colors
    369         self.logger.infoPair("Adding 'row' columns to", "Object and cps tables")
    370         self.scratchDb.addRowCountColumn("ObjectThin", "row")
     326        self.logger.infoPair("Adding 'row' columns to", "DiffObject and cps tables")
     327        self.scratchDb.addRowCountColumn("DiffObject", "row")
    371328        self.scratchDb.addRowCountColumn(cpsTableName, "row")
    372329
    373330##        self.insertMeanMags(cpsTableName)
    374331## this is the new version
    375         self.logger.infoPair("update objName for ","objectThin")
    376         self.updateObjName()
    377 
    378         self.logger.infoPair("update ObjectThin from ","cps table")
    379 
    380         self.updateObjectThinFromCps(cpsTableName)
     332        self.logger.infoPair("update diffObjName for ","DiffObject")
     333        self.updateDiffObjName()
     334
     335        self.logger.infoPair("update DiffObject from ","cps table")
     336
     337        self.updateDiffObjectFromCps(cpsTableName)
    381338
    382339        # XXX EAM 20140724 : is this necessary??
    … …  
    396353        #count out of range
    397354
    398         sql = "SELECT count(*) FROM ObjectThin where \
    399               ObjectThin.decMean > " + str(decMax) + " \
    400               or ObjectThin.decMean < " + str(decMin) + " \
    401               or ObjectThin.raMean > " + str(raMax) + " \
    402               or ObjectThin.raMean < " + str(raMin)       
     355        sql = "SELECT count(*) FROM DiffObject where \
     356              DiffObject.dec > (" + str(decMax) + " + .0033) \
     357              or DiffObject.dec < (" + str(decMin) + " - .0033) \
     358              or DiffObject.ra > (" + str(raMax) + " + .0033) \
     359              or DiffObject.ra < (" + str(raMin) + " - .0033)"       
    403360   
    404361        rs = self.scratchDb.executeQuery(sql)
    … …  
    409366       
    410367 
    411         sql = "DELETE FROM ObjectThin where \
    412               ObjectThin.decMean > (" + str(decMax) + " + .0033) or \
    413               ObjectThin.decMean < (" + str(decMin) + " - .0033) or \
    414               ObjectThin.raMean > (" + str(raMax) + " + .0033) or \
    415               ObjectThin.raMean < (" + str(raMin) + " - .0033)"
     368        sql = "DELETE FROM DiffObject where \
     369              DiffObject.decMean > (" + str(decMax) + " + .0033) or \
     370              DiffObject.decMean < (" + str(decMin) + " - .0033) or \
     371              DiffObject.raMean > (" + str(raMax) + " + .0033) or \
     372              DiffObject.raMean < (" + str(raMin) + " - .0033)"
    416373        self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range")
    417374
    … …  
    419376            self.scratchDb.execute(sql)
    420377        except:
    421             self.logger.errorPair("Couldn't cull outsiders from ObjectThin table", sql)
     378            self.logger.errorPair("Couldn't cull outsiders from DiffObject table", sql)
    422379            return False
    423380
    424         self.logger.infoPair("Dropping row column from", "ObjectThin table")
    425         self.scratchDb.dropColumn("ObjectThin", "row")
     381        self.logger.infoPair("Dropping row column from", "DiffObject table")
     382        self.scratchDb.dropColumn("DiffObject", "row")
    426383        ##self.logger.infoPair("Purging from scratch Db", self.region + " region")
    427384
    … …  
    429386        ##self.dvoObjects.purgeRegion(self.region)
    430387
    431         self.setMinMaxObjID(["ObjectThin"])
     388        self.setMinMaxDiffObjID(["DiffObject"])
    432389
    433390        return True
    434391
    435392    '''
    436     Populates the ObjectCalColor table
    437     '''
    438     def populateMeanObjectTable(self):
    439         cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt")
    440         cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps")
    441         self.logger.infoPair("Populating MeanObject from ", "ObjectThin")
    442 
    443         sql = "INSERT INTO MeanObject ( objID ) SELECT objID FROM ObjectThin"
    444         try:
    445             self.scratchDb.execute(sql)
    446         except:
    447             self.logger.errorPair("Couldn't populate MeanObject table", sql)
    448             return False
    449 
    450         self.logger.infoPair("Adding 'row' columns to", "MeanObject table")
    451         self.scratchDb.addRowCountColumn("MeanObject", "row")
    452  
    453 
    454         ##self.scratchDb.addRowCountColumn(cpsTableName, "row")
    455         self.logger.infoPair("update MeanObjects from ","cps table")
    456         self.updateMeanObjectFromCps(cpsTableName)
    457 
    458         self.logger.infoPair("Dropping row column from", "MeanObject table")
    459         self.scratchDb.dropColumn("MeanObject", "row")
    460 
    461         return True
    462 
    463     '''
    464393    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    465394    '''
    466395    def populatePspsTables(self):
    467396
    468         if not self.populateObjectThinTable(): return False
    469         #if not self.populateObjectCalColorTable(): return False
    470         if not self.populateMeanObjectTable(): return False
     397        if not self.populateDiffObjectTable(): return False
    471398
    472399        # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number
    473         self.logger.infoPair("Forcing uniqueness on", "objID in ObjectThin table")
    474         rowCountBefore = self.scratchDb.getRowCount("ObjectThin")
     400        self.logger.infoPair("Forcing uniqueness on", "objID in DIffObject table")
     401        rowCountBefore = self.scratchDb.getRowCount("DiffObject")
    475402
    476403        # XXX EAM : note that in mysql versions later than 5.1, this fails
    … …  
    483410            self.scratchDb.execute("set session old_alter_table=1")
    484411           
    485         self.scratchDb.execute("ALTER IGNORE TABLE ObjectThin ADD UNIQUE INDEX(objID)")
     412        self.scratchDb.execute("ALTER IGNORE TABLE DiffObject ADD UNIQUE INDEX(diffObjID)")
    486413        if self.scratchDb.version > 5.1:
    487414            self.scratchDb.execute("set session old_alter_table=0")
    488415
    489         rowCountAfter = self.scratchDb.getRowCount("ObjectThin")
    490         self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
    491 
    492         self.logger.infoPair("Forcing uniqueness on", "objID in MeanObject table")
    493         rowCountBefore = self.scratchDb.getRowCount("MeanObject")
    494 
    495         # XXX EAM : note that in mysql versions later than 5.1, this fails
    496         # unless the following is called first:
    497         # set session old_alter_table=1
    498         # follow the command with
    499         # set session old_alter_table=0
    500         # OF COURSE, this fails for mysql version < 5.5...
    501         if self.scratchDb.version > 5.1:
    502             self.scratchDb.execute("set session old_alter_table=1")
    503            
    504         self.scratchDb.execute("ALTER IGNORE TABLE MeanObject ADD UNIQUE INDEX(objID)")
    505         if self.scratchDb.version > 5.1:
    506             self.scratchDb.execute("set session old_alter_table=0")
    507 
    508         rowCountAfter = self.scratchDb.getRowCount("MeanObject")
    509         self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
     416        rowCountAfter = self.scratchDb.getRowCount("DiffObject")
     417        self.logger.infoPair("Number of duplicated diffobjIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore))
    510418
    511419        self.dvoObjects.purgeRegion(self.region)
    … …  
    513421        #this is abuse of something but this is how I get the object batches to crash to further investigate them
    514422       
    515 #        rowCountAfter = self.scratchDb.getRowCount("Object")
     423        rowCountAfter = self.scratchDb.getRowCount("Object")
    516424        return True
    517425#        return False
Note: See TracChangeset for help on using the changeset viewer.