Common Interface for Astronomical Data Service Yuji Shirasaki

Common Interface for Astronomical Data Service Yuji Shirasaki National Astronomical Observatory of Japan, JVO VOQL+DAL Joint session

JVO Skynode Implementation Experience Yuji SHIRASAKI National Astronomical Observatory of Japan, JVO VOQL session

Contents • Objective of this talk • Concept for adapting ADQL query interface to a General data service • JVO Sky. Node experimental implemenation – Sky. Node 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

Contents • Introduction of JVO Sky. Node toolkit – used free software – architecture of JVO skynode • Problems in implementation and interoperability – – – XML Java deserialization problem in AXIS Namespace problem ADQL, VOTable, STC Usage of VOTable id, name attributes. . . Complexity of ADQL and STC object. . . • Proposal – Simplify the ADQL and STC Define minimum subset of ADQL and STC and freeze them (never update, never change the namespace) – VOTable transfer attachment or URL – Standardize the error message (not presented, as a future work) –. . .

Objective of this talk • From a Simple Protocol to an Intelligent Protocol – SIAP and SSAP are simple parameter-based query protocol. – ADQL is a SQL-based query protocol and have a capability 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 and observational data – Query to the multiple data services (Portal) • Effect on the current SIAP and SSAP spec. – Introducing the ADQL to the DAL service does not affect to the data model of a returned VOTable. – Just introduce an additional query interface to the DAL service.

Concept for adapting SQL to Image Database. I have shown that image query can be described in SQL syntax by introducing virtual column concept. SIAP Query POS SIZE FORMAT INTERSECT. . . http: //jvo. nao. ac. jp/Image? Pos=23, +30&Size=1. 0&Format=image/fits& INTERSECT=OVERLAPS [mandatory] [option] SIAP parameters and returned metadata are taken as columns of the virtual table. Image Query on Virtual Table Pos Size Format Image. URL (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 image. URL From image. Table Where Pos = Point(23, +30) and Size = 1. 0 and Format = ‘fits’ This is a virtual table which has infinite number records.

Development of the JVO Sky. Node Toolkit • Primary aim: – to provide a reference implementation for every kind of data 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 Postgre. SQL 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/

What can be done with the toolkit ? • Catalog data query • Catalog data cross match query using VOTable • Image data query • Image data cross match query using VOTable • Spectrum data is not supported, but the frame work will be the same as that of Catalog and Image. next work • You can build a sample Sky. Node service.

Software used • • • Tomcat 4. 1. 31 ---- servlet container Axis 1. 2 RC 1 ---- web service engine J 2 SDK 1. 4. 2 ---- Java compiler & library Ant 1. 6. 1 ---- Java-based build tool Java. CC 3. 2 ---- parser generator for Java JAXB v 1. 0. 3 -b 18 -fcs ---- XML Java conversion Postgre. SQL 7. 4. 7 ---- DBMS Java HTM library (JHU) ---- spherical indexing Java FITS library (HEASARC) ---- FITS IO lib. . .

Architecture

JVO Sky. Node Toolkit Architecture : Query. Sky. Node Client-Request Query. Sky. Node class SQL converts the client-request SQL executed on the backend DBMS VOTable SQL to the SQL which is a query actually executed on the backend DBMS. Service provider may extends this class and override some methods to configure for their database. User view table may have a function type column which is calculated according to the query result of the other columns. • • • Column name data. Type unit UCD. . . • column 1 • column 2 • . . . • query. ID • region. ID • ra • dec • size 1 • size 2 • region. Expr • data. ID • . . . Column Metadata Table User View This is a virtual table exposed to the world. This table defines what data is available and which search parameters can be specified. region. Table main. Table : Data. Formatter : Function. Column Table region. HTM Real Tables main. HTM • data. ID • htm. Index • query. ID • region. ID • htm. Low • htm. Upp This is a collection of tables actually queried. Space Region is approximately expressed as a collection of HTM index and the original region condition is replaced by the condition expressed in HTM index ranges.

Catalog Data Query by ADQL select ra, dec, mag from catalog. Table where Point(ra, dec) within Box ( (280, +20), 1. 0) and mag < 24 : Query. Catalog catalog. Table (1) Insert region data • object. ID • ra • dec • mag • … User View (3) Create SQL for real tables select * from (select * from region. Table query. ID = 1) t 1, (4) Execute region. Table natural join region. HTM t 2, Query • query. ID join catalog. HTM t 3 • region. ID on t 3. htm between t 2. htm. Low and t 2. htm. Upp, • ra natural join (select * • dec from catalog. Table • size 1 where mag < 24) t 4, • size 2 • region. Expr : Data. Formatter VOTable (6) Query Result in VOTable • • • (5) Query Result Column name data. Type unit UCD. . . Column Metadata Table (2) Insert HTM index for the specified region. HTM • query. ID • region. ID • htm. Low • htm. Upp Real Table catalog. HTM • object. ID • ra • dec • mag • … • object. ID • htm. Index

Catalog Data Xmatch Query with VOTable select vot. *, cat. * from EXT: : 0 vot, catalog. Table cat where distance( (vot. ra, vot. dec), (cat. ra, cat. dec)) < 1 [arcsec] and cat. mag < 24 : Query. Catalog. X match (1) Insert votable data and region data catalog. Table • object. ID • ra • dec • mag • … User View (3) Create SQL for real tables select * from (select * from region. Table query. ID = 1) t 1, (4) Execute region. Table natural join region. HTM t 2, Query • query. ID natural join votable t 3, • region. ID join catalog. HTM t 4 • ra on t 4. htm between t 2. htm. Low and t 2. htm. Upp, • dec natural join (select * • size 1 from catalog. Table • size 2 where mag < 24) t 4, • region. Expr : Data. Formatter VOTable (6) Query Result in VOTable • • • (5) Query Result Column name data. Type unit UCD. . . Column Metadata Table (2) Insert HTM index for the specified region votable • region. ID • ra • dec • … catalog. Table catalog. HTM • object. ID • ra • dec • mag • … • object. ID • htm. Index region. HTM • query. ID • region. ID • htm. Low • htm. Upp Real Table

Image Data Query by ADQL select access. URL from virtual. Table where region overlaps Box ( (280, +20), 1. 0) and filter. Name = ‘W-C-RC’ and format = ‘image/fits’ : Query. Image (1) Insert region data virtual. Table • region • filter. Name • format • access. URL User View region. Tabel. region. Expr frame. Table. filter. Name format Function. Column. access. URL() (3) Create SQL for real tables select * from (select * from region. Table query. ID = 1) t 1, natural join region. HTM t 2, (4) Execute natural join frame. HTM t 3, natural join (select * Query from frame. Table where filter. Name = ‘W-C-RC’) t 4, natural join (select * from format where format = ‘image/fits’) t 5 VOTable : Data. Formatter (6) Query Result in VOTable : Function. Column • • • (5) Query Result Column name data. Type unit UCD. . . Column Metadata Table region. Table • query. ID • region. ID • ra • dec • size 1 • size 2 • region. Expr (2) Insert HTM index for the specified region. HTM • query. ID • region. ID • htm. Index Real Table frame. HTM format • frame. ID • filter. Name • . . . • frame. ID • htm. Index • frame. ID • format

Image Data Xmatch Query with VOTable 1. 0) select img. access. URL from EXT: : 0 vot, virtual. Table img where img. region overlaps Box ( (vot. ra, vot. dec), 1. 0, and filter. Name = ‘W-C-RC’ and format = ‘image/fits’ : Query. Image. X match (1) Insert VOTable data and region data virtual. Table • region • filter. Name • format • access. URL User View region. Tabel. region. Expr frame. Table. filter. Name format Function. Column. access. URL() (3) Create SQL for real tables select * from (select * from region. Table query. ID = 1) t 1, natural join region. HTM t 2, natural join frame. HTM t 3, (4) Execute natural join votable t 4, Query natural join (select * from frame. Table where filter. Name = ‘W-C-RC’) t 5, natural join (select * from format where format = ‘image/fits’) t 6 : Data. Formatter VOTable (6) Query Result in VOTable : Function. Column • • • (5) Query Result Column name data. Type unit UCD. . . Column Metadata Table region. Table • query. ID • region. ID • ra • dec • size 1 • size 2 • region. Expr (2) Insert HTM index for the specified region votable • region. ID • ra • dec • … region. HTM • query. ID • region. ID • htm. Index Real Table frame. HTM format • frame. ID • filter. Name • . . . • frame. ID • htm. Index • frame. ID • format

Science Use Case QSO/Galaxy Clustering Study

What we can learn from the QSO/Galaxy clustering ? – Origin of the large scale structure • QSO is a tracer of high density regions in the universe hierarchical clustering model • Comparison between the observation and theoretical prediction is required. – Origin of the QSO activity • Why is the QSO so powerful. • Test of the galaxy merger model

Work flow for studying the QSO/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 the SExtractor Web service. 4. Estimate the distance to the objects around the QSO Invoke the Hyper. Z 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 WHERE POINT(cat. raj 2000, cat. dej 2000) WITHIN CIRCLE((189. 20625, 62. 216111), 0. 10) AND cat. v_mag < 20 AND img. format = 'image/fits' AND img. region OVERLAPS BOX((cat. _raj 2000, cat. _dej 2000), 0. 02) (1) 1. 2. 3. 4. 5. User request SQL to the QSO catalog DB QSO coordinates in VOTable SQL & VOTable to Image DB Access. URL for images around QSOs (2) SELECT cat. * FROM jvo. misc. qso_veron_2003 cat WHERE POINT(cat. raj 2000, cat. dej 2000) WITHIN CIRCLE((189. 20625, 62. 216111), 0. 10) AND cat. v_mag < 20 (2) JVO Portal (3) (4) VOTable QSO+Image (5) (4) Sky. Node QSO Catalog DB VOTable QSO list Sky. Node (3) SELECT vot. *, img. * FROM EXT: : 0 vot, VOTable jvo. test. smoka. spcam_img QSO list WHERE img. format = 'image/fits' AND img. region OVERLAPS BOX((vot. raj 2000, vot. dej 2000), 0. 02) (5) Subaru Suprime. Cam Image DB

QSO-Galaxy Search

QSO Search Plot

How to treat object data • Spectrum metadata has a structured data type. Access method to an object data is defined in SQL 99. Example is: Select t. * From spectrum. Table t Where t. target. position within STC(‘ … ‘) (*) target is a column name, position is a field name of Target object. Always specify alias table name to express 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 a flat table, then emulate object datatype column.

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 in advance. • Table Data Model for each data service are required. Top level data table classes Table data model defines If space. Frame is the required Catalog same for all the rows, columns and – space. Frame it may be returned as recommended – src. Position PARAMETER in columns – brightness VOTable –. . . The data service must have an interface to return the class name of the table and column metadata. Image Cutout Service Obs. Data – space. Frame – data. Format – access. URL –. . . Image Data Spectrum Data – space. Frame – data. Format – access. URL – region – space. Frame – data. Format – access. URL – target. Position Image Atlas Service Each subclass inherits the property of its super class 3 D Cube Data Service Image Mosaic Pointed Image Service Archive 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 of VOTable. A coordinate column must have an coordinate. Frame 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 not defined 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 SQL 92 syntax and some astro-extension many elements (33) & many data types (69), hard to build a data service. It is helpful to define minimum subset ADQL spec as a base line • Selection. Item. Type (13 sub types in ADQL 0. 8) – Only Alias. Selection. Item and Column. Reference. Type • From. Table. Type (3 sub types in ADQL 0. 8) – Only Table. Type • Search. Type (16 sub types in ADQL 0. 8) – Only Intersection. Search. Type and Comparison. Pred. Type – Region Search Criterion can be specified by Comparison. Pred. Type.

Minimum subset of ADQL Element: 33 (full) 12 (basic) Simple Type: 13 (f) 4 (b) Fundamentral Type xs: unsigned. Int xs: string(*) xs: double(*) xs: long(*) Complex Type: 56 (f) 12 (b) Element Allow Arg(*) Column Condition(*) End. Comment Expression(*) From(*) Group. By Having In. To Item(*) Literal(*) Name Nature Order. By Params Pattern Qualifier Region(*) Simple Type aggregate. Function. Name. Type all. Or. Distinct. Type binary. Operator. Type comparison. Type(*) joint. Table. Qualifier. Type math. Function. Name. Type order. Direction. Type trigonometric. Function. Name. Type unary. Operator. Type Complex Type Restrict Select(*) Selection. List(*) Set Sigma Start. Comment Table(*) Table. Name Tables Unit(*) Where(*) from. Table. Type selection Array. Of. From. Table. Type Constant. List. Set aggregate. Function. Type alias. Selection. Item. Type(*) all. Selection. Item. Type archive. Table. Type atom. Type(*) between. Pred. Type binary. Expr. Type closed. Search. Type column. Reference. Type(*) comparison. Pred. Type(*) drop. Table. Type exclusive. Search. Type from. Table. Type from. Type(*) function. Type group. By. Type having. Type include. Table. Type inclusion. Set. Type inclusive. Search. Type integer. Type(*) intersection. Search. Type(*) into. Type inverse. Search. Type join. Table. Type like. Pred. Type literal. Type(*) math. Function. Type not. Between. Pred. Type not. Like. Pred. Type number. Type order. Expression. Type order. Option. Type order. Type real. Type(*) region. Search. Type scalar. Expression. Type search. Type selection. Item. Type selection. Limit. Type selection. List. Type selection. Option. Type string. Type(*) sub. Query. Set table. Type(*) trigonometric. Function. Type unary. Expr. Type union. Search. Type user. Defined. Function. Type where. Type(*) x. Match. Table. Alias. Type x. Match. Typ

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 critical standard for interoperability. • Each schema has a difference namespace. ADQL (Full) schema STC (Full) schema ADQL (Core) schema STC (Core) schema

Substitution of Column Name by UCD or UType • Without knowing the column names, we cannot write an SQL… • Possible Solution use UCD or Utype for describing the query condition and selection list. • Instroduce “ucd” and “utype” attributes to the column. Reference. Type <Item xsi: type=“column. Reference. Type” Name=“ra” Table=“qso”/> <Item xsi: type=“column. Reference. Type” ucd=“pos. eq; src” Table=“qso”/> <Item xsi: type=“column. Reference. Type” utype=“Target. pos” Table=“qso”/>

Example Query <Select xmlns: region="urn: region-core“ Permanent namespace xmlns=“http: //www. ivoa. net/xml/ADQL/core” xmlns: xsi="http: //www. w 3. org/2001/XMLSchema-instance"> Red characters <Selection. List> are user-supplied <Item Table=“t” Name=“*” xsi: type="column. Reference. Type“/> parameters </Selection. List> <From> Use “comparison. Pred” <Table Name=“image" Alias=“t" xsi: type="table. Type"/> for describing the </From> search condition <Where> <Condition comparison=“overlaps” xsi: type=“comparison. Pred. Type"> <Arg Table=“t” Name=“region” xsi: type=“column. Reference. Type”/> <Arg xsi: type="circle. Type" unit="deg" coord_system_id=“FK 5"> <region: Center>20. 0 30. 0</region: Center> <region: Radius>1. 0</region: Radius> </Arg> SELECT t. * </Condition> FROM image t Only “Circle. Type” and </Where> WHERE region OVERLAPS “All. Sky” are defined in </Select> region-core schema Circle((20. 0, 30. 0, ’FK 5’), 1. 0)

Comparison. Pred. Type as a “must-support” Condition. Type • A basic Sky. Node MUST support “comparison. Pred. Type”. • A basic Sky. Node MAY support the other search. Type. • A basic Sky. Node MUST recognize an unsupported search. Type as a “true. Type”. • A basic Sky. Node MUST support the following construct: – <STC_column. Name> <STC_operator> <STC_search. Location. Type> – STC_operator : : = within, overlaps, outside STC-type column must be compared with an object of STC seach. Location. Type – 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(‘Spectral. Interval A 4000 7000’) and observation. Time within STC(‘Time. Interval 2004 -05 -01 2004 -05 utype defines which 31’) (Specturm Query) column represents STC.

ADQL-x version of point within ‘Circle ICRS 200 – 20 2. 0’ It might be convenient to define substitution types for frequently used frames such as <Space. Frame xsi: tye=“ICRSFrame. Type”/> <Condition comparison=“within” xsi: type=“comparison. Pred. Type"> <Arg Table=“t” utype=“src. position” xsi: type=“column. Reference. Type”/> <Arg xsi: type=“search. Location. Type"> <Astro. Coord. System ID=“ICRS”> <Space. Frame> <ICRS/><BARYCENTER/><SPHERICAL coord_naxes=“ 2”/> </Space. Frame> </Astro. Coord. System> <Astro. Coord. Area ID=“Search. Region” coord_system_id=“ICRS”> <Region> <Circle unit=“deg”> <Center>200 – 20</Center> <Radius>2. 0</Radius> </Circle> </Region> </Astro. Coord. Area> Which frame should be defined as </Arg> a “must be supported” frame </Condition>

Returned VOTable • FIELD id If alias name is specified “adql: alias. Name”, otherwise “adql: table. Alias. Name. column. Name”. • TABLE id table alias name • FIELD & TABLE name free, client app may use it • STC metadata (Coord. Sys) 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 as PARAMETER. • Some of the columns must always be included in the output VOTable. These columns are defined in SIAP, SSAP and SXAP document.

Problem encountered in implementation and problem for interoperability

Problems encountered in implementation (1) • XML Java auto-conversion in AXIS – With the standard usage of AXIS, creation of a Java object corresponding to a XML document (e. g. VOTable) is, as a default, required. – Server memory is easily exhausted. – Even several hundreds records of VOTable suffers from out of memory error. • Possible Solution : – Don’t use the auto-serialization and deserialization mechanism of AXIS (suggestion from an Astro. Grid person). – return VOTable as an attachment – return a reference URL to retrieve the VOTable

Problem in implementation (2) • Usage of VOTable is not clearly defined – id and name attribute what should be filled ? – Where column alias name should be put. This information might be used for postsearch processing on portal side. – Location where a table name and an alias table name are put. – Information on the origin of the column data should be kept anywhere in VOTable.

Problems for interoperability (1) • Name space problem (as of 2005 Jan) – JVO ADQL v 0. 8 + VOTable v 1. 1 + STC v 1. 1 – NVO ADQL v 0. 74 + VOTable <v 1. 0 + NVO-STC • Temporal workaround – External interface ADQL v 0. 74, VOTable v 1. 0 – Internal interface ADQL v 0. 8, VOTable v 1. 1 – Namespace exchanger • Complexity of ADQL and STC object – ADQL 33 elements, 69 types – STC 250 elements, 88 types • Possible Solutions: – Define a core part of ADQL as a minimum subset and assign a permanent namespace. Never update, never change the namespace of the core part.

Problem for interoperability (2) • Column name must be known in advance for writing ADQL. • We can get column names by “Columns” interface and write ADQL, but it requires human intervention. • A possible solution: – use UCD or Utype for specifying a column – Introduce “ucd” and “utype” attributes to the column. Reference. Type <Item xsi: type=“column. Reference. Type” Name=“ra” Table=“qso”/> <Item xsi: type=“column. Reference. Type” ucd=“pos. eq; src” Table=“qso”/> <Item xsi: type=“column. Reference. Type” utype=“Target. pos” Table=“qso”/> – If the specified utype or ucd is not found in the queried table, ignore the condition for that column return PARAMETER of “Na. N” for that column

Summary • Experimental release of JVO Sky. Node toolkit – http: //jvo. nao. ac. jp/download/skynode-toolkit/ – Support for Catalog query, Image query • Some Proposals – Need minimum subset of ADQL and STC – Minor update on ADQL: ucd and utype attributes to the Column. Reference. Type. – Usage of VOTable. Location where column name, column alias name, table name and table alias name are described. – Error message (for future work)

Client & Server roll separation Skynode draft should clearly identify which functionalities must be on the server. Sky. Node client Sky. Node server Basic unit conversion ? coordinate frame conversion ? ? conversion ucd, utype to column name planet location (ra, dec) Extended cross match (x, y, z) & (ra, dec) execution plan Require reference implementation of client too.