how to search
Login | Signup

Query the ZTF object database

The form below is a builder for SQL SELECT queries on the ZTF database of objects. There are three tables that can be joined in SELECT queries (only SELECT queries are allowed). See examples below. The tables are:

  • objects: The astrophysical objects which consist of a series of candidates (aka detections). The object has a light curve, the candidate has a single magnitude and date.
  • sherlock_crossmatches: Information about each object derived from multiple catalogs by the Sherlock software. The crossmatches have a rank 1,2,3... where 1 is considered most likely.
  • candidates: The individual detections provided by ZTF each night. Each is associated with an object, which is a cluster of detections within 1.5 arcsec, and presumed to be an astrophysical object.

For detailed information about the attributes of these three tables, that you can use in the queries : click here.

  • The public survey uses two filters: fid=1 (g) and fid=2 (r)
  • For each observing field of the survey and each of the g and r filters, ZTF will only issue candidate alerts when it has built up a reference image of that field with that filter, by stacking 15 good images.
  • Once that is in place, each fresh image is subtracted from the reference, and any 5-sigma difference generates a candidate alert.
  • When a candidate is within 1.5 arcseconds of a previous candidate, it gets the same objectId. Thus a light curve can be obtained from all the candidates that have a given objectId.
  • More details of the processing pipeline are available here.
  • Further cuts can be made to remove spurious candidates, The highest quality candidates satisfy the criterion rb >= 0.65 and nbad = 0 and fwhm <= 5 and elong <= 1.2 and abs(magdiff) <= 0.1. See example below.
  • In the example queries below, you can see the expression jdnow(). This evaluates to the current Julian Day: in short, it means simply now.
  • If you would like to learn the SQL language, this is a good resource.
(check this box for JSON output )

(If you would like to create your own stored queries, you should be signed in. See links at top left.)

Contributed Stored Queries

Stored queries created and made public are listed below. Click on the name of the query to push it into the query area above.
Name Owner Description Query
Recent high glat Roy Williams Recently observed objects (last 12 hours), with high galactic latitude. 
SELECT objectId, ncand
FROM objects WHERE
    jdmax > jdnow() - 0.5
AND abs(glatmean) > 10.0
ORDER BY ncand DESC
Recent solar system Roy Williams Recently observed solar system candidates (last 12 hours) 
SELECT objectId, ssmagnr AS magnitude, ssnamenr as MPC_name
FROM candidates WHERE
    ssdistnr BETWEEN 0.0 and 5.0
AND jd > jdnow() - 0.5
ORDER BY jd DESC
Active CVs Roy Williams Cataclysmic variables with wide swings in magnitude and more than 10 points in the light curve 
SELECT objectId, ncand, maggmean, magrmean FROM objects 
WHERE 
    (maggmax-maggmin > 2 OR magrmax-magrmin > 2)
AND ncand > 10
AND sherlock_classification = "CV"
ORDER BY ncand DESC
Quality near Galaxy Roy Williams Best high-quality candidates near a PanStarrs galaxy 
SELECT objectId, c.jd-2400000.5 as mjd, c.fid, c.magpsf, 
    o.sherlock_classification
FROM objects o NATURAL JOIN candidates c
WHERE 
    c.rb >= 0.75 and c.nbad = 0 and c.fwhm <= 5 
    and c.elong <= 1.2 and abs(c.magdiff) <= 0.1
AND c.sgscore1 < 0.4 
AND o.jdmax > jdnow() - 2 
AND o.ncand > 5
Bright SN candidates Roy Williams Bright objects, not coincident with a Pan-STARRS star, discovered in last 20 days 
SELECT objectId, c.jd-2400000.5 as mjd, c.fid, c.magpsf, 
    o.sherlock_classification
    FROM objects o NATURAL JOIN candidates c
WHERE 
    c.rb >= 0.75 and c.nbad = 0 and c.fwhm <= 5 
    and c.elong <= 1.2 and abs(c.magdiff) <= 0.1
AND c.magpsf < 19
AND c.sgscore1 < 0.49
AND c.jd > jdnow() - 2
AND o.ncand > 5
ORDER BY objectId,c.jd
SN-like candidates in last 14 days Stephen Smartt SN-like candidates (Sherlock classifications SN, NT and orphans), time limit adjustable (just adjust the number 14). Rejects Pan-STARRS star matches 
SELECT DISTINCT
    o.objectId,
    o.ramean,
    o.decmean,
    o.jdmin - 2400000.5 AS mjdmin,
    o.jdmax - 2400000.5 AS mjdmax,
    o.magrmin,
    latestrmag,
    sherlock_classification,
    IF(distpsnr1 < 2 AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score
FROM
    objects o,
    candidates c
WHERE
    o.sherlock_classification NOT IN ('VS' , 'AGN', 'CV', 'BS')
        AND o.jdmin > JDNOW() - 14
        AND o.ncand > 3
        AND c.objectId = o.objectId
        AND (c.jd > JDNOW() - 14)
        AND c.magpsf < 20
        AND c.rb >= 0.75
        AND c.nbad = 0
        AND c.isdiffpos = 't'
        AND c.fwhm <= 5
        AND ABS(c.magdiff) <= 0.1
        AND c.elong <= 1.2
ORDER BY  score , mjdmin DESC
All nuclear transients and TDE candidates Stephen Smartt Near core of inactive catalogued galaxies (within 1"), flags Pan-STARRS stellar matches to let user judge star/galaxy separation. Objects discovered in last 30 days. 
SELECT DISTINCT
    o.objectId,
    o.ramean,
    o.decmean,
    o.jdmin - 2400000.5 AS mjdmin,
    o.jdmax - 2400000.5 AS mjdmax,
    o.magrmin,
    latestrmag,
    sherlock_classification,
    IF(distpsnr1 < 2 AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score
FROM
    objects o,
    candidates c
WHERE
    o.sherlock_classification IN ('NT')
        AND o.jdmin > JDNOW() - 30
        AND o.ncand > 3
        AND c.objectId = o.objectId
        AND (c.jd > JDNOW() - 30)
        AND c.magpsf < 20
        AND c.rb >= 0.75
        AND c.nbad = 0
        AND c.isdiffpos = 't'
        AND c.fwhm <= 5
        AND ABS(c.magdiff) <= 0.1
        AND c.elong <= 1.2
ORDER BY  score , mjdmin DESC
TNS crossmatch Roy Williams This query finds all Lasair objects that are in the <a href=https://wis-tns.weizmann.ac.il/>Transient Name Server</a>, meaning they have a comment that includes the string 'TNS'. The most recent are first. 
SELECT objectId, content FROM objects NATURAL JOIN comments WHERE content LIKE '%TNS%' ORDER BY time DESC
An Asteroid's Tail Ken Smith Check ZTF detections for active asteroid (6478) Gault 
SELECT objectId, jd-2400000.5 as mjd, ssnamenr
FROM candidates WHERE
ssnamenr = "6478"
SN-like candidates in last 3 days Stephen Smartt SN-like candidates (Sherlock classifications SN, NT and orphans), discovered within the last 3 days. Also rejects Pan-STARRS star matches. 
SELECT DISTINCT
    o.objectId,
    o.ramean,
    o.decmean,
    o.jdmin - 2400000.5 AS mjdmin,
    o.jdmax - 2400000.5 AS mjdmax,
    o.magrmin,
    latestrmag,
    sherlock_classification,
    IF(distpsnr1 < 2 AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score
FROM
    objects o,
    candidates c
WHERE
    o.sherlock_classification NOT IN ('VS' , 'AGN', 'CV', 'BS')
        AND o.jdmin > JDNOW() - 3
        AND o.ncand > 3
        AND c.objectId = o.objectId
        AND (c.jd > JDNOW() - 3)
        AND c.magpsf < 20
        AND c.rb >= 0.75
        AND c.nbad = 0
        AND c.isdiffpos = 't'
        AND c.fwhm <= 5
        AND ABS(c.magdiff) <= 0.1
        AND c.elong <= 1.2
ORDER BY  score , mjdmin DESC