def test_get_trixel_ids_that_overlap_conesearch_circles(self):
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra="23:25:53.56",
dec="+26:54:23.9",
radiusArcsec=5)
print("SINGLE COORDINATE CONESEARCH TRIXEL IDs")
print(cs._get_trixel_ids_that_overlap_conesearch_circles())
raList1 = ["13:20:00.00", 200.0, "13:20:00.00", 175.23, 21.36]
decList1 = ["+24:18:00.00", 24.3, "+24:18:00.00", -28.25, -15.32]
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=5)
print("COORDINATE LIST CONESEARCH TRIXEL IDs")
print(cs._get_trixel_ids_that_overlap_conesearch_circles())
def test_get_trixel_ids_that_overlap_conesearch_circles(self):
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra="23:25:53.56",
dec="+26:54:23.9",
radiusArcsec=5
)
print "SINGLE COORDINATE CONESEARCH TRIXEL IDs"
print cs._get_trixel_ids_that_overlap_conesearch_circles()
raList1 = ["13:20:00.00", 200.0, "13:20:00.00", 175.23, 21.36]
decList1 = ["+24:18:00.00", 24.3, "+24:18:00.00", -28.25, -15.32]
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=5
)
print "COORDINATE LIST CONESEARCH TRIXEL IDs"
print cs._get_trixel_ids_that_overlap_conesearch_circles()
def test_documentaion_function(self):
raList1 = [
"23:25:53.56", "02:10:08.16", "13:20:00.00", 1.47329, 35.34279
]
decList1 = [
"+26:54:23.9", "-48:38:24.3", "+24:18:00.00", 8.43016, -42.34428
]
print("TUTORIAL")
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=10,
separations=False,
distinct=False,
sqlWhere=False)
print(cs.query)
matchIndies, matches = cs.search()
for row in matches.list:
print(row)
def test_documentaion_functio2(self):
raList1 = ["23:25:53.56", "02:10:08.16",
"13:20:00.00", 1.47329, 35.34279]
decList1 = ["+26:54:23.9", "-48:38:24.3",
"+24:18:00.00", 8.43016, -42.34428]
print "TUTORIAL"
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=10,
separations=True,
distinct=True,
sqlWhere="spectralType is not null"
)
print cs.query
matchIndies, matches = cs.search()
for row in matches.list:
print row
print matches.table()
matches.table(filepath=pathToOutputDir + "results.dat")
print matches.mysql(tableName="mysql_table", filepath=None)
def test_documentaion_function(self):
raList1 = ["23:25:53.56", "02:10:08.16",
"13:20:00.00", 1.47329, 35.34279]
decList1 = ["+26:54:23.9", "-48:38:24.3",
"+24:18:00.00", 8.43016, -42.34428]
print "TUTORIAL"
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=10,
separations=False,
distinct=False,
sqlWhere=False
)
print cs.query
matchIndies, matches = cs.search()
for row in matches.list:
print row
def test_documentaion_functio2(self):
raList1 = [
"23:25:53.56", "02:10:08.16", "13:20:00.00", 1.47329, 35.34279
]
decList1 = [
"+26:54:23.9", "-48:38:24.3", "+24:18:00.00", 8.43016, -42.34428
]
print("TUTORIAL")
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=10,
separations=True,
distinct=True,
sqlWhere="spectralType is not null")
print(cs.query)
matchIndies, matches = cs.search()
for row in matches.list:
print(row)
print(matches.table())
matches.table(filepath=pathToOutputDir + "results.dat")
print(matches.mysql(tableName="mysql_table", filepath=None))
def test_conesearch_sql_where_function2(self):
raList1 = [
"23:25:53.56", "02:10:08.16", "13:20:00.00", 1.47329, 35.34279
]
decList1 = [
"+26:54:23.9", "-48:38:24.3", "+24:18:00.00", 8.43016, -42.34428
]
print("WHERE CLAUSE ADDED & DISTINCT")
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=10,
separations=True,
distinct=True,
sqlWhere="spectralType is not null")
matchIndies, matches = cs.search()
for row in matches.list:
pass
print(row)
def test_conesearch_function_exception(self):
from HMpTy.mysql import conesearch
try:
this = conesearch(log=log, fakeKey="break the code")
this.get()
assert False
except Exception as e:
assert True
print(str(e))
def test_conesearch_function2(self):
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=351.47321,
dec=26.90664,
radiusArcsec=5)
print(cs.query)
def test_conesearch_function(self):
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra="23:25:53.56",
dec="+26:54:23.9",
radiusArcsec=5)
print(cs.query)
def test_conesearch_function(self):
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra="23:25:53.56",
dec="+26:54:23.9",
radiusArcsec=5
)
print cs.query
def test_conesearch_function2(self):
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=351.47321,
dec=26.90664,
radiusArcsec=5
)
print cs.query
def test_conesearch_function_exception(self):
from HMpTy.mysql import conesearch
try:
this = conesearch(
log=log,
fakeKey="break the code"
)
this.get()
assert False
except Exception, e:
assert True
print str(e)
def test_conesearch_function4(self):
raList1 = ["23:25:53.56", "02:10:08.16",
"13:20:00.00", 1.47329, 35.34279]
decList1 = ["+26:54:23.9", "-48:38:24.3",
"+24:18:00.00", 8.43016, -42.34428]
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=7200
)
matchIndies, matches = cs.search()
def test_conesearch_function4(self):
raList1 = [
"23:25:53.56", "02:10:08.16", "13:20:00.00", 1.47329, 35.34279
]
decList1 = [
"+26:54:23.9", "-48:38:24.3", "+24:18:00.00", 8.43016, -42.34428
]
from HMpTy.mysql import conesearch
cs = conesearch(log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=7200)
matchIndies, matches = cs.search()
def test_conesearch_sql_where_function2(self):
raList1 = ["23:25:53.56", "02:10:08.16",
"13:20:00.00", 1.47329, 35.34279]
decList1 = ["+26:54:23.9", "-48:38:24.3",
"+24:18:00.00", 8.43016, -42.34428]
print "WHERE CLAUSE ADDED & DISTINCT"
from HMpTy.mysql import conesearch
cs = conesearch(
log=log,
dbConn=dbConn,
tableName="transientBucket",
columns="transientBucketId, spectralType",
ra=raList1,
dec=decList1,
radiusArcsec=10,
separations=True,
distinct=True,
sqlWhere="spectralType is not null"
)
matchIndies, matches = cs.search()
for row in matches.list:
print row
def main(arguments=None):
"""
*The main function used when `cl_utils.py` is run as a single script from the cl, or when installed as a cl command*
"""
# setup the command-line util settings
su = tools(arguments=arguments,
docString=__doc__,
logLevel="WARNING",
options_first=False,
projectName="HMpTy",
defaultSettingsFile=True)
arguments, settings, log, dbConn = su.setup()
# tab completion for raw_input
readline.set_completer_delims(' \t\n;')
readline.parse_and_bind("tab: complete")
readline.set_completer(tab_complete)
# UNPACK REMAINING CL ARGUMENTS USING `EXEC` TO SETUP THE VARIABLE NAMES
# AUTOMATICALLY
a = {}
for arg, val in list(arguments.items()):
if arg[0] == "-":
varname = arg.replace("-", "") + "Flag"
else:
varname = arg.replace("<", "").replace(">", "")
a[varname] = val
if arg == "--dbConn":
dbConn = val
a["dbConn"] = val
log.debug('%s = %s' % (
varname,
val,
))
hostFlag = a["hostFlag"]
userFlag = a["userFlag"]
passwdFlag = a["passwdFlag"]
dbNameFlag = a["dbNameFlag"]
tableName = a["tableName"]
index = a["index"]
htmid = a["htmid"]
primaryIdCol = a["primaryIdCol"]
raCol = a["raCol"]
decCol = a["decCol"]
ra = a["ra"]
dec = a["dec"]
radius = a["radius"]
level = a["level"]
forceFlag = a["forceFlag"]
renderFlag = a["renderFlag"]
search = a["search"]
if "database settings" in settings:
dbSettings = settings["database settings"]
else:
dbSettings = False
## START LOGGING ##
startTime = times.get_now_sql_datetime()
log.info('--- STARTING TO RUN THE cl_utils.py AT %s' % (startTime, ))
# set options interactively if user requests
if "interactiveFlag" in a and a["interactiveFlag"]:
# load previous settings
moduleDirectory = os.path.dirname(__file__) + "/resources"
pathToPickleFile = "%(moduleDirectory)s/previousSettings.p" % locals()
try:
with open(pathToPickleFile):
pass
previousSettingsExist = True
except:
previousSettingsExist = False
previousSettings = {}
if previousSettingsExist:
previousSettings = pickle.load(open(pathToPickleFile, "rb"))
# x-raw-input
# x-boolean-raw-input
# x-raw-input-with-default-value-from-previous-settings
# save the most recently used requests
pickleMeObjects = []
pickleMe = {}
theseLocals = locals()
for k in pickleMeObjects:
pickleMe[k] = theseLocals[k]
pickle.dump(pickleMe, open(pathToPickleFile, "wb"))
if a["init"]:
from os.path import expanduser
home = expanduser("~")
filepath = home + "/.config/HMpTy/HMpTy.yaml"
try:
cmd = """open %(filepath)s""" % locals()
p = Popen(cmd, stdout=PIPE, stderr=PIPE, shell=True)
except:
pass
try:
cmd = """start %(filepath)s""" % locals()
p = Popen(cmd, stdout=PIPE, stderr=PIPE, shell=True)
except:
pass
return
# CALL FUNCTIONS/OBJECTS
if index:
add_htm_ids_to_mysql_database_table(raColName=raCol,
declColName=decCol,
tableName=tableName,
dbConn=dbConn,
log=log,
primaryIdColumnName=primaryIdCol,
reindex=forceFlag,
dbSettings=dbSettings)
if search:
cs = conesearch(log=log,
dbConn=dbConn,
tableName=tableName,
columns=False,
ra=ra,
dec=dec,
radiusArcsec=float(radius),
separations=True,
distinct=False,
sqlWhere=False)
matchIndies, matches = cs.search()
if not renderFlag:
print(matches.table())
elif renderFlag == "json":
print(matches.json())
elif renderFlag == "csv":
print(matches.csv())
elif renderFlag == "yaml":
print(matches.yaml())
elif renderFlag == "md":
print(matches.markdown())
elif renderFlag == "table":
print(matches.markdown())
elif renderFlag == "mysql":
print(matches.mysql(tableName=resultsTable))
if level:
from HMpTy import HTM
mesh = HTM(depth=int(level), log=log)
htmids = mesh.lookup_id(ra, dec)
print(htmids[0])
if "dbConn" in locals() and dbConn:
dbConn.commit()
dbConn.close()
## FINISH LOGGING ##
endTime = times.get_now_sql_datetime()
runningTime = times.calculate_time_difference(startTime, endTime)
log.info(
'-- FINISHED ATTEMPT TO RUN THE cl_utils.py AT %s (RUNTIME: %s) --' % (
endTime,
runningTime,
))
return
def main(arguments=None):
"""
*The main function used when ``cl_utils.py`` is run as a single script from the cl, or when installed as a cl command*
"""
# setup the command-line util settings
su = tools(
arguments=arguments,
docString=__doc__,
logLevel="DEBUG",
options_first=False,
projectName="HMpTy"
)
arguments, settings, log, dbConn = su.setup()
# unpack remaining cl arguments using `exec` to setup the variable names
# automatically
for arg, val in arguments.iteritems():
if arg[0] == "-":
varname = arg.replace("-", "") + "Flag"
else:
varname = arg.replace("<", "").replace(">", "")
if isinstance(val, str) or isinstance(val, unicode):
exec(varname + " = '%s'" % (val,))
else:
exec(varname + " = %s" % (val,))
if arg == "--dbConn":
dbConn = val
log.debug('%s = %s' % (varname, val,))
## START LOGGING ##
startTime = times.get_now_sql_datetime()
log.info(
'--- STARTING TO RUN THE cl_utils.py AT %s' %
(startTime,))
# CALL FUNCTIONS/OBJECTS
if index:
add_htm_ids_to_mysql_database_table(
raColName=raCol,
declColName=decCol,
tableName=tableName,
dbConn=dbConn,
log=log,
primaryIdColumnName=primaryIdCol,
reindex=forceFlag
)
if search:
cs = conesearch(
log=log,
dbConn=dbConn,
tableName=tableName,
columns=False,
ra=ra,
dec=dec,
radiusArcsec=float(radius),
separations=True,
distinct=False,
sqlWhere=False
)
matchIndies, matches = cs.search()
if not renderFlag:
print matches.table()
elif renderFlag == "json":
print matches.json()
elif renderFlag == "csv":
print matches.csv()
elif renderFlag == "yaml":
print matches.yaml()
elif renderFlag == "md":
print matches.markdown()
elif renderFlag == "table":
print matches.markdown()
elif renderFlag == "mysql":
print matches.mysql(tableName=resultsTable)
if "dbConn" in locals() and dbConn:
dbConn.commit()
dbConn.close()
## FINISH LOGGING ##
endTime = times.get_now_sql_datetime()
runningTime = times.calculate_time_difference(startTime, endTime)
log.info('-- FINISHED ATTEMPT TO RUN THE cl_utils.py AT %s (RUNTIME: %s) --' %
(endTime, runningTime, ))
return
def main(arguments=None):
"""
*The main function used when ``cl_utils.py`` is run as a single script from the cl, or when installed as a cl command*
"""
# setup the command-line util settings
su = tools(arguments=arguments,
docString=__doc__,
logLevel="DEBUG",
options_first=False,
projectName="HMpTy")
arguments, settings, log, dbConn = su.setup()
# unpack remaining cl arguments using `exec` to setup the variable names
# automatically
for arg, val in arguments.items():
if arg[0] == "-":
varname = arg.replace("-", "") + "Flag"
else:
varname = arg.replace("<", "").replace(">", "")
if isinstance(val, str) or isinstance(val, str):
exec(varname + " = '%s'" % (val, ))
else:
exec(varname + " = %s" % (val, ))
if arg == "--dbConn":
dbConn = val
log.debug('%s = %s' % (
varname,
val,
))
## START LOGGING ##
startTime = times.get_now_sql_datetime()
log.info('--- STARTING TO RUN THE cl_utils.py AT %s' % (startTime, ))
# CALL FUNCTIONS/OBJECTS
if index:
add_htm_ids_to_mysql_database_table(raColName=raCol,
declColName=decCol,
tableName=tableName,
dbConn=dbConn,
log=log,
primaryIdColumnName=primaryIdCol,
reindex=forceFlag)
if search:
cs = conesearch(log=log,
dbConn=dbConn,
tableName=tableName,
columns=False,
ra=ra,
dec=dec,
radiusArcsec=float(radius),
separations=True,
distinct=False,
sqlWhere=False)
matchIndies, matches = cs.search()
if not renderFlag:
print(matches.table())
elif renderFlag == "json":
print(matches.json())
elif renderFlag == "csv":
print(matches.csv())
elif renderFlag == "yaml":
print(matches.yaml())
elif renderFlag == "md":
print(matches.markdown())
elif renderFlag == "table":
print(matches.markdown())
elif renderFlag == "mysql":
print(matches.mysql(tableName=resultsTable))
if level:
from HMpTy import HTM
mesh = HTM(depth=int(level), log=log)
htmids = mesh.lookup_id(ra, dec)
print(htmids[0])
if "dbConn" in locals() and dbConn:
dbConn.commit()
dbConn.close()
## FINISH LOGGING ##
endTime = times.get_now_sql_datetime()
runningTime = times.calculate_time_difference(startTime, endTime)
log.info(
'-- FINISHED ATTEMPT TO RUN THE cl_utils.py AT %s (RUNTIME: %s) --' % (
endTime,
runningTime,
))
return
def _feeder_survey_transientbucket_crossmatch(self):
"""*crossmatch remaining unique, unmatched sources in feeder survey with sources in the transientbucket & copy matched feeder survey rows to the transientbucket*
**Return**
- ``unmatched`` -- a list of the unmatched (i.e. new to the marshall) feeder survey surveys
"""
self.log.debug(
'starting the ``_feeder_survey_transientbucket_crossmatch`` method'
)
fsTableName = self.fsTableName
# GET THE COLUMN MAP FOR THE FEEDER SURVEY TABLE
sqlQuery = u"""
SELECT * FROM marshall_fs_column_map where fs_table_name = '%(fsTableName)s' and transientBucket_column in ('name','raDeg','decDeg','limitingMag')
""" % locals()
rows = readquery(log=self.log,
sqlQuery=sqlQuery,
dbConn=self.dbConn,
quiet=False)
columns = {}
for row in rows:
columns[row["transientBucket_column"]] = row["fs_table_column"]
if "raDeg" not in columns:
print(f"No coordinates to match in the {fsTableName} table")
return []
# BUILD QUERY TO GET UNIQUE UN-MATCHED SOURCES
fs_name = columns["name"]
self.fs_name = fs_name
fs_ra = columns["raDeg"]
fs_dec = columns["decDeg"]
if 'limitingMag' in columns:
fs_lim = columns["limitingMag"]
limitClause = " and %(fs_lim)s = 0 " % locals()
else:
limitClause = ""
sqlQuery = u"""
select %(fs_name)s, avg(%(fs_ra)s) as %(fs_ra)s, avg(%(fs_dec)s) as %(fs_dec)s from %(fsTableName)s where ingested = 0 %(limitClause)s and %(fs_ra)s is not null and %(fs_dec)s is not null group by %(fs_name)s
""" % locals()
rows = readquery(log=self.log,
sqlQuery=sqlQuery,
dbConn=self.dbConn,
quiet=False)
# STOP IF NO MATCHES
if not len(rows):
return []
# SPLIT INTO BATCHES SO NOT TO OVERWHELM MEMORY
batchSize = 200
total = len(rows)
batches = int(old_div(total, batchSize))
start = 0
end = 0
theseBatches = []
for i in range(batches + 1):
end = end + batchSize
start = i * batchSize
thisBatch = rows[start:end]
theseBatches.append(thisBatch)
unmatched = []
ticker = 0
for batch in theseBatches:
fs_name_list = []
fs_ra_list = []
fs_dec_list = []
fs_name_list = [row[fs_name] for row in batch if row[fs_ra]]
fs_ra_list = [row[fs_ra] for row in batch if row[fs_ra]]
fs_dec_list = [row[fs_dec] for row in batch if row[fs_ra]]
ticker += len(fs_name_list)
print(
"Matching %(ticker)s/%(total)s sources in the %(fsTableName)s against the transientBucket table"
% locals())
# CONESEARCH TRANSIENT BUCKET FOR PRE-KNOWN SOURCES FROM OTHER
# SURVEYS
from HMpTy.mysql import conesearch
cs = conesearch(log=self.log,
dbConn=self.dbConn,
tableName="transientBucket",
columns="transientBucketId, name",
ra=fs_ra_list,
dec=fs_dec_list,
radiusArcsec=3.5,
separations=True,
distinct=True,
sqlWhere="masterIDFlag=1",
closest=True)
matchIndies, matches = cs.search()
# CREATE SQL QUERY TO UPDATE MATCHES IN FS TABLE WITH MATCHED
# TRANSIENTBUCKET IDs
updates = []
originalList = matches.list
originalTotal = len(originalList)
print(
"Adding %(originalTotal)s new %(fsTableName)s transient detections to the transientBucket table"
% locals())
if originalTotal:
updates = []
updates[:] = [
"update " + fsTableName + " set transientBucketId = " +
str(o['transientBucketId']) + " where " + fs_name +
" = '" + str(fs_name_list[m]) +
"' and transientBucketId is null;"
for m, o in zip(matchIndies, originalList)
]
updates = ("\n").join(updates)
writequery(log=self.log, sqlQuery=updates, dbConn=self.dbConn)
# RETURN UNMATCHED TRANSIENTS
for i, v in enumerate(fs_name_list):
if i not in matchIndies:
unmatched.append(v)
# COPY MATCHED ROWS TO TRANSIENTBUCKET
self._feeder_survey_transientbucket_name_match_and_import()
self.log.debug(
'completed the ``_feeder_survey_transientbucket_crossmatch`` method'
)
return unmatched