Index: /trunk/ippToPsps/jython/detectionbatch.py =================================================================== --- /trunk/ippToPsps/jython/detectionbatch.py (revision 34629) +++ /trunk/ippToPsps/jython/detectionbatch.py (revision 34630) @@ -452,5 +452,5 @@ ,MOMENTS_XY \ ,MOMENTS_YY \ - ,AP_MAG \ + ,POW(10.0, -0.4 *AP_MAG) / " + self.header['EXPTIME'] + " \ ,KRON_FLUX / " + self.header['EXPTIME'] + " \ ,KRON_FLUX_ERR / " + self.header['EXPTIME'] + " \ @@ -458,6 +458,6 @@ ,PSF_CHISQ \ ,FLAGS2 << 32 | FLAGS\ - ,SKY \ - ,SKY_SIGMA \ + ,SKY / " + self.header['EXPTIME'] + " \ + ,SKY_SIGMA / " + self.header['EXPTIME'] + " \ ,EXT_NSIGMA \ , 0 \ @@ -466,12 +466,4 @@ , " + str(self.config.dataRelease) + "\ FROM " + ippTableName - # SA10 todo - # need to correct formulas for psfFlux/psfFluxErr - # need to correct apMag apFlux - # need to correct unites kronFlux/Err to correct units - # need to change psfqfperfect -> psfqfperfect PSF_QF_PERFECT? - # need to change psfchisq PSF_CHI_SQ? - # need to change unites of sky/skyErr to adu/s/pixel - # need formula for sgsep/psflikelhood # self.logger.info(sql) self.scratchDb.execute(sql) @@ -557,8 +549,16 @@ FROM Detection_" + ota self.scratchDb.execute(sql) - #SA10 TODO - # insert ra/dec/calstuff into Table - # heather discovered dec is called 'dec_' - sql = "UPDATE " + tableName + " SET ra = -999, dec_ =-999, raErr = -999, decErr = -999, zp = -999, zpErr = -999, expTime = -999, airMass = -999" + + # 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.zpErr = b.zpErr, \ + 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.config.dataRelease)) Index: /trunk/ippToPsps/jython/dvodetections.py =================================================================== --- /trunk/ippToPsps/jython/dvodetections.py (revision 34629) +++ /trunk/ippToPsps/jython/dvodetections.py (revision 34630) @@ -44,5 +44,5 @@ self.importFits( cpmPath, - "IMAGE_ID DET_ID OBJ_ID CAT_ID EXT_ID DB_FLAGS", + "IMAGE_ID DET_ID OBJ_ID CAT_ID EXT_ID DB_FLAGS PHOTCODE AIRMASS M_TIME M_CAL MAG_CAL_ERR D_RA D_DEC PLTSCALE X_CCD_ERR Y_CCD_ERR", cpmTableName) @@ -50,4 +50,6 @@ self.scratchDb.createIndex(cpmTableName, "CAT_ID") self.scratchDb.createIndex(cpmTableName, "OBJ_ID") + + # XXX: Should we create index on PHOTCODE? # we can now report that we have imported this table @@ -60,5 +62,5 @@ self.importFits( cptPath, - "OBJ_ID CAT_ID EXT_ID", + "OBJ_ID CAT_ID EXT_ID RA DEC", cptTableName) @@ -67,17 +69,35 @@ self.scratchDb.createIndex(cptTableName, "OBJ_ID") + # SA10 + # Columns needed for DetectionCalib and StackDetectionCalib + # zp = photcode.K * (AIRMASS - 1) + photcode.C_LAM * 0.001 - cpm.M_CAL + # zpErr = cpm.MAG_CAL_ERR + # airMass = cpm.AIRMASS + # expTime = POW(10.0, 0.4 * cpm.M_TIME) + # ra = cpt.RA - (cpm.D_RA / 3600.0) + # dec = cpt.DEC - (cpm.D_DEC / 3600.0) + # raErr = cpm.X_CCD_ERR * cpm.PLTSCALE + # decErr = cpm.Y_CCD_ERR * cpm.PLTSCALE + self.logger.infoPair("Adding","PSPS objIDs") - # first try to add a column for PSPS_OBJ_ID. catch failure and continue + # first try to add the new columns. catch failure and continue try: - sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (PSPS_OBJ_ID BIGINT)" + sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (PSPS_OBJ_ID BIGINT), (ZP REAL), (RA FLOAT), (DEC FLOAT)" self.scratchDb.execute(sql) except: self.logger.infoPair("Already added PSPS_OBJ_ID column to", cpmTableName) - # shove PSPS objIDs from cpt table into measurement table - sql = "UPDATE " + cpmTableName + " AS a, " + cptTableName + " AS b \ - SET a.PSPS_OBJ_ID = b.EXT_ID \ + # shove PSPS objIDs from cpt table and the calibrated zero point and coordinates which are formed + # by combining values from multiple tables into cpt table + sql = "UPDATE " + cpmTableName + " AS a, " + cptTableName + " AS b, " \ + + self.scratchDb.dvoPhotcodesTable + " AS c \ + SET a.PSPS_OBJ_ID = b.EXT_ID, \ + b.ZP = c.C_LAM * 0.001 + c.K * (b.AIRMASS - 1) - b.M_CAL, \ + b.RA = a.RA - (b.D_RA / 3600.0), \ + b.DEC = a.DEC - (b.D_DEC / 3600.0) \ WHERE a.CAT_ID = b.CAT_ID \ - AND a.OBJ_ID = b.OBJ_ID" + AND a.OBJ_ID = b.OBJ_ID \ + AND b.PHOTCODE = c.CODE" + self.scratchDb.execute(sql) @@ -86,4 +106,5 @@ # PSPS object IDs assigned in the corresponding cpt file. This is believed to be a chip-boundary # issue within DVO. So, for now, we take the first IMAGE_ID/DET_ID detection we find, and ignore the rest + # XXX: Bills 2012-10031 Does this problem still exist? self.logger.infoPair("Merging into", self.scratchDb.dvoDetectionTable) sql = "INSERT IGNORE INTO " + self.scratchDb.dvoDetectionTable + " (\ @@ -94,4 +115,12 @@ ,objID \ ,flags \ + ,zp \ + ,zpErr \ + ,airMass \ + ,expTime \ + ,ra \ + ,dec_ \ + ,raErr \ + ,decErr \ ,fileID \ ) SELECT \ @@ -102,6 +131,15 @@ ,PSPS_OBJ_ID \ ,DB_FLAGS \ + ,ZP \ + ,MAG_CAL_ERR \ + ,AIRMASS \ + ,POW(10.0, 0.4 * M_TIME) \ + ,RA \ + ,DEC \ + ,X_CCD_ERR*PLTSCALE \ + ,Y_CCD_ERR*PLTSCALE \ , " + str(fileId) + " \ FROM " + cpmTableName + try: self.scratchDb.execute(sql) Index: /trunk/ippToPsps/jython/objectbatch.py =================================================================== --- /trunk/ippToPsps/jython/objectbatch.py (revision 34629) +++ /trunk/ippToPsps/jython/objectbatch.py (revision 34630) @@ -113,4 +113,6 @@ self.logger.infoPair("Adding magnitudes from", "cps table") for filter in filters: + + filterID = self.scratchDb.getFilterID(filter[1]) sql = "UPDATE Object JOIN \ @@ -129,8 +131,6 @@ ," + filter[1] + "80pct = MAG_80/1000 \ ," + filter[1] + "Flags = FLAGS \ - ," + filter[1] + "StackDetectID = STACK_ID" - - #stackDetectID needs to be filled still for SA10 - currently at STACK_ID for test purposes - # everthing should be correctly calculated as mags for SA10 + ," + filter[1] + "StackDetectID = (10 * STACK_DETECT_ID) + " + str(filterID) + #self.logger.info(sql) self.scratchDb.execute(sql) Index: /trunk/ippToPsps/jython/scratchdb.py =================================================================== --- /trunk/ippToPsps/jython/scratchdb.py (revision 34629) +++ /trunk/ippToPsps/jython/scratchdb.py (revision 34630) @@ -68,4 +68,6 @@ DECLARE t REAL; \ DECLARE z REAL; \ + DECLARE s INT; \ + SET s= SIGN(x); \ SET x = ABS(x) / 1.4142135623731; \ SET z=ABS(x); \ @@ -75,4 +77,5 @@ SET ans = 2.0 - ans; \ END IF; \ + SET ans = ans * s; \ RETURN ans; \ END" @@ -385,4 +388,12 @@ objID BIGINT, \ flags INT, \ + zp REAL, + zpErr REAL, + airMass REAL, + expTime REAL, + ra FLOAT, + dec_ FLOAT, + raErr REAL + decErr REAL, PRIMARY KEY (imageID, ippDetectID) \ )" Index: /trunk/ippToPsps/jython/stackbatch.py =================================================================== --- /trunk/ippToPsps/jython/stackbatch.py (revision 34629) +++ /trunk/ippToPsps/jython/stackbatch.py (revision 34630) @@ -197,6 +197,6 @@ sql = "UPDATE StackModelFit AS a, SkyChip_xfit AS b SET \ "+model+"Radius=b.EXT_WIDTH_MAJ, \ - "+model+"Flux=b.EXT_INST_MAG, \ - "+model+"FluxErr=b.EXT_INST_MAG_SIG, \ + "+model+"Flux=POW(10, -0.4 * b.EXT_INST_MAG) " + str(self.expTime) " , \ + "+model+"FluxErr=ABS(b.EXT_INST_MAG_SIG) * POW(10, -0.4 * b.EXT_INST_MAG) " + str(self.expTime) " / 1.085736, \ "+model+"Ab=b.EXT_WIDTH_MAJ/b.EXT_WIDTH_MIN, \ "+model+"Phi=b.EXT_THETA, \ @@ -230,6 +230,4 @@ "+modelLong+"Covar77=b.EXT_COVAR_06_06 \ WHERE a.ippDetectID=b.IPP_IDET AND b.MODEL_TYPE = '"+ippModelType+"'" - - #SA10 convert mag to flux in calculation above self.scratchDb.execute(sql) @@ -369,6 +367,6 @@ ,ABS((PSF_INST_MAG_SIG*(POW(10.0, (-0.4*PSF_INST_MAG)) / " + str(self.expTime) + ")) / 1.085736) \ ,POW(10.0, (-0.4*PEAK_FLUX_AS_MAG)) / " + str(self.expTime) + " \ - ,SKY \ - ,SKY_SIGMA \ + ,SKY / " + str(self.expTime) + " \ + ,SKY_SIGMA / " + str(self.expTime) + " \ ,EXT_NSIGMA \ ,PSF_MAJOR \ @@ -388,5 +386,5 @@ ,MOMENTS_R1 \ ,MOMENTS_RH \ - ,AP_MAG \ + ,POW(10.0, (-0.4*AP_MAG) / " + str(self.expTime) + " \ , NULL \ ,KRON_FLUX / " + str(self.expTime) + " \ @@ -398,8 +396,4 @@ ," + self.historyModNum + " \ FROM SkyChip_psf" - - #SA10 to do - # put in correct formula for apFlux, psfFlux, psfLikelihood, sgsep - # need correct units of anything in Flux self.scratchDb.execute(sql) @@ -481,6 +475,6 @@ petRadius=b.PETRO_RADIUS \ ,petRadiusErr=b.PETRO_RADIUS_ERR \ - ,petFlux=b.PETRO_MAG \ - ,petFluxErr=b.PETRO_MAG_ERR \ + ,petFlux=POW(10.0, -0.4 * b.PETRO_MAG) /" + str(self.expTime) + " \ + ,petFluxErr=ABS((b.PETRO_MAG_ERR * POW(10.0, (-0.4*b.petroMag))) / " + str(self.expTime) + " / 1.085736 \ ,petR50=b.PETRO_RADIUS_50 \ ,petR50Err=b.PETRO_RADIUS_50_ERR \ @@ -489,5 +483,4 @@ ,petCf=b.PETRO_FILL \ WHERE a.ippDetectID=b.IPP_IDET" - #SA10 Put correct calculation for PetFlux self.scratchDb.execute(sql) @@ -620,8 +613,15 @@ self.logger.info(sql) self.scratchDb.execute(sql) - #SA10 TODO - # insert ra/dec/calstuff into Table - # heather discovered dec is called 'dec_' - sql = "UPDATE " + tableName + " SET ra = -999, dec_ =-999, raErr = -999, decErr = -999, zp = -999, zpErr = -999, expTime = -999, airMass = -999" + # 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.zpErr = b.zpErr, \ + a.expTime = b.expTime, \ + a.airMass = b.airMass \ + WHERE a.objID = b.objID AND a.detectID = b.detectID" self.logger.info(sql) self.scratchDb.execute(sql)