Common Interface for
Astronomical Data ServiceYuji Shirasaki
National Astronomical Observatoryof Japan, JVO

VOQL+DAL Joint session

Contents• Objective of this talk
• Concept for adapting ADQL query interface to aGeneral data service• JVO SkyNode experimental implemenation– SkyNode Toolkit– Application to the science use case

• Required Specification– Data Service Classification and Table Data model– Definition of minimum subset of ADQL
– Minor update on ADQL
– Standard on usage of VOTable– Access to the Object data type

Objective of this talk• From a Simple Protocol to an Intelligent Protocol– SIAP and SSAP are simple parameter-based queryprotocol.
– ADQL is a SQL-based query protocol and have acapability to describe more complicated query conditions.

• What is the merit ?

– Multiple regions condition, directly or by VOTable– Homogeneous data access to both of the catalog andobservational data
– Query to the multiple data services (Portal)• Effect on the current SIAP and SSAP spec.

– Introducing the ADQL to the DAL service does notaffect to the data model of a returned VOTable.
– Just introduce an additional query interface to the DALservice.

Concept for adapting SQL to Image Database.
I have shown that image query can be described in SQL syntax byintroducing virtual column concept.
SIAP QueryPOS
SIZE
FORMAT
INTERSECT...

http://jvo.nao.ac.jp/Image?
Pos=23,+30&Size=1.0&Format=image/fits&
INTERSECT=OVERLAPS[mandatory][mandatory][mandatory][option]

SIAP parameters and returned metadata
are taken as columns of the virtual table.

Image Query on Virtual Table
Pos

Size

FormatImageURL(23,+30)

1.0

image/fits

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

0.3

image/jpeg

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

...

...

...

...

Select imageURL
From imageTable
Where Pos = Point(23,+30)
and Size = 1.0 andFormat = ‘fits’
This is a virtual table which
has infinite number records.

Development of the JVO SkyNode Toolkit• Primary aim:
– to provide a reference implementation for every kind ofdata service which uses ADQL & VOTable interface• Supported DBMS:– aimed to be independent on the type of DBMS
– The only requirement is availability of JDBC driver.
– but still PostgreSQL native SQL (copy command) is used...

• Restrictions:– Not all the ADQL syntax are supported.
– String representation of ADQL is JVOQL.

• Experimental Release:– http://jvo.nao.ac.jp/download/skynode-toolkit/

Software used
• Tomcat 4.1.31 ---- servlet container• Axis 1.2RC1 ---- web service engine• J2SDK 1.4.2 ---- Java compiler & library• Ant 1.6.1 ---- Java-based build tool
• JavaCC 3.2 ---- parser generator for Java
• JAXB v1.0.3-b18-fcs ---- XML Java conversion• PostgreSQL 7.4.7 ---- DBMS
• Java HTM library (JHU) ---- spherical indexing• Java FITS library (HEASARC) ---- FITS IO lib• ...ArchitectureJVO SkyNode Toolkit Architecture:QuerySkyNode

Client-Request
QuerySkyNode classSQLconverts the client-request
SQL
executedon thebackend
DBMS
VOTableSQL to the SQL which is aquery actually executed on
the backend DBMS.
Service provider may extends
this class and override somemethods to configure for
their database.

: DataFormatter: FunctionColumn
User view table may have afunction type column whichis calculated according tothe query result of theother columns.

Column• name• dataType• unit• UCD• ...

ColumnMetadata
TableTable• column1• column2• ...

User View
This is a virtual table
exposed to the world.
This table defines what
data is available andwhich search parameters
can be specified.

regionTable

regionHTM• queryID• regionID
• ra
• dec
• size1• size2• regionExpr• queryID• regionID• htmLow• htmUpp

Real Tables

mainTable

mainHTM• dataID• ...

• dataID• htmIndex

This is acollection oftables actuallyqueried.

Space Region is approximatelyexpressed as a collection of
HTM index and the original
region condition is replaced bythe condition expressed in
HTM index ranges.

Image Data Query by ADQL
select accessURL: QueryImage
from virtualTable
where region overlaps
Box ( (280, +20), 1.0, 1.0)
and filterName = ‘W-C-RC’ (1) Insert region
and format = ‘image/fits’ data

virtualTable

User View• region• filterName• format• accessURL

 regionTabel.regionExpr
 frameTable.filterName
 format.format
 FunctionColumn.accessURL()(3) Create SQL for real tables
select *from (select * from regionTable queryID = 1) t1,natural join regionHTM t2,(4) Executenatural join frameHTM t3,natural join (select *Queryfrom frameTable
where filterName = ‘W-C-RC’) t4,
natural join (select *from format
where format = ‘image/fits’) t5

VOTable: DataFormatter(6) Query Result in
VOTable: FunctionColumn(5) Query ResultColumn• name• dataType• unit• UCD• ...

ColumnMetadataTable(2) Insert HTM
regionTable index for the• queryID
specified region• regionID• ra• dec• size1• size2• regionExpr

regionHTM• queryID• regionID• htmIndex

Real Table

frameTable

frameHTM

format• frameID• filterName• ...

• frameID• htmIndex• frameID• formatImage Data Xmatch Query with VOTable
select img.accessURL:QueryImage
from EXT::0 vot, virtualTable img
Xmatchwhere img.region overlaps
Box ( (vot.ra, vot.dec), 1.0, 1.0)
(1) Insert VOTableand filterName = ‘W-C-RC’data and region data
and format = ‘image/fits’

virtualTable

User View• region• filterName• format• accessURL

 regionTabel.regionExpr
 frameTable.filterName
 format.format
 FunctionColumn.accessURL()(3) Create SQL for real tables

select *from (select * from regionTable queryID = 1) t1,
natural join regionHTM t2,
natural join frameHTM t3,(4) Executenatural join votable t4,
Querynatural join (select *from frameTable
where filterName = ‘W-C-RC’) t5,
natural join (select *from format
where format = ‘image/fits’) t6: DataFormatter

VOTable(6) Query Result in
VOTable: FunctionColumn(5) Query ResultColumn• name• dataType• unit• UCD• ...

ColumnMetadata
Table(2) Insert HTM
regionTable index for the• queryID
specified region• regionID
• ra
• dec
• size1• size2• regionExprvotable• regionID
• ra
• dec
•…

regionHTM• queryID• regionID• htmIndex

Real Table

frameTable

frameHTM

format• frameID• filterName• ...

• frameID• htmIndex• frameID• formatScience Use CaseQSO/Galaxy
Clustering Study

Work flow for studying theQSO/Galaxy clustering
1. Select QSO coordinates from the QSO catalog Query to the Skynode Catalog Database.
2. Search deep imaging data which covers the QSO
regions  Query to the Skynode of Subaru Image
Database
3. Create catalog from the imaging data  Invoke theSExtractor Web service.
4. Estimate the distance to the objects around the QSO
 Invoke the HyperZ Web service
5. Try Clustering Analysis  Invoke the clustering analysis
web service.

Query Flow Chart(1)

SELECT cat.*, img.*
FROM jvo.misc.qso_veron_2003 cat,
jvo.test.smoka.spcam_img img
WHERE POINT(cat.raj2000, cat.dej2000)
WITHINCIRCLE((189.20625, 62.216111), 0.10)
AND cat.v_mag < 20
AND img.format = 'image/fits'
AND img.region OVERLAPS
BOX((cat._raj2000, cat._dej2000), 0.02, 0.02)

(1)

(2)

JVO
Portal(4)

VOTableQSO+Image
(5)

1.
2.
3.
4.
5.

User request SQLSQL to the QSO catalog DB
QSO coordinates in VOTable
SQL & VOTable to Image DB
AccessURL for images around QSOs

(2)

SELECT cat.*
FROM jvo.misc.qso_veron_2003 cat
WHERE POINT(cat.raj2000, cat.dej2000)
WITHINCIRCLE((189.20625, 62.216111), 0.10)
AND cat.v_mag < 20

(3)

SkyNodeQSO
CatalogDB

VOTableQSO list
SkyNode(3)

Subaru(5) SuprimeCam
SELECT vot.*, img.*(4)
Image DB
FROM EXT::0 vot,VOTable
jvo.test.smoka.spcam_img img
QSO list
WHERE img.format = 'image/fits'
AND img.region OVERLAPS
BOX((vot.raj2000, vot.dej2000), 0.02, 0.02)

QSO-Galaxy Search

QSO Search Plot

Required Standard

Table Data Model for Astro-Data Service• Query is described in SQL construct (ADQL).
• Query result is returned in a tabular form (VOTable).
• To write a SQL, table structure (column name) need to be known inadvance.
• Table Data Model for each data service are required.
Table datamodel
definesrequiredcolumns andrecommended
columns

Top level data table classes

Catalog– spaceFrame– srcPosition– brightness– ...

The data service musthave an interface toreturn the class name of
the table and column
metadata.

Image Cutout
ServiceIf spaceFrame is thesame for all the rows,
it may be returned as
PARAMETER in
VOTableObs. Data Each subclass inherits the– spaceFrame– dataFormat– accessURL– ...

Image Data

Spectrum Data

– spaceFrame– dataFormat– accessURL– region– spaceFrame– dataFormat– accessURL– targetPosition

Image AtlasService

property of its super class

3D Cube Data
ServiceImage Mosaic

Pointed Image

ServiceArchive Service

Photon List
Data ServiceColumn metadata• All the column must have the following attributes:– name, datatype, unit, arraysize, width, precision, ucd,utype. These are used in the FIELD element attributes ofVOTable. A coordinate column must have ancoordinateFrame attribute.
– column name can be specified by a data provider.
– datatype must follows the VOTable specification.
– expression of unit must follows the VOTable spec.
– utype is supplied by VO data model. For columns notdefined in the data model, utype is not required.
– appropriate ucd is assigned by the data provider.

Minimum Requirement for Query Spec.
Current ADQL:

describe most of the SQL92 syntax and some astro-extension
many elements (33) & many data types (69), hard to build a dataservice. It is helpful to define minimum subset ADQL spec as abase line

• SelectionItemType (13 sub types in ADQL 0.8)
– Only AliasSelectionItem and ColumnReferenceType• FromTableType (3 sub types in ADQL 0.8)
– Only TableType• SearchType (16 sub types in ADQL 0.8)
– Only IntersectionSearchType and ComparisonPredType– Region Search Criterion can be specified by ComparisonPredType.

Proposed ADQL Schema structure• STC (Full) schema imports STC (Core) schema• ADQL (Core) imports STC (Core)• ADQL (Full) imports ADQL (Core) and STC (Full)• ADQL (Core) and STC (Core) should not be changed. These are criticalstandard for interoperability.
• Each schema has a difference namespace.

ADQL (Full)schemaADQL (Core)
schemaSTC (Full)schemaSTC (Core)
schemaSubstitution of Column Name
by UCD or UType• Without knowing the column names, we cannot write anSQL…
• Possible Solution  use UCD or Utype for describing thequery condition and selection list.
• Instroduce “ucd” and “utype” attributes to thecolumnReferenceType<Item xsi:type=“columnReferenceType” Name=“ra” Table=“qso”/><Item xsi:type=“columnReferenceType” ucd=“pos.eq;src” Table=“qso”/><Item xsi:type=“columnReferenceType” utype=“Target.pos” Table=“qso”/>

ComparisonPredType as a “must-support”
ConditionType• A basic SkyNode MUST support “comparisonPredType” .
• A basic SkyNode MAY support the other searchType.
• A basic SkyNode MUST recognize an unsupported
searchType as a “trueType”.
• A basic SkyNode MUST support the following construct:– <STC_columnName> <STC_operator> <STC_searchLocationType>– STC_operator ::= within, overlaps, outside

STC-type column must becompared with an object ofSTC seachLocationType– point within STC(‘Circle ICRS 200 –20 2.0’ ) (Catalog Query)– region overlaps STC(‘Circle ICRS 200 –20 2.0’) (Image Query)– spectrum overlaps STC(‘SpectralInterval A 4000 7000’) andobservationTime within STC(‘TimeInterval 2004-05-01 2004-05utype defines31’)(Specturm Query)which column
represents STC.

ADQL-x version ofpoint within ‘Circle ICRS 200 –20 2.0’

It might be convenient todefine substitution types
for frequently used framessuch as <SpaceFrame
xsi:tye=“ICRSFrameType”/><Condition comparison=“within” xsi:type=“comparisonPredType"><Arg Table=“t” utype=“src.position” xsi:type=“columnReferenceType”/><Arg xsi:type=“searchLocationType"><AstroCoordSystem ID=“ICRS”><SpaceFrame><ICRS/><BARYCENTER/><SPHERICAL coord_naxes=“2”/></SpaceFrame></AstroCoordSystem><AstroCoordArea ID=“SearchRegion” coord_system_id=“ICRS”><Region><Circle unit=“deg”> <Center>200 –20</Center> <Radius>2.0</Radius></Circle></Region></AstroCoordArea>
Which frame should be defined as</Arg>a “must be supported” frame</Condition>

Returned VOTable• FIELD id  If alias name is specified“adql:aliasName”, otherwise“adql:tableAliasName.columnName”.
• TABLE id  table alias name
• FIELD & TABLE name  free, client app may use it• STC metadata (CoordSys) for STC column– If each record in the STC column has a different STC
metadata, STC metadata column must be included.
Otherwise STC metadata must be included asPARAMETER.

• Some of the columns must always be included in theoutput VOTable. These columns are defined in
SIAP, SSAP and SXAP document.

How to treat object data
• Spectrum metadata data has a structured data
type. Access method to an object data is defined in
SQL99. Example is:
Select t.*
From spectrumTable t
Where t.target.position within STC(‘ … ‘)(*) target is a column name, position is a field name ofTarget object. Always specify alias table name toexpress a column.

• Most of the recent RDB support object data type.
• If you don’t want to use an object data type of RDB,
relate one object type to one table or just have aflat table, then emulate object datatype column.

Client & Server roll separation
Skynode draft should clearly identify which functionalities
must be on the server.
SkyNode client

SkyNode server
Basicunit conversion ?

coordinate frame conversion  ?
?  conversion ucd, utype tocolumn nameplanet location  (ra, dec)

Extendedcross match (x,y,z) & (ra,dec)
execution plan

Require reference implementation of client too.