Index: trunk/ippToPsps/jython/dvo.py =================================================================== --- trunk/ippToPsps/jython/dvo.py (revision 33154) +++ trunk/ippToPsps/jython/dvo.py (revision 33154) @@ -0,0 +1,270 @@ +#!/usr/bin/env jython + +import stilts +import datetime +import re +import sys +import os +import logging +import glob +from subprocess import call, PIPE, Popen + +from pslogger import PSLogger +from scratchdb import ScratchDb + +from java.lang import * +from java.sql import * +from xml.etree.ElementTree import ElementTree, Element, tostring + + +''' +A class that encapsulates a DVO database + +Pulls relavant bits and pieces into the scratch MySQL database and then provides access queries +''' +class Dvo(object): + + ''' + Constructor + + ''' + def __init__(self, logger, doc): + + # set up logging + self.logger = logger + self.doc = doc + self.logger.infoSeparator() + self.dvoLocation = self.doc.find("dvo/location").text + + # create database object + self.scratchDb = ScratchDb(logger, self.doc, 1) + + # some log stuff + self.logger.infoPair("Started", "dvo") + + ''' + Destructor + ''' + def __del__(self): + + self.logger.debug("Dvo destructor") + + + ''' + Resets all dvo tables. Be very careful.... + ''' + def resetAllTables(self): + self.scratchDb.resetDvoToMysqlTables() + + ''' + Loads the DVO Iamges.dat FITS file + ''' + def loadImages(self): + + # import Images.dat table + self.logger.infoPair("Deleting from table", self.scratchDb.dvoMetaTable) + sql = "DELETE FROM " + self.scratchDb.dvoMetaTable + self.scratchDb.execute(sql) + + self.imagesTableName = self.importFits( + "Images.dat", + "SOURCE_ID IMAGE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR") + self.scratchDb.createIndex(self.imagesTableName, "EXTERN_ID") + + # insert into dvoMetaFull + # NB what we and smf files call IMAGE_ID, DVO calls EXTERN_ID. We are sticking + # with the smf name + self.logger.infoPair("Populating", "all image meta data") + sql = "INSERT INTO " + self.scratchDb.dvoMetaTable + " ( \ + sourceID, \ + imageID, \ + externID, \ + flags, \ + photcode \ + ) SELECT \ + SOURCE_ID, \ + IMAGE_ID, \ + EXTERN_ID, \ + FLAGS, \ + PHOTCODE \ + FROM " + self.imagesTableName + self.scratchDb.execute(sql) + + + ''' + Loads the DVO SkyTable into MySQL and then applies an index + ''' + def loadSkyTable(self): + + self.skyTableName = self.importFits( + "SkyTable.fits", + "R_MIN R_MAX D_MIN D_MAX INDEX NAME") + + self.logger.infoPair("Adding index to", self.skyTableName) + + self.scratchDb.createIndex(self.skyTableName, "INDEX") + + self.logger.infoPair("Finished importing SkyTable at", self.dvoLocation) + + + ''' + Populates dvoDetections with FITS tables from DVO for the whole sky + ''' + def loadRegion(self, minRa, maxRa, minDec, maxDec): + self.loadRegion(-1, 361, -91, 91) + + ''' + Populates dvoDetections and dvoMeta with FITS tables from DVO for the defined region + ''' + def loadRegion(self, minRa, maxRa, minDec, maxDec): + + files = self.scratchDb.getDvoFilesCoveringThisRegion(minRa, maxRa, minDec, maxDec) + + count = 0 + for file in files: + path = self.dvoLocation + "/" + file + ".cpm" + if not os.path.isfile(path): continue + count = count + 1 + print path + + if self.scratchDb.alreadyImportedThisDvoTable(file): continue + + # import cpm table and index + cpmTableName = self.importFits( + file + ".cpm", + "IMAGE_ID DET_ID OBJ_ID CAT_ID EXT_ID DB_FLAGS") + self.scratchDb.createIndex(cpmTableName, "IMAGE_ID") + self.scratchDb.createIndex(cpmTableName, "CAT_ID") + self.scratchDb.createIndex(cpmTableName, "OBJ_ID") + + # import cpt table and index + cptTableName = self.importFits( + file + ".cpt", + "OBJ_ID CAT_ID EXT_ID") + self.scratchDb.createIndex(cptTableName, "IMAGE_ID") + self.scratchDb.createIndex(cptTableName, "CAT_ID") + self.scratchDb.createIndex(cptTableName, "OBJ_ID") + + # shove SOURCE_IDs into measurement table + self.logger.infoPair("Adding", "SOURCE_IDs") + sql = "ALTER TABLE " + cpmTableName + " ADD COLUMN (SOURCE_ID SMALLINT)" + self.scratchDb.execute(sql) + sql = "UPDATE " + cpmTableName + " AS a, " + self.imagesTableName + " AS b \ + SET a.SOURCE_ID = b.SOURCE_ID \ + WHERE a.IMAGE_ID = b.IMAGE_ID" + self.scratchDb.execute(sql) + + # shove PSPS objID in measurement table + self.logger.infoPair("Adding","PSPS objIDs") + sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (PSPS_OBJ_ID BIGINT)" + self.scratchDb.execute(sql) + sql = "UPDATE "+cpmTableName+" AS a, "+cptTableName+" AS b \ + SET a.PSPS_OBJ_ID = b.EXT_ID \ + WHERE a.CAT_ID = b.CAT_ID \ + AND a.OBJ_ID = b.OBJ_ID" + self.scratchDb.execute(sql) + + + # NB we use an INSERT IGNORE here. This is because of a known issue where multiple DVO cpm + # files can include the same detection, with the same IMAGE_ID/DET_ID pairing, but different + # PSPS object IDs assigned in the corresponding cpt file. This is believed to be a chip-boundary + # issue within DVO. So, for now, we take the first IMAGE_ID/DET_ID detection we find, and ignore the rest + self.logger.infoPair("Populating", self.scratchDb.dvoDetectionTable) + sql = "INSERT IGNORE INTO " + self.scratchDb.dvoDetectionTable + " (\ + sourceID \ + ,imageID \ + ,ippDetectID \ + ,detectID \ + ,ippObjID \ + ,objID \ + ,flags \ + ) SELECT \ + SOURCE_ID \ + ,IMAGE_ID \ + ,DET_ID \ + ,EXT_ID \ + ,CAT_ID * 1000000000 + OBJ_ID \ + ,PSPS_OBJ_ID \ + ,DB_FLAGS \ + FROM " + cpmTableName + try: + self.scratchDb.execute(sql) + except: + self.logger.error("FAILED: " + sql) + return + + # now drop what we don't need + self.logger.infoPair("Dropping table", cpmTableName) + #self.scratchDb.dropTable(cpmTableName) + self.logger.infoPair("Dropping table", cptTableName) + #self.scratchDb.dropTable(cptTableName) + + self.scratchDb.setImportedThisDvoTable(file) + + self.logger.infoPair("Found", "%d region files" % count) + + + ''' + Imports a FITS file. A regex filter lets you choose which tables to pull from the file + ''' + def importFits(self, file, columns): + + fullPath = self.dvoLocation + "/" + file + self.logger.infoPair("Importing file", fullPath) + + tableName = file + tableName = tableName.replace('.', '_') + tableName = tableName.replace('/', '_') + + self.logger.infoPair("Writing to database table", tableName) + + tables = stilts.treads(fullPath) + + count = 0 + for table in tables: + + self.logger.infoPair("Reading IPP table", table.name) + table = stilts.tpipe(table, cmd='explodeall') + + # IPP FITS files are littered with infinities, so remove these + self.logger.infoPair("Removing", "infinity values") + table = stilts.tpipe(table, cmd='replaceval -Infinity null *') + table = stilts.tpipe(table, cmd='replaceval Infinity null *') + + #try: + self.logger.infoPair("Writing " + table.name + " table to", "database") + table.cmd_keepcols(columns).write(self.scratchDb.url + '#' + tableName) + #except: + # self.logger.exception(" Problem writing table '" + table.name + "' to the database") + count = count + 1 + + + self.logger.infoPair("Finished importing", tableName) + + return tableName + + +''' +Start of program. +''' + +if len(sys.argv) > 1: CONFIG = sys.argv[1] +else: + print "** Usage: " + sys.argv[0] + " " + sys.exit(1) + +# open config file +configDoc = ElementTree(file=CONFIG) + +logging.setLoggerClass(PSLogger) +logger = logging.getLogger("dvo") +logger.setup(configDoc, "dvo", 0, 1) + +dvo = Dvo(logger, configDoc) +#dvo.resetAllTables() +#dvo.loadImages() +#dvo.loadSkyTable() +dvo.loadRegion(330, 330.1, 0.3, 0.4) + +logger.infoPair("Program...", "complete") +