Long queries

Question

Hi,

I would like to retrieve a catalogue of all galaxies in the LS survey that are not flagged as stars in GAIA (the query is at the end of the mail). I get time-out errors because the query is too long. Is it possible to run such queries or does a crossmatched ls_dr8.tractor ls_dr8.photo_z table exist for download?

Cheers,

Steve

##########

SELECT
p.ra, p.dec,
p.dered_mag_g, p.dered_mag_r, p.dered_mag_w1, p.dered_mag_w2, p.dered_mag_z,
p.snr_g, p.snr_r, p.snr_z, p.snr_W1, p.snr_W2,
p.galdepth_g, p.galdepth_r, p.galdepth_z, p.psfdepth_w1, p.psfdepth_w2,
p.ebv,
photo_z.z_phot_median, photo_z.z_phot_l68, photo_z.z_phot_l95, photo_z.z_spec

FROM ls_dr8.tractor as p, ls_dr8.photo_z photo_z

WHERE

gaia_pointsource=0 AND
p.ls_id=photo_z.ls_id AND
photo_z.z_phot_median > 0

Answer 1

Hi Steve,

this is a big query... Our DB estimates it will generate ~1.2B rows.

This is certainly too long for a synchroneous query (they time
out after 300s, but can be increased up to 600s with the 'timeout'
parameter).

I was to suggest you run this as an asynchroneous query, but then tried myself, and this crashes our VMs.
Can you try to reduce the result set by applying any more constrains?

But finally, to get the data you want, we suggest that as a
workaround you loop smaller queries over, e.g., RA. You could for
instance query in slices of delta RA = 5 deg, like this:
(if you still get timeouts, make the slices even smaller!)

from dl import authClient as ac, queryClient as qc, storeClient as sc
from getpass import getpass

token = ac.login(input('User name: [+ENTER]'),getpass('Password: [+ENTER]'))

query_template = """SELECT
p.ra, p.dec,
p.dered_mag_g, p.dered_mag_r, p.dered_mag_w1, p.dered_mag_w2, p.dered_mag_z,
p.snr_g, p.snr_r, p.snr_z, p.snr_W1, p.snr_W2,
p.galdepth_g, p.galdepth_r, p.galdepth_z, p.psfdepth_w1, p.psfdepth_w2,
p.ebv,
photo_z.z_phot_median, photo_z.z_phot_l68, photo_z.z_phot_l95, photo_z.z_spec
FROM ls_dr8.tractor as p, ls_dr8.photo_z photo_z
WHERE
gaia_pointsource=0 AND
p.ls_id=photo_z.ls_id AND
photo_z.z_phot_median > 0 AND
p.ra>%f AND p.ra<=%f"""

segments = list(range(0,360+1,5)) # slices of 5 deg in RA

sc.mkdir('vos://out1')  # make a dir in your VOspace for the result files

for ramin,ramax in list(zip(segments[:-1],segments[1:])):
    outname = 'stevetable_ramin%03d_ramax%03d.csv' % (ramin,ramax)
    print('Querying with RA between %f and %f' % (ramin,ramax))
    query = query_template % (ramin,ramax)
    print(query)
    aux = qc.query(sql=query_template,timeout=600,out='vos://out1/'+outname)

This will put the output csv files into your vospace. If you want
to download them later, with the datalab command line client
installed on your local machine via

pip install --ignore-installed --no-cache-dir noaodatalab

and being logged in via

datalab login
# enter your DL username and password

you can download the files (it will be several hundred GB!)

cd targetdir
datalab get fr=vos://out1/* verbose=True

Hope this helps somewhat. Let us know if you run into trouble.

Cheers,
Robert

Comments

Addendum: I ran the first RA slice from RA 0 to 5, and only 41K out of 28M rows have z_spec != -99. Looks like you could cut down the size of the result set considerably.

---

Hi Robert,

Thanks for your quick response and your detailed instructions. :) I didn't know that the LS catalog is so huge. This would make it difficult to process the catalog on my desktop to look for the host galaxies of extragalactic transients. I made a simpler query to look for individual objects.

Cheers,

Steve