Say I have a medium number of objects (10s to ~1M) where I know RA/dec and expnum and want more information for each of these objects. I think my approach in the question will fill out a table that will help me. You can comment if this approach makes sense for small (<100 objects), medium (~100K). For 100M objects, it might be best to download the whole Y3A1_FINALCUT_OBJECT and do my own matching. In principle, since I got RA/dec and expnum, I can ask the person who made my list to include the object_number and then I'd expect the matching query on object_number to go much faster than the RA/dec match. But I don't have the object_number, so assume that I don't.


Specifically, I have a list of actual TNO observations in DES data and want to add columns like:

o.flags,z.flag as zflag,round(z.mag_zero-2.5*log(10,o.flux_psf),4) as mag_psf,round(1.08574*(fluxerr_psf/flux_psf),4) as magerr_psf, z.version, o.spread_model, o.spreaderr_model, o.a_image, o.b_image, o.theta_image, ... (other quantities)

from y3a1_finalcut_object o, y3a1_image i,y3a1_exposure e,y3a1_catalog c, y3a1_zeropoint z, y3a1_qa_summary q where o.filename = c.filename and c.expnum = z.expnum and c.ccdnum = z.ccdnum and z.imagename = i.filename and c.expnum = e.expnum and c.expnum = q.expnum and z.version='v2.0' and (RA/dec and expnum matching).


Here's a short list of 8 TNO observations: RA, dec, expnum













    CommentAdd your comment...

    2 answers


      As an update on STEP 4 in my first response (below).  If the input table has image filename(s) the query can be sped up by a factor of ~60 (since it can search the catalogs that result from a specific CCD rather than over the entire focal plane.  That query looks like the following:


      create table tmp_match as
      select it.id,o.filename,o.object_number,c.ccdnum,o.ra-15.*it.ra as dra, o.dec-it.dec as ddec
      from y3a1_finalcut_object o, y3a1_image i, y3a1_catalog c, gruendl.my_input_table it
      where it.filename=i.filename
          and i.pfw_attempt_id=c.pfw_attempt_id
          and i.ccdnum=c.ccdnum
          and c.filetype='cat_finalcut'
          and c.filename=o.filename
          and (o.ra-15.*it.ra) between -0.0002778*cos(it.dec*0.017453) and 0.0002778*cos(it.dec*0.017453)
          and (o.dec-it.dec) between -0.0002778 and 0.0002778;
        CommentAdd your comment...

        If I understand your case correctly here is what I would do:

        Step 1:  Create table.  For example,

        create table my_great_table (
        	id  number(8),
        	ra number(9,6),
        	dec number(9,6),
        	expnum number(8),


        Step 2: Populate portion of the table you already know (either by direct inserts, easyaccess (load), your own fabulous code (and e.g. despydb.desdbi).

        insert into my_great_table (id,ra,dec,expnum) values (1,1.675117571,-3.080487198,503010);


        Step 3: Depending on the scale perhaps add an index on a useful column here or there.

        create index idx_mgt_expnum on my_great_table(expnum);


        Step 4: Getting a set of matches so you can obtain further information.

         In your example you have expnum... if you happen to have a ccdnum and are willing to constrain the result to come from a tag then you would already be there.  However, assuming you don't then you need to perform some sort of matching with an object table (e.g., SE_OBJECT or in this case Y3A1_FINALCUT_OBJECT).  In this table the primary key is a combination of FILENAME and OBJECT_NUMBER (this means each record can be uniquely identified with those two columns).  Since we have an exposure number constraint this will run a lot faster than if I had to match against all items therefore I have chosen a simple route of matching against all sources within 1 arc second:

        I have added a couple of other things so that I might have some way to quickly discern how good a match might be... then I added CCDNUM since it might come in handy later (other useful things I could have grabbed are PFW_ATTEMPT_ID and BAND)

        create table tmp_match as
        select i.id,
        	o.ra-15.*i.ra as dra, 
        	o.dec-i.dec as ddec
        from y3a1_finalcut_object o, y3a1_catalog c, y3a1_proctag t, gruendl.my_input_table i
        where i.expnum=c.expnum
            and c.pfw_attempt_id=t.pfw_attempt_id
            and t.tag='Y3A1_FINALCUT'
            and c.filetype='cat_finalcut'
            and c.filename=o.filename
            and (o.ra-15.*i.ra) between -0.0002778*cos(i.dec*0.017453) and 0.0002778*cos(i.dec*0.017453)
            and (o.dec-i.dec) between -0.0002778 and 0.0002778;

        Note:  I usually develop something like this without the create table portion since I would need to drop the results each time as I refine the query.

        Also note: it turns out that William's RA's are in units of time (i.e. RA in hours rather than degrees so there are factors of 15. scattered through the calculation).


        Just to makes sure I only got one match per object I ran the following:

        with x as (select id ,count(object_number) as cnt from tmp_match group by id) 
        select x.id from x where x.cnt > 1;


        Step 5: Pull together other information

        create table my_great_table as
        select i.id,
        from y3a1_finalcut_object o, y3a1_exposure e, y3a1_catalog c,
        	y3a1_qa_summary q, my_input_table i, tmp_match m
        where i.id=m.id
        	and m.filename=o.filename
        	and m.object_number=o.object_number
        	and m.filename=c.filename
        	and c.expnum=e.expnum
        	and c.pfw_attempt_id=q.pfw_attempt_id;


        Voila! ?!?!

          CommentAdd your comment...