Queries to Isolate the Complete SEGUE-1 G and K dwarf Sample
Introduction
This multi-part query extracts the best spectroscopic observation of all targets in SEGUE-1 that meet the criteria of a G or K dwarf. For some of these targets, the best version of the spectrum is from SEGUE-2. Below, we include each component of the SQL query and a brief description of what it does.
Query 1
Using the bitmasks from segueTargetAll, this extracts information for all targets that meet the criteria of a G-dwarf, regardless of whether or not they also fulfill the criteria of other SEGUE categories. It also ensures that the spectroscopic observation will be science quality. The resulting table will have duplicate spectra (e.g., multiple specobjid for a single bestobjid.
SELECT --any information you need from the various tables INTO mydb.query1 FROM SpecObjAll as so JOIN PlateX as px on so.plateid = px.plateid JOIN sppParams as sp on sp.bestobjid = so.bestobjid JOIN segueTargetAll as sta on so.bestobjid = sta.objid JOIN PhotoObjAll as poa on so.bestobjid = poa.objid JOIN ProperMotions as pm on poa.objid = pm.objid WHERE so.programname like '%segue%' --The above statement ensures that all targets are pulled from --either the segue or seguefaint programs AND so.survey = 'segue1' --This specifies that all targets must be from the SEGUE-1 survey --as SEGUE-2 does not explicitly target these categories AND (sta.segue1_target1 & 0x40000) != 0 --Photometry must meet the criteria of a G-dwarf --For K dwarf stars, replace 0x40000 with 0x8000 AND px.isprimary = 1 --Requires the plate to be science quality AND sp.scienceprimary = 1 --Requires the observation to be science quality ORDER BY sp.bestobjid --Organizes the resulting sample by bestobjid
Query 2
Using the bestobjid for each photometric target, we then make a list of all of the unique targets from the table produced in Query 1.
SELECT bestobjid, count(bestobjid) as count INTO mydb.query2 FROM mydb.query1 GROUP BY bestobjid HAVING count(bestobjid) = 1
Query 3
This query uses the list made in Query 2 to pull out the data from Query 1 for all unique targets.
SELECT q1.* INTO mydb.query3 FROM mydb.query1 as q1 JOIN mydb.query2 as q2 on q1.bestobjid=q2.bestobjid
Query 4
We then consider the duplicate spectra, making a list of all bestobjid with more than one specobjid. There is only one row for each target in the resulting data set.
SELECT bestobjid, count(bestobjid) as count INTO mydb.query4 FROM mydb.query1 GROUP BY bestobjid HAVING count(bestobjid) > 1
Query 5
Extract all of the information from the Query 1 table for each of the duplicate observations. One bestobjid will have multiple rows of data in this file.
SELECT q1.* INTO mydb.query5 FROM mydb.query1 as q1 JOIN mydb.query4 q4 on q4.bestobjid = q1.bestobjid ORDER BY q1.bestobjid
Query 6
To get the best sample of stars from the duplicate spectra, we pull out all of the observations on the bright plates (programname is segue rather than seguefaint). There will still be some duplicates from geometric overlaps.
SELECT * FROM mydb.query5 INTO mydb.query6 WHERE programname = 'segue' ORDER BY bestobjid
Query 7
This goes through the data table from Query 6 and lists the bestobjid for all targets that now only show up once (e.g., eliminates the geometric overlap duplicates).
SELECT bestobjid, count(bestobjid) INTO mydb.query7 FROM mydb.query6 GROUP BY bestobjid HAVING count(bestobjid) = 1
Query 8
Query 8 creates a table for all of the segue plate observations for the targets that were obsered multiple times. It removes the targets which are duplicates due to geometric overlaps.
SELECT q7.* INTO mydb.query8 FROM mydb.query6 as q6 JOIN mydb.query7 as q7 on q7.bestobjid = q6.bestobjid
Query 9
This query pulls out the bestobjid for all geometric overlap duplicate spectra. For example, the same line of sight observed on plates 2042/2062 was studied by plate 2043/2063. Also, plates 2175/2178 line up with 2186/2189.
SELECT q6.bestobjid, count(bestobjid) INTO mydb.query9 FROM mydb.query6 as q6 WHERE q6.plate=2042 OR q6.plate=2043 OR q6.plate=2175 OR q6.plate=2178 OR q6.plate=2062 OR q6.plate=2063 OR q6.plate=2186 OR q6.plate=2189 GROUP BY q6.bestobjid HAVING count(bestobjid)>1
Query 10
For all of the geometric overlap duplicates, extracts only the spectroscopic observation FROM the brightest plates (2042 and 2175).
SELECT q6.* INTO mydb.query10 FROM mydb.query6 as q6 JOIN mydb.query9 q9 on q9.bestobjid = q6.bestobjid WHERE q6.plate=2042 OR q6.plate=2175
Final Step
Download the resulting tables from Query 3 (data for all stars with only one spectroscopic observation), Query 8 (data from the bright plate for all duplicates except geometric overlaps), and Query 10 (bright data for geometric overlap duplicates). These three combined make a complete G-dwarf sample, with the best, unique observation of each target that meets the SEGUE selection criteria.