This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Getting A List Of Satellite Repeats As Bed File From Ucsc [Repeatmasker]

Hi,

I know how to get this list from UCSC, but the problem is the after clicking the "get output", the list to big to be copied. What could be the mysql query for it.

I tried mysql -h genome-mysql.cse.ucsc.edu -A -u genome -D mm9 -e 'select * from rmsk' but got the error

ERROR 1146 (42S02) at line 1: Table 'mm9.rmsk' doesn't exist

Also, in the ucsc downloads page, I am unable to find the rmsk table as a whole genome file (per chromosome exists).

Thanks

mysql repeatmasker repeats
• 5,599 views • link
updated by Biostar Biostar • written by Sukhi Singh Sukhi Singh

1 answer

the table was split for each chromosome:

   $  mysql -h genome-mysql.cse.ucsc.edu -A -u genome -D mm9 -e 'show tables like "%rmsk"'
    +-----------------------+
    | Tables_in_mm9 (%rmsk) |
    +-----------------------+
    | chr10_rmsk            |
    | chr11_rmsk            |
    | chr12_rmsk            |
    | chr13_random_rmsk     |
    | chr13_rmsk            |
    | chr14_rmsk            |
    | chr15_rmsk            |
    | chr16_random_rmsk     |
    | chr16_rmsk            |
    | chr17_random_rmsk     |
    | chr17_rmsk            |
    | chr18_rmsk            |
    | chr19_rmsk            |
    | chr1_random_rmsk      |
    | chr1_rmsk             |
    | chr2_rmsk             |
    | chr3_random_rmsk      |
    | chr3_rmsk             |
    | chr4_random_rmsk      |
    | chr4_rmsk             |
    | chr5_random_rmsk      |
    | chr5_rmsk             |
    | chr6_rmsk             |
    | chr7_random_rmsk      |
    | chr7_rmsk             |
    | chr8_random_rmsk      |
    | chr8_rmsk             |
    | chr9_random_rmsk      |
    | chr9_rmsk             |
    | chrM_rmsk             |
    | chrUn_random_rmsk     |
    | chrX_random_rmsk      |
    | chrX_rmsk             |
    | chrY_random_rmsk      |
    | chrY_rmsk             |
    +-----------------------+



$ mysql -h genome-mysql.cse.ucsc.edu -A -u genome -D mm9 -e 'select * from chrY_rmsk limit 2\G'
*************************** 1. row ***************************
      bin: 585
  swScore: 477
 milliDiv: 0
 milliDel: 0
 milliIns: 0
 genoName: chrY
genoStart: 95
  genoEnd: 148
 genoLeft: -15902407
   strand: +
  repName: (TC)n
 repClass: Simple_repeat
repFamily: Simple_repeat
 repStart: 1
   repEnd: 53
  repLeft: 0
       id: 1
*************************** 2. row ***************************
      bin: 585
  swScore: 3001
 milliDiv: 210
 milliDel: 22
 milliIns: 28
 genoName: chrY
genoStart: 283
  genoEnd: 1198
 genoLeft: -15901357
   strand: +
  repName: L1_Mur3
 repClass: LINE
repFamily: L1
 repStart: 1060
   repEnd: 1980
  repLeft: -3900
       id: 2
• 25 views • link
written by Pierre Lindenbaum Pierre Lindenbaum

Thanks Pierre, that was the confusion, as when you pull the table from the ucsc website (from table browser), its somehow combines all the files and outputs them. So, finally I dumped all the tables individually using mysql and appended the output.

Cheers

• 0 views • link
written by Sukhi Singh Sukhi Singh

Log in to answer this question.

More posts like this