This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Get A File To Cross Reference Ensembl Gene Ids And Alias

I have a list of Ensembl gene IDs. I would like a file to cross reference them to aliases from kgAlias.

Preferably it would be some kind of mysql statements with a join of of some kind:

mysql -N -h  genome-mysql.cse.ucsc.edu -A -u genome -D hg18 -e 'select name,name2,exonStarts,exonEnds from ensGene'

UPDATE: Pierre answered this question:

mysql -N -h  genome-mysql.cse.ucsc.edu -A -u genome -D hg18 -e '\
 select QUERY.name,QUERY.name2,QUERY.geneSymbol from
 (select X.*,\
 G.* from ensGene as G,\
 knownToEnsembl as KE,\
 kgXref as X where\
 G.name=KE.value and KE.name=X.kgID)\
 as QUERY'
conversion mysql ensembl
• 4,758 views • link
written by D W • D W

2 answers

From the table Browser ("describe table schema"), you get:

For ensGene:

hg19.knownToEnsembl.value (via ensGene.name)

From knownToEnsembl

hg19.kgXref.kgID (via knownToEnsembl.name)

All in one, your request would be:

• 1 views • link
updated by Ram Ram • written by Pierre Lindenbaum Pierre Lindenbaum

Thanks very much. May I ask, how can I restrict the results to output only the name (ex.ENST00000250784), name2 (ex. ENSG00000129824), and geneSymbol (ex. RPS4Y1)?

• 0 views • link
written by D W • D W

thanks for the link

• 0 views • link
written by D W • D W

Pierre's solution was what I was looking for. But just for completeness you can get this from the web as well.

  • GOTO: http://www.biomart.org/biomart/martview/
  • Select database: Ensembl Genes Select
  • dataset: Homo Sapiens Click
  • Attributes Exapand: External
  • References Check: HGNC automatic gene
  • name Click Top Button: Results
  • Select output desired

http://www.biomart.org/biomart/martview/6a219783b4cc3e15820e9ea9f654e555

• 0 views • link
written by D W • D W

Log in to answer this question.

More posts like this