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()
