Changeset 34630

Timestamp:

Oct 31, 2012, 11:54:39 AM (14 years ago)

Author:

bills

Message:

Changes to PSPS table definitions for SA 10.
Detection tables contain all fluxes
Object table contains all magnitudes
Populate DetectionCalib and StackDetectinCalib

Location:

trunk/ippToPsps/jython

Files:

• detectionbatch.py (modified) (4 diffs)
• dvodetections.py (modified) (7 diffs)
• objectbatch.py (modified) (2 diffs)
• scratchdb.py (modified) (3 diffs)
• stackbatch.py (modified) (8 diffs)

trunk/ippToPsps/jython/detectionbatch.py

@@ -452 +452 @@
                ,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 +458 @@
                ,PSF_CHISQ \
                ,FLAGS2 << 32 | FLAGS\
-               ,SKY \
-               ,SKY_SIGMA \
+               ,SKY  / " + self.header['EXPTIME'] + " \
+               ,SKY_SIGMA  / " + self.header['EXPTIME'] + " \
                ,EXT_NSIGMA \
                , 0 \
@@ -466 +466 @@
                , " + 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 +549 @@
                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))

trunk/ippToPsps/jython/dvodetections.py

@@ -44 +44 @@
            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 +50 @@
            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 +62 @@
        self.importFits(
                cptPath,
-               "OBJ_ID CAT_ID EXT_ID",
+               "OBJ_ID CAT_ID EXT_ID RA DEC",
                cptTableName)
 
@@ -67 +69 @@
        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 +106 @@
        # 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 +115 @@
               ,objID \
               ,flags \
+              ,zp   \
+              ,zpErr    \
+              ,airMass  \
+              ,expTime  \
+              ,ra   \
+              ,dec_  \
+              ,raErr    \
+              ,decErr   \
               ,fileID \
               ) SELECT \
@@ -102 +131 @@
               ,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)

trunk/ippToPsps/jython/objectbatch.py

@@ -113 +113 @@
         self.logger.infoPair("Adding magnitudes from", "cps table")
         for filter in filters:
+
+            filterID = self.scratchDb.getFilterID(filter[1])
 
             sql = "UPDATE Object JOIN \
@@ -129 +131 @@
                    ," + 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)

trunk/ippToPsps/jython/scratchdb.py

@@ -68 +68 @@
                DECLARE t REAL; \
                DECLARE z REAL; \
+               DECLARE s INT; \
+               SET s= SIGN(x); \
                SET x = ABS(x) / 1.4142135623731; \
                SET z=ABS(x); \
@@ -75 +77 @@
                SET ans = 2.0 - ans; \
                END IF; \
+               SET ans = ans * s; \
                RETURN ans; \
                END"
@@ -385 +388 @@
                objID BIGINT, \
                flags INT, \
+               zp REAL,
+               zpErr REAL,
+               airMass REAL,
+               expTime REAL,
+               ra FLOAT,
+               dec_ FLOAT,
+               raErr REAL
+               decErr REAL,
                PRIMARY KEY (imageID, ippDetectID) \
                )"

trunk/ippToPsps/jython/stackbatch.py

@@ -197 +197 @@
         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 +230 @@
         "+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 +367 @@
                ,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 +386 @@
                ,MOMENTS_R1 \
                ,MOMENTS_RH \
-               ,AP_MAG \
+               ,POW(10.0, (-0.4*AP_MAG) / " + str(self.expTime) + " \
                , NULL \
                ,KRON_FLUX / " + str(self.expTime) + " \
@@ -398 +396 @@
                ," + 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 +475 @@
         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 +483 @@
         ,petCf=b.PETRO_FILL \
         WHERE a.ippDetectID=b.IPP_IDET"
-        #SA10 Put correct calculation for PetFlux
         self.scratchDb.execute(sql)
 
@@ -620 +613 @@
         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)