SEGUE SQL Cookbook

There are a number of useful functions included in CASJobs. A list of these functions with brief explanations and instructions is available in the SDSS Function Browser.

Below we list a few of the functions which are particularly useful with SEGUE data and a brief description of them.

The JOIN statement is the key to combining information from multiple data tables. Suppose one has a database with two tables:

• sppParams with parameters for all spectroscopically-observed stars
• PhotoObjAll with parameters for all photometrically-observed targets

One wishes to match up the two tables to obtain both photometric and spectroscopic data for all stars, merged into a single one-row-per-star output file. Normally, a match is done on RA and Dec, requiring that photometric and spectroscopic objects have positions within 1 arcsec or so of each other. However, this two dimensional match can be very time consuming. One can "pre-match" objects to save time.

Every spectroscopic object has a unique id, a 64-bit integer, composed of its triplet component ids, i.e., plate-mjd-fiberid. This number is called the specobjid. Every photometric object also has a unique identification, also a 64-bit integer, composed of its heptuple components (run, rerun, camcol, field, obj, firstfield, skyversion). This is called objid in PhotoObjAll.

A matching procedure was run in the database after initial loading which sought to match up every spectroscopic object with a photometric object via RA and Dec position. If a match was found then the parameter bestobjid in the spectroscopic table was filled in with the objid value from the photometric table. Similarly, the specobjid parameter in the photometric tables was filled in with the specobjid from the spectroscopic table. This provides a shortcut to matching up the photometric and spectroscopic information for a star. Not all of the stars in the photometry are observed spectroscopically; most specobjid in PhotoObjAll are set to 0, as there is no associated spectrum.

Using specobjid and bestobjid, one can use a JOIN query in SQL to rapidly match up objects between the two tables. For a very basic example, the parameter bestobjid links up the sppParams and PhotoObjAll tables:

SELECT sp.bestobjid, ph.objid as photoobjall_bestobjid
FROM sppParams as sp
JOIN PhotoObjAll as ph ON sp.bestobjid = ph.objid

The query above is much faster than matching objects by RA and Dec, which can take on the order of days to weeks for a similar query. Note that if you fail to include the ON condition in the JOIN statement, CASJobs will try to do a cross product, attempting to return all of the approximately 540 trillion objects made up of photometry and spectroscopy matched in every possible combination. This will quickly fill up all of your myDB disk space before it even comes close to completing. Do not use any JOIN statements without the ON clause statement to prevent a runaway.

Most of the tables have various ways to link up to the others. A handy crib sheet for the major tables is below.

Not all of the tables connect easily. Sometimes, it will require connecting them through another table. Also note that this chart is just to get you started using JOINs. ALWAYS verify the database matching yourself.

More Intricate Joins

As mentioned earlier, there are some specobjid values in the photometry tables set to 0, indicating that there is no spectrum of a photometric object. A standard JOIN between the spectroscopic and photometric tables will only return information on stars which have both spectroscopy and photometry. However, there are ways to join tables which will include stars that may not appear in both tables. These are called LEFT OUTER JOIN and are more advanced SQL. For example, suppose you want all of the photometric data within a particular region, regardless of whether or not the target was observed spectroscopically. The query below pulls out all of the photometry and spectroscopy for a line of sight; if a particular target does not have spectroscopic information, this query assigns the values -999 to the columns.

SELECT
ph.objid,isnull(sppobj.specobjid,-999) as specobjid
FROM PhotoObjAll as ph
LEFT OUTER JOIN
(SELECT
soa.*
FROM SpecObjAll as soa
JOIN PhotoObjAll as ph on soa.bestobjid = ph.objid)
AS sppobj
ON sppobj.bestobjid = ph.objid

More information about these advanced JOINs is available on the SDSS SQL Sample Queries page.

PlateX contains information about the data quality of every SEGUE plate in the parameter plateQuality. This parameter was determined by checking the S/N of stars with g-r color of old MS turnoff at g=18.* Plates with low signal-to-noise or other issues will be listed as "bad."

*Note that the faint limit for SEGUE-1 bright plates is r < 17.8.

These quality constraints ensure that all SEGUE-1 plates in DR7 and DR8 are included in DR9. The information used to determine the plateQuality parameter is also included in PlateX, under the heading snturnoff. Additionally, the number of stars used to calculate the S/N for each plate is under the heading nturnoff.

In addition to the plateQuality parameter, the quality information is distilled into two other PlateX table elements:

isBest

set to 1 for the highest S/N observation (a plate-mjd combination) of each plate. It is 0 for all other
observations of the plate.

isPrimary

set to 1 for a plate-mjd combination if it passes the S/N criteria used for plateQuality and is the highest
S/N observation of the plate.

An important thing that isBest and isPrimary do is choose one and only one observation of a plate. Note that if objects are targeted on multiple plates they will still be present in a sample selected using criteria on isBest and isPrimary. More information about determining the plate quality is available here.

• To get a unique list of all plates in the "main" SEGUE-2 survey (i.e., not cluster plates), always picking the
  highest S/N observation of each plate:

  SELECT *
  FROM platex px
  WHERE px.survey = 'segue2'
  AND px.programname LIKE '%segue%'
  AND px.isPrimary = 1
  

• To get all the SEGUE-1 and SEGUE-2 "main" plates that were placed at random on the sky:

  SELECT *
  FROM platex px
  WHERE (px.survey = 'segue2' OR px.survey = 'segue1')
  AND px.programname LIKE '%segue%'
  AND px.isPrimary = 1
  

• The query above can also be written:

  SELECT *
  FROM platex px
  WHERE px.survey LIKE 'segue%'
  AND px.programname LIKE '%segue%'
  AND px.isPrimary = 1
  

• If you determine that the placement of the plates does not bias a particular target selection category in which you are interested, you could select from the SEGUE-1 pointed plates, too:

  SELECT * FROM platex px
  WHERE px.survey LIKE 'segue%'
  AND (px.programname LIKE '%segue%' OR px.programname LIKE 'segpointed%')
  AND px.isPrimary = 1
  

You can also examine the plateQuality parameter for individual plates to see if you want to impose your own criteria. Keep in mind that the plates were observed to a fixed S/N criteria, so the variation near the magnitude limit where the plateQuality is evaluated is not large. It is probably more useful to return the value of the S/N for each individual object in your queries and select on that. The S/N quality section below expands on this. Remember that even with specifying the plate quality, you can still get duplicate observations of individual targets.

For radial velocities of stars, use the parameter elodiervfinal in the sppParams data table. To ensure that the targets have well-determined radial velocities, add the following two clauses to any query:

AND sppParams.elodiervfinalerr > 0
AND (sppParams.zwarning = 0 OR sppParams.zwarning = 16)

The parameter zwarning is a warning flag about the velocity determination. The clause above ensures that this aspect of the reduction was normal.

Spectra with higher signal-to-noise will tend to have better estimates of spectroscopic parameters. At a minimum, the SSPP requires a S/N of at least 10 to estimate parameters.

There are two S/N measurements made for each spectrum. The first is the snMedian in the SpecObjAll table. This is the median S/N per 1Å pixel in the extracted spectrum. The second S/N parameter is snr in the sppParams table. It is the mean S/N per 1Å pixel in the wavelength range from 4000 to 8000 Å, the region used by the SSPP for stellar parameter estimation. snr is typically used to extract quality spectra.

Adding the following clause to any query will set a limit on the S/N of the returned targets:

AND sppParams.snr > 10

The SSPP has a number of different flags that indicate anomalies in stellar spectra, listed in sppParams.flag. Even if a spectrum has a high S/N, it may still not be a high-quality observation. The warnings are divided into two categories, cautionary and critical flags. For a critical flag, such as a star with emission lines in the spectrum, the SSPP will not report estimated parameters. When a spectrum has a cautionary flag, such as a large discrepancy between the SSPP-predicted and observed color, the SSPP provides estimates of stellar parameters. It is up to the user to decide which of the various cautionary flags are unacceptable for their science. The different flags are detailed here.

We have listed some useful criteria for isolating a quality SEGUE sample. However, one must also take into account other caveats in the SEGUE sample. First of all, not all SEGUE spectra have associated photometry. Second, there are a number of stars that were observed multiple times in SEGUE. To achieve a complete sample, both of these must be taken into account. Finally, there are some known anomalies in the SSPP, which will affect both sample completeness and estimates of stellar atmospheric parameters. These, in addition to general SDSS spectroscopy caveats must be taken into account.

To get spectroscopic catalog information for stars in a range of RA and Dec, returning only the unique and best observation of each:

SELECT top 10
sp.plate, sp.mjd, sp.fiberid,
p.run,p.rerun,p.camcol,p.field,p.obj,
--The parameters above have information about the observation of the particular target,
--such as when it was processed.
--They are useful for organization and matching different datasets.
s.elodiervfinal,s.elodiervfinalerr,
s.teffadop, s.fehadop, s.fehadopunc,s.loggadop,
sp.ra, sp.dec,
p.psfmag_g,p.psfmag_g-p.extinction_g as g0,
p.psfmag_u-p.psfmag_g as umg,
p.psfmag_u-p.psfmag_g-p.extinction_u+p.extinction_g as umg0,
p.psfmag_g-p.psfmag_r as gmr,
p.psfmag_g-p.psfmag_r-p.extinction_g+p.extinction_r as gmr0
FROM sppParams as s
JOIN SpecObjAll as sp ON s.specobjid = sp.specobjid
JOIN PhotoObjAll as p ON sp.bestobjid = p.objid
WHERE sp.ra > 180.0 AND sp.ra < 220.0
AND sp.dec > -1.5 AND sp.dec < 1.5
AND sp.seguePrimary = 1
AND s.elodiervfinalerr > 0

The above basic query joins together the table of all spectra, SpecObjAll, with the table of stellar spectroscopic parameters (radial velocity, metallicity, surface temperature, surface gravity), sppParams, and photometry for the related imaging (g magnitude, colors, de-reddened colors) from the table PhotoObjAll. JOIN statements are briefly described here and on the SDSS Sample SQL Queries,

Two JOINs are required for this very common template query. The clause specobjall.seguePrimary = 1 results in a unique list of spectra (without duplicates), and the clause sppParams.elodiervfinalerr > 0 ensures that we have a spectrum of a star and not a 'noise object' such as a sky fiber. The connection to the photometric information (from PhotoObjAll) is done by requiring specobjall.bestobjid = photoobjall.objid. Approximately 0.5% of spectra provided do not have matching SDSS photometry. Photometry for those objects may be found in DR7 if necessary, as explained in Missing SDSS Photometry.

Variant with Proper motion match

Here is a variant on the above query which also returns proper motion information for each object. This requires an additional JOIN to the ProperMotions table.

SELECT top 10
sp.plate, sp.mjd, sp.fiberid,
p.run,p.rerun,p.camcol,p.field,p.obj,
s.elodiervfinal,s.elodiervfinalerr,
s.teffadop, s.fehadop, s.fehadopunc,s.loggadop,
sp.ra, sp.dec,
p.psfmag_g,p.psfmag_g-p.extinction_g as g0,
p.psfmag_u-p.psfmag_g as umg,
p.psfmag_u-p.psfmag_g-p.extinction_u+p.extinction_g as umg0,
p.psfmag_g-p.psfmag_r as gmr,
p.psfmag_g-p.psfmag_r-p.extinction_g+p.extinction_r as gmr0,
pm.pmra,pm.pmdec,pm.pmraerr,pm.pmdecerr,pm.pml,pm.pmb,pm.match,pm.delta,pm.O,pm.E
FROM sppParams as s
JOIN SpecObjAll as sp ON s.specobjid = sp.specobjid
JOIN PhotoObjAll as p ON sp.bestobjid = p.objid
JOIN ProperMotions as pm ON p.objid = pm.objid
WHERE sp.ra > 180.0 AND sp.ra < 220.0
AND sp.dec > -1.5 AND sp.dec < 1.5
AND sp.seguePrimary = 1
AND s.elodiervfinalerr > 0

The SEGUE survey consists of both a number of surveys (namely SEGUE-1 and SEGUE-2) which in turn encompass a number of observing programs, such as a focus on open and globular clusters or low latitudes. These are detailed in the Surveys and Programs in SEGUE page.

The PlateX table has details on individual plates, from the basics, like RA and Dec, to more intricate information, such as the programname. The following describes some of the useful information in this table and how to use it to your advantage. The survey parameter in PlateX varies depending on why a particular plate was observed. For most SEGUE plates, survey will be segue1, segue2, or sdss. For later data releases, it could also be boss, marvels, or apogee. In addition, every plate is also labeled with programname. This parameter provides information about the location and purpose for a particular plate, i.e., what aspect of SEGUE was the plate observed for? The programnames are listed below:

By selecting on survey and programname it is possible to identify any desired subset of the SEGUE plates.The query below returns the plate, MJD, plateid, and the RA, Dec of the plate center for all of the cluster plates taken in either SEGUE-1 or SEGUE-2 in a DR9 context:

SELECT px.plate, px.programname, px.plateid, px.ra, px.dec
FROM platex as px
WHERE (px.survey = 'segue1' OR px.survey = 'segue2')
AND px.programname LIKE 'segcluster%'

Just as you can use the PlateX dataset to extract all plates from a particular program, you can also use various parameters to extract all targets within a particular spectral category. The various target categories, and their different criteria, are detailed for each of the surveys on the Target Selection page. The organizational scheme for these parameters is described in the Bit Guide.

Extracting Stars via Fiber Assignment

Each target type is allotted a certain amount of fibers per plate. Using CASJobs, you can ask for the spectroscopic catalog information for all objects that were assigned to fibers as belonging to a particular target selection category in SEGUE-1. However, this will not get you every single target that passes the category criteria. We detail how to extract these different samples below. Selecting stars by fiber assignment is not an option for SEGUE-2 targets.

The SQL query detailed below will extract every single stellar target that was assigned a fiber for a particular spectral category in SEGUE1. This query does not account for the fact that, the target selection criteria for several of the categories evolved over the course of the survey. A particular star may have been targeted using early criteria, but would not have been observed using the latest selection algorithms.

For SEGUE-1, the values in the SpecObjAll and sppParams target selection bitmasks are based on the criteria as they were at the time the plate was designed. The target selection versions for individual plates are listed at the SEGUE Plates page. Detailed information on the changing criteria is available at the Previous Versions page. This intricacy is also detailed in Yanny et al. (2009). Accounting for this nuance while looking at the targets by fiber category may require removing some plates from your survey sample to ensure homogeneous target selection.

Given the above caveats, here is how to select the spectroscopic catalog information for all objects that were targeted as, for example, the SEGUE-1 low metallicity candidates, and take objects only from those plates in the SEGUE-1 survey:

SELECT top 10
sp.*
FROM sppParams as sp
WHERE (sp.segue1_target1 & 0x10000) != 0
AND sp.survey = 'segue1'
AND sp.programname like 'segue%'

The above query will also return all the duplicate observations of each object, too, if they were given fibers on more than one plate in SEGUE-1 as low metallicity candidates.

This brings up a SEGUE subtlety. Some of the targets that were assigned SEGUE-1 fibers for a particular target category may have also been observed in other surveys or programs, and the observation in the other program may be the seguePrimary=1 observation. In that case, demanding that seguePrimary=1 in the above query will remove objects from your sample with a better observation on a SEGUE-2 plate that you wish to include. Setting segue1primary=1 will select the best spectroscopic observation from SEGUE-1 plates. The target will be included, but it may not be the best SEGUE observation of it. Be careful!

Extracting Stars via Stellar Type Criteria

It is possible to ask for the spectroscopic catalog information for all objects that pass the target selection criteria for a particular category, regardless of why it was actually assigned a fiber. This information is in the target selection bitmasks of the segueTargetAll table; the specObjAll and sppParams table also have this information for the SEGUE-2 targets. This bitmask is set once the survey was completed, using the latest target selection criteria on the latest photometry and astrometry. Below is a query that asks for the catalog information for all objects that passed the SEGUE-1 low metallicity target selection criteria, independent of why it was assigned a fiber. This requires a JOIN on the segueTargetAll table. Here, as an example of double JOINs, we also extract data from the PhotoObjAll table.

SELECT top 10
sp.segue1_target1, t.segue1_target1 as seg1_targ1, ph.psfmag_g
FROM sppParams sp
JOIN PhotoObjAll ph on sp.bestobjid = ph.objid
JOIN SegueTargetAll t on ph.objid = t.objid
WHERE (t.segue1_target1 & 0x10000) != 0
AND sp.survey = 'segue1'
AND sp.programname like 'segue%'

Whereas with the fiber assignment query, every target had segue1_target1 of -2147418112, with this new query one can see that this parameter varies. Not every star that meets the low-metallicity criteria was assigned a fiber for this category.
In addition, a particular target can fulfill the criteria for multiple spectral types, resulting in it having multiple bits set. Specifying that (t.segue1_target1 & 0x10000) != 0 will return all objects with that bit set, including objects that have other target bits set as well.

As with the query based on fiber assignment, some of the targets here may have been observed as part of other surveys or programs. Combining criteria on survey, programname, and seguePrimary can potentially remove objects from your sample, so be conscientious!
The SEGUE target bitmasks are set for all objects in the SegueTargetAll table, not just spectroscopic targets. Thus, it is possible to get the entire sample of photometric candidates for each category for each plate using a similar query to that above.

SELECT top 10
t.segue1_target1, count(t.segue1_target1)
FROM SegueTargetAll t
JOIN PhotoObjAll ph on ph.objid = t.objid
WHERE (t.segue1_target1 & 0x10000) != 0
group by t.segue1_target1

The queries below demonstrate how to access spectroscopic catalog information for the unique and best observation of stars in a certain range of T_eff, [Fe/H], and log(g). When the SSPP is unable to calculate a particular stellar parameter for a target, due to things such as a substandard S/N, the placeholder in the data table is -9999. Thus, we specify the parameter ranges to avoid these undetermined parameters.

SELECT top 100
sp.plate, sp.mjd, sp.fiberid, sp.specobjid, sp.bestobjid,
sp.elodiervfinal, sp.teffadop, sp.fehadop, sp.loggadop
FROM sppParams as sp
WHERE sp.teffadop between 5500 and 6500
AND sp.fehadop < -1
---This next clause avoids observations with no [Fe/H] determination
AND sp.fehadop > -5
---This restricts the selection to stars with surface gravities within the range of Giants.
AND sp.loggadop < 3
---This next clause avoids observations with no log(g) determination
AND sp.loggadop > 0
---Finally, avoid duplicate spectra
AND sp.seguePrimary = 1

We can expand the query listed above by adding in quality checks on radial velocities and S/N. Additionally, the query below selects only the spectra taken as part of the "main" SEGUE-1 and SEGUE-2 surveys:

SELECT top 100
sp.plate, sp.mjd, sp.fiberid, sp.specobjid, sp.bestobjid,
sp.elodiervfinal, sp.teffadop, sp.fehadop, sp.loggadop
FROM sppParams as sp
WHERE sp.teffadop between 5500 and 6500
AND sp.fehadop < -1
---This next clause avoids observations with no [Fe/H] determination
AND sp.fehadop > -5
AND sp.loggadop < 3
---This next clause avoids observations with no log(g) determination
AND sp.loggadop > 0
AND sp.seguePrimary = 1
AND sp.survey LIKE 'segue%'
AND sp.programname LIKE 'segue%'
---The next clause avoids targets which have warning flags about velocity/redshift determination
AND (sp.zwarning = 0 OR sp.zwarning = 16)
AND sp.elodiervfinalerr != 0
and sp.snr > 35

Alternatively, one can use BETWEEN statements to replace some of the conditional statements used above:

SELECT top 100
sp.plate, sp.mjd, sp.fiberid, sp.specobjid, sp.bestobjid,
sp.elodiervfinal, sp.teffadop, sp.fehadop, sp.loggadop
FROM sppParams as sp
WHERE sp.teffadop between 5500 and 6500
AND sp.fehadop between -5 and -1
AND sp.loggadop between 0 and 3
AND sp.seguePrimary = 1
AND sp.survey LIKE 'segue%'
AND sp.programname LIKE 'segue%'
---The next clause avoids targets which have warning flags about velocity/redshift determination
AND (sp.zwarning = 0 OR sp.zwarning = 16)
AND sp.elodiervfinalerr != 0
AND sp.snr > 35

This query extracts spectroscopic data which meets specified color and magnitude criteria. This requires matching the entry for each spectroscopic observation in the sppParams table to an entry for that same object in the PhotoObjAll table that contains the photometric catalog information. This can be done using bestobjid in the SpecObjAll and sppParams table, as explained by the section on Joins.

SELECT top 10
sp.plate, sp.mjd, sp.fiberid, sp.specobjid, sp.bestobjid,
sp.elodiervfinal, sp.teffadop, sp.fehadop, sp.loggadop,
ph.psfmag_g, ph.psfmag_r, ph.psfmag_i,
ph.psfmagErr_g, ph.psfmagErr_r, ph.psfmagErr_i
FROM sppParams sp
JOIN PhotoObjAll ph ON sp.bestobjid = ph.objid
WHERE sp.seguePrimary = 1
AND ph.psfmag_g-ph.psfmag_r > 0.5
AND ph.psfmag_g-ph.psfmag_r < 1.0
AND ph.psfmag_g < 20.0
AND sp.snr > 10.0
AND elodiervfinalerr > 0

The PhotoObjAll table does not have information of what spectroscopic plate it overlaps with. To extract all photometric targets within a particular plate region, you must use RA and Dec parameters with fGetNearbyObjEq, a provided SQL function. First lookup the plate center of the plate you are interested in (say SEGUE-1 plate 1880)

SELECT ra,dec
FROM platex
WHERE plate=1880

Then select all objects within 1.5 degrees of the plate center (90 arcmin) which are stellar and have magnitudes within the range for spectroscopy:

SELECT top 100
plmatch.*, ph.objid as poa_objid, ph.psfmag_g, ph.psfmag_r
FROM fGetNearbyObjEq(358.2639, 36.40135,90) plmatch
JOIN PhotoObjAll ph on ph.objid = plmatch.objid
--Ensure the targets are stars and spectroscopically accessible with statement below--
WHERE plmatch.type = 6
AND (ph.psfmag_g < 21 or ph.psfmag_z < 21)

If you would like to look at the parameters for only a particular target type, alter the query as follows:

SELECT top 100
plmatch.*, ph.objid as poa_objid, ph.psfmag_g, ph.psfmag_r
FROM fGetNearbyObjEq(358.2639, 36.40135,90) plmatch
JOIN PhotoObjAll ph on ph.objid = plmatch.objid
JOIN SegueTargetAll st on st.objid = ph.objid
--Ensure the targets are stars with statement below--
WHERE plmatch.type = 6
AND (ph.psfmag_g < 21 OR ph.psfmag_z < 21)
AND (st.segue1_target1 & 0x40000) != 0

The above query will do the trick nicely for all targets that meet the criteria of G dwarfs. Using the different bitmasks, you can isolate individual target types as desired.