def __init__(self,trigger_id):
self.connection = ea.connect(DATABASE)
self.cursor = connection.cursor()
dire = './processing/'+trigger_id+'/'
if not os.path.exists(dire):
os.makedirs(dire)
def _make_conn(self):
sources = self.get_sources()
if sources is not None:
# share connection with the sources
conn=sources.get_conn()
else:
import easyaccess as ea
conn=ea.connect(section='desoper')
self._conn=conn
def get_paths(self, expnum, ccd, tag='Y3A1_FINALCUT'):
try:
ccd = ','.join(map(str, ccd))
except:
pass
inputs = dict(expnum=expnum, ccd=ccd, tag=tag)
self.base_query = self.base_query.format(**inputs)
print(self.base_query)
con = easyaccess.connect(self._db, user=self.user, passwd=self._passwd)
self.data = con.query_to_pandas(self.base_query)
print(self.data)
for j in range(len(self.data)):
self.links.append(self.root_url + self.data.PATH.ix[j])
def run(args):
usernm = args.usernm
xs = float(args.xsize)
ys = float(args.ysize)
colors = args.colors.split(',')
jobid = ''
outdir = ''
if rank == 0:
jobid = str(uuid.uuid4())
outdir = usernm + '/' + jobid + '/'
if args.db == 'DR1':
db = 'desdr'
elif args.db == 'Y3A2':
db = 'dessci'
# This puts any input type into a pandas dataframe
if args.csv:
userdf = pd.DataFrame(pd.read_csv(args.csv))
elif args.ra:
coords = {}
coords['RA'] = args.ra
coords['DEC'] = args.dec
userdf = pd.DataFrame.from_dict(coords, orient='columns')
elif args.coadd:
coadds = {}
coadds['COADD_OBJECT_ID']
userdf = pd.DataFrame.from_dict(coadds, orient='columns')
df = pd.DataFrame()
unmatched_coords = {'RA': [], 'DEC': []}
unmatched_ids = []
conn = ea.connect(db)
curs = conn.cursor()
if 'RA' in userdf:
for i in range(len(userdf)):
ra = userdf['RA'][i]
ra180 = ra
if ra > 180:
ra180 = 360 - ra
if args.db == 'DR1':
query = "select * from (select T.TILENAME, M.ALPHAWIN_J2000, M.DELTAWIN_J2000, M.RA, M.DEC from DR1_TILE_INFO T, DR1_MAIN M where M.TILENAME = T.TILENAME and (CROSSRA0='N' and ({0} between RACMIN and RACMAX) and ({1} between DECCMIN and DECCMAX)) or (CROSSRA0='Y' and ({2} between RACMIN-360 and RACMAX) and ({1} between DECCMIN and DECCMAX))) where rownum=1".format(
ra, userdf['DEC'][i], ra180)
#elif args.db.upper == 'Y3A2':
f = conn.query_to_pandas(query)
if f.empty:
unmatched_coords['RA'].append(userdf['RA'][i])
unmatched_coords['DEC'].append(userdf['DEC'][i])
else:
df = df.append(f)
if 'COADD_OBJECT_ID' in userdf:
for i in range(len(userdf)):
if args.db == 'DR1':
query = "select COADD_OBJECT_ID, ALPHAWIN_J2000, DELTAWIN_J2000, RA, DEC, TILENAME from DR1_MAIN where COADD_OBJECT_ID={0}".format(
userdf['COADD_OBJECT_ID'][i])
#elif args.db.upper == 'Y3A2':
f = conn.query_to_pandas(query)
if f.empty:
unmatched_ids.append(userdf['COADD_OBJECT_ID'][i])
else:
df = df.append(f)
conn.close()
df = df.sort_values(by=['TILENAME'])
chunksize = int(df.shape[0] / nprocs) + (df.shape[0] % nprocs)
df = [df[i:i + chunksize] for i in range(0, df.shape[0], chunksize)]
else:
df = None
jobid = jobid
jobid = comm.bcast(jobid, root=0)
outdir = usernm + '/' + jobid + '/'
df = comm.scatter(df, root=0)
tilenm = df['TILENAME'].unique()
for i in tilenm:
tiledir = 'tiles_sample/' + i + '/'
udf = df[df.TILENAME == i]
udf = udf.reset_index()
size = u.Quantity((ys, xs), u.arcmin)
positions = SkyCoord(udf['ALPHAWIN_J2000'],
udf['DELTAWIN_J2000'],
frame='icrs',
unit='deg',
equinox='J2000',
representation_type='spherical')
if args.make_tiffs or args.make_pngs:
# 20180823 - I can't think of a good reason for this to NOT be included within the function above, so, merge?
imgname = glob.glob(tiledir + '*.tiff')
try:
im = Image.open(imgname[0])
except IOError as e:
print('No TIFF file found for tile ' + str(i) +
'. Will not create true-color cutout.')
else:
MakeTiffCut(tiledir, outdir, im, positions, xs, ys, udf,
args.make_tiffs, args.make_pngs)
if args.make_fits:
MakeFitsCut(tiledir, outdir, size, positions, colors, udf)
from astropy.table import Table, join, Column
#directory to store outputs
data_dir = '/Users/Christina/DES/data/'
#format of balrog tables in db
table_format = "JELENA.BALROG_SVA1_TAB{}_{}_{}"
table_nums = [str(r).zfill(2) for r in range(3, 12)]
#09 is not valid
table_nums.remove('09')
bands = ['G', 'R', 'I', 'Z']
tables = ['TRUTH'] #'SIM']#, 'NOSIM']
connection = ea.connect()
for table in tables:
for table_num in table_nums:
if table == 'SIM':
out_file = data_dir + 'balrog_sva1_tab{}_SIM_flux_radii.fits'.format(
table_num)
elif table == 'TRUTH':
out_file = data_dir + 'balrog_sva1_tab{}_TRUTH_fluxes.fits'.format(
table_num)
if os.path.exists(out_file):
print "Output path {} already exists. Delete before running again. Moving to next table.".format(
out_file)
continue
def db_connect(section='desoper'):
db = ea.connect(section=section)
cursor = db.cursor()
return cursor
def run(args):
start = time.time()
usernm = args.usernm
xs = float(args.xsize)
ys = float(args.ysize)
colors = args.colors.split(',')
jobid = str(uuid.uuid4())
outdir = args.usernm + '/' + jobid + '/'
maketiff = args.make_tiff
makepngs = args.make_pngs
makefits = args.make_fits
if args.db == 'DR1':
db = 'desdr'
elif args.db == 'Y3A2':
db = 'dessci'
userdf = pd.DataFrame(pd.read_csv('des0210-1624_100_sample.csv'))
#userdf = pd.DataFrame(pd.read_csv('des0210-1624_100_sample_no_tile_borders.csv'))
userdf.drop(
columns=['ALPHAWIN_J2000', 'DELTAWIN_J2000', 'RA', 'DEC', 'TILENAME'],
inplace=True)
df = pd.DataFrame()
unmatched_coords = {'RA': [], 'DEC': []}
unmatched_ids = []
conn = ea.connect(db)
curs = conn.cursor()
if args.ra:
for i in range(len(userdf)):
ra = userdf['RA'][i]
ra180 = ra
if ra > 180:
ra180 = 360 - ra
if args.db == 'DR1':
query = "select * from (select T.TILENAME, M.ALPAHWIN_J2000, M.DELTAWIN_J2000, M.RA, M.DEC from DR1_TILE_INFO T, DR1_MAIN M where M.TILENAME = T.TILENAME and (CROSSRA0='N' and ({0} between RACMIN and RACMAX) and ({1} between DECCMIN and DECCMAX)) or (CROSSRA0='Y' and ({2} between RACMIN-360 and RACMAX) and ({1} between DECCMIN and DECCMAX))) where rownum=1".format(
ra, userdf['DEC'][i], ra180)
#elif args.db.upper == 'Y3A2':
#
f = conn.query_to_pandas(query)
if f.empty:
unmatched['RA'].append(userdf['RA'][i])
unmatched['DEC'].append(userdf['DEC'][i])
else:
df = df.append(f)
#if args.coadd:
if 'COADD_OBJECT_ID' in userdf:
for i in range(len(userdf)):
if args.db == 'DR1':
query = "select COADD_OBJECT_ID, ALPHAWIN_J2000, DELTAWIN_J2000, RA, DEC, TILENAME from DR1_MAIN where COADD_OBJECT_ID={0}".format(
userdf['COADD_OBJECT_ID'][i])
#elif args.db.upper == 'Y3A2':
#
f = conn.query_to_pandas(query)
if f.empty:
unmatched_ids.append(userdf['COADD_OBJECT_ID'][i])
else:
df = df.append(f)
conn.close()
df = df.merge(userdf, on='COADD_OBJECT_ID', how='inner')
#df = df.sort_values(by=['TILENAME']) # 2018-08-09 - Isn't necessary when slicing dataframe by tilename below.
dt1 = time.time() - start
print(
'It took {} seconds to sort the desired objects by tile.'.format(dt1))
size = u.Quantity((ys, xs), u.arcmin)
#positions = SkyCoord(df['ALPHAWIN_J2000'], df['DELTAWIN_J2000'], frame='icrs', unit='deg', equinox='J2000', representation_type='spherical')
#positions = SkyCoord([32.301142,32.301148], [-16.475058,-16.306904], frame='icrs', unit='deg', equinox='J2000', representation_type='spherical')
#positions = SkyCoord([32.339727, 32.579913], [-16.486495, -16.402778], frame='icrs', unit='deg', equinox='J2000', representation_type='spherical')
tilenm = df['TILENAME'].unique()
for i in tilenm:
tiledir = i + '/'
udf = df[df.TILENAME == i]
positions = SkyCoord(udf['ALPHAWIN_J2000'],
udf['DELTAWIN_J2000'],
frame='icrs',
unit='deg',
equinox='J2000',
representation_type='spherical')
if maketiff or makepngs:
imgname = glob.glob(tiledir + '*.tiff')
try:
im = Image.open(imgname[0])
except IOError as e:
print('No TIFF file found. Will not create true-color cutout.')
else:
MakeTiffCut(tiledir, outdir, im, positions, xs, ys, udf,
maketiff, makepngs)
if args.make_fits:
MakeFitsCut(tiledir, outdir, size, positions, colors, udf)
dt2 = time.time() - dt1
print('It took {} seconds to make the cutouts.'.format(dt2))
end = dt2 - start
print('It took {} seconds for this program to run.'.format(end))
#Produce Truth Table for Fakes#
explist=','.join(map(str,expnums))
# the database where diffimg outputs are stored
db='destest'
schema = 'marcelle'
# the query you want to run to get the truth table data
query='select distinct SNFAKE_ID, EXPNUM, CCDNUM, TRUEMAG, TRUEFLUXCNT, FLUXCNT, BAND, NITE, MJD from '+ schema +'.SNFAKEIMG where EXPNUM IN ('+explist+') order by SNFAKE_ID'
# the file where you want to save the truth table
filename= config.get('GWmakeDataFiles-fake', 'fake_input')
filename=os.path.join(outdir,filename)
connection=easyaccess.connect(db)
connection.query_and_save(query,filename)
connection.close()
### FOR THE FIRST RUN EXIT HERE TO LEARN NAMES OF VALUES WE NEED###
print "Data Made"
print "Read Data"
#Make plots Section#
###Plot1 Efficiency Plot ###
###Plot5 Magerror Distribution ###
###Plots should include all bands###
reals = diffimg.DataSet(os.path.join(outdir,outDir_datareal), label = 'reals')
def run(args):
if rank == 0:
if args.db == 'DR1':
db = 'desdr'
elif args.db == 'Y3A2':
db = 'dessci'
conn = ea.connect(db)
curs = conn.cursor()
usernm = str(conn.user)
if args.jobid:
jobid = args.jobid
else:
jobid = str(uuid.uuid4())
outdir = OUTDIR + usernm + '/' + jobid + '/'
try:
os.makedirs(outdir, exist_ok=False)
except OSError as e:
print(e)
print('Specified jobid already exists in output directory. Aborting job.')
conn.close()
sys.stdout.flush()
comm.Abort()
else:
usernm, jobid, outdir = None, None, None
usernm, jobid, outdir = comm.bcast([usernm, jobid, outdir], root=0)
logtime = datetime.datetime.now().strftime('%Y%m%d-%H%M%S')
#logname = OUTDIR + 'BulkThumbs_' + logtime + '.log'
logname = outdir + 'BulkThumbs_' + logtime + '.log'
formatter = logging.Formatter('%(asctime)s - '+str(rank)+' - %(levelname)-8s - %(message)s')
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)
fh = MPILogHandler(logname, comm)
fh.setLevel(logging.INFO)
fh.setFormatter(formatter)
logger.addHandler(fh)
xs = float(args.xsize)
ys = float(args.ysize)
colors = args.colors.split(',')
#usernm = ''
#jobid = ''
#outdir = ''
if rank == 0:
summary = {}
start = time.time()
#if args.db == 'DR1':
# db = 'desdr'
#elif args.db == 'Y3A2':
# db = 'dessci'
logger.info('Selected Options:')
# This puts any input type into a pandas dataframe
if args.csv:
userdf = pd.DataFrame(pd.read_csv(args.csv))
logger.info(' CSV: '+args.csv)
summary['csv'] = args.csv
elif args.ra:
coords = {}
coords['RA'] = args.ra
coords['DEC'] = args.dec
userdf = pd.DataFrame.from_dict(coords, orient='columns')
logger.info(' RA: '+str(args.ra))
logger.info(' DEC: '+str(args.dec))
summary['ra'] = str(args.ra)
summary['dec'] = str(args.dec)
elif args.coadd:
coadds = {}
coadds['COADD_OBJECT_ID'] = args.coadd
userdf = pd.DataFrame.from_dict(coadds, orient='columns')
logger.info(' CoaddID: '+str(args.coadd))
summary['coadd'] = str(args.coadd)
logger.info(' X size: '+str(args.xsize))
logger.info(' Y size: '+str(args.ysize))
logger.info(' Make TIFFs? '+str(args.make_tiffs))
logger.info(' Make PNGs? '+str(args.make_pngs))
logger.info(' Make FITS? '+str(args.make_fits))
logger.info(' Make RGBs? {}'.format('True' if args.make_rgb else 'False'))
summary['xsize'] = str(args.xsize)
summary['ysize'] = str(args.ysize)
summary['make_tiffs'] = str(args.make_tiffs)
summary['make_pngs'] = str(args.make_pngs)
summary['make_fits'] = str(args.make_fits)
summary['make_rgb'] = 'True' if args.make_rgb else 'False'
if args.make_fits:
logger.info(' Bands: '+args.colors)
summary['bands'] = args.colors
if args.make_rgb:
logger.info(' Bands: '+str(args.make_rgb))
summary['rgb_colors'] = args.make_rgb
summary['db'] = args.db
df = pd.DataFrame()
unmatched_coords = {'RA':[], 'DEC':[]}
unmatched_coadds = []
logger.info('User: '******'JobID: ' + str(jobid))
summary['user'] = usernm
summary['jobid'] = str(jobid)
tablename = 'BTL_'+jobid.upper().replace("-","_") # "BulkThumbs_List_<jobid>"
if 'RA' in userdf:
if args.db == 'Y3A2':
ra_adjust = [360-userdf['RA'][i] if userdf['RA'][i]>180 else userdf['RA'][i] for i in range(len(userdf['RA']))]
userdf = userdf.assign(RA_ADJUSTED = ra_adjust)
userdf.to_csv(OUTDIR+tablename+'.csv', index=False)
conn.load_table(OUTDIR+tablename+'.csv', name=tablename)
#query = "select temp.RA, temp.DEC, temp.RA_ADJUSTED, temp.RA as ALPHAWIN_J2000, temp.DEC as DELTAWIN_J2000, m.TILENAME from {} temp left outer join Y3A2_COADDTILE_GEOM m on (m.CROSSRA0='N' and (temp.RA between m.URAMIN and m.URAMAX) and (temp.DEC between m.UDECMIN and m.UDECMAX)) or (m.CROSSRA0='Y' and (temp.RA_ADJUSTED between m.URAMIN-360 and m.URAMAX) and (temp.DEC between m.UDECMIN and m.UDECMAX))".format(tablename)
query = "select temp.RA, temp.DEC, temp.RA_ADJUSTED, temp.RA as ALPHAWIN_J2000, temp.DEC as DELTAWIN_J2000, m.TILENAME"
if 'XSIZE' in userdf:
query += ", temp.XSIZE"
if 'YSIZE' in userdf:
query += ", temp.YSIZE"
query += " from {} temp left outer join Y3A2_COADDTILE_GEOM m on (m.CROSSRA0='N' and (temp.RA between m.URAMIN and m.URAMAX) and (temp.DEC between m.UDECMIN and m.UDECMAX)) or (m.CROSSRA0='Y' and (temp.RA_ADJUSTED between m.URAMIN-360 and m.URAMAX) and (temp.DEC between m.UDECMIN and m.UDECMAX))".format(tablename)
df = conn.query_to_pandas(query)
curs.execute('drop table {}'.format(tablename))
os.remove(OUTDIR+tablename+'.csv')
df = df.replace('-9999',np.nan)
df = df.replace(-9999.000000,np.nan)
dftemp = df[df.isnull().any(axis=1)]
unmatched_coords['RA'] = dftemp['RA'].tolist()
unmatched_coords['DEC'] = dftemp['DEC'].tolist()
df = df.dropna(axis=0, how='any')
if args.db == 'DR1':
for i in range(len(userdf)):
ra = userdf['RA'][i]
ra180 = ra
if ra > 180:
ra180 = 360 - ra
query = "select * from (select TILENAME from DR1_TILE_INFO where (CROSSRA0='N' and ({0} between RACMIN and RACMAX) and ({1} between DECCMIN and DECCMAX)) or (CROSSRA0='Y' and ({2} between RACMIN-360 and RACMAX) and ({1} between DECCMIN and DECCMAX))) where rownum=1".format(ra, userdf['DEC'][i], ra180)
f = conn.query_to_pandas(query)
if f.empty:
unmatched_coords['RA'].append(userdf['RA'][i])
unmatched_coords['DEC'].append(userdf['DEC'][i])
else:
df = df.append(f)
logger.info('Unmatched coordinates: \n{0}\n{1}'.format(unmatched_coords['RA'], unmatched_coords['DEC']))
summary['Unmatched_Coords'] = unmatched_coords
print(unmatched_coords)
if 'COADD_OBJECT_ID' in userdf:
if args.db == 'Y3A2':
userdf.to_csv(OUTDIR+tablename+'.csv', index=False)
conn.load_table(OUTDIR+tablename+'.csv', name=tablename)
#query = "select temp.COADD_OBJECT_ID, m.ALPHAWIN_J2000, m.DELTAWIN_J2000, m.RA, m.DEC, m.TILENAME from {} temp left outer join Y3A2_COADD_OBJECT_SUMMARY m on temp.COADD_OBJECT_ID=m.COADD_OBJECT_ID".format(tablename)
query = "select temp.COADD_OBJECT_ID, m.ALPHAWIN_J2000, m.DELTAWIN_J2000, m.RA, m.DEC, m.TILENAME"
if 'XSIZE' in userdf:
query += ", temp.XSIZE"
if 'YSIZE' in userdf:
query += ", temp.YSIZE"
query += " from {} temp left outer join Y3A2_COADD_OBJECT_SUMMARY m on temp.COADD_OBJECT_ID=m.COADD_OBJECT_ID".format(tablename)
df = conn.query_to_pandas(query)
curs.execute('drop table {}'.format(tablename))
os.remove(OUTDIR+tablename+'.csv')
df = df.replace('-9999',np.nan)
df = df.replace(-9999.000000,np.nan)
dftemp = df[df.isnull().any(axis=1)]
unmatched_coadds = dftemp['COADD_OBJECT_ID'].tolist()
df = df.dropna(axis=0, how='any')
if args.db == 'DR1':
for i in range(len(userdf)):
query = "select COADD_OBJECT_ID, ALPHAWIN_J2000, DELTAWIN_J2000, RA, DEC, TILENAME from DR1_MAIN where COADD_OBJECT_ID={0}".format(userdf['COADD_OBJECT_ID'][i])
f = conn.query_to_pandas(query)
if f.empty:
unmatched_coadds.append(userdf['COADD_OBJECT_ID'][i])
else:
df = df.append(f)
logger.info('Unmatched coadd ID\'s: \n{}'.format(unmatched_coadds))
summary['Unmatched_Coadds'] = unmatched_coadds
print(unmatched_coadds)
conn.close()
df = df.sort_values(by=['TILENAME'])
df = df.drop_duplicates(['RA','DEC'], keep='first')
if args.return_list:
os.makedirs(outdir, exist_ok=True)
df.to_csv(outdir+tablename+'.csv', index=False)
df = np.array_split(df, nprocs)
end1 = time.time()
query_elapsed = '{0:.2f}'.format(end1-start)
print('Querying took (s): ' + query_elapsed)
logger.info('Querying took (s): ' + query_elapsed)
summary['query_time'] = query_elapsed
else:
df = None
df = comm.scatter(df, root=0)
tilenm = df['TILENAME'].unique()
for i in tilenm:
tiledir = TILES_FOLDER + i + '/'
udf = df[ df.TILENAME == i ]
udf = udf.reset_index()
size = u.Quantity((ys, xs), u.arcmin)
positions = SkyCoord(udf['ALPHAWIN_J2000'], udf['DELTAWIN_J2000'], frame='icrs', unit='deg', equinox='J2000', representation_type='spherical')
if args.make_tiffs or args.make_pngs:
MakeTiffCut(tiledir, outdir+i+'/', positions, xs, ys, udf, args.make_tiffs, args.make_pngs)
if args.make_fits:
MakeFitsCut(tiledir, outdir+i+'/', size, positions, colors, udf)
if args.make_rgb:
MakeLuptonRGB(tiledir, outdir+i+'/', udf, positions, xs, ys, args.make_rgb, args.rgb_minimum, args.rgb_stretch, args.rgb_asinh)
comm.Barrier()
if rank == 0:
end2 = time.time()
processing_time = '{0:.2f}'.format(end2-end1)
print('Processing took (s): ' + processing_time)
logger.info('Processing took (s): ' + processing_time)
summary['processing_time'] = processing_time
dirsize = getPathSize(outdir)
dirsize = dirsize * 1. / 1024
if dirsize > 1024. * 1024:
dirsize = '{0:.2f} GB'.format(1. * dirsize / 1024. / 1024)
elif dirsize > 1024.:
dirsize = '{0:.2f} MB'.format(1. * dirsize / 1024.)
else:
dirsize = '{0:.2f} KB'.format(dirsize)
logger.info('All processes finished.')
logger.info('Total file size on disk: {}'.format(dirsize))
summary['size_on_disk'] = str(dirsize)
files = glob.glob(outdir + '*/*')
logger.info('Total number of files: {}'.format(len(files)))
summary['number_of_files'] = len(files)
files = [i.split('/')[-2:] for i in files]
files = [('/').join(i) for i in files]
if 'COADD_OBJECT_ID' in userdf:
files = [i.split('.')[-2] for i in files]
else:
files = [('.').join(i.split('.')[-4:-1]) for i in files]
files = [i.split('_')[0] for i in files]
files = list(set(files))
summary['files'] = files
jsonfile = outdir + 'BulkThumbs_'+logtime+'_SUMMARY.json'
with open(jsonfile, 'w') as fp:
json.dump(summary, fp)
datefmt='%Y-%m-%d %H:%M:%S', stream=sys.stdout)
import easyaccess as ea
if __name__ == "__main__":
import argparse
description = "python script"
parser = argparse.ArgumentParser(description=description)
parser.add_argument('infiles',nargs='+')
parser.add_argument('-t','--table',default='MyTable')
parser.add_argument('-f','--force',action='store_true')
parser.add_argument('-s','--section',default='desoper')
args = parser.parse_args()
#ea.set_color_term(False)
con = ea.connect(section=args.section,quiet=True)
con.ct = False
cur = con.cursor()
msg = "easyaccess v%s"%ea.__version__
logging.info(msg)
exists = con.check_table_exists(args.table)
if exists and not args.force:
msg = "Found table %s; skipping..."%args.table
raise Exception(msg)
elif exists and args.force:
con.drop_table(args.table,purge=True)
for i,infile in enumerate(args.infiles):
msg = '\n' + 30*"-" + '\n'
def find_observations(search_exps, cand_df, orbit, year='Y4', nsigma=3):
'''
Finds SE transients in the search exposures from the given year that fall inside the error ellipse of the candidate.
:param search_exps: dataframe of exposures to be searched
:param cand_df: dataframe of observations of the candidate
:param orbit: the candidate's corresponding orbit
:param year: DES observing year of the search exposures, e.g. 'Y4'
:param nsigma: multiplier for error ellipse dimensions
:return: dataframe of transients
'''
conn_desoper = ea.connect(section='desoper')
conn_dessci = ea.connect(section='dessci')
matched_obs = pd.DataFrame()
transients_inside = pd.DataFrame()
# search_exps = search_exps[search_exps['expnum']!=374572] # to exclude particular exposures
df = pd.DataFrame(columns=['expnum', 'ra_pred', 'dec_pred', 'err_a', 'err_b', 'err_PA'])
full_df_coadd = pd.DataFrame()
for ind, exp in search_exps.iterrows():
print 'Searching exposure... ', exp['expnum'], ephem.date(exp['date']), exp['band'], exp['t_eff'],'...',
pos_pred = orbit.predict_pos(exp['date'])
ra_pred, dec_pred, err_a, err_b, err_PA = pos_pred['ra'], pos_pred['dec'], pos_pred['err']['a'],\
pos_pred['err']['b'], pos_pred['err']['PA']
print 'RA_PRED, DEC_PRED, ERR_A, ERR_B, PA = ', ra_pred, dec_pred, err_a, err_b, err_PA
if err_a<15.0: err_a=15.0
if err_b<15.0: err_b=15.0
coadd_obs = get_coadd_cutout(conn_dessci, ra_pred, dec_pred, nsigma * err_a)
full_df_coadd = full_df_coadd.append(coadd_obs)
df.loc[ind] = [exp['expnum'], ra_pred, dec_pred, nsigma*err_a, nsigma*err_b, err_PA]
if year=='Y4':
se_obs = get_SE_detections(conn_desoper, df)
else:
se_obs = get_SE_detections(conn_dessci, df)
#coadd_obs = get_coadd_cutout(conn_dessci, df)
transients = get_transient_detections(se_obs, full_df_coadd)
if len(transients):
transients['delta_chisq'] = transients.apply(lambda row: getOrbit(cand_df, row).chisq-orbit.chisq,axis=1)
# transients['mag'] = good_matches.apply(lambda row: mag_calib(row), axis=1)
transients['mag'] = 99. # temp workaround
transients['ml_score']=10.0 # placeholder
transients['fakeid']=0
transients.rename(columns={'object_number': 'objid'}, inplace=True)
# These transients fall inside the error ellipse
transients_inside = transients.loc[transients.apply(lambda row:
point_in_ellipse(row['ra'],
row['dec'],
np.degrees(df.loc[df['expnum'] == row['expnum'], 'ra_pred'].iloc[0]),
np.degrees(df.loc[df['expnum'] == row['expnum'], 'dec_pred'].iloc[0]),
df.loc[df['expnum'] == row['expnum'], 'err_a'].iloc[0],
df.loc[df['expnum'] == row['expnum'], 'err_b'].iloc[0],
90 - df.loc[df['expnum'] == row['expnum'], 'err_PA'].iloc[0]),
axis=1)]
#transients_inside = transients.loc[transients.apply(lambda row: point_in_ellipse(row['ra'], row['dec'], df), axis=1)]
# for ind2, row2 in transients_inside.iterrows():
# print ephem.date(exp['date']), str(ephem.hours(np.radians(row2['ra']))), str(ephem.degrees(np.radians(row2['dec']))), exp['expnum'],exp['band']
matched_obs = pd.concat([matched_obs, transients_inside])
return matched_obs
def query_function(params):
con = ea.connect('desdr')
DF = con.query_to_pandas(query_string(params))
DF = QTable.from_pandas(DF)
return DF
import os
import sys
import pandas as pd
import easyaccess as ea
from tqdm import tqdm
deep = pd.read_hdf(sys.argv[1], key='main')
new_sncand = pd.read_csv(
'/media/data3/wiseman/des/coadding/catalogs/all_snids.csv')
#new_snids = new_sncand[new_sncand['SNFAKE_ID']==0]['SNID'].values
conn = ea.connect('desoper')
cursor = conn.cursor()
try:
start = sys.argv[2]
except:
start = 0
for i in tqdm(range(int(start), len(deep))): #len(deep)
query = (
"INSERT INTO SNGALS_DEEP "
"( A_IMAGE, B_IMAGE, CCDNUM, CLASS_STAR_G, CLASS_STAR_I,"
"CLASS_STAR_R, CLASS_STAR_Z, CXX_IMAGE, CXY_IMAGE, CYY_IMAGE,"
"DLR, DLR_RANK, ELONGATION, FIELD, FLUXERR_APER_4_G,"
"FLUXERR_APER_4_I, FLUXERR_APER_4_R, FLUXERR_APER_4_Z,"
"FLUXERR_AUTO_G, FLUXERR_AUTO_I, FLUXERR_AUTO_R, FLUXERR_AUTO_Z,"
"FLUX_APER_4_G, FLUX_APER_4_I, FLUX_APER_4_R, FLUX_APER_4_Z,"
"FLUX_AUTO_G, FLUX_AUTO_I, FLUX_AUTO_R, FLUX_AUTO_Z,"
"KRON_RADIUS, LIMFLUX_G, LIMFLUX_I, LIMFLUX_R, LIMFLUX_Z,"
"LIMMAG_G, LIMMAG_I, LIMMAG_R, LIMMAG_Z, MAGERR_APER_4_G,"
"MAGERR_APER_4_I, MAGERR_APER_4_R, MAGERR_APER_4_Z,"
parser.add_argument('--user',
dest='user')
parser.add_argument('--password',
dest='password')
options = parser.parse_args()
user = options.user
password = options.password
out_directory = options.out
csv_path = '{0}/httppath_exposure.csv'.format(out_directory)
if options.job == 'paths':
print('Downloading CSV of exposures.')
query = "select path, obstype, band, exposurename, id, expnum, nite, mjd_obs from httppath_exposure where obstype in ('dome flat')"
try:
import easyaccess
connection = easyaccess.connect()
cursor = connection.cursor()
#csv = connection.query_to_pandas(query)
connection.query_and_save(query, csv_path)
connection.close()
except:
# huh. Well let's try commandline
command = ['easyaccess', '-c',
"{0}; > {1}".format(query, csv_path)]
call(command)
elif options.job == 'images':
print('Using CSV at {0}'.format(csv_path))
csv = pd.read_csv(csv_path)
# cut out duplicate filenames
class TestInterpreter(unittest.TestCase):
conf = ea.config_mod.get_config(ea.config_file)
conf.set('display', 'loading_bar', 'no')
db = conf.get('easyaccess', 'database')
desconf = ea.config_mod.get_desconfig(ea.desfile, db)
con = ea.easy_or(conf,
desconf,
db,
interactive=False,
quiet=True,
refresh=False)
con2 = ea.connect(quiet=True)
tablename = 'testtable'
nrows = 10000
prefetch = 4000
chunk = 1000
memsize = 1
sqlfile = 'temp.sql'
csvfile = 'temp.csv'
fitsfile = 'temp.fits'
h5file = 'temp.h5'
def test_describe(self):
print('\n*** test_describe ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
command = 'describe_table %s;' % self.tablename.upper()
self.con.onecmd(command)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_add_comment(self):
print('\n*** test_add_comment ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "add_comment table %s 'Test table'" % self.tablename.upper()
self.con.onecmd(command)
command = "add_comment column %s.RA 'Coordinate'" % self.tablename.upper(
)
self.con.onecmd(command)
command = 'describe_table %s;' % self.tablename.upper()
self.con.onecmd(command)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_select(self):
print('\n*** test_select ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "select RA,DEC from %s ;" % self.tablename.upper()
self.con.onecmd(command)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_select_csv(self):
print('\n*** test_select_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.csvfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.csvfile))
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_select_fits(self):
print('\n*** test_select_fits ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
os.remove(self.csvfile)
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.fitsfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.fitsfile))
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_select_hdf5(self):
print('\n*** test_select_hdf5 ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
os.remove(self.csvfile)
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.h5file)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.h5file))
os.remove(self.h5file)
self.con.drop_table(self.tablename)
def test_select_by_chunks(self):
print('\n*** test_select_by_chunks ***\n')
global load_bar
load_bar = False
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
for i in range(34):
command = "append_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 35)
command = "prefetch set 30000"
self.con.onecmd(command)
self.con.outfile_max_mb = 1
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.csvfile)
self.con.onecmd(command)
for i in range(6):
self.assertTrue(
os.path.exists(
os.path.splitext(self.csvfile)[0] + '_00000' + str(i + 1) +
'.csv'))
os.remove(
os.path.splitext(self.csvfile)[0] + '_00000' + str(i + 1) +
'.csv')
self.con.outfile_max_mb = 1000
self.con.drop_table(self.tablename)
if os.path.exists(self.csvfile):
os.remove(self.csvfile)
def test_load_append_table_csv(self):
print('\n*** test_load_append_table_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
# name from filename
command = "load_table %s " % self.csvfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s " % self.csvfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
os.remove(self.csvfile)
def test_load_append_table_name_csv(self):
print('\n*** test_load_append_table_name_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
# name from tablename
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_load_append_table_chunk_csv(self):
print('\n*** test_load_append_table_chunk_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
# chunksize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --chunksize %s" % (
self.csvfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s --chunksize %s" % (
self.csvfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
def test_load_append_table_memory_csv(self):
print('\n*** test_load_append_table_memory_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile,
index=False,
float_format='%.8f',
sep=',',
mode='a',
header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s" % (
self.csvfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
command = "append_table %s --tablename %s --memsize %s" % (
self.csvfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
# end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_csv(self):
print('\n*** test_load_append_table_memory_chunk_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile,
index=False,
float_format='%.8f',
sep=',',
mode='a',
header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s --chunksize %s" % (
self.csvfile, self.tablename, self.memsize, self.chunk * 10)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
command = "append_table %s --tablename %s --memsize %s --chunksize %s" % (
self.csvfile, self.tablename, self.memsize, self.chunk * 200)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
# end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_append_table_fits(self):
print('\n*** test_load_append_table_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
# name from filename
command = "load_table %s " % self.fitsfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s " % self.fitsfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
os.remove(self.fitsfile)
def test_load_append_table_name_fits(self):
print('\n*** test_load_append_table_name_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# name from tablename
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.fitsfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s" % (self.fitsfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_chunk_fits(self):
print('\n*** test_load_append_table_chunk_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# chunksize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --chunksize %s" % (
self.fitsfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s --chunksize %s" % (
self.fitsfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_fits(self):
print('\n*** test_load_append_table_memory_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s" % (
self.fitsfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
command = "append_table %s --tablename %s --memsize %s" % (
self.fitsfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
# end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_fits(self):
print('\n*** test_load_append_table_memory_chunk_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s --chunksize %s" % (
self.fitsfile, self.tablename, self.memsize, self.chunk * 10)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
command = "append_table %s --tablename %s --memsize %s --chunksize %s" % (
self.fitsfile, self.tablename, self.memsize, self.chunk * 200)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
# end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_loadsql(self):
print('\n*** test_loadsql ***\n')
data = create_test_data()
df = pd.DataFrame(data)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
os.remove(self.csvfile)
query = """
-- This is a comment
select RA, DEC from %s -- this is another comment
; > %s
""" % (self.tablename, self.csvfile)
with open(self.sqlfile, 'w') as F:
F.write(query)
command = "loadsql %s" % (self.sqlfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.csvfile))
df = pd.read_csv(self.csvfile, sep=',')
self.assertEqual(len(df), self.nrows)
os.remove(self.csvfile)
self.assertFalse(os.path.exists(self.csvfile))
os.remove(self.sqlfile)
@unittest.skip("Need to re evaluate")
def test_inline(self):
print('\n*** test_inline ***\n')
data = create_test_data()
df = pd.DataFrame(data)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "import wrapped as Y"
self.con.onecmd(command)
command = "select /*p: Y.my_sum(ra,dec) as testcol */, dec from %s ; > %s" % (
self.tablename, self.csvfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.csvfile))
df = pd.read_csv(self.csvfile, sep=',')
self.assertEqual(len(df), self.nrows)
self.assertTrue('TESTCOL' in df.columns.values.tolist())
os.remove(self.csvfile)
self.assertFalse(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
def rawdata_exp_query(args):
import easyaccess as ea
import pandas as pd
import numpy as np
if args.verbose>0:
print
print '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *'
print 'rawdata_exp_query'
print '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *'
print
# Updated query to get rid of any commas in the field, object, or program name
# (which could play havoc downstream)...
query="""
select e.expnum,
e.radeg as EXPRA, e.decdeg as EXPDEC, e.exptime, e.airmass,
e.band, e.nite, e.mjd_obs,
REPLACE(e.field, ',', ' ') as field,
REPLACE(e.object, ',', ' ') as object,
REPLACE(e.program, ',', ' ') as program,
t.pfw_attempt_id, t.tag,
qa.source, qa.t_eff, qa.psf_fwhm, qa.f_eff,
qa.skybrightness, qa.b_eff, qa.cloud_apass, qa.cloud_nomad,
qa.n_apass, qa.n_nomad, qa.c_eff, qa.skytilt, qa.astrom_sigma,
qa.astrom_offset, qa.astrom_ndets, qa.astrom_chi2, qa.nobjects,
qa.flag, qa.calnac, qa.cloud_des, qa.n_des
from prod.exposure e, prod.proctag t, prod.qa_summary qa
where t.pfw_attempt_id=qa.pfw_attempt_id and
e.expnum=qa.expnum and
t.tag='%s' and
qa.expnum between %d and %d
order by qa.expnum
""" % (args.tag, args.expnumMin, args.expnumMax)
#select e.expnum,
# e.radeg as EXPRA, e.decdeg as EXPDEC, e.exptime, e.airmass,
# e.band, e.nite, e.mjd_obs, e.field, e.object, e.program,
# q.reqnum,q.unitname,q.attnum,
# qa.t_eff,qa.c_eff,qa.b_eff,qa.skybrightness,qa.fwhm_asec
#from exposure e,finalcut_eval qa,proctag t,pfw_attempt q
#where t.pfw_attempt_id=qa.pfw_attempt_id and
# e.expnum=qa.expnum and
# q.id=t.pfw_attempt_id and
# tag like 'Y3A2_MISC'
#order by e.expnum;
#query="""
# select e.expnum,
# e.radeg as EXPRA, e.decdeg as EXPDEC, e.exptime, e.airmass,
# e.band, e.nite, e.mjd_obs, e.field, e.object, e.program,
# t.pfw_attempt_id, t.tag,
# f.t_eff, f.f_eff, f.b_eff, f.c_eff,
# f.fwhm_asec, f.ellipticity, f.skybrightness,
# f.cloud_apass, f.cloud_nomad, f.cloud_catalog,
# f.n_apass, f.n_nomad,
# f.cloud_des, f.n_des
# from prod.exposure e, prod.proctag t, prod.finalcut_eval f
# where t.pfw_attempt_id=f.pfw_attempt_id and
# e.expnum=f.expnum and
# t.tag='%s' and
# e.expnum between %d and %d
# order by e.expnum
# """ % (args.tag, args.expnumMin, args.expnumMax)
if args.verbose>0: print query
outputFile=args.outputFile
# Open connection to db...
connection=ea.connect('desoper')
# Make query and save to output file...
connection.query_and_save(query,outputFile)
# Close connection to db...
connection.close()
if args.verbose>0: print
return 0
def run(args):
xs = float(args.xsize)
ys = float(args.ysize)
colors = args.colors.split(',')
usernm = ''
jobid = ''
outdir = ''
if rank == 0:
start = time.time()
if args.db == 'DR1':
db = 'desdr'
elif args.db == 'Y3A2':
db = 'dessci'
# This puts any input type into a pandas dataframe
if args.csv:
userdf = pd.DataFrame(pd.read_csv(args.csv))
elif args.ra:
coords = {}
coords['RA'] = args.ra
coords['DEC'] = args.dec
userdf = pd.DataFrame.from_dict(coords, orient='columns')
elif args.coadd:
coadds = {}
coadds['COADD_OBJECT_ID'] = args.coadd
userdf = pd.DataFrame.from_dict(coadds, orient='columns')
df = pd.DataFrame()
unmatched_coords = {'RA':[], 'DEC':[]}
unmatched_coadds = []
conn = ea.connect(db)
curs = conn.cursor()
usernm = str(conn.user)
jobid = str(uuid.uuid4())
outdir = OUTDIR + '/' + usernm + '/' + jobid + '/'
tablename = 'BTL_'+jobid.upper().replace("-","_") # "BulkThumbs_List_<jobid>"
if 'RA' in userdf:
print(userdf.head())
if args.db == 'Y3A2':
ra_adjust = [360-userdf['RA'][i] if userdf['RA'][i]>180 else userdf['RA'][i] for i in range(len(userdf['RA']))]
userdf = userdf.assign(RA_ADJUSTED = ra_adjust)
userdf.to_csv(tablename+'.csv', index=False)
conn.load_table(tablename+'.csv', name=tablename)
query = "select temp.RA, temp.DEC, temp.RA_ADJUSTED, temp.RA as ALPHAWIN_J2000, temp.DEC as DELTAWIN_J2000, m.TILENAME from Y3A2_COADDTILE_GEOM m, {} temp where (m.CROSSRA0='N' and (temp.RA between m.RACMIN and m.RACMAX) and (temp.DEC between m.DECCMIN and m.DECCMAX)) or (m.CROSSRA0='Y' and (temp.RA_ADJUSTED between m.RACMIN-360 and m.RACMAX) and (temp.DEC between m.DECCMIN and m.DECCMAX))".format(tablename)
df = conn.query_to_pandas(query)
curs.execute('drop table {}'.format(tablename))
os.remove(tablename+'.csv')
if args.db == 'DR1':
for i in range(len(userdf)):
ra = userdf['RA'][i]
ra180 = ra
if ra > 180:
ra180 = 360 - ra
if args.db == 'DR1':
query = "select * from (select TILENAME from DR1_TILE_INFO where (CROSSRA0='N' and ({0} between RACMIN and RACMAX) and ({1} between DECCMIN and DECCMAX)) or (CROSSRA0='Y' and ({2} between RACMIN-360 and RACMAX) and ({1} between DECCMIN and DECCMAX))) where rownum=1".format(ra, userdf['DEC'][i], ra180)
f = conn.query_to_pandas(query)
if f.empty:
unmatched_coords['RA'].append(userdf['RA'][i])
unmatched_coords['DEC'].append(userdf['DEC'][i])
else:
df = df.append(f)
if 'COADD_OBJECT_ID' in userdf:
if args.db == 'Y3A2':
conn.load_table(args.csv, name=tablename)
query = "select temp.COADD_OBJECT_ID, m.ALPHAWIN_J2000, m.DELTAWIN_J2000, m.RA, m.DEC, m.TILENAME from Y3A2_COADD_OBJECT_SUMMARY m, {} temp where temp.COADD_OBJECT_ID=m.COADD_OBJECT_ID".format(tablename)
df = conn.query_to_pandas(query)
curs.execute('drop table {}'.format(tablename))
if args.db == 'DR1':
for i in range(len(userdf)):
query = "select COADD_OBJECT_ID, ALPHAWIN_J2000, DELTAWIN_J2000, RA, DEC, TILENAME from DR1_MAIN where COADD_OBJECT_ID={0}".format(userdf['COADD_OBJECT_ID'][i])
f = conn.query_to_pandas(query)
if f.empty:
unmatched_coadds.append(userdf['COADD_OBJECT_ID'][i])
else:
df = df.append(f)
conn.close()
print('finished query')
print(len(df))
print(df.head())
df = df.sort_values(by=['TILENAME'])
chunksize = int(df.shape[0] / nprocs) + (df.shape[0] % nprocs)
df = [ df[ i:i+chunksize ] for i in range(0, df.shape[0], chunksize) ]
end = time.time()
print('Querying took (s): ' + str(end-start))
print(unmatched_coords)
print(unmatched_coadds)
else:
df = None
usernm, jobid, outdir = comm.bcast([usernm, jobid, outdir], root=0)
#outdir = usernm + '/' + jobid + '/'
df = comm.scatter(df, root=0)
tilenm = df['TILENAME'].unique()
for i in tilenm:
tiledir = TILES_FOLDER + '/' + i + '/'
udf = df[ df.TILENAME == i ]
udf = udf.reset_index()
size = u.Quantity((ys, xs), u.arcmin)
positions = SkyCoord(udf['ALPHAWIN_J2000'], udf['DELTAWIN_J2000'], frame='icrs', unit='deg', equinox='J2000', representation_type='spherical')
if args.make_tiffs or args.make_pngs:
MakeTiffCut(tiledir, outdir, positions, xs, ys, udf, args.make_tiffs, args.make_pngs)
"""
# 20180823 - I can't think of a good reason for this to NOT be included within the function above, so, merge?
imgname = glob.glob(tiledir + '*.tiff')
try:
im = Image.open(imgname[0])
except IOError as e: # Might need to change to indexerror - "IndexError: list index out of range"
print('No TIFF file found for tile ' + str(i) + '. Will not create true-color cutout.')
else:
MakeTiffCut(tiledir, outdir, im, positions, xs, ys, udf, args.make_tiffs, args.make_pngs)
"""
if args.make_fits:
MakeFitsCut(tiledir, outdir, size, positions, colors, udf)
query8 = 'select * from (select COADD_OBJECT_ID from DR1_MAIN where TILENAME=\'DES0308-1958\') where rownum<{};'.format(r)
r = random.randint(1, m)
total += r
query9 = 'select * from (select COADD_OBJECT_ID from DR1_MAIN where TILENAME=\'DES0311-3749\') where rownum<{};'.format(r)
r = random.randint(1, m)
total += r
query10 = 'select * from (select COADD_OBJECT_ID from DR1_MAIN where TILENAME=\'DES0435-2623\') where rownum<{};'.format(r)
r = random.randint(1, m)
total += r
query11 = 'select * from (select COADD_OBJECT_ID from DR1_MAIN where TILENAME=\'DES2147-5540\') where rownum<{};'.format(r)
r = random.randint(1, m)
total += r
query12 = 'select * from (select COADD_OBJECT_ID from DR1_MAIN where TILENAME=\'DES2246-4914\') where rownum<{};'.format(r)
"""
conn = ea.connect('desdr')
curs = conn.cursor()
df1 = conn.query_to_pandas(query1)
df2 = conn.query_to_pandas(query2)
df3 = conn.query_to_pandas(query3)
df4 = conn.query_to_pandas(query4)
df5 = conn.query_to_pandas(query5)
df6 = conn.query_to_pandas(query6)
df7 = conn.query_to_pandas(query7)
df8 = conn.query_to_pandas(query8)
df9 = conn.query_to_pandas(query9)
df10 = conn.query_to_pandas(query10)
df11 = conn.query_to_pandas(query11)
df12 = conn.query_to_pandas(query12)
def rawdata_se_object_firstcut_query(args):
import easyaccess as ea
import numpy as np
if args.verbose > 0:
print
print '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *'
print 'rawdata_se_object_firstcut_query'
print '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *'
print
connection = ea.connect('desoper')
# Based on query from Robert Gruendl from early Y3A1 tests,
# with some more modern (Y4A1 vintage) suggestions from
# Eli Rykoff...
#
# (Originally, included "b.accepted='True'" in WHERE
# statement for z, but realized that this omitted
# lots of standard star field stars. Now, this
# WHERE clause is included as part of a
# "(z.accepted='True' or e.program='photom-std-field')"
# WHERE clause for the exposure query.
# Thus, be careful where you want to include standard
# star fields or not downstream...)
query="""
with x as (
select /*+materialize */ pfw_attempt_id
from prod.proctag
where tag='%s'
),
y as (
select /*+materialize */ a.expnum as expnum,
max(a.lastchanged_time) as evaltime
from prod.firstcut_eval a
where a.analyst!='SNQUALITY'
group by a.expnum
),
z as (
select /*+materialize */ b.expnum
from prod.firstcut_eval b, y
where b.expnum=y.expnum and
b.lastchanged_time=y.evaltime and
b.expnum between %d and %d
)
select cast(c.expnum as NUMBER(8)) as EXPNUM,
c.filename, c.band,
cast(c.ccdnum as NUMBER(4)) as CCDNUM,
o.object_number, o.x_image, o.y_image, o.ra, o.dec,
o.flux_psf, o.fluxerr_psf, o.flux_aper_8, fluxerr_aper_8,
o.class_star, o.spread_model, o.spreaderr_model, o.flags
from prod.se_object o, prod.catalog c, x, z
where x.pfw_attempt_id=c.pfw_attempt_id and
c.filetype='cat_firstcut' and
c.expnum=z.expnum and
o.filename=c.filename and
(o.x_image between 100 and 1900) and
(o.y_image between 100 and 3950) and
o.flags = 0 and o.imaflags_iso = 0 and
(o.spread_model between -0.01 and 0.01) and o.class_star > 0.80 and
((1.086*o.fluxerr_psf/o.flux_psf) between 0.0001 and 0.1000)
""" % \
(args.tag, args.expnumMin, args.expnumMax)
if args.verbose > 0: print query
outputFiles = args.outputFiles
connection.query_and_save(query, outputFiles)
connection.close()
if args.verbose > 0: print
return 0
def main():
desoper = ea.connect(section='desoper')
dessci = ea.connect(section='dessci')
#y4_exps = pd.read_csv('/Volumes/lsa-gerdes/wsdiff_catalogs/season240/nofakes/wsdiff_season240_Y4_griz_nofakes.csv')
#y4_exps.to_pickle('/Users/lynuszullo/pyOrbfit/Y4_Transient_Search/y4_pickle')
#s240_df = pd.read_pickle('/Users/lynuszullo/pyOrbfit/Y4_Transient_Search/y4_pickle')
#s240_expnums = s240_df['expnum'].drop_duplicates()
ra_deg = 315
dec_deg = -46
catalogue = 0
diffimg = 0
astcount = 0
diff_asteroids = 0
cat_asteroids = 0
catdiffast = 0
#for exp in s240_expnums:
ra = ephem.degrees(ra_deg * ephem.pi / 180)
dec = ephem.degrees(dec_deg * ephem.pi / 180)
box = 10000. # arcsec
season = 240
expnum = 572126
exposure_info = desoper.query_to_pandas('SELECT RADEG, DECDEG, DATE_OBS FROM PROD.EXPOSURE WHERE ' +
'EXPNUM = ' + str(expnum))
ra_exp, dec_exp = np.radians(exposure_info.RADEG[0]), np.radians(exposure_info.DECDEG[0])
ra_exp_deg, dec_exp_deg = np.degrees(ra), np.degrees(dec)
date = exposure_info.DATE_OBS[0] # type: str
date = date.replace('-', '/')
date = date.replace('T', ' ')
date = date.split('.')[0]
c = SkyCoord(ra=ra_exp * u.rad, dec=dec_exp * u.rad, frame='icrs')
asteroids = MPChecker(str(c.ra.to_string(unit=u.hour, sep=':')),
str(c.dec.to_string(unit=u.degree, sep=':')),
date)#, box=box // 60 + 1)
diff_img_df = get_diffimg_season(season)
diff_img_df = diff_img_df[diff_img_df['expnum'] == expnum]
asteroids = asteroids.rename(columns={'mpcRA': 'ra', 'mpcDec': 'dec'})
asteroids.ra = asteroids.ra.apply(MPCToTNO.ra_convert)
asteroids.dec = asteroids.dec.apply(MPCToTNO.dec_convert)
asteroids.ra = asteroids.ra.apply(lambda ra: float(Angle(ra + ' hours').to_string(unit=u.degree, decimal=True)))
asteroids.dec = asteroids.dec.apply(lambda dec: float(Angle(dec + ' degrees').to_string(unit=u.degree, decimal=True)))
assert not asteroids.empty
print "Asteroids Found!"
se_df = get_SE_detections(desoper, expnum=expnum)
assert not se_df.empty
print "Exposure Not Empty!"
# se_df = get_SE_detections(desoper, ra, dec, box, expnum=expnum)
coadd_df = get_coadd_cutout(dessci, ra_exp, dec_exp, box)
catalog_df = get_transient_detections(se_df, coadd_df, 1)
catalog_df['date'] = catalog_df['date'].apply(lambda date: str(ephem.date(date)))
# diff_img_df = get_diffimg_cutout(ra, dec, box, season, "../wsdiff_catalogs")
# diff_img_df = get_diffimg_season(season, "../wsdiff_catalogs")
pickle(coadd=coadd_df, catalog=catalog_df, diff_img=diff_img_df)
# _, _, catalog_df, diff_img_df = unpickle(catalog=True, diff_img=True)
# diff_img_df.to_pickle('diff_img_df.pickle')
# catalog_df = pd.read_pickle('catalog_df_LARGE.pickle')
# diff_img_df = pd.read_pickle('diff_img_df_LARGE.pickle')
# drawT_eff(catalog_df, diff_img_df)
# diff_img_df['star_like'] = diff_img_df['spread_model'] + 3 * diff_img_df['spreaderr_model']
#
# diff_img_df = diff_img_df[(diff_img_df['star_like'] < 0.1) & (diff_img_df['star_like'] > -0.1)]
# plt.plot(diff_img_df['mag'], diff_img_df['star_like'], linestyle='None', marker='o')
# plt.xlabel('Magnitude')
# plt.ylabel("SM + 3 * SEM")
# plt.title("Starlike Diff_img Detections over Magnitude")
# plt.tight_layout()
# plt.savefig("Mag_vs_Starlike_Catalog.png")
catalog_df['mag'] = catalog_df.apply(lambda row: mag_calib(row), axis=1)
catalog_df['star_like'] = catalog_df['spread_model'] + 3 * catalog_df['spreaderr_model']
# catalog_df.to_pickle('catalog_df_LARGE_mag.pickle')
# catalog_df = pd.read_pickle('catalog_df_LARGE_mag.pickle')
print 'star_like column created'
# print catalog_df.head(10)
catalog_df = catalog_df[(catalog_df['star_like'] > -0.05) & (catalog_df['star_like'] < 0.05)]
print 'cuts made'
# catalog_df = catalog_df[(catalog_df['star_like'] > -0.1) & (catalog_df['star_like'] < 0.1)]
# plt.plot(catalog_df['mag'], catalog_df['star_like'], linestyle='None', marker='o')
# plt.xlabel('Magnitude')
# plt.ylabel("SM + 3 * SEM")
# plt.title("Starlike Catalog Detections over Magnitude")
# plt.tight_layout()
# plt.savefig("Mag_vs_Starlike_Catalog.png")
# overlap_coadd = overlap(diff_img_df, coadd_df, datematch=False, dropOverlap=True)
selfoverlap_df = self_overlap(catalog_df)
# overlap_df = overlap(diff_img_df, catalog_df)
try:
asteroid_catalog = overlap(catalog_df, asteroids, dropOverlap=False, datematch=False, threshold=4)
asteroid_diffimg = overlap(diff_img_df, asteroids, dropOverlap=False, datematch=False, threshold=4)
astcat_astdiff = df_intersection(asteroid_catalog, asteroid_diffimg, 1 / 3600)
# = overlap(asteroid_catalog, asteroid_diffimg, dropOverlap=True, datematch=False,
# threshold=1)
except ValueError: # raised if `y` is empty.
pass
print "Catalog Only: ", len(catalog_df)
print "Diff Img Only: ", len(diff_img_df)
# print "Both: ", len(overlap_df)
# print "Coadd Matches: ", len(overlap_coadd)
print "Self Overlap: ", len(selfoverlap_df)
print "Asteroids: ", len(asteroids)
print "Asteroids Found via Cat: ", len(asteroid_catalog)
print "Asteroids Found via DiffImg: ", len(asteroid_diffimg)
# expnum_ccd_band_set = set()
#
# for index, row in catalog_df.iterrows():
# expnum_ccd_band_set.add((row['expnum'], row['ccd'], row['band']))
#
# bleed_df = grab_bleedtrails(desoper, expnum_ccd_band_set)
# print bleed_df.head()
# print len(bleed_df)
cat_detect = plt.scatter(catalog_df['ra'], catalog_df['dec'], linestyle='None', color='g', marker='.', s=0.5)
diff_detect = plt.scatter(diff_img_df['ra'], diff_img_df['dec'], linestyle='None',color='c', marker='.', s=0.5) #see if any fall in coadd already
asteroid_plt = plt.scatter(asteroids['ra'], asteroids['dec'], linestyle='None', color='r', marker='o', alpha=0.4, label='asteroids')
cat_asteroid = plt.scatter(asteroid_catalog['ra'], asteroid_catalog['dec'], linestyle='None', color='blue', marker='o', label = 'catalogue')
diff_asteroid = plt.scatter(asteroid_diffimg['ra'], asteroid_diffimg['dec'], linestyle='None', color='black', marker='o', label = 'diffimg')
cat_diff_asteroid = plt.scatter(astcat_astdiff['ra'], astcat_astdiff['dec'], linestyle='None', color='purple', marker='o', label = 'catdiff')
plt.legend((cat_detect, diff_detect, asteroid_plt, cat_asteroid, diff_asteroid, cat_diff_asteroid),
('Catalogue', 'DiffImg', 'Asteroids', 'Catalogue Detections', 'Diffimg Detections', 'Catalogue and Diffimg'),
scatterpoints=1,
loc='lower left',
ncol=3,
fontsize=8)
title = "Exposure " + str(expnum)
plt.title(title)
plt.savefig('/Users/lynuszullo/pyOrbfit/Y4_Transient_Search/' + title)
print "Saved as " + title
# plt.savefig('detectionsLarge_0.05_cut_Overlap_Cat_Removed.png')
#
# plt.figure()
# plt.hist(catalog_df['star_like'], 50)
# plt.savefig('hist_starlike_Large_0.05_cut_Overlap_Cat_Removed.png')
#
# plt.figure()
# plt.hist(catalog_df['mag'], 50, range=(20, 30))
# plt.savefig('hist_mag_Large_0.05_cut_Overlap_Cat_Removed.png')
#
# plt.savefig('detectionsLargeCoadd_DI_Removed_Overlap_Cat_Removed_SpreadCut.png')
# plt.savefig("exposure" + str(expnum) + "_zoom.png")
expnumCCD_list = []
catalog_list = []
diff_img_list = []
overlap_list = []
coadd_list = []
asteroid_list = []
for index, row in catalog_df.iterrows():
expnumCCD_list.append((row['expnum'], row['ccd']))
catalog_list.append((row['ra'], row['dec'], row['expnum'], row['ccd']))
for index, row in diff_img_df.iterrows():
expnumCCD_list.append((row['expnum'], row['ccd']))
diff_img_list.append((row['ra'], row['dec'], row['expnum'], row['ccd']))
for index, row in asteroid_catalog.iterrows():
expnumCCD_list.append((row['expnum'], row['ccd']))
overlap_list.append((row['ra'], row['dec'], row['expnum'], row['ccd']))
# for index, row in coadd_df.iterrows():
# coadd_list.append((row['ra'], row['dec']))
for index, row in asteroids.iterrows():
asteroid_list.append((row['ra'], row['dec']))
os.chdir('/Users/lynuszullo/pyOrbfit/Y4_Transient_Search')
Find_imgs.findImgs(expnumCCD_list, catalog_list, diff_img_list, overlap_list, ra_deg,
dec_deg, box * 2, coadd_list=coadd_list, asteroid_list=asteroid_list,
keep_fits=False, only_asteroids=True)
parser = argparse.ArgumentParser(description=description)
parser.add_argument('config')
parser.add_argument('-t','--table',action='append')
parser.add_argument('-f','--force',action='store_true')
parser.add_argument('-v','--verbose',action='store_true')
parser.add_argument('-d','--dryrun',action='store_true')
args = parser.parse_args()
if args.verbose:
logging.getLogger().setLevel(logging.DEBUG)
config = yaml.load(open(args.config))
section = config['db']
data = yaml.load(open(config['dbtables']))
con = ea.connect(section=section,quiet=True)
con.drop_index = drop_index
cursor = con.cursor()
tables = data.keys()
for table in tables:
if args.table and (table not in args.table): continue
logging.debug(table)
idxname = data[table].get('idxname',table.split('_')[0])
params = dict(idxname=idxname,table=table)
for key,val in data[table]['columns'].items():
if not val.get('index',None): continue
for b in BANDS:
def __init__(self, trigger_id, jsonfilelist, triggerdir, datadir, real,
trigger_path):
#os.system('kinit -k -t /var/keytab/desgw.keytab desgw/des/[email protected]')
tstart = time.time()
if real:
self.backend = FileBackend("./realdb")
else:
self.backend = FileBackend("./testdb")
try:
thisevent = self.backend.get(Trigger, {'id': trigger_id})
print 'Found this event in desgw database...'
except Trigger.DoesNotExist:
thisevent = Trigger({
'id':
trigger_id,
'jsonfilelist':
jsonfilelist,
'triggerpath':
triggerdir,
'mapspath':
datadir,
'jobids': [
(0, 'jsonfile_corresponding_to_jobid.json'),
],
})
print 'Database entry created!'
self.trigger_id = trigger_id
self.trigger_path = trigger_path
self.backend.save(thisevent)
self.backend.commit()
with open(os.path.join(triggerdir, "strategy.yaml"), "r") as f:
self.config = yaml.safe_load(f)
self.filterobslist = np.array(self.config['exposure_filter'],
dtype='str')
self.strategydict = {}
for f in np.unique(self.filterobslist):
self.strategydict[f] = len(
self.filterobslist[self.filterobslist == f])
self.connection = ea.connect(DATABASE)
self.cursor = self.connection.cursor()
self.jsonfilelist = jsonfilelist
print self.jsonfilelist
if hardjson:
self.jsonfilelist = hj
self.trigger_id = trigger_id
self.datadir = datadir
self.triggerdir = triggerdir
self.processingdir = os.path.join(self.triggerdir, 'PROCESSING')
if not os.path.exists(self.processingdir):
os.makedirs(self.processingdir)
dire = './processing/' + trigger_id + '/'
if not os.path.exists(dire):
os.makedirs(dire)
with open(os.path.join(triggerdir, "strategy.yaml"), "r") as f:
self.strategy = yaml.safe_load(f)
with open("jobmanager.yaml", "r") as g:
self.jmconfig = yaml.safe_load(g)
q1 = "select expnum,nite,mjd_obs,telra,teldec,band,exptime,propid,obstype,object from exposure where " \
"nite>20130828 and nite<20150101 and expnum<300000 and obstype='object' order by expnum" # y1 images
self.connection.query_and_save(q1, './processing/exposuresY1.tab')
q2 = "select expnum,nite,mjd_obs,radeg,decdeg,band,exptime,propid,obstype,object from prod.exposure where " \
"nite>20150901 and obstype='object' order by expnum" # y2 and later
self.connection.query_and_save(q2, './processing/exposuresCurrent.tab')
os.system(
'cat ./processing/exposuresY1.tab ./processing/exposuresCurrent.tab > ./processing/exposures.list'
)
self.submit_all_jsons_for_sejobs(
) #preps all DES images that already exist
tfin = time.time()
print 'TOTAL SE JOBS TIME', tfin - tstart
#sys.exit()
self.monitor_images_from_mountain(
) #A loop that waits for images off mountain and submits for processing
def rawdata_exp_firstcut_query(args):
import easyaccess as ea
import pandas as pd
import numpy as np
if args.verbose>0:
print
print '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *'
print 'rawdata_exp_firstcut_query'
print '* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *'
print
query="""
with x as (
select /*+materialize */ pfw_attempt_id, tag
from prod.proctag
where tag='%s'
),
y as (
select /*+materialize */ a.expnum as expnum,
max(a.lastchanged_time) as evaltime
from prod.firstcut_eval a
where a.analyst!='SNQUALITY'
group by a.expnum
),
z as (
select /*+materialize */ b.*
from prod.firstcut_eval b, y
where b.expnum=y.expnum and
b.lastchanged_time=y.evaltime and
b.expnum between %d and %d
)
select e.expnum,
e.radeg as EXPRA, e.decdeg as EXPDEC, e.exptime, e.airmass,
e.band, e.nite, e.mjd_obs, e.field, e.object, e.program,
x.pfw_attempt_id, x.tag,
z.analyst as SOURCE, z.t_eff, z.fwhm_asec as PSF_FWHM, z.f_eff,
z.skybrightness, z.b_eff, z.cloud_apass, z.cloud_nomad,
z.n_apass, z.n_nomad, z.c_eff, z.cloud_des, z.n_des, z.accepted
from prod.exposure e, x, z
where e.expnum=z.expnum and
x.pfw_attempt_id=z.pfw_attempt_id and
(z.accepted='True' or e.program='photom-std-field')
order by e.expnum
""" % \
(args.tag, args.expnumMin, args.expnumMax)
if args.verbose>0: print query
outputFile=args.outputFile
# Open connection to db...
connection=ea.connect('desoper')
# Make query and save to output file...
connection.query_and_save(query,outputFile)
# Close connection to db...
connection.close()
if args.verbose>0: print
return 0
def test_connect_desoper(self):
con = ea.connect('desoper', quiet=True)
self.assertTrue(con.ping())
if update:
os.environ.update(env)
return env
############# Image Processing ################
############ create new season number ###########
### Y6 will start with 600 (use 417 for mock runs) #####
import easyaccess
import fitsio
query = 'SELECT max(SEASON) from marcelle.SNFAKEIMG where SEASON < 800;'
connection = easyaccess.connect('destest')
connection.query_and_save(query, 'testfile.fits')
data = fitsio.read('testfile.fits')
print(data[0][0])
newseason = (int(data[0][0] / 100) + 1) * 100
newseason = 519
print("the season number for this event is " + str(newseason))
print('')
#Update season number in dagmaker.rc
os.system("sed -i -e '/^SEASON/s/=.*$/=" + str(newseason) + "/' " +
DIR_SOURCE + "/gw_workflow/dagmaker.rc")
#Make curatedExposure.list
os.system("bash " + DIR_SOURCE + "/make_curatedlist.sh")
parser = argparse.ArgumentParser(description=description)
args = parser.parse_args()
data = create_test_data()
nrows = len(data)
# Create the data files
fitsfile = create_test_fits(data=data)
csvfile = create_test_csv(data=data)
tabfile = create_test_tab(data=data)
filenames = [fitsfile, csvfile, tabfile]
# Try loading through the python interface
# NOTE: This requires a desservice.ini file
conn = ea.connect()
query = 'select * from %s' % BASENAME
# Complains when the table doesn't exist, we could add:
# if conn.check_table_exists(BASENAME): conn.drop_table(BASENAME)
for filename in filenames:
# First try loading through python interface
print("*** TESTING PYTHON INTERFACE ***")
conn.drop_table(BASENAME)
conn.load_table(filename)
df = conn.query_to_pandas(query)
assert len(df) == nrows
# Then try loading with explicit tablename
import numpy as np
import easyaccess as ea
import shapely.geometry as geo
con_op = ea.connect(section='desoper')
q2 = 'SELECT f.RA_CENT,f.DEC_CENT,f.RACMAX,f.RACMIN,f.DECCMAX,f.DECCMIN,f.RAC1,f.RAC2,f.RAC3,f.RAC4,f.DECC1,f.DECC2,f.DECC3,f.DECC4,f.ID,d.TILENAME FROM RUMBAUGH.Y1A1_TILENAMES_LIST d,FELIPE.COADDTILE_GEOM_NEW f WHERE d.tilename=f.tilename'
TDF = con_op.query_to_pandas(q2)
Tpolys = np.zeros(len(TDF), dtype='object')
altpolys = np.zeros(0, dtype='object')
altpolynames = np.zeros(0, dtype='|S30')
altpolynums = np.zeros(0, dtype='i8')
altpolyracens, altpolydeccens = np.zeros(0, dtype='f8'), np.zeros(0,
dtype='f8')
for i in range(0, len(TDF)):
Tpolys[i] = geo.Polygon([(TDF['RAC1'][i], TDF['DECC1'][i]),
(TDF['RAC2'][i], TDF['DECC2'][i]),
(TDF['RAC3'][i], TDF['DECC3'][i]),
(TDF['RAC4'][i], TDF['DECC4'][i])])
if ((TDF['RACMAX'][i] < TDF['RACMIN'][i]) | (TDF['RACMIN'][i] < 0) |
(TDF['RACMAX'][i] > 360)):
ractmps = np.array(
[TDF['RAC1'][i], TDF['RAC2'][i], TDF['RAC3'][i], TDF['RAC4'][i]])
ractmps[ractmps > 300] -= 360
Tpolys[i] = geo.Polygon([(ractmps[0], TDF['DECC1'][i]),
(ractmps[1], TDF['DECC2'][i]),
(ractmps[2], TDF['DECC3'][i]),
(ractmps[3], TDF['DECC4'][i])])
ractmps = np.array(
parser = argparse.ArgumentParser(description=description)
args = parser.parse_args()
data = create_test_data()
nrows = len(data)
# Create the data files
fitsfile=create_test_fits(data=data)
csvfile =create_test_csv(data=data)
tabfile =create_test_tab(data=data)
filenames = [fitsfile,csvfile,tabfile]
# Try loading through the python interface
# NOTE: This requires a desservice.ini file
conn = ea.connect()
query = 'select * from %s'%BASENAME
# Complains when the table doesn't exist, we could add:
# if conn.check_table_exists(BASENAME): conn.drop_table(BASENAME)
for filename in filenames:
# First try loading through python interface
print("*** TESTING PYTHON INTERFACE ***")
conn.drop_table(BASENAME)
conn.load_table(filename)
df = conn.query_to_pandas(query)
assert len(df) == nrows
# Then try loading with explicit tablename
def main():
cmd = " ".join(sys.argv)
options, args = parser.parse_args(sys.argv[1:])
conn = ea.connect(section='destest')
cursor = conn.cursor() # create a cursor object to handle db
fname = options.outfile
if fname is None:
print "must specify output file."
exit()
out = open(fname, 'w+')
out.write(master_head_template.format(cmd))
if options.zpmin:
# Require minimum zeropoint for highly extincted measurements
q_zp = " and oi.chip_zero_point > {0:f}".format(options.zpmin)
else:
q_zp = " "
expnumfile = open(options.exptable, 'r')
expnumstr = '('
while (1 == 1):
try:
expnumstr = expnumstr + expnumfile.readline().split()[0]
except:
break
expnumstr = expnumstr + ','
expnumstr = expnumstr[:-1] + ')' # [:-1] to throw out unneeded ','
query = """\
SELECT DISTINCT
oi.ccdnum,
oi.nite,
oi.band,
oi.expnum,
(oi.chip_ccdgaina + oi.chip_ccdgainb)/2. as gain,
oi.chip_sigsky_search,
oi.chip_sigsky_template,
oi.psf_nea,
oi.chip_zero_point,
oi.chip_zero_point_rms,
fi.field,
f.mjd_obs,
fi.rac1,
fi.decc1,
fi.rac2,
fi.decc2,
fi.rac3,
fi.decc3,
fi.rac4,
fi.decc4
FROM
marcelle.snobsinfo oi, marcelle.snforce_image fi, marcelle.snforce f
WHERE
oi.image_name_diff = fi.image_name_diff and
fi.image_name_diff = f.image_name_diff and
oi.tile = 0 {0} and
oi.expnum in {1}
ORDER BY
field, oi.ccdnum, oi.nite, oi.band
""".format(q_zp, expnumstr)
print expnumstr
print query
# Run the query or load pre-saved results.
if os.path.exists(cache_filename):
print "Loading from {0}...".format(cache_filename)
data = np.load(cache_filename)
else:
print "Querying database (this may take a minute or two)..."
t0 = time.time()
QQ = cursor.execute(query) # execute query
QQ.description
header = [item[0] for item in cursor.description]
print header
rows = cursor.fetchall()
# figure out dtypes for our array so we can reference names:
dtype = []
for head in header:
if (head in ['CCDNUM', 'NITE', 'EXPNUM']):
dtype.append((head.lower(), int))
elif (head in ['BAND', 'FIELD']):
dtype.append((head.lower(), "S10"))
else:
dtype.append((head.lower(), float))
data = np.array(rows, dtype=dtype)
print data['field']
print data['band']
print "Query took {0:.2f} seconds, {1:.2f} MB array.".format(
time.time() - t0, data.nbytes / 1.e6)
if options.cache:
print "Saving to {0}...".format(cache_filename)
np.save(cache_filename, data)
msg = "{0:d} records".format(len(data))
print msg
out.write('#{0}\n'.format(msg))
#-------------------------------------------------------------------------
# BEGIN HACKS
# Hack to remove dirty data from the table:
# This is necessary because there were some -9's written to the database.
mask = data['psf_nea'] > 0.
data = data[mask]
msg = (
"HACK: {0:d} records after trimming entries with PSF_NEA < 0".format(
len(data)))
print msg
out.write('#{0}\n'.format(msg))
# Hack to cut crazy zeropoint RMS values, including nan
mask = data['chip_zero_point_rms'] <= 0.5
data = data[mask]
msg = ("HACK: {0:d} records after trimming entries with "
"CHIP_ZERO_POINT_RMS > 0.5".format(len(data)))
print msg
out.write('#{0}\n'.format(msg))
# END HACKS
# ------------------------------------------------------------------------
# If input file is defined, read positions.
if options.infile is not None:
msg = "Reading positions from {0}".format(options.infile)
print msg
positions = []
with open(options.infile, 'r') as f:
keys = f.readline().upper().strip().split(',')
if ('RA' not in keys) or ('DEC' not in keys):
print "RA and DEC must appear in first line of infile"
exit(1)
for line in f:
positions.append(odict(zip(keys, line.strip().split(','))))
# Convert RA, DEC to floats
for pos in positions:
pos['RA'] = float(pos['RA'])
pos['DEC'] = float(pos['DEC'])
ntarget = len(positions)
msg += '... {0:d} positions'.format(ntarget)
out.write('#{0}\n'.format(msg))
# Otherwise, report area from which positions will be selected.
else:
msg = "Randomly selecting position from fields:"
print msg
out.write('#{0}\n'.format(msg))
for name in areas.keys():
msg = (" {0}: RA=[{1:5.1f},{2:5.1f}] Dec=[{3:5.1f},{4:5.1f}] "
"{5:7.4f} deg^2, weight={6:6.4f}".format(
name, bounds[name][0], bounds[name][1], bounds[name][2],
bounds[name][3], areas[name], weights[name]))
print msg
out.write('#{0}\n'.format(msg))
ntarget = options.n
out.write('BEGIN LIBGEN\n')
ntot = 0
ngood = 0
noverlap = 0 # keep track of number in overlapping fields
while ngood < ntarget:
ntot += 1
# Get a position
if options.infile:
if len(positions) == 0: break
extrakeys = positions.pop(0)
ra = extrakeys.pop('RA')
dec = extrakeys.pop('DEC')
else:
ra, dec = get_random_position()
extrakeys = {}
mask = point_within_quad(ra, dec, data['rac1'], data['decc1'],
data['rac2'], data['decc2'], data['rac3'],
data['decc3'], data['rac4'], data['decc4'])
posdata = data[mask]
# Only write this out if there are 2 or more images (1 image is not
# enough to make a subtraction).
if len(posdata) > 1:
ngood += 1
# Write header and entries for this position to SIMLIB file
out.write(
head_template.format(ngood, ra, dec, len(posdata), 0., 0.27))
# Write extra keys
for key, val in extrakeys.iteritems():
out.write('{}: {}\n'.format(key, val))
# Get unique fields
fields = np.unique(posdata['field'])
if len(fields) > 1:
noverlap += 1
for i, field in enumerate(fields):
fielddata = posdata[posdata['field'] == field]
ccds = np.unique(fielddata['ccdnum'])
# For this field, find the first (arbitrary) image in each band
# and get its CHIP_ZERO_POINT and CHIP_SKYSIG_TEMPLATE
# to be recorded as TEMPLATE_ZPT and TEMPLATE_SKYSIG.
#
# This is made harder by the fact that some fields don't have
# images in every band. So there's not always a "first" band.
#
# NOTE:
# This is non-robust acrobatics!
# This info should just be two more columns in the table.
# Can YOU decipher what this is doing? I sure can't.
idx = []
for band in ['g', 'r', 'i', 'z']:
try:
idx.append(
np.flatnonzero(fielddata['band'] == band)[0])
except:
idx.append(-1) # -1 indicates no image in given band
zpstr = [
"%6.3f" %
fielddata['chip_zero_point'][k] if k != -1 else "0.0"
for k in idx
]
zpstr = " ".join(zpstr)
skystr = [
"%7.2f" %
fielddata['chip_sigsky_template'][k] if k != -1 else "0.0"
for k in idx
]
skystr = " ".join(skystr)
out.write(
field_template.format(field, str(ccds), zpstr, skystr))
if i == 0:
out.write(head_comment)
for j in range(len(fielddata)):
out.write(
line_template.format(
'S', float(fielddata['mjd_obs'][j]),
int(fielddata['expnum'][j]),
str(fielddata['band'][j]),
float(fielddata['gain'][j]), 0.,
float(fielddata['chip_sigsky_search'][j]),
float(fielddata['psf_nea'][j]), 0., 0.,
float(fielddata['chip_zero_point'][j]),
float(fielddata['chip_zero_point_rms'][j]), 99.))
out.write('END_LIBID: {0:d}\n'.format(ngood))
print '\r{0:<5d} on images / {1:<5d} placed'.format(ngood, ntot),
sys.stdout.flush()
out.write("END_OF_SIMLIB:\n")
print "\n{0:<5d} on overlapping fields".format(noverlap)
# Effective area. Only calculated if positions were seleceted randomly.
if options.infile is None:
effective_area = totarea * float(ngood) / ntot
print "Effective area: {0:.4f} deg^2".format(effective_area)
out.write('EFFECTIVE_AREA: {0:.4f}\n'.format(effective_area))
out.close()
print "Wrote to:", fname
class TestApi(unittest.TestCase):
con = ea.connect(quiet=True)
tablename = 'testtable'
nrows = 10000
prefetch = 4000
chunk = 1000
memsize = 1
sqlfile = 'temp.sql'
csvfile = 'temp.csv'
fitsfile = 'temp.fits'
h5file = 'temp.h5'
def test_ea_import(self):
print('\n*** test_ea_import ***\n')
test1 = self.con.ea_import('wrapped')
if test1 is not None:
self.assertTrue(test1)
test2 = self.con.ea_import('wrapped', help=True)
if test2 is not None:
self.assertTrue(test2)
def test_pandas_to_db(self):
print('\n*** test_pandas_to_db ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
cursor = self.con.cursor()
self.assertTrue(self.con.ping())
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(
self.con.pandas_to_db(df, tablename=self.tablename, append=True))
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
self.assertTrue(
self.con.pandas_to_db(df, tablename=self.tablename, append=True))
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
self.con.drop_table(self.tablename)
cursor.close()
def test_query_to_pandas(self):
print('\n*** test_query_to_pandas ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = 'select RA,DEC from {:}'.format(self.tablename.upper())
df2 = self.con.query_to_pandas(query)
self.assertEqual(len(df), len(df2))
self.assertEqual(df.columns.values.tolist().sort(),
df2.columns.values.tolist().sort())
#iterator
df3 = self.con.query_to_pandas(query, prefetch=4000, iterator=True)
self.assertEqual(len(df3.next()), 4000)
self.assertEqual(df3.next().columns.values.tolist().sort(),
df.columns.values.tolist().sort())
self.assertEqual(len(df3.next()), 2000)
self.con.drop_table(self.tablename)
def test_describe_table(self):
print('\n*** test_describe_table ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
self.assertEqual(len(self.con.describe_table(self.tablename)), 2)
self.con.drop_table(self.tablename)
def test_loadsql(self):
print('\n*** test_loadsql ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = """
-- This is a comment
select RA, DEC from %s -- this is another comment
""" % self.tablename
with open(self.sqlfile, 'w') as F:
F.write(query)
df2 = self.con.query_to_pandas(self.con.loadsql(self.sqlfile))
self.assertEqual(len(df), len(df2))
self.assertEqual(df.columns.values.tolist().sort(),
df2.columns.values.tolist().sort())
query = """
-- This is a comment
select RA, DEC from %s ; -- this is another comment
""" % self.tablename
with open(self.sqlfile, 'w') as F:
F.write(query)
df2 = self.con.query_to_pandas(self.con.loadsql(self.sqlfile))
self.assertEqual(len(df), len(df2))
self.assertEqual(df.columns.values.tolist().sort(),
df2.columns.values.tolist().sort())
self.con.drop_table(self.tablename)
os.remove(self.sqlfile)
def test_mytables(self):
print('\n*** test_mytables ***\n')
df = self.con.mytables()
self.assertTrue('FGOTTENMETADATA' in df['TABLE_NAME'].values.tolist())
def test_load_table_csv(self):
print('\n*** test_load_table_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
# name from filename
self.assertTrue(self.con.load_table(self.csvfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(self.con.append_table(self.csvfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
# name from tablename
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.csvfile, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(
self.con.append_table(self.csvfile, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
# chunksize
self.con.drop_table(self.tablename)
self.assertTrue(
self.con.load_table(self.csvfile,
name=self.tablename,
chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(
self.con.append_table(self.csvfile,
name=self.tablename,
chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_load_append_table_memory_csv(self):
print('\n*** test_load_append_table_memory_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile,
index=False,
float_format='%.8f',
sep=',',
mode='a',
header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(
self.con.load_table(self.csvfile,
name=self.tablename,
memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
self.assertTrue(
self.con.append_table(self.csvfile,
name=self.tablename,
memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
## end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_csv(self):
print('\n*** test_load_append_table_memory_chunk_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile,
index=False,
float_format='%.8f',
sep=',',
mode='a',
header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(
self.con.load_table(self.csvfile,
name=self.tablename,
memsize=self.memsize,
chunksize=self.chunk * 10))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
self.assertTrue(
self.con.append_table(self.csvfile,
name=self.tablename,
memsize=self.memsize,
chunksize=self.chunk * 200))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
## end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_table_fits(self):
print('\n*** test_load_table_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
# name from filename
self.assertTrue(self.con.load_table(self.fitsfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(self.con.append_table(self.fitsfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
# name from tablename
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.fitsfile,
name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(
self.con.append_table(self.fitsfile, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
# chunksize
self.con.drop_table(self.tablename)
self.assertTrue(
self.con.load_table(self.fitsfile,
name=self.tablename,
chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(
self.con.append_table(self.fitsfile,
name=self.tablename,
chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.fitsfile)
def test_load_append_table_memory_fits(self):
print('\n*** test_load_append_table_memory_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(
self.con.load_table(self.fitsfile,
name=self.tablename,
memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
self.assertTrue(
self.con.append_table(self.fitsfile,
name=self.tablename,
memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
## end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_fits(self):
print('\n*** test_load_append_table_memory_chunk_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(
self.con.load_table(self.fitsfile,
name=self.tablename,
memsize=self.memsize,
chunksize=self.chunk * 10))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
self.assertTrue(
self.con.append_table(self.fitsfile,
name=self.tablename,
memsize=self.memsize,
chunksize=self.chunk * 200))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
## end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_table_hdf5(self):
print('\n*** test_load_table_hdf5 ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_hdf(self.h5file, key='data')
self.assertTrue(os.path.exists(self.h5file))
self.con.drop_table(os.path.splitext(self.h5file)[0].upper())
# name from filename
self.assertTrue(self.con.load_table(self.h5file))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.h5file)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(self.con.append_table(self.h5file))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.h5file)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.h5file)[0].upper())
# name from tablename
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.h5file, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
## appending
self.assertTrue(self.con.append_table(self.h5file,
name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.h5file)
def test_query_and_save(self):
print('\n*** test_query_and_save ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
cursor = self.con.cursor()
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = 'select RA,DEC from %s' % self.tablename.upper()
self.con.query_and_save(query, self.csvfile, print_time=False)
self.assertTrue(os.path.exists(self.csvfile))
self.con.query_and_save(query, self.fitsfile, print_time=False)
self.assertTrue(os.path.exists(self.fitsfile))
self.con.query_and_save(query, self.h5file, print_time=False)
self.assertTrue(os.path.exists(self.h5file))
os.remove(self.csvfile)
os.remove(self.fitsfile)
os.remove(self.h5file)
for i in range(34):
self.assertTrue(
self.con.pandas_to_db(df,
tablename=self.tablename,
append=True))
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 35)
self.con.outfile_max_mb = 1
self.con.query_and_save(query, self.csvfile, print_time=False)
for i in range(4):
self.assertTrue(
os.path.exists(
os.path.splitext(self.csvfile)[0] + '_00000' + str(i + 1) +
'.csv'))
os.remove(
os.path.splitext(self.csvfile)[0] + '_00000' + str(i + 1) +
'.csv')
self.con.query_and_save(query, self.fitsfile, print_time=False)
for i in range(4):
self.assertTrue(
os.path.exists(
os.path.splitext(self.fitsfile)[0] + '_00000' +
str(i + 1) + '.fits'))
os.remove(
os.path.splitext(self.fitsfile)[0] + '_00000' + str(i + 1) +
'.fits')
self.con.outfile_max_mb = 1000
self.con.drop_table(self.tablename)
def test_inline_functions(self):
print('\n*** test_inline_functions ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
cursor = self.con.cursor()
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = 'select /*p: Y.my_sum(ra,dec) as testcol*/ from %s' % self.tablename
self.con.ea_import('wrapped as Y')
df = self.con.query_to_pandas(query)
self.assertEqual(len(df), self.nrows)
self.assertTrue('TESTCOL' in df.columns.values.tolist())
self.con.drop_table(self.tablename)
def run(args):
logname = datetime.datetime.now().strftime('%Y%m%d-%H%M%S')
logging.basicConfig(filename='BulkThumbs_' + logname + '_Rank_' +
str(rank) + '.log',
format='%(asctime)s - %(levelname)-8s - %(message)s',
level=logging.INFO)
logger = logging.getLogger(__name__)
logger.info('Rank: ' + str(rank) + '\n')
xs = float(args.xsize)
ys = float(args.ysize)
colors = args.colors.split(',')
usernm = ''
jobid = ''
outdir = ''
if rank == 0:
start = time.time()
if args.db == 'DR1':
db = 'desdr'
elif args.db == 'Y3A2':
db = 'dessci'
logger.info('Selected Options:')
# This puts any input type into a pandas dataframe
if args.csv:
userdf = pd.DataFrame(pd.read_csv(args.csv))
logger.info(' CSV: ' + args.csv)
elif args.ra:
coords = {}
coords['RA'] = args.ra
coords['DEC'] = args.dec
userdf = pd.DataFrame.from_dict(coords, orient='columns')
logger.info(' RA: ' + str(args.ra))
logger.info(' DEC: ' + str(arg.dec))
elif args.coadd:
coadds = {}
coadds['COADD_OBJECT_ID'] = args.coadd
userdf = pd.DataFrame.from_dict(coadds, orient='columns')
logger.info(' CoaddID: ' + str(args.coadd))
logger.info(' X size: ' + str(args.xsize))
logger.info(' Y size: ' + str(args.ysize))
logger.info(' Make TIFFs? ' + str(args.make_tiffs))
logger.info(' Make PNGs? ' + str(args.make_pngs))
logger.info(' Make FITS? ' + str(args.make_fits))
if args.make_fits:
logger.info(' Bands: ' + args.colors)
df = pd.DataFrame()
unmatched_coords = {'RA': [], 'DEC': []}
unmatched_coadds = []
logger.info('Connecting to: ' + db)
conn = ea.connect(db)
curs = conn.cursor()
usernm = str(conn.user)
jobid = str(uuid.uuid4())
#outdir = usernm + '/' + jobid + '/'
outdir = OUTDIR + '/' + usernnm + '/' + jobid + '/'
tablename = 'BTL_' + jobid.upper().replace(
"-", "_") # "BulkThumbs_List_<jobid>"
if 'RA' in userdf:
print(userdf.head())
if args.db == 'Y3A2':
ra_adjust = [
360 - userdf['RA'][i]
if userdf['RA'][i] > 180 else userdf['RA'][i]
for i in range(len(userdf['RA']))
]
userdf = userdf.assign(RA_ADJUSTED=ra_adjust)
userdf.to_csv(tablename + '.csv', index=False)
conn.load_table(tablename + '.csv', name=tablename)
query = "select temp.RA, temp.DEC, temp.RA_ADJUSTED, temp.RA as ALPHAWIN_J2000, temp.DEC as DELTAWIN_J2000, m.TILENAME from Y3A2_COADDTILE_GEOM m, {} temp where (m.CROSSRA0='N' and (temp.RA between m.RACMIN and m.RACMAX) and (temp.DEC between m.DECCMIN and m.DECCMAX)) or (m.CROSSRA0='Y' and (temp.RA_ADJUSTED between m.RACMIN-360 and m.RACMAX) and (temp.DEC between m.DECCMIN and m.DECCMAX))".format(
tablename)
df = conn.query_to_pandas(query)
curs.execute('drop table {}'.format(tablename))
os.remove(tablename + '.csv')
if args.db == 'DR1':
for i in range(len(userdf)):
ra = userdf['RA'][i]
ra180 = ra
if ra > 180:
ra180 = 360 - ra
if args.db == 'DR1':
query = "select * from (select TILENAME from DR1_TILE_INFO where (CROSSRA0='N' and ({0} between RACMIN and RACMAX) and ({1} between DECCMIN and DECCMAX)) or (CROSSRA0='Y' and ({2} between RACMIN-360 and RACMAX) and ({1} between DECCMIN and DECCMAX))) where rownum=1".format(
ra, userdf['DEC'][i], ra180)
f = conn.query_to_pandas(query)
if f.empty:
unmatched_coords['RA'].append(userdf['RA'][i])
unmatched_coords['DEC'].append(userdf['DEC'][i])
else:
df = df.append(f)
if 'COADD_OBJECT_ID' in userdf:
if args.db == 'Y3A2':
conn.load_table(args.csv, name=tablename)
query = "select temp.COADD_OBJECT_ID, m.ALPHAWIN_J2000, m.DELTAWIN_J2000, m.RA, m.DEC, m.TILENAME from Y3A2_COADD_OBJECT_SUMMARY m, {} temp where temp.COADD_OBJECT_ID=m.COADD_OBJECT_ID".format(
tablename)
df = conn.query_to_pandas(query)
curs.execute('drop table {}'.format(tablename))
if args.db == 'DR1':
for i in range(len(userdf)):
query = "select COADD_OBJECT_ID, ALPHAWIN_J2000, DELTAWIN_J2000, RA, DEC, TILENAME from DR1_MAIN where COADD_OBJECT_ID={0}".format(
userdf['COADD_OBJECT_ID'][i])
f = conn.query_to_pandas(query)
if f.empty:
unmatched_coadds.append(userdf['COADD_OBJECT_ID'][i])
else:
df = df.append(f)
conn.close()
df = df.sort_values(by=['TILENAME'])
#chunksize = int(df.shape[0] / nprocs) + (df.shape[0] % nprocs)
#df = [ df[ i:i+chunksize ] for i in range(0, df.shape[0], chunksize) ]
df = np.array_split(df, nprocs)
end = time.time()
print('Querying took (s): ' + str(end - start))
print(unmatched_coords)
print(unmatched_coadds)
logger.info('Querying took (s): ' + str(end - start))
if db == 'desdr':
logger.info('For coords input and DR1 db, \nUnmatched Coords: ' +
str(unmatched_coords))
logger.info('For coadds input and DR1 db, \nUnmatched Coadds: ' +
str(unmatched_coadds))
else:
df = None
usernm, jobid, outdir = comm.bcast([usernm, jobid, outdir], root=0)
#outdir = usernm + '/' + jobid + '/'
df = comm.scatter(df, root=0)
logger.info('JobID: ' + str(jobid))
tilenm = df['TILENAME'].unique()
for i in tilenm:
#tiledir = 'tiles_sample/' + i + '/'
tiledir = TILES_FOLDER + '/' + i + '/'
udf = df[df.TILENAME == i]
udf = udf.reset_index()
size = u.Quantity((ys, xs), u.arcmin)
positions = SkyCoord(udf['ALPHAWIN_J2000'],
udf['DELTAWIN_J2000'],
frame='icrs',
unit='deg',
equinox='J2000',
representation_type='spherical')
if args.make_tiffs or args.make_pngs:
MakeTiffCut(tiledir, outdir, positions, xs, ys, udf,
args.make_tiffs, args.make_pngs)
if args.make_fits:
MakeFitsCut(tiledir, outdir, size, positions, colors, udf)
def test_connect_dessci(self):
con = ea.connect('dessci', quiet=True)
self.assertTrue(con.ping())
def __init__(self, trigger_id, jsonfilelist, triggerdir, datadir, real, trigger_path):
#os.system('kinit -k -t /var/keytab/desgw.keytab desgw/des/[email protected]')
tstart = time.time()
if real:
self.backend = FileBackend("./realdb")
else:
self.backend = FileBackend("./testdb")
try:
self.thisevent = self.backend.get(Trigger, {'id': trigger_id})
print 'Found this event in desgw database...'
except Trigger.DoesNotExist:
self.thisevent = Trigger({
'id':trigger_id,
'jsonfilelist':jsonfilelist,
'triggerpath':triggerdir,
'mapspath':datadir,
'jobids':[
(0,'jsonfile_corresponding_to_jobid.json'),
],
'postprocint': 0
})
print 'Database entry created!'
self.trigger_id = trigger_id
self.trigger_path = trigger_path
self.backend.save(self.thisevent)
self.backend.commit()
with open(os.path.join(triggerdir,"strategy.yaml"), "r") as f:
self.config = yaml.safe_load(f);
self.filterobslist = np.array(self.config['exposure_filter'],dtype='str')
self.strategydict = {}
for f in np.unique(self.filterobslist):
self.strategydict[f] = len(self.filterobslist[self.filterobslist == f])
self.connection = ea.connect(DATABASE)
self.cursor = self.connection.cursor()
self.jsonfilelist = jsonfilelist
print self.jsonfilelist
if hardjson:
self.jsonfilelist = hj
#self.pp = subprocess.Popen('echo starting',stdout=PIPE, stderr=PIPE,shell=True)
self.trigger_id = trigger_id
self.datadir = datadir
self.triggerdir = triggerdir
self.processingdir = os.path.join(self.triggerdir,'PROCESSING')
if not os.path.exists(self.processingdir):
os.makedirs(self.processingdir)
dire = './processing/' + trigger_id + '/'
if not os.path.exists(dire):
os.makedirs(dire)
with open(os.path.join(triggerdir, "strategy.yaml"), "r") as f:
self.strategy = yaml.safe_load(f)
with open("jobmanager.yaml", "r") as g:
self.jmconfig = yaml.safe_load(g);
q1 = "select expnum,nite,mjd_obs,telra,teldec,band,exptime,propid,obstype,object from exposure where " \
"nite>20130828 and nite<20150101 and expnum<300000 and obstype='object' order by expnum" # y1 images
self.connection.query_and_save(q1, './processing/exposuresY1.tab')
q2 = "select expnum,nite,mjd_obs,radeg,decdeg,band,exptime,propid,obstype,object from prod.exposure where " \
"nite>20150901 and obstype='object' order by expnum" # y2 and later
self.connection.query_and_save(q2, './processing/exposuresCurrent.tab')
os.system('cat ./processing/exposuresY1.tab ./processing/exposuresCurrent.tab > ./processing/exposures.list')
#self.submit_post_processing()
self.submit_all_jsons_for_sejobs()#preps all DES images that already exist
tfin = time.time()
print 'TOTAL SE JOBS TIME', tfin - tstart
#sys.exit()
self.monitor_images_from_mountain()#A loop that waits for images off mountain and submits for processing
