Refseq Transcript Version Numbers In Ucsc

Question

[Mahdi Sarmady]

I found out that the UCSC refseq transcript ids do not have a version number.. below are all the "same" transcript (from UCSC refGene table). The positions and exon counts are wildly different. According to NCBI there are seven versions for this particular one: http://www.ncbi.nlm.nih.gov/nuccore/NM_000500

id   | strand |  start   |   end    | coding_start | coding_end | coding_start_status | coding_end_status | exon_count | refseq_id | gene_id 
-------+--------+----------+----------+--------------+------------+---------------------+-------------------+------------+-----------+---------
  2438 | +      |  3355551 |  3356557 |      3355551 |    3356021 | incmpl              | cmpl              |          2 | NM_000500 |    5448
  2439 | +      |  3385938 |  3389289 |      3386045 |    3388753 | cmpl                | cmpl              |         10 | NM_000500 |    5448
  2440 | +      |  3267147 |  3270502 |      3267254 |    3269966 | cmpl                | cmpl              |         10 | NM_000500 |    5448
  2441 | +      |  3306069 |  3342156 |      3306176 |    3341620 | cmpl                | cmpl              |         10 | NM_000500 |    5448
  2442 | +      |  3306853 |  3309423 |      3306853 |    3308887 | incmpl              | cmpl              |          9 | NM_000500 |    5448
 16023 | +      | 31973359 | 31976713 |     31973466 |   31976177 | cmpl                | cmpl              |         11 | NM_000500 |    5448
 16024 | +      |  3476744 |  3480099 |      3476851 |    3479563 | cmpl                | cmpl              |         10 | NM_000500 |    5448
 16025 | +      |  3258942 |  3262296 |      3259049 |    3261760 | cmpl                | cmpl              |         11 | NM_000500 |    5448
 16026 | +      |  3285309 |  3288664 |      3285416 |    3288128 | cmpl                | cmpl              |         10 | NM_000500 |    5448
 16061 | +      | 32006093 | 32009448 |     32006200 |   32008912 | cmpl                | cmpl              |         10 | NM_000500 |    5448

Does UCSC keep track of the RefSeq versions for the refGene table?

[Malachi Griffith]

For clarity/completeness, can you please explain how you obtained this result from the UCSC refGene table

[Byron]

We loaded the data into our own defined table with internal surrogate keys.

Answer 1

[Malachi Griffith]

Yes, UCSC does indeed track RefSeq versions for the refGene table. In the browser you can see this by clicking on your RefSeq transcript.

To get them through the table browser you will have to join another table though. Specifically you can join 'gbCdnaInfo' or 'gbStatus'. In both cases the join is via refGene.name. Both of these contain the 'version' field. See attached screenshot.

To get them by a mysql query at the command line you can do something like this:

mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu -A -D hg19 -e 'select distinct refGene.name,gbCdnaInfo.version from refGene,gbCdnaInfo WHERE refGene.name=gbCdnaInfo.acc AND refGene.name="NM_000500"'

+-----------+---------+
| name      | version |
+-----------+---------+
| NM_000500 |       7 |
+-----------+---------+

This reveals that there is in fact only one version of this transcript that is stored in the current UCSC database. That version is NM_000500.7 (i.e. the latest version right now). The reason you are seeing so many multiple rows can be explained by a further refinement of the mysql query example I provide above. Try this:

mysql --user=genomep --password=password --host=genome-mysql.cse.ucsc.edu -A -D hg19 -e 'select distinct refGene.name,gbCdnaInfo.version,refGene.chrom,refGene.txStart,refGene.txEnd from refGene,gbCdnaInfo WHERE refGene.name=gbCdnaInfo.acc AND refGene.name="NM_000500" order by refGene.chrom,refGene.txStart'

+-----------+---------+----------------+----------+----------+
| name      | version | chrom          | txStart  | txEnd    |
+-----------+---------+----------------+----------+----------+
| NM_000500 |       7 | chr6           | 31973358 | 31976712 |
| NM_000500 |       7 | chr6           | 32006092 | 32009447 |
| NM_000500 |       7 | chr6_cox_hap2  |  3476743 |  3480098 |
| NM_000500 |       7 | chr6_dbb_hap3  |  3258941 |  3262295 |
| NM_000500 |       7 | chr6_dbb_hap3  |  3285308 |  3288663 |
| NM_000500 |       7 | chr6_mcf_hap5  |  3355550 |  3356556 |
| NM_000500 |       7 | chr6_mcf_hap5  |  3385937 |  3389288 |
| NM_000500 |       7 | chr6_qbl_hap6  |  3267146 |  3270501 |
| NM_000500 |       7 | chr6_ssto_hap7 |  3306068 |  3342155 |
| NM_000500 |       7 | chr6_ssto_hap7 |  3306852 |  3309422 |
+-----------+---------+----------------+----------+----------+

As you can see, these entries do not correspond to multiple versions of the same transcript (from a transcript sequence perspective, only one is being considered) but rather multiple alignments of that transcript to the genome. Your gene of interest happens to be one from a challenging region of the genome that has been the subject of haplotype investigation that has subsequently been incorporated into the human genome by the human genome reference consortium (HGRC).

Try going to the UCSC genome browser (hg19) and entering these coordinates: chr6:31,973,359-32,009,447. You will see that the positioning of NM_000500 is ambiguous and corresponds to at least two places in hg19. It also maps to five alternate haplotype chromosomes of chr6. In some of those haplotypes, the apparent tandem duplication is maintained. In a few it is not.

As a final sanity check, retrieve the sequence of NM_000500 and perform a BLAT with that sequence:

NM_000500         2122     1  2131  2131 100.0%     6   +   32006093  32009447   3355
NM_000500         2116     1  2131  2131  99.9%  6_qbl_hap6   +    3267147   3270501   3355
NM_000500         2116     1  2131  2131  99.9%  6_cox_hap2   +    3476744   3480098   3355
NM_000500         2110     1  2131  2131  99.8%  6_ssto_hap7   +    3306069   3342155  36087
NM_000500         2108     1  2131  2131  99.8%  6_mcf_hap5   +    3385938   3389288   3351
NM_000500         2104     1  2131  2131  99.6%  6_dbb_hap3   +    3285309   3288663   3355
NM_000500         2064     1  2131  2131  98.9%  6_dbb_hap3   +    3258942   3262295   3354
NM_000500         2064     1  2131  2131  98.9%     6   +   31973359  31976712   3354
NM_000500         1675   400  2131  2131  98.8%  6_ssto_hap7   +    3306853   3309422   2570
NM_000500          894  1223  2131  2131  99.3%  6_mcf_hap5   +    3355551   3356556   1006

I list the top 10 alignments from BLAT (ordered by percent identity this time instead of chromosome position), all with a percent identity higher than 99%. These appear to have a 1-to-1 relationship with those records being stored in the refGene table.

[Byron]

That is good to know, thanks. I am still confused as to why there are 10 different rows for the same transcript NM_000500. I realize there are different versions, but they seem so different and given that the join to get the version is on the RefSeq ID, how do I know which record is version 7?

[Malachi Griffith]

They are all version 7. I have updated my answer to summarize further investigation. I'm sure that you would have noticed all of this if you have included chromosome in your initial database query. ;)

[Byron]

Thank you for the further investigation!

Answer 2

[Maximilian Haeussler]

The fact that Refseq tables don't have the version right in them is often annoying. A typical mysql query concats the version number right to the refseq accession:

hgsql hg38 -NBe 'select CONCAT(refseq, ".", version), kgId from kgXref, gbCdnaInfo where gbCdnaInfo.acc=kgXref.refseq' > refseqToUcscId.tab