IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Changeset 37168


Ignore:
Timestamp:
Aug 1, 2014, 5:12:31 PM (12 years ago)
Author:
eugene
Message:

stackbatch.py now basically workds for all tables except StackModelFitThin

Location:
branches/eam_branches/ipp-20140717/ippToPsps/jython
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • branches/eam_branches/ipp-20140717/ippToPsps/jython/loader.py

    r37156 r37168  
    178178            if abort or not self.checkClientStatus(): abort = True
    179179            elif numAttempts > 1 and not self.waitForPollTime():  break
    180 
    181             print "done with wait?"
    182180
    183181            if self.onePassOnly: self.exitProgram("one pass completed")

  • branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py

    r37160 r37168  
    44import glob
    55import sys
    6 
     6import math
    77
    88import stilts
    … …  
    5555
    5656       self.stackType = "DEEP_STACK" # TODO
     57       self.stackTypeID = self.getStackTypeID()
    5758       
    5859       self.filters = ['g', 'r', 'i', 'z', 'y']
    … …  
    158159       self.dropTableVerbose("StackObjectThin")
    159160       self.dropTableVerbose("StackObjectExtra")
    160 
     161       
    161162       self.dropTableVerbose("StackApFlx")
    162 
     163       
    163164       self.dropTableVerbose("StackModelFitThin")
    164165       self.dropTableVerbose("StackModelFitDeV")
    … …  
    204205       self.logger.infoPair("Projectioncell", "%s" % self.projectioncell)
    205206
     207    ### *********************** Utility Functions ************************************
     208
    206209    '''
    207210    Drop a table and report the drop
    … …  
    212215
    213216    '''
    214     Updates a table with stackMetaID
    215     '''
    216     def updateStackMetaID(self, table):
    217 
    218         sql = "UPDATE " + table + "  SET "
    219 
    220         for i in range(len(self.filters)):
    221             filter = self.filters[i]
    222             field = filter + "stackMetaID"
    223             sql += field + " = '" + self.stackIDs[filter] + "'"
    224             if (i < len(self.filters) - 1):
    225                 sql += ", "
    226      
    227         self.scratchDb.execute(sql)
    228 
    229     '''
    230     Updates a table with stackTypeID grabbed from FitModel init table
    231     '''
    232     def updateStackTypeID(self, table):
    233 
    234         sql = "UPDATE "+table+" AS a, StackType AS b SET a.stackTypeID=b.stackTypeID WHERE b.name = '" + self.stackType + "'"
    235         self.scratchDb.execute(sql)
    236 
     217    get stackTypeID corresponding to our stackType
     218    '''
     219    def getStackTypeID(self):
     220
     221        sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'"
     222        rs = self.scratchDb.executeQuery(sql)
     223        if not rs.next():
     224            self.logger.errorPair("cannot get stack type ID from database for", self.stackType)
     225            raise
     226       
     227        ID = rs.getInt(1)
     228        return ID
    237229
    238230    def testprint(self):
    … …  
    241233
    242234    '''
    243     Updates aperture fluxes for StackApFkx table
    244     '''
    245     def updateModelFitFlxs(self, prefix, psfCondition, tablename, filter):
    246         #properly -999 these to start with:
    247         f = filter
    248         if tablename == 'StackModelFit':
    249             sql = "UPDATE " + tablename + " AS a, "+f+"SkyChip_xrad AS b SET \
    250         "+f+prefix+"flxR1=-999, \
    251         "+f+prefix+"flxR1Err=-999, \
    252         "+f+prefix+"flxR1Std=-999, \
    253         "+f+prefix+"flxR1Fill=-999, \
    254         "+f+prefix+"flxR2=-999, \
    255         "+f+prefix+"flxR2Err=-999, \
    256         "+f+prefix+"flxR2Std=-999, \
    257         "+f+prefix+"flxR2Fill=-999, \
    258         "+f+prefix+"flxR3=-999, \
    259         "+f+prefix+"flxR3Err=-999, \
    260         "+f+prefix+"flxR3Std=-999, \
    261         "+f+prefix+"flxR3Fill=-999, \
    262         "+f+prefix+"flxR4=-999, \
    263         "+f+prefix+"flxR4Err=-999, \
    264         "+f+prefix+"flxR4Std=-999, \
    265         "+f+prefix+"flxR4Fill=-999, \
    266         "+f+prefix+"flxR5=-999, \
    267         "+f+prefix+"flxR5Err=-999, \
    268         "+f+prefix+"flxR5Std=-999, \
    269         "+f+prefix+"flxR5Fill=-999, \
    270         "+f+prefix+"flxR6=-999, \
    271         "+f+prefix+"flxR6Err=-999, \
    272         "+f+prefix+"flxR6Std=-999, \
    273         "+f+prefix+"flxR6Fill=-999, \
    274         "+f+prefix+"flxR7=-999, \
    275         "+f+prefix+"flxR7Err=-999, \
    276         "+f+prefix+"flxR7Std=-999, \
    277         "+f+prefix+"flxR7Fill=-999, \
    278         "+f+prefix+"flxR8=-999, \
    279         "+f+prefix+"flxR8Err=-999, \
    280         "+f+prefix+"flxR8Std=-999, \
    281         "+f+prefix+"flxR8Fill=-999, \
    282         "+f+prefix+"flxR9=-999, \
    283         "+f+prefix+"flxR9Err=-999, \
    284         "+f+prefix+"flxR9Std=-999, \
    285         "+f+prefix+"flxR9Fill=-999, \
    286         "+f+prefix+"flxR10=-999, \
    287         "+f+prefix+"flxR10Err=-999, \
    288         "+f+prefix+"flxR10Std=-999, \
    289         "+f+prefix+"flxR10Fill=-999 \
    290         WHERE a."+f+"ippDetectID=b.IPP_IDET "
    291         #self.logger.infoPair("sql", sql)
    292             self.scratchDb.execute(sql)
    293 
    294 
    295         #we have variable numbers of these columns. find out which are in use
    296         sql = "DESCRIBE "+f+"SkyChip_xrad 'aper_fill_%'"
    297         rs = self.scratchDb.executeQuery(sql)
    298         rs.first()
    299         columns = []
    300         column = rs.getString(1)
    301         columns.append(column)
    302         while (rs.next()):
    303             column = rs.getString(1)
    304             columns.append(column)
    305         rs.close()
    306 
    307         #use regex to leave just the numbers
    308 
    309         numbers = []
    310         min = 1
    311         max = 10
    312         if tablename == 'StackApFlx':
    313             min = 3
    314             max = 5
    315         for column in columns:
    316             number = re.sub("APER_FILL_","",column)
    317             if (int(number) >= min and int(number) <=max):
    318                 numbers.append(number)
    319                 self.logger.infoPair("Aperture Numbers",number)
    320 
    321         #generate the sql to do the necessary ops on the columns   
    322         sql = "UPDATE " + tablename + " AS a, "+f+"SkyChip_xrad AS b SET "
    323         cnt =0
    324 
    325         # XXX : I need to worry about which header this is
    326         exptime = self.safeDictionaryAccess(self.header, 'EXPTIME')
    327         for number in numbers:
    328             if (cnt>0):
    329                 sql=sql+" , "
    330             sql=sql+f+prefix+"flxR"+number+"=b.APER_FLUX_"+number +" / " + str(exptime) + ", "
    331             sql=sql+f+prefix+"flxR"+number+"Err=b.APER_FLUX_ERR_"+number +" / " + str(exptime) + ", "
    332             sql=sql+f+prefix+"flxR"+number+"Std=b.APER_FLUX_STDEV_"+number +" / " + str(exptime) + ", "
    333             sql=sql+f+prefix+"flxR"+number+"Fill=b.APER_FILL_"+number + " "
    334             cnt=cnt+1
    335            
    336         sql=sql+"WHERE a."+f+"ippDetectID=b.IPP_IDET AND  "+psfCondition   
    337         self.logger.infoPair("sql",sql)
    338         self.scratchDb.execute(sql)
    339 
    340 
    341     '''
    342     Populates the StackMeta table, mainly from dictionary values found in IPP FITS header
     235    Populates the StackMeta table, mainly from header values found in stack cmf FITS header
    343236    '''
    344237    def populateStackMeta(self,filter):
    … …  
    374267        sqlLine = sqlUtility("INSERT INTO " + tablename + " (")
    375268
     269        sqlLine.group("stackMetaID",   str(stackID))       
    376270        sqlLine.group("batchID",       str(self.batchID))
    377271        sqlLine.group("surveyID",      str(self.surveyID))
    378         sqlLine.group("dataRelease",   str(self.skychunk.dataRelease))
    379         sqlLine.group("stackMetaID",   str(stackID))       
    380272        sqlLine.group("filterID",      str(filterID))           
     273        sqlLine.group("stackTypeID",   str(self.stackTypeID))
    381274        sqlLine.group("skyCellID",     self.skycell)           
    382275        sqlLine.group("photoCalID",    photoCalID)
    383         sqlLine.group("photoZero",     self.getKeyFloat(header, "%.5f", 'FPA.ZP'))
    384276        sqlLine.group("expTime",       self.getKeyFloat(header, "%.5f", 'EXPTIME')) 
    385277        sqlLine.group("psfModelID",    psfmodel)           
    386278        sqlLine.group("psfFwhm_mean",  fwhm_maj)     
    387279        sqlLine.group("psfFwhm_max",   fwhm_maj_uq)   
     280        sqlLine.group("photoZero",     self.getKeyFloat(header, "%.5f", 'FPA.ZP'))
    388281        sqlLine.group("ctype1",        header['CTYPE1']) 
    389282        sqlLine.group("ctype2",        header['CTYPE2']) 
    … …  
    398291        sqlLine.group("pc002001",      self.getKeyFloat(header, "%.8e", 'PC002001'))
    399292        sqlLine.group("pc002002",      self.getKeyFloat(header, "%.8e", 'PC002002'))
     293        sqlLine.group("dataRelease",   str(self.skychunk.dataRelease))
    400294
    401295        sql = sqlLine.make(") VALUES ( ", ")")
    … …  
    404298        self.scratchDb.execute(sql)
    405299
    406         # set stackTypeID in table based on lookup table & self.stackType
    407         # XXX include in the INSERT above?
    408         self.updateStackTypeID(tablename)
    409 
    410300        self.tablesToExport.append(tablename)
    411301
    … …  
    419309
    420310        # XXX EAM 20140731 : if we are going to add indexes (objID?) to StackObjectThin, do it HERE
     311        self.scratchDb.createIndex("StackObjectThin", "objID")
    421312
    422313        self.logger.infoPair("inserting filter dependent items into", "StackObjectThin")
    … …  
    491382                self.logger.errorPair('failed sql: ', sql)
    492383                raise
     384
     385        self.tablesToExport.append("StackObjectThin")
    493386
    494387    '''
    … …  
    576469                return
    577470
     471        self.tablesToExport.append("StackObjectExtra")
     472
    578473    '''
    579474    Populates the StackModelFitThin table
    … …  
    587482        # self.scratchDb.createIndex("StackObjectExtra", "objID")
    588483
     484        # currently, none of the fields defined by the table are generated
    589485       
    590486    '''
    … …  
    603499        for filter in self.filters:
    604500            self.populateStackModelFitFilter(model, ippModelType, filter)
     501
     502        self.tablesToExport.append(table)
    605503
    606504    '''
    … …  
    616514
    617515        header  = self.headerSet[filter]
     516        print "exptime in header: " + header['EXPTIME']
     517
     518        magtime = "%.5f" % (2.5*math.log10(float(header['EXPTIME'])))
    618519        exptime = self.getKeyFloat(header, "%.5f", "EXPTIME")
    619520
     521        print "magtime: " + magtime + ", exptime: " + exptime
     522
    620523        sqlLine = sqlUtility("UPDATE " + table + " AS a, " + filter + "SkyChip_xfit AS b SET")
    621524
    622         # these lines yield the flux, flux error values, but I do not think we want those in the database...
    623         # sqlLine.group("a." + filter + model + "Flux",    "POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime))
    624         # sqlLine.group("a." + filter + model + "FluxErr", "ABS(b.EXT_INST_MAG_SIG) * POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime) + " / 1.085736")
    625 
    626         sqlLine.group("a." + filter + model + "Mag",       "b.EXT_INST_MAG") # need to modify by zero point and 2.5*log10(exptime)
    627         sqlLine.group("a." + filter + model + "MagErr",    "b.EXT_INST_MAG_SIG") # need to modify by zero point and 2.5*log10(exptime)
     525        sqlLine.group("a." + filter + model + "Mag",       "b.EXT_INST_MAG + " + magtime) # need to modify by zero point and 2.5*log10(exptime)
     526        sqlLine.group("a." + filter + model + "MagErr",    "b.EXT_INST_MAG_SIG")
    628527                     
    629528        sqlLine.group("a." + filter + model + "Radius",    "b.EXT_WIDTH_MAJ")
    … …  
    655554        self.scratchDb.execute(sql)
    656555
    657 
    658         # XXX old code for flux tables
    659         # self.logger.infoPair("Adding fluxes", "un-convolved")
    660         # self.updateModelFitFlxs("", "((b.table_index -1) % 3 = 0)","StackModelFit",f)
    661         # self.logger.infoPair("Adding fluxes", "1st convolved")
    662         # self.updateModelFitFlxs("c1", "((b.table_index -1) % 3 = 1)","StackModelFit",f)
    663         # self.logger.infoPair("Adding fluxes", "2nd convolved")
    664         # self.updateModelFitFlxs("c2", "((b.table_index -1) % 3 = 2)","StackModelFit",f)
    665 
    666     '''
    667     Populates the StackApFlx table, mainly from dictionary values found in IPP FITS header
    668     '''
    669     # def populateStackApFlx(self,filter):
    670 
    671         ## self.logger.infoPair("Adding", "petrosians for extended sources")
    672         ## sql = "UPDATE StackModelFit as a, "+f+"SkyChip_xsrc AS b SET \
    673         ## "+f+"petRadius=b.PETRO_RADIUS \
    674         ## ,"+f+"petRadiusErr=b.PETRO_RADIUS_ERR \
    675         ## ,"+f+"petFlux=POW(10.0, -0.4 * b.PETRO_MAG) /" + str(self.header['EXPTIME']) + " \
    676         ## ,"+f+"petFluxErr=-999  \
    677         ## ,"+f+"petR50=b.PETRO_RADIUS_50 \
    678         ## ,"+f+"petR50Err=b.PETRO_RADIUS_50_ERR \
    679         ## ,"+f+"petR90=b.PETRO_RADIUS_90 \
    680         ## ,"+f+"petR90Err=b.PETRO_RADIUS_90_ERR \
    681         ## ,"+f+"petCf=b.PETRO_FILL \
    682         ## WHERE a."+f+"ippDetectID=b.IPP_IDET"
    683         ## self.scratchDb.execute(sql)
    684         ##
    685         ## self.logger.infoPair("Adding", "petFluxErr for non nulls")
    686         ## sql = "UPDATE StackModelFit as a, "+f+"SkyChip_xsrc AS b SET \
    687         ## "+f+"petFluxErr=ABS(b.PETRO_MAG_ERR) * POW(10.0, (-0.4*b.PETRO_MAG)) / " + str(self.header['EXPTIME']) + " / 1.085736  \
    688         ## WHERE a."+f+"ippDetectID=b.IPP_IDET \
    689         ## AND (b.PETRO_MAG_ERR is not NULL AND b.PETRO_MAG is not NULL AND PETRO_MAG_ERR < 1000.0)"
    690         ## # added this because when the petromagerr is crazy large (10^29 it out of ranges the flux err calc)
    691         ## self.logger.infoPair("sql", sql)
    692         ## self.scratchDb.execute(sql)
    693 
    694 
    695         # self.scratchDb.updateAllRows("StackModelFit", "surveyID", str(self.surveyID))
    696         # self.scratchDb.updateAllRows("StackModelFit", "dataRelease", str(self.skychunk.dataRelease))
    697 
    698         # self.logger.infoPair("not adding"," PrimaryF to stackApFlx")
    699         # sql = "UPDATE StackModelFit AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \
    700         #        a.primaryF = ((b.flags & 0x10000) >> 16 ) \
    701         #        WHERE a.ippDetectID = b.ippDetectID \
    702         #        AND b.imageID = " + str(imageID)
     556        # insert detection information for each filter based on the DVO contents
     557        sqlLine = sqlUtility("UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")
     558        sqlLine.group("a." + filter + model + "Mag",       "a." + filter + model + "Mag + b.zp")
     559        sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID")
     560
     561        self.logger.infoPair('sql', sql)     
     562        try:
     563            self.scratchDb.execute(sql)
     564        except:
     565            self.logger.errorPair('failed sql',sql)
     566            return
     567
     568    '''
     569    Populates the StackModelFitPet table, mainly from dictionary values found in IPP FITS header
     570    '''
     571    def populateStackPetrosian(self):
     572
     573        table = "StackModelFitPet"
     574
     575        self.logger.infoPair("Procesing table", table)
     576        self.insertDvoIDs(table, "StackObjectThin")
     577
     578        # if we are going to add a key, do it here so it is useful
     579        # self.scratchDb.createIndex(table, "objID")
     580
     581        for filter in self.filters:
     582            self.populateStackPetrosianFilter(filter)
     583
     584        self.tablesToExport.append(table)
     585
     586    '''
     587    Populates the StackModelFitPet table, mainly from dictionary values found in IPP FITS header
     588    '''
     589    def populateStackPetrosianFilter(self, filter):
     590
     591        if self.stackIDs[filter] <= 0:
     592            self.logger.infoPair("no stack data for filter" , filter)
     593            return True
     594
     595        self.logger.infoPair("Adding", "petrosians for extended sources")
     596        sqlLine = sqlUtility("UPDATE StackModelFitPet AS a, " + filter + "SkyChip_xsrc AS b SET")
     597
     598        sqlLine.group("a." + filter + "petRadius",    "b.PETRO_RADIUS")
     599        sqlLine.group("a." + filter + "petRadiusErr", "b.PETRO_RADIUS_ERR")
     600        sqlLine.group("a." + filter + "petFlux",      "b.PETRO_MAG")  # XXX EAM 20140732 : still need to decide about flux vs mag
     601        sqlLine.group("a." + filter + "petFluxErr",   "b.PETRO_MAG_ERR")  # XXX EAM 20140732
     602        sqlLine.group("a." + filter + "petR50",       "b.PETRO_RADIUS_50")
     603        sqlLine.group("a." + filter + "petR50Err",    "b.PETRO_RADIUS_50_ERR")
     604        sqlLine.group("a." + filter + "petR90",       "b.PETRO_RADIUS_90")
     605        sqlLine.group("a." + filter + "petR90Err",    "b.PETRO_RADIUS_90_ERR")
     606        sqlLine.group("a." + filter + "petCf",        "b.PETRO_FILL")
     607
     608        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET")
     609
     610        print "SQL: " + sql
     611        self.scratchDb.execute(sql)
    703612
    704613    '''
    705614    Populates the StackApFlx table
    706615    '''
    707     def populateStackApFlx(self):
     616    def populateStackApFlxThin(self):
     617
     618        tablename = "StackApFlx"
     619
     620        self.logger.infoPair("Procesing table", tablename)
     621        self.insertDvoIDs(tablename, "StackObjectThin")
     622
     623        # if we are going to add a key, do it here so it is useful
     624        # self.scratchDb.createIndex(tablename, "objID")
    708625
    709626        for filter in self.filters:
    710             if self.stackIDs[filter] <= 0:
    711                 continue
    712 
    713             # XXX is this a reasonable solution?
    714             self.header = self.headerSet[filter]
    715 
    716             self.logger.infoPair("Adding fluxes", "un-convolved")
    717             self.updateModelFitFlxs("", "((b.table_index -1) % 3 = 0)", "StackApFlx", filter)
    718             self.logger.infoPair("Adding fluxes", "1st convolved")
    719             self.updateModelFitFlxs("c1", "((b.table_index -1) % 3 = 1)","StackApFlx", filter)
    720             self.logger.infoPair("Adding fluxes", "2nd convolved")
    721             self.updateModelFitFlxs("c2", "((b.table_index -1) % 3 = 2)","StackApFlx", filter)
    722 
    723             self.deleteDetectionsNotInStackObject("StackApFlx",f)
    724 
    725         self.scratchDb.reportAndDeleteRowsWithNULLS("StackApFlx", "objID")
     627            self.populateStackApFlxFilter(tablename, "", filter)
     628            self.populateStackApFlxFilter(tablename, "C1", filter)
     629            self.populateStackApFlxFilter(tablename, "C2", filter)
     630
     631        self.tablesToExport.append(tablename)
     632
     633    '''
     634    Populates the StackApFlx table
     635    '''
     636    def populateStackApFlx(self, version):
     637
     638        tablename = "StackModelFitFlx" + version
     639
     640        self.logger.infoPair("Procesing table", tablename)
     641        self.insertDvoIDs(tablename, "StackObjectThin")
     642
     643        # if we are going to add a key, do it here so it is useful
     644        # self.scratchDb.createIndex(tablename, "objID")
     645
     646        for filter in self.filters:
     647            self.populateStackApFlxFilter(tablename, version, filter)
     648
     649        self.tablesToExport.append(tablename)
     650
     651    '''
     652    Updates aperture fluxes for StackApFkx table
     653    '''
     654    def populateStackApFlxFilter(self, tablename, version, filter):
     655
     656        prefix = "none"
     657
     658        # order of the 3 convolutions is fixed (raw, C1, C2) = (1, 2, 3)
     659        if version == "":   
     660            psfCondition = "(b.table_index % 3 = 1)"
     661            prefix = ""
     662        if version == "C1":
     663            psfCondition = "(b.table_index % 3 = 2)"
     664            prefix = "c1"
     665        if version == "C2":
     666            psfCondition = "(b.table_index % 3 = 0)"
     667            prefix = "c2"
     668
     669        if prefix == "none":
     670            self.logger.errorPair("invalid stack ap flux version", version)
     671            raise
     672
     673        if self.stackIDs[filter] <= 0:
     674            self.logger.infoPair("no stack data for filter" , filter)
     675            return True
     676
     677        minRadius = 1
     678        maxRadius = 10
     679        if tablename == 'StackApFlx':
     680            minRadius = 3
     681            maxRadius = 5
     682
     683        # properly -999 these to start with:
     684        # XXX the default should take care of this, but does not
     685
     686        sqlLine = sqlUtility("UPDATE " + tablename + " SET")
     687        for radius in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]:
     688            if radius < minRadius: continue
     689            if radius > maxRadius: continue
     690            sqlLine.group(filter + prefix + "flxR" + str(radius),          "-999")
     691            sqlLine.group(filter + prefix + "flxR" + str(radius) + "Err",  "-999")
     692            sqlLine.group(filter + prefix + "flxR" + str(radius) + "Std",  "-999")
     693            sqlLine.group(filter + prefix + "flxR" + str(radius) + "Fill", "-999")
     694
     695        sql = sqlLine.makeEquals("")
     696
     697        print "SQL: " + sql
     698        self.scratchDb.execute(sql)
     699
     700        # set the flux values from the cmf file
     701        cmfTable = filter + "SkyChip_xrad"
     702
     703        # we have variable numbers of these columns. find out which are in use
     704        sql = "DESCRIBE " + cmfTable + " 'aper_fill_%'"
     705        rs = self.scratchDb.executeQuery(sql)
     706        rs.first()
     707        columns = []
     708        column = rs.getString(1)
     709        columns.append(column)
     710        while (rs.next()):
     711            column = rs.getString(1)
     712            columns.append(column)
     713        rs.close()
     714
     715        # use regex to select the aperture number (APER_FILL_N)
     716        numbers = []
     717        for column in columns:
     718            number = re.sub("APER_FILL_","",column)
     719            if (int(number) >= minRadius and int(number) <= maxRadius):
     720                numbers.append(number)
     721                self.logger.infoPair("Aperture Numbers",number)
     722
     723        # XXX : I need to worry about which header this is
     724        header = self.headerSet[filter]
     725        exptime = self.getKeyFloat(header, "%.5f", "EXPTIME")
     726
     727        # generate the sql to do the necessary ops on the columns   
     728        sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET ")
     729
     730        for number in numbers:
     731            sqlLine.group(filter + prefix + "flxR" + number,          "b.APER_FLUX_"       + number + " / " + exptime)
     732            sqlLine.group(filter + prefix + "flxR" + number + "Err",  "b.APER_FLUX_ERR_"   + number + " / " + exptime)
     733            sqlLine.group(filter + prefix + "flxR" + number + "Std",  "b.APER_FLUX_STDEV_" + number + " / " + exptime)
     734            sqlLine.group(filter + prefix + "flxR" + number + "Fill", "b.APER_FILL_"       + number + " / " + exptime)
     735           
     736        sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND  " + psfCondition)
     737        self.logger.infoPair("sql",sql)
     738        self.scratchDb.execute(sql)
     739
     740    '''
     741    Populates the StackToImage table
     742    '''
     743    def populateStackToImage(self):
     744
     745        self.logger.infoPair("Procesing table", "StackToImage")
     746
     747        for filter in self.filters:
     748           
     749            stackmetaID = self.stackIDs[filter]
     750            if stackmetaID > 0:
     751   
     752                imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaID)
     753
     754                for imageID in imageIDs:
     755
     756                    sql = "INSERT INTO StackToImage (stackMetaID, imageID) \
     757                   VALUES (\
     758                   " + str(stackmetaID) + ", " + imageID + ")"
     759                    self.scratchDb.execute(sql)
     760
     761                    # now update StackMeta with correct number of inputs
     762                    sql = "UPDATE "+filter+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaID)+")"
     763                    self.scratchDb.execute(sql)
     764
     765        self.tablesToExport.append("StackToImage")
    726766
    727767    '''
    … …  
    743783       
    744784
    745     '''
    746     Populates the StackToImage table
    747     '''
    748     def populateStackToImage(self):
    749 
    750         self.logger.infoPair("Procesing table", "StackToImage")
    751 
    752         for filter in self.filters:
    753            
    754             stackmetaID = self.stackIDs[filter]
    755             if stackmetaID > 0:
    756    
    757                 imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaID)
    758 
    759                 for imageID in imageIDs:
    760 
    761                     sql = "INSERT INTO StackToImage (stackMetaID, imageID) \
    762                    VALUES (\
    763                    " + str(stackmetaID) + ", " + imageID + ")"
    764                     self.scratchDb.execute(sql)
    765 
    766                     # now update StackMeta with correct number of inputs
    767                     sql = "UPDATE "+filter+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaID)+")"
    768                     self.scratchDb.execute(sql)
    769 
    770     '''
    771     Generates the randomStackIds
    772     '''
    773     def populateRandomsAndSkyCellID(self):
    774 
    775         self.logger.infoPair("Generating Randoms","for StackObject")
    776         sql = "update StackObject set randomStackObjID = FLOOR(RAND()*9223372036854775807) \
    777               ,skyCellID = " + str(self.skycell)+ " \
    778               ,surveyID = " + str(self.surveyID)
    779         try:
    780             self.scratchDb.execute(sql)
    781         except:
    782             self.logger.infoPair("failed sql", sql)
    783 
    784     '''
    785     copies randomStackIds from StackObject to tableName
    786     '''
    787     def copyRandomsAndSkyCellIDFromStackObject(self, tableName):
    788 
    789         self.logger.infoPair("copying Randoms from StackObject to ", tableName)
    790         sql = "update " + tableName + " join StackObject using (objID) \
    791         set " + tableName + ".randomStackObjID = StackObject.randomStackObjID \
    792         , " + tableName + ".skyCellID = StackObject.skyCellID \
    793         , " + tableName + ".surveyID = StackObject.surveyID"
    794         self.logger.infoPair("sql", sql)
    795         try:
    796             self.scratchDb.execute(sql)
    797         except:
    798             self.logger.infoPair("failed sql", sql)
    799 
    800 
    801        
    802     '''
    803     I think we are no longer using this function: this have been
    804     replaced with the code in 'selectDvoObjIDs' and 'selectDvoFilterIDs'
    805     '''
    806     def populateStackIPPDetectIDFromDvo(self, tableName, all):
    807 
    808         #tableName = "StackObject"
    809         self.logger.infoPair("Processing table", tableName)
    810 
    811         filters = ['g','r','i','z','y']
    812        
    813         for filter in self.filters:
    814             if self.stackIDs[filter] < 0:
    815                 self.logger.infoPair("skipping ingest of dvo for filter",f)
    816                 continue
    817 
    818             self.header = self.headerSet[filter]
    819             stackmetaID = self.stackIDs[filter]
    820 
    821             imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])
    822             self.logger.infoPair("obtained","imageID")
    823             addsql = ""
    824             if (all == 1):
    825                 addsql = "a." + filter + "ra = b.ra, \
    826                   a." + filter + "dec = b.dec_, \
    827                   a." + filter + "raErr = b.raErr, \
    828                   a." + filter + "decErr = b.decErr, \
    829                   a." + filter + "zp = b.zp, \
    830                   a." + filter + "expTime = b.expTime,"
    831 
    832                
    833             sql = "UPDATE " + tableName + " AS a, "  + self.scratchDb.dvoDetectionTable + " AS b \
    834                   SET a." + filter + "ippDetectID = b.ippDetectID, \
    835                   " + addsql + " \
    836                   a." + filter + "stackDetectID = b.detectID,   \
    837                   a." + filter + "ippObjID = b.ippObjID, \
    838                   a." + filter + "stackMetaID = " + str(stackmetaID) +  " \
    839                   WHERE a.objID = b.objID  AND b.imageID = " + str(imageID)
    840 
    841             self.logger.infoPair('sql',sql)   
    842             self.scratchDb.execute(sql)
    843 
    844         # out of the loops - give dataRelase to all       
    845         self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease))
    846 
    847 
    848785    def deleteNULLippDetectIDs(self, tableName):
    849786
    … …  
    863800    def alterPspsTables(self):
    864801
    865         self.logger.debug("Altering PSPS tables")
     802        self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)")
    866803        # heather uncommented the unique line -- well that no worky
    867804        #self.scratchDb.makeColumnUnique("StackObject", "objID")
    868         self.scratchDb.createIndex("StackObject", "ippDetectID")
    869         self.scratchDb.createIndex("StackObject", "stackDetectID")
    870         self.scratchDb.createIndex("StackApFlx", "ippDetectID")
    871         self.scratchDb.createIndex("StackModelFit", "ippDetectID")
     805        #self.scratchDb.createIndex("StackObject", "ippDetectID")
     806        #self.scratchDb.createIndex("StackObject", "stackDetectID")
     807        #self.scratchDb.createIndex("StackApFlx", "ippDetectID")
     808        #self.scratchDb.createIndex("StackModelFit", "ippDetectID")
    872809
    873810        return True
    … …  
    878815    def indexIppTables(self):
    879816
    880         self.logger.infoPair("Creating indexes on", "IPP tables")
    881         self.scratchDb.createIndex("SkyChip_psf",  "IPP_IDET")
    882         self.scratchDb.createIndex("SkyChip_xfit", "IPP_IDET")
    883         self.scratchDb.createIndex("SkyChip_xrad", "IPP_IDET")
    884         self.scratchDb.createIndex("SkyChip_xsrc", "IPP_IDET")
     817        for filter in self.filters:
     818            if self.stackIDs[filter] <= 0: 
     819                self.logger.infoPair("no files for filter" , filter)
     820                continue
     821
     822            self.logger.infoPair("Creating indexes on", "IPP tables")
     823            self.scratchDb.createIndex(filter + "SkyChip_psf",  "IPP_IDET")
     824            self.scratchDb.createIndex(filter + "SkyChip_xfit", "IPP_IDET")
     825            self.scratchDb.createIndex(filter + "SkyChip_xrad", "IPP_IDET")
     826            self.scratchDb.createIndex(filter + "SkyChip_xsrc", "IPP_IDET")
    885827
    886828        return True
    … …  
    892834    def selectDvoObjIDs(self):
    893835
    894         print "starting selectDvoObjIDs"
    895 
    896         sql  = "INSERT INTO StackObjectThin (objID, ippObjID, surveyID, skyCellID, dvoRegionID, dataRelease) "
    897         sql += "SELECT DISTINCT objID, ippObjID, "
    898         sql += str(self.surveyID) + ", "
    899         sql += str(self.skycell) + ", "
    900         sql += str(self.dvoRegionID) + ", "
    901         sql += str(self.skychunk.dataRelease)
    902 
    903         sql += " FROM " + self.scratchDb.dvoDetectionTable
    904         sql += " WHERE ("
    905 
    906         print "sql so far: " + sql
    907 
     836        sqlLine = sqlUtility("INSERT INTO StackObjectThin (")
     837       
     838        sqlLine.group("objID",            "objID")
     839        sqlLine.group("ippObjID",         "ippObjID")
     840        sqlLine.group("surveyID",         "'" + str(self.surveyID) + "'")
     841        sqlLine.group("skyCellID",        "'" + str(self.skycell) + "'")
     842        sqlLine.group("dvoRegionID",      "'" + str(self.dvoRegionID) + "'")
     843        sqlLine.group("dataRelease",      "'" + str(self.skychunk.dataRelease) + "'")
     844        sqlLine.group("randomStackObjID", "FLOOR(RAND()*9223372036854775807)")
     845
     846        whereClause = " WHERE ("
    908847        imageIDs = self.imageIDs.values()
    909848        for i in range(len(imageIDs)):
    … …  
    911850            self.logger.infoPair("selecting imageID", imageID)
    912851
    913             print "table: self.scratchDb.dvoDetectionTable"
    914             print "imageID: " + str(imageID)
    915 
    916             sql += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")"
    917             print "sql: " + sql
     852            whereClause += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")"
    918853
    919854            if (i < len(imageIDs) - 1):
    920                 sql += " or "
    921 
    922         print "done segment"
    923 
    924         sql += ")"
    925 
     855                whereClause += " or "
     856
     857        whereClause += ")"
     858
     859        sql = sqlLine.makeRaw(") SELECT DISTINCT ", "FROM " + self.scratchDb.dvoDetectionTable + whereClause)
    926860        print "SQL: " + sql
     861
    927862        self.scratchDb.execute(sql)
    928863
    929     ## XXX '''
    930     ## XXX select objid (psps object ID) from dvo restricted to this set of imageIDs
    931     ## XXX '''
    932     ## XXX def selectDvoFilterIDs(self, table):
    933     ## XXX
    934     ## XXX     # XXX I am using imageID a lot: generate a filter-index list
    935     ## XXX     for filter in self.filters:
    936     ## XXX         self.header = self.headerSet[filter]
    937     ## XXX         imageIDgpc = self.header['IMAGEID']
    938     ## XXX         imageIDdvo = self.scratchDb.getImageIDFromExternID(imageIDgpc)
    939     ## XXX         self.logger.infoPair("selecting imageID", imageIDdvo)
    940     ## XXX
    941     ## XXX         sql  = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable " AS b SET "
    942     ## XXX
    943     ## XXX         sql += "a." + filter + "ippDetectID   = b.ippDetectID, " # eg, gippDetectID (from dvo.measure.detID)
    944     ## XXX         sql += "a." + filter + "stackDetectID = b.detectID "     # eg, gstackDetectID (from dvo.measure.extID)
    945     ## XXX         sql += "a." + filter + "stackMetaID   = " + imageIDgpc   # eg, gstackMetaID (from cmf.IMAGEID)
    946     ## XXX
    947     ## XXX         sql += "WHERE a.objID = b.objID "
    948     ## XXX         sql += "AND b.imageID = " + imageIDdvo
    949     ## XXX
    950     ## XXX         print "SQL: " + sql
    951     ## XXX         self.scratchDb.execute(sql)
    952 
    953864    '''
    954865    Insert objid (from dvo) into table from main table
    … …  
    956867    def insertDvoIDs(self, table, mainTable):
    957868
    958         fields = "objID, ippObjID, surveyID, skyCellID, dvoRegionID, dataRelease, "
     869        fields = "objID, ippObjID, surveyID, skyCellID, randomStackObjID, primaryDetection, dvoRegionID, dataRelease, "
    959870
    960871        for i in range(len(self.filters)):
    … …  
    1007918        self.scratchDb.execute(sql)
    1008919   
    1009    
    1010920    '''
    1011921    Does the processing, i.e. pulling stuff from IPP tables into PSPS tables
    … …  
    1015925        self.skipBatch = False
    1016926
    1017         # get the IDs from the DVO tables
    1018         # XXX deprecated along with dvograbber
    1019         # if not self.getIDsFromDVO(): return False
    1020 
    1021         # do some basics first:
    1022         # populate stack meta per filter
    1023         # insert objid/stackdetectid per filter to each table           
     927        # each of the "populate*" methods below add their tables to the list:
    1024928        self.tablesToExport=[]   
    1025929
    … …  
    1060964        if self.stackType != "NIGHTLY_STACK":
    1061965
     966            self.logger.infoPair("populating ","StackModelFitThin")
     967            self.populateStackModelFitThin()
     968
    1062969            self.logger.infoPair("populating ","StackModelFit")
    1063970            self.populateStackModelFit("Exp", "PS_MODEL_EXP")
    … …  
    1065972            self.populateStackModelFit("Ser", "PS_MODEL_SERSIC")
    1066973
    1067             #self.populateStackModelPet("Ser", "PS_MODEL_SERSIC")
    1068 
    1069             #self.logger.infoPair("populating ","StackApFlx")
    1070             #self.populateStackApFlx()
    1071        
     974            self.populateStackPetrosian()
     975
     976            self.populateStackModelFit("Ser", "PS_MODEL_SERSIC")
     977
     978            self.logger.infoPair("populating ","StackApFlx Tables")
     979            self.populateStackApFlxThin()
     980
     981            self.populateStackApFlx("")
     982            self.populateStackApFlx("C1")
     983            self.populateStackApFlx("C2")
     984
    1072985        self.logger.infoPair("populating","StackToImage")
    1073986        self.populateStackToImage()
    1074987       
    1075988        # self.setMinMaxObjID(["StackObject"])
    1076         # self.updateStackTypeID("StackModelFit")
    1077 
    1078         self.tablesToExport.append("StackToImage")
    1079         self.tablesToExport.append("StackObject")
    1080         self.tablesToExport.append("StackModelFit")
    1081         self.tablesToExport.append("StackApFlx")
    1082         self.tablesToExport.append("SkinnyObject")
     989
    1083990        self.logger.infoPair("finishing","populatePspsTables");
    1084991        return True
    … …  
    11341041                 
    11351042                # IPP FITS files are littered with infinities, so remove these
    1136                 self.logger.debug("Removing Infinity values from all columns")
     1043                self.logger.info("Removing Infinity values from all columns")
    11371044                table = stilts.tpipe(table, cmd='keepcols "' + columns + '"')
    11381045                table = stilts.tpipe(table, cmd='replaceval -Infinity null *')

  • branches/eam_branches/ipp-20140717/ippToPsps/jython/testCode.py

    r37156 r37168  
    66import sys
    77# import os
    8 # import math
     8import math
    99# import logging.config
    1010
    11 from sqlUtility import sqlUtility
     11# from sqlUtility import sqlUtility
    1212
    1313# testCode class
    … …  
    1919
    2020    def run(self):
     21
     22        myvalue = "10.0"
     23        print "test line"
     24        print "log of 5.0: " + str(math.log10(float(myvalue)))
    2125
    2226        names = {}
Note: See TracChangeset for help on using the changeset viewer.