Index: trunk/ippToPsps/jython/stackbatch.py =================================================================== --- trunk/ippToPsps/jython/stackbatch.py (revision 37106) +++ trunk/ippToPsps/jython/stackbatch.py (revision 37246) @@ -1,8 +1,8 @@ -q#!/usr/bin/env jython +#!/usr/bin/env jython import os.path import glob import sys - +import math import stilts @@ -17,4 +17,5 @@ from ipptopspsdb import IppToPspsDb from scratchdb import ScratchDb +from sqlUtility import sqlUtility import logging.config @@ -39,6 +40,5 @@ scratchDb, skyID, - batchID, - useFullTables): + batchID): super(StackBatch, self).__init__( @@ -52,201 +52,134 @@ batchID, "ST", - "", - #gpc1Db.getStackStageCmf(skychunk.dvoLabel, stackID), - useFullTables) - - # self.printline = 0 - # # self.testprint() -# self.logger.infoPair("did I get", "here"); + None) + self.stackType = "DEEP_STACK" # TODO - ## = stuff I commented out for pv2 stacks + self.stackTypeID = self.getStackTypeID() - #heather tasks - - # get a list of stackIDs - self.logger.infoPair ("getting stackID for: ","g.00000") - gstackID = gpc1Db.getStackIDFromSkyIDAndFilter( skychunk.dvoLabel, skyID, "g.00000") - self.logger.infoPair ("getting stackID for: ","r.00000") - rstackID = gpc1Db.getStackIDFromSkyIDAndFilter( skychunk.dvoLabel, skyID, "r.00000") - self.logger.infoPair ("getting stackID for: ","i.00000") - istackID = gpc1Db.getStackIDFromSkyIDAndFilter( skychunk.dvoLabel, skyID, "i.00000") - self.logger.infoPair ("getting stackID for: ","z.00000") - zstackID = gpc1Db.getStackIDFromSkyIDAndFilter( skychunk.dvoLabel, skyID, "z.00000") - self.logger.infoPair ("getting stackID for: ","y.00000") - ystackID = gpc1Db.getStackIDFromSkyIDAndFilter( skychunk.dvoLabel, skyID, "y.00000") - # self.logger.infoPair("g stackID ",gstackID) - # self.logger.infoPair("r stackID ",rstackID) - # self.logger.infoPair("i stackID ",istackID) - # self.logger.infoPair("z stackID ",zstackID) - # self.logger.infoPair("y stackID ",ystackID) - # raise - # from each stackget fits name from getStackStageCmf - if gstackID > 0: - self.logger.infoPair ("finding fits file for: ","g.00000") - gfits = gpc1Db.getStackStageCmf(skychunk.dvoLabel, gstackID) - self.gfits = gfits - if rstackID > 0: - self.logger.infoPair ("finding fits file for: ","r.00000") - rfits = gpc1Db.getStackStageCmf(skychunk.dvoLabel, rstackID) - self.rfits = rfits - if istackID > 0: - self.logger.infoPair ("finding fits file for: ","i.00000") - ifits = gpc1Db.getStackStageCmf(skychunk.dvoLabel, istackID) - self.ifits = ifits - if zstackID > 0: - self.logger.infoPair ("finding fits file for: ","z.00000") - zfits = gpc1Db.getStackStageCmf(skychunk.dvoLabel, zstackID) - self.zfits = zfits - if ystackID > 0: - self.logger.infoPair ("finding fits file for: ","y.00000") - yfits = gpc1Db.getStackStageCmf(skychunk.dvoLabel, ystackID) - self.yfits = yfits - - #from each of those get self.gheader, self.rheader, etc. - if gstackID > 0: - if gfits: - gheader = gfits.getPrimaryHeader() - if not gheader: - self.logger.errorPair("could not read FITS PHU for ",gstackID) - raise - self.gheader = gheader - if rstackID > 0: - if rfits: - rheader = rfits.getPrimaryHeader() - if not rheader: - self.logger.errorPair("could not read FITS PHU for ",rstackID) - raise - self.rheader = rheader - if istackID > 0: - if ifits: - iheader = ifits.getPrimaryHeader() - if not iheader: - self.logger.errorPair("could not read FITS PHU for ",istackID) - raise - self.iheader = iheader - if zstackID > 0: - if zfits: - zheader = zfits.getPrimaryHeader() - if not zheader: - self.logger.errorPair("could not read FITS PHU for ",zstackID) - raise - self.zheader = zheader - if ystackID > 0: - if yfits: - yheader = yfits.getPrimaryHeader() - if not yheader: - self.logger.errorPair("could not read FITS PHU for ",ystackID) - raise - self.yheader = yheader - # get stack meta data - self.gstackID = gstackID - self.rstackID = rstackID - self.istackID = istackID - self.zstackID = zstackID - self.ystackID = ystackID - - metastackid = -999 - if gstackID > 0: - metastackid = gstackID - if rstackID > 0: - metastackid = rstackID - if istackID > 0: - metastackid = istackID - if zstackID > 0: - metastackid = zstackID - if ystackID > 0: - metastackid = ystackID - #there is probably a better way to do this - if metastackid < 0: - self.logger.errorPair("can't find any stack ids...?? ","??") + self.filters = ['g', 'r', 'i', 'z', 'y'] + self.stackIDs = {} + self.imageIDs = {} + self.fits = {} + self.headerSet = {} + + # we have two sets of imageIDs here: + + # stackID : this is the id from the gpc1 stack table and (should) match the + # cmf header keyword 'IMAGEID', this is also called EXTERN_ID in dvoImagesTable + + # imageID : this is the DVO-internal image ID for the stack cmf, and is called + # IMAGE_ID in dvoImagesTable + + # we just need one valid filter to get the generic metadata + validFilter = 'none' + + # get a list of stackIDs, identify the CMFs, read the headers: + for filter in self.filters: + filterName = filter + ".00000" + + self.logger.infoPair ("getting stackID for: ", filterName) + stackID = gpc1Db.getStackIDFromSkyIDAndFilter(skychunk.dvoLabel, skyID, filterName) + self.stackIDs[filter] = stackID + + if (stackID <= 0): + continue + + validFilter = filter + + # from each stack, get fits name from getStackStageCmf + self.logger.infoPair ("finding fits file for: ", filterName) + self.fits[filter] = gpc1Db.getStackStageCmf(skychunk.dvoLabel, stackID) + if not self.fits[filter]: + self.logger.errorPair("could not find cmf file name for ", stackID) + raise + + header = self.fits[filter].getPrimaryHeader() + if not header: + self.logger.errorPair("could not read FITS PHU for ", stackID) + raise + + self.headerSet[filter] = header + + self.logger.infoPair(filter + " stackID = ", stackID) + + if not (str(stackID) == header['IMAGEID']): + self.logger.errorPair("inconsistency: header IMAGEID does not match stackID", str(stackID) + " vs " + header['IMAGEID']) + raise + + imageID = self.scratchDb.getImageIDFromExternID(stackID) + self.logger.infoPair(filter + " imageID = ", imageID) + self.imageIDs[filter] = imageID + + # did we find any valid stacks? + if validFilter == 'none': + self.logger.errorPair("can't find any valid stacks...?? ","??") raise - meta = self.gpc1Db.getStackStageMeta(metastackid) - ## this should be ok - it takes a stack_id and returns filter/skycell etc. - - - if not meta: - self.logger.errorPair("Could not get stack", "metadata") + validStackID = self.stackIDs[validFilter] + + # using the given stack ID, get the generic information: filter, skycell, etc. + metadata = self.gpc1Db.getStackStageMeta(validStackID) + + if not metadata: + self.logger.errorPair("Could not get stack metadata for", validStackID) raise - #self.filter = meta[0]; - #self.filter = self.filter[0:1] - #self.filterID = self.scratchDb.getFilterID(self.filter) - self.skycell = meta[1]; - - # self.testprint() - - # skycell is, eg "skycell.1133.081" - # we need 1133.081 for skycell, but store as an int, so instead store 1133081 - # projection cell is just the 1133 bit - # This Is how it should be done - #self.projectioncell = self.skycell[8:12] - #self.skycell = self.skycell[8:] - #self.skycell = self.skycell.replace(".", "") - #self.projectioncell = self.skycell[8:12] - - # TODO this is the wrong way, but is consistent with SkyCell table in IN data - self.skycell = self.skycell[8:] - self.projectioncell = self.skycell - - # self.testprint() - - # proposed new values. Need to coordinate with the SkyCell table - # this fits in 32 bits for 0 < tessID < 214 - # self.skycell = str( (tessID * (10000 * 1000)) + (int(self.projectioncell) * 1000) + cellID ) - - self.analysisVer = meta[2]; - - #self.expTime = gpc1Db.getStackExpTime(self.id) + validHeader = self.headerSet[validFilter] + + tessName = self.getKeyValue(header, 'TESS_ID') + self.tessID = self.scratchDb.getTessID(tessName) + + skycellName = self.getKeyValue(header, 'SKYCELL') + + # XXX EAM 20140812 : I am hardwiring the parsing logic for RINGS vs LOCAL + if tessName == 'RINGS.V3': + # skycell is, eg "skycell.1133.081" + # 0123456789012345 + self.projectionID = skycellName[8:12] + self.skycellID = skycellName[13:] + else: + # skycell is, eg "skycell.081" + # 01234567890 + self.projectionID = 0 + self.skycellID = skycellName[8:11] + if self.skycellID == "": + self.skycellID = -1 + + self.analysisVer = metadata[2]; + + # delete PSPS tables + for filter in self.filters: + tableName = filter + "StackMeta" + self.dropTableVerbose(tableName) + + self.dropTableVerbose("StackObjectThin") + self.dropTableVerbose("StackObjectRaw") - # delete PSPS tables - self.logger.infoPair("dropping table:","gStackMeta") - self.scratchDb.dropTable("gStackMeta") - self.logger.infoPair("dropping table:","rStackMeta") - self.scratchDb.dropTable("rStackMeta") - self.logger.infoPair("dropping table:","iStackMeta") - self.scratchDb.dropTable("iStackMeta") - self.logger.infoPair("dropping table:","zStackMeta") - self.scratchDb.dropTable("zStackMeta") - self.logger.infoPair("dropping table:","yStackMeta") - self.scratchDb.dropTable("yStackMeta") - self.logger.infoPair("dropping table:","StackObject") - - self.scratchDb.dropTable("StackObject") - self.logger.infoPair("dropping table:","StackModelFit") - self.scratchDb.dropTable("SkinnyObject") - self.scratchDb.dropTable("StackModelFit") - self.logger.infoPair("dropping table:","StackApFlx") - - self.scratchDb.dropTable("StackApFlx") - self.logger.infoPair("dropping table:","StackToImage") - - self.scratchDb.dropTable("StackToImage") - # self.testprint() - - # for PV2 we will have (grizy) of stack meta and skychip - + self.dropTableVerbose("StackModelFitExtra") + self.dropTableVerbose("StackModelFitDeV") + self.dropTableVerbose("StackModelFitExp") + self.dropTableVerbose("StackModelFitSer") + + self.dropTableVerbose("StackApFlx") + self.dropTableVerbose("StackApFlxFull") + self.dropTableVerbose("StackApFlxFullC1") + self.dropTableVerbose("StackApFlxFullC2") + + self.dropTableVerbose("StackPetrosian") + + self.dropTableVerbose("StackToImage") # delete IPP tables - self.scratchDb.dropTable("gSkyChip_psf") - self.scratchDb.dropTable("gSkyChip_xrad") - self.scratchDb.dropTable("gSkyChip_xfit") - self.scratchDb.dropTable("gSkyChip_xsrc") - self.scratchDb.dropTable("rSkyChip_psf") - self.scratchDb.dropTable("rSkyChip_xrad") - self.scratchDb.dropTable("rSkyChip_xfit") - self.scratchDb.dropTable("rSkyChip_xsrc") - self.scratchDb.dropTable("iSkyChip_psf") - self.scratchDb.dropTable("iSkyChip_xrad") - self.scratchDb.dropTable("iSkyChip_xfit") - self.scratchDb.dropTable("iSkyChip_xsrc") - self.scratchDb.dropTable("zSkyChip_psf") - self.scratchDb.dropTable("zSkyChip_xrad") - self.scratchDb.dropTable("zSkyChip_xfit") - self.scratchDb.dropTable("zSkyChip_xsrc") - self.scratchDb.dropTable("ySkyChip_psf") - self.scratchDb.dropTable("ySkyChip_xrad") - self.scratchDb.dropTable("ySkyChip_xfit") - self.scratchDb.dropTable("ySkyChip_xsrc") + if not self.config.retry: + self.logger.info("dropping Stack cmf tables") + for filter in self.filters: + tableName = filter + "SkyChip_psf" + self.scratchDb.dropTable(tableName) + tableName = filter + "SkyChip_xrad" + self.scratchDb.dropTable(tableName) + tableName = filter + "SkyChip_xsrc" + self.scratchDb.dropTable(tableName) + tableName = filter + "SkyChip_xfit" + self.scratchDb.dropTable(tableName) # create an output filename, which is {stackID}.FITS @@ -263,98 +196,584 @@ self.ippToPspsDb.insertStackMeta(self.batchID, "x" , self.stackType) - # insert sourceID/imageID combo so DVO can look it up - ##if not self.useFullTables: - ## self.scratchDb.insertNewDvoExternID(self.header['SOURCEID'], self.header['IMAGEID']) - - # self.testprint() - # dump stuff to log self.logger.infoPair("Stack ID", "%d" % self.id) self.logger.infoPair("Stack type", "%s" % self.stackType) - self.logger.infoPair("Skycell", "%s" % self.skycell) - self.logger.infoPair("Projectioncell", "%s" % self.projectioncell) - ##self.logger.infoPair("Filter", "%s" % self.filter) - - ''' - Updates a table with stackMetaID - ''' - def updateStackMetaID(self, table): - - sql = "UPDATE " + table + " SET gstackMetaID=" + str(self.gstackID) + " \ - , rstackMetaID=" + str(self.rstackID) + " \ - , istackMetaID=" + str(self.istackID) + " \ - , zstackMetaID=" + str(self.zstackID) + " \ - , ystackMetaID=" + str(self.ystackID) - - self.scratchDb.execute(sql) - - ''' - Updates a table with stackTypeID grabbed from FitModel init table - ''' - def updateStackTypeID(self, table): - - sql = "UPDATE "+table+" AS a, StackType AS b SET a.stackTypeID=b.stackTypeID WHERE b.name = '" + self.stackType + "'" - self.scratchDb.execute(sql) - - - def testprint(self): - print "here ", self.printline - self.printline += 1 + self.logger.infoPair("SkycellID", "%s" % self.skycellID) + self.logger.infoPair("ProjectionID", "%s" % self.projectionID) + + ### *********************** main populate functions ************************************ + + ''' + Populates the StackMeta table, mainly from header values found in stack cmf FITS header + ''' + def populateStackMeta(self,filter): + + header = self.headerSet[filter] + tablename = filter + "StackMeta" + stackID = self.stackIDs[filter] + filterName = filter + ".00000" + + self.logger.infoPair("Populating table", tablename) + + fwhm_maj = self.getKeyFloat(header, "%.8f", 'FWHM_MAJ') + fwhm_maj_uq = self.getKeyFloat(header, "%.8f", 'FW_MJ_UQ') + psfmodel = self.getKeyValue(header, 'PSFMODEL') + + # make a table + filterID = self.scratchDb.getFilterID(filter) + self.logger.infoPair("filterID is ",str(filterID)) + + photoCalID = str(self.scratchDb.getPhotoCalID(stackID)) + + # mysql is sensitive to values which are ambiugously float. eg + # a warning is raised if we try to insert '25.' into a float field. + # use getKeyFloat(hdr, format, key) to avoid this problem + + sql = "CREATE TABLE " + tablename + " LIKE StackMeta" + try: self.scratchDb.execute(sql) + except: pass + + sqlLine = sqlUtility("INSERT INTO " + tablename + " (") + + sqlLine.group("stackMetaID", str(stackID)) + sqlLine.group("batchID", str(self.batchID)) + sqlLine.group("surveyID", str(self.surveyID)) + sqlLine.group("filterID", str(filterID)) + sqlLine.group("stackTypeID", str(self.stackTypeID)) + sqlLine.group("tessID", str(self.tessID)) + sqlLine.group("projectionID", str(self.projectionID)) + sqlLine.group("skyCellID", str(self.skycellID)) + sqlLine.group("photoCalID", photoCalID) + sqlLine.group("analysisVer", str(self.analysisVer)) + sqlLine.group("expTime", self.getKeyFloat(header, "%.5f", 'EXPTIME')) + sqlLine.group("psfModelID", psfmodel) + sqlLine.group("psfFwhm_mean", fwhm_maj) + sqlLine.group("psfFwhm_max", fwhm_maj_uq) + sqlLine.group("photoZero", self.getKeyFloat(header, "%.5f", 'FPA.ZP')) + sqlLine.group("ctype1", header['CTYPE1']) + sqlLine.group("ctype2", header['CTYPE2']) + sqlLine.group("crval1", self.getKeyFloat(header, "%.8f", 'CRVAL1')) + sqlLine.group("crval2", self.getKeyFloat(header, "%.8f", 'CRVAL2')) + sqlLine.group("crpix1", self.getKeyFloat(header, "%.8f", 'CRPIX1')) + sqlLine.group("crpix2", self.getKeyFloat(header, "%.8f", 'CRPIX2')) + sqlLine.group("cdelt1", self.getKeyFloat(header, "%.8e", 'CDELT1')) + sqlLine.group("cdelt2", self.getKeyFloat(header, "%.8e", 'CDELT2')) + sqlLine.group("pc001001", self.getKeyFloat(header, "%.8e", 'PC001001')) + sqlLine.group("pc001002", self.getKeyFloat(header, "%.8e", 'PC001002')) + sqlLine.group("pc002001", self.getKeyFloat(header, "%.8e", 'PC002001')) + sqlLine.group("pc002002", self.getKeyFloat(header, "%.8e", 'PC002002')) + sqlLine.group("dataRelease", str(self.skychunk.dataRelease)) + + sql = sqlLine.make(") VALUES ( ", ")") + + try: self.scratchDb.execute(sql) + except: + self.logger.error("problem with SQL: " + sql) + raise + + self.tablesToExport.append(tablename) + + ''' + Populates the StackObjectThin table + ''' + def populateStackObjectThin(self): + + self.logger.infoPair("Procesing table", "StackObjectThin") + + # insert the per-object data (IDs, random stack ID, dataRelease, etc) + self.selectDvoObjIDs() + + # add indexes StackObjectThin + self.scratchDb.createIndex("StackObjectThin", "objID") + + self.logger.infoPair("inserting filter dependent items into", "StackObjectThin") + + for filter in self.filters: + self.logger.infoPair("filter", filter) + + stackID = self.stackIDs[filter] + self.logger.infoPair(filter + "stackID = ", stackID) + + if stackID <= 0: + self.logger.infoPair("no stack data for filter" , filter) + continue + + imageID = self.imageIDs[filter] + self.logger.infoPair("selecting imageID", imageID) + + # at this point, the StackObjectThin table includes objID and ippObjID + + # insert detection information for each filter based on the DVO contents + sqlLine = sqlUtility("UPDATE StackObjectThin as a, " + self.scratchDb.dvoDetectionTable + " as b SET") + + sqlLine.group("a."+filter+"ippDetectID", "b.ippDetectID") # eg, gippDetectID (from dvo.measure.detID) + sqlLine.group("a."+filter+"stackDetectID", "b.detectID") # eg, gstackDetectID (from dvo.measure.extID) + sqlLine.group("a."+filter+"stackMetaID", str(stackID)) # eg, gstackMetaID (from cmf.IMAGEID) + + sqlLine.group("a.dvoRegionID", "b.catID") # this is not filter dependent, but is re-written for each filter (if data exists) + sqlLine.group("a.primaryDetection", "((b.flags & 0x10000) >> 16)") # this is not filter dependent, but is re-written for each filter (if data exists) + + sqlLine.group("a."+filter+"haveData", "'1'") + + sqlLine.group("a."+filter+"ra", "b.ra") + sqlLine.group("a."+filter+"dec", "b.dec_") + + sqlLine.group("a."+filter+"PSFMag", "b.Mpsf") + sqlLine.group("a."+filter+"PSFMagErr", "b.dMpsf") + sqlLine.group("a."+filter+"KronMag", "b.Mkron") + sqlLine.group("a."+filter+"KronMagErr", "b.dMkron") + sqlLine.group("a."+filter+"ApMag", "b.Map") + sqlLine.group("a."+filter+"ApMagErr", "b.dMap") + sqlLine.group("a."+filter+"telluricExt", "b.telluricExt") + + sqlLine.group("a."+filter+"infoFlag2", "(b.flags << 13)") + sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND b.imageID = " + str(imageID)) + + try: self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql: ', sql) + raise + + # insert values coming from the CMF, not carried by DVO + header = self.headerSet[filter] + exptime = self.getKeyFloat(header, "%.5f", "EXPTIME") + + sqlLine = sqlUtility("UPDATE StackObjectThin AS a , " + filter + "SkyChip_psf AS b SET") + + sqlLine.group("a."+filter+"raErr", "b.X_PSF_SIG * b.PLTSCALE") + sqlLine.group("a."+filter+"decErr", "b.Y_PSF_SIG * b.PLTSCALE") + sqlLine.group("a."+filter+"sky", "b.SKY / " + str(exptime)) + sqlLine.group("a."+filter+"skyErr", "b.SKY_SIGMA / " + str(exptime)) + sqlLine.group("a."+filter+"infoFlag", "b.FLAGS") + sqlLine.group("a."+filter+"infoFlag2", "b.FLAGS2 | a."+filter+"infoFlag2") + sqlLine.group("a."+filter+"nFrames", "b.N_FRAMES") + + sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID=b.IPP_IDET") + + # index the (f)ippDetectIDs for the next steps? + # for filter in self.filters: + # IDname = filter + "ippDetectID" + # self.scratchDb.createIndex("StackObjectThin", IDname) + + try: + self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql: ', sql) + raise + + self.tablesToExport.append("StackObjectThin") + + ''' + Populates the StackObjectRaw table + ''' + def populateStackObjectRaw(self): + + tablename = "StackObjectRaw" + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + self.scratchDb.createIndex(tablename, "objID") + + self.logger.infoPair("inserting filter dependent cmf items into", tablename) + for filter in self.filters: + self.logger.infoPair("filter", filter) + + stackID = self.stackIDs[filter] + self.logger.infoPair(filter + "stackID = ", stackID) + + if stackID <= 0: + self.logger.infoPair("no stack data for filter" , filter) + continue + + header = self.headerSet[filter] + exptime = self.getKeyFloat(header, "%.5f", "EXPTIME") + + # insert all the detections + sqlLine = sqlUtility("UPDATE " + tablename + " AS a , " + filter + "SkyChip_psf AS b SET") + + sqlLine.group("a."+filter+"haveData", "'1'") + + sqlLine.group("a."+filter+"xPos", "b.X_PSF") + sqlLine.group("a."+filter+"yPos", "b.Y_PSF") + sqlLine.group("a."+filter+"xPosErr", "b.X_PSF_SIG") + sqlLine.group("a."+filter+"yPosErr", "b.Y_PSF_SIG") + sqlLine.group("a."+filter+"psfMajorFWHM", "b.PSF_FWHM_MAJ") + sqlLine.group("a."+filter+"psfMinorFWHM", "b.PSF_FWHM_MIN") + sqlLine.group("a."+filter+"psfTheta", "b.PSF_THETA") + sqlLine.group("a."+filter+"psfCore", "b.PSF_CORE") + sqlLine.group("a."+filter+"psfLikelihood", "psfLikelihood(b.EXT_NSIGMA)") + sqlLine.group("a."+filter+"psfQf", "b.PSF_QF") + sqlLine.group("a."+filter+"psfQfPerfect", "b.PSF_QF_PERFECT") + sqlLine.group("a."+filter+"psfChiSq", "b.PSF_CHISQ / b.PSF_NDOF") + sqlLine.group("a."+filter+"momentXX", "b.MOMENTS_XX") + sqlLine.group("a."+filter+"momentXY", "b.MOMENTS_XY") + sqlLine.group("a."+filter+"momentYY", "b.MOMENTS_YY") + sqlLine.group("a."+filter+"momentR1", "b.MOMENTS_R1") + sqlLine.group("a."+filter+"momentRH", "b.MOMENTS_RH") + sqlLine.group("a."+filter+"PSFFlux", "b.PSF_INST_FLUX / " + str(exptime)) + sqlLine.group("a."+filter+"PSFFluxErr", "b.PSF_INST_FLUX_SIG / " + str(exptime)) + sqlLine.group("a."+filter+"ApFlux", "b.AP_FLUX / " + str(exptime)) + sqlLine.group("a."+filter+"ApFluxErr", "b.AP_FLUX_SIG / " + str(exptime)) + sqlLine.group("a."+filter+"KronFlux", "b.KRON_FLUX / " + str(exptime)) + sqlLine.group("a."+filter+"KronFluxErr", "b.KRON_FLUX_ERR / " + str(exptime)) + sqlLine.group("a."+filter+"ApFillFac", "b.AP_NPIX / (3.14159265359 * POW(b.AP_MAG_RADIUS - 0.5, 2))") + sqlLine.group("a."+filter+"ApRadius", "b.AP_MAG_RADIUS") + sqlLine.group("a."+filter+"KronRad", "b.MOMENTS_R1 * 2.5") + sqlLine.group("a."+filter+"PlateScale", "b.PLTSCALE") + + sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET") + + try: + self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + # insert detection information for each filter based on the DVO contents + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") + sqlLine.group("a."+filter+"expTime", "b.expTime") + sqlLine.group("a."+filter+"zp", "b.zp") + + # where should these go? + # sqlLine.group("a."+filter+"airmass", "b.airmass") + + sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID") + + try: + self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) + + ''' + Populates the StackModelFitExtra table + ''' + def populateStackModelFitExtra(self): + + self.logger.infoPair("Procesing table", "StackModelFitExtra") + self.insertDvoIDs("StackModelFitExtra", "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + # self.scratchDb.createIndex(tablename, "objID") + + # currently, none of the fields defined by the table are generated + + ''' + Populates the StackModelFitExp table + ''' + def populateStackModelFit(self, model, ippModelType): + + tablename = "StackModelFit" + model + + if self.config.camera == "simtest": + self.logger.infoPair("Skipping table for simtest:", tablename) + return + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + # self.scratchDb.createIndex(table, "objID") + + for filter in self.filters: + self.populateStackModelFitFilter(model, ippModelType, filter) + + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) + + ''' + Updates parameters for a particular model in the StackModelFit table + ''' + def populateStackModelFitFilter(self, model, ippModelType, filter): + + tablename = "StackModelFit" + model + + if self.stackIDs[filter] <= 0: + self.logger.infoPair("no stack data for filter" , filter) + return True + + header = self.headerSet[filter] + + # model calibrated magnitude = instrumental magnitude + 2.5*log10(exptime) + ZP (added below from dvoDetectionTable) + magtime = "%.5f" % (2.5*math.log10(float(header['EXPTIME']))) + + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + filter + "SkyChip_xfit AS b SET") + + sqlLine.group("a."+filter+"haveData", "'1'") + + # model calibrated magnitude = instrumental magnitude + 2.5*log10(exptime) + ZP (added below from dvoDetectionTable) + sqlLine.group("a." + filter + model + "Mag", "b.EXT_INST_MAG + " + magtime) # need to modify by zero point and 2.5*log10(exptime) + sqlLine.group("a." + filter + model + "MagErr", "b.EXT_INST_MAG_SIG") + + sqlLine.group("a." + filter + model + "Radius", "b.EXT_WIDTH_MAJ") + sqlLine.group("a." + filter + model + "Ab", "b.EXT_WIDTH_MIN/b.EXT_WIDTH_MAJ") + sqlLine.group("a." + filter + model + "Phi", "DEGREES(b.EXT_THETA)") + + sqlLine.group("a." + filter + model + "RadiusErr", "b.EXT_WIDTH_MAJ_ERR") # these are not correctly defined. + sqlLine.group("a." + filter + model + "AbErr", "b.EXT_WIDTH_MIN_ERR") # these are not correctly defined. + sqlLine.group("a." + filter + model + "PhiErr", "b.EXT_THETA_ERR") # these are not correctly defined. + + sqlLine.group("a." + filter + model + "Chisq", "b.EXT_CHISQ / b.EXT_NDOF") + + # I have raExp = X_EXT + + # This is tricky: RA_EXT is not defined for PS1_SV3 or PS1_V5, but X_EXT is + # XXX EAM 20140812: for PV3, update cmf output to define RA_EXT, DEC_EXT + sqlLine.group("a." + filter + model + "Ra", "b.X_EXT") + sqlLine.group("a." + filter + model + "Dec", "b.Y_EXT") + sqlLine.group("a." + filter + model + "RaErr", "b.X_EXT_SIG") # need to modify by platescale + sqlLine.group("a." + filter + model + "DecErr", "b.Y_EXT_SIG") + + # sersic fit has an extra parameter + if (ippModelType == "PS_MODEL_SERSIC"): + sqlLine.group("a." + filter + model + "Nu", "b.EXT_PAR_07") + sqlLine.group("a." + filter + model + "NuErr", "'0.1'") # this is not measured at the moment + + # missing these two fields? + # sqlLine.group("a." + filter + model + "CF", "b.XX") + # sqlLine.group("a." + filter + model + "Likelihood", "b.XX") + + sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND b.MODEL_TYPE = '" + ippModelType + "'") + + try: self.scratchDb.execute(sql) + except: + self.logger.error("problem with SQL: " + sql) + raise + + # insert detection information for each filter based on the + # DVO contents. Since I already have extracted + # StackObjectRaw, this is probably faster using that table: + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") + sqlLine.group("a." + filter + model + "Mag", "a." + filter + model + "Mag + b.zp") + sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID") + + try: + self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + # we have (modulo signs): + # gRaExp = gra + (X_EXT - gxPos)*gPlateScale / cos(gdec) + # gDecExp = gdec + (Y_EXT - gyPos)*gPlateScale + + rawS = "c." + filter + "PlateScale" + + extR = "a." + filter + model + "Ra" + rawR = "b." + filter + "ra" + rawX = "c." + filter + "xPos" + + extD = "a." + filter + model + "Dec" + rawD = "b." + filter + "dec" + rawY = "c." + filter + "yPos" + + # dRA / dX = -PlateScale + # b.+filter+ra - (a.+filter+model+Ra - c.+filter+xPos)*c.+filter+PlateScale / cos(+filter+dec) + # extR = rawR - (extR - rawX)*rawS / 3600.0 / cos(rawD) + + # dDEC / dY = +PlateScale + # b.+filter+dec + (a.+filter+model+Dec - c.+filter+xPos)*c.+filter+PlateScale / cos(+filter+dec) + # extD = rawD + (extD - rawY)*rawS / 3600.0 + + # insert detection information for each filter based on the DVO contents + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, StackObjectThin as b, StackObjectRaw as c SET ") + sqlLine.group(extR, rawR + " - (" + extR + " - " + rawX + ")*" + rawS + " / 3600.0 / cos(radians(" + rawD + "))") + sqlLine.group(extD, rawD + " + (" + extD + " - " + rawY + ")*" + rawS + " / 3600.0") + field = "a." + filter + model + "RaErr" + sqlLine.group(field, field + "*" + rawS) + field = "a." + filter + model + "DecErr" + sqlLine.group(field, field + "*" + rawS) + sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a.objID = c.objID") + + try: + self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + ''' + Populates the StackPetrosian table, mainly from dictionary values found in IPP FITS header + ''' + def populateStackPetrosian(self): + + tablename = "StackPetrosian" + + if self.config.camera == "simtest": + self.logger.infoPair("Skipping table for simtest:", tablename) + return + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + # self.scratchDb.createIndex(tablename, "objID") + + for filter in self.filters: + self.populateStackPetrosianFilter(filter) + + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) + + ''' + Populates the StackPetrosian table, mainly from dictionary values found in IPP FITS header + ''' + def populateStackPetrosianFilter(self, filter): + + tablename = "StackPetrosian" + + if self.stackIDs[filter] <= 0: + self.logger.infoPair("no stack data for filter" , filter) + return True + + header = self.headerSet[filter] + + # PETRO_MAG is instrumental + 2.5log(exptime) + FPA.ZP + # we want to apply the DVO zero point, so remove FPA.ZP: + + hdrZP = "%.5f" % float(header['FPA.ZP']) + # magtime = "%.5f" % (2.5*math.log10(float(header['EXPTIME']))) + + self.logger.infoPair("Adding", "petrosians for extended sources") + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + filter + "SkyChip_xsrc AS b SET") + + sqlLine.group("a."+filter+"haveData", "'1'") + + sqlLine.group("a." + filter + "petRadius", "b.PETRO_RADIUS") + sqlLine.group("a." + filter + "petRadiusErr", "b.PETRO_RADIUS_ERR") + sqlLine.group("a." + filter + "petMag", "b.PETRO_MAG - " + hdrZP) + sqlLine.group("a." + filter + "petMagErr", "1.0 / b.PETRO_MAG_ERR") + sqlLine.group("a." + filter + "petR50", "b.PETRO_RADIUS_50") + sqlLine.group("a." + filter + "petR50Err", "b.PETRO_RADIUS_50_ERR") + sqlLine.group("a." + filter + "petR90", "b.PETRO_RADIUS_90") + sqlLine.group("a." + filter + "petR90Err", "b.PETRO_RADIUS_90_ERR") + sqlLine.group("a." + filter + "petCf", "b.PETRO_FILL") + + sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET") + + try: self.scratchDb.execute(sql) + except: + self.logger.error("problem with SQL: " + sql) + raise + + # modify petMag to apply the zero point + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") + sqlLine.group("a." + filter + "petMag", "a." + filter + "petMag + b.zp") + sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID") + + try: self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + ''' + Populates the StackApFlx table + ''' + def populateStackApFlx(self): + + tablename = "StackApFlx" + + if self.config.camera == "simtest": + self.logger.infoPair("Skipping table for simtest:", tablename) + return + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + self.scratchDb.createIndex(tablename, "objID") + + for filter in self.filters: + self.populateStackApFlxFilter(tablename, "", filter) + self.populateStackApFlxFilter(tablename, "C1", filter) + self.populateStackApFlxFilter(tablename, "C2", filter) + + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) + + ''' + Populates the StackApFlxFull table + ''' + def populateStackApFlxFull(self, version): + + tablename = "StackApFlxFull" + version + + if self.config.camera == "simtest": + self.logger.infoPair("Skipping table for simtest:", tablename) + return + + self.logger.infoPair("Procesing table", tablename) + self.insertDvoIDs(tablename, "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + self.scratchDb.createIndex(tablename, "objID") + + for filter in self.filters: + self.populateStackApFlxFilter(tablename, version, filter) + + self.deleteRowsWithNoStackData(tablename) + self.tablesToExport.append(tablename) ''' Updates aperture fluxes for StackApFkx table ''' - def updateModelFitFlxs(self, prefix, psfCondition, tablename, filter): - #properly -999 these to start with: - f = filter - if tablename == 'StackModelFit': - sql = "UPDATE " + tablename + " AS a, "+f+"SkyChip_xrad AS b SET \ - "+f+prefix+"flxR1=-999, \ - "+f+prefix+"flxR1Err=-999, \ - "+f+prefix+"flxR1Std=-999, \ - "+f+prefix+"flxR1Fill=-999, \ - "+f+prefix+"flxR2=-999, \ - "+f+prefix+"flxR2Err=-999, \ - "+f+prefix+"flxR2Std=-999, \ - "+f+prefix+"flxR2Fill=-999, \ - "+f+prefix+"flxR3=-999, \ - "+f+prefix+"flxR3Err=-999, \ - "+f+prefix+"flxR3Std=-999, \ - "+f+prefix+"flxR3Fill=-999, \ - "+f+prefix+"flxR4=-999, \ - "+f+prefix+"flxR4Err=-999, \ - "+f+prefix+"flxR4Std=-999, \ - "+f+prefix+"flxR4Fill=-999, \ - "+f+prefix+"flxR5=-999, \ - "+f+prefix+"flxR5Err=-999, \ - "+f+prefix+"flxR5Std=-999, \ - "+f+prefix+"flxR5Fill=-999, \ - "+f+prefix+"flxR6=-999, \ - "+f+prefix+"flxR6Err=-999, \ - "+f+prefix+"flxR6Std=-999, \ - "+f+prefix+"flxR6Fill=-999, \ - "+f+prefix+"flxR7=-999, \ - "+f+prefix+"flxR7Err=-999, \ - "+f+prefix+"flxR7Std=-999, \ - "+f+prefix+"flxR7Fill=-999, \ - "+f+prefix+"flxR8=-999, \ - "+f+prefix+"flxR8Err=-999, \ - "+f+prefix+"flxR8Std=-999, \ - "+f+prefix+"flxR8Fill=-999, \ - "+f+prefix+"flxR9=-999, \ - "+f+prefix+"flxR9Err=-999, \ - "+f+prefix+"flxR9Std=-999, \ - "+f+prefix+"flxR9Fill=-999, \ - "+f+prefix+"flxR10=-999, \ - "+f+prefix+"flxR10Err=-999, \ - "+f+prefix+"flxR10Std=-999, \ - "+f+prefix+"flxR10Fill=-999 \ - WHERE a."+f+"ippDetectID=b.IPP_IDET " - #self.logger.infoPair("sql", sql) - self.scratchDb.execute(sql) - - - #we have variable numbers of these columns. find out which are in use - sql = "DESCRIBE "+f+"SkyChip_xrad 'aper_fill_%'" + def populateStackApFlxFilter(self, tablename, version, filter): + + prefix = "none" + + # order of the 3 convolutions is fixed (raw, C1, C2) = (1, 2, 3) + if version == "": + psfCondition = "(b.table_index % 3 = 1)" + prefix = "" + if version == "C1": + psfCondition = "(b.table_index % 3 = 2)" + prefix = "c1" + if version == "C2": + psfCondition = "(b.table_index % 3 = 0)" + prefix = "c2" + + if prefix == "none": + self.logger.errorPair("invalid stack ap flux version", version) + raise + + if self.stackIDs[filter] <= 0: + self.logger.infoPair("no stack data for filter" , filter) + return True + + minRadius = 1 + maxRadius = 10 + if tablename == 'StackApFlx': + minRadius = 3 + maxRadius = 5 + + # properly -999 these to start with. the default should take + # care of this, but does not + + sqlLine = sqlUtility("UPDATE " + tablename + " SET") + for radius in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]: + if radius < minRadius: continue + if radius > maxRadius: continue + sqlLine.group(filter + prefix + "flxR" + str(radius), "-999") + sqlLine.group(filter + prefix + "flxR" + str(radius) + "Err", "-999") + sqlLine.group(filter + prefix + "flxR" + str(radius) + "Std", "-999") + sqlLine.group(filter + prefix + "flxR" + str(radius) + "Fill", "-999") + + sql = sqlLine.makeEquals("") + + try: self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + # set the flux values from the cmf file + cmfTable = filter + "SkyChip_xrad" + + # we have variable numbers of these columns. find out which are in use + sql = "DESCRIBE " + cmfTable + " 'aper_fill_%'" rs = self.scratchDb.executeQuery(sql) rs.first() @@ -367,840 +786,217 @@ rs.close() - #use regex to leave just the numbers - + # use regex to select the aperture number (APER_FILL_N) numbers = [] - min = 1 - max = 10 - if tablename == 'StackApFlx': - min = 3 - max = 5 for column in columns: number = re.sub("APER_FILL_","",column) - if (int(number) >= min and int(number) <=max): + if (int(number) >= minRadius and int(number) <= maxRadius): numbers.append(number) self.logger.infoPair("Aperture Numbers",number) - #generate the sql to do the necessary ops on the columns - sql = "UPDATE " + tablename + " AS a, "+f+"SkyChip_xrad AS b SET " - cnt =0 - exptime = self.safeDictionaryAccess(self.header, 'EXPTIME') + # get the exposure time for aperture fluxes + header = self.headerSet[filter] + exptime = self.getKeyFloat(header, "%.5f", "EXPTIME") + + # generate the sql to do the necessary ops on the columns + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET ") + + sqlLine.group("a."+filter+"haveData", "'1'") + for number in numbers: - if (cnt>0): - sql=sql+" , " - sql=sql+f+prefix+"flxR"+number+"=b.APER_FLUX_"+number +" / " + str(exptime) + ", " - sql=sql+f+prefix+"flxR"+number+"Err=b.APER_FLUX_ERR_"+number +" / " + str(exptime) + ", " - sql=sql+f+prefix+"flxR"+number+"Std=b.APER_FLUX_STDEV_"+number +" / " + str(exptime) + ", " - sql=sql+f+prefix+"flxR"+number+"Fill=b.APER_FILL_"+number + " " - cnt=cnt+1 + field = filter + prefix + "flxR" + number + sqlLine.group(field, "b.APER_FLUX_" + number + " / " + exptime) + sqlLine.group(field + "Err", "b.APER_FLUX_ERR_" + number + " / " + exptime) + sqlLine.group(field + "Std", "b.APER_FLUX_STDEV_" + number + " / " + exptime) + sqlLine.group(field + "Fill", "b.APER_FILL_" + number) - sql=sql+"WHERE a."+f+"ippDetectID=b.IPP_IDET AND "+psfCondition - self.logger.infoPair("sql",sql) + sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND " + psfCondition) + self.scratchDb.execute(sql) - - ''' - Updates parameters for a particular model in the StackModelFit table - ''' - def updateModelFit(self, model, ippModelType, filter, exptime): - f = filter - - # idk what to do about PET - - sql = "DESCRIBE "+f+"SkyChip_xfit 'EXT_COVAR_%'" + # instrumental flux, AB mags, Jy: + # flux_inst = cts / sec + # mag_AB = -2.5*log(flux_inst) + ZP [with appropriate definition of zero point] + # mag_AB = -2.5*log(flux_cgs) - 48.6 [by construction with ~Vega flux in V-band] + # flux_cgs = ten(-0.4*(mag_AB + 48.6)) + # flux_cgs = ten(-0.4*((-2.5*log(flux_inst) + ZP) + 48.6)) + # flux_cgs = flux_inst * ten(-0.4*(ZP + 48.6)) + # flux_Jy = flux_cgs * 10^23 + # flux_Jy = flux_inst * ten(-0.4*(ZP + 48.6) + 23.0) [23.0 - 0.4*48.6 = 3.56] + # flux_Jy = flux_inst * ten(-0.4*ZP + 3.56) + # flux_Jy = flux_inst * ten(-0.4*ZP) * 3630.78 + + # modify flux to apply the zero point + sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") + for number in numbers: + field = filter + prefix + "flxR" + number + sqlLine.group(field, field + " * POW(10, 3.56 - 0.4* b.zp)") + sqlLine.group(field + "Err", field + "err" + " * POW(10, 3.56 - 0.4* b.zp)") + sqlLine.group(field + "Std", field + "Std" + " * POW(10, 3.56 - 0.4* b.zp)") + sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID") + self.scratchDb.execute(sql) + + ''' + Populates the StackToImage table + ''' + def populateStackToImage(self): + + self.logger.infoPair("Procesing table", "StackToImage") + + for filter in self.filters: + + stackmetaID = self.stackIDs[filter] + if stackmetaID > 0: + + imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaID) + + for imageID in imageIDs: + + sql = "INSERT INTO StackToImage (stackMetaID, imageID) \ + VALUES (\ + " + str(stackmetaID) + ", " + imageID + ")" + self.scratchDb.execute(sql) + + # now update StackMeta with correct number of inputs + sql = "UPDATE "+filter+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaID)+")" + self.scratchDb.execute(sql) + + self.tablesToExport.append("StackToImage") + + ### *********************** Utility Functions ************************************ + + ''' + Drop a table and report the drop + ''' + def dropTableVerbose(self, table): + self.logger.infoPair("dropping table:",table) + self.scratchDb.dropTable(table) + + ''' + get stackTypeID corresponding to our stackType + ''' + def getStackTypeID(self): + + sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'" rs = self.scratchDb.executeQuery(sql) - rs.first() - hasResult = rs.next() - - - - sql = "UPDATE StackModelFit AS a, " + f + "SkyChip_xfit AS b SET \ - "+f+model+"Radius=b.EXT_WIDTH_MAJ, \ - "+f+model+"Flux=POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime) + " , \ - "+f+model+"FluxErr=ABS(b.EXT_INST_MAG_SIG) * POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime) + " / 1.085736, \ - "+f+model+"Ab=b.EXT_WIDTH_MAJ/b.EXT_WIDTH_MIN, \ - "+f+model+"Phi=b.EXT_THETA \ - \ - WHERE a."+f+"ippDetectID=b.IPP_IDET AND b.MODEL_TYPE = '"+ippModelType+"'" - - print sql + if not rs.next(): + self.logger.errorPair("cannot get stack type ID from database for", self.stackType) + raise + + ID = rs.getInt(1) + return ID + + def deleteRowsWithNoStackData(self, tableName): + + self.logger.infoPair("Deleting rows with no stack data from", tableName) + sql = "DELETE FROM " + tableName + " WHERE " + + for i in range(len(self.filters)): + filter = self.filters[i] + sql += filter + "haveData is NULL" + + if (i < len(self.filters) - 1): + sql += " AND " + + self.logger.infoPair('sql',sql) self.scratchDb.execute(sql) - - - # sersic fit has an extra parameter - if (ippModelType == "PS_MODEL_SERSIC"): - extra_sql = "" - if hasResult: - extra_sql = ","+f+"serNuErr=SQRT(EXT_COVAR_07_07)" - sql = "UPDATE StackModelFit AS a, "+ f+ "SkyChip_xfit AS b SET \ - "+f+"serNu=b.EXT_PAR_07 \ - "+extra_sql+" \ - WHERE a."+f+"ippDetectID=b.IPP_IDET AND b.MODEL_TYPE = '"+ippModelType+"'" -# "+f+"serNuErr=SQRT(EXT_COVAR_07_07) \ - self.logger.infoPair("sql",sql) - self.scratchDb.execute(sql) - - - ''' - Populates the StackMeta table, mainly from dictionary values found in IPP FITS header - ''' - def populateStackMeta(self,filterName): - - self.logger.infoPair("Procesing table", "StackMeta") - - # this should work : set header into (grizy)header and run from there - if filterName == 'g.00000': - f = "g" - self.header = self.gheader - tablename = 'gStackMeta' - stackmetaid = self.gstackID - if filterName == 'r.00000': - f = "r" - tablename = 'rStackMeta' - self.header = self.rheader - stackmetaid = self.rstackID - if filterName == 'i.00000': - f = "i" - tablename = 'iStackMeta' - self.header = self.iheader - stackmetaid = self.istackID - if filterName == 'z.00000': - f = "z" - tablename = 'zStackMeta' - self.header = self.zheader - stackmetaid = self.zstackID - if filterName == 'y.00000': - f = "y" - tablename = 'yStackMeta' - self.header = self.yheader - stackmetaid = self.ystackID - - self.fwhm_maj = self.safeDictionaryAccess(self.header, 'FWHM_MAJ') - self.fwhm_maj_uq = self.safeDictionaryAccess(self.header, 'FW_MJ_UQ') - self.psfmodel = self.safeDictionaryAccess(self.header, 'PSFMODEL') - if (self.fwhm_maj == "NULL"): self.fwhm_maj = -999 - if (self.fwhm_maj_uq == "NULL"): self.fwhm_maj_uq = -999 - - # print "fwhm_maj = ", self.fwhm_maj - # print "fwhm_maj_uq = ", self.fwhm_maj_uq - # print "psfmodel = ", self.psfmodel - - #make a table - - filterid = self.scratchDb.getFilterID(f) - self.logger.infoPair("filterid is ",str(filterid)) - - sql = "CREATE TABLE " + tablename + " LIKE StackMeta" + for filter in self.filters: + column = filter + "haveData" + self.scratchDb.dropColumn(tableName, column) + + ''' + Applies indexes to the PSPS tables + ''' + def alterPspsTables(self): + + self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)") + + return True + + ''' + Applies indexes to the IPP tables + ''' + def indexIppTables(self): + + for filter in self.filters: + if self.stackIDs[filter] <= 0: + self.logger.infoPair("no files for filter" , filter) + continue + + self.logger.infoPair("Creating indexes on", "IPP tables") + self.scratchDb.createIndex(filter + "SkyChip_psf", "IPP_IDET") + self.scratchDb.createIndex(filter + "SkyChip_xfit", "IPP_IDET") + self.scratchDb.createIndex(filter + "SkyChip_xrad", "IPP_IDET") + self.scratchDb.createIndex(filter + "SkyChip_xsrc", "IPP_IDET") + + return True + + ''' + select objID (psps object ID) from dvo restricted to this set of imageIDs. also insert + ippObjID and several constants (for a skycell): surveyID, skyCellId, dataRelease + ''' + def selectDvoObjIDs(self): + + # we need a temporary column for each filter to identify empty rows + for filter in self.filters: + self.scratchDb.addColumn("StackObjectThin", filter + "haveData", "tinyint") + + sqlLine = sqlUtility("INSERT INTO StackObjectThin (") + + sqlLine.group("objID", "objID") + sqlLine.group("ippObjID", "ippObjID") + sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") + sqlLine.group("tessID", "'" + str(self.tessID) + "'") + sqlLine.group("projectionID", "'" + str(self.projectionID) + "'") + sqlLine.group("skyCellID", "'" + str(self.skycellID) + "'") + sqlLine.group("dataRelease", "'" + str(self.skychunk.dataRelease) + "'") + sqlLine.group("randomStackObjID", "FLOOR(RAND()*9223372036854775807)") + + whereClause = " WHERE (" + imageIDs = self.imageIDs.values() + for i in range(len(imageIDs)): + imageID = imageIDs[i] + self.logger.infoPair("selecting imageID", imageID) + + whereClause += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")" + + if (i < len(imageIDs) - 1): + whereClause += " or " + + whereClause += ")" + + sql = sqlLine.makeRaw(") SELECT DISTINCT ", "FROM " + self.scratchDb.dvoDetectionTable + whereClause) + try: self.scratchDb.execute(sql) - except: pass - - sql = "INSERT INTO " + tablename + " (\ - stackMetaID \ - ,filterID \ - ,skyCellID \ - ,photoCalID \ - ,photoZero \ - ,expTime \ - ,psfModelID \ - ,psfFwhm_mean \ - ,psfFwhm_max \ - ,ctype1 \ - ,ctype2 \ - ,crval1 \ - ,crval2 \ - ,crpix1 \ - ,crpix2 \ - ,cdelt1 \ - ,cdelt2 \ - ,pc001001 \ - ,pc001002 \ - ,pc002001 \ - ,pc002002 \ - ) VALUES ( \ - " + str(stackmetaid) + " \ - , " + str(filterid) + " \ - ," + self.skycell + " \ - ," + str(self.scratchDb.getPhotoCalID(self.header['IMAGEID'])) + " \ - ," + self.header['FPA.ZP'] + " \ - ," + self.header['EXPTIME'] + " \ - ,'" + self.psfmodel + "' \ - ," + str(self.fwhm_maj) + " \ - ," + str(self.fwhm_maj_uq) + " \ - ,'" + self.header['CTYPE1'] + "' \ - ,'" + self.header['CTYPE2'] + "' \ - ," + self.header['CRVAL1'] + " \ - ," + self.header['CRVAL2'] + " \ - ," + self.header['CRPIX1'] + " \ - ," + self.header['CRPIX2'] + " \ - ," + self.header['CDELT1'] + " \ - ," + self.header['CDELT2'] + " \ - ," + self.header['PC001001'] + " \ - ," + self.header['PC001002'] + " \ - ," + self.header['PC002001'] + " \ - ," + self.header['PC002002'] + " \ - )" - self.scratchDb.execute(sql) - - - self.scratchDb.updateAllRows(tablename, "batchID", str(self.batchID)) - self.scratchDb.updateAllRows(tablename, "surveyID", str(self.surveyID)) - # self.scratchDb.updateFilterID(tablename, self.scratchDb.getFilterID(filterName)) - self.scratchDb.updateAllRows(tablename, "dataRelease", str(self.skychunk.dataRelease)) - self.updateStackTypeID(tablename) - - ''' - Populates the StackObject table - ''' - def populateStackObject(self): - - self.logger.infoPair("Procesing table", "StackObject") - - self.logger.infoPair("inserting non-filter dependent items into", "StackObject") - - #this is dvo crap - #sql = "INSERT INTO StackObject(\ - # objID, \ - # gcobjID, \ - # ippObjID, \ - # surveyID, \ - # skyCellID, \ - # randomStackObjID, \ - # stackDetectRowID, \ - # dvoRegionID, \ - # dataRelease) SELECT \ - # -999, - # -999, - # -999, - # , - - - - - self.logger.infoPair("inserting filter dependent cmf items into", "StackObject") - filters =['g','r','i','z','y'] - for f in filters: - skip = 0 - if (f == "g"): - self.logger.infoPair("gstackID = ",self.gstackID) - if self.gstackID > 0: - stackid = self.gstackID - exptime = self.gheader['EXPTIME'] - else: - skip = 1 - if (f == "r"): - self.logger.infoPair("rstackID = ",self.rstackID) - if self.rstackID > 0: - stackid = self.rstackID - exptime = self.rheader['EXPTIME'] - else: - skip = 1 - if (f == "i"): - self.logger.infoPair("istackID = ",self.istackID) - if self.istackID > 0: - stackid = self.istackID - exptime = self.iheader['EXPTIME'] - else: - skip = 1 - if (f == "z"): - self.logger.infoPair("zstackID = ",self.zstackID) - if self.zstackID > 0: - stackid = self.zstackID - exptime = self.zheader['EXPTIME'] - else: - skip = 1 - if (f == "y"): - self.logger.infoPair("ystackID = ",self.ystackID) - if self.ystackID > 0: - stackid = self.ystackID - exptime = self.yheader['EXPTIME'] - else: - skip = 1 - - if skip == 1: - self.logger.infoPair("nothing for filter" , f) - else: - self.logger.infoPair("filter",f) - - - - # insert all the detections - sql = "UPDATE StackObject as a , " + f + "SkyChip_psf as b set \ - a."+f+"xPos = b.X_PSF \ - ,a."+f+"yPos = b.Y_PSF \ - ,a."+f+"xPosErr = b.X_PSF_SIG \ - ,a."+f+"yPosErr = b.Y_PSF_SIG \ - ,a."+f+"PSFFlux = b.PSF_INST_FLUX / " + str(exptime) + " \ - ,a."+f+"PSFFluxErr = b.PSF_INST_FLUX_SIG / " + str(exptime) + " \ - ,a."+f+"sky = b.SKY / " + str(exptime) + " \ - ,a."+f+"skyErr = b.SKY_SIGMA / " + str(exptime) + " \ - ,a."+f+"psfMajorFWHM = b.PSF_MAJOR \ - ,a."+f+"psfMinorFWHM = b.PSF_MINOR \ - ,a."+f+"psfTheta = b.PSF_THETA \ - ,a."+f+"psfCore = NULL \ - ,a."+f+"psfLikelihood = psfLikelihood(b.EXT_NSIGMA) \ - ,a."+f+"infoFlag = b.FLAGS2 << 32 | FLAGS \ - ,a."+f+"psfQf = b.PSF_QF \ - ,a."+f+"psfQfPerfect = b.PSF_QF_PERFECT \ - ,a."+f+"psfChiSq = b.PSF_CHISQ / b.PSF_NDOF \ - ,a."+f+"momentXX = b.MOMENTS_XX \ - ,a."+f+"momentXY = b.MOMENTS_XY \ - ,a."+f+"momentYY = b.MOMENTS_YY \ - ,a."+f+"momentR1 = b.MOMENTS_R1 \ - ,a."+f+"momentRH = b.MOMENTS_RH \ - ,a."+f+"ApFlux = POW(10.0, -0.4*b.AP_MAG) / " + str(exptime) + " \ - ,a."+f+"ApFluxErr = NULL \ - ,a."+f+"ApFillFac = NULL \ - ,a."+f+"KronFlux = b.KRON_FLUX / " + str(exptime) + " \ - ,a."+f+"KronFluxErr = b.KRON_FLUX_ERR / " + str(exptime) + " \ - ,a."+f+"KronRad = b.MOMENTS_R1 * 2.5 \ - ,a."+f+"PSFMag = b.PSF_INST_MAG \ - ,a."+f+"PSFMagErr = b.PSF_INST_MAG_SIG \ - ,a."+f+"ApMag = b.AP_MAG \ - ,a."+f+"ApMagErr = NULL \ - ,a."+f+"KronMag = NULL \ - ,a."+f+"KronMagErr = NULL \ - ,a."+f+"nFrames = b. N_FRAMES \ - WHERE a." + f + "ippDetectID=b.IPP_IDET " - self.logger.infoPair('sql', sql) - try: - self.scratchDb.execute(sql) - except: - self.logger.errorPair('failed sql',sql) - return - #it is possible to drop some detections from dvo (that are present in the cmf). when that happens we get a 0 for objid - #we drop those... - - self.logger.infoPair("Deleting", "entries with StackObject.objID = 0") - sql="DELETE FROM StackObject where objID = 0" - try: - self.scratchDb.execute(sql) except: self.logger.errorPair('failed sql',sql) return + + ''' + Insert objid (from dvo) into table from main table + ''' + def insertDvoIDs(self, table, mainTable): + + # we need a temporary column for each filter to identify empty rows + for filter in self.filters: + self.scratchDb.addColumn(table, filter + "haveData", "tinyint") + + fields = "objID, ippObjID, randomStackObjID, primaryDetection, bestDetection, " + + for i in range(len(self.filters)): + filter = self.filters[i] + fields += filter + "ippDetectID, " + fields += filter + "stackDetectID, " + fields += filter + "stackMetaID" # careful here: do not add comma to last entry + if (i < len(self.filters) - 1): + fields += ", " - try: - self.scratchDb.updateAllRows("StackObject", "surveyID", str(self.surveyID)) + sql = "INSERT INTO " + table + " (" + fields + ") " + sql += "SELECT " + fields + " FROM " + mainTable + + try: self.scratchDb.execute(sql) except: - self.logger.errorPair('failed update surveyID on','StackObject') - raise -# self.scratchDb.updateFilterID("StackObject", f+".00000") - - self.scratchDb.updateAllRows("StackObject", "dataRelease", str(self.skychunk.dataRelease)) - try: - self.updateStackMetaID("StackObject") - except: - self.logger.errorPair("failed update stack meta id on ", "StackObject") - raise - ##self.updateStackTypeID("StackObject") - - #self.updateDvoIDsAndFlags("StackObject") - #this now updates primary F as well - # response = raw_input("updated dvo ") - - # XXX EAM : note that in mysql versions later than 5.1, this fails - # unless the following is called first: - # set session old_alter_table=1 - # follow the command with - # set session old_alter_table=0 - # OF COURSE, this fails for mysql version < 5.5... - if self.scratchDb.version > 5.1: - self.scratchDb.execute("set session old_alter_table=1") - - sql = "ALTER IGNORE TABLE StackObject ADD PRIMARY KEY (objID)" - - if self.scratchDb.version > 5.1: - self.scratchDb.execute("set session old_alter_table=0") - - self.scratchDb.execute(sql) - # response = raw_input("add primary key? ") - - ### XXX this should not be a DEEP_STACK only case... - if False and self.stackType == "DEEP_STACK": - ## need to do for all filters? - #if deep stack and instFlux = null and err not null - sql = "UPDATE StackObject AS a, SkyChip_psf AS b \ - SET psfFlux = 2*b.PSF_INST_FLUX_SIG / " + str(self.expTime) + " \ - WHERE psfFlux IS NULL \ - AND a.ippDetectID = b.IPP_IDET \ - AND b.PSF_INST_FLUX_SIG IS NOT NULL" - - self.scratchDb.execute(sql) - # response = raw_input("add psf flux ") - - #filter dependent - # NOTE : Flux limits : in the current PSPS schema, negative fluxes - # cause problems for sql queries which work in mags as SQL cannot do - # something like (f < 0.0) ? -999 : -2.5*log10(f) - # as a result, the negative fluxes here result in floating point errors - sql = "UPDATE StackObject SET gPSFFlux = 1e20 WHERE gPSFFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET gApFlux = 1e20 WHERE gApFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET gKronFlux = 1e20 WHERE gKronFlux <= 0.0" - self.scratchDb.execute(sql) - sql = "UPDATE StackObject SET rPSFFlux = 1e20 WHERE rPSFFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET rApFlux = 1e20 WHERE rApFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET rKronFlux = 1e20 WHERE rKronFlux <= 0.0" - self.scratchDb.execute(sql) - sql = "UPDATE StackObject SET iPSFFlux = 1e20 WHERE iPSFFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET iApFlux = 1e20 WHERE iApFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET iKronFlux = 1e20 WHERE iKronFlux <= 0.0" - self.scratchDb.execute(sql) - sql = "UPDATE StackObject SET zPSFFlux = 1e20 WHERE zPSFFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET zApFlux = 1e20 WHERE zApFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET zKronFlux = 1e20 WHERE zKronFlux <= 0.0" - self.scratchDb.execute(sql) - sql = "UPDATE StackObject SET yPSFFlux = 1e20 WHERE yPSFFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET yApFlux = 1e20 WHERE yApFlux <= 0.0" - self.scratchDb.execute(sql) - - sql = "UPDATE StackObject SET yKronFlux = 1e20 WHERE yKronFlux <= 0.0" - self.scratchDb.execute(sql) - - #leave null instflux in - #self.scratchDb.reportAndDeleteRowsWithNULLS("StackObject", "instFlux") - - self.scratchDb.reportAndDeleteRowsWithNULLS("StackObject", "objID") - # response = raw_input("delete nulls ") - - sql="DELETE FROM StackObject where objID = 0" - - self.scratchDb.execute(sql) - self.logger.infoPair("Deleting", "entries with StackObject.objID = 0") - # response = raw_input("deleted objID is 0 ") - ''' - Populates the SkinnyObject table - ''' - def populateSkinnyObject(self): - - self.logger.infoPair("Procesing table", "SkinnyObject") - - sql = "INSERT INTO SkinnyObject (\ - objID \ - ,ippObjID \ - ,projectionCellID \ - ) \ - SELECT \ - DISTINCT objID \ - ,ippObjID \ - ,projectionCellID \ - FROM StackDetection" - self.scratchDb.execute(sql) - - self.scratchDb.updateAllRows("SkinnyObject", "surveyID", str(self.surveyID)) - self.scratchDb.updateAllRows("SkinnyObject", "dataRelease", str(self.skychunk.dataRelease)) - - - ''' - Populates the StackApFlx table - ''' - def populateStackApFlx(self): - filter = ['g','r','i','z','y'] - - for f in filter: - skip = 0 - if (f == 'g'): - if self.gstackID > 0: - self.header = self.gheader - else: - skip = 1 - if (f == 'r'): - if self.rstackID > 0: - self.header = self.rheader - else: - skip = 1 - if (f == 'i'): - if self.istackID > 0: - self.header = self.iheader - else: - skip = 1 - if (f == 'z'): - if self.zstackID > 0: - self.header = self.zheader - else: - skip = 1 - if (f == 'y'): - if self.ystackID > 0: - self.header = self.yheader - else: - skip =1 - - if skip == 0: - self.logger.infoPair("Adding fluxes", "un-convolved") - self.updateModelFitFlxs("", "((b.table_index -1) % 3 = 0)", "StackApFlx", f) - self.logger.infoPair("Adding fluxes", "1st convolved") - self.updateModelFitFlxs("c1", "((b.table_index -1) % 3 = 1)","StackApFlx",f) - self.logger.infoPair("Adding fluxes", "2nd convolved") - self.updateModelFitFlxs("c2", "((b.table_index -1) % 3 = 2)","StackApFlx",f) - - self.deleteDetectionsNotInStackObject("StackApFlx",f) - - self.scratchDb.reportAndDeleteRowsWithNULLS("StackApFlx", "objID") - - - - ''' - Populates the StackModelFit table - ''' - def populateStackModelFit(self, filter): - # for each filter - f =filter - if (filter == 'g'): - self.header = self.gheader - - if (filter == 'r'): - self.header = self.rheader - if (filter == 'i'): - self.header = self.iheader - if (filter == 'z'): - self.header = self.zheader - if (filter == 'y'): - self.header = self.yheader - - - - - - self.logger.infoPair("getting","imageID") - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - self.logger.infoPair("Procesing table", "StackModelFit") - - - # insert all the detections - - # populate model parameters - self.logger.infoPair("Adding model fit", "deVaucouleurs") - self.updateModelFit("deV", "PS_MODEL_DEV", filter, self.header['EXPTIME']) - self.logger.infoPair("Adding model fit", "exponential") - self.updateModelFit("deV", "PS_MODEL_EXP", filter, self.header['EXPTIME']) - self.logger.infoPair("Adding model fit", "sersic") - self.updateModelFit("deV", "PS_MODEL_SERSIC", filter, self.header['EXPTIME']) - - - self.logger.infoPair("Adding fluxes", "un-convolved") - self.updateModelFitFlxs("", "((b.table_index -1) % 3 = 0)","StackModelFit",f) - self.logger.infoPair("Adding fluxes", "1st convolved") - self.updateModelFitFlxs("c1", "((b.table_index -1) % 3 = 1)","StackModelFit",f) - self.logger.infoPair("Adding fluxes", "2nd convolved") - self.updateModelFitFlxs("c2", "((b.table_index -1) % 3 = 2)","StackModelFit",f) - - - self.logger.infoPair("Adding", "petrosians for extended sources") - sql = "UPDATE StackModelFit as a, "+f+"SkyChip_xsrc AS b SET \ - "+f+"petRadius=b.PETRO_RADIUS \ - ,"+f+"petRadiusErr=b.PETRO_RADIUS_ERR \ - ,"+f+"petFlux=POW(10.0, -0.4 * b.PETRO_MAG) /" + str(self.header['EXPTIME']) + " \ - ,"+f+"petFluxErr=-999 \ - ,"+f+"petR50=b.PETRO_RADIUS_50 \ - ,"+f+"petR50Err=b.PETRO_RADIUS_50_ERR \ - ,"+f+"petR90=b.PETRO_RADIUS_90 \ - ,"+f+"petR90Err=b.PETRO_RADIUS_90_ERR \ - ,"+f+"petCf=b.PETRO_FILL \ - WHERE a."+f+"ippDetectID=b.IPP_IDET" - self.scratchDb.execute(sql) - - self.logger.infoPair("Adding", "petFluxErr for non nulls") - sql = "UPDATE StackModelFit as a, "+f+"SkyChip_xsrc AS b SET \ - "+f+"petFluxErr=ABS(b.PETRO_MAG_ERR) * POW(10.0, (-0.4*b.PETRO_MAG)) / " + str(self.header['EXPTIME']) + " / 1.085736 \ - WHERE a."+f+"ippDetectID=b.IPP_IDET \ - AND (b.PETRO_MAG_ERR is not NULL AND b.PETRO_MAG is not NULL AND PETRO_MAG_ERR < 1000.0)" - # added this because when the petromagerr is crazy large (10^29 it out of ranges the flux err calc) - self.logger.infoPair("sql", sql) - self.scratchDb.execute(sql) - - - - self.scratchDb.updateAllRows("StackModelFit", "surveyID", str(self.surveyID)) - self.scratchDb.updateAllRows("StackModelFit", "dataRelease", str(self.skychunk.dataRelease)) - - self.logger.infoPair("not adding"," PrimaryF to stackApFlx") - sql = "UPDATE StackModelFit AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ - a.primaryF = ((b.flags & 0x10000) >> 16 ) \ - WHERE a.ippDetectID = b.ippDetectID \ - AND b.imageID = " + str(imageID) - -# try: -# self.scratchDb.execute(sql) -# except: -# return - -## self.scratchDb.updateAllRows("StackModelFit", "activeFlag", "0") - ##self.updateStackMetaID("StackModelFit") -## self.updateStackTypeID("StackModelFit") - - self.scratchDb.reportAndDeleteRowsWithNULLS("StackModelFit", "objID") - self.deleteDetectionsNotInStackObject("StackModelFit", f) - - ''' - Reports and deletes detections in this table that are not in StackObject - ''' - def deleteDetectionsNotInStackObject(self, table, filter): - f= filter - - sql = "SELECT COUNT(*) FROM " + table + " WHERE "+f+"ippDetectID NOT IN (SELECT "+f+"ippDetectID FROM StackObject)" - rs = self.scratchDb.executeQuery(sql) - rs.first() - nMissing = rs.getInt(1) - - if nMissing < 1: return - - sql = "DELETE FROM " + table + " WHERE "+f+"ippDetectID NOT IN (SELECT "+f+"ippDetectID FROM StackObject)" - self.scratchDb.execute(sql) - self.logger.infoPair("Detections not in StackObject", "%d deleted from %s" % (nMissing, table)) - - - ''' - Populates the StackToImage table - ''' - def populateStackToImage(self): - - self.logger.infoPair("Procesing table", "StackToImage") - filters = ['g','r','i','z','y'] - for f in filters: - - if f == 'g': - stackmetaid = self.gstackID - if f == 'r': - stackmetaid = self.rstackID - if f == 'i': - stackmetaid = self.istackID - if f == 'z': - stackmetaid = self.zstackID - if f == 'y': - stackmetaid = self.ystackID - if stackmetaid > 0: - - imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaid) - - for imageID in imageIDs: - sql = "INSERT INTO StackToImage (stackMetaID, imageID) \ - VALUES (\ - " + str(stackmetaid) + ", " + imageID + ")" - self.scratchDb.execute(sql) - - # now update StackMeta with correct number of inputs - sql = "UPDATE "+f+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaid)+")" - self.scratchDb.execute(sql) - - ''' - Generates the randomStackIds - ''' - def populateRandomsAndSkyCellID(self): - - self.logger.infoPair("Generating Randoms","for StackObject") - sql = "update StackObject set randomStackObjID = FLOOR(RAND()*9223372036854775807) \ - ,skyCellID = " + str(self.skycell)+ " \ - ,surveyID = " + str(self.surveyID) - try: - self.scratchDb.execute(sql) - except: - self.logger.infoPair("failed sql", sql) - - ''' - copies randomStackIds from StackObject to tableName - ''' - def copyRandomsAndSkyCellIDFromStackObject(self, tableName): - - self.logger.infoPair("copying Randoms from StackObject to ", tableName) - sql = "update " + tableName + " join StackObject using (objID) \ - set " + tableName + ".randomStackObjID = StackObject.randomStackObjID \ - , " + tableName + ".skyCellID = StackObject.skyCellID \ - , " + tableName + ".surveyID = StackObject.surveyID" - self.logger.infoPair("sql", sql) - try: - self.scratchDb.execute(sql) - except: - self.logger.infoPair("failed sql", sql) - - - - def populateStackIPPDetectIDFromDvo(self, tableName, all): - - #tableName = "StackObject" - self.logger.infoPair("Processing table", tableName) - - filters = ['g','r','i','z','y'] - - for f in filters: - skip = 0 - #first: do we skip this filter - if f == 'g': - if self.gstackID < 0: - skip = 1 - else: - self.header=self.gheader - stackmetaid = self.gstackID - elif f == 'r': - if self.rstackID < 0: - skip = 1 - else: - self.header = self.rheader - stackmetaid = self.rstackID - elif f == 'i': - if self.istackID < 0: - skip = 1 - else: - self.header = self.iheader - stackmetaid = self.istackID - elif f == 'z': - if self.zstackID < 0: - skip = 1 - else: - self.header = self.zheader - stackmetaid = self.zstackID - elif f == 'y': - if self.ystackID < 0: - skip = 1 - else: - self.header = self.yheader - stackmetaid = self.ystackID - if skip == 0: - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - addsql = "" - if (all == 1): - addsql = "a." + f + "ra = b.ra, \ - a." + f + "dec = b.dec_, \ - a." + f + "raErr = b.raErr, \ - a." + f + "decErr = b.decErr, \ - a." + f + "zp = b.zp, \ - a." + f + "expTime = b.expTime," - - - sql = "UPDATE " + tableName + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b \ - SET a." + f + "ippDetectID = b.ippDetectID, \ - " + addsql + " \ - a." + f + "stackDetectID = b.detectID, \ - a." + f + "ippObjID = b.ippObjID, \ - a." + f + "stackMetaID = " + str(stackmetaid) + " \ - WHERE a.objID = b.objID AND b.imageID = " + str(imageID) - self.logger.infoPair('sql',sql) - self.scratchDb.execute(sql) - else: - self.logger.infoPair("skipping ingest of dvo for filter",f) - #out of the loops - give dataRelase to all - self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - - - def deleteNULLippDetectIDs(self, tableName): - - self.logger.infoPair("Deleting NULL ippdetectIDs from table", tableName) - sql = "DELETE FROM " + tableName + " WHERE \ - gippDetectID is NULL AND \ - rippDetectID is NULL AND \ - iippDetectID is NULL AND \ - zippDetectID is NULL AND \ - yippDetectID is NULL " - self.logger.infoPair('sql',sql) - self.scratchDb.execute(sql) - - ''' - Applies indexes to the PSPS tables - ''' - def alterPspsTables(self): - - self.logger.debug("Altering PSPS tables") - # heather uncommented the unique line -- well that no worky - #self.scratchDb.makeColumnUnique("StackObject", "objID") - self.scratchDb.createIndex("StackObject", "ippDetectID") - self.scratchDb.createIndex("StackObject", "stackDetectID") - self.scratchDb.createIndex("StackApFlx", "ippDetectID") - self.scratchDb.createIndex("StackModelFit", "ippDetectID") - - return True - - ''' - Applies indexes to the IPP tables - ''' - def indexIppTables(self): - - self.logger.infoPair("Creating indexes on", "IPP tables") - self.scratchDb.createIndex("SkyChip_psf", "IPP_IDET") - self.scratchDb.createIndex("SkyChip_xfit", "IPP_IDET") - self.scratchDb.createIndex("SkyChip_xrad", "IPP_IDET") - self.scratchDb.createIndex("SkyChip_xsrc", "IPP_IDET") - - return True - ''' - Insert objid (from dvo) into table - ''' - def insertDvoIDs(self, table, filterName): - self.logger.infoPair("getting imageID for ","filter") - if (filterName == 'g.00000'): - self.header=self.gheader - if (filterName == 'r.00000'): - self.header=self.rheader - if (filterName == 'i.00000'): - self.header=self.iheader - if (filterName == 'z.00000'): - self.header=self.zheader - if (filterName == 'y.00000'): - self.header=self.yheader - - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - self.logger.debug("Updating table '" + table + "' with DVO IDs...") - sql = "INSERT IGNORE INTO " + table + " ( \ - objID ) \ - SELECT (objID) FROM " + self.scratchDb.dvoDetectionTable + " \ - WHERE " + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) - self.logger.infoPair("sql",sql) - self.scratchDb.execute(sql) - - - - ''' - Updates provided table with DVO IDs from DVO table - ''' - def updateDvoIDs(self, table, filterName): - self.logger.infoPair("getting imageID for ","filter") - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - self.logger.debug("Updating table '" + table + "' with DVO IDs...") - sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ - a.ippObjID = b.ippObjID, \ - a.stackDetectID = b.detectID, \ - a.objID = b.objID \ - WHERE a.ippDetectID = b.ippDetectID \ - AND b.imageID = " + str(imageID) - self.scratchDb.execute(sql) - - ''' - Updates Flags - ''' - def updateDvoIDsAndFlags(self, table): - self.logger.infoPair("getting","imageID") - imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) - self.logger.infoPair("obtained","imageID") - self.logger.debug("Updating table '" + table + "' with DVO IDs...") - sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ - a.ippObjID = b.ippObjID, \ - a.stackDetectID = b.detectID, \ - a.objID = b.objID, \ - a.infoFlag = b.flags << 45 | a.infoFlag, \ - a.primaryF = ((b.flags & 0x10000) >> 16 ) \ - WHERE a.ippDetectID = b.ippDetectID \ - AND b.imageID = " + str(imageID) - - print "update dvo sql: ", sql - self.scratchDb.execute(sql) - + self.logger.errorPair('failed sql',sql) + return ''' @@ -1210,134 +1006,68 @@ self.logger.infoPair("starting","populatePspsTables"); self.skipBatch = False - if not self.useFullTables: - if not self.getIDsFromDVO(): - return False - #do some basics first - # populate stack meta per filter - # insert objid/stackdetectid per filter to each table + + # each of the "populate*" methods below add their tables to the list: self.tablesToExport=[] - if self.gstackID > 0: - self.logger.infoPair("populating","gStackMeta") - self.populateStackMeta("g.00000") - self.insertDvoIDs("StackObject","g.00000") - self.insertDvoIDs("StackModelFit","g.00000") - self.insertDvoIDs("StackApFlx","g.00000") - self.tablesToExport.append("gStackMeta") - if self.rstackID > 0: - self.logger.infoPair("populating","rStackMeta") - self.populateStackMeta("r.00000") - self.insertDvoIDs("StackObject","r.00000") - self.insertDvoIDs("StackModelFit","r.00000") - self.insertDvoIDs("StackApFlx","r.00000") - self.tablesToExport.append("rStackMeta") - if self.istackID > 0: - self.logger.infoPair("populating","iStackMeta") - self.populateStackMeta("i.00000") - self.insertDvoIDs("StackObject","i.00000") - self.insertDvoIDs("StackModelFit","i.00000") - self.insertDvoIDs("StackApFlx","i.00000") - self.tablesToExport.append("iStackMeta") - if self.zstackID > 0: - self.logger.infoPair("populating","zStackMeta") - self.populateStackMeta("z.00000") - self.insertDvoIDs("StackObject","z.00000") - self.insertDvoIDs("StackModelFit","z.00000") - self.insertDvoIDs("StackApFlx","z.00000") - self.tablesToExport.append("zStackMeta") - if self.ystackID > 0: - self.logger.infoPair("populating","yStackMeta") - self.populateStackMeta("y.00000") - self.insertDvoIDs("StackObject","y.00000") - self.insertDvoIDs("StackModelFit","y.00000") - self.insertDvoIDs("StackApFlx","y.00000") - self.tablesToExport.append("yStackMeta") - - self.logger.infoPair("populating ","StackObject") - # need to index on objid and ippdetectid or it is stupidly slow - self.scratchDb.createIndex("StackObject", "objID") - #populate from dvo the (f)ippDetectID into StackObject - self.populateStackIPPDetectIDFromDvo("StackObject",1) - #index the ippDetectids for the next step - self.scratchDb.createIndex("StackObject", "gippDetectID") - self.scratchDb.createIndex("StackObject", "rippDetectID") - self.scratchDb.createIndex("StackObject", "iippDetectID") - self.scratchDb.createIndex("StackObject", "zippDetectID") - self.scratchDb.createIndex("StackObject", "yippDetectID") - self.populateStackObject() - self.populateRandomsAndSkyCellID() - # stackObject should be done, pending a few NULL columns (need help) + # EAM 20140730 : I have revised the StackObjectXX construction + # process. The challenge here is that we have dvo-generated + # objIDs and ippObjIDs for each stack detection in seperate + # rows, but we want to merge the 5 filter sets into a single + # set of rows. The old code was relying on INSERT IGNORE, + # making it hard to catch errors, and probably not doing the + # insert very efficiently + + # Now we do a first pass with SELECT DISTINCT to get the + # complete set of objIDs which are associated with detections + # from one of the relevant images (ie, measure.imageID matches + # the set of image IDs). + + # once we have determined the complete set of desired objects, + # then we can use an update to set the per-field fields with + # data selected for each filter. Once we have constructed + # this basic common information in StackObjectThin, then we + # can uses this table to seed the same rows in the other Stack + # tables + + for filter in self.filters: + if self.stackIDs[filter] > 0: + self.populateStackMeta(filter) + + self.populateStackObjectThin() + + self.populateStackObjectRaw() if self.stackType != "NIGHTLY_STACK": + self.logger.infoPair("populating ","StackModelFitExtra") + self.populateStackModelFitExtra() + self.logger.infoPair("populating ","StackModelFit") - - self.scratchDb.createIndex("StackModelFit", "objID") - #populate from dvo the (f)ippdetectid into stackModelFit - self.populateStackIPPDetectIDFromDvo("StackModelFit",0) - #index the ippDetectIds for the next step - self.scratchDb.createIndex("StackModelFit", "gippDetectID") - self.scratchDb.createIndex("StackModelFit", "rippDetectID") - self.scratchDb.createIndex("StackModelFit", "iippDetectID") - self.scratchDb.createIndex("StackModelFit", "zippDetectID") - self.scratchDb.createIndex("StackModelFit", "yippDetectID") - #cull the entries that should not be (how?) - self.deleteNULLippDetectIDs("StackModelFit") - #populate stackModelFit - if self.gstackID > 0: - self.logger.infoPair("populating g filter for stackModelFit, stackmetaid =", self.gstackID) - self.populateStackModelFit("g") - if self.rstackID > 0: - self.logger.infoPair("populating r filter for stackModelFit, stackmetaid =", self.rstackID) - self.populateStackModelFit("r") - if self.istackID > 0: - self.logger.infoPair("populating i filter for stackModelFit, stackmetaid =", self.istackID) - self.populateStackModelFit("i") - if self.zstackID > 0: - self.logger.infoPair("populating z filter for stackModelFit, stackmetaid =", self.zstackID) - self.populateStackModelFit("z") - if self.ystackID > 0: - self.logger.infoPair("populating y filter for stackModelFit, stackmetaid =", self.ystackID) - self.populateStackModelFit("y") - - self.copyRandomsAndSkyCellIDFromStackObject("StackModelFit") - - self.logger.infoPair("populating ","StackApFlx") - - self.scratchDb.createIndex("StackApFlx", "objID") - #populate from dvo the (f)ippDetectID into StackApFlx - self.populateStackIPPDetectIDFromDvo("StackApFlx",0) - #index the ippDetectIDs for the next step - self.scratchDb.createIndex("StackApFlx", "gippDetectID") - self.scratchDb.createIndex("StackApFlx", "rippDetectID") - self.scratchDb.createIndex("StackApFlx", "iippDetectID") - self.scratchDb.createIndex("StackApFlx", "zippDetectID") - self.scratchDb.createIndex("StackApFlx", "yippDetectID") - #populate StackApFlx - self.deleteNULLippDetectIDs("StackApFlx") + self.populateStackModelFit("Exp", "PS_MODEL_EXP") + self.populateStackModelFit("DeV", "PS_MODEL_DEV") + self.populateStackModelFit("Ser", "PS_MODEL_SERSIC") + + self.populateStackPetrosian() + + self.logger.infoPair("populating ","StackApFlx Tables") self.populateStackApFlx() - self.copyRandomsAndSkyCellIDFromStackObject("StackApFlx") - #cull the entries that should not be (how?) - - + + self.populateStackApFlxFull("") + self.populateStackApFlxFull("C1") + self.populateStackApFlxFull("C2") + + # delete rows with no content. for the other tables, this is done in + # the 'populate' functions, but StackObjectThin needs to retain all rows + # until those tables are done + self.deleteRowsWithNoStackData("StackObjectThin") + self.logger.infoPair("populating","StackToImage") self.populateStackToImage() - self.setMinMaxObjID(["StackObject"]) - # self.updateStackTypeID("StackModelFit") - - ##if self.totalDetections < 1: - #self.logger.infoPair("populating","SkinnyObject") - self.populateSkinnyObject() - - ##self.logger.error("No detections to publish") - ##return False - self.tablesToExport.append("StackToImage") - self.tablesToExport.append("StackObject") - self.tablesToExport.append("StackModelFit") - self.tablesToExport.append("StackApFlx") - self.tablesToExport.append("SkinnyObject") + self.setMinMaxObjID(["StackObjectThin"]) + self.logger.infoPair("finishing","populatePspsTables"); return True + ''' overidding this method - use regex to trimoff letter before stack @@ -1345,2 +1075,59 @@ def exportPspsTablesToFits(self, regex="(.*)"): return super(StackBatch, self).exportPspsTablesToFits("(Stack.*)") + + ''' + Imports IPP tables from FITS file + Accepts a regular expression to match the tables so not all tables need to be imported + (This regex feature is not currently used...) + ''' + def importIppTables(self, columns="*", tableRE=""): + + if self.config.retry: return True + + self.logger.infoPair("Importing ST tables with table match expression: ", tableRE) + + count = 0 + for filter in self.filters: + if self.stackIDs[filter] <= 0: + self.logger.infoPair("no files for filter" , filter) + continue + + fileName = self.fits[filter].getPath() + + self.logger.infoPair("using filename:",fileName) + + try: + tables = stilts.treads(fileName) + except: + self.logger.errorPair("STILTS could not import from", fileName) + return False + + # count = 0 + for table in tables: + + match = re.match(tableRE, table.name) + if not match: continue + + self.logger.infoPair("Reading IPP table", filter + table.name) + table = stilts.tpipe(table, cmd='addcol table_index $0') + table = stilts.tpipe(table, cmd='explodeall') + + # drop any previous tables before import + self.scratchDb.dropTable(filter + table.name) + + # IPP FITS files are littered with infinities, so remove these + self.logger.info("Removing Infinity values from all columns") + table = stilts.tpipe(table, cmd='keepcols "' + columns + '"') + table = stilts.tpipe(table, cmd='replaceval -Infinity null *') + table = stilts.tpipe(table, cmd='replaceval Infinity null *') + + try: + table.write(self.scratchDb.url + '#' + filter + table.name) + count = count + 1 + except: + self.logger.exception("Problem writing table '" + filter + table.name + "' to the database") + + self.logger.infoPair("Done. Imported", "%d tables" % count) + self.indexIppTables() + + return True