Index: trunk/ippToPsps/jython/detectionbatch.py =================================================================== --- trunk/ippToPsps/jython/detectionbatch.py (revision 37106) +++ trunk/ippToPsps/jython/detectionbatch.py (revision 37246) @@ -19,4 +19,5 @@ from ipptopspsdb import IppToPspsDb from scratchdb import ScratchDb +from sqlUtility import sqlUtility import logging.config @@ -41,6 +42,5 @@ scratchDb, camID, - batchID, - useFullTables): + batchID): super(DetectionBatch, self).__init__( @@ -54,6 +54,5 @@ batchID, "P2", - gpc1Db.getCameraStageSmf(camID,skychunk.P2_smf_version), - useFullTables) + gpc1Db.getCameraStageSmf(camID,skychunk.P2_smf_version)) # get camera meta data @@ -99,4 +98,5 @@ raise + # MJD-OBS is the exposure start, EXTIME / 172800 = (EXPTIME sec / 84600 sec/day) / 2 self.obsTime = float(self.header['MJD-OBS']) + (float(self.header['EXPTIME']) / 172800.0) @@ -124,95 +124,57 @@ self.logger.infoPair("Proccesing table", "FrameMeta") - sql = "INSERT INTO FrameMeta (\ - frameID \ - ,frameName \ - ,cameraID \ - ,cameraConfigID \ - ,telescopeID \ - ,analysisVer \ - ,p1Recip \ - ,p2Recip \ - ,p3Recip \ - ,photoScat \ - ,expStart \ - ,expTime \ - ,airmass \ - ,raBore \ - ,decBore \ - ,ctype1 \ - ,ctype2 \ - ,crval1 \ - ,crval2 \ - ,crpix1 \ - ,crpix2 \ - ,cdelt1 \ - ,cdelt2 \ - ,pc001001 \ - ,pc001002 \ - ,pc002001 \ - ,pc002002 \ - ,polyOrder \ - ,pca1x3y0 \ - ,pca1x2y1 \ - ,pca1x1y2 \ - ,pca1x0y3 \ - ,pca1x2y0 \ - ,pca1x1y1 \ - ,pca1x0y2 \ - ,pca2x3y0 \ - ,pca2x2y1 \ - ,pca2x1y2 \ - ,pca2x0y3 \ - ,pca2x2y0 \ - ,pca2x1y1 \ - ,pca2x0y2 \ - ) VALUES ( \ - " + str(self.expID) + " \ - ,'" + self.expName + "' \ - ,1 \ - ,1 \ - ,1 \ - ,'" + str(self.analysisVer) + "' \ - ,' ' \ - ,' ' \ - ,' ' \ - ," + self.safeDictionaryAccess(self.header, 'ZPT_ERR') + " \ - ," + self.safeDictionaryAccess(self.header, 'MJD-OBS') + " \ - ," + self.safeDictionaryAccess(self.header, 'EXPREQ') + " \ - ," + self.safeDictionaryAccess(self.header, 'AIRMASS') + " \ - ," + self.safeDictionaryAccess(self.header, 'RA') + " \ - ," + self.safeDictionaryAccess(self.header, 'DEC') + " \ - ,'" + self.safeDictionaryAccess(self.header, 'CTYPE1') + "' \ - ,'" + self.safeDictionaryAccess(self.header, 'CTYPE2') + "' \ - ," + self.safeDictionaryAccess(self.header, 'CRVAL1') + " \ - ," + self.safeDictionaryAccess(self.header, 'CRVAL2') + " \ - ," + self.safeDictionaryAccess(self.header, 'CRPIX1') + " \ - ," + self.safeDictionaryAccess(self.header, 'CRPIX2') + " \ - ," + self.safeDictionaryAccess(self.header, 'CDELT1') + " \ - ," + self.safeDictionaryAccess(self.header, 'CDELT2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC001001') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC001002') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC002001') + " \ - ," + self.safeDictionaryAccess(self.header, 'PC002002') + " \ - ," + self.safeDictionaryAccess(self.header, 'NPLYTERM') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X3Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X2Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X1Y2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X0Y3') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X2Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X1Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA1X0Y2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X3Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X2Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X1Y2') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X0Y3') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X2Y0') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X1Y1') + " \ - ," + self.safeDictionaryAccess(self.header, 'PCA2X0Y2') + " \ - )" - - # print "frame meta sql: ", sql - - self.scratchDb.execute(sql) + sqlLine = sqlUtility("INSERT INTO FrameMeta (") + + # sqlLine.group("p1Recip", ' '); + # sqlLine.group("p2Recip", ' '); + # sqlLine.group("p3Recip", ' '); + + sqlLine.group("frameID", str(self.expID)); + sqlLine.group("frameName", self.expName); + sqlLine.group("cameraID", "1"); + sqlLine.group("cameraConfigID", "1"); + sqlLine.group("telescopeID", "1"); + sqlLine.group("analysisVer", self.analysisVer); + sqlLine.group("photoScat", self.getKeyFloat(self.header, "%.8f", 'ZPT_ERR')); + sqlLine.group("expStart", self.getKeyFloat(self.header, "%.10f", 'MJD-OBS')); + sqlLine.group("expTime", self.getKeyFloat(self.header, "%.8f", 'EXPREQ')); + sqlLine.group("airmass", self.getKeyFloat(self.header, "%.8f", 'AIRMASS')); + sqlLine.group("raBore", self.getKeyFloat(self.header, "%.10f", 'RA')); + sqlLine.group("decBore", self.getKeyFloat(self.header, "%.10f", 'DEC')); + sqlLine.group("ctype1", self.getKeyValue(self.header, 'CTYPE1')); + sqlLine.group("ctype2", self.getKeyValue(self.header, 'CTYPE2')); + sqlLine.group("crval1", self.getKeyFloat(self.header, "%.8f", 'CRVAL1')); + sqlLine.group("crval2", self.getKeyFloat(self.header, "%.8f", 'CRVAL2')); + sqlLine.group("crpix1", self.getKeyFloat(self.header, "%.8f", 'CRPIX1')); + sqlLine.group("crpix2", self.getKeyFloat(self.header, "%.8f", 'CRPIX2')); + sqlLine.group("cdelt1", self.getKeyFloat(self.header, "%.8e", 'CDELT1')); + sqlLine.group("cdelt2", self.getKeyFloat(self.header, "%.8e", 'CDELT2')); + sqlLine.group("pc001001", self.getKeyFloat(self.header, "%.8e", 'PC001001')); + sqlLine.group("pc001002", self.getKeyFloat(self.header, "%.8e", 'PC001002')); + sqlLine.group("pc002001", self.getKeyFloat(self.header, "%.8e", 'PC002001')); + sqlLine.group("pc002002", self.getKeyFloat(self.header, "%.8e", 'PC002002')); + sqlLine.group("polyOrder", self.getKeyValue(self.header, 'NPLYTERM')); + sqlLine.group("pca1x3y0", self.getKeyFloat(self.header, "%.8e", 'PCA1X3Y0')); + sqlLine.group("pca1x2y1", self.getKeyFloat(self.header, "%.8e", 'PCA1X2Y1')); + sqlLine.group("pca1x1y2", self.getKeyFloat(self.header, "%.8e", 'PCA1X1Y2')); + sqlLine.group("pca1x0y3", self.getKeyFloat(self.header, "%.8e", 'PCA1X0Y3')); + sqlLine.group("pca1x2y0", self.getKeyFloat(self.header, "%.8e", 'PCA1X2Y0')); + sqlLine.group("pca1x1y1", self.getKeyFloat(self.header, "%.8e", 'PCA1X1Y1')); + sqlLine.group("pca1x0y2", self.getKeyFloat(self.header, "%.8e", 'PCA1X0Y2')); + sqlLine.group("pca2x3y0", self.getKeyFloat(self.header, "%.8e", 'PCA2X3Y0')); + sqlLine.group("pca2x2y1", self.getKeyFloat(self.header, "%.8e", 'PCA2X2Y1')); + sqlLine.group("pca2x1y2", self.getKeyFloat(self.header, "%.8e", 'PCA2X1Y2')); + sqlLine.group("pca2x0y3", self.getKeyFloat(self.header, "%.8e", 'PCA2X0Y3')); + sqlLine.group("pca2x2y0", self.getKeyFloat(self.header, "%.8e", 'PCA2X2Y0')); + sqlLine.group("pca2x1y1", self.getKeyFloat(self.header, "%.8e", 'PCA2X1Y1')); + sqlLine.group("pca2x0y2", self.getKeyFloat(self.header, "%.8e", 'PCA2X0Y2')); + + sql = sqlLine.make(") VALUES ( ", ")") + + try: self.scratchDb.execute(sql) + except: + self.logger.errorPair('failed sql: ', sql) + raise + self.scratchDb.updateAllRows("FrameMeta", "batchID", str(self.batchID)) self.scratchDb.updateAllRows("FrameMeta", "surveyID", str(self.surveyID)) @@ -228,154 +190,111 @@ tableName = "ImageMeta_" + ota - # XXX we drop the table above so it is not left behind on failure - # drop then re-create table - # self.scratchDb.dropTable(tableName) + # we drop the table before calling this functoin so it is not left behind on failure sql = "CREATE TABLE " + tableName + " LIKE ImageMeta" try: self.scratchDb.execute(sql) except: pass + if (ota[0:2] == "XY"): ccdID = ota[2:4] else: ccdID = 0 + psfFwhmMajor = self.getKeyFloat(header, "%.8f", 'FWHM_MAJ') + psfFwhmMinor = self.getKeyFloat(header, "%.8f", 'FWHM_MIN') + psfFwhm = 0.5*(float(psfFwhmMajor) + float(psfFwhmMinor)) + # insert image metadata into table - sql = "INSERT INTO " + tableName + " ( \ - frameID \ - ,ccdID \ - ,bias \ - ,biasScat \ - ,sky \ - ,skyScat \ - ,magSat \ - ,completMag \ - ,astroScat \ - ,photoScat \ - ,numAstroRef \ - ,numPhotoRef \ - ,nx \ - ,ny \ - ,psfFwhm \ - ,psfWidMajor \ - ,psfWidMinor \ - ,psfTheta \ - ,momentXX \ - ,momentXY \ - ,momentYY \ - ,momentM3C \ - ,momentM3S \ - ,momentM4C \ - ,momentM4S \ - ,momentR1 \ - ,momentRH \ - ,apResid \ - ,dapResid \ - ,detectorID \ - ,qaFlags \ - ,detrend1 \ - ,detrend2 \ - ,detrend3 \ - ,detrend4 \ - ,detrend5 \ - ,detrend6 \ - ,detrend7 \ - ,detrend8 \ - ,photoZero \ - ,ctype1 \ - ,ctype2 \ - ,crval1 \ - ,crval2 \ - ,crpix1 \ - ,crpix2 \ - ,cdelt1 \ - ,cdelt2 \ - ,pc001001 \ - ,pc001002 \ - ,pc002001 \ - ,pc002002 \ - ,polyOrder \ - ,pca1x3y0 \ - ,pca1x2y1 \ - ,pca1x1y2 \ - ,pca1x0y3 \ - ,pca1x2y0 \ - ,pca1x1y1 \ - ,pca1x0y2 \ - ,pca2x3y0 \ - ,pca2x2y1 \ - ,pca2x1y2 \ - ,pca2x0y3 \ - ,pca2x2y0 \ - ,pca2x1y1 \ - ,pca2x0y2 \ - ) VALUES ( \ - " + str(self.expID) + " \ - ," + str(ccdID) + " \ - ," + str(self.bias) + " \ - ," + str(self.biasScat) + " \ - ," + self.safeDictionaryAccess(header, 'MSKY_MN') + " \ - ," + self.safeDictionaryAccess(header, 'MSKY_SIG') + " \ - ," + self.safeDictionaryAccess(header, 'FSATUR') + " \ - ," + self.safeDictionaryAccess(header, 'FLIMIT') + " \ - ," + self.safeDictionaryAccess(header, 'CERROR') + " \ - ," + self.safeDictionaryAccess(self.header, 'ZPT_OBS') + " \ - ," + self.safeDictionaryAccess(header, 'NASTRO') + " \ - ," + self.safeDictionaryAccess(header, 'NASTRO') + " \ - ," + self.safeDictionaryAccess(header, 'CNAXIS1') + " \ - ," + self.safeDictionaryAccess(header, 'CNAXIS2') + " \ - , ( " + self.safeDictionaryAccess(header, 'FWHM_MAJ') + " + " + self.safeDictionaryAccess(header, 'FWHM_MIN') + " ) / 2.0 \ - ," + self.safeDictionaryAccess(header, 'FWHM_MAJ') + " \ - ," + self.safeDictionaryAccess(header, 'FWHM_MIN') + " \ - ," + self.safeDictionaryAccess(header, 'ANGLE') + " \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ,-999 \ - ," + self.safeDictionaryAccess(header, 'APMIFIT') + " \ - ," + self.safeDictionaryAccess(header, 'DAPMIFIT') + " \ - ,'" + self.safeDictionaryAccess(header, 'DETECTOR') + "' \ - ," + str(self.scratchDb.getDvoImageFlags(header['IMAGEID'])) + " \ - ,'" + self.safeDictionaryAccess(header, 'DETREND.MASK') + "' \ - ,'" + self.safeDictionaryAccess(header, 'DETREND.DARK') + "' \ - ,'" + self.safeDictionaryAccess(header, 'DETREND.FLAT') + "' \ - ,' ' \ - ,' ' \ - ,' ' \ - ,' ' \ - ,' ' \ - ," + self.safeDictionaryAccess(self.header, 'ZPT_OBS') + " \ - ,'" + self.safeDictionaryAccess(header, 'CTYPE1') + "' \ - ,'" + self.safeDictionaryAccess(header, 'CTYPE2') + "' \ - ," + self.safeDictionaryAccess(header, 'CRVAL1') + " \ - ," + self.safeDictionaryAccess(header, 'CRVAL2') + " \ - ," + self.safeDictionaryAccess(header, 'CRPIX1') + " \ - ," + self.safeDictionaryAccess(header, 'CRPIX2') + " \ - ," + self.safeDictionaryAccess(header, 'CDELT1') + " \ - ," + self.safeDictionaryAccess(header, 'CDELT2') + " \ - ," + self.safeDictionaryAccess(header, 'PC001001') + " \ - ," + self.safeDictionaryAccess(header, 'PC001002') + " \ - ," + self.safeDictionaryAccess(header, 'PC002001') + " \ - ," + self.safeDictionaryAccess(header, 'PC002002') + " \ - ," + self.safeDictionaryAccess(header, 'NPLYTERM') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X3Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X2Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X1Y2') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X0Y3') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X2Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X1Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA1X0Y2') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X3Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X2Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X1Y2') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X0Y3') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X2Y0') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X1Y1') + " \ - ," + self.safeDictionaryAccess(header, 'PCA2X0Y2') + " \ - )" + sqlLine = sqlUtility("INSERT INTO " + tableName + "(") + + sqlLine.group("frameID", str(self.expID)) + sqlLine.group("ccdID", str(ccdID)) + sqlLine.group("bias", str(self.bias)) + sqlLine.group("biasScat", str(self.biasScat)) + sqlLine.group("sky", self.getKeyFloat(header, "%.8f", 'MSKY_MN')) + sqlLine.group("skyScat", self.getKeyFloat(header, "%.8f", 'MSKY_SIG')) + sqlLine.group("magSat", self.getKeyFloat(header, "%.8f", 'FSATUR')) + sqlLine.group("completMag", self.getKeyFloat(header, "%.8f", 'FLIMIT')) + sqlLine.group("astroScat", self.getKeyFloat(header, "%.8f", 'CERROR')) + sqlLine.group("photoScat", self.getKeyFloat(self.header, "%.8f", 'ZPT_OBS')) + sqlLine.group("numAstroRef", self.getKeyValue(header, 'NASTRO')) + sqlLine.group("numPhotoRef", self.getKeyValue(header, 'NASTRO')) + sqlLine.group("nx", self.getKeyInt(header, 0, 'CNAXIS1')) + sqlLine.group("ny", self.getKeyInt(header, 0, 'CNAXIS2')) + sqlLine.group("psfFwhm", str(psfFwhm)) + sqlLine.group("psfWidMajor", psfFwhmMajor) + sqlLine.group("psfWidMinor", psfFwhmMinor) + sqlLine.group("psfTheta", self.getKeyFloat(header, "%.8f", 'ANGLE')) + sqlLine.group("momentMajor", self.getKeyFloat(header, "%.8f", 'IQ_FW1')) + sqlLine.group("momentMinor", self.getKeyFloat(header, "%.8f", 'IQ_FW2')) + sqlLine.group("momentM2C", self.getKeyFloat(header, "%.8f", 'IQ_M2C')) + sqlLine.group("momentM2S", self.getKeyFloat(header, "%.8f", 'IQ_M2S')) + sqlLine.group("momentM3", self.getKeyFloat(header, "%.8f", 'IQ_M3')) + sqlLine.group("momentM4", self.getKeyFloat(header, "%.8f", 'IQ_M4')) + sqlLine.group("apResid", self.getKeyFloat(header, "%.8f", 'APMIFIT')) + sqlLine.group("dapResid", self.getKeyFloat(header, "%.8f", 'DAPMIFIT')) + sqlLine.group("detectorID", self.getKeyValue(header, 'DETECTOR')) + sqlLine.group("qaFlags", str(self.scratchDb.getDvoImageFlags(header['IMAGEID']))) + sqlLine.group("detrend1", self.getKeyValue(header, 'DETREND.MASK')) + sqlLine.group("detrend2", self.getKeyValue(header, 'DETREND.DARK')) + sqlLine.group("detrend3", self.getKeyValue(header, 'DETREND.FLAT')) + # sqlLine.group("detrend4", " ") + # sqlLine.group("detrend5", " ") + # sqlLine.group("detrend6", " ") + # sqlLine.group("detrend7", " ") + # sqlLine.group("detrend8", " ") + sqlLine.group("photoZero", self.getKeyFloat(self.header, "%.8f", 'ZPT_OBS')) + sqlLine.group("ctype1", self.getKeyValue(header, 'CTYPE1')) + sqlLine.group("ctype2", self.getKeyValue(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("polyOrder", self.getKeyInt(header, 0, 'NPLYTERM')) + + nPolyterm = int(self.getKeyValue(header, 'NPLYTERM')) + if nPolyterm > 1: + sqlLine.group("pca1x2y0", self.getKeyFloat(header, "%.8e", 'PCA1X2Y0')) + sqlLine.group("pca1x1y1", self.getKeyFloat(header, "%.8e", 'PCA1X1Y1')) + sqlLine.group("pca1x0y2", self.getKeyFloat(header, "%.8e", 'PCA1X0Y2')) + sqlLine.group("pca2x2y0", self.getKeyFloat(header, "%.8e", 'PCA2X2Y0')) + sqlLine.group("pca2x1y1", self.getKeyFloat(header, "%.8e", 'PCA2X1Y1')) + sqlLine.group("pca2x0y2", self.getKeyFloat(header, "%.8e", 'PCA2X0Y2')) + else: + sqlLine.group("pca1x2y0", "-999") + sqlLine.group("pca1x1y1", "-999") + sqlLine.group("pca1x0y2", "-999") + sqlLine.group("pca2x2y0", "-999") + sqlLine.group("pca2x1y1", "-999") + sqlLine.group("pca2x0y2", "-999") + + if nPolyterm > 2: + sqlLine.group("pca1x3y0", self.getKeyFloat(header, "%.8e", 'PCA1X3Y0')) + sqlLine.group("pca1x2y1", self.getKeyFloat(header, "%.8e", 'PCA1X2Y1')) + sqlLine.group("pca1x1y2", self.getKeyFloat(header, "%.8e", 'PCA1X1Y2')) + sqlLine.group("pca1x0y3", self.getKeyFloat(header, "%.8e", 'PCA1X0Y3')) + sqlLine.group("pca2x3y0", self.getKeyFloat(header, "%.8e", 'PCA2X3Y0')) + sqlLine.group("pca2x2y1", self.getKeyFloat(header, "%.8e", 'PCA2X2Y1')) + sqlLine.group("pca2x1y2", self.getKeyFloat(header, "%.8e", 'PCA2X1Y2')) + sqlLine.group("pca2x0y3", self.getKeyFloat(header, "%.8e", 'PCA2X0Y3')) + else: + sqlLine.group("pca1x3y0", "-999") + sqlLine.group("pca1x2y1", "-999") + sqlLine.group("pca1x1y2", "-999") + sqlLine.group("pca1x0y3", "-999") + sqlLine.group("pca2x3y0", "-999") + sqlLine.group("pca2x2y1", "-999") + sqlLine.group("pca2x1y2", "-999") + sqlLine.group("pca2x0y3", "-999") + + sql = sqlLine.make(") VALUES ( ", ")") try: self.scratchDb.execute(sql) - except: print sql + except: + self.logger.errorPair('failed sql: ', sql) + raise self.scratchDb.updateFilterID(tableName, self.filter) @@ -407,104 +326,70 @@ results['SATDET'] = 0 - # insert all detections into table - sql = "INSERT IGNORE INTO " + pspsTableName + " ( \ - ippDetectID \ - ,randomDetID \ - ,filterID \ - ,surveyID \ - ,obsTime \ - ,xPos \ - ,yPos \ - ,xPosErr \ - ,yPosErr \ - ,psfFlux \ - ,psfFluxErr \ - ,psfMajorFWHM \ - ,psfMinorFWHM \ - ,psfTheta \ - ,psfCore \ - ,psfLikelihood \ - ,psfQf \ - ,momentXX \ - ,momentXY \ - ,momentYY \ - ,momentR1 \ - ,momentRH \ - ,momentM3C \ - ,momentM3S \ - ,momentM4C \ - ,momentM4S \ - ,apFlux \ - ,kronFlux \ - ,kronFluxErr \ - ,psfQfPerfect \ - ,psfChiSq \ - ,infoFlag \ - ,sky \ - ,skyErr \ - ,pltScale \ - ,posAngle \ - ,dataRelease \ - ) \ - SELECT \ - IPP_IDET \ - , FLOOR(RAND()*9223372036854775807) \ - , " + str(self.filterID) + "\ - , " + str(self.surveyID) + " \ - ," + str(self.obsTime) + " \ - ,X_PSF \ - ,Y_PSF \ - ,X_PSF_SIG \ - ,Y_PSF_SIG \ - ,PSF_INST_FLUX / " + self.header['EXPTIME'] + " \ - ,PSF_INST_FLUX_SIG / " + self.header['EXPTIME'] + " \ - ,PSF_MAJOR \ - ,PSF_MINOR \ - ,PSF_THETA \ - ,-999 \ - ,psfLikelihood(EXT_NSIGMA) \ - ,PSF_QF \ - ,MOMENTS_XX \ - ,MOMENTS_XY \ - ,MOMENTS_YY \ - ,MOMENTS_R1 \ - ,MOMENTS_RH \ - ,MOMENTS_M3C \ - ,MOMENTS_M3S \ - ,MOMENTS_M4C \ - ,MOMENTS_M4S \ - ,POW(10.0, -0.4 *AP_MAG) / " + self.header['EXPTIME'] + " \ - ,KRON_FLUX / " + self.header['EXPTIME'] + " \ - ,KRON_FLUX_ERR / " + self.header['EXPTIME'] + " \ - ,PSF_QF_PERFECT \ - ,PSF_CHISQ \ - ,FLAGS2 << 32 | FLAGS \ - ,SKY / " + self.header['EXPTIME'] + " \ - ,SKY_SIGMA / " + self.header['EXPTIME'] + " \ - ,PLTSCALE \ - ,POSANGLE \ - , " + str(self.skychunk.dataRelease) + "\ - FROM " + ippTableName - # self.logger.info(sql) - -# these were used above (left over from before PSF_INST_FLUX, PSF_INST_FLUX_SIG were available? -# ,POW(10.0, (-0.4*PSF_INST_MAG)) / " + self.header['EXPTIME'] + " -# ,ABS((PSF_INST_MAG_SIG*(POW(10.0, (-0.4*PSF_INST_MAG)) / " + self.header['EXPTIME'] + ")) / 1.085736) - -# I need a way to choose the sql above based on the cmf version: V3 (LAP.PV1) does not have AP_FLUX -# AP_FLUX is no ,AP_FLUX / " + self.header['EXPTIME'] + " - - self.scratchDb.execute(sql) - - # XXX EAM : I removed this old fix for invalid fluxes - # add a instFlux = 0.0 -> 0.000001 - # XXX EAM : why is this done? - # sql="UPDATE " + pspsTableName + " SET psfFlux = 0.0000001 WHERE psfFlux = 0" - # self.scratchDb.execute(sql) - + extTimeString = str(self.header['EXPTIME']) + + # insert all detections into table : do NOT include the IGNORE unless we have to + # sqlLine = sqlUtility("INSERT IGNORE INTO " + pspsTableName + " (") + sqlLine = sqlUtility("INSERT INTO " + pspsTableName + " (") + + # XXX WARNING: need to apply platescale to convert sizes to arcsec + # XXX apFluxF or apFluxNpix + apFluxRadius? + + sqlLine.group("ippDetectID", "IPP_IDET") + sqlLine.group("randomDetID", "FLOOR(RAND()*9223372036854775807)") + sqlLine.group("filterID", str(self.filterID)) + sqlLine.group("surveyID", str(self.surveyID)) + sqlLine.group("obsTime", str(self.obsTime)) + sqlLine.group("xPos", "X_PSF") + sqlLine.group("yPos", "Y_PSF") + sqlLine.group("xPosErr", "X_PSF_SIG") + sqlLine.group("yPosErr", "Y_PSF_SIG") + sqlLine.group("pltScale", "PLTSCALE") + sqlLine.group("posAngle", "POSANGLE") + sqlLine.group("raErr", "X_PSF_SIG * abs(PLTSCALE)") + sqlLine.group("decErr", "Y_PSF_SIG * abs(PLTSCALE)") + sqlLine.group("psfFlux", "PSF_INST_FLUX / " + extTimeString) + sqlLine.group("psfFluxErr", "PSF_INST_FLUX_SIG / " + extTimeString) + sqlLine.group("psfMajorFWHM", "PSF_FWHM_MAJ") + sqlLine.group("psfMinorFWHM", "PSF_FWHM_MIN") + sqlLine.group("psfTheta", "PSF_THETA") + sqlLine.group("psfCore", "PSF_CORE") + sqlLine.group("psfQf", "PSF_QF") + sqlLine.group("psfQfPerfect", "PSF_QF_PERFECT") + sqlLine.group("psfChiSq", "PSF_CHISQ") + sqlLine.group("psfLikelihood", "psfLikelihood(EXT_NSIGMA)") + sqlLine.group("momentXX", "MOMENTS_XX") + sqlLine.group("momentXY", "MOMENTS_XY") + sqlLine.group("momentYY", "MOMENTS_YY") + sqlLine.group("momentR1", "MOMENTS_R1") + sqlLine.group("momentRH", "MOMENTS_RH") + sqlLine.group("momentM3C", "MOMENTS_M3C") + sqlLine.group("momentM3S", "MOMENTS_M3S") + sqlLine.group("momentM4C", "MOMENTS_M4C") + sqlLine.group("momentM4S", "MOMENTS_M4S") + sqlLine.group("apFlux", "AP_FLUX / " + extTimeString) + sqlLine.group("apFluxErr", "AP_FLUX_SIG / " + extTimeString) + sqlLine.group("apFillF", "AP_NPIX / (3.14159265359 * POW(AP_MAG_RADIUS - 0.5, 2))") + sqlLine.group("apRadius", "AP_MAG_RADIUS") + sqlLine.group("kronFlux", "KRON_FLUX / " + extTimeString) + sqlLine.group("kronFluxErr", "KRON_FLUX_ERR / " + extTimeString) + sqlLine.group("kronRad", "MOMENTS_R1 * 2.5") + sqlLine.group("sky", "SKY / " + extTimeString) + sqlLine.group("skyErr", "SKY_SIGMA / " + extTimeString) + sqlLine.group("infoFlag", "FLAGS") + sqlLine.group("infoFlag2", "FLAGS2") + sqlLine.group("dataRelease", str(self.skychunk.dataRelease)) + + sql = sqlLine.makeRaw(") SELECT ", " FROM " + ippTableName) + + try: self.scratchDb.execute(sql) + except: + self.logger.errorPair("failed sql: ", sql) + raise + # 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 + # XXX EAM 2014072 : Is this still a problem? sql = "UPDATE " + pspsTableName + " SET psfFlux = 1e20 WHERE psfFlux <= 0.0" self.scratchDb.execute(sql) @@ -518,5 +403,4 @@ # we don't delete these anymore results['NULLINSTFLUX'] = 0; - ''' @@ -575,117 +459,4 @@ self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - - - ''' - Populates the DetectionCalib table for this OTA - ''' - def populateDetectionCalibTableUpdateInsert(self, ota): - - tableName = "Detection_" + ota - # drop then re-create table - #self.scratchDb.dropTable(tableName) - #sql = "CREATE TABLE " + tableName + " LIKE DetectionCalib" - #try: self.scratchDb.execute(sql) - #except: pass - - # insert all detections into table - sql = "INSERT INTO " + tableName + " ( \ - objID \ - ,detectID \ - ,ippObjID \ - ,ippDetectID \ - ,filterID \ - ,surveyID \ - ) \ - SELECT \ - objID \ - ,detectID \ - ,ippObjID \ - ,ippDetectID \ - ,filterID \ - ,surveyID \ - FROM Detection_" + ota -# self.scratchDb.execute(sql) - - # insert calibration information from dvoDetections into the Table - sql = "UPDATE " + tableName + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b \ - SET a.ra = b.ra, \ - a.dec = b.dec_, \ - a.raErr = b.raErr, \ - a.decErr = b.decErr, \ - a.zp = b.zp, \ - a.expTime = b.expTime, \ - a.airMass = b.airMass \ - WHERE a.objID = b.objID AND a.detectID = b.detectID" - self.scratchDb.execute(sql) - self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - - ''' - Populates the DetectionCalib table for this OTA - ''' - def populateDetectionCalibTable(self, ota): - - # target table name: - tableName = "Detection_" + ota - # drop then re-create table - #self.scratchDb.dropTable(tableName) - #sql = "CREATE TABLE " + tableName + " LIKE Detection" - #try: self.scratchDb.execute(sql) - #except: pass - - externID = self.imageIDs[ota] - - imageID = self.scratchDb.getImageIDFromExternID(externID) - self.logger.infoPair("obtained imageID",imageID) - self.logger.infoPair("obtained externID",externID) - - # insert all detections into table - sql = "Update Detection_" + ota + " AS a, "+ self.scratchDb.dvoDetectionTable + " as b \ - set a.ra=b.ra, \ - a.dec = b.dec_, \ - a.raErr = b.raErr, \ - a.decErr = b.decErr, \ - a.zp = b.zp, \ - a.expTime = b.expTime, \ - a.airMass = b.airMass \ - WHERE (a.objID = b.objID AND a.detectID = b.detectID) \ - AND b.imageID = " + str(imageID) \ - - - try: self.scratchDb.execute(sql) - except: - self.logger.info("failed to update data for detectionCalib") - self.logger.infoPair("sql: ", sql) - raise - - ''' - Populates the Detection table for this OTA - ''' - def populateObjectCalColorTable(self, ota): - - tableName = "ObjectCalColor_" + ota - - # drop then re-create table - self.scratchDb.dropTable(tableName) - sql = "CREATE TABLE " + tableName + " LIKE ObjectCalColor" - try: self.scratchDb.execute(sql) - except: pass - - # insert all detections into table - sql = "INSERT INTO " + tableName + " ( \ - objID \ - ,ippObjID \ - ,filterID \ - ) \ - SELECT \ - objID \ - ,ippObjID \ - ,filterID \ - FROM Detection_" + ota - self.scratchDb.execute(sql) - - self.scratchDb.updateAllRows(tableName, "calibModNum", str(self.calibModNum)) - self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease)) - ''' Applies indexes and other constraints to the PSPS tables @@ -710,10 +481,9 @@ # check we have valid sourceID/imageID pair from the header - if self.safeDictionaryAccess(header, 'SOURCEID') == "NULL": return False - if self.safeDictionaryAccess(header, 'IMAGEID') == "NULL": return False - - # store sourceID/imageID combo in Db so DVO can look up later - if not self.useFullTables: - self.scratchDb.insertNewDvoExternID(header['SOURCEID'], header['IMAGEID']) + if self.getKeyValue(header, 'SOURCEID') == "NULL": return False + if self.getKeyValue(header, 'IMAGEID') == "NULL": return False + + # store sourceID/imageID combo in Db so DVO can look up later (XXX deprecated?) + # self.scratchDb.insertNewDvoExternID(header['SOURCEID'], header['IMAGEID']) # store these for later @@ -746,7 +516,47 @@ self.logger.infoPair("Reading", "FITS headers") self.logger.infoPair("Populating table", "ImageMeta") + + if self.config.camera == "gpc1": + for x in range(self.startX, self.endX): + for y in range(self.startY, self.endY): + + # dodge the corners + if x==0 and y==0: continue + if x==0 and y==7: continue + if x==7 and y==0: continue + if x==7 and y==7: continue + + ota = "XY%d%d" % (x, y) + + if self.config.test and not ((x == 0) and (y == 1)): + print "skipping ota: " + ota + continue + + self.alterPspsTablesChip(ota, ota + ".hdr", x, y) + + if self.config.camera == "simtest": + # try the test Chip + self.alterPspsTablesChip("Chip", "Chip.hdr", 0, 0) + + # now run DVO code to get all IDs ( XXX deprecated, uses dvograbber) + # if not self.getIDsFromDVO(): return False + + # dec is reserved in MySQL, so STILTS replaces if with dec_, which is not the name of + # the column in PSPS + self.scratchDb.execute("ALTER TABLE Detection CHANGE dec_ `dec` double") + + return True + + + ''' + Applies indexes to the IPP tables + ''' + def indexIppTables(self): + + self.logger.infoPair("Creating indexes on", "IPP tables") + for x in range(self.startX, self.endX): for y in range(self.startY, self.endY): - + # dodge the corners if x==0 and y==0: continue @@ -755,38 +565,4 @@ if x==7 and y==7: continue - ota = "XY%d%d" % (x, y) - - self.alterPspsTablesChip(ota, ota + ".hdr", x, y) - - # try the test Chip - self.alterPspsTablesChip("Chip", "Chip.hdr", 0, 0) - - # now run DVO code to get all IDs - if not self.useFullTables: - if not self.getIDsFromDVO(): return False - - # dec is reserved in MySQL, so STILTS replaces if with dec_, which is not the name of - # the column in PSPS - self.scratchDb.execute("ALTER TABLE Detection CHANGE dec_ `dec` double") - - return True - - - ''' - Applies indexes to the IPP tables - ''' - def indexIppTables(self): - - self.logger.infoPair("Creating indexes on", "IPP tables") - - for x in range(self.startX, self.endX): - for y in range(self.startY, self.endY): - - # dodge the corners - if x==0 and y==0: continue - if x==0 and y==7: continue - if x==7 and y==0: continue - if x==7 and y==7: continue - extension = "XY%d%d_psf" % (x, y) self.scratchDb.createIndex(extension, "IPP_IDET") @@ -803,9 +579,17 @@ imageID = self.scratchDb.getImageIDFromExternID(externID) self.logger.debug("Updating table '" + table + "' with DVO IDs using imageID = %d" % imageID) + sql = "UPDATE IGNORE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ - a.ippObjID = b.ippObjID, \ - a.detectID = b.detectID, \ - a.objID = b.objID, \ - a.infoFlag = b.flags << 45 | a.infoFlag \ + a.objID = b.objID, \ + a.detectID = b.detectID, \ + a.ippObjID = b.ippObjID, \ + a.dvoRegionID = b.catID, \ + a.ra = b.ra, \ + a.dec = b.dec_, \ + a.zp = b.zp, \ + a.telluricExt = b.telluricExt, \ + a.airmass = b.airmass, \ + a.expTime = b.expTime, \ + a.infoFlag2 = (b.flags << 13) | a.infoFlag2 \ WHERE a.ippDetectID = b.ippDetectID \ AND b.imageID = " + str(imageID) @@ -817,9 +601,9 @@ ''' def populatePspsTablesChip(self, chipname, x, y, results, tables): - # XXX EAM NOTE: drop tables (Detection_, SkinnyObject_, DetectionCalib_, Detection_) here so + # XXX EAM NOTE: drop tables Detection_* here so # they do not polute the db? # XXX or put an explicit drop at the end of the loop? - #self.logger.infoTitle("Processing " + chipname) + # self.logger.infoTitle("Processing " + chipname) # this is a bit crude: if the chip is not present, this test will fail and the chip # will be (correctly) skipped. would be better to carry that information explicitly ("chip is missing") @@ -882,11 +666,4 @@ self.scratchDb.execute(sql) - #self.logger.info("updated imagedata") - self.populateSkinnyObjectTable(chipname) - self.logger.info("updated skinnyobject") - - self.populateDetectionCalibTable(chipname) - #self.logger.info("updated detectioncalibtable") - # add these to list of tables to export later self.tablesToExport.append("ImageMeta_" + chipname) @@ -896,10 +673,6 @@ self.logger.info("export Detection") - self.tablesToExport.append("SkinnyObject_" + chipname) - self.logger.info("export Skinny") - - tables.append("Detection_" + chipname) - self.logger.info("updated detectioncalibtable") + self.logger.info("updated detection") self.validChips.append(chipname) @@ -936,4 +709,8 @@ if ota not in self.imageIDs: continue + if self.config.test and not ((x == 0) and (y == 1)): + print "skipping ota " + ota + continue + if self.populatePspsTablesChip(ota, x, y, results, tables): otaCount = otaCount + 1 if self.skipBatch: @@ -1045,17 +822,16 @@ ''' def importIppTables(self, filter=""): - - ## if self.config.test: regex = "XY33.psf" - ## else : regex = ".*.psf" - regex = ".*.psf" - - # XXX EAM NOTE : this is fragile : requires PS1_V4 - # columns = "IPP_IDET X_PSF Y_PSF X_PSF_SIG Y_PSF_SIG PSF_INST_MAG PSF_INST_MAG_SIG PSF_INST_FLUX PSF_INST_FLUX_SIG PEAK_FLUX_AS_MAG PSF_MAJOR PSF_MINOR PSF_THETA EXT_NSIGMA PSF_QF MOMENTS_XX MOMENTS_XY MOMENTS_YY AP_MAG AP_FLUX KRON_FLUX KRON_FLUX_ERR FLAGS FLAGS2 SKY SKY_SIGMA EXT_NSIGMA PSF_QF_PERFECT PSF_CHISQ" - - # XXX EAM NOTE : this is fragile : requires PS1_V4 - columns = "IPP_IDET X_PSF Y_PSF X_PSF_SIG Y_PSF_SIG PSF_INST_MAG PSF_INST_MAG_SIG PSF_INST_FLUX PSF_INST_FLUX_SIG PEAK_FLUX_AS_MAG PSF_MAJOR PSF_MINOR PSF_THETA EXT_NSIGMA PSF_QF MOMENTS_XX MOMENTS_XY MOMENTS_YY MOMENTS_R1 MOMENTS_RH MOMENTS_M3C MOMENTS_M3S MOMENTS_M4C MOMENTS_M4S AP_MAG KRON_FLUX KRON_FLUX_ERR FLAGS FLAGS2 SKY SKY_SIGMA EXT_NSIGMA PSF_QF_PERFECT PSF_CHISQ POSANGLE PLTSCALE" - - return super(DetectionBatch, self).importIppTables(columns, regex) - + + regex = ".*.psf" + if self.config.test and self.config.camera == "gpc1": + regex = "XY01.psf" + + # XXX EAM NOTE : this is fragile : requires PS1_V4 + # columns = "IPP_IDET X_PSF Y_PSF X_PSF_SIG Y_PSF_SIG PSF_INST_MAG PSF_INST_MAG_SIG PSF_INST_FLUX PSF_INST_FLUX_SIG PEAK_FLUX_AS_MAG PSF_MAJOR PSF_MINOR PSF_THETA EXT_NSIGMA PSF_QF MOMENTS_XX MOMENTS_XY MOMENTS_YY AP_MAG AP_FLUX KRON_FLUX KRON_FLUX_ERR FLAGS FLAGS2 SKY SKY_SIGMA EXT_NSIGMA PSF_QF_PERFECT PSF_CHISQ" + + # XXX EAM NOTE : this is fragile : requires PS1_V5 + columns = "IPP_IDET X_PSF Y_PSF X_PSF_SIG Y_PSF_SIG POSANGLE PLTSCALE PSF_INST_FLUX PSF_INST_FLUX_SIG PSF_FWHM_MAJ PSF_FWHM_MIN PSF_THETA PSF_CORE PSF_QF PSF_QF_PERFECT PSF_CHISQ EXT_NSIGMA MOMENTS_XX MOMENTS_XY MOMENTS_YY MOMENTS_R1 MOMENTS_RH MOMENTS_M3C MOMENTS_M3S MOMENTS_M4C MOMENTS_M4S AP_FLUX AP_FLUX_SIG AP_NPIX AP_MAG_RADIUS KRON_FLUX KRON_FLUX_ERR SKY SKY_SIGMA FLAGS FLAGS2" + + return super(DetectionBatch, self).importIppTables(columns, regex) ''' @@ -1064,3 +840,2 @@ def exportPspsTablesToFits(self, regex="(.*)"): return super(DetectionBatch, self).exportPspsTablesToFits("([a-zA-Z]+)") -