How to retrieve data from an LDAS database

Peter Shawhan
Revised March 7, 2002

There are LDAS databases at each of the LIGO Laboratory sites, as well as at some LSC institutions. There are two ways to retrieve information from a given LDAS database.

`guild`

guild ("graphical user interface to LIGO databases") provides a point-and-click interface to construct a database query in the SQL query language. It then submits the query to the specified LDAS system, retrieves the results, and displays them in tabular form. There are many additional features which will not be described here; see the various help windows within guild.

Once a table has been retrieved and displayed, you can save it to a file by clicking on the "Save as..." button at the bottom of the table display window. In the window that pops up, selecting "Save whole table in original format" causes the table to be saved in LIGO_LW format; there are other FAQ articles which discuss different ways to read such a file. It is also possible to retrieve the results of a query directly to a LIGO_LW file, skipping the display (which can take a lot of CPU time and memory, especially for large tables), by selecting "Don't display; just write to file" under the "Display" menu in the main guild window.

There is a bit more information on the web page for the guild package. In particular, a step-by-step tour of guild's main functions can be found here.

A firewall or IP-mapping router may block the communication protocol that guild normally uses to communicate with LDAS. However, there is a workaround that allows guild to work in these situations: in the main window, go to the "Connect" menu and set "Operate through firewall?" to "Yes". This causes guild to utilize a special proxy server as an intermediary, eliminating the need for LDAS to connect directly back to guild through the firewall or IP-mapping router.

`getMeta`

getMeta is a program which can be executed on the unix command line or within a script. It takes arguments to specify the query (in the form of an SQL "select" statement) and the LDAS system to which the query is to be sent, and retrieves the results into a file in LIGO_LW format. Type 'getMeta' without any arguments to see usage details.

It is probably easiest to use guild to construct the query (unless you are already familiar with SQL syntax) and then paste it onto the command line as an argument to getMeta. Here are a few examples:

getMeta -d llo -q "select start_time,start_time_ns,snr,event_id from sngl_burst where (search='power') and (snr > 6.0)" -o

getMeta -d lho -q "select * from sngl_inspiral where (end_time between 693964813 and 694051213) fetch first 500 rows only" -o ins500.xml

getMeta -d lho -q "select count(*) from sngl_burst"         # omitting '-o' causes output to be sent to stdout

Note that LDAS normally will return a maximum of 10000 database entries in response to any query. However, getMeta has an '-a' option which allows it to retrieve all database entries matching the query. For more information on how to use this option, see the FAQ article on this topic.

Times in queries must be specified in GPS seconds. There are a few ways to convert between UTC or local time and GPS, as described in another FAQ article. You can even use the tconvert utility in-line, between backticks, as in the following example:

getMeta -d lho -q "select * from sngl_inspiral where (end_time between `tconvert jan 2 2002` and `tconvert jan 3 2002 5:45`)" -o out2.xml