Archives of Photographic PLates for Astronomical USE

Queries for HandsOn Session

This page contains all examples which will be used during the Hands-On Session
at “Large Surveys with Small Telescopes”, Bamberg, 03/2019.

Simple Queries, and Andromeda observations

  • How many plates are in the archives?

    -- POSTGRES
    SELECT archive_id, COUNT(archive_id) AS N_plate
    FROM applause_dr3.plates GROUP BY archive_id
  • Count all plates having object name Andromeda in the metadata

    -- POSTGRES
    SELECT object_name,COUNT(DISTINCT plate_id) as N_obj
    FROM applause_dr3.exposure
    WHERE object_name LIKE '%dromed%'
    GROUP BY object_name
  • Get a list of preview files for all plates having object name Andromeda in the exposure table

    -- POSTGRES
    SELECT DISTINCT ex.plate_id, ex.object_name, pr.filename
    FROM applause_dr3.exposure AS ex
    JOIN applause_dr3.preview AS pr USING (plate_id)
    WHERE ex.object_name LIKE '%dromed%'
  • Find center coordinates for these Andromeda plates from astrometric solution

    -- POSTGRES
    SELECT DISTINCT ex.plate_id, ex.object_name, sol.raj2000, sol.dej2000
    FROM applause_dr3.exposure AS ex
    JOIN applause_dr3.solution AS sol USING (plate_id)
    WHERE ex.object_name LIKE '%dromed%'
  • Differences in center coordinates and distances for these Andromeda plates from astrometric solution and from exposure table metadata

    -- ADQL
    SELECT DISTINCT ex.plate_id, pl.archive_id, pl.plate_num,
    DISTANCE(POINT('ICRS', ex.raj2000, ex.dej2000), POINT('ICRS', sol.raj2000, sol.dej2000)) AS dist,
    ex.ra_orig, ex.dec_orig, ex.raj2000_hms, ex.dej2000_dms, ex.raj2000, ex.dej2000,
    sol.raj2000 AS sol_ra,sol.dej2000 AS sol_dec
    FROM applause_dr3.exposure AS ex
    JOIN applause_dr3.solution AS sol USING (plate_id)
    JOIN applause_dr3.plate AS pl ON ex.plate_id=pl.plate_id
    WHERE ex.object_name LIKE '%dromed%'
  • Differences in center coordinates and distances for these Andromeda plates from astrometric solution and from exposure table metadata, discarding rows where coordinates are missing from metadata

    -- ADQL
    SELECT DISTINCT ex.plate_id, pl.archive_id, pl.plate_num,
    DISTANCE(POINT('ICRS',ex.raj2000,ex.dej2000), POINT('ICRS',sol.raj2000,sol.dej2000)) AS dist,
    ex.ra_orig, ex.dec_orig, ex.raj2000_hms, ex.dej2000_dms, ex.raj2000, ex.dej2000,
    sol.raj2000 AS sol_ra,sol.dej2000 AS sol_dec
    FROM applause_dr3.exposure AS ex
    JOIN applause_dr3.solution AS sol USING (plate_id)
    JOIN applause_dr3.plate AS pl ON ex.plate_id=pl.plate_id
    WHERE ex.object_name LIKE '%dromed%' AND ex.raj2000 IS NOT NULL