- Timestamp:
- Jan 12, 2015, 12:24:24 PM (12 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/eam_branches/ipp-pv3-20140717-merge/ippToPsps/jython/objectbatch.py
r36744 r37820 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 98 89 ''' 90 Updates table and generates pspsuniqueids 91 ''' 92 93 def updatePspsUniqueIDs(self): 94 sql = "UPDATE ObjectThin join (select @r:=@r+1 rownum, objID from \ 95 (select @r:=0) r, ObjectThin t) as foo using (objID) set \ 96 uniquePspsOBid = ((" +str(self.batchID) +"*1000000000 ) + rownum)"; 97 try: self.scratchDb.execute(sql) 98 except: 99 self.logger.errorPair('failed sql',sql) 100 raise 101 102 103 104 ''' 99 105 Applies indexes to the IPP tables 100 106 ''' 101 107 def indexIppTables(self): 102 108 103 self.logger.infoPair("Creating indexes on", "IPP tables") 109 # since dvopsps is now used, no action is needed here 110 # self.logger.infoPair("Creating indexes on", "IPP tables") 104 111 105 112 return True … … 109 116 ''' 110 117 def updateMeanObjectFromCps(self, cpsTable): 118 119 # list of all filters PSPS is interested in 120 # XXX EAM : 2014.07.24 : this list should probably be in a config file somewhere 121 interestedFilters = ['g', 'r', 'i', 'z', 'y'] 122 123 filters = self.scratchDb.getOrderedListOfFiltersFromPhotcodesTable(interestedFilters) 124 125 # get a count of the available filters 126 filterCount = self.scratchDb.getCountOfFiltersFromPhotcodesTable() 127 128 self.logger.infoPair("Available filters in Photcodes", filters) 129 130 # the 'code' now defines the order in the cps file that the mags are listed for a given filter 131 self.logger.infoPair("Adding magnitudes from", "cps table") 132 for filter in filters: 133 134 filterID = self.scratchDb.getFilterID(filter[1]) 135 136 # NOTE: Manipulation of FLAGS from cpsTable is to move ID_SECF_OBJ_EXT flag (0x01000000) 137 # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags 138 # XXX EAM : 20140724 this manipulation is no longer needed : [grizy]Flags is now 4 byte (was 8 byte!) 139 140 # set the MeanObject fields based largely on dvopsps cps fields: 141 142 # XXX EAM 20140724 : filterCount is meant to match 143 # Nsecfilt, but is potentially not determined correctly. 144 # use a call to a dvo-native command which knows how to 145 # find Nsecfilt (or save in the db with dvopsps) 146 147 # the math below depends on filterCount = Nsecfilt and MeanObject.row being 1 counting but cps being 0 counting? 148 # cps.row has a count of MeanObject.row * Nsecfilt + Nfilter 149 # " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ 150 151 sql = "UPDATE MeanObject JOIN \ 152 " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ 153 SET \ 154 MeanObject." + filter[1] + "QfPerfect = PSF_QF_PERF_MAX \ 155 ,MeanObject." + filter[1] + "MeanPSFMag = MAG \ 156 ,MeanObject." + filter[1] + "MeanPSFMagErr = MAG_ERR \ 157 ,MeanObject." + filter[1] + "MeanPSFMagStd = MAG_STDEV \ 158 ,MeanObject." + filter[1] + "MeanPSFMagMin = MAG_MIN \ 159 ,MeanObject." + filter[1] + "MeanPSFMagMax = MAG_MAX \ 160 ,MeanObject." + filter[1] + "MeanPSFMagNpt = NUSED \ 161 ,MeanObject." + filter[1] + "MeanKronMag = MAG_KRON \ 162 ,MeanObject." + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \ 163 ,MeanObject." + filter[1] + "MeanKronMagStd = MAG_KRON_STDEV \ 164 ,MeanObject." + filter[1] + "MeanKronMagNpt = NUSED_KRON \ 165 ,MeanObject." + filter[1] + "MeanApMag = MAG_AP \ 166 ,MeanObject." + filter[1] + "MeanApMagErr = MAG_AP_ERR \ 167 ,MeanObject." + filter[1] + "MeanApMagStd = MAG_AP_STDEV \ 168 ,MeanObject." + filter[1] + "MeanApMagNpt = NUSED_AP \ 169 ,MeanObject." + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) " 170 171 172 try: self.scratchDb.execute(sql) 173 except: 174 self.logger.errorPair("failed update MeanObject", sql) 175 raise 176 177 # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12) 178 # XXX EAM 20140724 : keep this cut? 179 cut = 0.5 180 self.logger.infoPair("Setting to NULL all MeanMagErr value >", "%f" % cut) 181 for filter in filters: 182 183 sql = "UPDATE MeanObject \ 184 SET " + filter[1] + "MeanPSFMagErr = null \ 185 WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut) 186 self.scratchDb.execute(sql) 187 188 ''' 189 Inserts stuff for all mags 190 ''' 191 def updateObjectThinFromCps(self, cpsTable): 111 192 112 193 # list of all filters PSPS is interested in … … 127 208 filterID = self.scratchDb.getFilterID(filter[1]) 128 209 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 210 # XXX EAM 20140724 : this is quite awkward, add a objRow and ncode value to mysql db table? 211 # sqlLine = sqlUtility() 212 213 sql = "UPDATE ObjectThin JOIN " 214 sql += cpsTable + " AS cps " 215 sql += "ON (cps.row = (ObjectThin.row * " 216 sql += str(filterCount) + ")-(" 217 sql += str(filterCount) + " - " 218 sql += str(filter[0]) 219 sql += ")) " 220 221 sql += "SET ObjectThin.n" + filter[1] + " = NCODE, " 222 sql += "ObjectThin.nStackDetections = ObjectThin.nStackDetections + cps.NSTACK_DET" 154 223 self.logger.info(sql) 155 224 self.scratchDb.execute(sql) 156 225 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 226 # XXX this does not seem like a good thing to leave in SQL 198 227 self.logger.infoPair("Calculating nDetections from", "n[filters]") 199 228 for filter in filters: 200 229 # 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 230 sql = "UPDATE ObjectThin " 231 sql += "SET nDetections = nDetections + n" + filter[1] 232 sql += " WHERE n" + filter[1] + " != -999" 233 self.scratchDb.execute(sql) 207 234 208 235 ''' 209 236 give objectName to objectThin 237 XXX EAM 20140714 : This seems quite inefficient in SQL, move to dvopsps? 210 238 ''' 211 239 def updateObjName(self): … … 281 309 return False 282 310 283 284 285 286 287 288 289 311 ''' 290 312 Populates the Object table … … 296 318 297 319 if False: 320 # XXX EAM 20140724 : this is probably wrong : flux measurements can be 0.0 or negative: please review 298 321 self.logger.infoPair("setting to null > 1e-38 and < 1e-38 in", "cps FLUX_KRON_ERR") 299 322 sql = "UPDATE " + cpsTableName + " set FLUX_KRON_ERR = NULL where FLUX_KRON_ERR < 1e-37 AND FLUX_KRON_ERR > -1e-37 " 323 self.exitProgram("review this code" + sql) 300 324 301 325 try: … … 315 339 316 340 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 341 self.logger.info("Populating ThinObject") 342 self.logger.info("Inserting objects from cpt file") 343 344 # note "dec" is a reserved word in MySQL 345 # XXX EAM 20140724 : do not use INGORE unless we discover unavoidable problems... 346 # INSERT IGNORE INTO ObjectThin 347 348 sqlLine = sqlUtility("INSERT INTO ObjectThin (") 349 350 sqlLine.group("objID", "EXT_ID") 351 sqlLine.group("ippObjID", "OBJ_ID + (CAT_ID << 32)") # NOTE: shift by 32 bits exactly 352 sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") 353 sqlLine.group("randomID", "FLOOR(RAND("+str(self.batchID)+")*9223372036854775807)") # XXX where does this number come from?? 354 sqlLine.group("batchID", "'" + str(self.batchID) + "'") 355 sqlLine.group("dvoRegionID", "CAT_ID") 356 sqlLine.group("tessID", "TESS_ID") 357 sqlLine.group("projectionID", "PROJECTION_ID") 358 sqlLine.group("skycellID", "SKYCELL_ID") 359 sqlLine.group("dataRelease", "'" + str(self.skychunk.dataRelease) + "'") 360 sqlLine.group("objInfoFlag", "FLAGS") 361 sqlLine.group("qualityFlag", "FLAGS >> 24 & 0xFF") 362 sqlLine.group("consistencyFlag", "'0'") 363 sqlLine.group("raStack", "RA_STK") 364 sqlLine.group("decStack", "DEC_STK") 365 sqlLine.group("raStackErr", "RA_STK_ERR") 366 sqlLine.group("decStackErr", "DEC_STK_ERR") 367 sqlLine.group("raMean", "RA_MEAN") 368 sqlLine.group("decMean", "DEC_MEAN") 369 sqlLine.group("raMeanErr", "RA_ERR") 370 sqlLine.group("decMeanErr", "DEC_ERR") 371 sqlLine.group("posMeanChisq", "CHISQ_POS") 372 sqlLine.group("nStackObjectRows", "'0'") # XXX I need to add / define this in dvopsps 373 sqlLine.group("nStackDetections", "'0'") 374 sqlLine.group("nDetections", "'0'") 375 sql = sqlLine.makeRaw(") SELECT ", " FROM " + cptTableName) 375 376 376 377 try: … … 390 391 self.updateObjName() 391 392 392 393 393 self.logger.infoPair("update ObjectThin from ","cps table") 394 394 395 395 self.updateObjectThinFromCps(cpsTableName) 396 396 397 # XXX EAM 20140724 : is this necessary?? 397 398 #objects can have out of range ra dec in dvo - need to find and kill them at the end 398 399 … … 424 425 425 426 sql = "DELETE FROM ObjectThin where \ 426 ObjectThin.decMean > " + str(decMax) + "or \427 ObjectThin.decMean < " + str(decMin) + "or \428 ObjectThin.raMean > " + str(raMax) + "or \429 ObjectThin.raMean < " + str(raMin)427 ObjectThin.decMean > (" + str(decMax) + " + .0033) or \ 428 ObjectThin.decMean < (" + str(decMin) + " - .0033) or \ 429 ObjectThin.raMean > (" + str(raMax) + " + .0033) or \ 430 ObjectThin.raMean < (" + str(raMin) + " - .0033)" 430 431 self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range") 431 432 … … 443 444 ##self.dvoObjects.purgeRegion(self.region) 444 445 446 self.updatePspsUniqueIDs() 447 445 448 self.setMinMaxObjID(["ObjectThin"]) 446 449 … … 453 456 cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") 454 457 cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps") 455 self.logger.infoPair("Populating MeanObject from ", "ObjectThin") 456 457 sql = "INSERT INTO MeanObject ( \ 458 objID \ 459 ,gcobjID \ 460 ) \ 461 SELECT \ 462 objID \ 463 ,gcobjID \ 464 FROM ObjectThin" 458 self.logger.infoPair("Populating MeanObject.objid and uniquePspsOBid from ", "ObjectThin") 459 460 sql = "INSERT INTO MeanObject ( objID, uniquePspsOBid ) SELECT objID, uniquePspsOBid FROM ObjectThin" 465 461 try: 466 462 self.scratchDb.execute(sql) … … 471 467 self.logger.infoPair("Adding 'row' columns to", "MeanObject table") 472 468 self.scratchDb.addRowCountColumn("MeanObject", "row") 473 469 470 471 472 474 473 475 474 ##self.scratchDb.addRowCountColumn(cpsTableName, "row")
Note:
See TracChangeset
for help on using the changeset viewer.
