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
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 1s place, bit 1 is the 2s place, bit 2 is the 4s 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
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
s.ebv from DLS2009a.Colorpro c, DLS2009a.PhotoObjAll p, DLS2009a.Coords s
where c.objid = p.objid and c.subfield = s.subfield limit 25;