Astronomical Dataset Query Language (ADQL)

1. THE US NATIONAL VIRTUAL OBSERVATORY Astronomical Dataset Query Language (ADQL) Ray Plante NVO Summer School 2005 - Aspen

2. Intersections and Goals • Users • Will primarily use portal interfaces to query catalogs in pursuit of science Goals: • Create original ADQL queries using the OpenSkyPortal • Cross-correlate two catalogs using the XMatch function • “Scripters” and Data Providers • Will use client libraries to query catalogs • In pursuit of science • requires more queries and/or repetition than is practical to do by hand. • Will use a toolkit to deploy a service that uses ADQL (e.g. SkyNode) Goals: • Understand the role of ADQL/x in supporting query web services • See how ADQL will be used with other services beyond SkyNodes • Developers • Will develop new services and applications that use ADQL • Will extend existing code that uses ADQL Goals: • Use library to convert between ADQL/s & ADQL/x • Understand how ADQL can be adapted to new local query languages NVO Summer School 2005 - Aspen

3. What is ADQL? • ADQL = subset of SQL92 + astronomy extensions • Originally developed as part of SkyNode interface • Focuses on read-access via SELECT • May include some support for CREATE TABLE or more in future • astronomy: regions and cross-matching • ADQL has two forms: • ADQL/s – string form • Looks like SQL • Intended as user’s view of ADQL • ADQL/x – XML form • SQL “parse tree” • Format used in messages passed to search services • Easier to parse and transform into local query NVO Summer School 2005 - Aspen

4. Why ADQL/x? If ADQL is standard SQL (plus) and we are querying SQL databases, why do we need an XML form? Query Transformation is commonly necessary • Few databases are 100% compliant with the SQL standard. • Transform to local SQL dialect • Semantic filtering possible (transforming metadata). • Easier to adapt to non-relational databases • E.g. XML database NVO Summer School 2005 - Aspen

5. What is ADQL? • ADQL is an emerging IVOA standard • http://www.ivoa.net/Documents/latest/ADQL.html • Current SkyNodes use ADQL ver. 0.7.4 • Migration to ver. 1.0 expected this year • Greater use beyond SkyNodes expected SELECT o.objId, o.ra, o.r, o.type, t.objId FROM SDSS:PhotoPrimary o, TWOMASS:PhotoPrimary t WHERE XMATCH(o,t,3.5,1) AND Region('Circle J2000 181.3 -0.76 6.5') AND o.type=3 NVO Summer School 2005 - Aspen

6. ADQL for UsersRestrictions on SQL92 • Only SELECT statements allowed • v1.0: SELECT INTO for saving results in remote storage • All tables in FROM must define an alias FROM PhotoPrimary o • JOIN keywords not supported • Traditional inner joins supported • Outer joins not supported • IN keyword not supported p.id IN (23872871, 2309823, 1512342) NVO Summer School 2005 - Aspen

7. ADQL for UsersAstronomical Extension: Regions • Does a position fall within a region of sky? • Uses Space-time Coordinate metadata schema • Also an emerging IVOA standard • Has string and XML formats • Allows for a several region shapes, coordinate systems • Current SkyNode implementations only support CIRCLE J2000 WHERE REGION('CIRCLE J2000 ra dec radius') • ra dec = right ascension, declination in degrees • radius = angular radius of cone, in degrees NVO Summer School 2005 - Aspen

8. ADQL for UsersJoining Tables • Traditional Inner Joins • WHERE constraint where key in one table is set to key in other table p.objId = s.objId • This is only practical for joining tables from the same catalog/node • ADQL/SkyQuery was motivated by the need to cross-correlate distributed tables by position • XMatch: extension for joining based on angular distance. NVO Summer School 2005 - Aspen

9. ADQL for UsersAstronomical Extension: XMatch • Catalog/Node designation: • Tables in FROM class are preceeded by node name FROM SDSS:PhotoPrimary s, TWOMASS:PhotoPrimary t • XMatch function • Arguments: tables to cross-match • Referred to by the table aliases • Returns: statistical confidence level in sigmas XMATCH(s, t) > 3.5 • given sources in each table are the same with a confidence of better than 3.5 sigmas • Can include negative detection XMATCH(s, t, !o) > 3.5 • Detected in tables s and t but not in o NVO Summer School 2005 - Aspen

10. ADQL for Users/Data ProvidersThe XMatch Algorithm • ADQL does not set cross-match algorithm • Requires positional information and positional errors • SkyNode recommends chi-square minimization • Implementations should add 4 columns to hold accumulated a values NVO Summer School 2005 - Aspen

11. ADQL for ScriptersAccessing ADQL Services • OpenSkyPortal web service takes ADQL/s • querying SkyNodes is straight-forward through portal service • Recall IDL client • Querying an individual SkyNode service • Requires use of ADQL/x • Client-side support is not great but improving • As a user, would like to give ADQL/s NVO Summer School 2005 - Aspen

12. ADQL for ScriptersOther uses of ADQL • Searching Registries • Registry Interface v1.0 for this year • Specify only WHERE clause • Use simple XPaths for column names Ex: WHERE content/description LIKE '%quasars%' • Future generations of SIAP, SSAP • Replace simple keyword=value query • More fine-grained control over search criteria NVO Summer School 2005 - Aspen

13. ADQL for DevelopersADQLlib • Parses and converts between string & XML versions • Can be incorporated into client code to provide support for ADQL/s • ADQL/x -> ADQL/s • Two implementations, one using XSLT • Different stylesheets provided to convert into DB’s flavor of SQL NVO Summer School 2005 - Aspen