def datafetcher():
# This function fetches a sample SDSS data for ra, dec, run from PhotoObj within certain magnitudes.
# The resulting data table is then saved for future reference as a .csv file.
query1 = """
select
ra, dec, run
from PhotoObj
where psfMag_g BETWEEN 16.96 and 16.99
"""
query2 = """
select
ra, dec, run
from PhotoObj
where psfMag_g BETWEEN 17 and 17.041
"""
query3 = """
select
ra, dec, run
from PhotoObj
where psfMag_g BETWEEN 17.041 and 17.08
"""
query4 = """
select
ra, dec, run
from PhotoObj
where psfMag_g BETWEEN 17.08 and 18.02
"""
res1 = SDSS.query_sql(query1, timeout=3600).to_pandas()
res2 = SDSS.query_sql(query2, timeout=3600).to_pandas()
res3 = SDSS.query_sql(query3, timeout=3600).to_pandas()
res4 = SDSS.query_sql(query4, timeout=3600).to_pandas()
concat_res = pd.concat([res1, res2, res3, res4], ignore_index=True)
concat_res.to_csv('SDSSdatasample.csv')
return concat_res
def query_sdss(query_str, filename, obj_key='objID', data_release=14):
objid = -1
cnt = 0
row_count = 500000
print('querying', filename)
while row_count == 500000:
start = time.time()
print('query number', cnt)
table = SDSS.query_sql(query_str.format(objid),
timeout=600,
data_release=data_release)
print('seconds taken:', int(time.time() - start))
row_count = len(table)
objid = table[row_count - 1][obj_key]
print('row_count', row_count)
print('head')
print(table[:5])
print('tail')
print(table[-5:])
ascii.write(table,
filename.format(cnt),
format='csv',
fast_writer=False)
print('saved to csv')
cnt += 1
def SDSS_QUERY_SPEC_Z(TILE_RA_LOWER_LIMIT, TILE_DEC_LOWER_LIMIT,
TILE_RA_UPPER_LIMIT, TILE_DEC_UPPER_LIMIT):
query = "SELECT objID, ra, dec,z FROM SpecPhoto WHERE (ra between %s and %s) and (dec between %s and %s)" % (
TILE_RA_UPPER_LIMIT, TILE_RA_LOWER_LIMIT, TILE_DEC_LOWER_LIMIT,
TILE_DEC_UPPER_LIMIT)
data = SDSS.query_sql(query, data_release=15)
name = 'SDSS_Tile_Sources.csv'
file = open(os.path.join(Main_Path, name), 'w')
file.write('specobjID,ra,dec,z')
file.write('\n')
specobjID = data['objID']
ra = data['ra']
dec = data['dec']
z = data['z']
for i in range(len(z)):
info = '%s,%s,%s,%s' % (specobjID[i], ra[i], dec[i], z[i])
file.write(info)
file.write('\n')
file.close()
print data
def get_photometry(N=10000):
"""Get photometry from the SDSS Database
Parameters
----------
N : int
specifies the number of objects to query
Returns
-------
data : astropy.Table
table of queried photometry
"""
query = """
SELECT TOP {N}
p.psfMag_r, p.fiberMag_r, p.fiber2Mag_r, p.petroMag_r,
p.deVMag_r, p.expMag_r, p.modelMag_r, p.cModelMag_r,
s.class
FROM PhotoObjAll AS p JOIN specObjAll s ON s.bestobjid = p.objid
WHERE p.mode = 1 AND s.sciencePrimary = 1 AND p.clean = 1 AND s.class != 'QSO'
ORDER BY p.objid ASC
"""
data = SDSS.query_sql(query.format(N=N))
return data
def SDSS_get_calib(self, rad_min = 5):
'''
Parameters
----------
rad_min (optional, int or float) : search radius in decimal arcminutes
Output
------
file : writes processed calibration file and sets member variables
'''
# formulate SQL query and execute
query = '''SELECT p.ra, p.dec, p.u, p.Err_u, p.g, p.Err_g, p.r, p.Err_r, p.i, p.Err_i, p.z, p.Err_z
FROM fGetNearbyObjEq({},{},{}) n, PhotoPrimary p
WHERE n.objID = p.objID AND p.type = 6'''.format(self.targetra, self.targetdec, rad_min)
data = SDSS.query_sql(query)
# process and return results
if data is None:
print('Search of SDSS for calibration information failed...')
return None
for filt in 'ugriz':
data.rename_column('Err_' + filt, filt + 'Err')
self.cal_source = 'SDSS'
self.cal_filename = 'cal_{}_{}.dat'.format(self.targetname, self.cal_source)
ascii.write(data, os.path.join(self.relative_path, self.cal_filename))
def __init__(self, query=None, table=None):
# Either query or table, but not both (query xor table).
if (query and table is not None) or\
(not query and table is None):
raise Exception('Either the query or the table parameter is '
'required. But not both.')
if table is not None:
# Use table if it was provided.
self.table = table
else:
# Otherwise use the query to create the table.
self.table = SDSS.query_sql(query)
self.catalog = np.array(self.table)
self.catalog = rename_fields(self.catalog, {'objID': 'id'})
# Calculate B and V like the VizieR data.
# Use Robert Lupton's derived equations found here:
# http://www.sdss3.org/dr8/algorithms/sdssUBVRITransform.php
g = self.catalog['g']
r = self.catalog['r']
B = g + 0.3130 * (g - r) + 0.2271 # sigma = 0.0107
V = g - 0.5784 * (g - r) - 0.0038 # sigma = 0.0054
self.catalog = append_fields(self.catalog, 'B', B)
self.catalog = append_fields(self.catalog, 'V', V)
def res(ra, dec, ang):
"""Fetches SDSS data with GetNearbyObj function and given parameters"""
query = """
SELECT
s.ra, s.dec,
s.dered_g as g, s.dered_r as r,
s.err_g, s.err_r,
s.flags
FROM
dbo.fGetNearbyObjEq({}, {}, {}) AS n
JOIN Star AS s ON n.objID = s.objID
WHERE
g - r BETWEEN -0.5 AND 2.5
AND g BETWEEN 14 and 24
""".format(ra,dec,ang)
return SDSS.query_sql(query, timeout = 600)
def SDSS_query(
ra_bounds: Tuple[float, float],
dec_bounds: Tuple[float, float],
clean: bool = False,
threshold: float = 20,
num_stars: int = 20000,
data_release: int = 15,
):
jobquery = (
"SELECT TOP " + str(num_stars) +
" p.objid,p.ra,p.dec,p.u,p.g,p.r,p.i,p.z,p.type,p.clean,pm.pmra,pm.pmdec FROM PhotoObj AS p JOIN propermotions pm ON p.objid = pm.objid WHERE p.ra BETWEEN "
+ str(ra_bounds[0]) + " AND " + str(ra_bounds[1]) +
" AND p.dec BETWEEN " + str(dec_bounds[0]) + " AND " +
str(dec_bounds[1]) + " AND p.g < " + str(threshold))
if clean:
# recommend applying cut after matching to avoid mismatches
jobquery = jobquery + " AND p.clean = 1"
res = SDSS.query_sql(jobquery, data_release=data_release)
return res
def select(sql, filename='SDSSobjects.csv'):
"""
Executes an SDSS database query and stores the result in a csv file.
Parameters
----------
sql: string
SQL query statement
filename: string
desired name of output csv file
Notes
-----
Uses `astroquery` to query the SDSS database. Read more at https://astroquery.readthedocs.io
"""
from astroquery.sdss import SDSS as now
sdssdata = now.query_sql(sql)
# sdssdata is a Table object that can be saved in a csv file with:
from astropy.io import ascii
ascii.write(sdssdata, filename, format='csv', fast_writer=False)
print('SDSS data downloaded and written to '+filename)
# Return a dataframe to the command line as well.
return pd.read_csv(filename)
def query_sdss(**kwargs):
log = get_log_func(default_level='debug', **kwargs)
sql_query = [
"SELECT ra,dec,raErr,decErr,u,err_u,g,err_g,r,err_r,i,err_i,z,err_z",
"FROM Star WHERE",
" ra BETWEEN {min_ra:f} and {max_ra:f}",
"AND dec BETWEEN {min_dec:f} and {max_dec:f}",
"AND ((flags_{filter:s} & 0x10000000) != 0)", # detected in BINNED1
# not EDGE, NOPROFILE, PEAKCENTER, NOTCHECKED, PSF_FLUX_INTERP,
# SATURATED, or BAD_COUNTS_ERROR"
"AND ((flags_{filter:s} & 0x8100000c00a4) = 0)",
# not DEBLEND_NOPEAK or small PSF error
"AND (((flags_{filter:s} & 0x400000000000) = 0) or "
"(psfmagerr_{filter:s} <= 0.2))",
# not INTERP_CENTER or not COSMIC_RAY
"AND (((flags_{filter:s} & 0x100000000000) = 0) or "
"(flags_{filter:s} & 0x1000) = 0)"
]
sql_query = '\n'.join(sql_query).format(**kwargs)
log("query SDSS with sql string\n{}".format(sql_query))
cat = SDSS.query_sql(sql_query)
if cat is not None:
log("{} stars found in SDSS".format(len(cat)))
return cat
def get_photometry(N=10000):
"""Get photometry from the SDSS Database
Parameters
----------
N : int
specifies the number of objects to query
Returns
-------
data : astropy.Table
table of queried photometry
"""
query = """
SELECT TOP {N}
p.psfMag_r, p.fiberMag_r, p.fiber2Mag_r, p.petroMag_r,
p.deVMag_r, p.expMag_r, p.modelMag_r, p.cModelMag_r,
s.class
FROM PhotoObjAll AS p JOIN specObjAll s ON s.bestobjid = p.objid
WHERE p.mode = 1 AND s.sciencePrimary = 1 AND p.clean = 1 AND s.class != 'QSO'
ORDER BY p.objid ASC
"""
data = SDSS.query_sql(query.format(N=N))
return data
def get_catalog(self, photoobj_fields=None, timeout=120, print_query=False):
"""
Query SDSS for all objects within a given
radius of the input coordinates.
Merges photometry with photo-z
TODO -- Expand to include spectroscopy
TODO -- Consider grabbing all of the photometry fields
Args:
coord: astropy.coordiantes.SkyCoord
radius: Angle, optional
Search radius
photoobj_fields: list
Fields for querying
timeout: float, optional
Default value - 120 s.
print_query: bool, optional
Print the SQL query for the photo-z values
Returns:
catalog: astropy.table.Table
Contains all measurements retieved
*WARNING* :: The SDSS photometry table frequently has multiple entries for a given
source, with unique objid values
"""
if photoobj_fields is None:
photoobj_fs = ['ra', 'dec', 'objid', 'run', 'rerun', 'camcol', 'field','type']
mags = ['modelMag_'+band for band in SDSS_bands]
magsErr = ['modelMagErr_'+band for band in SDSS_bands]
extinct = ["extinction_"+band for band in SDSS_bands]
photoobj_fields = photoobj_fs+mags+magsErr+extinct
# Call
photom_catalog = SDSS.query_region(self.coord, radius=self.radius, timeout=timeout,
photoobj_fields=photoobj_fields)
if photom_catalog is None:
self.catalog = Table()
self.catalog.meta['radius'] = self.radius
self.catalog.meta['survey'] = self.survey
# Validate
self.validate_catalog()
return
# Now query for photo-z
query = "SELECT GN.distance, "
query += "p.objid, "
query += "pz.z as redshift, pz.zErr as redshift_error\n"
query += "FROM PhotoObj as p\n"
query += "JOIN dbo.fGetNearbyObjEq({:f},{:f},{:f}) AS GN\nON GN.objID=p.objID\n".format(
self.coord.ra.value,self.coord.dec.value,self.radius.to('arcmin').value)
query += "JOIN Photoz AS pz ON pz.objID=p.objID\n"
query += "ORDER BY distance"
if print_query:
print(query)
# SQL command
photz_cat = SDSS.query_sql(query,timeout=timeout)
# Match em up
if photz_cat is not None:
matches = catalog_utils.match_ids(photz_cat['objid'], photom_catalog['objid'], require_in_match=False)
else:
matches = -1 * np.ones(len(photom_catalog), dtype=int)
gdz = matches > 0
# Init
photom_catalog['photo_z'] = -9999.
photom_catalog['photo_zerr'] = -9999.
# Fill
if np.any(gdz):
photom_catalog['photo_z'][matches[gdz]] = photz_cat['redshift'][np.where(gdz)]
photom_catalog['photo_zerr'][matches[gdz]] = photz_cat['redshift_error'][np.where(gdz)]
# Trim down catalog
trim_catalog = trim_down_catalog(photom_catalog, keep_photoz=True)
# Clean up
trim_catalog = catalog_utils.clean_cat(trim_catalog, photom['SDSS'])
# Spectral info
spec_fields = ['ra', 'dec', 'z', 'run2d', 'plate', 'fiberID', 'mjd', 'instrument']
spec_catalog = SDSS.query_region(self.coord,spectro=True, radius=self.radius,
timeout=timeout, specobj_fields=spec_fields) # Duplicates may exist
if spec_catalog is not None:
trim_spec_catalog = trim_down_catalog(spec_catalog)
# Match
spec_coords = SkyCoord(ra=trim_spec_catalog['ra'], dec=trim_spec_catalog['dec'], unit='deg')
phot_coords = SkyCoord(ra=trim_catalog['ra'], dec=trim_catalog['dec'], unit='deg')
idx, d2d, d3d = match_coordinates_sky(spec_coords, phot_coords, nthneighbor=1)
# Check
if np.max(d2d).to('arcsec').value > 1.5:
raise ValueError("Bad match in SDSS")
# Fill me
zs = -1 * np.ones_like(trim_catalog['ra'].data)
zs[idx] = trim_spec_catalog['z']
trim_catalog['z_spec'] = zs
else:
trim_catalog['z_spec'] = -1.
# Sort by offset
catalog = trim_catalog.copy()
self.catalog = catalog_utils.sort_by_separation(catalog, self.coord, radec=('ra','dec'), add_sep=True)
# Meta
self.catalog.meta['radius'] = self.radius
self.catalog.meta['survey'] = self.survey
# Validate
self.validate_catalog()
# Return
return self.catalog.copy()
sdss_filter, sky_layout, out_file = sys.argv[1:]
entry = Table.read(sky_layout, format='ascii.commented_header')[-1]
box = [entry['ramin'], entry['ramax'], entry['decmin'], entry['decmax']]
# sdss_filter = re.match(r'.+odi_(\w).+', in_files[0]).group(1)
# query sdss and get reference catalog
sql_query = [
"SELECT ra,dec,raErr,decErr,u,err_u,g,err_g,r,err_r,i,err_i,z,err_z",
"FROM Star WHERE",
" ra BETWEEN {min_ra:f} and {max_ra:f}",
"AND dec BETWEEN {min_dec:f} and {max_dec:f}",
"AND ((flags_{filter:s} & 0x10000000) != 0)", # detected in BINNED1
# not EDGE, NOPROFILE, PEAKCENTER, NOTCHECKED, PSF_FLUX_INTERP,
# SATURATED, or BAD_COUNTS_ERROR"
"AND ((flags_{filter:s} & 0x8100000c00a4) = 0)",
# not DEBLEND_NOPEAK or small PSF error
"AND (((flags_{filter:s} & 0x400000000000) = 0) or "
"(psfmagerr_{filter:s} <= 0.2))",
# not INTERP_CENTER or not COSMIC_RAY
"AND (((flags_{filter:s} & 0x100000000000) = 0) or "
"(flags_{filter:s} & 0x1000) = 0)"
]
sql_query = '\n'.join(sql_query).format(filter=sdss_filter,
min_ra=box[0],
max_ra=box[1],
min_dec=box[2],
max_dec=box[3])
print sql_query
stdstar = SDSS.query_sql(sql_query)
stdstar.write(out_file, format='ascii.commented_header')
def get_sdss_spectra(outfile = "outfile", N_spec = 5):
from urllib2 import HTTPError
from astroquery.sdss import SDSS
# query = "SELECT TOP 1000 p.objid, p.dec, p.r,p.i, p.run, p.rerun, p.camcol, p.field, s.specobjid, s.class, s.z as redshift FROM PhotoObj AS p JOIN SpecObj AS s ON s.bestobjid = p.objid WHERE p.r BETWEEN 0 AND 17.0 AND s.class = 'QSO' AND s.z BETWEEN 1.0 AND 2.3 AND p.dec >= 15.0"
query = "SELECT TOP "+str(N_spec)+" specObjID, plate, mjd, subClass, fiberID FROM SpecPhoto WHERE (class = 'QSO') AND" \
" (psfmag_r <= 17.0) AND (dec >= 15.0) AND (z BETWEEN 1.0 AND 2.3) AND zwarning = 0 AND" \
" (subClass = 'BROADLINE') AND nChild = 0 AND (mode = 1) AND ((0x10000000) != 0)" \
" AND (bossprimary= 0) AND programname = 'legacy'"
res = SDSS.query_sql(query)
# (subClass = 'BROADLINE') AND
# print(res['subClass'])
spectra = []
var = []
waves = []
mask = []
z = []
# print(res['plate'], res['mjd'], res['fiberID'])
num_skipped = 0
count = 1
n_spec = len(res['specObjID'])
for i in range(n_spec):
# print(res['subClass'][i])
try:
sp = SDSS.get_spectra(plate=res['plate'][i], mjd=res['mjd'][i], fiberID=res['fiberID'][i])[0]
data = (sp[1].data)
wave = (10**data.field('loglam'))
flux = data.field('flux')
err = data.field('ivar')
masking = data.field('and_mask')
mask.append(masking)
z.append(sp[2].data.field('Z'))
spectra.append(flux)
var.append(err)
waves.append(wave)
# print(res['plate'][i],res['mjd'][i], res['fiberID'][i])
# pl.plot(wave, flux)
# pl.show()
count += 1
except HTTPError:
num_skipped += 1
print("%i, %i, %i not found" % (res['plate'][i], res['mjd'][i], res['fiberID'][i]))
continue
except ValueError:
num_skipped += 1
print("%i, %i, %i ValueError" % (res['plate'][i], res['mjd'][i], res['fiberID'][i]))
continue
except TypeError:
num_skipped += 1
print("%i, %i, %i TypeError" % (res['plate'][i], res['mjd'][i], res['fiberID'][i]))
continue
print('Number of spectrum processed: {0} out of {1}'.format(count, n_spec - num_skipped))
print(" %i spectra skipped" % num_skipped)
# exit()
np.savez(outfile,
wave = waves,
spectra=spectra,
var = var,
mask = mask,
plate = res['plate'],
mjd = res['mjd'],
fiberID = res['fiberID'],
z = z
)
WHERE zs.p_cw+zs.p_acw > {} OR zs.p_el > {}
AND p.petroR90_g > {} AND p.petroR90_g < {} """.format(
query_selected_galaxies, select_class_prob, select_class_prob,
select_min_radius, select_max_radius)
# uncomment that if you want to really see the sql query
#print queryZooGalaxies
#-------------------------------------------------------------------------------
# running query ----------------------------------------------------------------
if not os.path.exists('00_to_be_downloaded.lst'):
# > Performing query:
resultsZoo = SDSS.query_sql(queryZooGalaxies)
print "> Number of objects returned: ", len(resultsZoo)
# > Storing query results:
f = open("00_to_be_downloaded.lst", "w")
f.write("# List of objectIDs left to be downloaded\n")
f.write("#\n")
for i in range(0, len(resultsZoo)):
objectID = resultsZoo[i][0]
f.write("%s\n" % objectID)
resultsZoo = SDSS.query_sql(queryZooGalaxies)
# reloading query
def get_coordinates_from_query(save_metatable=False, save_coordinates=False, source_type=None):
"""
get_coordinates_from_query()
Downloads and saves into CSV a list of coordinates based on the SQL query written
in the function
Parameters
----------
save_metatable : boolean
When True, save the resulting DataFrame containing meta data into a pickle
When False, don't save
save_coordinates : boolean
When True, save the coordinates to a CSV
When False, don't save
"""
start = time.clock()
if source_type == 'QSO':
query = "select \
spec.z, spec.ra, spec.dec, spec.specObjID, spec.bestObjID, spec.fluxObjID, spec.targetObjID, spec.plate, \
spec.class, spec.subClass, spec.zErr, spho.petroMag_u, spho.petroMag_g, spho.petroMag_r, spho.petroMag_i, \
spho.petroMag_z, spho.petroMagErr_u, spho.petroMagErr_g, spho.petroMagErr_r, spho.petroMagErr_i, spho.petroMagErr_z \
from SpecObjAll AS spec \
JOIN SpecPhotoAll AS spho ON spec.specObjID = spho.specObjID \
where \
spec.zWarning = 0 AND spec.class = 'QSO'"
elif source_type == 'STAR':
query = "select \
spec.z, spec.ra, spec.dec, spec.specObjID, spec.bestObjID, spec.fluxObjID, spec.targetObjID, spec.plate, \
spec.class, spec.subClass, spec.zErr, spho.petroMag_u, spho.petroMag_g, spho.petroMag_r, spho.petroMag_i, \
spho.petroMag_z, spho.petroMagErr_u, spho.petroMagErr_g, spho.petroMagErr_r, spho.petroMagErr_i, spho.petroMagErr_z \
from SpecObjAll AS spec \
JOIN SpecPhotoAll AS spho ON spec.specObjID = spho.specObjID \
where \
spec.zWarning = 0 AND spec.class = 'STAR'"
else:
query = "SELECT\
spec.z, spec.ra, spec.dec, spec.specObjID, spec.bestObjID, spec.fluxObjID, spec.targetObjID, spec.plate, \
spec.class, spec.subClass, spec.zErr, spho.petroMag_u, spho.petroMag_g, spho.petroMag_r, spho.petroMag_i, \
spho.petroMag_z, spho.petroMagErr_u, spho.petroMagErr_g, spho.petroMagErr_r, spho.petroMagErr_i, spho.petroMagErr_z, \
em.Flux_Hb_4861, em.Flux_Hb_4861_Err, em.Amplitude_Hb_4861, em.Amplitude_Hb_4861_Err, \
em.Flux_OIII_4958, em.Flux_OIII_4958_Err, em.Amplitude_OIII_4958, em.Amplitude_OIII_4958_Err, \
em.Flux_OIII_5006, em.Flux_OIII_5006_Err, em.Amplitude_OIII_5006, em.Amplitude_OIII_5006_Err, \
em.Flux_Ha_6562, em.Flux_Ha_6562_Err, em.Amplitude_Ha_6562, em.Amplitude_Ha_6562_Err, \
em.Flux_NII_6547, em.Flux_NII_6547_Err, em.Amplitude_NII_6547, em.Amplitude_NII_6547_Err, \
em.Flux_NII_6583, em.Flux_NII_6583_Err, em.Amplitude_NII_6583, em.Amplitude_NII_6583_Err \
FROM SpecObjAll AS spec \
JOIN SpecPhotoAll AS spho ON spec.specObjID = spho.specObjID \
JOIN emissionLinesPort AS em ON em.specObjID = spec.specObjID \
WHERE \
spec.zWarning = 0 AND spec.class = 'AGN'"
res = SDSS.query_sql(query, timeout=500)
df = res.to_pandas()
print('df', df)
print(df.columns)
# df_coordinate_list = pd.DataFrame(df["ra"])
# df_coordinate_list["dec"]=df["dec"]
# print(f'df_coordinate_list = {df_coordinate_list}')
# print(f'df = {df}')
# if save_coordinates:
# df_coordinate_list.to_csv('data/sdss/star_coordinate_list.csv')
if save_metatable:
df.to_pickle('data/sdss/qso_meta_table_emissionlines.pkl')
end = time.clock()
tt = end - start
print("time consuming:", truncate(tt), 's')
import astropy
from astropy.io import fits, ascii
import numpy as np
from astroquery.sdss import SDSS
from astropy import coordinates as coords
query = "SELECT s.plate, s.fiberid, s.mjd, s.z, s.zwarning, g.nii_6584_flux, g.nii_6584_flux_err, g.h_beta_flux, g.h_beta_flux_err, g.h_alpha_flux, g.h_alpha_flux_err, g.oii_3729_flux, g.oii_3729_flux_err, g.oii_3726_flux, g.oii_3726_flux_err, g.oiii_4959_flux, g.oiii_4959_flux_err, g.oiii_5007_flux, g.oiii_5007_flux_err FROM GalSpecLine AS g JOIN SpecObj AS s ON s.specobjid = g.specobjid WHERE h_alpha_flux > h_alpha_flux_err*5 AND h_beta_flux > h_beta_flux_err*5 AND h_beta_flux_err > 0 AND h_alpha_flux > 10.*h_beta_flux AND s.class = 'GALAXY' AND s.zwarning = 0 "
sample_n = SDSS.query_sql(query)
ascii.write(
sample_n,
output='/Users/users/mulder/astrods/project/sample_trainingset.csv',
format='csv',
overwrite=True) #sample_newlines.csv', format='csv', overwrite=True)
'''
#query = "SELECT s.plate, s.fiberid, s.mjd, s.z, s.zwarning, g.h_beta_flux, g.h_beta_flux_err, g.oii_3729_flux, g.oii_3729_flux_err, g.oii_3726_flux, g.oii_3726_flux_err, g.oiii_4959_flux, g.oiii_4959_flux_err, g.oiii_5007_flux, g.oiii_5007_flux_err FROM GalSpecLine AS g JOIN SpecObj AS s ON s.specobjid = g.specobjid WHERE h_alpha_flux > h_alpha_flux_err*5 AND h_beta_flux > h_beta_flux_err*5 AND h_beta_flux_err > 0 AND h_alpha_flux > 10.*h_beta_flux AND s.class = 'GALAXY' AND s.zwarning = 0 "
#query = "SELECT s.plate, s.fiberid, s.mjd, s.z, s.zwarning, g.h_beta_flux, g.h_beta_flux_err, g.h_alpha_flux, g.h_alpha_flux_err, nii_6584_flux, nii_6584_flux_err, sii_6717_flux, sii_6717_flux_err, oi_6300_flux, oi_6300_flux_err, g.oiii_5007_flux, g.oiii_5007_flux_err FROM GalSpecLine AS g JOIN SpecObj AS s ON s.specobjid = g.specobjid WHERE h_alpha_flux > h_alpha_flux_err*5 AND h_beta_flux > h_beta_flux_err*5 AND h_beta_flux_err > 0 AND h_alpha_flux > 10.*h_beta_flux AND s.class = 'GALAXY' AND s.zwarning = 0 "
#sample_o = SDSS.query_sql(query)
#ascii.write(sample_o, output='/Users/users/mulder/astrods/project/sample_oldlines.csv', format='csv', overwrite=True)
'''
from astroML.plotting import scatter_contour
from astroML.datasets import fetch_sdss_spectrum
from sklearn.naive_bayes import GaussianNB
from sklearn.ensemble import RandomForestClassifier
from sklearn.svm import SVC
query = """SELECT TOP 20000
p.objid,p.ra,p.dec,p.u,p.g,p.r,p.i,p.z, p.run, p.rerun, p.camcol, p.field,
s.specobjid, s.class, s.z as redshift, s.plate, s.mjd, s.fiberid
FROM PhotoObj AS p
JOIN SpecObj AS s ON s.bestobjid = p.objid
WHERE
p.u BETWEEN 0 AND 19.6
AND g BETWEEN 0 AND 20 """
data = SDSS.query_sql(query).to_pandas()
k= data['class'].value_counts()
k1 = data.columns.values
k2 = data.head()
#print(k)
#print(k1)
################################
#plot the spectrum
# Fetch single spectrum
plate = 2645
mjd = 54477
fiber = 007
spec = fetch_sdss_spectrum(plate, mjd, fiber)
#ax = plt.axes()
#Quereying database for galaxies
lon1, lat1 = w.all_pix2world(0, 0, 0)
lon2, lat2 = w.all_pix2world(6132, 8176, 0)
maxlon = max(lon1, lon2)
minlon = min(lon1, lon2)
maxlat = max(lat1, lat2)
minlat = min(lat1, lat2)
query = f"""
SELECT ra,dec
FROM Galaxy
WHERE ra between {minlon} and {maxlon}
AND dec between {minlat} and {maxlat}
AND g < 21
"""
res = SDSS.query_sql(query)
galaxies = res.to_pandas()
c = SkyCoord(ra=dfsel['sky_centroid.ra'] * u.degree,
dec=dfsel['sky_centroid.dec'] * u.degree)
catalog = SkyCoord(ra=galaxies['ra'].to_numpy() * u.degree,
dec=galaxies['dec'].to_numpy() * u.degree)
#Cross referencing SDSS and sources
max_sep = 3.0 * u.arcsec
idx, d2d, d3d = c.match_to_catalog_3d(catalog)
sep_constraint = d2d < max_sep
c_matches = c[sep_constraint]
catalog_matches = catalog[idx[sep_constraint]]
res = SDSS.query_sql(query)
galaxies = res.to_pandas()
def get_star_galaxy_data(N=10000):
query = QUERY.format(N=N)
data = SDSS.query_sql(query)
return data.to_pandas()
def query_object(outfile,
database=None,
querytype="both",
inname=None,
inra=None,
indec=None,
verbose=False,
sdss_dr=15,
sdss_onlyprim=True,
conrad=3,
sdsstable=None,
overwrite=False):
"""
Query an online database for an object either by name or coordinates
(or both) and write the results into an output file. Mostly this is a
subroutine for the query_and_download routine which does the query for a
list of objects/coordinates in a parallelized way. (called by query_and_download)
"""
if not overwrite and os.path.isfile(outfile):
if verbose:
print("File exists! Skipping to next...")
return (0)
# --- first try to find a match by name
if querytype == "both" or querytype == "name":
if verbose:
print(" - Searching by name ...")
try:
if database == "NED":
res = Ned.query_object(inname)
elif database == "SIMBAD":
time.sleep(1)
res = cSimbad.query_object(inname)
# nname +=1
_ = res.colnames
if len(res) == 0:
namefail = True
else:
namefail = False
if verbose:
if namefail:
print(" - object not found by name")
else:
print(" - Found by name: ", res)
except:
namefail = True
if verbose:
print(" - object not found by name")
if querytype == "name":
if verbose:
print("Object not found: ", inname, inra, indec)
return (-1)
# --- if coordinate search or by name fails, try by coordinates:
if querytype == "coordinates" or (querytype == "both" and namefail):
if verbose:
print(" - Searching by coordinates ...")
try:
coord = coordinates.SkyCoord(ra=inra,
dec=indec,
unit=(u.deg, u.deg),
frame='fk5')
if verbose:
print(coord)
if database == "NED":
res = Ned.query_region(coord,
radius=conrad * u.arcsec,
equinox='J2000')
if len(res) == 0:
if verbose:
print(" - Object not found: ", inname, inra, indec)
return (-1)
elif database == "SIMBAD":
time.sleep(1)
res = cSimbad.query_region(coord, radius=conrad * u.arcsec)
print(" query done..")
elif database == "SDSS":
# --- query region only looks for coordinates in the photo
# catalog which for some reason is null for many
# galaxies. Therefore, we have to query by sql
sqlquery = ("SELECT TOP 50 s.ra, s.dec, s.specobjid, s.z, \
s.zErr, s.zWarning, s.sciencePrimary, \
s.class, s.subclass, p.type FROM .." + sdsstable +
" as\
s LEFT JOIN ..PhotoObj AS p ON \
s.bestObjID=p.objID JOIN \
dbo.fGetNearby" + sdsstable + "Eq(" + str(inra) +
"," + str(indec) + "," + str(conrad / 60.0) + ") \
AS b ON b.SpecobjID = S.SpecobjID")
res = SDSS.query_sql(sqlquery, data_release=sdss_dr)
# res = None
if verbose:
print(" - Found by coordinates: ", res)
# return(res)
res = res[np.where(
np.array(res['sciencePrimary']) == 1)[0]]
if verbose:
print(" - Found by coordinates: ", res)
except:
# --- if both fail, object is not in database apparently
# print("ERROR: No object found neither by name nor by coordinates!")
if verbose:
print(" - Object not found: ", inname, inra, indec)
return (-1)
# f.write(res)
if res is not None:
if len(res) > 0:
# return(res)
res.write(outfile,
delimiter=',',
format='ascii',
fill_values=[(ascii.masked, '')],
overwrite=True)
return (1)
def makelist(ra, dec, ccd, date, lname, variable=0., transients=0.):
vname = date + '/var/' + lname + '.var'
tname = date + '/var/' + lname + '.trans'
rname = date + '/reg/' + lname + '.reg'
radec = date + '/radec/' + lname + '.txt'
#Define size of camera in pixels:
xsi = 8176.
ysi = 6132.
#Corresponding size in degs:
wid = xsi * 1.24 / 3600.
hei = ysi * 1.24 / 3600.
#Define the columns to get from UCAC, magnitude filter and set unlimited rows:
v = Vizier(columns=['_RAJ2000', '_DEJ2000', 'Vmag'],
column_filters={"Vmag": "<17."})
v.ROW_LIMIT = -1
#Query UCAC4
ucac = v.query_region(\
coord.SkyCoord(ra=ra, dec=dec,\
unit=(u.deg, u.deg),\
frame='icrs'),\
height=str(hei+1)+"d", width=str(wid+1)+"d",\
catalog=["UCAC4"])[0]
#For SDSS, need to calculate bounding box:
declim = dec + np.array([-hei, hei]) / 2.
declim_r = np.pi * declim / 180.
wid_r = np.pi * wid / 180.
d_lim_r = 2. * np.arcsin(np.sin(wid_r / 4.) / np.cos(declim_r))
ralim = ra + np.array([-1, 1]) * np.max(180. * d_lim_r / np.pi)
#Query SDSS:
query = "SELECT p.objid, p.ra, p.dec, p.g, "+\
"p.deVRad_g, p.deVPhi_g, p.deVAB_g, "+\
"p.type, p.flags_g, (flags & dbo.fPhotoFlags('SATURATED')) as SAT "+\
"FROM PhotoPrimary AS p "+\
"WHERE p.ra BETWEEN "+str(ralim[0])+" AND "+str(ralim[1])+" AND "+\
"p.dec BETWEEN "+str(declim[0])+" AND "+str(declim[1])+" AND "+\
"p.g BETWEEN 16 AND 22"# AND "+\
#"p.htmid*37 & 0x000000000000FFFF < (650 * 10)"
sdss = SDSS.query_sql(query)
#Remove saturated sources and separate gals from stars:
o = np.where(sdss['SAT'] == 0)
sdss = sdss[o]
#Find and remove matching sources:
c = coord.SkyCoord(ra=sdss['ra'] * u.degree, dec=sdss['dec'] * u.degree)
cs = coord.SkyCoord(ra=np.array(ucac['_RAJ2000'])*u.degree, \
dec=np.array(ucac['_DEJ2000'])*u.degree)
idx, d2d, d3d = cs.match_to_catalog_sky(c)
match = np.where(d2d.value * 3600. > 1.0)
ucac = ucac[match]
#Group stars from SDSS and UCAC together:
o = np.where(sdss['type'] == 6)
sdss_stars = sdss[o]
stars = np.append(np.array([ucac['_RAJ2000'], ucac['_DEJ2000'], ucac['Vmag']]),\
np.array([sdss_stars['ra'], sdss_stars['dec'], sdss_stars['g']]),\
axis=1)
#Extract gals from SDSS:
o = np.where(sdss['type'] != 6)
sdss_gals = sdss[o]
gals = np.array([sdss_gals['ra'], sdss_gals['dec'],\
sdss_gals['g'],\
sdss_gals['deVRad_g'], sdss_gals['deVAB_g'], sdss_gals['deVPhi_g']])
#Generate WCS to convert (RA,Dec) to (x,y)
w = wcs.WCS(naxis=2)
w.wcs.crpix = np.array([4088, 3066]) + np.random.normal(scale=3., size=2)
w.wcs.cdelt = np.array([3.444e-4, 3.444e-4])
w.wcs.crval = [ra, dec] #Pointing position of telescope.
w.wcs.ctype = ["RA---TAN", "DEC--TAN"]
pixcrds = np.column_stack((stars[0, :], stars[1, :]))
pixcrdg = np.column_stack((gals[0, :], gals[1, :]))
worldg = w.wcs_world2pix(pixcrdg, 1)
worlds = w.wcs_world2pix(pixcrds, 1)
gals[0, :] = worldg[:, 0]
gals[1, :] = worldg[:, 1]
stars[0, :] = worlds[:, 0]
stars[1, :] = worlds[:, 1]
ind = [-1]
if variable > 0:
#Select a set proportion of m<19 stars and add random variation:
bright = np.squeeze(np.where((stars[2,:]<19) &\
(stars[0,:]>0) & (stars[0,:]<xsi) &\
(stars[1,:]>0) & (stars[1,:]<ysi)))
n_vary = int(np.round(variable * (np.size(bright))))
if n_vary > 0:
ind = np.random.choice(bright, n_vary, replace=False)
orig = stars[2, ind]
stars[2, ind] = stars[2, ind] + np.random.normal(0, 1, ind.size)
np.savetxt(vname, np.c_[pixcrds[ind,0], pixcrds[ind,1], orig, stars[2,ind]], \
header = 'RA Dec Orig_Mag New_Mag', \
fmt='%9.5f %8.5f %5.2f %5.2f')
if transients > 0:
#Randomly distribute transients around image:
tx = np.random.uniform(0, xsi, transients)
ty = np.random.uniform(0, ysi, transients)
tm = np.random.uniform(14, 19, transients)
t = np.array([tx, ty, tm])
stars = np.append(stars, t, axis=1)
txy = (np.array([tx, ty])).transpose()
pixcrdt = w.wcs_pix2world(txy, 1)
#Append transients to stars:
pixcrds = np.append(pixcrds, pixcrdt, axis=0)
np.savetxt(tname, np.c_[pixcrdt[:,0], pixcrdt[:,1], tm], \
header = 'RA Dec Orig_Mag New_Mag', \
fmt='%9.5f %8.5f %5.2f')
#Write stars to file:
myfile = open('templist' + ccd + '.list', 'w')
regfile = open(rname, 'w')
regfile.write('image\n')
obj = np.concatenate(
(100 * np.ones(pixcrds[:, 0].size), 200 * np.ones(pixcrdg[:, 0].size)))
ras = np.concatenate((pixcrds[:, 0], pixcrdg[:, 0]))
decs = np.concatenate((pixcrds[:, 1], pixcrdg[:, 1]))
mags = np.concatenate((stars[2, :], gals[2, :]))
np.savetxt(radec, \
np.c_[obj, ras, decs, mags], \
fmt='%3i %9.5f %8.5f %5.2f', \
header='Type RA DEC Mag')
for i in range(0, (stars.shape)[1]):
myfile.write((str(100) + ' ' + str(stars[0, i]) + ' ' +
str(stars[1, i]) + ' ' + str(stars[2, i])) + '\n')
if np.any(ind == i):
regfile.write('circle(' + str(stars[0, i]) + ',' +
str(stars[1, i]) + ',3) #color=blue\n')
else:
regfile.write('circle(' + str(stars[0, i]) + ',' +
str(stars[1, i]) + ',3)\n')
#Write gals to file:
for i in range(0, (gals.shape)[1]):
myfile.write((str(200)+' '+str(gals[0,i])+' '+str(gals[1,i])+' '+str(gals[2,i])+' ' +\
str(0)+' ' +str(0)+' ' +str(0)+' ' +str(0)+' ' + str(gals[3,i])+' '+\
str(gals[4,i])+' '+str(gals[5,i])+'\n'))
regfile.write('circle(' + str(gals[0, i]) + ',' + str(gals[1, i]) +
',3) #color=red\n')
myfile.close()
regfile.close()
def sdss(srcids, columns='cntr,w1mpro,w2mpro', wsid_column='SDSSID'):
photo = SDSS.query_sql('select top 10 z, ra, dec, bestObjID')
print(photo)
