Sloan Digital Sky Survey
SkyServer DR14  
Not logged in Login Help

DR14 Help
 Start Here
 Cooking with Sloan
     - General
     - Solar System
     - Stars
     - Galaxies
     - QSOs/Cosmology
     - Teaching/Labs
 Contact Help Desk
 Searching SDSS
 SQL Tutorial
 SQL in SkyServer
 Sample SQL Queries
 Query Limits
 Searching Advice
 About the Database
 Table Descriptions
 Schema Browser
Multiple Observations

Back to Stars index

Search with SQL (open in new window)

  1. Go to the SQL Search tool (link opens in new window). To get there from the astronomers' main page, look under Search Tools. To get there from the public main page, look under SkyServer Tools, then Search. Or, you can practice the query in the query box at the bottom of this page.

  2. The SDSS keeps track of multiple detections of the same underlying "thing" in its catalogs using tables called thingIndex and detectionIndex. Each thing is stored in thingIndex, identified by a primary key column called thingID. Each individual detection of a "thing" is stored in the detectionIndex table, which indexes the objectID (objID) of each detection with the appropriate thingID.

  3. Thus, to find multiple detections of an object, just write a SQL statement to JOIN each detection (stored in detectionIndex.objID) with its associated thing (thingIndex.thingID). For example:

  4. In the main window, type the following query:

    SELECT TOP 100 t.thingid, t.ndetect, d.objid, p.psfMag_r,
    FROM thingIndex AS t
    JOIN detectionindex AS d ON t.thingid = d.thingid
    JOIN Star AS p ON d.objid = p.objid
    WHERE t.ndetect > 1

    The query returns the object ID of each observation of the star, for 100 stars.

  5. To learn more about each observation of the star, you will need to go to another tool. Save the object ID of each observation; you will need it to find the observations later. One way to save the object IDs is to request the query results as CSV, then save the resulting CSV file to your machine.

  6. You can also return more data about each star in a SQL query. However, because detectionIndex contains only the object IDs of each observation, you will need to JOIN it with the PhotoObjAll table. Note that since PhotoObjAll's associated views contain only primary objects, you must use the full PhotoObjAll table in such queries.

  7. For example, the following compound query returns time series data for a specific object:

     LTRIM(STR(mjd_r,20,2)) AS MJD, dbo.fSDSS(p.objId) AS ID,
     modelMag_g, modelMagErr_g, modelMag_r, modelMagErr_r,
     modelMag_i, modelMagErr_i, p.ra, p.dec
    INTO #list
    FROM detectionindex d
    JOIN PhotoObjAll p ON d.objid=p.objid
    JOIN Field f ON p.fieldid=f.fieldid
    WHERE d.thingid=97423000
    ORDER BY 1
    --- Now find objects near each of the detections
    SELECT a.*, b.*
    FROM #list a
    CROSS APPLY dbo.fGetNearbyObjEq(a.ra, a.dec, 0.1) b

Use the query box below to practice running the two queries shown above. The "Query 1" and "Query 2" buttons will make the two queries above appear in the query window below. You can modify the query to return other data if you want. Click Submit to run the query or Reset to reset the query box.


Enter your SQL query in the text box. The query is limited to 10 minutes and 100,000 rows.

Next: Explore the observations of each star

 Go to the previous page Go to the next page

Back to Cooking with Sloan main page
Back to Help main page
Contact Help Desk