Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/config.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/config.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/config.py (revision 37156) @@ -22,17 +22,35 @@ self.test = False + self.resetDvo = False + self.gpc1test = True + + if sys.argv.count("-test"): + self.test = True + sys.argv.remove("-test") + + if sys.argv.count("-t"): + self.test = True + sys.argv.remove("-t") + + # by default, we use gpc1test in a test, and do not if not a test + self.gpc1test = self.test + + if sys.argv.count("-realgpc"): + if not self.test: + print "-realgpc requires -test" + sys.exit(1) + self.gpc1test = False + sys.argv.remove("-realgpc") + + if sys.argv.count("-reset-dvo"): + self.resetDvo = True + sys.argv.remove("-reset-dvo") + + print "test: " + str(self.test) + print "gpc1test: " + str(self.gpc1test) + print "resetDvo: " + str(self.resetDvo) + for arg in sys.argv: - if arg == "-test": - self.test = True - sys.argv.remove(arg) - if arg == "-t": - self.test = True - sys.argv.remove(arg) - - self.gpc1test = self.test - for arg in sys.argv: - if arg == "-realgpc": - self.gpc1test = False - sys.argv.remove(arg) + print "arg: " + arg ## name of the top-level jython script Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/dvo.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/dvo.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/dvo.py (revision 37156) @@ -680,11 +680,20 @@ if (useP2 ==0 and useST ==1): - #grab only stacks - cmd += " -photcode-start 11000 -photcode-end 11500" + # grab only stacks + if self.config.gpc1test: + # gpc1test -> SIMTEST.x.SkyChip + cmd += " -photcode-start 14100 -photcode-end 14500" + else: + # not gpc1test -> GPC1.x.SkyShip + cmd += " -photcode-start 11000 -photcode-end 11500" if (useP2 ==1 and useST==0): #grab only P2s (4000-4500 are test cameras, SIMTEST and SIMMOSAIC) - # XXX EAM 20140729 : this is a terrible hack... - cmd += " -photcode-start 4000 -photcode-end 10577" + if self.config.gpc1test: + # gpc1test -> SIMTEST.x.Chip + cmd += " -photcode-start 4100 -photcode-end 4500" + else: + # not gpc1test -> GPC1.x.XYnn + cmd += " -photcode-start 10000 -photcode-end 10577" self.logger.infoPair("Running dvopsps", cmd) Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/gpc1db.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/gpc1db.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/gpc1db.py (revision 37156) @@ -123,11 +123,14 @@ # XXX EAM : test output rs = self.executeQuery(sql) - while (rs.next()): - rows.append([rs.getInt(1), rs.getFloat(2), rs.getFloat(3)]) - rs.close() + except: self.logger.exception("Can't query for ids in DVO") self.logger.infoPair("failed sql:", sql) - return rows + sys.exit(1) + + while (rs.next()): + rows.append([rs.getInt(1), rs.getFloat(2), rs.getFloat(3)]) + + rs.close() self.logger.debug("Found %d items in DVO database '%s' for stage='%s'" % (len(rows), dvoDb, stage)) Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/ippjython =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/ippjython (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/ippjython (revision 37156) @@ -15,6 +15,6 @@ # XXX if you want to run a local test, use . for JYDIR +set JYDIR = $datadir/jython # set JYDIR = . -set JYDIR = $datadir/jython setenv IPPTOPSPS_DATA $datadir/ipptopsps Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/loader.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/loader.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/loader.py (revision 37156) @@ -162,5 +162,5 @@ if batchType != "OB": - if not self.ippToPspsDb.isBoxIngested(boxId, self.scratchDb.dbHost): + if self.config.resetDvo or not self.ippToPspsDb.isBoxIngested(boxId, self.scratchDb.dbHost): if not self.dvoDetections.nativeIngestDetections(boxId, boxDim): self.logger.error("Unable to ingest detections with nativeIngest") @@ -206,5 +206,5 @@ self.ippToPspsDb.unlockTables() #self.logger.infoPair("heather:","unlock table") - self.ippToPspsDb.deletePendingItem(batchType, id) + # self.ippToPspsDb.deletePendingItem(batchType, id) #self.logger.infoPair("heather:","deleted pending item") @@ -288,4 +288,5 @@ self.logger.error("EXCEPTION: " + str(e)) self.logger.errorPair("Problem with this %s batch (%d)" % (batchType, id), "skipping") + sys.exit(1) pass Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/mysql.py (revision 37156) @@ -83,4 +83,5 @@ ''' def lockTable(self, table): + print "Locking table: " + table self.execute("LOCK TABLES " + table + " WRITE") @@ -89,4 +90,5 @@ ''' def unlockTables(self): + print "Unlocking tables" self.execute("UNLOCK TABLES") @@ -280,4 +282,23 @@ except: pass #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'") + + def addPrimaryKeyWithIgnore(self, table, column) + # 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.1... + if self.scratchDb.version > 5.1: + self.scratchDb.execute("set session old_alter_table=1") + + # I should probably do this before the whole table is built, right? + sql = "ALTER IGNORE TABLE " + table + " ADD PRIMARY KEY (" + column + ")" + self.scratchDb.execute(sql) + + if self.scratchDb.version > 5.1: + self.scratchDb.execute("set session old_alter_table=0") ''' Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/scratchdb.py (revision 37156) @@ -484,4 +484,10 @@ airmass FLOAT, \ expTime FLOAT, \ + Mpsf FLOAT, \ + dMpsf FLOAT, \ + Mkron FLOAT, \ + dMkron FLOAT, \ + Map FLOAT, \ + dMap FLOAT, \ flags INT, \ PRIMARY KEY (imageID, ippDetectID), \ Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/sqlUtility.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/sqlUtility.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/sqlUtility.py (revision 37156) @@ -21,7 +21,8 @@ return + # result is startString + fields + middleString + values + endString def makeRaw(self,middleString,endString): - output = self.startString + output = self.startString + " " for i in range(len(self.fields)): @@ -30,5 +31,5 @@ output += ", " - output += middleString + output += " " + middleString + " " for i in range(len(self.values)): @@ -37,11 +38,12 @@ output += ", " - output += endString + output += " " + endString return output + # result is startString + fields + middleString + values + endString (each value surrounded by '') def make(self,middleString,endString): - output = self.startString + output = self.startString + " " for i in range(len(self.fields)): @@ -50,5 +52,5 @@ output += ", " - output += middleString + output += " " + middleString + " " for i in range(len(self.values)): @@ -57,5 +59,19 @@ output += ", " - output += endString + output += " " + endString + + return output + + # result is startString + field = value,... + endString + def makeEquals(self,endString): + + output = self.startString + " " + + for i in range(len(self.fields)): + output += self.fields[i] + " = " + self.values[i] + if (i < len(self.fields) - 1): + output += ", " + + output += " " + endString return output Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py (revision 37156) @@ -17,4 +17,5 @@ from ipptopspsdb import IppToPspsDb from scratchdb import ScratchDb +from sqlUtility import sqlUtility import logging.config @@ -54,121 +55,82 @@ self.stackType = "DEEP_STACK" # TODO - ## = stuff I commented out for pv2 stacks - #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 stack, get 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.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 stack ID to get the generic metadata + metastackID = -999 + + # 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 + + metastackID = stackID + + # 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", stackID + " vs " + header['IMAGEID']) + raise + + print "2" + + imageID = self.scratchDb.getImageIDFromExternID(stackID) + self.logger.infoPair(filter + " imageID = ", imageID) + self.imageIDs[filter] = imageID + + print "3" + + # did we find any valid stacks? + if metastackID < 0: self.logger.errorPair("can't find any stack ids...?? ","??") raise - meta = self.gpc1Db.getStackStageMeta(metastackid) - ## this should be ok - it takes a stack_id and returns filter/skycell etc. + # using the given stack ID, get the generic information: filter, skycell, etc. + meta = self.gpc1Db.getStackStageMeta(metastackID) if not meta: - self.logger.errorPair("Could not get stack", "metadata") + self.logger.errorPair("Could not get stack metadata for", metastackID) raise - #self.filter = meta[0]; - #self.filter = self.filter[0:1] - #self.filterID = self.scratchDb.getFilterID(self.filter) - self.skycell = meta[1]; - - # self.testprint() - + fullSkycell = meta[1]; + + # XXX EAM 20140729 : review and fix skycell vs projection cell ID # 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:] @@ -177,8 +139,9 @@ # 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() + self.skycell = fullSkycell[8:] + self.projectioncell = fullSkycell + + # XXX need to set this somewhere else, but where?? + self.dvoRegionID = 1 # proposed new values. Need to coordinate with the SkyCell table @@ -188,54 +151,36 @@ self.analysisVer = meta[2]; - #self.expTime = gpc1Db.getStackExpTime(self.id) - # 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 + for filter in self.filters: + tableName = filter + "StackMeta" + self.dropTableVerbose(tableName) + + self.dropTableVerbose("StackObjectThin") + self.dropTableVerbose("StackObjectExtra") + + self.dropTableVerbose("StackApFlx") + + self.dropTableVerbose("StackModelFitThin") + self.dropTableVerbose("StackModelFitDeV") + self.dropTableVerbose("StackModelFitExp") + self.dropTableVerbose("StackModelFitSer") + self.dropTableVerbose("StackModelFitPet") + self.dropTableVerbose("StackModelFitFlx") + self.dropTableVerbose("StackModelFitFlxC1") + self.dropTableVerbose("StackModelFitFlxC2") + + 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") + 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 @@ -257,5 +202,11 @@ self.logger.infoPair("Skycell", "%s" % self.skycell) self.logger.infoPair("Projectioncell", "%s" % self.projectioncell) - ##self.logger.infoPair("Filter", "%s" % self.filter) + + ''' + Drop a table and report the drop + ''' + def dropTableVerbose(self, table): + self.logger.infoPair("dropping table:",table) + self.scratchDb.dropTable(table) ''' @@ -264,9 +215,12 @@ 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) + sql = "UPDATE " + table + " SET " + + for i in range(len(self.filters)): + filter = self.filters[i] + field = filter + "stackMetaID" + sql += field + " = '" + self.stackIDs[filter] + "'" + if (i < len(self.filters) - 1): + sql += ", " self.scratchDb.execute(sql) @@ -367,4 +321,6 @@ sql = "UPDATE " + tablename + " AS a, "+f+"SkyChip_xrad AS b SET " cnt =0 + + # XXX : I need to worry about which header this is exptime = self.safeDictionaryAccess(self.header, 'EXPTIME') for number in numbers: @@ -383,94 +339,33 @@ ''' - 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_%'" - 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 - 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') + def populateStackMeta(self,filter): + + header = self.headerSet[filter] + tablename = filter + "StackMeta" + stackID = self.stackIDs[filter] + filterName = filter + ".00000" + + self.logger.infoPair("Populating table", tablename) + + self.fwhm_maj = self.getKeyValue(header, 'FWHM_MAJ') + self.fwhm_maj_uq = self.getKeyValue(header, 'FW_MJ_UQ') + self.psfmodel = self.getKeyValue(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)) + # make a table + filterID = self.scratchDb.getFilterID(filter) + self.logger.infoPair("filterID is ",str(filterID)) + + photoCalID = str(self.scratchDb.getPhotoCalID(stackID)) + + # XXX mysql is sensitive to values which are ambiugously float. eg + # a warning is raised if we try to insert '25.' into a float field. + # I should make a function to sanitize float values to a desired number + # of decimal points + zpString = "%5.5f" % float(header['FPA.ZP']) sql = "CREATE TABLE " + tablename + " LIKE StackMeta" @@ -478,316 +373,327 @@ 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'] + " \ - )" + sqlLine = sqlUtility("INSERT INTO " + tablename + " (") + + sqlLine.group("batchID", str(self.batchID)) + sqlLine.group("surveyID", str(self.surveyID)) + sqlLine.group("dataRelease", str(self.skychunk.dataRelease)) + sqlLine.group("stackMetaID", str(stackID)) + sqlLine.group("filterID", str(filterID)) + sqlLine.group("skyCellID", self.skycell) + sqlLine.group("photoCalID", photoCalID) + sqlLine.group("photoZero", zpString) # XXX see note above + sqlLine.group("expTime", header['EXPTIME']) + sqlLine.group("psfModelID", self.psfmodel) + sqlLine.group("psfFwhm_mean", str(self.fwhm_maj)) + sqlLine.group("psfFwhm_max", str(self.fwhm_maj_uq)) + sqlLine.group("ctype1", header['CTYPE1']) + sqlLine.group("ctype2", header['CTYPE2']) + sqlLine.group("crval1", header['CRVAL1']) + sqlLine.group("crval2", header['CRVAL2']) + sqlLine.group("crpix1", header['CRPIX1']) + sqlLine.group("crpix2", header['CRPIX2']) + sqlLine.group("cdelt1", header['CDELT1']) + sqlLine.group("cdelt2", header['CDELT2']) + sqlLine.group("pc001001", header['PC001001']) + sqlLine.group("pc001002", header['PC001002']) + sqlLine.group("pc002001", header['PC002001']) + sqlLine.group("pc002002", header['PC002002']) + + sql = sqlLine.make(") VALUES ( ", ")") + print "stack meta SQL: " + sql + 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)) + # set stackTypeID in table based on lookup table & self.stackType + # XXX include in the INSERT above? self.updateStackTypeID(tablename) - ''' - Populates the StackObject table - ''' - def populateStackObject(self): - - self.logger.infoPair("Procesing table", "StackObject") + self.tablesToExport.append(tablename) + + ''' + Populates the StackObjectThin table + ''' + def populateStackObjectThin(self): + + self.logger.infoPair("Procesing table", "StackObjectThin") + self.selectDvoObjIDs() + + # XXX EAM 20140731 : if we are going to add indexes (objID?) to StackObjectThin, do it HERE + + 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."+filter+"ra", "b.ra") + sqlLine.group("a."+filter+"dec", "b.dec_") + sqlLine.group("a."+filter+"raErr", "b.raErr") + sqlLine.group("a."+filter+"decErr", "b.decErr") + + 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)) + self.logger.infoPair('sql', sql) + + 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 = header['EXPTIME'] + + sqlLine = sqlUtility("UPDATE StackObjectThin AS a , " + filter + "SkyChip_psf AS b SET") + + 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") + self.logger.infoPair('sql', sql) + + # 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 + + ''' + Populates the StackObjectExtra table + ''' + def populateStackObjectExtra(self): + + self.logger.infoPair("Procesing table", "StackObjectExtra") + self.insertDvoIDs("StackObjectExtra", "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + # self.scratchDb.createIndex("StackObjectExtra", "objID") + + self.logger.infoPair("inserting filter dependent cmf items into", "StackObjectExtra") + 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 = header['EXPTIME'] + + # insert all the detections + sqlLine = sqlUtility("UPDATE StackObjectExtra AS a , " + filter + "SkyChip_psf AS b SET") + + 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 * b.AP_MAG_RADIUS * b.AP_MAG_RADIUS)") + sqlLine.group("a."+filter+"ApRadius", "b.AP_MAG_RADIUS") + sqlLine.group("a."+filter+"KronRad", "b.MOMENTS_R1 * 2.5") + + sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET") + + self.logger.infoPair('sql', sql) + 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 StackObjectExtra 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") + + self.logger.infoPair('sql', sql) + try: + self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql',sql) + return + + ''' + Populates the StackModelFitThin table + ''' + def populateStackModelFitThin(self): + + self.logger.infoPair("Procesing table", "StackModelFitThin") + self.insertDvoIDs("StackModelFitThin", "StackObjectThin") + + # if we are going to add a key, do it here so it is useful + # self.scratchDb.createIndex("StackObjectExtra", "objID") + - 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 - - try: - self.scratchDb.updateAllRows("StackObject", "surveyID", str(self.surveyID)) - 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") - + ''' + Populates the StackModelFitExp table + ''' + def populateStackModelFit(self, model, ippModelType): + + table = "StackModelFit" + model + + self.logger.infoPair("Procesing table", table) + self.insertDvoIDs(table, "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: + populateStackModelFitFilter(model, ippModelType, filter) + + ''' + Updates parameters for a particular model in the StackModelFit table + ''' + def populateStackModelFitFilter(self, model, ippModelType, filter, exptime): + + header = self.headerSet[filter] + exptime = header['EXPTIME'] + + sqlLine = sqlUtility("UPDATE StackModelFit AS a, " + filter + "SkyChip_xfit AS b SET") + + # these lines yield the flux, flux error values, but I do not think we want those in the database... + # sqlLine.group("a." + filter + model + "Flux", "POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime)) + # sqlLine.group("a." + filter + model + "FluxErr", "ABS(b.EXT_INST_MAG_SIG) * POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime) + " / 1.085736") + + sqlLine.group("a." + filter + model + "Mag", "b.EXT_INST_MAG") # need to modify by zero point and 2.5*log10(exptime) + sqlLine.group("a." + filter + model + "MagErr", "b.EXT_INST_MAG_SIG") # need to modify by zero point and 2.5*log10(exptime) + + sqlLine.group("a." + filter + model + "Radius", "b.EXT_WIDTH_MAJ") + sqlLine.group("a." + filter + model + "Ab", "b.EXT_WIDTH_MAJ/b.EXT_WIDTH_MIN") + sqlLine.group("a." + filter + model + "Phi", "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 + "Ra", "b.RA_EXT") + sqlLine.group("a." + filter + model + "Dec", "b.DEC_EXT") + + sqlLine.group("a." + filter + model + "RaErr", "b.X_EXT_SIG * b.PLTSCALE") + sqlLine.group("a." + filter + model + "DecErr", "b.Y_EXT_SIG * b.PLTSCALE") + + # 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 + "'") + + print sql 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)) - + + + # XXX old code for flux tables + # 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) + + ''' + Populates the StackApFlx table, mainly from dictionary values found in IPP FITS header + ''' + # def populateStackApFlx(self,filter): + + ## 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) ''' @@ -795,140 +701,22 @@ ''' 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) + + for filter in self.filters: + if self.stackIDs[filter] <= 0: + continue + + # XXX is this a reasonable solution? + self.header = self.headerSet[filter] + + self.logger.infoPair("Adding fluxes", "un-convolved") + self.updateModelFitFlxs("", "((b.table_index -1) % 3 = 0)", "StackApFlx", filter) + self.logger.infoPair("Adding fluxes", "1st convolved") + self.updateModelFitFlxs("c1", "((b.table_index -1) % 3 = 1)","StackApFlx", filter) + self.logger.infoPair("Adding fluxes", "2nd convolved") + self.updateModelFitFlxs("c2", "((b.table_index -1) % 3 = 2)","StackApFlx", filter) + + 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) ''' @@ -956,29 +744,21 @@ self.logger.infoPair("Procesing table", "StackToImage") - filters = ['g','r','i','z','y'] - for f in filters: + + for filter in self.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: + stackmetaID = self.stackIDs[filter] + if stackmetaID > 0: - imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaid) + imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaID) for imageID in imageIDs: + sql = "INSERT INTO StackToImage (stackMetaID, imageID) \ VALUES (\ - " + str(stackmetaid) + ", " + imageID + ")" + " + 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)+")" + # 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) @@ -1015,4 +795,8 @@ + ''' + I think we are no longer using this function: this have been + replaced with the code in 'selectDvoObjIDs' and 'selectDvoFilterIDs' + ''' def populateStackIPPDetectIDFromDvo(self, tableName, all): @@ -1022,62 +806,36 @@ 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: + for filter in self.filters: + if self.stackIDs[filter] < 0: self.logger.infoPair("skipping ingest of dvo for filter",f) - #out of the loops - give dataRelase to all + continue + + self.header = self.headerSet[filter] + stackmetaID = self.stackIDs[filter] + + imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) + self.logger.infoPair("obtained","imageID") + addsql = "" + if (all == 1): + addsql = "a." + filter + "ra = b.ra, \ + a." + filter + "dec = b.dec_, \ + a." + filter + "raErr = b.raErr, \ + a." + filter + "decErr = b.decErr, \ + a." + filter + "zp = b.zp, \ + a." + filter + "expTime = b.expTime," + + + sql = "UPDATE " + tableName + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b \ + SET a." + filter + "ippDetectID = b.ippDetectID, \ + " + addsql + " \ + a." + filter + "stackDetectID = b.detectID, \ + a." + filter + "ippObjID = b.ippObjID, \ + a." + filter + "stackMetaID = " + str(stackmetaID) + " \ + WHERE a.objID = b.objID AND b.imageID = " + str(imageID) + + self.logger.infoPair('sql',sql) + self.scratchDb.execute(sql) + + # out of the loops - give dataRelase to all self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) @@ -1122,36 +880,94 @@ 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) + + ''' + select objID (psps object ID) from dvo restricted to this set of imageIDs. also insert + ippObjID and several constants (for a skycell): surveyID, skyCellId, dvoRegionID, dataRelease + ''' + def selectDvoObjIDs(self): + + print "starting selectDvoObjIDs" + + sql = "INSERT INTO StackObjectThin (objID, ippObjID, surveyID, skyCellID, dvoRegionID, dataRelease) " + sql += "SELECT DISTINCT objID, ippObjID, " + sql += str(self.surveyID) + ", " + sql += str(self.skycell) + ", " + sql += str(self.dvoRegionID) + ", " + sql += str(self.skychunk.dataRelease) + + sql += " FROM " + self.scratchDb.dvoDetectionTable + sql += " WHERE (" + + print "sql so far: " + sql + + imageIDs = self.imageIDs.values() + for i in range(len(imageIDs)): + imageID = imageIDs[i] + self.logger.infoPair("selecting imageID", imageID) + + print "table: self.scratchDb.dvoDetectionTable" + print "imageID: " + str(imageID) + + sql += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")" + print "sql: " + sql + + if (i < len(imageIDs) - 1): + sql += " or " + + print "done segment" + + sql += ")" + + print "SQL: " + sql self.scratchDb.execute(sql) - - + + ## XXX ''' + ## XXX select objid (psps object ID) from dvo restricted to this set of imageIDs + ## XXX ''' + ## XXX def selectDvoFilterIDs(self, table): + ## XXX + ## XXX # XXX I am using imageID a lot: generate a filter-index list + ## XXX for filter in self.filters: + ## XXX self.header = self.headerSet[filter] + ## XXX imageIDgpc = self.header['IMAGEID'] + ## XXX imageIDdvo = self.scratchDb.getImageIDFromExternID(imageIDgpc) + ## XXX self.logger.infoPair("selecting imageID", imageIDdvo) + ## XXX + ## XXX sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable " AS b SET " + ## XXX + ## XXX sql += "a." + filter + "ippDetectID = b.ippDetectID, " # eg, gippDetectID (from dvo.measure.detID) + ## XXX sql += "a." + filter + "stackDetectID = b.detectID " # eg, gstackDetectID (from dvo.measure.extID) + ## XXX sql += "a." + filter + "stackMetaID = " + imageIDgpc # eg, gstackMetaID (from cmf.IMAGEID) + ## XXX + ## XXX sql += "WHERE a.objID = b.objID " + ## XXX sql += "AND b.imageID = " + imageIDdvo + ## XXX + ## XXX print "SQL: " + sql + ## XXX self.scratchDb.execute(sql) + + ''' + Insert objid (from dvo) into table from main table + ''' + def insertDvoIDs(self, table, mainTable): + + fields = "objID, ippObjID, surveyID, skyCellID, dvoRegionID, dataRelease, " + + 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 += ", " + + sql = "INSERT INTO " + table + " (" + fields + ") " + sql += "SELECT " + fields + " FROM " + mainTable + print "SQL: " + sql + self.scratchDb.execute(sql) ''' Updates provided table with DVO IDs from DVO table ''' - def updateDvoIDs(self, table, filterName): + def updateDvoIDs(self, table): self.logger.infoPair("getting imageID for ","filter") imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) @@ -1203,119 +1019,56 @@ 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) + # insertDvoIDs called below populates the given StackDetection + # tables with the DVO object IDs by querying the dvoDetection + # table for detections associated with the corresponding image + # IDs. XXX EAM 20140729 : Is this an efficient method? + + # 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.populateStackObjectExtra() + + # self.populateRandomsAndSkyCellID() if self.stackType != "NIGHTLY_STACK": 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.populateStackApFlx() - self.copyRandomsAndSkyCellIDFromStackObject("StackApFlx") - #cull the entries that should not be (how?) - + self.populateStackModelFit("Exp", "PS_MODEL_EXP") + self.populateStackModelFit("DeV", "PS_MODEL_DEV") + self.populateStackModelFit("Ser", "PS_MODEL_SERSIC") + + #self.populateStackModelPet("Ser", "PS_MODEL_SERSIC") + + #self.logger.infoPair("populating ","StackApFlx") + #self.populateStackApFlx() 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.setMinMaxObjID(["StackObject"]) + # self.updateStackTypeID("StackModelFit") + self.tablesToExport.append("StackToImage") self.tablesToExport.append("StackObject") @@ -1325,4 +1078,5 @@ self.logger.infoPair("finishing","populatePspsTables"); return True + ''' overidding this method - use regex to trimoff letter before stack @@ -1340,59 +1094,29 @@ self.logger.infoPair("Importing ST tables with table match expression: ", tableRE) - # print "trying to read ", self.fits.getPath() - - # ST has 5 cmf files - all with the same table names. To counter this we do it like this. - - filters = ["g","r","i","z","y"] count = 0 - for f in filters: - skip = 0 - if (f == "g"): - if self.gstackID > 0: - fileName = self.gfits.getPath() - else: - skip = 1 - if (f == "r"): - if self.rstackID > 0: - fileName = self.rfits.getPath() - else: - skip = 1 - if (f == "i"): - if self.istackID > 0: - fileName = self.ifits.getPath() - else: - skip = 1 - if (f == "z"): - if self.zstackID > 0: - fileName = self.zfits.getPath() - else: - skip = 1 - if (f == "y"): - if self.ystackID > 0: - fileName = self.yfits.getPath() - else: - skip = 1 - - if skip == 1: - self.logger.infoPair("no files for filter" , f) - else: - self.logger.infoPair("using filename:",fileName) + for filter in self.filters: + if self.stackIDs[filter] <= 0: + self.logger.infoPair("no files for filter" , filter) + continue + + fileName = self.fits[filter].getPath() - try: + self.logger.infoPair("using filename:",fileName) + + try: tables = stilts.treads(fileName) - except: + except: self.logger.errorPair("STILTS could not import from", fileName) return False - #count = 0 - for table in tables: + # count = 0 + for table in tables: # print "import smf table ", table match = re.match(tableRE, table.name) - if not match: continue - self.logger.infoPair("Reading IPP table", f + table.name) + + self.logger.infoPair("Reading IPP table", filter + table.name) table = stilts.tpipe(table, cmd='addcol table_index $0') - table = stilts.tpipe(table, cmd='explodeall') @@ -1400,5 +1124,5 @@ # drop any previous tables before import - self.scratchDb.dropTable(f + table.name) + self.scratchDb.dropTable(filter + table.name) # IPP FITS files are littered with infinities, so remove these @@ -1410,8 +1134,8 @@ try: - table.write(self.scratchDb.url + '#' + f + table.name) + table.write(self.scratchDb.url + '#' + filter + table.name) count = count + 1 except: - self.logger.exception("Problem writing table '" + f + table.name + "' to the database") + self.logger.exception("Problem writing table '" + filter + table.name + "' to the database") self.logger.infoPair("Done. Imported", "%d tables" % count) Index: /branches/eam_branches/ipp-20140717/ippToPsps/jython/testCode.py =================================================================== --- /branches/eam_branches/ipp-20140717/ippToPsps/jython/testCode.py (revision 37155) +++ /branches/eam_branches/ipp-20140717/ippToPsps/jython/testCode.py (revision 37156) @@ -18,19 +18,13 @@ super(testCode, self).__init__(argv) - ''' - Main processing loop. - Tiled area is defined in 'skychunk' and looks for available stage_ids for each tile and writes them to ippToPsps database ready for processing - by one or more loading clients that may be running on any host - ''' def run(self): - sqlLine = sqlUtility("INSERT INTO Table (") - sqlLine.group("field1", "value1") - sqlLine.group("field2", "value2") - sqlLine.group("field3", "value3") + names = {} - sql = sqlLine.make(") VALUES (", ")") + filters =['g','r','i'] + for f in filters: - print "sql: " + sql + names[f] = f + ".00000" + print "filter: " + f + " = " + names[f] '''