The UCSC Table Browser data retrieval tool

Abstract

The University of California Santa Cruz (UCSC) Table Browser (http://genome.ucsc.edu/cgi-bin/hgText) provides text-based access to a large collection of genome assemblies and annotation data stored in the Genome Browser Database. A flexible alternative to the graphical-based Genome Browser, this tool offers an enhanced level of query support that includes restrictions based on field values, free-form SQL queries and combined queries on multiple tables. Output can be filtered to restrict the fields and lines returned, and may be organized into one of several formats, including a simple tab- delimited file that can be loaded into a spreadsheet or database as well as advanced formats that may be uploaded into the Genome Browser as custom annotation tracks. The Table Browser User's Guide located on the UCSC website provides instructions and detailed examples for constructing queries and configuring output.

Figure 1

Example of an advanced Table Browser query illustrating the use of a base-by-base table intersection between a standard table and a user- created custom annotation table. The goal is to obtain a list of positions in the p-arm of human chromosome X (Build 34 assembly) in which a SINE repeat overlaps the coding sequence of a gene. (a) The user first creates a custom annotation table called tb_refGene that contains all coding exon entries from the refGene table in the genomic region chrX:1–58500000 (p-arm). To generate the table, one selects the refGene positional table, chooses the Custom Track output option and then selects the Coding Exons BED record option. The custom annotation table may be loaded into the UCSC Genome Browser for further inspection (labeled ‘User Track’ in this figure). (b) The tb_refGene table is then intersected with the chrN_rmsk table using the base-pair-wise (AND) intersection option. To limit the output to only SINE repeats, the chrN_rmsk table is filtered on the repClass field, with the value set to ‘SINE’. The BED format output file is configured to create one record per whole gene. The resulting tab-delimited output indicates four positions that meet the query criteria. This output can be loaded into another program or displayed in the Genome Browser as a custom annotation track.

Figure 1

Example of an advanced Table Browser query illustrating the use of a base-by-base table intersection between a standard table and a user- created custom annotation table. The goal is to obtain a list of positions in the p-arm of human chromosome X (Build 34 assembly) in which a SINE repeat overlaps the coding sequence of a gene. (a) The user first creates a custom annotation table called tb_refGene that contains all coding exon entries from the refGene table in the genomic region chrX:1–58500000 (p-arm). To generate the table, one selects the refGene positional table, chooses the Custom Track output option and then selects the Coding Exons BED record option. The custom annotation table may be loaded into the UCSC Genome Browser for further inspection (labeled ‘User Track’ in this figure). (b) The tb_refGene table is then intersected with the chrN_rmsk table using the base-pair-wise (AND) intersection option. To limit the output to only SINE repeats, the chrN_rmsk table is filtered on the repClass field, with the value set to ‘SINE’. The BED format output file is configured to create one record per whole gene. The resulting tab-delimited output indicates four positions that meet the query criteria. This output can be loaded into another program or displayed in the Genome Browser as a custom annotation track.