Science with the Virtual Observatory 2006 Summer School

Introduction to OpenSkyQuery

Authors: Maria Nieto-Santisteban and Roy Williams

These notes are companions to the presentationOpenSkyQuery (PPT).

Open SkyQuery is:

A Web application that allows querying and cross-matching distributed astronomical services known as SkyNodes. It is also a place to upload small personal catalogs (~ 5k rows) for cross-matching with the big public SkyNodes.

What is SkyNode?

A number of astronomy survey projects produce databases containing source catalogs. Some of these databases contain a billion or more entries, and for each entry, tens or even hundreds of columns may be stored. A VO SkyNode is a standard interface to such databases. SkyNodes are needed because these databases can be in different systems, such as SQL Server, Oracle, Sybase, MySQL, etc. To avoid requiring users to learn different database systems, or to get database access accounts for many servers, these databases are provided to the VO through the SkyNode interface.

SkyNodes are classified as BASIC and FULL. The main distinction between them is that FULL SkyNode are able to perform cross-matches.

What is ADQL?

ADQL is the Astronomy Data Query Language. It is the query language understood by SkyNodes. It looks very much like SQL, the standard Structured Query Language used by many database systems. ADQL does not support all features of SQL, but it adds several important capabilities specialized for astronomy. These include a cross-match function, XMATCH, that allows users to find objects in different databases that are spatially coincident to within some tolerance, and a REGION function that allows users to constrain their queries to a particular region on the sky.

Distributed Execution

When the portal receives a query, it is parsed and sent to the SkyNodes according to an optimized query execution plan. At the moment, the optimization is very basic and consists of first counting how many objects meet the WHERE constraint and then organizing the execution plan in such a way that the nodes with the smallest number of objects meeting the constraint executes first. The execution plan is transmitted from one node to the next in descending order of cardinality. Results are pushed up from one node to the next in ascending order of cardinality. The last node in receiving the plan does not require executing XMATCH. There is nothing to be cross-matched against yet. The cross-matching happens in subsequent nodes.

Limitations: SkyNodes are currently synchronous. This means the caller waits for the answer from the node. which may lead to performance problems if no limit is applied. Currently, we restrict the return from the nodes to 5k rows. This number is an arbitrary cut but some limit is necessary when running synchronously.

How does this limitation affect queries? First, single SkyNode queries will return a maximum of 5k rows. Second, and most important, cross-match queries between sets that contain about 5k rows are likely to be incomplete. If a SkyNode has more than 5k objects meeting the WHERE condition, the cut will be applied. This usually happens when the REGION constraint covers a big area with a lot of objects, OR/AND other conditions in the WHERE clause are not restrictive enough. An additional cut may happen after the cross-match is performed and the results are sent to the next node. During the cross-match process, each object from the prior node is compared to the current. If the prior node provided about 5k rows and a one-to-one match is expected, the match table might end up with more than 5k rows depending on how restrictive is the confidence level in the XMATCH and the catalog precision.

Conclusion: if you expect to have a big overlay between catalogs, use constrains (PARAMETER ranges or REGIONS) that keep the number of objects small. How small depends on how many objects you expect to cross-match per object. We are working on a parallel platform to provide large-scale query and cross-match. Thank you for your patience!

Exercise 1: Querying a catalog

Open a browser and point to http://www.openskyquery.net

0. Then click on "query page".
1. Click on the tab labelled "Simple Query"
2. At this point, you can choose a catalog, and then "Next"
3. You will see simple ways to specify how many records to return, where in the sky, and constrain the more popular attributes of the catalog. For each SkyNode, a subset of the most relevant catalog parameters (columns) have been selected. For more extensive, general queries, the advanced form should be used.
4. Click on "Generate Query" to see the ADQL.
5. Click on "Submit Query" to see the ADQL.
6. You see a table in HTML. You can select other formats also in the dropdown at the top.

Exercise 2: Crossmatch

Problem statement: JWST is planning to select guide stars from the Guide Star Catalog 2 (GSC2). The field of view of the JWST Fine Guidance Sensor (FGS) is only about 11 square arcmin, so it will be necessary to select guide stars near the faint limit of GSC2. GSC2 contains both unresolved point sources (“stars”) and extended objects (“galaxies”). The extended objects may not be good for guiding, so the guide star selection system may avoid objects classified as extended in GSC2. Classifying objects as point or extended sources is difficult at the faint limit of GSC2, so it is interesting and useful to explore where these classifications start to break down in the current version of GSC2. One way to see if there is a problem is to compare classifications from GSC2 and the Sloan Digital Sky Survey (SDSS) using Open SkyQuery. Where they agree, the classifications are probably good. Where they start to disagree, further work is probably needed in one or both catalogs. Below are detailed instructions for using Open SkyQuery to make this comparison.

Open a browser and point to http://www.openskyquery.net

• Select the GSC2 catalog
  • Look for GSC2 on the left bar and click on the + (by default will choose the PhotoPrimary table)
  • At this point, a basic SQL query will appear in the query window
• Select a column in the PhotoPrimary table
  • Click on the word “SELECT” in the query window.
  • Click “Add Column(s)” below
  • Select “GSC2:photoprimary a”
  • Scroll to and select “FpgMag”
  • Click “GO”
  • The FpgMag column will be added to the Select statement in the query window above.
• Select the SDSS catalog
  • Look for SDSS on the left bar and click on the + (by default will choose the PhotoPrimary table)
  • SDSS will be added to the “FROM” clause of the query
  • A “WHERE” clause will be added with a 3.5 sigma “XMATCH” constraint
• Select a column in the SDSS PhotoPrimary table
  • Click on the word “SELECT” in the query window.
  • Click “Add Column(s)” below
  • Select “SDSS:photoprimary b”
  • Scroll to and select “r {mag}” (roughly analogous to FpgMag above)
  • Click “GO”
  • The “r” magnitude column will be added to the Select statement in the query window above.
• Define a search region on the sky by adding a “REGION” constraint
  • Click the “Region” button below.
  • A Circular region constraint
  • Click on the word “REGION” to modify the parameters
  • Change the radius to 15 arcminutes to get more objects
  • If you change your position, remember that SDSS only covers part of the sky!
• Add a constraint on FpgMag (and avoid ‘nulls’ values in FpgMag)
  • Click the “Edit” tab at the top of the query window
  • At the end of the query add “AND a.FpgMag > 12”
  • Click the “Build” tab at the top of the query window to verify the syntax
• Submit the query
  • Click the “Submit” button at the top right
  • The query status will appear on the right
  • Wait until both nodes turn blue and the “Query is complete”
• Examine results of the query in tabular form
  • Click the “View” button on the right
  • A table of results will appear in a new window with one column for each parameter in the “SELECT” statement
  • You can “SAVE” the table in several formats (HTML, VOTable, CSV, ..)
  • Once you are done, close this window and go back to the original window
• Plot the results of the query
  • Click the “Plot” button on the right
  • The VOPlot java applet will start on a new window
  • Use the drop-down menu on the righ to select “gsc2_fpgmag” for the Y axis and “sdss_r” for the X axis
  • Click the “Plot” button. Figure 1 shows the type of relationship you will see, though this particular figure comes from a JWST memo, rather than VOPlot.
  • Notice that most points lie on a line because the magnitudes are similar in both catalogs
  • Points far from the line may correspond to spurious associations between two different objects with different magnitudes close to each other on the sky