- Timestamp:
- Aug 1, 2014, 5:12:31 PM (12 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py
r37160 r37168 4 4 import glob 5 5 import sys 6 6 import math 7 7 8 8 import stilts … … 55 55 56 56 self.stackType = "DEEP_STACK" # TODO 57 self.stackTypeID = self.getStackTypeID() 57 58 58 59 self.filters = ['g', 'r', 'i', 'z', 'y'] … … 158 159 self.dropTableVerbose("StackObjectThin") 159 160 self.dropTableVerbose("StackObjectExtra") 160 161 161 162 self.dropTableVerbose("StackApFlx") 162 163 163 164 self.dropTableVerbose("StackModelFitThin") 164 165 self.dropTableVerbose("StackModelFitDeV") … … 204 205 self.logger.infoPair("Projectioncell", "%s" % self.projectioncell) 205 206 207 ### *********************** Utility Functions ************************************ 208 206 209 ''' 207 210 Drop a table and report the drop … … 212 215 213 216 ''' 214 Updates a table with stackMetaID 215 ''' 216 def updateStackMetaID(self, table): 217 218 sql = "UPDATE " + table + " SET " 219 220 for i in range(len(self.filters)): 221 filter = self.filters[i] 222 field = filter + "stackMetaID" 223 sql += field + " = '" + self.stackIDs[filter] + "'" 224 if (i < len(self.filters) - 1): 225 sql += ", " 226 227 self.scratchDb.execute(sql) 228 229 ''' 230 Updates a table with stackTypeID grabbed from FitModel init table 231 ''' 232 def updateStackTypeID(self, table): 233 234 sql = "UPDATE "+table+" AS a, StackType AS b SET a.stackTypeID=b.stackTypeID WHERE b.name = '" + self.stackType + "'" 235 self.scratchDb.execute(sql) 236 217 get stackTypeID corresponding to our stackType 218 ''' 219 def getStackTypeID(self): 220 221 sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'" 222 rs = self.scratchDb.executeQuery(sql) 223 if not rs.next(): 224 self.logger.errorPair("cannot get stack type ID from database for", self.stackType) 225 raise 226 227 ID = rs.getInt(1) 228 return ID 237 229 238 230 def testprint(self): … … 241 233 242 234 ''' 243 Updates aperture fluxes for StackApFkx table 244 ''' 245 def updateModelFitFlxs(self, prefix, psfCondition, tablename, filter): 246 #properly -999 these to start with: 247 f = filter 248 if tablename == 'StackModelFit': 249 sql = "UPDATE " + tablename + " AS a, "+f+"SkyChip_xrad AS b SET \ 250 "+f+prefix+"flxR1=-999, \ 251 "+f+prefix+"flxR1Err=-999, \ 252 "+f+prefix+"flxR1Std=-999, \ 253 "+f+prefix+"flxR1Fill=-999, \ 254 "+f+prefix+"flxR2=-999, \ 255 "+f+prefix+"flxR2Err=-999, \ 256 "+f+prefix+"flxR2Std=-999, \ 257 "+f+prefix+"flxR2Fill=-999, \ 258 "+f+prefix+"flxR3=-999, \ 259 "+f+prefix+"flxR3Err=-999, \ 260 "+f+prefix+"flxR3Std=-999, \ 261 "+f+prefix+"flxR3Fill=-999, \ 262 "+f+prefix+"flxR4=-999, \ 263 "+f+prefix+"flxR4Err=-999, \ 264 "+f+prefix+"flxR4Std=-999, \ 265 "+f+prefix+"flxR4Fill=-999, \ 266 "+f+prefix+"flxR5=-999, \ 267 "+f+prefix+"flxR5Err=-999, \ 268 "+f+prefix+"flxR5Std=-999, \ 269 "+f+prefix+"flxR5Fill=-999, \ 270 "+f+prefix+"flxR6=-999, \ 271 "+f+prefix+"flxR6Err=-999, \ 272 "+f+prefix+"flxR6Std=-999, \ 273 "+f+prefix+"flxR6Fill=-999, \ 274 "+f+prefix+"flxR7=-999, \ 275 "+f+prefix+"flxR7Err=-999, \ 276 "+f+prefix+"flxR7Std=-999, \ 277 "+f+prefix+"flxR7Fill=-999, \ 278 "+f+prefix+"flxR8=-999, \ 279 "+f+prefix+"flxR8Err=-999, \ 280 "+f+prefix+"flxR8Std=-999, \ 281 "+f+prefix+"flxR8Fill=-999, \ 282 "+f+prefix+"flxR9=-999, \ 283 "+f+prefix+"flxR9Err=-999, \ 284 "+f+prefix+"flxR9Std=-999, \ 285 "+f+prefix+"flxR9Fill=-999, \ 286 "+f+prefix+"flxR10=-999, \ 287 "+f+prefix+"flxR10Err=-999, \ 288 "+f+prefix+"flxR10Std=-999, \ 289 "+f+prefix+"flxR10Fill=-999 \ 290 WHERE a."+f+"ippDetectID=b.IPP_IDET " 291 #self.logger.infoPair("sql", sql) 292 self.scratchDb.execute(sql) 293 294 295 #we have variable numbers of these columns. find out which are in use 296 sql = "DESCRIBE "+f+"SkyChip_xrad 'aper_fill_%'" 297 rs = self.scratchDb.executeQuery(sql) 298 rs.first() 299 columns = [] 300 column = rs.getString(1) 301 columns.append(column) 302 while (rs.next()): 303 column = rs.getString(1) 304 columns.append(column) 305 rs.close() 306 307 #use regex to leave just the numbers 308 309 numbers = [] 310 min = 1 311 max = 10 312 if tablename == 'StackApFlx': 313 min = 3 314 max = 5 315 for column in columns: 316 number = re.sub("APER_FILL_","",column) 317 if (int(number) >= min and int(number) <=max): 318 numbers.append(number) 319 self.logger.infoPair("Aperture Numbers",number) 320 321 #generate the sql to do the necessary ops on the columns 322 sql = "UPDATE " + tablename + " AS a, "+f+"SkyChip_xrad AS b SET " 323 cnt =0 324 325 # XXX : I need to worry about which header this is 326 exptime = self.safeDictionaryAccess(self.header, 'EXPTIME') 327 for number in numbers: 328 if (cnt>0): 329 sql=sql+" , " 330 sql=sql+f+prefix+"flxR"+number+"=b.APER_FLUX_"+number +" / " + str(exptime) + ", " 331 sql=sql+f+prefix+"flxR"+number+"Err=b.APER_FLUX_ERR_"+number +" / " + str(exptime) + ", " 332 sql=sql+f+prefix+"flxR"+number+"Std=b.APER_FLUX_STDEV_"+number +" / " + str(exptime) + ", " 333 sql=sql+f+prefix+"flxR"+number+"Fill=b.APER_FILL_"+number + " " 334 cnt=cnt+1 335 336 sql=sql+"WHERE a."+f+"ippDetectID=b.IPP_IDET AND "+psfCondition 337 self.logger.infoPair("sql",sql) 338 self.scratchDb.execute(sql) 339 340 341 ''' 342 Populates the StackMeta table, mainly from dictionary values found in IPP FITS header 235 Populates the StackMeta table, mainly from header values found in stack cmf FITS header 343 236 ''' 344 237 def populateStackMeta(self,filter): … … 374 267 sqlLine = sqlUtility("INSERT INTO " + tablename + " (") 375 268 269 sqlLine.group("stackMetaID", str(stackID)) 376 270 sqlLine.group("batchID", str(self.batchID)) 377 271 sqlLine.group("surveyID", str(self.surveyID)) 378 sqlLine.group("dataRelease", str(self.skychunk.dataRelease))379 sqlLine.group("stackMetaID", str(stackID))380 272 sqlLine.group("filterID", str(filterID)) 273 sqlLine.group("stackTypeID", str(self.stackTypeID)) 381 274 sqlLine.group("skyCellID", self.skycell) 382 275 sqlLine.group("photoCalID", photoCalID) 383 sqlLine.group("photoZero", self.getKeyFloat(header, "%.5f", 'FPA.ZP'))384 276 sqlLine.group("expTime", self.getKeyFloat(header, "%.5f", 'EXPTIME')) 385 277 sqlLine.group("psfModelID", psfmodel) 386 278 sqlLine.group("psfFwhm_mean", fwhm_maj) 387 279 sqlLine.group("psfFwhm_max", fwhm_maj_uq) 280 sqlLine.group("photoZero", self.getKeyFloat(header, "%.5f", 'FPA.ZP')) 388 281 sqlLine.group("ctype1", header['CTYPE1']) 389 282 sqlLine.group("ctype2", header['CTYPE2']) … … 398 291 sqlLine.group("pc002001", self.getKeyFloat(header, "%.8e", 'PC002001')) 399 292 sqlLine.group("pc002002", self.getKeyFloat(header, "%.8e", 'PC002002')) 293 sqlLine.group("dataRelease", str(self.skychunk.dataRelease)) 400 294 401 295 sql = sqlLine.make(") VALUES ( ", ")") … … 404 298 self.scratchDb.execute(sql) 405 299 406 # set stackTypeID in table based on lookup table & self.stackType407 # XXX include in the INSERT above?408 self.updateStackTypeID(tablename)409 410 300 self.tablesToExport.append(tablename) 411 301 … … 419 309 420 310 # XXX EAM 20140731 : if we are going to add indexes (objID?) to StackObjectThin, do it HERE 311 self.scratchDb.createIndex("StackObjectThin", "objID") 421 312 422 313 self.logger.infoPair("inserting filter dependent items into", "StackObjectThin") … … 491 382 self.logger.errorPair('failed sql: ', sql) 492 383 raise 384 385 self.tablesToExport.append("StackObjectThin") 493 386 494 387 ''' … … 576 469 return 577 470 471 self.tablesToExport.append("StackObjectExtra") 472 578 473 ''' 579 474 Populates the StackModelFitThin table … … 587 482 # self.scratchDb.createIndex("StackObjectExtra", "objID") 588 483 484 # currently, none of the fields defined by the table are generated 589 485 590 486 ''' … … 603 499 for filter in self.filters: 604 500 self.populateStackModelFitFilter(model, ippModelType, filter) 501 502 self.tablesToExport.append(table) 605 503 606 504 ''' … … 616 514 617 515 header = self.headerSet[filter] 516 print "exptime in header: " + header['EXPTIME'] 517 518 magtime = "%.5f" % (2.5*math.log10(float(header['EXPTIME']))) 618 519 exptime = self.getKeyFloat(header, "%.5f", "EXPTIME") 619 520 521 print "magtime: " + magtime + ", exptime: " + exptime 522 620 523 sqlLine = sqlUtility("UPDATE " + table + " AS a, " + filter + "SkyChip_xfit AS b SET") 621 524 622 # these lines yield the flux, flux error values, but I do not think we want those in the database... 623 # sqlLine.group("a." + filter + model + "Flux", "POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime)) 624 # sqlLine.group("a." + filter + model + "FluxErr", "ABS(b.EXT_INST_MAG_SIG) * POW(10, -0.4 * b.EXT_INST_MAG) / " + str(exptime) + " / 1.085736") 625 626 sqlLine.group("a." + filter + model + "Mag", "b.EXT_INST_MAG") # need to modify by zero point and 2.5*log10(exptime) 627 sqlLine.group("a." + filter + model + "MagErr", "b.EXT_INST_MAG_SIG") # need to modify by zero point and 2.5*log10(exptime) 525 sqlLine.group("a." + filter + model + "Mag", "b.EXT_INST_MAG + " + magtime) # need to modify by zero point and 2.5*log10(exptime) 526 sqlLine.group("a." + filter + model + "MagErr", "b.EXT_INST_MAG_SIG") 628 527 629 528 sqlLine.group("a." + filter + model + "Radius", "b.EXT_WIDTH_MAJ") … … 655 554 self.scratchDb.execute(sql) 656 555 657 658 # XXX old code for flux tables 659 # self.logger.infoPair("Adding fluxes", "un-convolved") 660 # self.updateModelFitFlxs("", "((b.table_index -1) % 3 = 0)","StackModelFit",f) 661 # self.logger.infoPair("Adding fluxes", "1st convolved") 662 # self.updateModelFitFlxs("c1", "((b.table_index -1) % 3 = 1)","StackModelFit",f) 663 # self.logger.infoPair("Adding fluxes", "2nd convolved") 664 # self.updateModelFitFlxs("c2", "((b.table_index -1) % 3 = 2)","StackModelFit",f) 665 666 ''' 667 Populates the StackApFlx table, mainly from dictionary values found in IPP FITS header 668 ''' 669 # def populateStackApFlx(self,filter): 670 671 ## self.logger.infoPair("Adding", "petrosians for extended sources") 672 ## sql = "UPDATE StackModelFit as a, "+f+"SkyChip_xsrc AS b SET \ 673 ## "+f+"petRadius=b.PETRO_RADIUS \ 674 ## ,"+f+"petRadiusErr=b.PETRO_RADIUS_ERR \ 675 ## ,"+f+"petFlux=POW(10.0, -0.4 * b.PETRO_MAG) /" + str(self.header['EXPTIME']) + " \ 676 ## ,"+f+"petFluxErr=-999 \ 677 ## ,"+f+"petR50=b.PETRO_RADIUS_50 \ 678 ## ,"+f+"petR50Err=b.PETRO_RADIUS_50_ERR \ 679 ## ,"+f+"petR90=b.PETRO_RADIUS_90 \ 680 ## ,"+f+"petR90Err=b.PETRO_RADIUS_90_ERR \ 681 ## ,"+f+"petCf=b.PETRO_FILL \ 682 ## WHERE a."+f+"ippDetectID=b.IPP_IDET" 683 ## self.scratchDb.execute(sql) 684 ## 685 ## self.logger.infoPair("Adding", "petFluxErr for non nulls") 686 ## sql = "UPDATE StackModelFit as a, "+f+"SkyChip_xsrc AS b SET \ 687 ## "+f+"petFluxErr=ABS(b.PETRO_MAG_ERR) * POW(10.0, (-0.4*b.PETRO_MAG)) / " + str(self.header['EXPTIME']) + " / 1.085736 \ 688 ## WHERE a."+f+"ippDetectID=b.IPP_IDET \ 689 ## AND (b.PETRO_MAG_ERR is not NULL AND b.PETRO_MAG is not NULL AND PETRO_MAG_ERR < 1000.0)" 690 ## # added this because when the petromagerr is crazy large (10^29 it out of ranges the flux err calc) 691 ## self.logger.infoPair("sql", sql) 692 ## self.scratchDb.execute(sql) 693 694 695 # self.scratchDb.updateAllRows("StackModelFit", "surveyID", str(self.surveyID)) 696 # self.scratchDb.updateAllRows("StackModelFit", "dataRelease", str(self.skychunk.dataRelease)) 697 698 # self.logger.infoPair("not adding"," PrimaryF to stackApFlx") 699 # sql = "UPDATE StackModelFit AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ 700 # a.primaryF = ((b.flags & 0x10000) >> 16 ) \ 701 # WHERE a.ippDetectID = b.ippDetectID \ 702 # AND b.imageID = " + str(imageID) 556 # insert detection information for each filter based on the DVO contents 557 sqlLine = sqlUtility("UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") 558 sqlLine.group("a." + filter + model + "Mag", "a." + filter + model + "Mag + b.zp") 559 sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID") 560 561 self.logger.infoPair('sql', sql) 562 try: 563 self.scratchDb.execute(sql) 564 except: 565 self.logger.errorPair('failed sql',sql) 566 return 567 568 ''' 569 Populates the StackModelFitPet table, mainly from dictionary values found in IPP FITS header 570 ''' 571 def populateStackPetrosian(self): 572 573 table = "StackModelFitPet" 574 575 self.logger.infoPair("Procesing table", table) 576 self.insertDvoIDs(table, "StackObjectThin") 577 578 # if we are going to add a key, do it here so it is useful 579 # self.scratchDb.createIndex(table, "objID") 580 581 for filter in self.filters: 582 self.populateStackPetrosianFilter(filter) 583 584 self.tablesToExport.append(table) 585 586 ''' 587 Populates the StackModelFitPet table, mainly from dictionary values found in IPP FITS header 588 ''' 589 def populateStackPetrosianFilter(self, filter): 590 591 if self.stackIDs[filter] <= 0: 592 self.logger.infoPair("no stack data for filter" , filter) 593 return True 594 595 self.logger.infoPair("Adding", "petrosians for extended sources") 596 sqlLine = sqlUtility("UPDATE StackModelFitPet AS a, " + filter + "SkyChip_xsrc AS b SET") 597 598 sqlLine.group("a." + filter + "petRadius", "b.PETRO_RADIUS") 599 sqlLine.group("a." + filter + "petRadiusErr", "b.PETRO_RADIUS_ERR") 600 sqlLine.group("a." + filter + "petFlux", "b.PETRO_MAG") # XXX EAM 20140732 : still need to decide about flux vs mag 601 sqlLine.group("a." + filter + "petFluxErr", "b.PETRO_MAG_ERR") # XXX EAM 20140732 602 sqlLine.group("a." + filter + "petR50", "b.PETRO_RADIUS_50") 603 sqlLine.group("a." + filter + "petR50Err", "b.PETRO_RADIUS_50_ERR") 604 sqlLine.group("a." + filter + "petR90", "b.PETRO_RADIUS_90") 605 sqlLine.group("a." + filter + "petR90Err", "b.PETRO_RADIUS_90_ERR") 606 sqlLine.group("a." + filter + "petCf", "b.PETRO_FILL") 607 608 sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET") 609 610 print "SQL: " + sql 611 self.scratchDb.execute(sql) 703 612 704 613 ''' 705 614 Populates the StackApFlx table 706 615 ''' 707 def populateStackApFlx(self): 616 def populateStackApFlxThin(self): 617 618 tablename = "StackApFlx" 619 620 self.logger.infoPair("Procesing table", tablename) 621 self.insertDvoIDs(tablename, "StackObjectThin") 622 623 # if we are going to add a key, do it here so it is useful 624 # self.scratchDb.createIndex(tablename, "objID") 708 625 709 626 for filter in self.filters: 710 if self.stackIDs[filter] <= 0: 711 continue 712 713 # XXX is this a reasonable solution? 714 self.header = self.headerSet[filter] 715 716 self.logger.infoPair("Adding fluxes", "un-convolved") 717 self.updateModelFitFlxs("", "((b.table_index -1) % 3 = 0)", "StackApFlx", filter) 718 self.logger.infoPair("Adding fluxes", "1st convolved") 719 self.updateModelFitFlxs("c1", "((b.table_index -1) % 3 = 1)","StackApFlx", filter) 720 self.logger.infoPair("Adding fluxes", "2nd convolved") 721 self.updateModelFitFlxs("c2", "((b.table_index -1) % 3 = 2)","StackApFlx", filter) 722 723 self.deleteDetectionsNotInStackObject("StackApFlx",f) 724 725 self.scratchDb.reportAndDeleteRowsWithNULLS("StackApFlx", "objID") 627 self.populateStackApFlxFilter(tablename, "", filter) 628 self.populateStackApFlxFilter(tablename, "C1", filter) 629 self.populateStackApFlxFilter(tablename, "C2", filter) 630 631 self.tablesToExport.append(tablename) 632 633 ''' 634 Populates the StackApFlx table 635 ''' 636 def populateStackApFlx(self, version): 637 638 tablename = "StackModelFitFlx" + version 639 640 self.logger.infoPair("Procesing table", tablename) 641 self.insertDvoIDs(tablename, "StackObjectThin") 642 643 # if we are going to add a key, do it here so it is useful 644 # self.scratchDb.createIndex(tablename, "objID") 645 646 for filter in self.filters: 647 self.populateStackApFlxFilter(tablename, version, filter) 648 649 self.tablesToExport.append(tablename) 650 651 ''' 652 Updates aperture fluxes for StackApFkx table 653 ''' 654 def populateStackApFlxFilter(self, tablename, version, filter): 655 656 prefix = "none" 657 658 # order of the 3 convolutions is fixed (raw, C1, C2) = (1, 2, 3) 659 if version == "": 660 psfCondition = "(b.table_index % 3 = 1)" 661 prefix = "" 662 if version == "C1": 663 psfCondition = "(b.table_index % 3 = 2)" 664 prefix = "c1" 665 if version == "C2": 666 psfCondition = "(b.table_index % 3 = 0)" 667 prefix = "c2" 668 669 if prefix == "none": 670 self.logger.errorPair("invalid stack ap flux version", version) 671 raise 672 673 if self.stackIDs[filter] <= 0: 674 self.logger.infoPair("no stack data for filter" , filter) 675 return True 676 677 minRadius = 1 678 maxRadius = 10 679 if tablename == 'StackApFlx': 680 minRadius = 3 681 maxRadius = 5 682 683 # properly -999 these to start with: 684 # XXX the default should take care of this, but does not 685 686 sqlLine = sqlUtility("UPDATE " + tablename + " SET") 687 for radius in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]: 688 if radius < minRadius: continue 689 if radius > maxRadius: continue 690 sqlLine.group(filter + prefix + "flxR" + str(radius), "-999") 691 sqlLine.group(filter + prefix + "flxR" + str(radius) + "Err", "-999") 692 sqlLine.group(filter + prefix + "flxR" + str(radius) + "Std", "-999") 693 sqlLine.group(filter + prefix + "flxR" + str(radius) + "Fill", "-999") 694 695 sql = sqlLine.makeEquals("") 696 697 print "SQL: " + sql 698 self.scratchDb.execute(sql) 699 700 # set the flux values from the cmf file 701 cmfTable = filter + "SkyChip_xrad" 702 703 # we have variable numbers of these columns. find out which are in use 704 sql = "DESCRIBE " + cmfTable + " 'aper_fill_%'" 705 rs = self.scratchDb.executeQuery(sql) 706 rs.first() 707 columns = [] 708 column = rs.getString(1) 709 columns.append(column) 710 while (rs.next()): 711 column = rs.getString(1) 712 columns.append(column) 713 rs.close() 714 715 # use regex to select the aperture number (APER_FILL_N) 716 numbers = [] 717 for column in columns: 718 number = re.sub("APER_FILL_","",column) 719 if (int(number) >= minRadius and int(number) <= maxRadius): 720 numbers.append(number) 721 self.logger.infoPair("Aperture Numbers",number) 722 723 # XXX : I need to worry about which header this is 724 header = self.headerSet[filter] 725 exptime = self.getKeyFloat(header, "%.5f", "EXPTIME") 726 727 # generate the sql to do the necessary ops on the columns 728 sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET ") 729 730 for number in numbers: 731 sqlLine.group(filter + prefix + "flxR" + number, "b.APER_FLUX_" + number + " / " + exptime) 732 sqlLine.group(filter + prefix + "flxR" + number + "Err", "b.APER_FLUX_ERR_" + number + " / " + exptime) 733 sqlLine.group(filter + prefix + "flxR" + number + "Std", "b.APER_FLUX_STDEV_" + number + " / " + exptime) 734 sqlLine.group(filter + prefix + "flxR" + number + "Fill", "b.APER_FILL_" + number + " / " + exptime) 735 736 sql = sqlLine.makeEquals("WHERE a." + filter + "ippDetectID = b.IPP_IDET AND " + psfCondition) 737 self.logger.infoPair("sql",sql) 738 self.scratchDb.execute(sql) 739 740 ''' 741 Populates the StackToImage table 742 ''' 743 def populateStackToImage(self): 744 745 self.logger.infoPair("Procesing table", "StackToImage") 746 747 for filter in self.filters: 748 749 stackmetaID = self.stackIDs[filter] 750 if stackmetaID > 0: 751 752 imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaID) 753 754 for imageID in imageIDs: 755 756 sql = "INSERT INTO StackToImage (stackMetaID, imageID) \ 757 VALUES (\ 758 " + str(stackmetaID) + ", " + imageID + ")" 759 self.scratchDb.execute(sql) 760 761 # now update StackMeta with correct number of inputs 762 sql = "UPDATE "+filter+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaID)+")" 763 self.scratchDb.execute(sql) 764 765 self.tablesToExport.append("StackToImage") 726 766 727 767 ''' … … 743 783 744 784 745 '''746 Populates the StackToImage table747 '''748 def populateStackToImage(self):749 750 self.logger.infoPair("Procesing table", "StackToImage")751 752 for filter in self.filters:753 754 stackmetaID = self.stackIDs[filter]755 if stackmetaID > 0:756 757 imageIDs = self.gpc1Db.getImageIDsForThisStackID(stackmetaID)758 759 for imageID in imageIDs:760 761 sql = "INSERT INTO StackToImage (stackMetaID, imageID) \762 VALUES (\763 " + str(stackmetaID) + ", " + imageID + ")"764 self.scratchDb.execute(sql)765 766 # now update StackMeta with correct number of inputs767 sql = "UPDATE "+filter+"StackMeta SET nP2Images = (SELECT COUNT(distinct(floor(imageID/100))) FROM StackToImage where stackMetaID = "+str(stackmetaID)+")"768 self.scratchDb.execute(sql)769 770 '''771 Generates the randomStackIds772 '''773 def populateRandomsAndSkyCellID(self):774 775 self.logger.infoPair("Generating Randoms","for StackObject")776 sql = "update StackObject set randomStackObjID = FLOOR(RAND()*9223372036854775807) \777 ,skyCellID = " + str(self.skycell)+ " \778 ,surveyID = " + str(self.surveyID)779 try:780 self.scratchDb.execute(sql)781 except:782 self.logger.infoPair("failed sql", sql)783 784 '''785 copies randomStackIds from StackObject to tableName786 '''787 def copyRandomsAndSkyCellIDFromStackObject(self, tableName):788 789 self.logger.infoPair("copying Randoms from StackObject to ", tableName)790 sql = "update " + tableName + " join StackObject using (objID) \791 set " + tableName + ".randomStackObjID = StackObject.randomStackObjID \792 , " + tableName + ".skyCellID = StackObject.skyCellID \793 , " + tableName + ".surveyID = StackObject.surveyID"794 self.logger.infoPair("sql", sql)795 try:796 self.scratchDb.execute(sql)797 except:798 self.logger.infoPair("failed sql", sql)799 800 801 802 '''803 I think we are no longer using this function: this have been804 replaced with the code in 'selectDvoObjIDs' and 'selectDvoFilterIDs'805 '''806 def populateStackIPPDetectIDFromDvo(self, tableName, all):807 808 #tableName = "StackObject"809 self.logger.infoPair("Processing table", tableName)810 811 filters = ['g','r','i','z','y']812 813 for filter in self.filters:814 if self.stackIDs[filter] < 0:815 self.logger.infoPair("skipping ingest of dvo for filter",f)816 continue817 818 self.header = self.headerSet[filter]819 stackmetaID = self.stackIDs[filter]820 821 imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])822 self.logger.infoPair("obtained","imageID")823 addsql = ""824 if (all == 1):825 addsql = "a." + filter + "ra = b.ra, \826 a." + filter + "dec = b.dec_, \827 a." + filter + "raErr = b.raErr, \828 a." + filter + "decErr = b.decErr, \829 a." + filter + "zp = b.zp, \830 a." + filter + "expTime = b.expTime,"831 832 833 sql = "UPDATE " + tableName + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b \834 SET a." + filter + "ippDetectID = b.ippDetectID, \835 " + addsql + " \836 a." + filter + "stackDetectID = b.detectID, \837 a." + filter + "ippObjID = b.ippObjID, \838 a." + filter + "stackMetaID = " + str(stackmetaID) + " \839 WHERE a.objID = b.objID AND b.imageID = " + str(imageID)840 841 self.logger.infoPair('sql',sql)842 self.scratchDb.execute(sql)843 844 # out of the loops - give dataRelase to all845 self.scratchDb.updateAllRows(tableName, "dataRelease", str(self.skychunk.dataRelease))846 847 848 785 def deleteNULLippDetectIDs(self, tableName): 849 786 … … 863 800 def alterPspsTables(self): 864 801 865 self.logger.debug("Altering PSPS tables ")802 self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)") 866 803 # heather uncommented the unique line -- well that no worky 867 804 #self.scratchDb.makeColumnUnique("StackObject", "objID") 868 self.scratchDb.createIndex("StackObject", "ippDetectID")869 self.scratchDb.createIndex("StackObject", "stackDetectID")870 self.scratchDb.createIndex("StackApFlx", "ippDetectID")871 self.scratchDb.createIndex("StackModelFit", "ippDetectID")805 #self.scratchDb.createIndex("StackObject", "ippDetectID") 806 #self.scratchDb.createIndex("StackObject", "stackDetectID") 807 #self.scratchDb.createIndex("StackApFlx", "ippDetectID") 808 #self.scratchDb.createIndex("StackModelFit", "ippDetectID") 872 809 873 810 return True … … 878 815 def indexIppTables(self): 879 816 880 self.logger.infoPair("Creating indexes on", "IPP tables") 881 self.scratchDb.createIndex("SkyChip_psf", "IPP_IDET") 882 self.scratchDb.createIndex("SkyChip_xfit", "IPP_IDET") 883 self.scratchDb.createIndex("SkyChip_xrad", "IPP_IDET") 884 self.scratchDb.createIndex("SkyChip_xsrc", "IPP_IDET") 817 for filter in self.filters: 818 if self.stackIDs[filter] <= 0: 819 self.logger.infoPair("no files for filter" , filter) 820 continue 821 822 self.logger.infoPair("Creating indexes on", "IPP tables") 823 self.scratchDb.createIndex(filter + "SkyChip_psf", "IPP_IDET") 824 self.scratchDb.createIndex(filter + "SkyChip_xfit", "IPP_IDET") 825 self.scratchDb.createIndex(filter + "SkyChip_xrad", "IPP_IDET") 826 self.scratchDb.createIndex(filter + "SkyChip_xsrc", "IPP_IDET") 885 827 886 828 return True … … 892 834 def selectDvoObjIDs(self): 893 835 894 print "starting selectDvoObjIDs" 895 896 sql = "INSERT INTO StackObjectThin (objID, ippObjID, surveyID, skyCellID, dvoRegionID, dataRelease) " 897 sql += "SELECT DISTINCT objID, ippObjID, " 898 sql += str(self.surveyID) + ", " 899 sql += str(self.skycell) + ", " 900 sql += str(self.dvoRegionID) + ", " 901 sql += str(self.skychunk.dataRelease) 902 903 sql += " FROM " + self.scratchDb.dvoDetectionTable 904 sql += " WHERE (" 905 906 print "sql so far: " + sql 907 836 sqlLine = sqlUtility("INSERT INTO StackObjectThin (") 837 838 sqlLine.group("objID", "objID") 839 sqlLine.group("ippObjID", "ippObjID") 840 sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") 841 sqlLine.group("skyCellID", "'" + str(self.skycell) + "'") 842 sqlLine.group("dvoRegionID", "'" + str(self.dvoRegionID) + "'") 843 sqlLine.group("dataRelease", "'" + str(self.skychunk.dataRelease) + "'") 844 sqlLine.group("randomStackObjID", "FLOOR(RAND()*9223372036854775807)") 845 846 whereClause = " WHERE (" 908 847 imageIDs = self.imageIDs.values() 909 848 for i in range(len(imageIDs)): … … 911 850 self.logger.infoPair("selecting imageID", imageID) 912 851 913 print "table: self.scratchDb.dvoDetectionTable" 914 print "imageID: " + str(imageID) 915 916 sql += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")" 917 print "sql: " + sql 852 whereClause += "(" + self.scratchDb.dvoDetectionTable + ".imageID = " + str(imageID) + ")" 918 853 919 854 if (i < len(imageIDs) - 1): 920 sql += " or " 921 922 print "done segment" 923 924 sql += ")" 925 855 whereClause += " or " 856 857 whereClause += ")" 858 859 sql = sqlLine.makeRaw(") SELECT DISTINCT ", "FROM " + self.scratchDb.dvoDetectionTable + whereClause) 926 860 print "SQL: " + sql 861 927 862 self.scratchDb.execute(sql) 928 863 929 ## XXX '''930 ## XXX select objid (psps object ID) from dvo restricted to this set of imageIDs931 ## XXX '''932 ## XXX def selectDvoFilterIDs(self, table):933 ## XXX934 ## XXX # XXX I am using imageID a lot: generate a filter-index list935 ## XXX for filter in self.filters:936 ## XXX self.header = self.headerSet[filter]937 ## XXX imageIDgpc = self.header['IMAGEID']938 ## XXX imageIDdvo = self.scratchDb.getImageIDFromExternID(imageIDgpc)939 ## XXX self.logger.infoPair("selecting imageID", imageIDdvo)940 ## XXX941 ## XXX sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable " AS b SET "942 ## XXX943 ## XXX sql += "a." + filter + "ippDetectID = b.ippDetectID, " # eg, gippDetectID (from dvo.measure.detID)944 ## XXX sql += "a." + filter + "stackDetectID = b.detectID " # eg, gstackDetectID (from dvo.measure.extID)945 ## XXX sql += "a." + filter + "stackMetaID = " + imageIDgpc # eg, gstackMetaID (from cmf.IMAGEID)946 ## XXX947 ## XXX sql += "WHERE a.objID = b.objID "948 ## XXX sql += "AND b.imageID = " + imageIDdvo949 ## XXX950 ## XXX print "SQL: " + sql951 ## XXX self.scratchDb.execute(sql)952 953 864 ''' 954 865 Insert objid (from dvo) into table from main table … … 956 867 def insertDvoIDs(self, table, mainTable): 957 868 958 fields = "objID, ippObjID, surveyID, skyCellID, dvoRegionID, dataRelease, "869 fields = "objID, ippObjID, surveyID, skyCellID, randomStackObjID, primaryDetection, dvoRegionID, dataRelease, " 959 870 960 871 for i in range(len(self.filters)): … … 1007 918 self.scratchDb.execute(sql) 1008 919 1009 1010 920 ''' 1011 921 Does the processing, i.e. pulling stuff from IPP tables into PSPS tables … … 1015 925 self.skipBatch = False 1016 926 1017 # get the IDs from the DVO tables 1018 # XXX deprecated along with dvograbber 1019 # if not self.getIDsFromDVO(): return False 1020 1021 # do some basics first: 1022 # populate stack meta per filter 1023 # insert objid/stackdetectid per filter to each table 927 # each of the "populate*" methods below add their tables to the list: 1024 928 self.tablesToExport=[] 1025 929 … … 1060 964 if self.stackType != "NIGHTLY_STACK": 1061 965 966 self.logger.infoPair("populating ","StackModelFitThin") 967 self.populateStackModelFitThin() 968 1062 969 self.logger.infoPair("populating ","StackModelFit") 1063 970 self.populateStackModelFit("Exp", "PS_MODEL_EXP") … … 1065 972 self.populateStackModelFit("Ser", "PS_MODEL_SERSIC") 1066 973 1067 #self.populateStackModelPet("Ser", "PS_MODEL_SERSIC") 1068 1069 #self.logger.infoPair("populating ","StackApFlx") 1070 #self.populateStackApFlx() 1071 974 self.populateStackPetrosian() 975 976 self.populateStackModelFit("Ser", "PS_MODEL_SERSIC") 977 978 self.logger.infoPair("populating ","StackApFlx Tables") 979 self.populateStackApFlxThin() 980 981 self.populateStackApFlx("") 982 self.populateStackApFlx("C1") 983 self.populateStackApFlx("C2") 984 1072 985 self.logger.infoPair("populating","StackToImage") 1073 986 self.populateStackToImage() 1074 987 1075 988 # self.setMinMaxObjID(["StackObject"]) 1076 # self.updateStackTypeID("StackModelFit") 1077 1078 self.tablesToExport.append("StackToImage") 1079 self.tablesToExport.append("StackObject") 1080 self.tablesToExport.append("StackModelFit") 1081 self.tablesToExport.append("StackApFlx") 1082 self.tablesToExport.append("SkinnyObject") 989 1083 990 self.logger.infoPair("finishing","populatePspsTables"); 1084 991 return True … … 1134 1041 1135 1042 # IPP FITS files are littered with infinities, so remove these 1136 self.logger. debug("Removing Infinity values from all columns")1043 self.logger.info("Removing Infinity values from all columns") 1137 1044 table = stilts.tpipe(table, cmd='keepcols "' + columns + '"') 1138 1045 table = stilts.tpipe(table, cmd='replaceval -Infinity null *')
Note:
See TracChangeset
for help on using the changeset viewer.
