Project

General

Profile

Db-queries » History » Version 26

« Previous - Version 26/33 (diff) - Next » - Current version
Eric Suchyta, 10/25/2014 09:56 AM


Example database queries

This page is for DES scientist to share their SQL queries to the DB, so others can learn from what they've found to work. Different users will want to do similar, perhaps non-trivial queries, and there's no need for everyone to reinvent the wheel. If you've used a query in one of your papers, it'd be good to put it here so it can be easily reproduced.

List existing tables and their owners

select 
   owner, 
   table_name 
from 
   all_tables

Locate images "near a coordinate on the sky"

SELECT 
   l.exposurename, 
   i.imagename, 
   r.run
FROM 
   image i, 
   run r, 
   location l, 
   runtag rt, 
   exposure e 
WHERE 
   l.run=r.run and 
   i.run=r.run and 
   rt.run=r.run and 
   l.id=i.id and 
   i.imagetype='red' and 
   i.exposureid=e.id and 
   rt.tag='SVA1_FINALCUT'and 
   i.band='i' and 
   i.ra >= '342.2' and 
   i.ra <= '342.5' and 
   i.dec >= '-44.5' and 
   i.dec <= '-44.2'

The run, exposure, and image can then be used to fetch the files from the image server, e.g.:

wget --no-check-certificate https://desar2.cosmology.illinois.edu:/DESFiles/desardata/OPS/red/20130717213138_20121124/red/DECam_00155253/DECam_00155253_22.fits.fz  -O testfile.fits.fz

NGMix queries courtesy of Erin Sheldon

im3shape queries courtesy of Joe Zuntz

Eric Suchyta's queries