[task #16211] Table using ADQL to work with input tables

[Mohammad Akhlaghi]

URL:
  <https://savannah.gnu.org/task/?16211>

                 Summary: Table using ADQL to work with input tables
                 Project: GNU Astronomy Utilities
            Submitted by: makhlaghi
            Submitted on: Sat 28 May 2022 06:38:20 PM BST
         Should Start On: Sat 28 May 2022 12:00:00 AM BST
   Should be Finished on: Sat 28 May 2022 12:00:00 AM BST
                Category: Table
                Priority: 5 - Normal
              Item Group: Enhancement
                  Status: Postponed
                 Privacy: Public
        Percent Complete: 0%
             Assigned to: None
             Open/Closed: Open
         Discussion Lock: Any
                  Effort: 0.00


    _______________________________________________________

Follow-up Comments:


-------------------------------------------------------
Date: Sat 28 May 2022 06:38:20 PM BST By: Mohammad Akhlaghi <makhlaghi>
Currently, Gnuastro's Table program has a set of options
<https://www.gnu.org/software//gnuastro/manual/html_node/Operation-precedence-in-Table.html>
for manipulating the input table(s), to create a new output table. However,
this syntax is needs some time to master (it is designed for ease of usage on
the command-line) and is too specific to Gnuastro's Table.

A more formal syntax to work with Tables in astronomical databases is ADQL (or
Astronomical Data Query Language
<https://wiki.ivoa.net/twiki/bin/view/IVOA/ADQL>), which is an extension of
the generic SQL (or Structured Query Language
<https://en.wikipedia.org/wiki/SQL>). 

Currently ADQL is used by large astronomical databases, but it can equally
well be applied to smaller tables on a random scientist's computer! For
example, imagine I have 'a.fits', and I want to select the columns 'ra' and
'dec' from it! Then the SQL command would be (which is equivalent to the
current 'asttable a.fits -cra,dec')


SELECT ra,dec FROM a.fits;


Table can have an option called '--adql' that will either take a file name
containing an ADQL/SQL source that will be parsed, or the query can be
directly given on the command-line, for example:


asttable a.fits --adql='SELECT ra,dec FROM a.fits;'


Table will load 'a.fits' (or any number of input tables) into memory (they can
be in any of the
[https://www.gnu.org/software/gnuastro/manual/html_node/Recognized-table-formats.html
recognized formats), internally translate it/them into an SQL database
structure (depending on the library that we will use), into the same name as
the file ('a.fits' in this case), and simply let the SQL library do the rest
of the job. Once the SQL library is finished, table and write the output on
the command-line (like above) or in any file with the '--output' option.

One good C library that I have found for SQL is SQLite
<https://www.sqlite.org> (by "lite", they don't mean it lacks functionality,
but that it has no dependencies!). They claim that it is the most widely used
SQL library <https://www.sqlite.org/mostdeployed.html>, and that it is very
well tested (+2 million automatic tests before each release), fast and robust.
It also has a comprehensive documentation <https://www.sqlite.org/docs.html>,
and has implementations of all core SQL functions
<https://www.sqlite.org/lang_corefunc.html> (as well as an interface to define
new functions or re-define existing ones
<https://www.sqlite.org/c3ref/create_function.html>).

Another nice feature of SQLite is that it has its own file format
<https://www.sqlite.org/fileformat2.html>. That should be easily
translate-able with other SQL-based database formats. So through SQLite, we
can convert such databases to FITS or other table formats also (to the extent
that is possible of course: SQL has many more features than FITS or
plain-text!).







    _______________________________________________________

Reply to this item at:

  <https://savannah.gnu.org/task/?16211>

_______________________________________________
Message sent via Savannah
https://savannah.gnu.org/