Questions arising from analysis of Brown Dwarf Science case

This is a summary of what I said at the Iteration 5 GDW meeting at IoA on Feb 12th.

1. How to support SELECT INTO?
   • Needed because stepwise working requires new tables to be generated from old ones
   • Large datasets will be hard to export as VOTable
   • Needs change to ADQL to support this
   • INTO destination could be:
     • New table in same DBMS
     • Myspace file
     • Somewhere else?
   • Can we support other commands: DROP TABLE?
2. How do we support physical units in tables
   • Does the Registry store units of all standard tables?
   • How does the user specify units
     • Units with each number, e.g.
       • SELECT pmra BETWEEN 100 as/yr AND 150 as/yr
     • Units agreed and well-known so all astronomers use same ones in every situation.
     • Note: Standard units are awkward, e.g. radians/sec for proper motion, 10^13 too big for proper motions.
3. How to output to Graphical Package?
   • Export as CSV can then use GNUplot and many others
   • Tend to lose metadata in CSV, even column names
   • Export as VOTable can then use VOPLOT, TOPCAT, etc.
   • Export as FITS can use FV and other utilities
4. How do we allow ALTER TABLE and UPDATE?
   • Users will want to add computed columns to their own tables.
   • Not supported by ADQL
   • Will the Registry store metadata (units etc) for columns of user-generated tables?
   • To populate an additional column need to use UPDATE statement, not supported by ADQL
   • Both should be allowed only on users own tables.
5. How does user generate complex queries?
   • Example from Brown Dwarf Search:
     • SELECT * FROM usnob WHERE REGION('Circle J2000 66.72 15.87 13.0') AND pmra BETWEEN -50 AND -150 AND pmdec BETWEEN 0 AND 100 AND acos( (sin(radians(7.6)) - sin(radians(decl)) * cos(sin(radians(decl)) * sin(radians(7.6)) + cos(radians(decl)) * cos(radians(7.6)) * cos(radians(94.2-ra))))) / (cos(radians(decl)) * sin(acos(sin(radians(decl)) * sin(radians(7.6)) + cos(radians(decl)) * cos(radians(7.6)) * cos(radians(94.2-ra)))) )) BETWEEN atan2(pmra,pmdec) - 1.5 * sqrt(ra_err*ra_err + dec_err*dec_err)/ sqrt(pmra*pmra + pmdec*pmdec) AND atan2(pmra,pmdec) + 1.5 * sqrt(ra_err*ra_err + dec_err*dec_err)/ sqrt(pmra*pmra + pmdec*pmdec);
6. How does user edit previous queries
   • In Brown Dwarf Search need two parallel data flows (data, control) can query easily be re-used with slightly different parameters?
   • Interactive use will involve trial-and-error: need to go back a step and modify previous query and re-submit.
7. How to compute statistics, histograms etc.
   • Easier to do using user-defined functions (stored procedures).
   • Can ADQL be extended to allow these, where they exist?
8. How about a standard set of functions including useful ones in astronomy e.g. Convert coordinates celestialgalactic etc., Great-circle distance functions, etc.
9. How to cross-match?
   • XMATCH function in VOQL but not ADQL
   • Poorly defined at present how to specify maximum offset or likelihood?
   • Hard to implement in general
     • Easy if spatial indices exist
     • Possible if pixel-code method used (HTM/HEALpix)
     • Can use inefficient method if region small enough
10. How to select from graphical display
    • Final step in Brown Dwarf Search required user to
    • draw line on graphical plot to separate brown dwarf candidates from others
    • Have no utilities at present allowing such graphical interaction
    • Even if we did, no way of extracting information and using that in database selection.
11. Other Desirable Database Operations
    • Get estimate of time taken to execute a proposed query, e.g. from EXPLAIN command. Commonly provided, but not in Standard SQL
    • Specify maximum time for query. Supported by Postgres and some other DBMS. SDSS skyquery has 3 queues of differing limits
    • Cancel long-running query

-- ClivePage - 16 Feb 2004