ADQL/SQL

This page describes the particular SQL 'flavour' that can be used to query AstroGrid data services.

SQL is, in theory, a 'standard' query language. However PAL installations require a restricted format to reduce ambiguity, and there are extra functions peculiar to the astronomy world.

The main functions handled are listed on the Portal Query page, such as FROM [scope of search] SELECT [returned columns] and WHERE [search criteria]. An introduction to SQL syntax is provided by FirstSQL.com

SELECT

Columns must be comma separated.

FROM

Also the FROM statement must be in the form FROM <table> or FROM <table> AS <alias> and each table or table/alias pair must be comma separated.

The comma separation helps distinguish between T.B-T.C and T.B-V where 'B-V' might (naughtily) be a column name.

Circle function

The circle function is CIRCLE("{equinox}",{ra},{dec},{radius}), eg:

SELECT * FROM SOURCE WHERE CIRCLE("J2000",20,30,0.1)

Operators

Make sure all operators (+, -, AND, OR, >, >=, etc) have at least one space on either side. This is again to unambiguously separate operators from column/table names

String Literals

All string literals should be enclosed in quotes. For example, SELECT * FROM dqc as o where (o.filter like 'i%')

What's Available?

To find out which tables and columns are available, and which functions are supported by the datacenter, use the Select a table button. This gives a Regsitry microbrowser in which you can Select a table which will produce a list of the columns on the left hand side of the Query page.

Examples

1. SQL: SELECT * FROM  usnob_psc as u where u.B2Mag > 21
   This means, select all columns from the USNO-B catalogue (which has to be given an alias, in this case u) where the column labelled B2Mag is greater than 21. Note that this should return only the few very faintest sources in the catalogue. The whole USNO-B catalogue is >10^7 sources, so be careful when querying such large data sets.
2. SQL: SELECT u.seqNo, u.ra, u.dec, u.B2Mag FROM usnob_psc  as u where u.B2Mag > 21
   will return just 3 columns, labelled seqNo, ra and dec, form USNO-B, where B2Mag is greater than 21.
3. SQL: SELECT * FROM twomass_psc as o where  (o.ra > 56.25) and (o.ra < 57.25) and (o.dec > 23.6167) and (o.dec < 24.6167)
   selects all columns from the 2MASS point source catalogue within a box bounded by blc (56.25, 23.6167) and trc (57.25, 24.6167). Note that position constraints must be in the same units (here, decimal degrees) and coordinate frame as the columns in question.
4. SQL: SELECT * FROM catalogue as o where (o.filter like 'i%')
   will find all images listed in the catalogue where the filter name contains 'i', e.g. 'i', 'i_RGO' etc.

Need more checked, working, real examples for other functions.

-- MartinHill - 25 Aug 2004

-- AnitaRichards - 22 Apr 2005