Site Map

Using SEGUE and CASJobs

This page describes the SEGUE data available in the DR8 CAS database, and how to access this data using queries composed in the SQL language. If you would like to access the data using some other system, please go to the Data Access for SDSS DR8 page.

The queries below have information about extracting the "science" data, i.e., the stellar parameters and photometry, but it also includes important information about data quality, selection information, and other things one must consider when using the data for science investigations.

To start, some important definitions for spectroscopic data:

More information about the overall general organizational scheme of SDSS-III spectroscopy is available at the Basics of SDSS Spectrograph Data page.

Below we detail a number of queries that will be useful both for quality control and general data access. To jump to a particular section, use the links below:

A Brief Overview of SQL

SDSSIII has resources for individuals just getting started with SQL. The material described below is particularly useful for use with SEGUE data. However, we highly recommend you use the provided SQL Tutorial if you are not familiar with the language. Complementing the tutorial, SDSSIII has many SQL Sample Queries available.

Note that for the SQL language "---" is a comment, and % works similarly to the * symbol in UNIX.

SEGUE Tables in the DR8 CAS

There are a number of tables in the DR8 CAS which are used to access spectroscopic, and in particular, SEGUE, information. These tables are documented in the schema browser here (select the Tables menu tab). We have listed some of the useful parameters in each of the tables below.

Joining Different Tables

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

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 origobjid 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. Because the photometric table is much larger than the spectroscopic table, most specobjid in the photometry are 0, as there is no associated spectrum. There are a number of entries in the spectroscopic data which have no matching photometry; for them, origobjid is set to 0. This latter case often happens for a "SKY" spectrum, which is pointed at a blank piece of sky, with no star or other imaging object underneath.

With these matching fields in place, one can use a JOIN query in SQL to rapidly match up objects between the two tables. For a very basic example, the parameter origobjid links up the sppParams and PhotoObjAll tables: 

SELECT sp.origobjid, ph.objid as photoobjall_origobjid
FROM sppParams as sp
JOIN PhotoObjAll as ph ON sp.origobjid = 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 included below.

SEGUE 1 and 2 Tables
Data Table PlateX sppParams SpecObjAll PhotoObjAll sppLines ProperMotions SegueTargetAll Star
PlateX PlateID PlateID RA, Dec region Plate
sppParams PlateID specobjid or origobjid origobjid (match with poa.objid) SpecObjId origobjid (match with pm.objid) objid (with sp.origobjid) Objid (with sp.origobjid)
SpecObjAll PlateID specobjid or origobjid origobjid (match with poa.objid) SpecObjId origobjid (match with pm.objid) objid (with soa.origobjid)Objid (with soa.origobjid)
PhotoObjAll RA, Dec region objid (with sp.origobjid) objid (with soa.origobjid) specobjid objid objid objid
sppLines Plate specobjid specobjid specobjid specobjid specobjid
ProperMotions origobjid (with pm.objid) origobjid (with pm.objid) objid specobjid objid objid
SegueTargetAll origobjid (with sta.objid) origobjid (with sta.objid) objid objid objid
sppTargets PlateID origobjid (with spt.objid) origobjid (with spt.objid) objid specobjid objid objid objid
Plate2Target PlateID origobjid (with p2t.objid) origobjid (with p2t.objid) objid objid objid objid
SpecPhotoAll PlateID specobjid, origobjid (with spa.objid) specobjid, origobjid (with spa.objid) objid specobjid objid objid objid
Star origobjid (with st.objid) origobjid (with st.objid) objid specobjid objid objid

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 and some origobjid values in the spectroscopy table set to 0, indicating that there is no spectrum of a photometric object for the former and no imaging object for the latter. There are ways to join parts of two tables which allow for non-matches in certain cases. 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 in addition to any spectroscopic information for targets assigned fibers. If a particular target does not have any spectroscopic information, this query assigns the values -999 to the columns. 

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

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

Useful Functions for SEGUE

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

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

Useful SQL Functions
FunctionDescription
fDMS Converts declination in degrees to +dd:mm:ss.ss notation
fDocColumns Returns information about all of the columns in a particular data table, including data type and descriptions of the parameters
fDocFunctionParams Provides information about the input and output parameters for a specified function
fGetLat Converts a 3-vector to Latitude
fGetLon Converts a 3-vector to Longitude
fGetNearbyObjEq Given an RA and Dec, this function will return a table of all primary photometric objects within a specified distance (in arcminutes) of the point
fGetNearbySpecObjEq Given an RA and Dec, this function will return all primary spectroscopic objects within a specified distance (in arcminutes) of the point
fGetNearestSpecObjIdEqSearches within a specifed radius about a given RA and Dec for the nearest scienceprimary spectroscopic observation
fGetObjectsEq Returns all objects within a specified radius of a given RA and Dec
fGetObjFromRect Returns a table of objects within a rectangle defined by two RA and Dec pairs
fHMS Converts RA from degrees to +hh:mm:ss.ss notation
fSpecDescription Returns a string indicating class, status and zWarning for a given specObj

Downloading the FITS spectra

What if I have a plate, mjd, fiberid of an object of interest and wish to download the actual spectrum in FITS format for further analysis?

The Science Archive Server (SAS) is a portal into all the available DR8 images and spectra in FITS format. The SEGUE and stellar spectra for DR8 are organized into three reruns: rerun 26 which contains plates from the SDSS Legacy survey and SEGUE-1 (plates 182 through 2974), rerun 103 which contains a few special cluster plates, and rerun 104, which contains SEGUE-2 plates (generally plate number > 3000). Rerun 103 reexamined the cluster plates to improve flux calibration. Due to crowded fields or a lack of photometry when the plate was designed, the list of stars used by the SDSS pipeline for flux calibration needed to be edited by hand for these plates. Note that for all of the reruns the actual pipeline was unchanged in any way that would affect the science.

If you know which rerun your plate is in, you may go to the SAS archive for that plate by pointing your browser at: http://data.sdss3.org/sas/dr8/sdss/spectro/redux/$SPRERUN/$PLATE/ Where SPRERUN is 26,103 or 104 and PLATE is the plate number. There you may download the spPlate-$PLATE-$MJD.fits file which contains the 640 spectra associated with that plate. An easy-to-use browser for the SAS spectroscopic database is available here.

Running Queries from the Command Line

Although the CASJobs SQL interface is incredibly useful, if you need to run a large series of queries, it can be useful to send them from the command line. There are two methods of doing this, via Java or Python. Both methods are thoroughly explained at the links.

Quality Cuts

Selecting SEGUE plates on Quality

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.*

Plate TypeS/N constraint
SEGUE-1 Bright>7.5
SEGUE-1 Faint>16
SEGUE-2 >10

*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 are included in DR8. 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:

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. Here are some SQL examples using the parameters explained above:

Remember that even with these criteria, you can still get duplicate observations of individual targets.

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.

Radial Velocity Quality

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.elodiefinalerr > 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.

S/N Quality

There are two S/N measurements made for each spectrum. The first is the sn_median in the SpecObjAll table. This is the median S/N per 1Å pixel in the extracted spectrum.

The second S/N parameter is typically more informative of the stellar spectra. snr in the sppParams table is the S/N per 1Å pixel in the wavelength range from 4000 to 8000 Å, the region typically used by the SSPP for stellar parameter estimation. Note that the SSPP does not have any parameters for stars with S/N < 10.

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

AND sppParams.snr > 10

Avoiding Duplicates

Some objects have multiple spectroscopic observations, either from being an intentional repeat, as a QA target or as part of a different program, or from being on an overlapping plate. To make sure that any query returns one and only one spectroscopic observation of any object, and that it is the best (defined as the highest S/N) observation of that object, the specObjAll table has an entry called sciencePrimary. The criteria for an observation to be sciencePrimary and more general information is available at the SDSS Spectroscopic Catalogs page. Adding the following clause to a query will ensure that it returns a unique set of objects:

SELECT ...
FROM SpecObjAll as sp
WHERE sp.sciencePrimary = 1
AND ....

This same criteria will work for the sppParams table. The PlateX parameters survey and programname are repeated in the SpecObjAll and sppParams tables, making it unnecessary to join PlateX to those tables to use the sciencePrimary selection criteria to specify survey and programname.

If, for any particular reason, you do not want to use the sciencePrimary parameter to eliminate duplicates, you can examine the number of times a particular target appears in CASJobs output by using the count function. For example, to examine the number of times each origobjid value appears in a particular sample, as there will be repeats, one would use the following query: 

SELECT sp.origobjid, count(sp.origobjid) as count
FROM SpecObjAll as sp
group by sp.origobjid

The query above lists every origobjid in the SpecObjAll and the number of times it appears. If you want to avoid any target that is observed multiple times, which will severly limit any sample, you can use the following query: 

SELECT sp.origobjid, count(sp.origobjid) as count
FROM SpecObjAll as sp
group by sp.origobjid
having count(sp.origobjid) = 1

Similarly, altering the query above to read having count(sp.origobjid) > 1 would list every target that is observed multiple times. The query above can be particularly useful as a number of SEGUE-1 targets are reobserved as part of SEGUE-2. Oftentimes, this results in a SEGUE-1 target having its sciencePrimary observation on a SEGUE-2 plate. By examining the duplicates, one can isolate this issue for a particular sample if necessary.

The JOIN below on origobjid extracts targets from sppParams and matches them up with entries from SpecObjAll and PhotoObjAll. It specifically pulls out G dwarf targets that were observed as part of SEGUE-1 and then re-observed in SEGUE-2. This query returns the plate, mjd, and fiberid for the target in both surveys. Additionally, one can compare the radial velocity determined by SEGUE-1 to that from SEGUE-2. Note that the sp.sciencePrimary, which is associated with the SEGUE-2 measurements are all set to 1, whereas so.sciencePrimary, associated with the SEGUE-1 observations, is 0.

One can remove these duplicate SEGUE-1 observations by looking for the objType field in the SpecObjAll table. Note that although there are multiple spectroscopic observations, there is only one photometric match for the targets. 

SELECT top 100
sp.plate, sp.mjd, sp.fiberid, sp.specobjid, sp.origobjid,so.elodiez*3e5+7.3 as rvsegue2,
sp.elodiervfinal as rvsegue1, sp.elodiervfinalerr, sp.teffadop, sp.fehadop, sp.loggadop,
ph.psfmag_g, ph.psfmag_r, ph.psfmagErr_g, ph.psfmagErr_r,
ph.ra, ph.dec, ph.extinction_r, ph.l, ph.b,
so.plate as segue1plate, so.mjd as segue1mjd, so.fiberid as segue1fiberid,
so.survey as segue1survey, so.programname as segue1programname, sp.sciencePrimary, so.sciencePrimary as soSP
FROM sppParams sp
JOIN SpecObjAll as so ON so.origobjid = sp.origobjid AND sp.sciencePrimary = 1
JOIN PhotoObjAll ph ON ph.objid = sp.origobjid
AND (so.segue1_target1 & 262144)!=0
--Could also have used the clause (so.segue1_target1 & 0x40000) != 0
WHERE so.survey = 'segue1'
AND sp.survey = 'segue2'
AND so.programname like 'segue%'
AND elodiervfinalerr > 0

It is critical to identify and account for duplicates in your sample, both from the perspective of avoiding repeats and to ensure a complete sample.

Dive Right in: Some Sample queries

The following SEGUE-specific queries are a good place to get started. Open up a window into DR8 CasJobs or the DR8 CAS SQL and cut and paste these queries to get a sense for how to extract SEGUE spectral parameters for stars.

Many of the queries below potentially return a lot of objects. We have included the phrase top 10 to limit the number of targets returned. You can remove the top 10 phrase after the SELECT statements and CAS/CASJobs will return all objects that satisfy your query WHERE clause, rather than just the first 10 objects. Starting with top 10 until everything appears to be working is a good way to debug complex queries quickly. It can also be used as a quick check to see what columns a particular data set contains.

It is always a good idea to return the plate, MJD, and fiberid parameters for the queries of spectroscopic data tables. It is also very useful to return the specobjid and origobjid for efficient matching to other tables later.

Jump to Sample Queries:

RA and Dec Rectangular Search

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 and as what field.
--They are very 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.origobjid = p.objid
WHERE sp.ra > 180.0 AND sp.ra < 220.0
AND sp.dec > -1.5 AND sp.dec < 1.5
AND sp.sciencePrimary = 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 dr8 Sample SQL Queries,

Two JOINs are required for this very common template query. The clause specobjall.sciencePrimary = 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.origobjid = photoobjall.objid. Approximately 0.5% of spectra provided do not have matching DR8 photometry. Photometry for those objects may be found in DR7 if necessary, as explained below.

The sppParams table has an identical origobjid to that in SpecObjAll for joins to the photometry. In DR7, this join was done via a field called bestobjid. In DR8, bestobjid also exists, but it is a 'best flux match', used for deblended galaxies, and it is not always the best match for stars. Thus, we recommend that for matching between star spectra, corresponding photometry (color, magnitude), and proper motion information, one should use the position match term origobjid from the various spectroscopic tables.

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,
--The parameters above have information about the observation of the particular target, such as when and as what field.
--They are very 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,
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.origobjid = 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.sciencePrimary = 1
AND s.elodiervfinalerr > 0

Missing DR8 photometry

All SEGUE-1 and SEGUE-2 spectra were targeted off of DR7 photometry. The latest DR8 photometry is also available for nearly all objects; however, for a small fraction of fields (about 0.5%), the DR8 run of the photo pipeline timed out before it finished cataloging and deblending objects. This is usually because there is a bright star in the field with scattered light wings that cause the deblender to think especially hard. There are about 12,300 stellar spectra where this is true in DR8. To extract these, search for targets with sppparams.origobjid = 0 and sppparams.elodiervfinalerr > 0, while rejecting sky spectra by excluding objects with sp.objtype = 'SKY' or sp.sectarget != 16.

One can still find the photometry for these objects by looking in the DR7 database and doing a position match. This requires a two stage query, as follows.

1) Select RA, Dec and other spectral parameters of objects with no DR8 photometry and put them into your myDB as a table named mydb.orphandr8spectra: 

SELECT  s.plate,s.mjd,s.fiberid,ra,dec,elodiervfinal,elodiervfinalerr,fehadop,loggadop,
sp.objtype,sp.primtarget,sp.sectarget
INTO mydb.orphandr8spectra
FROM sppparams s,specobjall sp
WHERE s.specobjid = sp.specobjid
AND s.origobjid = 0
AND s.scienceprimary =1
AND elodiervfinalerr > 0
AND sp.objtype != 'SKY'
AND sp.sectarget != 16

2) Use the RA, Dec of these objects and the built-in function fGetNearestObjIdAllEq to get the DR7 objid of these objects: 

SELECT top 10
s.run,s.rerun,s.camcol,s.field,s.obj,
s.ra as pra,s.dec as pdec,s.psfmag_g,s.psfmag_r,m.*
FROM mydb.orphandr8spectra m, dr7.photoobjall s
WHERE dbo.fGetNearestObjIdAllEq(m.RA,m.DEC,0.03) = s.objid

The function above uses a radius of 0.03 arcminutes (= 1.8 arcseconds). This value is large enough to allow for slight differences in position, but small enough to avoid mismatches. More information about this function is available in the Built In Functions section.

Running this query (esp. with the top 10 removed) in the DR7 context rather than the DR8 context makes the query run significantly faster, and this is recommended for queries which access only DR7 (and MyDB) information. This query can find photometric information for about 5,456 of the 12,338 objects missing DR8 photometry.

Extracting Plate Information by Programname

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:

  • segue
  • segpointed
  • seglowlat
  • segue2
  • segtest
  • segcluster
  • seguefaint
  • segpointedf
  • seglowlatf

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 DR8 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%'

Extracting Specific Stellar Categories

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 and SEGUE-2. However, this will not get you every single target that passes the category criteria. We detail how to extract these different samples below.

The SQL query detailed below will extract every single stellar target that was assigned a fiber for a particular spectral category. This query does not account for the fact that, in SEGUE-1 especially, 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.

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 (run in a DR8 context): 

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 third 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 sciencePrimary=1 observation. In that case, demanding that sciencePrimary=1 in the above query will remove objects from your sample that you intend to include.

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. This bitmask is set once the survey was completed, using the latest target selection criteria. 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.origobjid = 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 sciencePrimary can potentially remove objects from your sample, so be conscientious!

The SEGUE target bitmasks are set for all objects in the PhotoObjAll 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, but only using PhotoObjAll and SegueTargetAll. 

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

Extracting Stellar Parameters

The queries below demonstrate how to access spectroscopic catalog information for the unique and best observation of stars in a certain range of Teff, [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.origobjid,
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
AND sp.sciencePrimary = 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.origobjid,
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.sciencePrimary = 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.origobjid,
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.sciencePrimary = 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

Color and Magnitude Cuts, with Join

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 origobjid 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.origobjid,
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.origobjid = ph.objid
WHERE sp.sciencePrimary = 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

Extract Photometric Targets for a Particular Plate

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.

Copyright © 2010-2013 SDSS-III