def handle(self, fitsInfo): keywords = fitsInfo[0].header classType = astroUtil.getKeyword(keywords, "CLASS", "1D_CLASS") if classType == "SKY": return ra = astroUtil.getKeyword(keywords, 'RA') dec = astroUtil.getKeyword(keywords, 'DEC') subClass = astroUtil.getKeyword(keywords, "SUBCLASS", "1D_SUBCL") mainClass = astroUtil.getMainClass(classType, subClass) date = astroUtil.getKeyword(keywords, "DATE-OBS")[0:10] planID = astroUtil.getKeyword(keywords, "PLANID") z = astroUtil.getKeyword(keywords, "Z", "1D_Z") z_err = astroUtil.getKeyword(keywords, "1D_Z_ERR", "Z_ERR") version_idl = astroUtil.getKeyword(keywords, "VERSIDL") version_ppl = astroUtil.getKeyword(keywords, "VERS2D", "VERSPIPE") SN_U = astroUtil.getKeyword(keywords, "SN_U") SN_G = astroUtil.getKeyword(keywords, "SN_G") SN_R = astroUtil.getKeyword(keywords, "SN_R") SN_I = astroUtil.getKeyword(keywords, "SN_I") SN_Z = astroUtil.getKeyword(keywords, "SN_Z") sqlRecord = [planID, version_idl, version_ppl, date, ra, dec, classType, mainClass, subClass, z, z_err, SN_U, SN_G, SN_R, SN_I, SN_Z] sqlHelper.execWithValues(self.conn, self.sql_insert, sqlRecord) self.count += 1
def getInputData(prefixes, table, sqlFilter): fieldList = genMagByPrefix(prefixes) fieldList.append("`type`") fieldList.append("mainclass") sql_select = sqlHelper.genSelectSQLWithFieldList(fieldList, table, sqlFilter) outFile = "data_input.txt" outFile_tag = "data_input_tag.txt" output = open(outFile, "w") output_tag = open(outFile_tag, "w") count = 0 res = [] tags = [] data = sqlHelper.getFetchAll(sql_select) for item in data: record = [] colCount = len(item) for i in range(colCount - 1): record.append(item[i]) res.append(record) tags.append(item[colCount - 1]) output.write(util.listToString(record)) output.write("\n") output_tag.write(item[colCount - 1]) output_tag.write("\n") count += 1 print "input data count : %s" % count output.close() output_tag.close() return np.mat(res), np.mat(tags).T
def migrationLamost(insertTable, sql_filter, colCount=40): sql_fields = "specid,obsdate,mjd,planid,spid,fiberid,dataversion,specpath" sql_fields += ",objra,objdec,final_class,final_subclass,final_z,final_z_err,snru,snrg,snrr,snri,snrz" sql_fields += ",teff,teff_err,teff_err_c,teff_err_cs" sql_fields += ",logg,logg_err,logg_err_c,logg_err_cs" sql_fields += ",feh,feh_err,feh_err_c,feh_err_cs" sql_fields += ",rv,rv_err,rv_err_c,rv_err_cs" sql_fields += ",method,exetime" sql_from_dr2 = "dr2.spec_info left join dr2.param_info on dr2.spec_info.specid =dr2.param_info.specid" sql_from_dr3 = "dr3.dr3_alpha left join dr3.param_alpha on dr3.dr3_alpha.specid =dr3.param_alpha.specid" sql_insert = sqlHelper.genInsertSQL(insertTable, colCount) sql_fields_dr2 = "dr2.spec_info." + sql_fields + ",0 as distance,2 as dr" sql_fields_dr3 = "dr3.dr3_alpha." + sql_fields + ",distance,3 as dr" sql_select_dr2 = sqlHelper.genSelectSQL(sql_fields_dr2, sql_from_dr2, sql_filter) sql_select_dr3 = sqlHelper.genSelectSQL(sql_fields_dr3, sql_from_dr3, sql_filter) importLamostData(sql_select_dr2, sql_insert) importLamostData(sql_select_dr3, sql_insert)
def PanstarrsCrossUpdate(specid, ra, dec, conn, cache): data = getCatchData(ra, dec, cache) if data is None: print "failed [file not exist]:" + specid return False for row in data: ra_panstarrs = row[0] dec_panstarrs = row[1] if astroUtil.checkDuplicate(ra, dec, ra_panstarrs, dec_panstarrs): # mags = row[6] # values = [mags[0], mags[1], mags[2], mags[3], mags[4], specid] values = [row[2], row[3], row[4], row[5], row[6], specid] sqlHelper.execWithValues(conn, sql_update, values) print "succeed :" + specid return True print "failed :" + specid return False
def importLamostData(sql_select, sql_insert): conn = sqlHelper.getMySQLConn() conn45 = sqlHelper.getMySQL45Conn() count = 0 data = sqlHelper.getFetchManyByConn(conn45, sql_select) for info in data: sqlRecord = [] colIndex = 1 try: for col in info: if colIndex == 13: classType = info[10] subClass = info[11] mainClass = astroUtil.getMainClass(classType, subClass) sqlRecord.append(mainClass) sqlRecord.append(col) colIndex += 1 sqlHelper.execWithValues(conn, sql_insert, sqlRecord) count += 1 except Exception, ex: exUtil.printMessage(ex, "Migration")
def run(self): crossedCount = 0 minDis = 100 minID = 0 for row in self.data_sdss: ra_sdss = row[1] dec_sdss = row[2] distance = getDistance(self.ra, self.dec, ra_sdss, dec_sdss) if distance == 100: continue if distance < minDis: minDis = distance minID = row[0] crossedCount += 1 if crossedCount == 0: print "failed :" + specid else: sqlHelper.execWithValues(self.conn, sql_insert, [self.specid, minID]) print "%s - succeed [%s] : %s" % (self.totalCount, str(crossedCount), specid)
def M31RegionSQLGen(): sql_select = "SELECT * FROM dr3.M31_Coordinate" sql_select_vo = "SELECT objID,ra,dec,petroMag_u,petroMag_g,petroMag_r,petroMag_i,petroMag_z FROM BestDR12.dbo.PhotoObjALl WHERE " conn, data = sqlHelper.getFetchMany(sql_select) dic_radec = util.getRaDecMap(data) sql_whereList = [] for ra in dic_radec.keys(): minDec = min(dic_radec[ra]) maxDec = max(dic_radec[ra]) sql_whereList.append(genSQL(ra, minDec, maxDec)) sql_where = util.listToString(sql_whereList, "or") sql_select_vo += sql_where print sql_select_vo
def insertCoordinate(sql_select, sql_select_count, insertTable): coordinateDic = getCoordinateDic(sql_select, sql_select_count) conn = sqlHelper.getMySQLConn() sql_insert = sqlHelper.genInsertSQL(insertTable, 3) for x in coordinateDic.keys(): for y in coordinateDic[x].keys(): count = coordinateDic[x][y] sqlHelper.execWithValues(conn, sql_insert, [x, y, count]) sqlHelper.closeConn(conn)
def migrationSDSS(filePath, tableName, colCount, blankColCount=0): sql_insert = sqlHelper.genInsertSQL(tableName, colCount) data = open(filePath) conn = sqlHelper.getMySQLConn() count = 0 for row in data: cols = row.split(",") if blankColCount > 0: for i in range(blankColCount): cols.append("") sqlHelper.execWithValues(conn, sql_insert, cols) count += 1 if count % 10000 == 0: print(count) sqlHelper.closeConn(conn)
def dereplicationByCoordinate(tableName, idField="objID", raField="ra", decField="dec", orderbyField="objID"): sql_select = "select %s,%s,`%s` from %s order by %s desc" % (idField, raField, decField, tableName, orderbyField) sql_delete = "delete from " + tableName + " where " + idField + " = %s" data = sqlHelper.getFetchAll(sql_select) data_saved = [] conn = sqlHelper.getMySQLConn() count = 0 derepCount = 0 for row in data: objID = row[0] ra = row[1] dec = row[2] if checkReplication(ra, dec, data_saved): sqlHelper.execWithValues(conn, sql_delete, [objID]) derepCount += 1 print str(derepCount) + ":" + str(objID) else: data_saved.insert(0, [ra, dec]) count += 1 if count % 1000 == 0: print "handled count : " + str(count) print "total : %s , derep : %s" % (count, derepCount) sqlHelper.closeConn(conn)
def preHandle(self): self.sql_insert = sqlHelper.genInsertSQL(self.insertTable, 20) self.conn = sqlHelper.getMySQLConn()
def getSpecTypeByTeff(teff, column="teff_m"): sql = "SELECT specType from DM.SPECTYPE where %s <= %s order by %s desc limit 0 ,1" % (column, teff, column) res = sqlHelper.getList(sql) return res[0]
def deleteBySpecID(specID, table): sql = "delete from %s where objID= \'%s\'" % (table, specID) sqlHelper.execSQL(sql)
ra_panstarrs = row[0] dec_panstarrs = row[1] if astroUtil.checkDuplicate(ra, dec, ra_panstarrs, dec_panstarrs): # mags = row[6] # values = [mags[0], mags[1], mags[2], mags[3], mags[4], specid] values = [row[2], row[3], row[4], row[5], row[6], specid] sqlHelper.execWithValues(conn, sql_update, values) print "succeed :" + specid return True print "failed :" + specid return False specids, ras, decs, count = dfh.get3FieldsData(sql_select, sql_count) conn = sqlHelper.getMySQLConn() succeedCount = 0 currentCount = 0 dic = [] for i in range(count): specid = str(specids[i]) ra = float(ras[i]) dec = float(decs[i]) if PanstarrsCrossUpdate(specid, ra, dec, conn, dic): succeedCount += 1 currentCount += 1 print "succeed/current count %d/%d:" % (succeedCount, currentCount)
colIndex = 1 try: for col in info: if colIndex == 13: classType = info[10] subClass = info[11] mainClass = astroUtil.getMainClass(classType, subClass) sqlRecord.append(mainClass) sqlRecord.append(col) colIndex += 1 sqlHelper.execWithValues(conn, sql_insert, sqlRecord) count += 1 except Exception, ex: exUtil.printMessage(ex, "Migration") sqlHelper.closeConn(conn45) sqlHelper.closeConn(conn) def migrationLamost(insertTable, sql_filter, colCount=40): sql_fields = "specid,obsdate,mjd,planid,spid,fiberid,dataversion,specpath" sql_fields += ",objra,objdec,final_class,final_subclass,final_z,final_z_err,snru,snrg,snrr,snri,snrz" sql_fields += ",teff,teff_err,teff_err_c,teff_err_cs" sql_fields += ",logg,logg_err,logg_err_c,logg_err_cs" sql_fields += ",feh,feh_err,feh_err_c,feh_err_cs" sql_fields += ",rv,rv_err,rv_err_c,rv_err_cs" sql_fields += ",method,exetime" sql_from_dr2 = "dr2.spec_info left join dr2.param_info on dr2.spec_info.specid =dr2.param_info.specid" sql_from_dr3 = "dr3.dr3_alpha left join dr3.param_alpha on dr3.dr3_alpha.specid =dr3.param_alpha.specid" sql_insert = sqlHelper.genInsertSQL(insertTable, colCount)
data = sqlHelper.getFetchAll(sql_select) data_saved = [] conn = sqlHelper.getMySQLConn() count = 0 derepCount = 0 for row in data: objID = row[0] ra = row[1] dec = row[2] if checkReplication(ra, dec, data_saved): sqlHelper.execWithValues(conn, sql_delete, [objID]) derepCount += 1 print str(derepCount) + ":" + str(objID) else: data_saved.insert(0, [ra, dec]) count += 1 if count % 1000 == 0: print "handled count : " + str(count) print "total : %s , derep : %s" % (count, derepCount) sqlHelper.closeConn(conn) # dereplicationByCoordinate("DM.SDSS_DEREP") dereplicationByCoordinate("DM.LAMOST_DEREP", "specid", "objra", "objdec", "obsdate") sqlHelper.execSQL("delete from DM.LAMOST_DEREP where final_class = \"VeryLowFlux\"") sqlHelper.execSQL("delete from DM.LAMOST_DEREP where snrr<6 and snrg<6") sqlHelper.execSQL( "delete from DM.LAMOST_DEREP where final_class = 'Dead' or final_class ='PosErr' or final_class = 'Unknown'")
def getMainClassList(sql_distinct, onlyShowAll): mainClassList = [] if not onlyShowAll: mainClassList = sqlHelper.getList(sql_distinct) mainClassList.append("ALL") return mainClassList
if distance == 100: continue if distance < minDis: minDis = distance minID = row[0] crossedCount += 1 if crossedCount == 0: print "failed :" + specid else: sqlHelper.execWithValues(self.conn, sql_insert, [self.specid, minID]) print "%s - succeed [%s] : %s" % (self.totalCount, str(crossedCount), specid) data1 = sqlHelper.getFetchAll(sql_select_lamost) data2 = sqlHelper.getFetchAll(sql_select_sdss) data_lamost = [] data_sdss = [] for row in data1: data_lamost.append([row[0], float(row[1]), float(row[2])]) for row in data2: data_sdss.append([row[0], float(row[1]), float(row[2])]) conn = sqlHelper.getMySQLConn() totalCount = 0 for row_lamost in data_lamost: specid = row_lamost[0]
def handled(self): sqlHelper.closeConn(self.conn)
def getCoordinateDic(tableName): sql_select = "SELECT * FROM " + tableName data = sqlHelper.getFetchAll(sql_select) return astroUtil.getCoordinateCountMap(data)
# coding=utf-8 __author__ = 'zdwdong' """ 画 teff/logg散点图 """ import CodeLib.Database.MySQLHepler as sqlHelper import matplotlib.pyplot as plt import CodeLib.Util.PlotUtil as plotUtil sql_select = "select teff,logg from dr3.M31_ELL where teff>0 and logg>0" data = sqlHelper.getFetchAll(sql_select) teff = [] logg = [] for dataRow in data: teff.append(float(dataRow[0])) logg.append(float(dataRow[1])) plt.title("Lamost Logg_teff") plt.plot(teff, logg, '.') plt.xlabel('teff') plt.ylabel('logg') plotUtil.plotGrid(plt) plotUtil.invertX(plt) plt.show()