- Timestamp:
- Jul 17, 2014, 12:30:45 PM (12 years ago)
- Location:
- branches/eam_branches/ipp-ops-20130712/ippToPsps
- Files:
-
- 2 edited
-
. (modified) (1 prop)
-
jython/objectbatch.py (modified) (12 diffs)
Legend:
- Unmodified
- Added
- Removed
-
branches/eam_branches/ipp-ops-20130712/ippToPsps
- Property svn:mergeinfo deleted
-
branches/eam_branches/ipp-ops-20130712/ippToPsps/jython/objectbatch.py
r35496 r37066 91 91 92 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 Object CHANGE dec_ `dec` double") 93 # self.scratchDb.execute("ALTER TABLE MeanObject CHANGE dec_ `dec` double") 94 # self.scratchDb.execute("ALTER TABLE ObjectThin CHANGE dec_ `dec` double") 94 95 95 96 return True … … 107 108 Inserts stuff for all mags 108 109 ''' 109 def insertMags(self, cpsTable):110 def updateMeanObjectFromCps(self, cpsTable): 110 111 111 112 # list of all filters PSPS is interested in … … 129 130 # from bit 24 to bit 13 so that it fits into the SMALLINT Object.Flags 130 131 131 sql = "UPDATE Object JOIN \132 " + cpsTable + " AS cps ON (cps.row = ( Object.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \132 sql = "UPDATE MeanObject JOIN \ 133 " + cpsTable + " AS cps ON (cps.row = (MeanObject.row* " + str(filterCount) + ")-(" + str(filterCount) + " - " + str(filter[0]) + ")) \ 133 134 SET \ 134 n" + filter[1] + " = NCODE \ 135 ," + filter[1] + "MeanPSFMag = MAG \ 136 ," + filter[1] + "MeanPSFMagErr = MAG_ERR \ 137 ," + filter[1] + "MeanKronMag = MAG_KRON \ 138 ," + filter[1] + "MeanKronMagErr = MAG_KRON_ERR \ 139 ," + filter[1] + "StackPSFMag = STACK_PSF_MAG \ 140 ," + filter[1] + "StackPSFMagErr = STACK_PSF_MAG_ERR \ 141 ," + filter[1] + "StackKronMag = STACK_KRON_MAG \ 142 ," + filter[1] + "StackKronMagErr = STACK_KRON_MAG_ERR \ 143 ," + filter[1] + "20pct = MAG_20/1000 \ 144 ," + filter[1] + "80pct = MAG_80/1000 \ 145 ," + filter[1] + "Flags = (0x7fff & FLAGS) | ((FLAGS >> 11) & 0x2000) \ 146 ," + filter[1] + "StackDetectID = STACK_DETECT_ID" 147 148 # old method for setting the Stack (PSF,Kron) Mags: 149 # ," + filter[1] + "StackPSFMag = -2.5 * log10(FLUX_PSF) + 8.9 \ 150 # ," + filter[1] + "StackPSFMagErr = FLUX_PSF_ERR / FLUX_PSF \ 151 # ," + filter[1] + "StackKronMag = -2.5 * log10(FLUX_KRON) + 8.9 \ 152 # ," + filter[1] + "StackKronMagErr = FLUX_KRON_ERR / FLUX_KRON \ 153 154 #self.logger.info(sql) 155 self.scratchDb.execute(sql) 156 157 self.logger.infoPair("Calculating nDetections from", "n[filters]") 158 for filter in filters: 159 # now do a sum of n[filters], but do not include the ones with -999 160 sql = "UPDATE Object \ 161 SET nDetections = nDetections + n" + filter[1] + " \ 162 WHERE n" + filter[1] + " != -999" 163 self.scratchDb.execute(sql) 164 165 166 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 154 self.logger.info(sql) 155 self.scratchDb.execute(sql) 167 156 168 157 # now set to null all MeanMagErr values > 0.5 (cut set by Gene, 2012-04-12) … … 171 160 for filter in filters: 172 161 173 sql = "UPDATE Object \162 sql = "UPDATE MeanObject \ 174 163 SET " + filter[1] + "MeanPSFMagErr = null \ 175 164 WHERE " + filter[1] + "MeanPSFMagErr > " + str(cut) 176 165 self.scratchDb.execute(sql) 177 178 self.logger.infoPair("Setting to NULL all x20pct =", "32.767" ) 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") 179 184 for filter in filters: 180 sql = "UPDATE Object \ 181 SET " + filter[1] + "20pct = null \ 182 WHERE " + filter[1] + "20pct > 32.766" 183 self.scratchDb.execute(sql) 184 self.logger.infoPair("Setting to NULL all x80pct =", "32.767" ) 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 198 self.logger.infoPair("Calculating nDetections from", "n[filters]") 185 199 for filter in filters: 186 sql = "UPDATE Object \ 187 SET " + filter[1] + "80pct = null \ 188 WHERE " + filter[1] + "80pct > 32.766" 189 self.scratchDb.execute(sql) 190 191 192 193 194 ''' 195 Inserts colors 196 ''' 197 def updateColors(self): 198 199 self.logger.infoPair("Calculating", "colors") 200 sql = "UPDATE Object \ 201 SET \ 202 grMeanColor = (gMeanMag - rMeanMag) \ 203 ,riMeanColor = (rMeanMag - iMeanMag) \ 204 ,izMeanColor = (iMeanMag - zMeanMag) \ 205 ,zyMeanColor = (zMeanMag - yMeanMag) \ 206 ,grMeanColorErr = SQRT(POW(gMeanMagErr,2) + POW(rMeanMagErr,2)) \ 207 ,riMeanColorErr = SQRT(POW(rMeanMagErr,2) + POW(iMeanMagErr,2)) \ 208 ,izMeanColorErr = SQRT(POW(iMeanMagErr,2) + POW(zMeanMagErr,2)) \ 209 ,zyMeanColorErr = SQRT(POW(zMeanMagErr,2) + POW(yMeanMagErr,2)) \ 210 " 211 try: 212 self.scratchDb.execute(sql) 213 except: 214 self.logger.errorPair("Couldn't calculate colors", sql) 200 # 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 207 208 ''' 209 give objectName to objectThin 210 ''' 211 def updateObjName(self): 212 ##this is a 4 part update: 213 self.logger.infoPair("updating objName", "using stacks") 214 ## if we have a stackra and dec, use stackra and dec , dec >=0 215 216 sql = "update ObjectThin set objName = concat('PS1.2 J', \ 217 lpad(floor(raStack/15.),2,'0'), \ 218 lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \ 219 lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \ 220 '+', \ 221 lpad(floor(decStack),2,'0'), \ 222 lpad((decStack-(floor(decStack)))*60. ,2,'0'), \ 223 lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \ 224 ) where raStack > -999 and decStack > -999 and decStack >= 0" 225 try: 226 self.scratchDb.execute(sql) 227 except: 228 self.logger.errorPair("failed sql", sql) 229 return False 230 231 232 ## if we have a stackra and dec, use stackra and dec, dec < 0 233 234 sql = "update ObjectThin set objName = concat('PS1.2 J', \ 235 lpad(floor(raStack/15.),2,'0'), \ 236 lpad((raStack/15.-(floor(raStack/15.)))*60. ,2,'0'), \ 237 lpad(format(((raStack/15.-(floor(raStack/15.)))*60.-floor((raStack/15.-(floor(raStack/15.)))*60.))*60.,2),4,'0'), \ 238 '-', \ 239 lpad(floor(decStack),2,'0'), \ 240 lpad((decStack-(floor(decStack)))*60. ,2,'0'), \ 241 lpad(format(((decStack-(floor(decStack)))*60.-floor((decStack-(floor(decStack)))*60.))*60.,2),4,'0') \ 242 ) where raStack > -999 and decStack > -999 and decStack <= 0" 243 try: 244 self.scratchDb.execute(sql) 245 except: 246 self.logger.errorPair("failed sql", sql) 247 return False 248 self.logger.infoPair("updating objName", "using means") 249 ## use mean ra and dec (dec >= 0) 250 sql = "update ObjectThin set objName = concat('PS1.2 J', \ 251 lpad(floor(raMean/15.),2,'0'), \ 252 lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \ 253 lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \ 254 '+', \ 255 lpad(floor(decMean),2,'0'), \ 256 lpad((decMean-(floor(decMean)))*60. ,2,'0'), \ 257 lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \ 258 ) where raMean > -999 and decMean > -999 and decMean >= 0" 259 try: 260 self.scratchDb.execute(sql) 261 except: 262 self.logger.errorPair("failed sql", sql) 263 return False 264 265 ## use mean ra and dec (dec < 0) 266 267 sql = "update ObjectThin set objName = concat('PS1.2 J', \ 268 lpad(floor(raMean/15.),2,'0'), \ 269 lpad((raMean/15.-(floor(raMean/15.)))*60. ,2,'0'), \ 270 lpad(format(((raMean/15.-(floor(raMean/15.)))*60.-floor((raMean/15.-(floor(raMean/15.)))*60.))*60.,2),4,'0'), \ 271 '-', \ 272 lpad(floor(decMean),2,'0'), \ 273 lpad((decMean-(floor(decMean)))*60. ,2,'0'), \ 274 lpad(format(((decMean-(floor(decMean)))*60.-floor((decMean-(floor(decMean)))*60.))*60.,2),4,'0') \ 275 ) where raMean > -999 and decMean > -999 and decMean < 0" 276 277 try: 278 self.scratchDb.execute(sql) 279 except: 280 self.logger.errorPair("failed sql", sql) 281 return False 282 283 284 285 286 287 215 288 216 289 ''' 217 290 Populates the Object table 218 291 ''' 219 def populateObjectT able(self):292 def populateObjectThinTable(self): 220 293 221 294 cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") … … 242 315 243 316 244 self.logger.infoPair("Populating", " Object s")317 self.logger.infoPair("Populating", "ThinObject") 245 318 self.logger.infoPair("Inserting objects from", "cpt file") 246 319 247 320 # note `` around dec here, as this is a reserved word in MySQL 248 sql = "INSERT IGNORE INTO Object (\321 sql = "INSERT IGNORE INTO ObjectThin (\ 249 322 objID \ 323 ,gcobjID \ 250 324 ,ippObjID \ 325 ,surveyID \ 326 ,skyCellID \ 327 ,randomID \ 328 ,batchID \ 329 ,dvoRegionID \ 330 ,dataRelease \ 251 331 ,objInfoFlag \ 252 332 ,qualityFlag \ 253 ,surveyID \ 254 ,ra \ 255 ,`dec` \ 256 ,raErr \ 257 ,decErr \ 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 \ 258 346 ,nDetections \ 259 ,qfPerfect \ 260 ,sgSep \ 261 ,dataRelease \ 262 ,Random \ 263 ,batchID \ 264 ) \ 347 ) \ 265 348 SELECT \ 266 349 EXT_ID \ 350 , -999 \ 267 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) + "\ 268 358 ,FLAGS \ 269 359 ,FLAGS >> 24 & 0xFF \ 270 ," + str(self.surveyID) + " \ 360 , 0 \ 361 , -999 \ 362 , -999 \ 363 , -999 \ 364 , -999 \ 271 365 ,RA \ 272 366 ,DEC_ \ 273 367 ,RA_ERR \ 274 368 ,DEC_ERR \ 369 , -999 \ 370 , -999 \ 275 371 ,0 \ 276 ,PSF_QF_PERF \ 277 ,STARGAL_SEP \ 278 , " + str(self.skychunk.dataRelease) + "\ 279 , RAND() \ 280 , " + str(self.batchID) + "\ 281 FROM " + cptTableName 372 ,0 \ 373 ,0 \ 374 FROM " + cptTableName 282 375 283 376 try: … … 289 382 # add row count columns so we can perform joins to get colors 290 383 self.logger.infoPair("Adding 'row' columns to", "Object and cps tables") 291 self.scratchDb.addRowCountColumn("Object ", "row")384 self.scratchDb.addRowCountColumn("ObjectThin", "row") 292 385 self.scratchDb.addRowCountColumn(cpsTableName, "row") 293 386 294 self.insertMags(cpsTableName) 295 296 #don't do this as we removed those columns.. 297 # self.updateColors() 387 ## self.insertMeanMags(cpsTableName) 388 ## this is the new version 389 self.logger.infoPair("update objName for ","objectThin") 390 self.updateObjName() 391 392 393 self.logger.infoPair("update ObjectThin from ","cps table") 394 395 self.updateObjectThinFromCps(cpsTableName) 298 396 299 397 #objects can have out of range ra dec in dvo - need to find and kill them at the end … … 312 410 #count out of range 313 411 314 sql = "SELECT count(*) FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin) 412 sql = "SELECT count(*) FROM ObjectThin where \ 413 ObjectThin.decMean > " + str(decMax) + " \ 414 or ObjectThin.decMean < " + str(decMin) + " \ 415 or ObjectThin.raMean > " + str(raMax) + " \ 416 or ObjectThin.raMean < " + str(raMin) 315 417 316 418 rs = self.scratchDb.executeQuery(sql) … … 321 423 322 424 323 sql = "DELETE FROM Object where Object.dec > " + str(decMax) + " or Object.dec < " + str(decMin) + " or Object.ra > " + str(raMax) + " or Object.ra < " + str(raMin) 425 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) 324 430 self.logger.infoPair("Deleting", str(nToDelete) + " objects outside of ra/dec range") 325 431 … … 327 433 self.scratchDb.execute(sql) 328 434 except: 329 self.logger.errorPair("Couldn't cull outsiders from Object table", sql) 330 return False 331 332 self.logger.infoPair("Dropping row column from", "Object table") 333 self.scratchDb.dropColumn("Object", "row") 334 self.logger.infoPair("Purging from scratch Db", self.region + " region") 335 336 self.dvoObjects.purgeRegion(self.region) 337 338 self.setMinMaxObjID(["Object"]) 435 self.logger.errorPair("Couldn't cull outsiders from ObjectThin table", sql) 436 return False 437 438 self.logger.infoPair("Dropping row column from", "ObjectThin table") 439 self.scratchDb.dropColumn("ObjectThin", "row") 440 ##self.logger.infoPair("Purging from scratch Db", self.region + " region") 441 442 ##Don't do this till after MeanObject 443 ##self.dvoObjects.purgeRegion(self.region) 444 445 self.setMinMaxObjID(["ObjectThin"]) 339 446 340 447 return True … … 343 450 Populates the ObjectCalColor table 344 451 ''' 345 def populateObjectCalColorTable(self): 346 347 self.logger.infoPair("Populating", "ObjectCalColor") 348 349 sql = "INSERT INTO ObjectCalColor ( \ 452 def populateMeanObjectTable(self): 453 cptTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cpt") 454 cpsTableName = self.scratchDb.getDbFriendlyTableName(self.region + ".cps") 455 self.logger.infoPair("Populating MeanObject from ", "ObjectThin") 456 457 sql = "INSERT INTO MeanObject ( \ 350 458 objID \ 351 ,ippObjID \ 352 ,dataRelease \ 459 ,gcobjID \ 353 460 ) \ 354 461 SELECT \ 355 462 objID \ 356 ,ippObjID \ 357 ,dataRelease \ 358 FROM Object" 359 try: 360 self.scratchDb.execute(sql) 361 except: 362 self.logger.errorPair("Couldn't populate ObjectCalColor table", sql) 363 return False 463 ,gcobjID \ 464 FROM ObjectThin" 465 try: 466 self.scratchDb.execute(sql) 467 except: 468 self.logger.errorPair("Couldn't populate MeanObject table", sql) 469 return False 470 471 self.logger.infoPair("Adding 'row' columns to", "MeanObject table") 472 self.scratchDb.addRowCountColumn("MeanObject", "row") 473 474 475 ##self.scratchDb.addRowCountColumn(cpsTableName, "row") 476 self.logger.infoPair("update MeanObjects from ","cps table") 477 self.updateMeanObjectFromCps(cpsTableName) 478 479 self.logger.infoPair("Dropping row column from", "MeanObject table") 480 self.scratchDb.dropColumn("MeanObject", "row") 364 481 365 482 return True … … 370 487 def populatePspsTables(self): 371 488 372 if not self.populateObjectT able(): return False489 if not self.populateObjectThinTable(): return False 373 490 #if not self.populateObjectCalColorTable(): return False 491 if not self.populateMeanObjectTable(): return False 374 492 375 493 # now remove the objID duplicates. We could not do this before as cpt/cps tables relate by row number 376 self.logger.infoPair("Forcing uniqueness on", "objID in Object table")377 rowCountBefore = self.scratchDb.getRowCount("Object ")494 self.logger.infoPair("Forcing uniqueness on", "objID in ObjectThin table") 495 rowCountBefore = self.scratchDb.getRowCount("ObjectThin") 378 496 379 497 # XXX EAM : note that in mysql versions later than 5.1, this fails … … 386 504 self.scratchDb.execute("set session old_alter_table=1") 387 505 388 self.scratchDb.execute("ALTER IGNORE TABLE Object ADD UNIQUE INDEX(objID)")506 self.scratchDb.execute("ALTER IGNORE TABLE ObjectThin ADD UNIQUE INDEX(objID)") 389 507 if self.scratchDb.version > 5.1: 390 508 self.scratchDb.execute("set session old_alter_table=0") 391 509 392 rowCountAfter = self.scratchDb.getRowCount("Object ")510 rowCountAfter = self.scratchDb.getRowCount("ObjectThin") 393 511 self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore)) 394 512 513 self.logger.infoPair("Forcing uniqueness on", "objID in MeanObject table") 514 rowCountBefore = self.scratchDb.getRowCount("MeanObject") 515 516 # XXX EAM : note that in mysql versions later than 5.1, this fails 517 # unless the following is called first: 518 # set session old_alter_table=1 519 # follow the command with 520 # set session old_alter_table=0 521 # OF COURSE, this fails for mysql version < 5.5... 522 if self.scratchDb.version > 5.1: 523 self.scratchDb.execute("set session old_alter_table=1") 524 525 self.scratchDb.execute("ALTER IGNORE TABLE MeanObject ADD UNIQUE INDEX(objID)") 526 if self.scratchDb.version > 5.1: 527 self.scratchDb.execute("set session old_alter_table=0") 528 529 rowCountAfter = self.scratchDb.getRowCount("MeanObject") 530 self.logger.infoPair("Number of duplicated objIDs removed", "%d out of %d" % ((rowCountBefore - rowCountAfter), rowCountBefore)) 531 532 self.dvoObjects.purgeRegion(self.region) 533 534 #this is abuse of something but this is how I get the object batches to crash to further investigate them 535 536 # rowCountAfter = self.scratchDb.getRowCount("Object") 395 537 return True 396 538 # return False
Note:
See TracChangeset
for help on using the changeset viewer.
