[ensembl-dev] Gene and RS

Thu Apr 17 13:32:07 BST 2014

The phenotype and phenotype_feature tables contain mappings between
phenotypes (some of which are diseases or disease-associated phenotypes)
and variations, genes and QTLs.

These are joined to their respective feature tables by the
phenotype_feature.object_id column to variation.name (for variations) and
gene.stable_id (for genes).

Once again though I would really recommend you try using our APIs for
retrieving this data, it will give you far fewer headaches I am sure!

Here's a tutorial for the variation part of our perl API:

http://www.ensembl.org/info/docs/api/variation/variation_tutorial.html

Regards

Will

On 17 April 2014 13:02, Igo Medeiros <igo701 at gmail.com> wrote:

> Is there a table in some base that have information about disease?
>
>
> 2014-04-16 12:10 GMT-03:00 Will McLaren <wm2 at ebi.ac.uk>:
>
> The gene table is in the core database (homo_sapiens_core_75_37):
>>
>> SELECT x.display_label
>> FROM homo_sapiens_core_75_37.gene g, homo_sapiens_core_75_37.xref x
>> WHERE g.display_xref_id = x.xref_id
>> AND g.stable_id = 'ENSG00000196218';
>>
>>
>> On 16 April 2014 15:52, Igo Medeiros <igo701 at gmail.com> wrote:
>>
>>> I am study the documentation about VEP too.
>>>
>>>
>>> 2014-04-16 11:52 GMT-03:00 Igo Medeiros <igo701 at gmail.com>:
>>>
>>>> How can I access gene table?
>>>> I saw VEP, but i need study more about.
>>>>
>>>>
>>>> 2014-04-16 11:16 GMT-03:00 Will McLaren <wm2 at ebi.ac.uk>:
>>>>
>>>> Have you considered using our Variant Effect Predictor tool (VEP)?
>>>>>
>>>>> http://www.ensembl.org/info/docs/tools/vep/index.html
>>>>>
>>>>> It's very useful for investigating novel variations found in
>>>>> sequencing experiments and the like, and can probably give you a lot of the
>>>>> information you need without having to write any code.
>>>>>
>>>>> The gene name is found in the xref (external reference) table, linked
>>>>> via the display_xref_id column in the gene table:
>>>>>
>>>>> SELECT display_label
>>>>> FROM gene g, xref x
>>>>> WHERE g.display_xref_id = x.xref_id
>>>>> AND g.stable_id = 'ENSG00000196218';
>>>>>
>>>>>
>>>>> On 16 April 2014 14:55, Igo Medeiros <igo701 at gmail.com> wrote:
>>>>>
>>>>>> Thank you,
>>>>>> both are help me.
>>>>>> I was looking just in schema homo_sapiens_variation_75_37, not in
>>>>>> homo_sapiens_core_75_37 too. Now I understand better why a lot of bases.
>>>>>> I am usuing PHP ang JQuery, but I would like to change the project,
>>>>>> that i am doing, to Perl and BioJS.
>>>>>>
>>>>>> There is a other thing. How can I find for gene name? I am looking
>>>>>> for all schema, but until now not found.
>>>>>>
>>>>>> All this is because If don't exist the variation found in lab, in
>>>>>> UFPA, in the schemas, I have to find all things connected with that area of
>>>>>> sequence. And publications.
>>>>>>
>>>>>>
>>>>>>
>>>>>> 2014-04-16 10:02 GMT-03:00 Will McLaren <wm2 at ebi.ac.uk>:
>>>>>>
>>>>>> If you do want a quick way to get all rsIDs that fall in a gene, this
>>>>>>> query should work:
>>>>>>>
>>>>>>> SELECT vf.variation_name
>>>>>>> FROM homo_sapiens_variation_75_37.variation_feature vf,
>>>>>>> homo_sapiens_core_75_37.gene g
>>>>>>> WHERE g.seq_region_id = vf.seq_region_id
>>>>>>> AND vf.seq_region_end >= g.seq_region_start
>>>>>>> AND vf.seq_region_start <= g.seq_region_end
>>>>>>> AND vf.source_id = 1
>>>>>>> AND g.stable_id = 'ENSG00000196218'
>>>>>>>
>>>>>>> Replace ENSG00000196218 with the stable identifier of your gene of
>>>>>>> interest. Remove "AND vf.source_id = 1" if you also want variation features
>>>>>>> from sources that aren't dbSNP.
>>>>>>>
>>>>>>> As Kieron says, we would encourage you to use our perl API, REST API
>>>>>>> or BioMart, all of which can accomplish this same task without the risks of
>>>>>>> schema changes.
>>>>>>>
>>>>>>> The REST API is particularly suited to this sort of task; the
>>>>>>> following URL retrieves all variants in a gene with ID ENSG00000157764:
>>>>>>>
>>>>>>>
>>>>>>> http://beta.rest.ensembl.org/feature/id/ENSG00000157764?feature=variation;content-type=application/json
>>>>>>>
>>>>>>> See http://beta.rest.ensembl.org/documentation/info/feature_id for
>>>>>>> more details.
>>>>>>>
>>>>>>> Regards
>>>>>>>
>>>>>>> Will McLaren
>>>>>>> Ensembl Variation
>>>>>>>
>>>>>>>
>>>>>>> On 16 April 2014 13:46, Kieron Taylor <ktaylor at ebi.ac.uk> wrote:
>>>>>>>
>>>>>>>> Ensembl has a table for each genetic feature type, hence we have a
>>>>>>>> Transcript table, a Gene table, and one for each class of variation. Each
>>>>>>>> table shares a common set of fields, plus more that are specific to that
>>>>>>>> feature. This is slightly contrary to a traditionally normalised schema.
>>>>>>>>
>>>>>>>> It is not clear to me what you mean by "RS inside a Gene". If you
>>>>>>>> wish to understand our schema, please take a look at the following link.
>>>>>>>>
>>>>>>>> http://www.ensembl.org/info/docs/api/core/core_schema.html
>>>>>>>>
>>>>>>>> I urge you to find another way to access our data if at all
>>>>>>>> possible. Direct SQL queries against our schema is sometimes the right
>>>>>>>> solution, but suffers when we have to make changes in later releases. Our
>>>>>>>> API and other services insulate you from these changes. Also, some commonly
>>>>>>>> required data is computed and therefore cannot be found in the database at
>>>>>>>> all.
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>>
>>>>>>>> Kieron Taylor
>>>>>>>> Ensembl Core
>>>>>>>>
>>>>>>>>
>>>>>>>> On 16/04/2014 13:17, Igo Medeiros wrote:
>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>> how can I found relation, in DB, between Gene and all RS inside
>>>>>>>>> Gene?
>>>>>>>>> there are columns like seq_region_start, seq_region_end ans
>>>>>>>>> seq_region_id those repeat in three tables(variation_feature,
>>>>>>>>> structural_variation_feature and phenotype_feature), what the
>>>>>>>>> diferent
>>>>>>>>> between them?
>>>>>>>>> Sorry for my english, its not so good. And thank you for help.
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Igo Paixão de Medeiros
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> Dev mailing list    Dev at ensembl.org
>>>>>>>> Posting guidelines and subscribe/unsubscribe info:
>>>>>>>> http://lists.ensembl.org/mailman/listinfo/dev
>>>>>>>> Ensembl Blog: http://www.ensembl.info/
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> Dev mailing list    Dev at ensembl.org
>>>>>>> Posting guidelines and subscribe/unsubscribe info:
>>>>>>> http://lists.ensembl.org/mailman/listinfo/dev
>>>>>>> Ensembl Blog: http://www.ensembl.info/
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Igo Paixão de Medeiros
>>>>>>
>>>>>> _______________________________________________
>>>>>> Dev mailing list    Dev at ensembl.org
>>>>>> Posting guidelines and subscribe/unsubscribe info:
>>>>>> http://lists.ensembl.org/mailman/listinfo/dev
>>>>>> Ensembl Blog: http://www.ensembl.info/
>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Dev mailing list    Dev at ensembl.org
>>>>> Posting guidelines and subscribe/unsubscribe info:
>>>>> http://lists.ensembl.org/mailman/listinfo/dev
>>>>> Ensembl Blog: http://www.ensembl.info/
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Igo Paixão de Medeiros
>>>>
>>>
>>>
>>>
>>> --
>>> Igo Paixão de Medeiros
>>>
>>> _______________________________________________
>>> Dev mailing list    Dev at ensembl.org
>>> Posting guidelines and subscribe/unsubscribe info:
>>> http://lists.ensembl.org/mailman/listinfo/dev
>>> Ensembl Blog: http://www.ensembl.info/
>>>
>>>
>>
>> _______________________________________________
>> Dev mailing list    Dev at ensembl.org
>> Posting guidelines and subscribe/unsubscribe info:
>> http://lists.ensembl.org/mailman/listinfo/dev
>> Ensembl Blog: http://www.ensembl.info/
>>
>>
>
>
> --
> Igo Paixão de Medeiros
>
> _______________________________________________
> Dev mailing list    Dev at ensembl.org
> Posting guidelines and subscribe/unsubscribe info:
> http://lists.ensembl.org/mailman/listinfo/dev
> Ensembl Blog: http://www.ensembl.info/
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20140417/f38cc72c/attachment.html>