Changeset 31367

Timestamp:

Apr 25, 2011, 9:40:49 AM (15 years ago)

Author:

rhenders

Message:

now does all SQL necessary to build up dvoDetection and dvoMeta tables

File:

• trunk/ippToPsps/jython/dvoToMySQL.py (modified) (5 diffs)

trunk/ippToPsps/jython/dvoToMySQL.py

@@ -42 +42 @@
         self.gpc1Db = Gpc1Db(self.logger)
 
-        # create DVO table
+        # create DVO tables
         self.scratchDb.createDvoTables()
 
-        # now import tables
-        self.importFits(self.pathToDvo, "Images.dat", 
-                "IMAGE_ID SOURCE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE")
+        # import Images.dat table
+        imagesTableName = self.importFits(self.pathToDvo, 
+                "", 
+                "Images.dat", 
+                "IMAGE_ID SOURCE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE NSTAR")
+        self.scratchDb.createIndex(imagesTableName, "IMAGE_ID")
+
+        # insert into dvoMeta
+        self.logger.info("Inserting all image meta data into database")
+        sql = "INSERT INTO dvoMeta ( \
+               sourceID, \
+               imageID, \
+               flags, \
+               photcode \
+               ) SELECT \
+               SOURCE_ID, \
+               IMAGE_ID, \
+               FLAGS, \
+               PHOTCODE \
+               FROM " + imagesTableName
+        self.scratchDb.stmt.execute(sql)
+
+        subdirs = ['n0000']
+
+        for subdir in subdirs:
+
+            files = ['0247.06', '0244.06', '0244.10']
+
+            for file in files:
+
+                self.logger.info("---------------------------------------------")
+
+                # import cpm table and index
+                cpmTableName = self.importFits(self.pathToDvo, 
+                        subdir, 
+                        file + ".cpm", 
+                        "IMAGE_ID DET_ID OBJ_ID CAT_ID EXT_ID DB_FLAGS")
+                self.scratchDb.createIndex(cpmTableName, "CAT_ID")
+                self.scratchDb.createIndex(cpmTableName, "OBJ_ID")
+
+                # import cpt table and index
+                cptTableName = self.importFits(self.pathToDvo, 
+                        subdir, 
+                        file + ".cpt", 
+                        "OBJ_ID CAT_ID EXT_ID")
+                self.scratchDb.createIndex(cptTableName, "CAT_ID")
+                self.scratchDb.createIndex(cptTableName, "OBJ_ID")
+      
+                # shove SOURCE_IDs into measurement table
+                self.logger.info("Adding SOURCE_IDs into measurements table")
+                sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (SOURCE_ID SMALLINT)"
+                self.scratchDb.stmt.execute(sql)
+                sql = "UPDATE "+cpmTableName+" AS a, "+imagesTableName+" AS b \
+                       SET a.SOURCE_ID = b.SOURCE_ID \
+                       WHERE a.IMAGE_ID = b.IMAGE_ID"
+                self.scratchDb.stmt.execute(sql)
+
+                # shove PSPS objID in measurement table
+                self.logger.info("Adding PSPS objID into measurements table")
+                sql = "ALTER TABLE "+cpmTableName+" ADD COLUMN (PSPS_OBJ_ID BIGINT)"
+                self.scratchDb.stmt.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.stmt.execute(sql)
+
+                # now put everything into dvoDetection table
+                self.logger.info("Putting everything into dvoDetection table")
+                sql = "INSERT INTO dvoDetection (\
+                       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
+                self.scratchDb.stmt.execute(sql)
+
+                # now drop what we don't need
+                self.logger.info("Dropping tables")
+                self.scratchDb.dropTable(cpmTableName)
+                self.scratchDb.dropTable(cptTableName)
         
+        self.scratchDb.dropTable(imagesTableName)
+
 
     '''
@@ -60 +151 @@
     Imports a FITS file. A regex filter lets you choose which tables to pull from the file
     '''
-    def importFits(self, path, file, columns):
+    def importFits(self, path, subdir, file, columns):
 
-      fullPath = path + "/" + file
-      self.logger.info("Attempting to import tables from: " + fullPath)
+      fullPath = path + "/" + subdir + "/" + file
+
+      if len(subdir) < 1: tableName = file
+      else: tableName = subdir + "_" + file
+
+      tableName = tableName.replace('.', '_')
+
+      self.logger.info("Attempting to import tables from '" + fullPath + "' to '" + tableName + "'")
 
       tables = stilts.treads(fullPath)
@@ -70 +167 @@
       for table in tables:
 
-          self.logger.info("   Reading IPP table " + table.name + " from FITS file")
+          self.logger.info("Reading IPP table " + table.name + " from FITS file")
           table = stilts.tpipe(table, cmd='explodeall')
      
-          self.scratchDb.dropTable(table.name)
-
-          table.cmd_keepcols('PHOTCODE')
           # IPP FITS files are littered with infinities, so remove these
-          self.logger.info("   Removing Infinity values from all columns")
+          self.logger.info("Removing Infinity values from all columns")
           table = stilts.tpipe(table, cmd='replaceval -Infinity null *')
           table = stilts.tpipe(table, cmd='replaceval Infinity null *')
 
           #try:
-          table.cmd_keepcols('IMAGE_ID SOURCE_ID CCDNUM EXTERN_ID FLAGS PHOTCODE').write(self.scratchDb.url + '#' + file)
+          table.cmd_keepcols(columns).write(self.scratchDb.url + '#' + tableName)
           #except:
           #    self.logger.exception("   Problem writing table '" + table.name + "' to the database")
@@ -89 +183 @@
       self.logger.info("Done. Imported %d tables" % count)
 
+      return tableName
 
 logging.config.fileConfig("logging.conf")
@@ -96 +191 @@
 dvoToMySql = DvoToMySql(logger, "/data/ipp005.0/gpc1/catdirs/MD04.merges/MD04.merge")
 
+logger.info("Program complete")