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,

   p.psfMagerr_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:

   SELECT

   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.

Next: Explore the observations of each star

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