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: