4
$\begingroup$

I am using the following command to get all refseq genes from UCSC:

/usr/bin/mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu \
    -A -D hg38 -e 'select concat(t.name, ".", i.version) name, \
    k.locusLinkId as "EntrezId", t.chrom, t.strand, t.txStart, \
    t.txEnd, t.cdsStart, t.cdsEnd, t.exonCount, t.exonStarts, \
    t.exonEnds, t.score, t.name2 from refGene t join hgFixed.gbCdnaInfo i \
    on t.name = i.acc join hgFixed.refLink k on t.name = k.mrnaAcc'

That returns data in the following format (showing the 1st 5 lines):

+-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+
| name        | EntrezId  | chrom | strand | txStart | txEnd | cdsStart | cdsEnd | exonCount | exonStarts         | exonEnds           | score | name2     |
+-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+
| NR_046018.2 | 100287102 | chr1  | +      |   11873 | 14409 |    14409 |  14409 |         3 | 11873,12612,13220, | 12227,12721,14409, |     0 | DDX11L1   |
| NR_106918.1 | 102466751 | chr1  | -      |   17368 | 17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-1 |
| NR_107062.1 | 102465909 | chr1  | -      |   17368 | 17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-2 |
| NR_107063.1 | 102465910 | chr1  | -      |   17368 | 17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-3 |
| NR_128720.1 | 103504738 | chr1  | -      |   17368 | 17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-4 |
+-------------+-----------+-------+--------+---------+-------+----------+--------+-----------+--------------------+--------------------+-------+-----------+

I also want to find the accession of the canonical transcript of each of the genes returned by the command above. Those seem to be stored in the knownCanonical table:

$ /usr/bin/mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu -A -D hg38 -e 'select * from knownCanonical limit 5'
+-------+------------+-----------+-----------+------------+--------------------+
| chrom | chromStart | chromEnd  | clusterId | transcript | protein            |
+-------+------------+-----------+-----------+------------+--------------------+
| chrX  |  100628669 | 100636806 |         1 | uc004ega.3 | ENSG00000000003.14 |
| chrX  |  100584801 | 100599885 |         2 | uc004efy.5 | ENSG00000000005.5  |
| chr20 |   50934866 |  50958550 |         3 | uc002xvw.2 | ENSG00000000419.12 |
| chr1  |  169853073 | 169893959 |         4 | uc001ggs.5 | ENSG00000000457.13 |
| chr1  |  169795048 | 169854080 |         5 | uc001ggp.4 | ENSG00000000460.16 |
+-------+------------+-----------+-----------+------------+--------------------+

However, there seems to be no obvious way to link the knownCanonical table to the refGene, hgFixed.gbCdnaInfo and hgFixed.refLink tables used above.

So, how can I modify my 1st query (or, if necessary write a new one) so that my results also include the accession of the gene's canonical transcript?

$\endgroup$
3
$\begingroup$

You can link them with the kgXref table, since kgXref.refseq == refGene.name and kgXref.kgID == knownCanonical.transcript. Since it seems that knownCanonical.transcript is what you want anyway, you don't even need to join on it:

mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu \
-A -D hg38 -e 'select concat(t.name, ".", i.version) name, x.kgID, \
k.locusLinkId as "EntrezId", t.chrom, t.strand, t.txStart, \
t.txEnd, t.cdsStart, t.cdsEnd, t.exonCount, t.exonStarts, \
t.exonEnds, t.score, t.name2 from refGene t join (hgFixed.gbCdnaInfo i, hgFixed.refLink k, kgXref x) \
on (t.name = i.acc and t.name = k.mrnaAcc and t.name = x.refseq) limit 10'

The output is then:

+-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
| name        | kgID       | EntrezId  | chrom | strand | txStart | txEnd  | cdsStart | cdsEnd | exonCount | exonStarts         | exonEnds           | score | name2      |
+-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+
| NR_106918.1 | uc031tla.1 | 102466751 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-1  |
| NR_107062.1 | uc031tlm.1 | 102465909 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-2  |
| NR_107063.1 | uc032cta.1 | 102465910 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-3  |
| NR_128720.1 | uc032dmn.1 | 103504738 | chr1  | -      |   17368 |  17436 |    17436 |  17436 |         1 | 17368,             | 17436,             |     0 | MIR6859-4  |
| NR_036051.1 | uc031tlb.1 | 100302278 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-2  |
| NR_036266.1 | uc033cjs.1 | 100422831 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-9  |
| NR_036267.1 | uc032csz.1 | 100422834 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-10 |
| NR_036268.1 | uc032hiw.1 | 100422919 | chr1  | +      |   30365 |  30503 |    30503 |  30503 |         1 | 30365,             | 30503,             |     0 | MIR1302-11 |
| NR_026822.1 | uc001aak.4 |    654835 | chr1  | -      |   34610 |  36081 |    36081 |  36081 |         3 | 34610,35276,35720, | 35174,35481,36081, |     0 | FAM138C    |
| NR_106918.1 | uc031tla.1 | 102466751 | chr1  | -      |  187890 | 187958 |   187958 | 187958 |         1 | 187890,            | 187958,            |     0 | MIR6859-1  |
+-------------+------------+-----------+-------+--------+---------+--------+----------+--------+-----------+--------------------+--------------------+-------+------------+

If you want the protein ID then join knownCanonical on x.kgID.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.