The SX Query Language

SXQL Syntax

SELECT-FROM-WHERE Block

The SELECT-FROM-WHERE is the basic building block of an SXQL query. The syntax is

SELECT selection_string
FROM   from_item
WHERE  predicate_string

The selection_string is one of the following:

Extraction Specification

if this is the FIRST select block

``*''

for a nested query on the same class

Association Name

for a nested query on an associated class

The from_item is either

• the name or alias of the class you want to query on, see the online Class Reference for all classes and members, accessible through the Help .. Classes menu item.
• or another SELECT-FROM-WHERE block (see nested select)

The predicate_string is the actual query specification for the class you specified in the from_item.

Extraction Specification

The very first SELECT is followed by a list of comma-separated items that specify what you want to get returned from the query result. The items have to be members of the class specified (directly or indirectly throught nested selects) in the from_item. You can specify in the list

Names of Class Members

If it is an array, you can specify the array item. If you don't specify which element of an array you want to have returned, ALL array elements will be returned.

Dereferenced Members

If the selected class has an association to another class, use the '.' (dot) operator to access the members of the associated class through the association name. Example: If you query on any of the Tag classes, you can access all PhotoObj class elements by prefixing them with obj..

Arithmetics on Class Members

Use the arithmetic operators +,-,*,/ and the bitwise operations |, & and ând the parentheses (,) to any level of complication. You also have the functions SIN, COS, EXP, LOG, LN, SQRT (always in uppercase!) at your disposition.

Format specifier STR

The SQL format specifier STR(item,n,d) can be used to specify the output format for item: n gives the total number of digits to display and d gives the number of decimal places (like in printf)

COUNTASSOC(assocname)

this special item returns a number count on the association given by assocname for a selected class.

Logical Operations between SELECT Blocks

We may have several SELECT-FROM-WHERE statements linked together by one of the following keywords:

UNION

Return the result of both queries (like an OR)

INTERSECT

: Return only objects that are the result of both queries (like an AND)

EXCEPT

: Return only objects from the first SELECT that are not in the second SELECT (like AND NOT)

These operations can be used to whatever level using simple parentheses around the SELECT blocks. Example sketch:

(SELECT block)
UNION
( SELECT block
  INTERSECT
  SELECT block
)

Composing Predicates

Predicates are the conditions that you specify to select the subset of the data you are interested in. For example: Find all objects with i < 22 translates just to i < 22 in the WHERE clause. A more prominent example would be the quasar-selection query (from Fan et. al.)

(i < 19)  && 
(u-g > 2 || u>22) &&
(g-r > 1.0) &&
(r-i < 0.2 + 0.42*(g-r - 1.0) || (g-r > 2.3)) &&
(i-z < 0.25)

of course this is where all the effort goes to get something meaningful out of the data.

The predicate is composed of arithmetic and logical operators and a few SQL keywords. See the Operator Table below for details.

Coordinate Cuts

There is a speciality to SXQL with respect to coordinate cuts. You can specify your coordinate cut in J2000, GALACTIC, SUPERGALACTIC, ECLIPTIC and SURVEY coordinates. The RA/dec of these coordinates are given by the macros

J2000          :  RA()     DEC()
GALACTIC       :  GL()     GB()
SUPERGALACTIC  :  SGL()    SGB()
ECLIPTIC       :  ECL()    ECB()
SURVEY         :  LAMBDA() ETA()

These macros have to be all written in uppercase, and with the two parenthesis. They work in any class that has an association with a tag class. But we recommend using them in the tag classes only.

To specify a cut, simply use the standard greater than and less than operators. To specify a range cut, use the SQL 'BETWEEN' keyword. Examples:

  RA() > 45
  DEC() BETWEEN 20 AND 25

All declination coordinates have periodic ranges, so for example RA() < 365 will translate into RA() < 5. RA coordinates are restricted to be between -90,+90 degrees.

Rules for Nested Select Blocks

SELECT statements can be nested in the FROM part of a SELECT block, which can be another SELECT block :

SELECT extractItems FROM 
   ( SELECT * FROM className WHERE predicate1 ) 
WHERE predicate2

Nested SELECT statements can be of two types. If we have a * (star) following the SELECT keyword (like in the example above). In that case, both the internal and external predicates (predicate 1 and 2 in the example above) are operating on the same class given by className.

The other nested SELECT type has an association name of the SELECT block's class (given by the FROM statement) instead of the * like

SELECT extractItems FROM 
   (SELECT assocName FROM className WHERE predicate1)
WHERE predicate2

In this case, predicate1 is a set of constraints on the data members of the class given by className but predicate2 is operating on data members of the class that assocName refers to. Also, the extractItems refer to members of the EXTERNAL class, i.e. the same as predicate2.

Example: a way to get all stars that have been observed with sub-arcsecond seeing is

SELECT modelCounts
FROM ( 
    SELECT obj FROM field WHERE psfWidth[2] < 1
     )
WHERE
  objType == AR_OBJECT_TYPE_STAR

Nested select queries are very well suited to speed up your query. Querying on fields and segments is very fast since there is only very few of them. If you select only a few fields or segments, it is usually much faster to use a nested select to get at the objects contained in them.

Keywords

There are several special-purpose keywords in the query language for macros, coordinate conversions and SXQL syntax.

SXQL Keywords
SELECT	Begin select block
FROM	Specify class or nested select
WHERE	End select block, followed by predicate
UNION	Logical OR two select blocks
INTERSECT	Logical AND two select blocks
DIFFERENCE	Logical AND NOT two select blocks

Coordinate Specifiers
J2000	Standard ra/dec coordinate system - use macros RA() DEC()
GALACTIC	Galactic coordinate system - use macros GL() GB()
SUPERGALACTIC	Supergalactic coordinate system - use macros SGL() SGB()
SURVEY	Survey coordinate system - use macros LAMBDA() ETA()
ECLIPTIC	Ecliptic coordinate system - use macros ECL() ECB()

SXQL language keywords and macros
x BETWEEN a AND b	Translates to $(x > a\;\; \&\&\;\; x < b)$. Can be used with coordinate macros.
STR(expression,n,d)	Only in the extractor specification: Format output of expression. n specifies the width and d the number of decimal places.
COUNTASSOC(association)	Only in the extractor specification: Return the number of associations
EXIST(association)	TRUE if an association from an object to another one exists.
EXIST(association,n)	TRUE if at least n associations from an object to another one exist.
OID( (run,camcol,rerun,field,objID), (run,camcol,rerun,field,objID), ...)	Macro selecting objects given by their SDSS id, which is run number, camera column, rerun, field and object id. You can specify between 1 and 10000 ids in a list. Currently this works only on the sxPhotoObj class (i.e. full photometric catalog objects only).
PROX(coordspec, ra, dec, diameter)	Macro returning objects within the area centered around the given coordinate with diameter specified by 'diameter'. Diameter has to be specified in arcminutes, not greater than 10. Coordspec can be any of the keywords given above.
POLY(coordspec, ra_1, dec_1, ..., ra_N, dec_N)	Macro returning objects within the polygonal area given by the N coordinate pairs in the given coordinate system.

SXQL Math Functions
SIN(expression)	Calculate sine function of expression. Expression can be any mathematical expression.
COS(expression)	Calculate cosine function of expression.
SQRT(expression)	Calculate square root of expression.
LN(expression)	Calculate natural logarithm of expression.
LOG(expression)	Calculate 10-based logarithm of expression.
EXP(expression)	Calculate natural exponentiation of expression.

Operators

Here is a table of operators currently supported by the Query Language

Arithmetic Operators
+	Addition
-	Subtraction
*	Multiplication
/	Division

Logical Operators
&& or AND	Logical AND
|| or OR	Logical OR
! or NOT	Logical NOT

Number Comparison
= or ==	Equals
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
!=	Not equal

Bitwise Operators
&	Bitwise AND
^	Bitwise XOR
|	Bitwise OR

Dereferencing
. or ->	Follows association links