- Timestamp:
- Aug 4, 2014, 4:27:44 PM (12 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/eam_branches/ipp-20140717/ippToPsps/jython/stackbatch.py
r37178 r37179 71 71 # IMAGE_ID in dvoImagesTable 72 72 73 # we just need one stack IDto get the generic metadata74 metastackID = -99973 # we just need one valid filter to get the generic metadata 74 validFilter = 'none' 75 75 76 76 # get a list of stackIDs, identify the CMFs, read the headers: … … 85 85 continue 86 86 87 metastackID = stackID87 validFilter = filter 88 88 89 89 # from each stack, get fits name from getStackStageCmf … … 112 112 113 113 # did we find any valid stacks? 114 if metastackID < 0:115 self.logger.errorPair("can't find any stack ids...?? ","??")114 if validFilter == 'none': 115 self.logger.errorPair("can't find any valid stacks...?? ","??") 116 116 raise 117 117 118 validStackID = self.stackIDs[validFilter] 119 118 120 # using the given stack ID, get the generic information: filter, skycell, etc. 119 meta = self.gpc1Db.getStackStageMeta(metastackID)120 121 if not meta :122 self.logger.errorPair("Could not get stack metadata for", metastackID)121 metadata = self.gpc1Db.getStackStageMeta(validStackID) 122 123 if not metadata: 124 self.logger.errorPair("Could not get stack metadata for", validStackID) 123 125 raise 124 126 125 fullSkycell = meta[1]; 126 127 # XXX EAM 20140729 : review and fix skycell vs projection cell ID 128 # skycell is, eg "skycell.1133.081" 129 # we need 1133.081 for skycell, but store as an int, so instead store 1133081 130 # projection cell is just the 1133 bit 131 132 #self.projectioncell = self.skycell[8:12] 133 #self.skycell = self.skycell[8:] 134 #self.skycell = self.skycell.replace(".", "") 135 #self.projectioncell = self.skycell[8:12] 136 137 # TODO this is the wrong way, but is consistent with SkyCell table in IN data 138 self.skycell = fullSkycell[8:] 139 self.projectioncell = fullSkycell 140 141 # XXX need to set this somewhere else, but where?? 142 self.dvoRegionID = 1 143 144 # proposed new values. Need to coordinate with the SkyCell table 145 # this fits in 32 bits for 0 < tessID < 214 146 # self.skycell = str( (tessID * (10000 * 1000)) + (int(self.projectioncell) * 1000) + cellID ) 147 148 self.analysisVer = meta[2]; 127 validHeader = self.headerSet[validFilter] 128 129 tessName = self.getKeyValue(header, 'TESS_ID') 130 self.tessID = self.scratchDb.getTessID(tessName) 131 132 skycellName = self.getKeyValue(header, 'SKYCELL') 133 134 # XXX EAM : I am hardwiring the parsing logic for RINGS vs LOCAL 135 if tessName == 'RINGS.V3': 136 # skycell is, eg "skycell.1133.081" 137 # 0123456789012345 138 self.projectionID = skycellName[8:12] 139 self.skycellID = skycellName[13:] 140 else: 141 # skycell is, eg "skycell.081" 142 # 01234567890 143 self.projectionID = 0 144 self.skycellID = skycellName[8:11] 145 146 self.analysisVer = metadata[2]; 147 if self.analysisVer[-1] == 'M': 148 self.analysisVer = self.analysisVer[:-1] 149 150 ### XXX TEST 151 self.analysisVer = 30000 149 152 150 153 # delete PSPS tables … … 198 201 self.logger.infoPair("Stack ID", "%d" % self.id) 199 202 self.logger.infoPair("Stack type", "%s" % self.stackType) 200 self.logger.infoPair("Skycell", "%s" % self.skycell) 201 self.logger.infoPair("Projectioncell", "%s" % self.projectioncell) 202 203 ### *********************** Utility Functions ************************************ 204 205 ''' 206 Drop a table and report the drop 207 ''' 208 def dropTableVerbose(self, table): 209 self.logger.infoPair("dropping table:",table) 210 self.scratchDb.dropTable(table) 211 212 ''' 213 get stackTypeID corresponding to our stackType 214 ''' 215 def getStackTypeID(self): 216 217 sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'" 218 rs = self.scratchDb.executeQuery(sql) 219 if not rs.next(): 220 self.logger.errorPair("cannot get stack type ID from database for", self.stackType) 221 raise 222 223 ID = rs.getInt(1) 224 return ID 225 226 def testprint(self): 227 print "here ", self.printline 228 self.printline += 1 203 self.logger.infoPair("SkycellID", "%s" % self.skycellID) 204 self.logger.infoPair("ProjectionID", "%s" % self.projectionID) 205 206 ### *********************** main populate functions ************************************ 229 207 230 208 ''' … … 268 246 sqlLine.group("filterID", str(filterID)) 269 247 sqlLine.group("stackTypeID", str(self.stackTypeID)) 270 sqlLine.group("skyCellID", self.skycell) 248 sqlLine.group("tessID", str(self.tessID)) 249 sqlLine.group("projectionID", str(self.projectionID)) 250 sqlLine.group("skyCellID", str(self.skycellID)) 271 251 sqlLine.group("photoCalID", photoCalID) 272 sqlLine.group("analysisVer", s elf.analysisVer))252 sqlLine.group("analysisVer", str(self.analysisVer)) 273 253 sqlLine.group("expTime", self.getKeyFloat(header, "%.5f", 'EXPTIME')) 274 254 sqlLine.group("psfModelID", psfmodel) … … 303 283 304 284 self.logger.infoPair("Procesing table", "StackObjectThin") 285 286 # insert the per-object data (IDs, random stack ID, dataRelease, etc) 305 287 self.selectDvoObjIDs() 306 288 307 # XXX EAM 20140731 : if we are going to add indexes (objID?) to StackObjectThin, do it HERE289 # add indexes StackObjectThin 308 290 self.scratchDb.createIndex("StackObjectThin", "objID") 309 291 … … 331 313 sqlLine.group("a."+filter+"stackDetectID", "b.detectID") # eg, gstackDetectID (from dvo.measure.extID) 332 314 sqlLine.group("a."+filter+"stackMetaID", str(stackID)) # eg, gstackMetaID (from cmf.IMAGEID) 315 316 sqlLine.group("a.dvoRegionID", "b.catID") # this is not filter dependent, but is re-written for each filter (if data exists) 317 sqlLine.group("a.primaryDetection", "((b.flags & 0x10000) >> 16)") # this is not filter dependent, but is re-written for each filter (if data exists) 318 319 sqlLine.group("a."+filter+"haveData", "'1'") 333 320 334 321 sqlLine.group("a."+filter+"ra", "b.ra") … … 387 374 def populateStackObjectExtra(self): 388 375 389 self.logger.infoPair("Procesing table", "StackObjectExtra") 390 self.insertDvoIDs("StackObjectExtra", "StackObjectThin") 376 tablename = "StackObjectExtra" 377 378 self.logger.infoPair("Procesing table", tablename) 379 self.insertDvoIDs(tablename, "StackObjectThin") 391 380 392 381 # if we are going to add a key, do it here so it is useful 393 382 # self.scratchDb.createIndex("StackObjectExtra", "objID") 394 383 395 self.logger.infoPair("inserting filter dependent cmf items into", "StackObjectExtra")384 self.logger.infoPair("inserting filter dependent cmf items into", tablename) 396 385 for filter in self.filters: 397 386 self.logger.infoPair("filter", filter) … … 408 397 409 398 # insert all the detections 410 sqlLine = sqlUtility("UPDATE StackObjectExtra AS a , " + filter + "SkyChip_psf AS b SET") 399 sqlLine = sqlUtility("UPDATE " + tablename + " AS a , " + filter + "SkyChip_psf AS b SET") 400 401 sqlLine.group("a."+filter+"haveData", "'1'") 411 402 412 403 sqlLine.group("a."+filter+"xPos", "b.X_PSF") … … 450 441 451 442 # insert detection information for each filter based on the DVO contents 452 sqlLine = sqlUtility("UPDATE StackObjectExtra asa, " + self.scratchDb.dvoDetectionTable + " as b SET")443 sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") 453 444 sqlLine.group("a."+filter+"expTime", "b.expTime") 454 445 sqlLine.group("a."+filter+"zp", "b.zp") … … 466 457 return 467 458 468 self.tablesToExport.append("StackObjectExtra") 459 460 self.deleteRowsWithNoStackData(tablename) 461 self.tablesToExport.append(tablename) 469 462 470 463 ''' … … 486 479 def populateStackModelFit(self, model, ippModelType): 487 480 488 table = "StackModelFit" + model489 490 self.logger.infoPair("Procesing table", table )491 self.insertDvoIDs(table , "StackObjectThin")481 tablename = "StackModelFit" + model 482 483 self.logger.infoPair("Procesing table", tablename) 484 self.insertDvoIDs(tablename, "StackObjectThin") 492 485 493 486 # if we are going to add a key, do it here so it is useful … … 497 490 self.populateStackModelFitFilter(model, ippModelType, filter) 498 491 499 self.tablesToExport.append(table) 492 self.deleteRowsWithNoStackData(tablename) 493 self.tablesToExport.append(tablename) 500 494 501 495 ''' … … 503 497 ''' 504 498 def populateStackModelFitFilter(self, model, ippModelType, filter): 499 500 tablename = "StackModelFit" + model 505 501 506 502 if self.stackIDs[filter] <= 0: … … 508 504 return True 509 505 510 table = "StackModelFit" + model511 512 506 header = self.headerSet[filter] 513 507 print "exptime in header: " + header['EXPTIME'] … … 518 512 print "magtime: " + magtime + ", exptime: " + exptime 519 513 520 sqlLine = sqlUtility("UPDATE " + table + " AS a, " + filter + "SkyChip_xfit AS b SET") 514 sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + filter + "SkyChip_xfit AS b SET") 515 516 sqlLine.group("a."+filter+"haveData", "'1'") 521 517 522 518 sqlLine.group("a." + filter + model + "Mag", "b.EXT_INST_MAG + " + magtime) # need to modify by zero point and 2.5*log10(exptime) … … 552 548 553 549 # insert detection information for each filter based on the DVO contents 554 sqlLine = sqlUtility("UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET")550 sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + self.scratchDb.dvoDetectionTable + " as b SET") 555 551 sqlLine.group("a." + filter + model + "Mag", "a." + filter + model + "Mag + b.zp") 556 552 sql = sqlLine.makeEquals("WHERE a.objID = b.objID AND a." + filter + "ippDetectID = b.ippDetectID") … … 568 564 def populateStackPetrosian(self): 569 565 570 table = "StackModelFitPet"571 572 self.logger.infoPair("Procesing table", table )573 self.insertDvoIDs(table , "StackObjectThin")566 tablename = "StackModelFitPet" 567 568 self.logger.infoPair("Procesing table", tablename) 569 self.insertDvoIDs(tablename, "StackObjectThin") 574 570 575 571 # if we are going to add a key, do it here so it is useful 576 # self.scratchDb.createIndex(table , "objID")572 # self.scratchDb.createIndex(tablename, "objID") 577 573 578 574 for filter in self.filters: 579 575 self.populateStackPetrosianFilter(filter) 580 576 581 self.tablesToExport.append(table) 577 self.deleteRowsWithNoStackData(tablename) 578 self.tablesToExport.append(tablename) 582 579 583 580 ''' … … 592 589 self.logger.infoPair("Adding", "petrosians for extended sources") 593 590 sqlLine = sqlUtility("UPDATE StackModelFitPet AS a, " + filter + "SkyChip_xsrc AS b SET") 591 592 sqlLine.group("a."+filter+"haveData", "'1'") 594 593 595 594 sqlLine.group("a." + filter + "petRadius", "b.PETRO_RADIUS") … … 626 625 self.populateStackApFlxFilter(tablename, "C2", filter) 627 626 627 self.deleteRowsWithNoStackData(tablename) 628 628 self.tablesToExport.append(tablename) 629 629 … … 644 644 self.populateStackApFlxFilter(tablename, version, filter) 645 645 646 self.deleteRowsWithNoStackData(tablename) 646 647 self.tablesToExport.append(tablename) 647 648 … … 678 679 maxRadius = 5 679 680 680 # properly -999 these to start with :681 # XXX the default should takecare of this, but does not681 # properly -999 these to start with. the default should take 682 # care of this, but does not 682 683 683 684 sqlLine = sqlUtility("UPDATE " + tablename + " SET") … … 718 719 self.logger.infoPair("Aperture Numbers",number) 719 720 720 # XXX : I need to worry about which header this is721 # get the exposure time for aperture fluxes 721 722 header = self.headerSet[filter] 722 723 exptime = self.getKeyFloat(header, "%.5f", "EXPTIME") … … 724 725 # generate the sql to do the necessary ops on the columns 725 726 sqlLine = sqlUtility("UPDATE " + tablename + " AS a, " + cmfTable + " AS b SET ") 727 728 sqlLine.group("a."+filter+"haveData", "'1'") 726 729 727 730 for number in numbers: … … 762 765 self.tablesToExport.append("StackToImage") 763 766 764 ''' 765 Reports and deletes detections in this table that are not in StackObject 766 ''' 767 def deleteDetectionsNotInStackObject(self, table, filter): 768 f= filter 769 770 sql = "SELECT COUNT(*) FROM " + table + " WHERE "+f+"ippDetectID NOT IN (SELECT "+f+"ippDetectID FROM StackObject)" 767 ### *********************** Utility Functions ************************************ 768 769 ''' 770 Drop a table and report the drop 771 ''' 772 def dropTableVerbose(self, table): 773 self.logger.infoPair("dropping table:",table) 774 self.scratchDb.dropTable(table) 775 776 ''' 777 get stackTypeID corresponding to our stackType 778 ''' 779 def getStackTypeID(self): 780 781 sql = "SELECT stackTypeID from StackType WHERE name = '" + self.stackType + "'" 771 782 rs = self.scratchDb.executeQuery(sql) 772 rs.first() 773 nMissing = rs.getInt(1) 774 775 if nMissing < 1: return 783 if not rs.next(): 784 self.logger.errorPair("cannot get stack type ID from database for", self.stackType) 785 raise 776 786 777 sql = "DELETE FROM " + table + " WHERE "+f+"ippDetectID NOT IN (SELECT "+f+"ippDetectID FROM StackObject)" 778 self.scratchDb.execute(sql) 779 self.logger.infoPair("Detections not in StackObject", "%d deleted from %s" % (nMissing, table)) 780 781 782 def deleteNULLippDetectIDs(self, tableName): 783 784 self.logger.infoPair("Deleting NULL ippdetectIDs from table", tableName) 785 sql = "DELETE FROM " + tableName + " WHERE \ 786 gippDetectID is NULL AND \ 787 rippDetectID is NULL AND \ 788 iippDetectID is NULL AND \ 789 zippDetectID is NULL AND \ 790 yippDetectID is NULL " 787 ID = rs.getInt(1) 788 return ID 789 790 def deleteRowsWithNoStackData(self, tableName): 791 792 self.logger.infoPair("Deleting rows with no stack data from", tableName) 793 sql = "DELETE FROM " + tableName + " WHERE " 794 795 for i in range(len(self.filters)): 796 filter = self.filters[i] 797 sql += filter + "haveData is NULL" 798 799 if (i < len(self.filters) - 1): 800 sql += " AND " 801 791 802 self.logger.infoPair('sql',sql) 792 803 self.scratchDb.execute(sql) … … 798 809 799 810 self.logger.debug("Altering PSPS tables (currently does nothing in stackbatch)") 800 # heather uncommented the unique line -- well that no worky801 #self.scratchDb.makeColumnUnique("StackObject", "objID")802 #self.scratchDb.createIndex("StackObject", "ippDetectID")803 #self.scratchDb.createIndex("StackObject", "stackDetectID")804 #self.scratchDb.createIndex("StackApFlx", "ippDetectID")805 #self.scratchDb.createIndex("StackModelFit", "ippDetectID")806 811 807 812 return True … … 827 832 ''' 828 833 select objID (psps object ID) from dvo restricted to this set of imageIDs. also insert 829 ippObjID and several constants (for a skycell): surveyID, skyCellId, d voRegionID, dataRelease834 ippObjID and several constants (for a skycell): surveyID, skyCellId, dataRelease 830 835 ''' 831 836 def selectDvoObjIDs(self): 837 838 # we need a temporary column for each filter to identify empty rows 839 for filter in self.filters: 840 self.scratchDb.addColumn("StackObjectThin", filter + "haveData", "tinyint") 832 841 833 842 sqlLine = sqlUtility("INSERT INTO StackObjectThin (") … … 836 845 sqlLine.group("ippObjID", "ippObjID") 837 846 sqlLine.group("surveyID", "'" + str(self.surveyID) + "'") 838 sqlLine.group("skyCellID", "'" + str(self.skycell) + "'") 839 sqlLine.group("dvoRegionID", "'" + str(self.dvoRegionID) + "'") 847 sqlLine.group("tessID", "'" + str(self.tessID) + "'") 848 sqlLine.group("projectionID", "'" + str(self.projectionID) + "'") 849 sqlLine.group("skyCellID", "'" + str(self.skycellID) + "'") 840 850 sqlLine.group("dataRelease", "'" + str(self.skychunk.dataRelease) + "'") 841 851 sqlLine.group("randomStackObjID", "FLOOR(RAND()*9223372036854775807)") … … 864 874 def insertDvoIDs(self, table, mainTable): 865 875 866 fields = "objID, ippObjID, surveyID, skyCellID, randomStackObjID, primaryDetection, dvoRegionID, dataRelease, " 876 # we need a temporary column for each filter to identify empty rows 877 for filter in self.filters: 878 self.scratchDb.addColumn(table, filter + "haveData", "tinyint") 879 880 fields = "objID, ippObjID, randomStackObjID, primaryDetection, bestDetection, " 867 881 868 882 for i in range(len(self.filters)): … … 882 896 Updates provided table with DVO IDs from DVO table 883 897 ''' 884 def updateDvoIDs(self, table):885 self.logger.infoPair("getting imageID for ","filter")886 imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])887 self.logger.infoPair("obtained","imageID")888 self.logger.debug("Updating table '" + table + "' with DVO IDs...")889 sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \890 a.ippObjID = b.ippObjID, \891 a.stackDetectID = b.detectID, \892 a.objID = b.objID \893 WHERE a.ippDetectID = b.ippDetectID \894 AND b.imageID = " + str(imageID)895 self.scratchDb.execute(sql)898 ## XX def updateDvoIDs(self, table): 899 ## XX self.logger.infoPair("getting imageID for ","filter") 900 ## XX imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) 901 ## XX self.logger.infoPair("obtained","imageID") 902 ## XX self.logger.debug("Updating table '" + table + "' with DVO IDs...") 903 ## XX sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ 904 ## XX a.ippObjID = b.ippObjID, \ 905 ## XX a.stackDetectID = b.detectID, \ 906 ## XX a.objID = b.objID \ 907 ## XX WHERE a.ippDetectID = b.ippDetectID \ 908 ## XX AND b.imageID = " + str(imageID) 909 ## XX self.scratchDb.execute(sql) 896 910 897 911 ''' 898 912 Updates Flags 899 913 ''' 900 def updateDvoIDsAndFlags(self, table):901 self.logger.infoPair("getting","imageID")902 imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID'])903 self.logger.infoPair("obtained","imageID")904 self.logger.debug("Updating table '" + table + "' with DVO IDs...")905 sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \906 a.ippObjID = b.ippObjID, \907 a.stackDetectID = b.detectID, \908 a.objID = b.objID, \909 a.infoFlag = b.flags << 45 | a.infoFlag, \910 a.primaryF = ((b.flags & 0x10000) >> 16 ) \911 WHERE a.ippDetectID = b.ippDetectID \912 AND b.imageID = " + str(imageID)913 914 print "update dvo sql: ", sql915 self.scratchDb.execute(sql)914 ## XX def updateDvoIDsAndFlags(self, table): 915 ## XX self.logger.infoPair("getting","imageID") 916 ## XX imageID = self.scratchDb.getImageIDFromExternID(self.header['IMAGEID']) 917 ## XX self.logger.infoPair("obtained","imageID") 918 ## XX self.logger.debug("Updating table '" + table + "' with DVO IDs...") 919 ## XX sql = "UPDATE " + table + " AS a, " + self.scratchDb.dvoDetectionTable + " AS b SET \ 920 ## XX a.ippObjID = b.ippObjID, \ 921 ## XX a.stackDetectID = b.detectID, \ 922 ## XX a.objID = b.objID, \ 923 ## XX a.infoFlag = b.flags << 45 | a.infoFlag, \ 924 ## XX a.primaryF = ((b.flags & 0x10000) >> 16 ) \ 925 ## XX WHERE a.ippDetectID = b.ippDetectID \ 926 ## XX AND b.imageID = " + str(imageID) 927 ## XX 928 ## XX print "update dvo sql: ", sql 929 ## XX self.scratchDb.execute(sql) 916 930 917 931 ''' … … 925 939 self.tablesToExport=[] 926 940 927 # insertDvoIDs called below populates the given StackDetection 928 # tables with the DVO object IDs by querying the dvoDetection 929 # table for detections associated with the corresponding image 930 # IDs. XXX EAM 20140729 : Is this an efficient method? 931 932 # EAM 20140730 : I have revised the StackObjectXX construction process. 933 # The challenge here is that we have dvo-generated objIDs and ippObjIDs 934 # for each stack detection in seperate rows, but we want to merge the 935 # 5 filter sets into a single set of rows. The old code was 936 # relying on INSERT IGNORE, making it hard to catch errors, 937 # and probably not doing the insert very efficiently 941 # EAM 20140730 : I have revised the StackObjectXX construction 942 # process. The challenge here is that we have dvo-generated 943 # objIDs and ippObjIDs for each stack detection in seperate 944 # rows, but we want to merge the 5 filter sets into a single 945 # set of rows. The old code was relying on INSERT IGNORE, 946 # making it hard to catch errors, and probably not doing the 947 # insert very efficiently 938 948 939 949 # Now we do a first pass with SELECT DISTINCT to get the … … 957 967 self.populateStackObjectExtra() 958 968 959 # self.populateRandomsAndSkyCellID()960 961 969 if self.stackType != "NIGHTLY_STACK": 962 970 … … 971 979 self.populateStackPetrosian() 972 980 973 self.populateStackModelFit("Ser", "PS_MODEL_SERSIC")974 975 981 self.logger.infoPair("populating ","StackApFlx Tables") 976 982 self.populateStackApFlxThin() … … 980 986 self.populateStackApFlx("C2") 981 987 988 # delete rows with no content. for the other tables, this is done in 989 # the 'populate' functions, but StackObjectThin needs to retain all rows 990 # until those tables are done 991 self.deleteRowsWithNoStackData("StackObjectThin") 992 982 993 self.logger.infoPair("populating","StackToImage") 983 994 self.populateStackToImage() 984 995 985 # self.setMinMaxObjID(["StackObject"])996 self.setMinMaxObjID(["StackObjectThin"]) 986 997 987 998 self.logger.infoPair("finishing","populatePspsTables");
Note:
See TracChangeset
for help on using the changeset viewer.
