def sql_get(self, host: str, user: str, password: str):
""" Query 'catalog_str' from 'database' using sqlutilpy.get() """
ra_min = self.ra_sat - 0.5 * self.width
ra_max = self.ra_sat + 0.5 * self.width
dec_min = self.dec_sat - 0.5 * self.width
dec_max = self.dec_sat + 0.5 * self.width
query_str = """
select {} from {}
where {} < ra and ra < {} and {} < dec and dec < {}
""".format(self.catalog_str, self.database, ra_min, ra_max,
dec_min, dec_max)
print("Querying data in the patch using sqlutilpy.get():")
datas = sqlutilpy.get(query_str,
host=host,
user=user,
password=password)
# update 'datas' dic to store queried data
for i, catalog in enumerate(self.catalog_list):
self.datas[catalog] = datas[i]
print(" %d sources are queried \n" % self.n_source())
def main():
# Whitespace does not matter
username = database_config.username
password = database_config.password
host = database_config.host
# database_name = database_config.database_name
ra, dec = sqlutil.get(
"select ra, dec from des_dr1.main where q3c_radial_query(ra,dec,317.2044,-51.1656,1)",
host=host,
user=username,
password=password)
# Getting the entire table
# Do not run this...this is too big. this is here for reference
"""
something = sqlutil.get(
"select * from des_dr1.main", host="wsdb.hpc1.cs.cmu.edu",
user=username, password=password
)
"""
print("ra:", ra, "dec:", dec)
print("ra type:", type(ra), "dec type:", type(dec))
import numpy as np
import sqlutilpy
RADIUS = 1
labels = "ra,dec,type,psfmag_u,psfmag_g,psfmag_r,psfmag_i,psfmag_z,modelmag_u,modelmag_g,modelmag_r,modelmag_i,modelmag_z"
sql_str = """
SELECT {}
FROM sdssdr9.phototag
WHERE q3c_radial_query(ra, dec, {}, {}, {})
and mode=1
and psfmag_u > 0 and modelmag_u > 0
and psfmag_g > 0 and modelmag_g > 0
and psfmag_r > 0 and modelmag_r > 0
and psfmag_i > 0 and modelmag_i > 0
and psfmag_z > 0 and modelmag_z > 0
""".format(labels, 150, 0, RADIUS)
sql_query = sqlutilpy.get(sql_str, host='xxx', user='******', password='******')
np.savetxt("sdss_phototag_{}.csv".format(RADIUS),
np.array(sql_query).T,
delimiter=",",
header=labels,
comments="")
print('%d objects' % len(sql_query[0]))
import sys, pickle
sys.path.append('/home/andy/Documents/Research/software/')
import sqlutilpy, getdata, numpy as np, h5py
#catalogue = "gaia_edr3.gaia_source"
catalogue = "andy_everall.gaia3_rand100m"
query = f"""select floor(phot_g_mean_mag/0.1)*0.1 as magbin,
percentile_cont(0.5) within group (ORDER BY SQRT(phot_g_n_obs)*phot_g_mean_flux_error/phot_g_mean_flux) as med_gamp
from {catalogue}
where phot_g_mean_mag is not NULL
group by magbin"""
print(query)
med_amp = sqlutilpy.get(query, asDict=True, **getdata.sql_args)
print('Processing data')
order = np.argsort(med_amp['magbin'])
for key in med_amp.keys():
med_amp[key] = med_amp[key][order]
savefile = 'median_gamp_edr3.h'
print(f'Saving data: {savefile}')
with h5py.File(savefile, 'w') as hf:
for key in med_amp.keys():
hf.create_dataset(key, data=med_amp[key])
import sys, pickle
sys.path.append('/home/andy/Documents/Research/software/')
import sqlutilpy, getdata, numpy as np, h5py
catalogue = "gaia_edr3.gaia_source" #"andy_everall.gaia3_rand100m"
query = f"""select floor(phot_g_mean_mag/0.1)*0.1 as magbin,
sum(phot_g_n_obs) as k,
sum(matched_transits) as n,
sum(astrometric_matched_transits) as n_ast,
count(*)
from {catalogue}
where phot_g_mean_mag is not NULL
group by magbin"""
print(query)
obs_counts = sqlutilpy.get(query, asDict=True, **getdata.sql_args)
order = np.argsort(obs_counts['magbin'])
for key in obs_counts.keys():
obs_counts[key] = obs_counts[key][order]
obs_counts['mean_eff'] = (obs_counts['k'] + 1) / (obs_counts['n'] * 9. + 2)
savefile = 'expected_gobs_efficiency_edr3.h'
print(f'Saving data: {savefile}')
with h5py.File(savefile, 'w') as hf:
for key in obs_counts.keys():
hf.create_dataset(key, data=obs_counts[key])