taskGNU Astronomy Utilities - Tasks: task #16211, Table using ADQL to work with...

 
 

You are not allowed to post comments on this tracker with your current authentication level.

task #16211: Table using ADQL to work with input tables

Submitter:  Mohammad Akhlaghi <makhlaghi>
Submitted:  Sat 28 May 2022 05:38:20 PM UTC
   
 
Should Start On:  Fri 27 May 2022 11:00:00 PM UTC Should be Finished on:  Fri 27 May 2022 11:00:00 PM UTC
Category:  Table Priority:  5 - Normal
Item Group:  Enhancement Status:  Postponed
Privacy:  Public Assigned to:  None
Percent Complete:  0% Open/Closed:  Open
Effort:  0.00

Fri 14 Jul 2023 08:03:38 PM UTC, comment #1: 

= Minimal working example =

To get a minimal working example, I tried playing around with the SQLite C API first.

I've created this repository. To try it out, you'd only need to clone and execute GNU Make:


git clone https://git.sr.ht/~pedramardakani/sqlite-integration
cd sqlite-integration
make


This will create the "main" program which takes the database as the first argument, and the query as its second argument. For instance:


./build/main ./build/chinook.db "SELECT * FROM albums LIMIT 2;"


It will print out the results with extra debugging information.

= Next Steps =

We need to convert from/to 'gal_data_t' structure. We can use the 'gal_data_t' column data types to create a temporary (or even permanent) database, and execute a SQL query on it. Finally, the output can stay in SQLite3 form, or get converted back to 'gal_data_t', and be saved in the FITS format.


Pedram Ashofteh-Ardakani <pedram>
Group Member
Sat 28 May 2022 05:38:20 PM UTC, original submission:  

Currently, Gnuastro's Table program has a set of options 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), which is an extension of the generic SQL (or Structured Query Language).

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 (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, and that it is very well tested (+2 million automatic tests before each release), fast and robust. It also has a comprehensive documentation, and has implementations of all core SQL functions (as well as an interface to define new functions or re-define existing ones).

Another nice feature of SQLite is that it has its own file format. 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!).

Mohammad Akhlaghi <makhlaghi>
Group administrator

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

No files currently attached

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by pedram (Posted a comment)
  • -email is unavailable- added by makhlaghi (Submitted the item)
  • -email is unavailable- added by makhlaghi
  •  

    There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

     

    Follows 1 latest change.

    Date Changed by Updated Field Previous Value => Replaced by
    2022-05-28 makhlaghi Carbon-Copy- Added -email is unavailable-

    Back to the top

    Powered by Savane 3.13-758e.
    Corresponding source code