Changeset 37246 for trunk/ippToPsps/jython/objectbatch.py
- Timestamp:
- Aug 12, 2014, 4:10:18 PM (12 years ago)
- File:
-
- 1 edited
-
trunk/ippToPsps/jython/objectbatch.py (modified) (13 diffs)
Legend:
- Unmodified
- Added
- Removed
-
trunk/ippToPsps/jython/objectbatch.py
r36744 r37246 15 15 from scratchdb import ScratchDb 16 16 from dvoobjects import DvoObjects 17 from sqlUtility import sqlUtility 17 18 18 19 import logging.config … … 37 38 scratchDb, 38 39 dvoID, 39 batchID, 40 useFullTables): 40 batchID): 41 41 42 42 super(ObjectBatch, self).__init__( … … 50 50 batchID, 51 51 "OB", 52 None, 53 1) 52 None) 54 53 55 54 try: 56 ## XX pass in the existing connection to scratchDb57 55 self.dvoObjects = DvoObjects(self.logger, self.config, self.skychunk, self.ippToPspsDb, self.scratchDb) 58 56 except: … … 74 72 self.region = self.scratchDb.getRegionNameFromThisDvoIndex(self.id) 75 73 self.ippToPspsDb.insertObjectMeta(self.batchID, self.region) 76 if True: 77 self.dvoObjects.nativeIngestRegion(self.region) 78 else: 79 self.dvoObjects.ingestRegion(self.region) 74 self.dvoObjects.nativeIngestRegion(self.region) 80 75 81 76 cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") … … 90 85 def alterPspsTables(self): 91 86 92 # dec is reserved in MySQL, so STILTS replaces if with dec_, which PSPS doesn't like. so, force it back again using ``93 # self.scratchDb.execute("ALTER TABLE MeanObject CHANGE dec_ `dec` double")94 # self.scratchDb.execute("ALTER TABLE ObjectThin CHANGE dec_ `dec` double")95 96 87 return True 97 88 … … 101 92 def indexIppTables(self): 102 93 103 self.logger.infoPair("Creating indexes on", "IPP tables") 94 # since dvopsps is now used, no action is needed here 95 # self.logger.infoPair("Creating indexes on", "IPP tables") 104 96 105 97 return True … … 109 101 ''' 110 102 def updateMeanObjectFromCps(self, cpsTable): 103 104 # list of all filters PSPS is interested in 105 # XXX EAM : 2014.07.24 : this list should probably be in a config file somewhere 106 interestedFilters = ['g', 'r', 'i', 'z', 'y'] 107 108 filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters) 109 110 # get a count of the available filters 111 filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable() 112 113 self.logger.infoPair("Available filters in Photcodes", filters) 114 115 # the 'code' now defines the order in the cps file that the mags are listed for a given filter 116 self.logger.infoPair("Adding magnitudes from", "cps table") 117 for filter in filters: 118 119 filterID = self.scratchDb.getFilterID(filter[1]) 120 121 # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000) 122 # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags 123 # XXX EAM : 20140724 this manipulation is no longer needed : [grizy]Flags is now 4 byte (was 8 byte!) 124 125 # set the MeanObject fields based largely on dvopsps cps fields: 126 127 # XXX EAM 20140724 : filterCount is meant to match 128 # Nsecfilt, but is potentially not determined correctly. 129 # use a call to a dvo-native command which knows how to 130 # find Nsecfilt (or save in the db with dvopsps) 131 132 # the math below depends on filterCount = Nsecfilt and MeanObject.row being 1 counting but cps being 0 counting? 133 # cps.row has a count of MeanObject.row * Nsecfilt + Nfilter 134 # " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ 135 136 sql = "UPDATE MeanObject JOIN \ 137 " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ 138 SET \ 139 MeanObject." + filter[1] + "QfPerfect = PSF_QF_PERF_MAX \ 140 ,MeanObject." + filter[1] + "MeanPSFMag = MAG \ 141 ,MeanObject." + filter[1] + "MeanPSFMagErr = MAG_ERR \ 142 ,MeanObject." + filter[1] + "MeanPSFMagStd = MAG_STDEV \ 143 ,MeanObject." + filter[1] + "MeanPSFMagMin = MAG_MIN \ 144 ,MeanObject." + filter[1] + "MeanPSFMagMax = MAG_MAX \ 145 ,MeanObject." + filter[1] + "MeanPSFMagNpt = NUSED \ 146 ,MeanObject." + filter[1] + "MeanKronMag = MAG_KRON \ 147 ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \ 148 ,MeanObject." + filter[1] + "MeanKronMagStd = MAG_KRON_STDEV \ 149 ,MeanObject." + filter[1] + "MeanKronMagNpt = NUSED_KRON \ 150 ,MeanObject." + filter[1] + "MeanApMag = MAG_AP \ 151 ,MeanObject." + filter[1] + "MeanApMagErr = MAG_AP_ERR \ 152 ,MeanObject." + filter[1] + "MeanApMagStd = MAG_AP_STDEV \ 153 ,MeanObject." + filter[1] + "MeanApMagNpt = NUSED_AP \ 154 ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) " 155 156 157 try: self.scratchDb.execute(sql) 158 except: 159 self.logger.errorPair("failed update MeanObject", sql) 160 raise 161 162 # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12) 163 # XXX EAM 20140724 : keep this cut? 164 cut = 0.5 165 self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut) 166 for filter in filters: 167 168 sql = "UPDATE MeanObject \ 169 SET " + filter[1] + "MeanPSFMagErr = null \ 170 WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut) 171 self.scratchDb.execute(sql) 172 173 ''' 174 Inserts stuff for all mags 175 ''' 176 def updateObjectThinFromCps(self, cpsTable): 111 177 112 178 # list of all filters PSPS is interested in … … 127 193 filterID = self.scratchDb.getFilterID(filter[1]) 128 194 129 # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000) 130 # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags 131 132 sql = "UPDATE MeanObject JOIN \ 133 " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ 134 SET \ 135 MeanObject." + filter[1] + "QfPerfect = -999 \ 136 ,MeanObject." + filter[1] + "MeanPSFMag = MAG \ 137 ,MeanObject." + filter[1] + "MeanPSFMagErr = MAG_ERR \ 138 ,MeanObject." + filter[1] + "MeanKronMag = MAG_KRON \ 139 ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \ 140 ,MeanObject." + filter[1] + "nIncPSFMag = -999 \ 141 ,MeanObject." + filter[1] + "MeanPSFMagStd = -999 \ 142 ,MeanObject." + filter[1] + "MinPSFMag = -999 \ 143 ,MeanObject." + filter[1] + "MaxPSFMag = -999 \ 144 ,MeanObject." + filter[1] + "nIncKronMag = -999 \ 145 ,MeanObject." + filter[1] + "MeanKronMag = -999 \ 146 ,MeanObject." + filter[1] + "MeanKronMagStd = -999 \ 147 ,MeanObject." + filter[1] + "MeanApMag = MAG_AP \ 148 ,MeanObject." + filter[1] + "MeanApMagErr = MAG_ERR \ 149 ,MeanObject." + filter[1] + "MeanApMagStd = MAG_STDEV \ 150 ,MeanObject." + filter[1] + "nIncApMag = -999 \ 151 ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) " 152 153 195 # XXX EAM 20140724 : this is quite awkward, add a objRow and ncode value to mysql db table? 196 # sqlLine = sqlUtility() 197 198 sql = "UPDATE ObjectThin JOIN " 199 sql += cpsTable + " AS cps " 200 sql += "ON (cps.row = (ObjectThin.row * " 201 sql += str(filterCount) + ")-(" 202 sql += str(filterCount) + " - " 203 sql += str(filter[0]) 204 sql += ")) " 205 206 sql += "SET ObjectThin.n" + filter[1] + " = NCODE, " 207 sql += "ObjectThin.nStackDetections = ObjectThin.nStackDetections + cps.NSTACK_DET" 154 208 self.logger.info(sql) 155 209 self.scratchDb.execute(sql) 156 210 157 # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12) 158 cut = 0.5 159 self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut) 160 for filter in filters: 161 162 sql = "UPDATE MeanObject \ 163 SET " + filter[1] + "MeanPSFMagErr = null \ 164 WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut) 165 self.scratchDb.execute(sql) 166 ''' 167 Inserts stuff for all mags 168 ''' 169 def updateObjectThinFromCps(self, cpsTable): 170 171 # list of all filters PSPS is interested in 172 interestedFilters = ['g', 'r', 'i', 'z', 'y'] 173 174 filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters) 175 176 # get a count of the available filters 177 filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable() 178 # filterCount = len(filters) 179 180 self.logger.infoPair("Available filters in Photcodes", filters) 181 182 # the 'code' now defines the order in the cps file that the mags are listed for a given filter 183 self.logger.infoPair("Adding magnitudes from", "cps table") 184 for filter in filters: 185 186 filterID = self.scratchDb.getFilterID(filter[1]) 187 188 # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000) 189 # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags 190 191 sql = "UPDATE ObjectThin JOIN \ 192 " + cpsTable + " AS cps ON (cps.row = (ObjectThin.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ 193 SET \ 194 ObjectThin.n" + filter[1] + " = NCODE " 195 self.logger.info(sql) 196 self.scratchDb.execute(sql) 197 211 # XXX this does not seem like a good thing to leave in SQL 198 212 self.logger.infoPair("Calculating nDetections from", "n[filters]") 199 213 for filter in filters: 200 214 # now do a sum of n[filters], but do not include the ones with -999 201 sql = "UPDATE ObjectThin \ 202 SET nDetections = nDetections + n" + filter[1] + " \ 203 WHERE n" + filter[1] + " != -999" 204 self.scratchDb.execute(sql) 205 206 215 sql = "UPDATE ObjectThin " 216 sql += "SET nDetections = nDetections + n" + filter[1] 217 sql += " WHERE n" + filter[1] + " != -999" 218 self.scratchDb.execute(sql) 207 219 208 220 ''' 209 221 give objectName to objectThin 222 XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps? 210 223 ''' 211 224 def updateObjName(self): … … 281 294 return False 282 295 283 284 285 286 287 288 289 296 ''' 290 297 Populates the Object table … … 296 303 297 304 if False: 305 # XXX EAM 20140724 : this is probably wrong : flux measurements can be 0.0 or negative: please review 298 306 self.logger.infoPair("setting to null > 1e-38 and < 1e-38 in", "cps FLUX_KRON_ERR") 299 307 sql = "UPDATE " + cpsTableName + " set FLUX_KRON_ERR = NULL where FLUX_KRON_ERR < 1e-37 AND FLUX_KRON_ERR > -1e-37 " 308 self.exitProgram("review this code" + sql) 300 309 301 310 try: … … 315 324 316 325 317 self.logger.infoPair("Populating", "ThinObject") 318 self.logger.infoPair("Inserting objects from", "cpt file") 319 320 # note `` around dec here, as this is a reserved word in MySQL 321 sql = "INSERT IGNORE INTO ObjectThin (\ 322 objID \ 323 ,gcobjID \ 324 ,ippObjID \ 325 ,surveyID \ 326 ,skyCellID \ 327 ,randomID \ 328 ,batchID \ 329 ,dvoRegionID \ 330 ,dataRelease \ 331 ,objInfoFlag \ 332 ,qualityFlag \ 333 ,consistencyFlag \ 334 ,raStack \ 335 ,decStack \ 336 ,raStackErr \ 337 ,decStackErr \ 338 ,raMean \ 339 ,decMean \ 340 ,raMeanErr \ 341 ,decMeanErr \ 342 ,raMeanStd \ 343 ,decMeanStd \ 344 ,nStackObjectRows \ 345 ,nStackDetections \ 346 ,nDetections \ 347 ) \ 348 SELECT \ 349 EXT_ID \ 350 , -999 \ 351 ,CAT_ID*1000000000 + OBJ_ID \ 352 ," + str(self.surveyID) + " \ 353 , -999 \ 354 , FLOOR(RAND()*9223372036854775807) \ 355 , " + str(self.batchID) + "\ 356 , -999 \ 357 , " + str(self.skychunk.dataRelease) + "\ 358 ,FLAGS \ 359 ,FLAGS >> 24 & 0xFF \ 360 , 0 \ 361 , -999 \ 362 , -999 \ 363 , -999 \ 364 , -999 \ 365 ,RA \ 366 ,DEC_ \ 367 ,RA_ERR \ 368 ,DEC_ERR \ 369 , -999 \ 370 , -999 \ 371 ,0 \ 372 ,0 \ 373 ,0 \ 374 FROM " + cptTableName 326 self.logger.info("Populating ThinObject") 327 self.logger.info("Inserting objects from cpt file") 328 329 # note "dec" is a reserved word in MySQL 330 # XXX EAM 20140724 : do not use INGORE unless we discover unavoidable problems... 331 # INSERT IGNORE INTO ObjectThin 332 333 sqlLine = sqlUtility("INSERT INTO ObjectThin (") 334 335 sqlLine.group("objID", "EXT_ID") 336 sqlLine.group("ippObjID", "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly 337 sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") 338 sqlLine.group("randomID", "FLOOR(RAND()*9223372036854775807)") # XXX where does this number come from?? 339 sqlLine.group("batchID", "'" + str(self.batchID) + "'") 340 sqlLine.group("dvoRegionID", "CAT_ID") 341 sqlLine.group("tessID", "TESS_ID") 342 sqlLine.group("projectionID", "PROJECTION_ID") 343 sqlLine.group("skycellID", "SKYCELL_ID") 344 sqlLine.group("dataRelease", "'" + str(self.skychunk.dataRelease) + "'") 345 sqlLine.group("objInfoFlag", "FLAGS") 346 sqlLine.group("qualityFlag", "FLAGS >> 24 & 0xFF") 347 sqlLine.group("consistencyFlag", "'0'") 348 sqlLine.group("raStack", "RA_STK") 349 sqlLine.group("decStack", "DEC_STK") 350 sqlLine.group("raStackErr", "RA_STK_ERR") 351 sqlLine.group("decStackErr", "DEC_STK_ERR") 352 sqlLine.group("raMean", "RA_MEAN") 353 sqlLine.group("decMean", "DEC_MEAN") 354 sqlLine.group("raMeanErr", "RA_ERR") 355 sqlLine.group("decMeanErr", "DEC_ERR") 356 sqlLine.group("posMeanChisq", "CHISQ_POS") 357 sqlLine.group("nStackObjectRows", "'0'") # XXX I need to add / define this in dvopsps 358 sqlLine.group("nStackDetections", "'0'") 359 sqlLine.group("nDetections", "'0'") 360 sql = sqlLine.makeRaw(") SELECT ", " FROM " + cptTableName) 375 361 376 362 try: … … 390 376 self.updateObjName() 391 377 392 393 378 self.logger.infoPair("update ObjectThin from ","cps table") 394 379 395 380 self.updateObjectThinFromCps(cpsTableName) 396 381 382 # XXX EAM 20140724 : is this necessary?? 397 383 #objects can have out of range ra dec in dvo - need to find and kill them at the end 398 384 … … 455 441 self.logger.infoPair("Populating MeanObject from ", "ObjectThin") 456 442 457 sql = "INSERT INTO MeanObject ( \ 458 objID \ 459 ,gcobjID \ 460 ) \ 461 SELECT \ 462 objID \ 463 ,gcobjID \ 464 FROM ObjectThin" 443 sql = "INSERT INTO MeanObject ( objID ) SELECT objID FROM ObjectThin" 465 444 try: 466 445 self.scratchDb.execute(sql)
Note:
See TracChangeset
for help on using the changeset viewer.
