Standard Query Language for VO-- Unification of Catalog query (ADQL) andImage query (SIAP),or, in general, Observation Data --Yuji SHIRASAKI

National Astronomical Observatory of Japan

Integration of SkyNode and SIAP node
We need to make standards not only for query language but also supported
protocol, the way of metadata query and specification of the returned VOTable.
SIAP Node

SkyNodeUnifiedSkyNode

DataImage

CatalogCatalog/Image/Spectrum/…

QueryLanguage

Parameter, name ofthe parameters aredefined.

SQL, no restriction
to the name ofcolumn

SQL, define aguideline for required
columns, syntax

ProtocolHttp/Get/Parameter Http/Put/Soap

Http/Put/Soap ?

Metadata
Query“FORMAT =Tables and Columns
METADATA”
interface arereturns supported
defined.
parameters andcolumns it will return.

Define standard
metadata tables
which have metadataof table and column.
Query is describedwith SQL.

Query
OutputVOTable, Some
metadata must beincluded.

VOTable, FIELD
element should have
metadata stored in
the metadata table.

VOTable, no
specification for thecontentIn the last IVOA meeting @ Cambrige ...
I have shown that image query can be described in SQL syntax by introducing
virtual column concept.
SIAP query by HTTP/Get parameter request

SIAP Paremters

POS[mandatory] http://jvo.nao.ac.jp/Image?
SIZE[mandatory] Pos=34.3,-5.11&Size=0.01&Format=VOTable
FORMAT[mandatory]
SIAP parameters and returned metadata
INTERSECT [option]
are taken as columns of the virtual table.
...
Virtual Columns
Pos

Size

FormatImageURL(23,+30)

1.0fits

http://jvo.nao.ac.jp/Image?id=124214(23,+23)0.3jpeg

http://jvo.nao.ac.jp/Image?id=124215

...

...

...

...

Select imageURLFrom naoj:image
Where Pos = Point(23,+30)
and Size = 1.0 andFormat = ‘fits’“Pos” and “Size” columns haveinfinite number of value, so thistable is a virtual table.

Column Type Allowed in the SkyNode
A column is classified into four types:• Ordinary Column: column of the relation table• Constant Value Column: constant value of the table– e.g. limiting magnitude of the observations relevant tothe table, typical error of the coordinate,…
Select limitingMagnitude
From galaxy• Enumeration Value Column:• e.g. image data formats, spectrum bandpass names.
• Virtual Column: continuous value of the table– e.g. parameter for specifying the image region,Select imageURL
From imageWhere region = Box((29,+10),1.0,1.0)

Data type allowed in the SkyNode• VOTable Data type
– boolean, int, long, float, double, character(*) …

• Space Data Type– SpacePoint, SpaceRegionCirce, SpaceRegionBox• Range Data Type– SpectrumRange, TemporalRange• Data Access URL Data Type– ImageAccess, SpectrumAccess, …

Metadata tables• SkyNode must have metadata tables which describe
attributes of the tables and columns of the SkyNode, and aclient can query to the metadata tables.
• “Tables” metadata table has the following columns:
tableId, tableName, tableType, description• “Columns” metadata table has the following columns:
columnId, columnName, tableId, dataType, coordinate,unit, ucd, columnType, precision, description

Guideline on Required Columns
We need to define a guideline what columns are “Required” for realizing
interoperability for each data type. This is just an example for object
catalog table and Image data table.
ColumnName

UCD (TBD)

Catalog

Image

Comment

Pos

VOX:POS_EQ

Req

Req

Center coordinate of the search regin in RA DEC
frame. (degree)

DeltaRa

VOX:POS_EQ_DE
LTA_RAX

Req

Box size of the search region in RA coordinate.
(degree)

DeltaDec

VOX:POS_EQ_DE
LTA_DECX

Req

Box size of the search region in Dec coordinate.
(degree)

Radius

VOX:POS_RADIU
SX

RecRadius of the search region. (degree)

DataFormat

VOX:DATA_FOR
MATX

Req

Data format of the observational data. (e.g.
FITS, JPEG, HTML...)

OutputFormatVOX:OUTPUT_FO
RMAT

Req

Req

Data format of the search result. (VOTAble,CSV, HTML, ...)

Region

VOX:POS_REGIONX

Rec

Region of Interest in Sky

SpectrumRange

VOX:SPECTRUM_RANGE

Opt

Opt

Region of Interest in Spectrum

TemporalRange

VOX:TEMPORAL_RANGE

Opt

Opt

Region of Interest in Time

Output of Image Query• According to the current SIAP specification, the following
metadata must be included in the output VOTable.
ImageTitle, Coordinate, NAXES, NAXIS, ImageScale,ImageFormat, ImageAccessRef.
• With the SQL specification, however, to get thosemetadata we must explicitly specify them in the “Select” list.
• I propose not to specify the content of output VOTable,
instead specify that
“Image data table must have columns related toImageTitle, Coordinate, ... .”SQL Syntax Specification for VO• VOQL should have scalable syntax:– Small size DBs  very simple syntax for easy implementation.
– Large size DBs  sophisticated syntax for efficient data search.
 We propose to define a minimal basic syntax as a standard andoptional syntax as an extension.

hybrid syntax structure
Ext. nExt. 6

Ext. 5Ext. 1

Basic
Ext. 4Ext. 2Ext. 3“Basic” syntax must be implemented
by all the VO data service.
Any “Extension-n” syntax may be
implemented by each VO dataservice.
Registry will have information which
extensions are implemented at eachdata service, or data service itself
returns the information by“voqlSpec” interface, or querying tothe metadata table. (TBD)

Basic Specification
Select ColumnName [[AS] AliasName] , … | *FromTableName [[AS] AliasName]
Where Condition [AND Condition]• Only column name or “*” is specified in the selection list.
• An algebraic expression is not supported.
• Only one table is specified at “From” part.
• Table name and Column name can have alias name.
• Comparison operators: =, <, >, >=, <=, <>, LIKE, BETWEEN• Logical operator: AND, NOT (OR is not supported.)• Region Comparison operator: =, within, contains, overlaps• Functions: Distance(), Point(), Circle(), Box()

Region Comparison Operator[NOT] <SpacePoint> <RegionCompOper> <SpaceRegion>[NOT] <SpaceRegion> <RegionCompOper> <SpacePoint>
Region Comparison

Meaning

A within B

Point A is within Region B.

B contains A

Region B contains Point A.

NOT A within B

Point A is outside Region B.

NOT B contains A

Region B excludes Point A.

FigureA

B<SpacePoint> ::= [Point](x, y , [‘frame’])e.g. Point(13.2,-34.5), Point(32.1, -12.5, ‘ICRS’), (34.7, -26, ‘Gala’)<SpaceRegion> ::= [Circle](<SpacePoint>, radius)| [Box](<SpacePoint>, xsize, ysize)e.g. Circle((23.7,-0.3), 2.3), Box((58.3,+1.2), 3.3, 3.3)

Region Comparison[NOT] <SpaceRegion> <RegionCompOper> <SpaceRegion>
RegionComparison

Meaning

A=B

Region A is thesmallest region which
overlaps the largest
part of B.

A overlaps B

Region A is thesmallest region which
overlaps B.

A contains B

Region A is thesmallest region whichcontains B.

A within B

Region A is thelargest region whichis contained in B.

Image Atlas Data
ServiceA

Image Cutout
ServiceB

Same as A = B

Same as A = B

Example for basic spec. VOQL
Select catalog data for the specified region.
SelectFrom
Wherera, dec, mag_r
can be omitted if it is trivial
galaxyPoint(ra, dec) within Circle((24.3, +5.0), 2.0))
and mag_r < 24

Select image of the specified region and the corresponding
filter name.
Selectfilter, imageURL

From

imageData

Whereregion = Box((24.3, +5.0), 0.2))

c.f.

“Pos = Point(24.2,5.0) and DeltaRa = 0.2
and DeltaDec = 0.2” is also valid

http://jvo.nao.ac.jp/imageData?POS=24.2,5.0&SIZE=0.2

Extension Syntax of VOQL
Ext.1 An algebraic expression in “Select” and “Where” part.
Ext.2 Multiple tables in “From” part.
Ext.3 Join predicate at “From” part.
Ext.4 Logical operator “OR”.
Ext.5 Data type Extension.
Ext.6 VOTable in “From” part and cross match with VOTables.
Ext.7 UCD (Portal). UCDs used as representative of column name areresolved from the column metadata.
Ext.8 Unit (Portal). Unit of the column is obtained from the column
metadata and the value is translated accordingly.
Ext.9 Use of Identifier for Table name (Portal): To identify a table inthe VO uniquely.
Ext.10 Omission of From part (Portal): Tables to be searched aredetermined from the condition described at Where part.

Use of Identifier for Table Name
<TableName> ::=[AuthorityName:][CatalogDataPath.]TableName
e.g. “galaxy” table of the data resource “ivo://naoj/subaryu/spcam/” isspecified as “naoj:subaru.spcam.galaxy”.

External Table in From part
<ExternalTableName> ::=EXT:( <Number> | [ResourceName.] TableName )e.g. Search images corresponding to objects listed in a VOTable.
Select vot.ra, vot.dec, img.imageURL
Fromimage as img, EXT:selectedGalaxy as vot
Where img.region = Box((vot.ra, vot.dec), 0.1, 0.1)

Xmatch Predicate
In addition to the ADQL 0.8.1 specification, column namecan be specified.
Xmatch (table1, table2.(ra, dec), !table3, … , sigma)
e.g. Try cross match between sdss and twomass catalog.
Select o.objId, o.ra, o.r, o.type, t.objId
From
SDSS.photoPrimary o, TWOMASS.PhotoPrimary t
Where Xmatch(o.(ra, dec), t.(ra, dec), 3.5)

Example for extension syntax
Image query using catalog table.
Select cat.ra, cat.dec, img.filter, img.ImageURL
FromgalaxyCatalog cat, imageTable img

Where img.region = Circle((cat.ra, cat.dec), 30 [arcsec])
and Point(cat.ra, cat.dec) within Circle((234, +10), 30 [arcmin])Image query using external VOTable file.
Select img.filter, img.ImageURL
From

ext:galaxyCatalog cat, imageTable img

Where img.region = Circle(cat.ra, cat.dec)“galaxyCatalog” table in an external file (VOTable)