Retrieving promoter regions from ucsc using mysql

Question

[annerionta93]

Hello there,

I think this is a really easy question for anybody with experience using mysql and the UCSC browser.

I’m trying to retrieve all “promoter” regions where promoter is defined as X bp before the TSS.

I know how to do this using the ucsc browser however the output is displayed on screen and I would like instead to download a bed file. Is there a button I'm missing somewhere?

Also, I would rather do this programmatically using a my sql query (I think this link shows how to do it using R)

Many Thanks!

Answer 1

[dariober]

Here's another strategy, since the OP asks for mysql. Get promoters as defined by TSS +/- 1000 bp from refseq for hg19, forward transcripts:

mysql -N --user=genome --host=genome-mysql.cse.ucsc.edu -A -e \
    "select chrom, (txStart + 1) - 1000, (txStart + 1) + 1000, name, name2, strand 
     from hg19.refGene
     where strand = '+'" > proms.txt

And reverse transcripts:

mysql -N --user=genome --host=genome-mysql.cse.ucsc.edu -A -e \
    "select chrom, txEnd-1000, txEnd+1000, name, name2, strand 
     from hg19.refGene
     where strand = '-'" >> proms.txt

[annerionta93]

This is exactly what I had in mind! Thanks dariober! I edited the answer a little bit to just get the upstream bp (instead of a flanking region)

mysql -N --user=genome --host=genome-mysql.cse.ucsc.edu -A -e "select chrom, (txStart + 1) - 1000, (txStart + 1), name, name2, strand from hg19.refGene where strand = '+'" >proms_plus.bed

and

mysql -N --user=genome --host=genome-mysql.cse.ucsc.edu -A -e "select chrom, txEnd, txEnd+1000, name, name2, strand from hg19.refGene where strand = '-'" > proms_neg.bed

Answer 2

[Denise CS]

Regions with promoter (or enhancer, TFBS) activity have been annotated in Ensembl through our Regulatory build based on the ENCODE, Roadmap and Blueprint biochemical data. It's available for both GRCh38 and GRCh37 ((=hg19) and it can be accessed with MySQL although we would recommend using our APIs (Perl or REST). This is the Regulation schema and the API tutorial.

[annerionta93]

Thanks Denise. I'm definitely spending some time learning the Ensemble API today.

[Denise CS]

Fab! If you get stuck, get in touch with our helpdesk. You may also sign up to our dev forum.

Answer 3

[GenoMax]

If the output is displayed in your browser window you should be able to right click on the page and do "save page as" (or an equivalent command for the browser you are using). If you are using UCSC table browser then providing a file name makes the output go to a file instead of the screen.

[annerionta93]

Thanks! This was easy enough.