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