Retrieving data of all exons from UCSC using MySQL
I am using this command bellow to retrieve data from all exon start and all exon end plus 10 bp from each end. However, looking the output I saw several genes with only the first exon of that gene. What I am doing wrong?
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg19 -N -e 'select chrom,exonStarts-10,exonEnds+10,name2,strand from refGene '
• 2,819 views
•
link
written
by
tiago211287
1 answer
Answering my own question:
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg19 -N -e 'select chrom,exonStarts-10,exonEnds+10,name2,strand from refGene '
The exon start and end column has all exons in comma separated fields, so adding 10 to it will make only the first value to be showed up. So the better thing to do is use like this:
mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -D hg19 -N -e 'select chrom,exonStarts,exonEnds,name2,strand from refGene ' > hg19.genes
If one wants to get each exon per row, you need to use this awk command :
awk '{ n = split($2, a, ","); split($3, b, ","); for(i=1; i<n; ++i) print $1, a[i], b[i], $4, $5 }' h19.genes > h19.genes.bed
• 0 views
•
link
written
by
tiago211287
Log in to answer this question.
More posts like this
-
retrieve a table of genes for mouse
written by Eliran Turgeman •
-
Query a public sql server from within python
written by Eliran Turgeman •
-
use a input file to a mysql query to retrieve genomic coordinates from a list of genes
written by jfertaj
-
query using mysql on ucsc table browser
written by bioguy24
-
Multiple Exon Start and End sites in UCSC Exons table
written by dally
-
How to mySQL repeatmasker with a list in R
written by sebastiz •
-
get biotype for refseq NM transcripts
written by Pablo Marin-Garcia
-
How to get protein names, gene symbols, refseq numbers, gi numbers... using UCSC mysql?
written by ngs101.com
-
What Is Hgsql? How To Install And Work With It?
written by jitendra •
-
Get A File To Cross Reference Ensembl Gene Ids And Alias
written by D W •
If I recall correctly, exonStarts and exonEnds are text columns with comma separated values, right? How is your query supposed to work on such data?
Dont know, I collected information from several posts to make this command. The first time I hear that, makes sense.
Did you happen to add the
+10part before testing the original query? You should try and run an unaltered query so it's easier to figure out when something stops working.No, because I did not knew this field was comma separated. Indeed this solved it all. Thanks.
Be useful to leave a not about
what solved it allfor folks who happen on this thread via search in future.