About Queries:  Tips and Examples

Updated:         June 18, 2010

 

Some examples of simple queries:

 

Here are some simple “select” query examples to get you going. 

 

            Simple select:                select alpha,delta,r from PhotoObj  limit 100

 

            Filtering rows:               select alpha,delta,r from PhotoObj where r < 16 limit 100

 

            Equi-joins:                    select p.alpha,p.delta,p.objid,p.r,a.mag_isor from PhotoObj p, PhotoObjAll a where p.objid=a.objid limit 100

 

            Left-joins:                     select PhotoObjAll a left join PhotoObj p on p.objid=a.objid limit 100

 

            Spatial:                         select alpha,delta from PhotoObj

where alpha > 139.5 and alpha < 139.6 and delta > 30 and delta < 30.1

limit 100

 

            Aggregation:                 select count(*) from PhotoObj

                                                select count(*) from PhotoObj group by subfield

                                                select count(*) from PhotoObj group by left(subfield,2)

                                                select max(alpha) from PhotoObj where subfield=’F2p22’

 

There are a variety of other constructs, including order by, which allows you sort your results (but often at a cost)

 

How to use foreign keys:

 

The PhotoObjAll catalog has one row for each 1 sigma object detection in any of the DLS subfields.   These are not all truly unique objects, since the DLS subfields overlap.  But each row does represent a individual detection in one of the subfields, and each is given a unique object identifier, a bigint primary key field named “objid”.

 

The PhotoObj has been pre-filtered to contain only unique objects, and has also been filtered to remove low signal-to-noise regions and regions around bright stars and galaxies.  PhotoObj does not contain all of the sextractor columns that PhotoObjAll contains, which makes it a good table for performing queries.  However, when you do want to fetch the additional columns from PhotoObjAll, you need to be able to match objects from one catalog to the other.

 

When other tables (such as Colorpro or Spectra) contain data for the same objects which are listed in PhotoObjAll, this equivalence is represented in the database using a field which is by convention also named “objid”.  Such a mapping between a primary and secondary table is called a foreign key in database terminology.  To make the mapping between the primary key and the foreign key field easy for the database, the foreign key relationship is supported with an index on the objid field of both tables.

 

Here is an example of a query using a foreign key relationship:

 

     select p.MAG_AUTOR, c.r, from PhotoObjAll p, Colorpro c where p.objid=c.objid

 

This query finds all records in PhotoObjAll which have a matching record in Colorpro, and displays a table which has the auto R magnitude from the PhotoObjAll table and the r magnitude from the Colorpro table.

 

 

Note on how to use flags:

 

There are several bitmap fields in the DLS database which are useful for cutting out objects which have been determined to be unuseful for one reason or another during image analysis.

 

processflags is a bitmap field in PhotoObjAll which provides information about each object.  In this case, it is information which is gathered during the post-processing of the various catalogs (sextractor, ellipto, colorpro, masks, etc.) which make up the database. 

 

The are other bitmap flags in the individual catalogs which are set during the individual processing steps by sextractor, ellipto, and colorpro.  For example, FLAGSB, FLAGSV, FLAGSR, and FLAGSz in PhotoObjAll are used to indicate error or problem conditions in the sextractor runs, while errcodeeB, errcodeeV, errcodeeR and errcodeez indicate errors or problems in ellipto runs. 

 

If you are not familiar with how to use a bitmap, each of the binary digits of a bitmap is an independent piece of information which has to be extracted using a binary mask.  Bit 0 is the 1’s place, bit 1 is the 2’s place, bit 2 is the 4’s place, etc.  To use the processflags, you must mask out the bits you are interested in, then test the resulting value to see if it is set to 1.

 

            bit                    decimal           hex                              binary

            0                      1                      0x1                  0000000000000001

            1                      2                      0x2                  0000000000000010

            2                      4                      0x4                  0000000000000100

            3                      8                      0x8                  0000000000001000

            4                      16                    0x10                0000000000010000

            5                      32                    0x20                0000000000100000

            6                      64                    0x40                0000000001000000

            7                      128                  0x80                0000000010000000

            8                      256                  0x100              0000000100000000

            9                      512                  0x200              0000001000000000

            10                    1024                0x400              0000010000000000

            11                    2048                0x800              0000100000000000

            12                    4096                0x1000            0001000000000000

            13                    8192                0x2000            0010000000000000

            14                    16384              0x4000            0100000000000000

            15                    32768              0x8000            1000000000000000

 

For example, to test just the border flag, do the following:

 

            Select * from PhotoObjAll where not processflags&32 = 0

or         Select * from PhotoObjAll where not processflags&0x20 = 0

 

To test all of the duplicate flags plus the border flag and the 2009 exclusion regions, sum 1+2+4+16+32 = 47 (0x2f).

 

            Select * from PhotoObjAll where not processflags&0x2f

 

To make the same test with the 2005 exclusion regions, use 31 (0x1f).

 

 

Some sample queries:

 

1.  Simple selection and filtering:

 

            select alpha,delta,mag_isor from PhotoObjAll where mag_isor < 23;

 

2.  Query PhotoObjAll, excluding duplicates and Masked Objects

 

            select TRUNCATE(alpha,6),TRUNCATE(delta,6) from PhotoObjAll

            where processflags&0x1f = 0

 

3.         Join Colorpro and PhotoObjAll table for F3p11 only

 

select .objid,c.alpha,c.delta,c.b,p.mag_isob,c.v,p.mag_isov,

            from DLS2009a.Colorpro c, DLS2009a.PhotoObjAll p

            where c.objid = p.objid  and c.subfield='F3p11';

 

4.  Join Colorpro, PhotoObjAll and Coords (which contains redding information

 

    select c.objid,c.subfield,c.alpha,c.delta,

    c.b,c.b-(s.ebv*s.bebvcoef),

    c.v,c.v-(s.ebv*s.vebvcoef),

    c.r,c.r-(s.ebv*s.rebvcoef),

    c.z,c.z-(s.ebv*s.zebvcoef),

    s.ebv from DLS2009a.Colorpro c, DLS2009a.PhotoObjAll p, DLS2009a.Coords s

                        where c.objid = p.objid  and c.subfield = s.subfield limit 25;