 |
|
|
||||
|
|
|
|
||||
|
This page contains a sample queries designed to serve as templates for writing your own SQL queries. The first section, Basic SQL, serves as an introduction to the syntax of the SQL database access language. The sections that follow feature queries written to solve real scientific problems submitted by astronomers. Those queries are divided by scientific topic.
Click on the name of the query in the list below to go directly to that
sample query. You can load the query into SkyServer's
SQL Query tool by clicking
on the NOTE: Please read the query hints below before you try any queries, especially if you are new to SQL or the SkyServer. Some hints on searching SkyServer:
Basic SELECT-FROM-WHERE | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
-- This is the "Hello world" example of how to search for data in DR8. -- This query shows the basic structure of a SQL query: -- SELECT [variables] FROM [table] WHERE [constraints] -- Although many of your SQL queries will be more complex, -- they will all follow this same basic structure. -- This sample query finds unique objects in an RA/Dec box. -- For a more efficient way to find objects by position, see the next query, -- Searching around a sky position. | |
| SELECT TOP 100 | |
| | -- Get the unique object ID and coordinates |
| FROM | |
| | -- From the table containing photometric data for unique objects |
| WHERE | |
| | |
| | -- that matches our criteria |
|
-- Find galaxies within 1' of a given point (ra=185.0, dec=-0.5). -- This is a slightly more complex query, but it can be easily adapted to search -- around any point in the sky. -- To see how to limit the search only to objects with clean photometry, see the -- Clean imaging query. | |||
| SELECT TOP 100 G.objID, GN.distance | |||
| FROM Galaxy as G | |||
| JOIN dbo.fGetNearbyObjEq(185.,-0.5, 1) AS GN | |||
| ORDER BY distance
| | | |
|
-- This query introduces the PhotoTag table, which contains the most frequently used columns -- of PhotoObj. Queries to PhotoTag will run more quickly than those to photoObj. -- This sample query finds data for all objects in fields with desired PSF width. | |
| SELECT TOP 100 | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| FROM | |
| | |
| | |
| | |
| WHERE mode=1 -- select primary objects only | |
| | |
| | |
| | |
| | |
|
-- The BETWEEN statement can be used to set constraints on a range of values.
-- This sample query finds galaxies with g magnitudes between 18 and 19.
| |
| SELECT TOP 10 objID, cModelMag_g | |
| FROM Galaxy | |
| WHERE | |
| | -- 18 < cModelMag_g < 19 |
| | |
|
-- There are several built-in functions available to CAS users that make spatial
-- queries, i.e., those with coordinate cuts, much more efficient than simply -- including the coordinate constraints in the WHERE clause. This example -- illustrates the use of the dbo.fGetObjFromRectEq function that invokes -- the Hierarchical Triangular Mesh (HTM) functionality. -- a) Rectangular search using straight coordinate constraints: SELECT objID, ra, dec FROM PhotoObj WHERE (ra between 179.5 and 182.3) and (dec between -1.0 and 1.8) -- b) This query can be rewritten as follows to use the HTM function that returns a -- rectangular search area (The "Run this Query" button above will run this query): SELECT p.objID, p.ra, p.dec FROM PhotoObj p |
|
-- When you need to search for data in two or more tables, you must "join" the tables -- together for the purposes of your query by using a JOIN...ON statement. -- JOIN...ON allows you to search for data and/or constraints in both tables. -- The syntax of the statement is: -- JOIN [the second table] ON [a variable the tables have in common]. -- The variable the tables have in common is called the "key" (think of it -- as the key that unlocks your ability to search two tables). -- The key variable(s) in each table are shown on the About the Database page. -- Find the two tables you want to join and look for a key variable they have in common. -- The sample query looks for spectra of quasars and shows the date and time at which -- each spectrum was taken. | |
| SELECT TOP 100 | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| FROM specPhoto AS sp | |
| JOIN plateX AS px | |
| | |
| WHERE | |
| | |
| | |
| | |
|
-- This query introduces the SpecPhoto view, which is a pre-computed join -- of the most commonly-searched fields in both the SpecObj view (which -- contains spectroscopy data) and the PhotoObj view (which -- contains photometry data). This means that to combine -- photometry and spectroscopy tables yourself using JOIN...ON, -- the join already has been "precomputed" and done for you. -- You should use specPhoto whenever your variables of interest -- can be found there; using specPhoto means that your queries will -- return results much faster than using JOIN...ON. -- that your queries will return results much faster than using JOIN...ON. -- This sample query retrieves both magnitudes (from photometry) and -- redshifts (from spectroscopy) of 100 quasars. Note that this query -- also introduces the use of mathematical operators (in this case subtraction) -- in queries. SELECT top 100 FROM SpecPhoto WHERE |
|
-- This simple query introduces two important SQL structures while showing how to count the number of -- spectra of each spectral classification (galaxy, quasar, star) in DR8. -- When included in the SELECT block, the count(*) statement returns the number of objects that -- meet your search criteria. -- The GROUP BY statement sorts results into groups (categories) based on the value of a data column. -- In this query, the grouping occurs on the 'class' column of the SpecObj view, which contains -- the spectral classification of the object. -- The query can be easily modified to find object counts sorted by other columns. -- For another example, see the Counts by Type and Program sample query. SELECT class, count(*) FROM SpecObj GROUP BY class |
|
-- Another useful query is to select stars that are not saturated. -- This query introduces bitwise logic for flags, and uses the 'as' syntax to -- make the query more readable. Note that if a flag is not set, the value -- will be zero. If you want to ensure multiple flags are not set, you can -- either check that each individually is zero, or their sum is zero. -- (From Gordon Richards) -- Important caveat: The 'flags' column is the union of the flag values in -- each band,and using it may produce unexpected results, for example if a -- bad flag bit is set in only one band (e.g. in the 'flags_r' value), that bit -- will be bad in the overall 'flags' value as well. For more information, -- please see the 'Photometric flags detail' entry in Algorithms. -- NOTE: This query takes a long time to run without the "TOP 1000". | |
| SELECT TOP 1000 | |
| | |
| | |
| | |
| | |
| | |
| FROM Galaxy | |
| WHERE | |
| | |
| | |
|
-- Give me the colors of a random 1% sample of objects from all fields that -- are "survey quality" so that I could plot up color-color diagrams and play -- around with more sophisticated cuts. Query requested by Karl Glazebrook. -- Uses the HTM spatial index ID as a random number generator. The htmID is -- multiplied by a prime number (37) to remove bias and generate a random number -- from the lowermost 16 bits. This number is then constrained to be between 650 -- and 65000 to generate a random sample between 1 and 100% of the data -- respectively. So replacing the "1" by a different number between 1 and 99 -- will generate a sample of the required percentage of objects. -- Note that this is much faster than the standard "ORDER BY RANDOM()" SQL method. | |
| SELECT TOP 100 u, g, r, i, z FROM Galaxy | |
| WHERE | |
| | |
| | -- Replace the "1" with a number < 99 to get a higher percentage data subsample |
| | |
|
-- This query introduces the Neighbors table, which contains each -- object's neighbors within 30 arcseconds. -- Find all objects within 30 arcseconds of one another -- that have very similar colors: that is where the color ratios -- u-g, g-r, r-I are less than 0.05m. | |
| SELECT TOP 10 P.ObjID | -- distinct cases |
| FROM PhotoPrimary AS P | -- P is the primary object |
| | -- N is the neighbor link |
| | |
| -- L is the lens candidate of P | |
| WHERE | |
| | -- avoid duplicates |
| | -- L and P have similar spectra. |
| | |
| | |
| | |
| | |
|
-- A more complex query that also uses the Neighbors table. -- This is a query from Robert Lupton that finds selected neighbors in a given run and -- camera column. It contains a nested query containing a join, and a join with the -- results of the nested query to select only those neighbors from the list that meet -- certain criteria. The nested queries are required because the Neighbors table does -- not contain all the parameters for the neighbor objects. This query also contains -- examples of setting the output precision of columns with STR. SELECT TOP 10 FROM WHERE |
|
-- Using object counting and logic in a query. -- Find all objects similar to the colors of a quasar at 5.5 | ||||||||||
| SELECT count(*) as 'total', | ||||||||||
| | | FROM PhotoPrimary -- for each object
| WHERE (( u - g > 2.0) or (u > 22.3) ) -- apply the quasar color cut.
| | | | | | | |
|
-- Create a count of galaxies for each of the HTM triangles. -- Galaxies should satisfy a certain color cut, like -- 0.7u-0.5g-0.2i<1.25 && r<21.75, output it in a form -- adequate for visualization. | |||||||||
| SELECT (htmID / power(2,24)) as htm_8 , | |||||||||
| | | | FROM Galaxy -- only look at galaxies
| WHERE (0.7*u - 0.5*g - 0.2*i) < 1.25 -- meeting this color cut
| | group by (htmID /power(2,24)) -- group into 8-deep HTM buckets.
| | | |
|
-- Compare different redshift measurements of the same object for objects
-- with high redshift SELECT prim.bestObjId, prim.mjd AS PrimMJD, prim.plate AS PrimPlate, other.mjd AS OtherMJD, other.plate AS OtherPlate, prim.z AS PrimZ, other.z AS OtherZ, plate.programname FROM SpecObjAll prim JOIN SpecObjAll other ON prim.bestObjId = other.bestObjId JOIN platex AS plate ON other.plate = plate.plate AND other.mjd = plate.mjd WHERE other.bestObjId > 0 AND prim.sciencePrimary = 1 AND other.sciencePrimary = 0 AND prim.z > 2.5 ORDER BY prim.bestObjId |
|
-- The flag fields in the SpecObjAll table are 64-bit, but some -- analysis tools (and FITS format) only accept 32-bit integers. -- Here is a way to split them up, using bitmasks to extract -- the higher and lower 32 bits, and dividing by a power of -- 2 to shift bits to the right (since there is no bit shift -- operator in SQL.) The hexadecimal version can be used for debugging -- to make sure you are splitting them up right. | ||
| SELECT TOP 10 objid,ra,dec, | ||
| -- output the whole bigint as a check | ||
| -- get the lower 32 bits with a mask | ||
| FROM PhotoObj | ||
| -- Hexadecimal version of above query - use for debugging ("Run this Query" button will run this). | ||
| SELECT TOP 10 objid,ra,dec, | ||
| FROM PhotoObj | ||
| | ||
|
-- This query demonstrates the use of a LEFT OUTER JOIN to include rows from the joined table -- that do not satisfy the JOIN condition. The OUTER JOIN (LEFT or RIGHT) -- is an inclusive JOIN, whereas the INNER (default) JOIN is an exclusive JOIN. -- In this case the query returns galaxies with or without spectra that meet the criteria -- in the WHERE clause. The ISNULL operator is used to replace null -- values with zeros for galaxies without spectra. SELECT TOP 10 p.objid, p.ra, p.dec, ISNULL(s.specobjid,0) AS specobjid, ISNULL(s.z, 0) AS z FROM Galaxy AS p WHERE |
|
-- This query demonstrates, among other tings, the use of a nested query (a query -- inside another query) in which the outer query operates on the result of the inner query. The -- query also gets the sky brighness and turns it into a flux, which illustrates the use of -- the POWER() function and CAST to change the string representation into floating -- point. The First table contains matches between SDSS and FIRST survey objects. SELECT TOP 10 fld.run, fld.avg_sky_muJy, fld.runarea AS area, ISNULL(fp.nfirstmatch,0) FROM ( --first part: for each run, get total area and average sky brightness ) AS fld LEFT OUTER JOIN ( -- second part: for each run,get total number of FIRST matches. To get the run number -- for each FIRST match, need to join FIRST with PHOTOPRIMARY. Some runs may have -- 0 FIRST matches, so these runs will not appear in the result set of this subquery. -- But we want to keep all runs from the first query in the final result, hence -- we need a LEFT OUTER JOIN between the first and the second query. -- The LEFT OUTER JOIN returns all the rows from the first subquery and matches -- with the corresponding rows from the second query. Where the second query -- has no corresponding row, a NULL is returned. The ISNULL() function in the -- SELECT above converts this NULL into a 0 to say "0 FIRST matches in this run". ) AS fp ON fld.run=fp.run ORDER BY fld.run |
|
-- SkyServer includes views called Star and Galaxy, which -- contain photometric data (but no spectral data such as -- redshift) for stars and galaxies respectively. -- This sample query shows how you can search for data -- for galaxies using the Galaxy view. Searching for stars -- using the Star view would work the same, but with -- "Star" in the FROM clause. -- This sample query finds 1000 galaxies -- brighter thanr-magnitude 22, extinction-corrected. -- You could remove the "TOP 1000" to find all galaxies brighter -- than r=22, but it would take a long time to run and might -- time out. | |
| SELECT TOP 1000 objID | |
| FROM Galaxy | |
| WHERE | |
| | -- extinction-corrected r magnitude |
|
-- One of the most important requirements for selecting data -- useful for research is to select only data that has passed -- a certain standard of clean photometry. Starting with DR8, -- the SDSS-III has made this easier by providing a flag called CLEAN -- that indicates that an object's photometry has passed all tests -- to bedeclared useful for science. A value of CLEAN=1 (true) -- indicates that that object's photometry has been vetted as clean. -- For more on using flags, see the Using Flags Sample Query. -- In some contexts, it is important to know what conditions -- are required for theimaging pipeline to set the CLEAN flag, -- so that you can adjust these requirements to suit your needs. -- The CLEAN flag works differently for stars and galaxies. For stars, -- see the Clean photometry - stars sample query. For galaxies, -- see the Clean photometry - galaxies sample query. SELECT top 10 objID, ra, dec, g, clean FROM PhotoObj WHERE CLEAN=1 | |
|
-- This sample query allows you to find the MJD (astronomical date) -- on which a specific SDSS-III field was observed. (This query uses -- the r-band, but you can specify other magnitude bands. -- It also returns only fields observed after MJD 53140 (May 15, 2004). -- There is an online calculator for converting between MJDs and -- calendar dates. -- This query uses the Field table, which contains data on SDSS fields. -- It is also possible to search for the observation date of a specific -- object by joining the Field table with the PhotoObj table or its -- associated views. SELECT top 100 FROM Field WHERE |
|
-- DR8 handles searches by spectral lines differently than previous
-- releases. In addition, spectral lines for galaxies and stars are -- identified through different processes. -- Spectral lines for galaxies are calculated using the MPA-JHU -- spectroscopic reanalysis (Tremonti et al. 2004; Brinchmann et al. 2004) -- and are stored in the galSpecLine table. For more on how spectral lines -- of galaxies are found, see the Galspec page of the sdss3.org website. -- Spectral lines for stars are calculated using the SEGUE Stellar Parameter -- Pipeline (SSPP; Lee et al. 2008) and are stored in the sppLines -- table. For more on how spectral lines of stars are found, see -- the SSPP page of the sdss3.org website. -- The "Run this Query" button will run the second query, which finds stars -- by searching CaII lines. -- a) Finding galaxies by their emission lines: -- This query selects galaxy spectra with high internal reddening, -- as measured by the standard Balmer decrement technique. It -- makes use of the galSpec tables for the measurements of -- galaxy lines. In this case we use galSpecLine, which has -- emission line measurements. SELECT FROM GalSpecLine AS g JOIN SpecObj AS s WHERE -- b) This query selects red stars (spectral type K), with -- large CaII triplet eq widths, with low errors on the CaII triplet -- equivalent widths. SELECT sp.loggadopn,sp.loggadopunc,sp.loggadopn FROM sppLines AS sl JOIN sppParams AS sp WHERE |
|
-- This sample query find all objects with spectra -- classified as stars. The query also checks that zWarning has no bits set, -- meaning that there are no known problems with the spectra. -- Other possible values with of class are 'QSO', 'GALAXY' and 'UNKNOWN'. | |
| SELECT TOP 100 specObjID | |
| FROM SpecObj | |
| WHERE class = 'STAR' AND zWarning = 0 | |
|
-- Provide a list of moving objects consistent with an asteroid. -- This sample query uses the 'as' syntax, which allows you to -- give your own names to columns in the results. -- This query is an example of a situation where you must -- search the full PhotoObj view, since the -- columns rowv and colv are not in PhotoTag. | |
| SELECT TOP 10 | |
| | |
| | |
| FROM PhotoObj | |
| WHERE | |
| | |
| | |
| | |
|
-- A query to list plates that have objects in common. -- Returns the pairs of plates, the total number of nights -- on which the objects they have in common have been observed, the progam to -- which the special plate belongs, and the number of objects the plates -- have in common. SELECT first.plate, other.plate, FROM SpecObjAll first WHERE first.scienceprimary = 1 AND other.scienceprimary = 0 GROUP BY first.plate, other.plate, otherPlate.programname ORDER BY nightsObserved DESC, otherPlate.programname, |
|
-- Find galaxies that are blended with a star, and output the -- deblended galaxy magnitudes. -- This query introduces the use of multiple tables or views with a table JOIN clause. -- You can assign nicknames to tables as in the FROM clause below. Since you are using -- multiple tables, you must specify which table each quantity in the SELECT clause -- comes from. The "ON -- condition between the two tables, which is achieved by requiring that a quantity -- present in both tables be equal. | |
| SELECT TOP 10 G.ObjID, G.u, G.g, G.r, G.i, G.z | -- get the ObjID and final mags |
| FROM Galaxy AS G | -- use two Views, Galaxy and Star, as a |
| | -- convenient way to compare objects |
| | -- JOIN condition: star has same parent |
| WHERE G.parentID > 0 | -- galaxy has a "parent", which tells us this |
| -- object was deblended | |
| | |
|
-- List the number of each type of object observed by each -- special spectroscopic observation program. SELECT plate.programname, class, FROM SpecObjAll GROUP BY plate.programname, class ORDER BY plate.programname, class |
|
-- This query uses the OUTER APPLY construct to apply a -- table-valued function to the results of a query. -- ( See also example below of checking whether a single point is in the SDSS footprint.) -- In this example, we use the fFootPrintEq function which returns a -- a non-NULL value ("POLYGON") if the area specified by the RA, dec and -- radius is inside the SDSS footprint. -- For each point in the input list, in this case the result of a query -- on PhotoObj, return "yes" or "no" depending on whether the point is in -- the SDSS footprint or not, along with any other needed columns. -- Note that this is really a trivial example since every point in PhotoObj -- is already in the SDSS footprint. To be really meaningful, a query like -- this needs to be run on a list of RA, decs that may or may not be in the -- in the SDSS footprint. This can be done using a MyDB table in CasJobs -- containing the list of points, for example, or by uploading a list in -- the Object Crossid tool. SELECT top 100 objID, ra, dec, FROM PhotoObj AS p WHERE (ra BETWEEN 179.5 AND 182.3) AND (dec BETWEEN -1.0 AND 1.8) | |
-- Checking the footprint for a single point with the fInFootprintEq scalar function. SELECT dbo.fInFootprintEq(180, -0.5, 0.2) -- Or if you have a table containing ra,dec values (for example a MyDB table -- in CasJobs), you can do something lile: SELECT dbo.fInFootprintEq(t.ra, t.dec, 0.1) FROM MyTable_10 AS t |
|
-- The Clean Photometry sample query above showed how -- to select only objects for which SDSS-III imaging pipeline has declared -- the photometry clean. Usually, it is sufficient to search only the CLEAN flag; -- but in some contexts, it is important to know what conditions -- are required for the CLEAN flag to be set. The imaging pipeline sets the -- CLEAN flag based on the values of other flags; these necessary values are -- different for stars and galaxies. The version of this sample query for galaxies -- can be found in the Clean photometry - Galaxies sample query. -- For queries on star objects, when you use PSF mags, use only PRIMARY objects -- and the flag combinations indicated below. If you use the Star view as this sample -- query does, you will get only primary objects; otherwise you will need to add -- a "mode=1" constraint. For example, if you are interested in r-band magnitudes -- of objects, perform the following checks (add analogous checks with AND for -- other bands if you are interested in multiple magnitudes or colors). -- In this query, the human-readable flag names (through calls to the flag -- functions have been replaced with explicit values. Using explicit values for flags -- makes the queries run much faster. | |
|
SELECT TOP 10 u,g,r,i,z,ra,dec, flags_r FROM Star WHERE |
|
-- Search for Cataclysmic Variables and pre-CVs with White Dwarfs and -- very late secondaries, using simple color cuts from Paula Szkody. -- This is a simple query that uses math in the WHERE clause. | |
| SELECT TOP 100 run, | |
| | |
| | |
| | |
| | |
| | |
| | -- Just get some basic quantities |
| FROM Star | -- From all stellar primary detections |
| WHERE u - g < 0.4 | |
| | |
| | |
| | -- that meet the color criteria |
| | |
|
-- Find binary stars with specific colors. -- At least one of them should have the colors of a white dwarf. | |
| SELECT TOP 100 s1.objID as s1, s2.objID as s2 | |
| FROM Star AS S1 | -- S1 is the white dwarf |
| JOIN Neighbors AS N ON S1.objID = N.objID | -- N is the precomputed neighbors lists |
| JOIN Star AS S2 ON S2.objID = N.NeighborObjID | -- S2 is the second star |
| WHERE | |
| | -- and S2 is a star |
| | -- the 3 arcsecond test |
| | -- and S1 meets Paul Szkodys color cut for |
| | -- white dwarfs. |
| | |
| | |
| | |
|
-- Another example of using the sppLines table generated by -- the SEGUE Spectroscopic Parameter Pipeline. This sample query -- selects low metallicity stars ([Fe/H] < -3.5) where more than three -- different measures of feh are ok and are averaged. SELECT sl.plate, sl.mjd, sl.fiber, FROM sppLines AS sl WHERE |
|
-- This sample query searches the sppParams table, which contains -- stellar parameters calculated by the SEGUE Spectroscopic Parameter Pipeline. -- The sample query selects spectroscopic stars in specific ranges of metallicity, -- gravity and temperature (with some flag checks). SELECT top 10 so.bestobjid, so.specobjid, so.plate, so.mjd, so.fiberid, FROM specobjall so WHERE sp.teffadop > 4500 and sp.teffadop < 5500 |
|
-- This query selects multiply-detected sources (stars) in photometry, -- using the thingIndex and detectionIndex tables. thingIndex contains -- a list of all unique sources, with a primary key defined called thingId, -- and detectionIndex contains the objId of each observation of each -- thingId. The objId can be used to join with the Star view of the photoObjAll table -- in order to recover the photometric data associated with each stellar observation. SELECT TOP 10 t.thingid, t.ndetect, d.objid, p.psfMag_r, FROM thingIndex AS t WHERE t.ndetect > 1 | |
|
-- Select stars surrounding the open cluster M67, include positions, color mag
-- and propermotion and pm errors. SELECT TOP 100 s.ra, s.dec, s.psfmag_g, (s.psfmag_g - s.psfmag_r) as gmr, pm.pmra, pm.pmdec, pm.pmraerr, pm.pmdecerr, pm.pml, pm.pmb FROM star s JOIN propermotions pm ON s.objid = pm.objid WHERE | |
|
-- The Clean Photometry sample query above showed how -- to select only objects for which SDSS-III imaging pipeline has declared -- the photometry clean. Usually, it is sufficient to search only the CLEAN flag; -- but in some contexts, it is important to know what conditions -- are required for the CLEAN flag to be set. The imaging pipeline sets the -- CLEAN flag based on the values of other flags; these necessary values are -- different for stars and galaxies. The version of this sample query for stars -- can be found in the Clean photometry - Stars sample query. -- For galaxies (i.e. not using PSF mags): Again use only PRIMARY objects. Other -- cuts are nearly the same, but remove the cut on EDGE. Possibly also remove -- the cut on INTERP flags. -- In this query, the human-readable flag names (through calls to the flag -- functions have been replaced with explicit values. Using explicit values for flags -- makes the queries run much faster. SELECT TOP 10 u,g,r,i,z,ra,dec, flags_r FROM Galaxy WHERE | |
|
-- Galaxies with bluer centers, by Michael Strauss. For all galaxies with r_Petro < 18, -- not saturated, not bright, and not edge, give me those with centers appreciably bluer -- than their outer parts, i.e., define the center color as: u_psf - g_psf and define -- the outer color as: u_model - g_model; give me all objs which have -- (u_model - g_model) - (u_psf - g_psf) < -0.4 -- -- Another flags-based query. -- NOTE: This query takes a long time to run without the "TOP 1000". | |
| SELECT TOP 1000 | |
| | |
| FROM Galaxy | |
| WHERE | |
| | |
| | |
| | |
| | |
| | |
| | |
|
-- Diameter-limited sample of galaxies from James Annis. -- Another query showing the use of flags, now using the bitwise '|' (or). -- NOTE: This query takes a long time to run without the "TOP 10", please see below for a faster version. | |
| SELECT TOP 10 | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| FROM Galaxy | |
| WHERE ( flags & (dbo.fPhotoFlags('BINNED1') | |
| | dbo.fPhotoFlags('BINNED2') | |
| | dbo.fPhotoFlags('BINNED4')) ) > 0 | |
| and ( flags & (dbo.fPhotoFlags('BLENDED') | |
| | dbo.fPhotoFlags('NODEBLEND') | |
| | dbo.fPhotoFlags('CHILD')) ) != dbo.fPhotoFlags('BLENDED') | |
| and ( (flags & dbo.fPhotoFlags('NOPETRO') = 0) | |
| and petroR50_i > 15) | |
-- A much faster way to do this query is to substitute the actual flag values rather | |
| -- than calling the dbo.fPhotoFlags functions for each row selected by the query. | |
| -- Invoking these functions for potentially millions of rows is quite expensive. You | |
| -- can avoid this by first running the 4 quick "pre-queries" shown below, with the | |
| -- indicated values returned from each query. Then use these values to substitute | |
| -- in the original query as shown in the last query below. | |
| | |
| 1) SELECT (dbo.fPhotoFlags('BINNED1') | |
| | dbo.fPhotoFlags('BINNED2') | |
| | dbo.fPhotoFlags('BINNED4')) | |
| | |
| -- This returns the value 1879048192. | |
| | |
| 2) SELECT (dbo.fPhotoFlags('BLENDED') | |
| | dbo.fPhotoFlags('NODEBLEND') | |
| | dbo.fPhotoFlags('CHILD')) | |
| | |
| -- This returns the value 88. | |
| | |
| 3) SELECT dbo.fPhotoFlags('BLENDED') -- This returns 8. | |
| 4) SELECT dbo.fPhotoFlags('NOPETRO') -- This returns 256. | |
| | |
| -- Finally, here is the original query with these values instead of the function calls. | |
| SELECT TOP 10 | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| FROM Galaxy | |
| WHERE | |
| ( flags & 1879048192 ) > 0 | |
| and ( flags & 88 ) != 8 | |
| and ( (flags & 256 = 0) | |
| and petroR50_i > 15) | |
| | |
|
-- A version of the LRG sample, by James Annis. -- Not precisely the same version as that used in target selection. -- Another query with many conditions and flag tests. -- As with the previous example, this query will run much faster if -- the flag function calls are replaced with explicit values. | |
| SELECT TOP 10 | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| FROM Galaxy | |
| WHERE ( ( flags & (dbo.fPhotoFlags('BINNED1') | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
|
-- DR8 includes data on visual galaxy morphologies from the -- Galaxy Zoo project. Galaxy Zoo is a web-based project -- that used the collective efforts of about 100,000 volunteer -- citizen scientists to classify more than one million -- SDSS DR7 Main Galaxy Sample (MGS) galaxies. -- Full details on the classification process, including the operation -- of the site and estimates of classification accurary, are given in -- Lintott et al. (2008). -- Galaxy Zoo data are contained in tables that begin with zoo -- in the DR8 schema. For each galaxy, available data includes its -- SDSS object ID and weighted probabilities for each of six different -- morphological classifications based on counts of volunteer "votes". -- Full details on what data the Galaxy Zoo catalog contains are given -- in Lintott et al. (2010). -- The first query below finds the weighted probability that a given galaxy -- has each of the six morphological classifications. -- The second query finds 100 galaxies that have clean photometry, -- at least 10 Galaxy Zoo volunteer votes and at least an 80% probability -- of being clockwise spirals. | |
| SELECT | |
| FROM ZooNoSpec | |
| WHERE objid = 1237656495650570395 | |
| -- Second query: find likely clockwise spirals. | |
| SELECT TOP 100 | |
| FROM Galaxy as G | |
| JOIN ZooNoSpec AS zns | |
| | |
| WHERE g.clean=1 and zns.nvote >= 10 and zns.p_cw > 0.8 | |
| | |
|
-- This query selects Luminous Red Galaxies between redshifts 0.4 and 0.65,
-- in a quasi-mass limited manner. This query is very similar to part of the -- selection of BOSS targets (the so-called CMASS LRGs). SELECT TOP 10 * FROM photoprimary WHERE |
|
-- This query does a table JOIN between the Portsmouth Passive (stellarMassPassivePort) -- and Portsmouth Starforming (stellarMassStarformingPort) and Wisconsin PCA (stellarmMassPCAWisc) -- tables and selects the stellar masses for a particular PLATE-MJD: SELECT TOP 10 passive.fiberid as fiberID, passive.ra, passive.dec, FROM stellarMassPassivePort AS passive starforming.specobjid WHERE passive.plate = 3606 AND passive.mjd = 55182 AND pca.warning=0 |
|
-- This query does a table JOIN between the Portsmouth Emission Lines (emissionLinesPort) -- and Wisconsin PCA (stellarmMassPCAWisc) and the SpecObj tables and selects -- the velocity dispersion for a particular PLATE-MJD: SELECT TOP 10 emline.fiberid as fiberID, emline.ra, emline.dec, FROM emissionLinesPort AS emline WHERE emline.plate = 3606 AND emline.mjd = 55182 AND pca.warning=0 |
|
-- The easiest way to find quasars is by finding objects whose spectra have -- been classified as quasars. This sample query searches the SpecObj -- table for the IDs and redshifts of objects with the class column equal to 'QSO' | |
| SELECT TOP 100 specObjID, z | |
| FROM SpecObj | |
| WHERE class = 'QSO' AND zWarning = 0 | |
|
-- Low-z QSO candidates using the color cuts from Gordon Richards. -- Also a simple query with a long WHERE clause. | |
| SELECT TOP 100 | |
| | |
| | |
| | |
| | |
| | |
| | |
| FROM Galaxy | |
| WHERE ( (g <= 22) | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
| | |
|
-- This sample query is a useful quasar query (from Sebastian Jester). -- Getting magnitudes for spectroscopic quasars - retrieves photometry. -- This query uses the SpecPhoto view of the SpecPhotoAll table, which is a pre-computed join -- of the important fields in the SpecObjAll and PhotoObjAll tables. It is very convenient and much -- faster to use this when you can instead of doing the join yourself. -- Getting FIRST data for spectroscopic quasars - returns only those quasars that have -- matches in the FIRST (Far-InfraRed Survey Telescope) table. SELECT sp.ra,sp.dec,sp.z, FROM SpecPhoto AS sp WHERE |
|
-- Here is a simple query for objects with reliable redshift estimation in the 0.4 < z < 0.5 range -- The query uses the Photoz table that contains photometric redshift estimates for galaxies. | |
| SELECT TOP 100 objId,z,zErr,absMagR FROM Photoz WHERE z BETWEEN 0.4 and 0.5 | |
| and nnIsInside=1 | -- the estimated object has nearest neighbors in the reference set |
| and nnCount>95 | -- note that zErr=-1000 indicates unreliable redshift estimate, |
| and zErr BETWEEN 0 and 0.03 | -- that's why the lower bound on zErr is needed |
-- A simple luminosity function with the same selection criteria as above -- Returns a histogram of absolute magnitudes for the given redshift range. -- (You will need to cut and paste this query if you want to run it in the SQL Search page, -- the above buttons will only load/run the first query). SELECT round(absMagR,1) as absMagR, COUNT(*) as cnt FROM Photoz WHERE z BETWEEN 0.4 and 0.5 and nnIsInside=1 and nnCount>95 and zErr BETWEEN 0 and 0.03 group by round(absMagR,1) order by round(absMagR,1) | |
|
-- This sample query find objects with spectra in the -- regular program of the SEGUE-2 survey. It checks -- both the "survey" tag (which tells you the overall -- survey) and the "programname" tag (which distinguishes -- subprograms within each survey). The PlateX table -- also has the survey and programname tags. SELECT TOP 50 ra, dec, specobjid, plate, mjd, fiberid FROM specObj WHERE survey = 'segue2' and programname = 'segue2' |
|
-- There are five possible survey names ("sdss", "segue1", -- "segue2", "boss" and "apogee", though APOGEE is not -- available in DR9 and earlier). Within each survey there -- can be a number of programnames; the main programs -- of each survey are: -- survey = 'sdss' and programname = 'legacy' -- survey = 'segue1' and programname = 'segue' -- survey = 'segue2' and programname = 'segue2' -- survey = 'boss' and programname = 'boss' -- For the SEGUE-1 and SDSS surveys in particular there -- are multiple programs. A full list of surveys and programs -- can be obtained with: SELECT DISTINCT survey, programname FROM platex ORDER BY survey |
