[ensembl-dev] ensembl_ontology_75 schema

[Kieron Taylor]

Hi Ryan,

You can find an abundance of suitable queries to help you in the source 
of our OntologyTermAdaptor (1)

You should probably use that adaptor to insulate yourself from future 
changes in our API, but it would also help you formulate some useful 
queries. The database contains a flattened ontology, which makes 
querying less recursive.

Once you have your list of ontology terms to search on, you can hit the 
core schema in the xref tables. The xref table keeps things such as GO 
accessions in the dbprimary_acc field. You then connect to Ensembl 
objects such as Genes via the ontology_xref and object_xref tables.

To summarise, the canonical approach would be to use the 
OntologyTermAdaptor to build a list, then to run that through 
DBEntryAdaptor->list_gene_ids_by_extids() to give you the corresponding 
access to Ensembl Genes. There are other methods in there too to give 
you other object types.

If you're determined to do it in SQL, the join would probably go 
ontology->term->xref->ontology_xref->object_xref->gene



(1) 
http://www.ensembl.org/info/docs/Doxygen/core-api/classBio_1_1EnsEMBL_1_1DBSQL_1_1OntologyTermAdaptor.html

-- 
Kieron Taylor PhD.
Ensembl Core team
EBI

On 17/03/2014 16:17, Ryan Brown wrote:
> Ensembl:
>
> I am interested in getting  information related to the Term table in the
> ensembl_ontology_75 database. Can you describe how rows in this table
> relate to tables and columns in the ensembl_core_75 database? My goal is
> to have a query where I can specify the name field in the ontology term
> table, perform a set of inner joins, and then get data from other tables
> / databases.
>
> Thanks
> Ryan
>
> --
> *Ryan M. Brown
> XenoGen Biosystems*
> The information contained in this message (including any files
> transmitted with this message) may contain proprietary, trade secret, or
> other confidential and/or legally privileged information. Any
> information contained in this message (or in any files transmitted with
> this message) is confidential and cannot be shared with any third party
> without prior written approval. This message is intended to be read only
> by the individual or entity to whom it is addressed or by their
> designee. If the reader of this message is not the intended recipient,
> you are on notice that any use, disclosure, copying or distribution of
> this message, in any form, is strictly prohibited. If you have received
> this message in error, please immediately notify the sender and destroy
> all copies of this message in your possession, custody or control.
>
>
> _______________________________________________
> 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/
>