VWDB — ADQL

STAND-IN DATA. This catalog contains 2,279 ZTF detections and 1,000,000 synthetic fixture rows — NOT Visnjan-Washington observations. Stand-in data is loaded while the VW Data Analysis Pipeline is built. Do not use these measurements for science.

ADQL query

A documented subset of ADQL, translated to Q3C-backed PostgreSQL and run synchronously. Not a TAP service — there is no /sync, /async or VOTable here, and this endpoint is registered nowhere. What is and is not supported is written out below. See the schema for the columns.

Examples

Cone search, nearest first

SELECT TOP 100 source_id, ra, dec, mjd, snr,
       DISTANCE(POINT('ICRS', ra, dec), POINT('ICRS', 185.0, 15.0))
FROM survey.source
WHERE CONTAINS(POINT('ICRS', ra, dec),
               CIRCLE('ICRS', 185.0, 15.0, 0.01)) = 1
ORDER BY DISTANCE(POINT('ICRS', ra, dec), POINT('ICRS', 185.0, 15.0))

Bright detections in one band

SELECT TOP 200 s.source_id, s.mjd, s.flux, e.band
FROM survey.source AS s
INNER JOIN survey.exposures AS e ON e.exposure_id = s.exposure_id
WHERE e.band = 'zr' AND s.snr > 20
ORDER BY s.flux DESC

How many detections per band

SELECT e.band, COUNT(*), AVG(s.snr)
FROM survey.source AS s
INNER JOIN survey.exposures AS e ON e.exposure_id = s.exposure_id
GROUP BY e.band
ORDER BY COUNT(*) DESC

Syntax reference — what this subset does and does not accept

The shape of a query:

SELECT [TOP n] columns FROM table [INNER JOIN table ON ...] [WHERE ...] [GROUP BY ...] [ORDER BY ... ASC|DESC]

Supported

One table, optionally one INNER JOIN of survey.source to survey.exposures.
Comparisons, BETWEEN, IN, IS [NOT] NULL, AND/OR/NOT, and + - * /.
Geometry: CONTAINS(POINT('ICRS', ra, dec), CIRCLE('ICRS', ra, dec, radius)) = 1 and DISTANCE(POINT(...), POINT(...)), both in degrees. The row's own columns go in the first POINT — that is what lets the query use the spatial index. The CIRCLE radius is capped at 1.0° and must be written out as a plain number, so that the number checked against the cap is the one the search uses.
Functions: ABS, ACOS, ASIN, ATAN, ATAN2, AVG, CEILING, CIRCLE, CONTAINS, COS, COUNT, DEGREES, DISTANCE, EXP, FLOOR, LOG, LOG10, MAX, MIN, MOD, POINT, POWER, RADIANS, ROUND, SIN, SQRT, SUM, TAN.
GROUP BY and ORDER BY ... ASC|DESC.
Column aliases: SELECT ra AS alpha names the output column in the table, the CSV header and the JSON. A label is up to 64 letters, digits and underscores, starting with a letter or an underscore. ORDER BY still names a catalog column, not a label.
Comments (--, /* */) are allowed and ignored, and a single trailing ; is too. LIMIT n is accepted as a spelling of TOP n and meets the same row cap.

Not supported, deliberately

Subqueries, UNION/EXCEPT/INTERSECT, common table expressions, window functions.
Outer joins, cross joins, self-joins, USING, NATURAL JOIN, more than one join.
Any statement that is not a SELECT, and more than one statement.
CAST, LIKE, DISTINCT, HAVING, OFFSET, string functions, and ORDER BY a select-list position.
A condition where a value goes — SELECT snr > 5 asks to select a yes/no. Filter by it instead: WHERE snr > 5.
Geometry beyond POINT, CIRCLE, CONTAINS and DISTANCE — no POLYGON, BOX or INTERSECTS.

These are not "not yet". The parser reads the whole of ADQL and then declines what this service does not serve, so a refusal names the construct and what to write instead rather than pointing at a character. The reasoning is in docs/decisions/006-adql-subset.md, and what the subset costs to keep — this list is maintained here, not by a VO server product — is in docs/decisions/018-adql-sqlglot-front-end.md.