Getting Started

Documentation

Glish

Learn More

Programming

Contact Us

Version 1.9 Build 1556

News	FAQ
Search	Home

Subsections

• Selection examples
  • Reference table results
  • Plain table results
• Modification examples
• Table creation examples
• Calculation examples

Examples

Selection examples

Reference table results

SELECT FROM mytable WHERE column1 > 0
selects the rows in which the value of column1 > 0

SELECT column0,column1 FROM mytable
selects 2 columns from the table.

SELECT column0,column1 FROM mytable WHERE column1>0
is a combination of the previous selections.

SELECT FROM [SELECT FROM mytable ORDERBY column0 DESC]
WHERE rownumber()< =10
selects the 10 highest values of column0.

SELECT FROM mytable ORDERBY column0 DESC LIMIT 10
is a more elegant solution using the newer LIMIT clause.

SELECT FROM mytable ORDERBY column0 DESC GIVING outtable
SELECT FROM outtable WHERE rownumber()< =10
is the SQL-like solution for the previous problem. It is less elegant, because it requires two steps.

SELECT FROM mytable WHERE column0 IN
[SELECT column0 FROM mytable ORDERBY column0 DESC][1:10]
is similar to above, but can select more values if there happen to be several equal values.

SELECT FROM some.MS WHERE
near(TIME/(24*3600),MJD(1999/03/30/17:27:15))
selects the rows with the given time from a MeasurementSet.
Note that the TIME is stored in seconds; the division converts it to days.

SELECT FROM some.MS where TIME/(24*3600) in
[{MJD(1999/03/30/17:27:15),MJD(1999/03/30/17:29:15)}]
selects the rows in the given closed time interval.

SELECT FROM some.MS where TIME/(24*3600) in
[MJD(1999/03/30/17:27:15),MJD(1999/03/30/17:29:15)]
selects the rows having one of the given times.
Note the difference with the previous example where an interval was given. Here a set of two individual time values is given.

SELECT FROM resource.table WHERE
any(PValues == pattern('synth*'))
selects the rows in which an element in array PValues matches the given regular expression.

SELECT FROM table WHERE ntrue(flags) >= 3
selects rows where at least 3 elements of array flags are set.

SELECT FROM book.table WHERE nelements(author) > 1
selects books with more than 1 author.

SELECT FROM my.ms WHERE any(ANTENNA1==[0,0,1] && ANTENNA2==[1,3,2])
selects the antenna pairs (baselines) 0-1, 0-3, and 1-2.
Note that the two comparisons result in a boolean vector. If a bool in the and-ed vectors is true, that baseline matches.

SELECT FROM mytable WHERE
cos(0d1m) < sin(52deg) * sin(DEC) + cos(52deg) * cos(DEC) * cos(3h30m - RA)
selects observations with an equatorial position (in say J2000) inside a cone with a radius of 1 arcmin around (3h30m, 52deg). To find them the condition DISTANCE< =RADIUS must be fulfilled, which is equivalent to COS(RADIUS)< =COS(DISTANCE).

SELECT FROM mytable WHERE
[RA,DEC] INCONE [3h30m, 52deg, 0d1m]
does the same as above in an easier (and faster) way.

SELECT FROM mytable WHERE object == pattern("3C*") &&
[RA,DEC] INCONE [3h30m, 52deg, 0d1m]
finds all 3C objects inside that cone.

select from MY.MS where DATA_DESC_ID in
[select from ::DATA_DESCRIPTION where
SPECTRAL_WINDOW_ID in [0,2,4] giving [ROWID()]]
finds all rows in a measurement set matching the given spectral windows.

select from MY.MS where TIME in
[select from ::SOURCE where REST_FREQUENCY < 180000000.
giving [TIME-INTERVAL/2 =:= TIME+INTERVAL/2]]
finds all rows in a measurement set observing sources with a rest frequency less than 180 Mhz.

select from MS,
[select from MS where sumsqr(UVW[1:2]) < 625] as TIMESEL
where TIME in [select distinct TIME from TIMESEL]
&& any([ANTENNA1,ANTENNA2] in [select from TIMESEL giving
[iif(UVW[3] < 0, ANTENNA1, ANTENNA2)]])
finds all antennas which are shadowed at a given time.
The query in the FROM command finds all rows where an antenna is shadowed (i.e. its UV-distance less than 25 meters) and creates a temporary table. This selection is done in the FROM command, otherwise two 2 equal selections are needed in the main WHERE command.

select from MS
where DATA_DESC_ID in [select from ::DATA_DESCRIPTION
where SPECTRAL_WINDOW_ID in [select from ::SPECTRAL_WINDOW
where NET_SIDEBAND==1 giving [ROWID()]] giving [ROWID()]]
finds all rows in the MeasurementSet with the given NET_SIDEBAND.
The MeasurementSet uses a table to map spectral-window-id to data-desc-id. Hence two nested subqueries are needed.

Plain table results

SELECT column0+column1 FROM mytable
creates a table of 1 column with name Col_1. Its data type is on the expression data type.

SELECT column0+column1 Res I4 FROM mytable
creates a table of 1 column with name Res. Its data type is 4 byte signed integer.

SELECT colx colx R4 FROM mytable
creates a table of 1 column with name colx. The sole purpose of this selection is to convert the data type of the column.

SELECT means(DATA,2) AS DATA_MEAN C4 FROM my.ms
creates a table of 1 column with name DATA_MEAN. Column DATA in a AIPS++ MeasurementSet is a 2-dimensional array with axes polarization and frequency. This command calculates and stores the mean in each polarization. If no data type was given, the means would have been stored as double precision complex (which is the expression data type).

Modification examples

update MY.MS set VIDEO_POINT=MEANS(DATA,2) where isdefined(DATA)
sets the VIDEO_POINT of each correlation to the mean of the DATA for that correlation. Note that the 2 indicates averaging over the second axis, thus the frequency axis.

update MY.MS set FLAG_ROW=T where isdefined(FLAG) && all(FLAG)
sets FLAG_ROW in the rows where the entire FLAG array is set.

delete from MY.MS where FLAG_ROW
deletes all flagged rows.

insert into MY.MS select from OTHER.MS where !FLAG_ROW
copies all unflagged rows from OTHER.MS to MY.MS.

insert into MY.MS/DATA_DESCRIPTION
(SPECTRAL_WINDOW_ID,POLARIZATION_ID,FLAG_ROW)
values (1,0,F)
adds a row to the DATA_DESCRIPTION subtable and initializes it.

Table creation examples

create table mytab (col1 I4, col2 I4, col3 R8)
creates table mytab of 3 scalar columns.

create table mytab
creates an empty table.

create table mytab colarr R4 ndim=0
creates a table of 1 array column with arbitrary dimensionality.

create table mytab colarr R4 [shape=[4,128], dmtype='TiledColumnStMan']
creates a table of 1 array column with the given shape. The column is stored with the TiledColumnStMan storage manager using its default settings.

create table mytab colarr R4 shape=[4,128] dminfo [TYPE='TiledColumnStMan', NAME='TCSM', SPEC=[DEFAULTTILESHAPE=[4,32,64]], COLUMNS=['colarr']]
creates a table of 1 array column with the given shape. The column is stored with the TiledColumnStMan storage manager using the given settings.

Calculation examples

calc 1+2
uses TaQL as a desktop calculator.

calc 7-Apr-2007 - 20-Nov-1979
calculates the number of days between these dates.

calc (1-1-2006 - 1-1-1950)%365
calculates the number of leap days in this time span.

calc sum([select from MY.MS giving [ntrue(FLAG)]])
determines the total number of flags set in the measurement set.

calc from [select from MY.MS where ANTENNA1==0]
calc mean(abs(DATA))

calculates for each row the mean of the data for the selected subset of the measurement set.

calc mean([select from MY.MS where ANTENNA1==0
giving [mean(abs(DATA))]])

looks like the previous example. It, however, calculates the mean of the mean of the data in each row for the selected subset of the measurement set.

calc max([select from MY.MS where isdefined(DATA)
giving [max(abs(VIDEO_POINT-MEANS(DATA,2)))]])

shows the maximum absolute difference between VIDEO_POINT of each correlation and the mean of the DATA for that correlation. Note that the 2 indicates averaging over the second axis, thus the frequency axis.