10. Database Query Interface

The following is a step-by-step guide to the C-MIND query interface, using an example of a relatively simple query that links participant attributes with imaging attributes.

10.1 Overview

When first opened, the query interface looks as illustrated in Figure 1:

Figure 1: Default view in search interface

 

The query interface has several panels:
 

  1. A “Current Selection” panel which summarizes the currently active filtering / querying criteria in plain English language
     
  2. A “Get Data” panel which lets users download data.
     
  3. A “Data Filters” panel which lets users add filters to the active query
     
  4. A “Results” panel which displays information about the participants and/or images that pass the filter criteria.

 

The query interface lets users simultaneously query and browse the data: query results are updated in the “Results” panel immediately after query criteria are modified. Initially all participants (175 in the example above) are included as query results, this number is updated as filters are entered in the “Data Filters” panel as in the following screen shots. Note that initially the “Participant Information” tab is selected in the Results panel by default and all query results are displayed in a participant-centric format.

10.2 A first query example

Suppose we want to restrict our results set to those participants with a Verbal IQ composite score above 85. Clicking on the “Choose an option….” dropdown in the “Data Filters” panel (see Figure 2) reveals the top-level organization of the participant-centric data dictionary (see manual for details):

 

Figure 2: The query variable selection dropdown

 

Users can now either browse the variable dictionary categories to find the relevant variable, or they can type a search term into the search box, such as in the following screen shot (Figure 3):

 

Figure 3: Typing in the search box limits variable choice

 

Clicking on the variable name of choice allows users then to limit the result set, as can be seen in Figure 4. Note that the number of participants in the results set went down from 206 to 42 as a result of having limited the results to those with WPPSI Verbal IQ composite scores above 85.  Note also that the default ‘blank’ for the value text box means that the column data does not exist in the database.  You may select ‘Not Equal To’ and leave the value ‘blank’ to test for the existence of a column.

 

Figure 4: First sample query: participant with verbal IQ composite score > 85

The dropdowns in the “Data Filters” panel are data type aware, i.e., they will change functionality depending on whether the variable selected is of numerical, categorical or string type. Note also that one can now expand the query criteria by clicking on the last dropdown box in the “Data Filters” panel and adding additional query terms.

10.3 Displaying information about query results

Query criteria entered as in Section 2 will limit the results set but not change what information is extracted and displayed on the Results Panel about the query results. By default the “Results Panel” will display basic information (age/gender/ethnicity/study site); we now show how to display and export additional information about the results set.

 

By clicking on the “Select a category” dropdown users can select groups of variables to be shows in tabs (see Figure 5):

 

Figure 5: Display tabs

 

 

Selecting any one category will result in a tab being opened displaying all relevant variables in that category. Clicking on the spreadsheet icon in the “Get Data” panel (top right on the screen) will now export all information from all tabs in comma-delimited (csv) format, which can in turn be imported easily into a spreadsheet program such as MS Excel. The floppy disk icon allows users to save their queries, and the "open folder" icon lets you retrieve previously saved queries.

 

Instead of loading entire groups of variables by category one can also create so-called “Custom tabs” with just the desired information. Suppose we want to only display the Participant ID, their gender and the age at first scan in a view. Selecting “Custom Tab” in the tab dropdown and then selecting the relevant variables in the dropdown under “Columns to Include in Output” dropdown, followed by pressing the “Add Column(s)” button will create a spreadsheet-like custom view (Figure 6):

 

Figure 6: Configuring a custom tab

 

Clicking the “Retrieve Data” button at the bottom of the screen completes the setup of the custom tab, which can now also be renamed by editing the tab header.

10.4. Querying participants using imaging criteria

 

So far in this user guide we have only worked with filtering and displaying study participant attributes, there has not been any mention of imaging parameters or the MRI data itself. Figure 7 illustrates how one can filter participants based on the existence of imaging data associated with them: by entering “has scan type” in the “Data Filters” panel one can narrow the results set to only those participants with, say, an anatomical scan. Moreover, one can now query image properties by selecting the last dropdown box in the “has scan type” filter row and selecting “With Properties….”.

 

Examples of attributes queried via the "With Properties…" functionality:

  • QA Review Value

  • Participant Age associated with Image File (Days)

  • Paradigm

  • Directions Kept after DTI/HARDI QA

 

Figure 7: Querying on and browsing imaging information

 

Secondly, the Figure 7 also illustrates the use of the “imaging information” tab in the “Results” panel. When this panel is clicked for the first time, all images pertaining to the participants in the results set are retrieved. This first click also activates a second download icon in the “Get Data” panel (top right brain icon), which can be used to download a tar.gz file with all selected images. Note that as soon as the “Imaging Information” tab is activated, the CSV download will switch from being “participant-centric” to “image file centric”.

 

 

 

Figure 8: Filtering scan types to display

 

The columns of the imaging tab have filters that can be activated by clicking on the funnel icon in the column header. Users can narrow their search to, say, just raw (unprocessed) data by clicking the filter for in the “File Type” column header.  Clicking “Uncheck All” would quickly deselect all file types.  Then checking the box next to “Raw” would select only the raw data prior to processing.  To restrict the raw data to a particular scan protocol the “Scan Type” filter would be used as shown in Fig. 8.  All other file types aside from “Raw” correspond to data processed by the processing pipelines.  Full details of what each data type represents can be found here.   The details of the processing pipelines are here

 

Similarly, there is a “Year” filter to restrict to a particular year of the longitudinal study and a “QA Review Value” filter to restrict to a particular set of allowed QA values.

10.5. Participant ages

There are a number of age-related database entries for each participant that users should be aware of. Under the category “Participant Info” in the query and display dropdowns users can access the “Participant Age at First Scan”, the “Participant Age at Neurological Exam” as well as “Participant Age at Neuropsych”.  These data points are all linked directly to the participants in the database. Furthermore, each image in the database has a “Participant Age associated with Image File” associated with it (accessible in the Imaging Information tab as well as the query menu following a “has scan type…. with properties” line). The latter is likely to be the most relevant parameter to be included as a covariate when performing image analyses.

10.6. Longitudinal data

A number of database fields are “longitudinal”, i.e., they are entered at each visit for participants that are part of the longitudinal follow-up cohort. The query interface accommodates these data, as can be seen e.g., in Figure 7. Currently we allow simple queries on the longitudinal parameters, we expect to expand these capabilities in the near future, e.g., to allow for queries reflecting changes in parameters (say, IQ values) over time.

 

 

Please send your feedback and questions to help-cmind@bmi.cchmc.org, we are keen on hearing from you!

 

Back to the Index Page.