MySQL: Mapping GRCh38 coordinates to GRCh37 using ensembldb

Question

[乙]

Hello,

I would like to know how can I obtain coordinates from one assembly to another, say GRCh38 to GRCh37, using MySQL connection to ensembldb.ensembl.org ?

I know ensembl has a REST API (http://rest.ensembl.org) that can be used to question the database but I am looking for a direct connection that could ease the implementation into my workflow using a single query (instead of a script with API).

See this example for the REST API: http://rest.ensembl.org/map/human/GRCh38/1:942451..942451:1/GRCh37? The coordinates of the chromosome 1 on 942,451 are converted to GRCh37.

I would like to do similarly but using a MySQL query.

For example, I have a file containing the following coordinates in GRCh38:

#chrom  GRCh38_pos
1       69511
1       930314
1       942451
1       953279
1       953778

I expect to get a file containing the following:

#chrom  GRCh38_pos  GRCh37_pos
1       69511       69511
1       930314      865694
1       942451      877831
1       953279      888659
1       953778      889158

How can I do this using MySQL query connection to ensembldb ?

PS: to connect to MySQL ensembl:

mysql -u anonymous --host=ensembldb.ensembl.org

or

mysql -A -u anonymous --host=ensembldb.ensembl.org to turn off completion of table and column names.

Thank you.

Answer 1

[Pierre Lindenbaum]

there is no way to do this inside mysql. You need to use liftover http://hgdownload.soe.ucsc.edu/admin/exe/linux.x86_64/ and a chain file: http://hgdownload.cse.ucsc.edu/goldenpath/hg19/liftOver/

[乙]

Hello Pierre,

Could I ask why not ? Isn't ensembl REST API based on ensembl MySQL ? If yes, shouldn't it then be possible ?

[Pierre Lindenbaum]

because not everything is computed in mysql.