Databases and SQL

Databases are a convenient way to store large quantities of information, and to cross reference different types of information. The SDSS provides a database with many of the derived quantities from the different SDSS surveys, and provides some tools to make accessing these databases fairly easy.

For SDSS, the main database is the Catalog Archive Server (CAS), which compiles derived quantities from the SDSS data (not images or spectra, but quantities that are derived from them: for example, ....?

Database structure and organization: the database schema
- Let's look at the DR12 schema . Note Tables and Views.
- Some key tables:
  - APOGEE: apogeeVisit, apogeeStar, aspcapStar
  - SDSS/BOSS: specObj (which is a view)
  - MaNGA: mangaDrpAll (but only in DR13Collab database! and only target information)
Basic database queries using SQL
- SELECT what you want
- FROM table(s) in which it is stored
- (optionally) WHERE certain conditions are met
- Let's look at a relatively simple query
SQL output can be directed into a file
For SDSS, databases are stored centrally, and web tools are used, but databases can be located whereever, and there are multiple tools for querying them (command line and graphical)

More advanced queries can extract information from multiple tables, joining them on specified conditions; this is where the power of a relational database really comes in

JOIN ON table1.yyy = table2.xxx
Some typical SDSS joins:
- SDSS/BOSS : join specobj and photoobj
- APOGEE : join apogeeStar, aspcapStar, apogeeObject
Note example queries on SDSS web site, and also in casJobs interface!
ADQL :Astronomical data query language: an extension on SQL
- tapvizier
- < a href=https://www.cosmos.esa.int/documents/915837/915858/ADQL_handson_slides.pdf/652b9120-a3fe-4857-b5eb-933b476687ad> GAIA queries

Accessing the CAS database:

SkyServer
Database access in left column:
- some simple searches that make the SQL for you: SQS (spectroscopic query search), IRSQS (IR spectroscopy query search)
- general SQL query form: SqlSearch
- interface for larger queries: casJobs

Practice:

Make some SDSS query:
- get at least four output numerical quantities, possibilities:
  - SDSS/BOSS: galaxies within some region of the sky, get RA/DEC/redshift velocity dispersion from specobj, magnitudes from PhotoObj
  - APOGEE: stars within some region of the sky, get RA/DEC/nvisits/radial velocity/vscatter from apogeeStar, stellar parameters/abundances from aspcapStar
- start with HTML query, then get both CSV and FITS output files on your computer
- Copy your output csv and fits files to your subdirectory on the astronomy server:
```
scp {filename} visitor@astronomy.nmsu.edu:{dirname}/
```