SEGUE SQL Cookbook
The best way to extract information for a large number of SEGUE targets is through the Catalog Archive Server (CAS). This interface allows the user to extract photometric and spectroscopic information, in addition to estimates of stellar parameters from the SSPP. If you would like to access the data using some other system, please go to the Data Access for SDSS page. If you are looking for more basic information about SEGUE, please go to the Getting Started with SEGUE page.
Using the SQL language, one can extract information about individual targets, lines-of-sight, stellar categories, and more from a range of different SDSS data sets. Below we give an overview of the SEGUE CAS information and how to access it using SQL. We also have included queries for ensuring data quality, which must be considered when using the data for science investigations.
SEGUE Tables in the Catalog Archive Server
There are a number of tables in the CAS which are used to access photometric and spectroscopic information. These tables are documented in the Schema Browser (select the Tables menu tab). All of these table can be linked together in various ways using SQL queries. We have listed some of the tables that are particularly useful for SEGUE below.
- For all spectra, plate, mjd, fiber, ra, dec
- [Fe/H], log g, Teff, Radial Velocity for all stellar objects
- Equivalent widths for common lines in stars
- Photometric information for all stars
- Subset (View) of PhotoObjAll table with all Primary Stellar photometric detections.
- Target selection flags for all photometric objects
- Matches plates to targets observed on that line of sight
- Proper motions of the photometry
- pre-determined match between SpecObjAll and PhotoObjAll (matched on flux, not position)
- Table of all plates, plate centers in RA, Dec, programnames
- Spatial cross-match between DR7 and DR8/DR9 Spectroscopy
- Spatial cross-match between DR7 and DR8/DR9 Photometry
Before getting started, here are some important definitions for working with the spectroscopic data:
- An object is a star or galaxy at a particular location on the sky. It may have multiple spectroscopic observations and thus, have multiple entries in the spectroscopic catalog tables (like SpecObjAll, sppParams). It will only have one listing in the imaging (PhotoObjAll).
- An observation is a spectrum of an object taken on a particular plate-MJD in a particular fiberid. It is defined by a specobjid.
- A plate-MJD combination is an observation of a plate, and is identified by a plateid.
- All of the spectroscopic observations are matched to one and only one object in the photometric catalog. The objid of that photometric catalog object is in the bestobjid parameter of the spectroscopic catalog tables.
More information about the overall general organizational scheme of SDSS spectroscopy is available at the Basics of SDSS Spectrograph Data page.
The Basics of SQL
SDSS has resources for individuals just getting started with SQL. While the material below will get you started, it is focuses specifically on extracting SEGUE data. If you are new to the CAS and SQL, we highly recommend you use the SQL Tutorial and SDSS SQL Sample Queries.
All SQL queries can be run from the command line, rather than the web version of the CAS. This is particularly useful if you need to run a large series of queries. There are two methods of doing this, via Java or Python. Both methods are thoroughly explained at the provided links.
Also note that for the SQL language “–” is a comment, and % works similarly to the * symbol in UNIX.
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 SDSS Function Browser.
Below we list a few of the functions which are particularly useful with SEGUE data and a brief description of them.
|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
|fGetNearestSpecObjIdEq||Searches within a specified radius about a given RA and Dec for the nearest scienceprimary
|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|
Joining Different Tables
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.
|PlateX||PlateID||PlateID||RA, Dec region||Plate|
|sppParams||PlateID||specobjid or bestobjid||bestobjid (match with poa.objid)||SpecObjId||bestobjid (match with pm.objid)||objid (with sp.bestobjid)||Objid (with sp.bestobjid)|
|SpecObjAll||PlateID||specobjid or bestobjid||bestobjid (match with poa.objid)||SpecObjId||bestobjid (match with pm.objid)||objid (with soa.bestobjid)||Objid (with soa.bestobjid)|
|PhotoObjAll||RA, Dec region||objid (with sp.bestobjid)||objid (with soa.bestobjid)||specobjid||objid||objid||objid|
|ProperMotions||bestobjid (with pm.objid)||bestobjid (with pm.objid)||objid||specobjid||objid||objid|
|SegueTargetAll||bestobjid (with sta.objid)||bestobjid (with sta.objid)||objid||objid||objid|
|Plate2Target||PlateID||bestobjid (with p2t.objid)||bestobjid (with p2t.objid)||objid||objid||objid||objid|
|SpecPhotoAll||PlateID||specobjid, bestobjid (with spa.objid)||specobjid, bestobjid (with spa.objid)||objid||specobjid||objid||objid||objid|
|Star||bestobjid (with st.objid)||bestobjid (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 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.
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.* Plates with low signal-to-noise or other issues will be listed as “bad.”
|Plate Type||S/N constraint|
*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:
- 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.
- 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.
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.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.
Dive Right in: Some Sample queries
The following SEGUE-specific queries are a good place to get started. Open up a window into CasJobs or the 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.
The queries below are designed to be run in the DR13 context of the CAS. However, if you have a query that uses only tables from previous data releases or data in your myDB context, running the queries there, rather than in the DR13 context, can be quicker.
Finally, it is always a good idea to return the plate, MJD, fiberid,
specobjid, and bestobjid parameters for the
queries of spectroscopic data tables 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 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
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:
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%'
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. 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 (run in a DR13 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 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
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.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
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 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
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.