Ontology Lookup Service logo

Ontology Lookup Service

spacer

Download the OLS dump file

The OLS now provides a database export in the form of a mySQL dump file. This file is generated on a weekly basis and can be obtained from the EBI FTP server: ftp://ftp.ebi.ac.uk//pub/databases/ols/sqldump

All files are named and timestamped in the form of ols-YYYY-MM-DD-HHMM.sql.gz. Simply download the latest file and unzip it using you program of choice to a temporary location.

Import the OLS dump file

To load the dump file to your mySQL database server, log in to your mySQL database server with a username and password that has enough privileges to create, insert and select. Then, use the following commands:


    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 1 to server version: 5.0.27-community-nt

    mysql> create database ols;
    Query OK, 1 row affected (0.08 sec)

    mysql> use ols;
    Database changed

    mysql> source /PATH/TO/UNZIPPED/FILE/ols-YYYY-MM-DD-HHMM.sql;
    Query OK, 0 rows affected (0.08 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    [and many other lines output not shown for clarity]

The import can take as long as 60 minutes to complete, depending on the speed of your hardware.

Use the OLS dump file

Here are some example queries that can be run against your newly imported data:

Search for a term by name:

select t.* from term t left join term_synonym ts on t.term_id = ts.term_id where term_name like '%mitochondrion%' or synonym_value like '%mitochondrion%';

This query will retrieve all term information based on a given name. It will search all possible synonyms of the preferred term name as well. Please note however that the search will be case sensitive.


Search metadata for a term by term accession:

select * from annotation a, term t where t.identifier = 'GO:0005739' and t.term_id = a.term_id;

select * from term_synonym ts, term t where t.identifier = 'GO:0005739' and t.term_id = ts.term_id;

select * from dbxref db, term t where t.identifier = 'GO:0005739' and t.term_id = db.term_id;

The first query will retrieve annotations associated with a given term. The second will retrieve all synonyms for that term. The last will retrieve cross-references associated with it.


Search for relationships for a term by term accession:

select subject_term.identifier, subject_term.term_name as child,
predicate_term.term_name as relation,
object_term.identifier, object_term.term_name as parent from
term subject_term,
term predicate_term,
term object_term,
term_relationship tr
where
tr.subject_term_id = subject_term.term_id and
tr.predicate_term_id = predicate_term.term_id and
tr.object_term_id = object_term.term_id and
object_term.identifier = 'GO:0005739';

This query will return all the direct children of a term:

+------------+--------------------------+----------+------------+---------------+
| identifier | child                    | relation | identifier | parent        |
+------------+--------------------------+----------+------------+---------------+
| GO:0016007 | mitochondrial derivative | is_a     | GO:0005739 | mitochondrion |
| GO:0016006 | Nebenkern                | is_a     | GO:0005739 | mitochondrion |
| GO:0044429 | mitochondrial part       | part_of  | GO:0005739 | mitochondrion |
+------------+--------------------------+----------+------------+---------------+
3 rows in set (0.00 sec)

select subject_term.identifier, subject_term.term_name as child,
predicate_term.term_name as relation,
object_term.identifier, object_term.term_name as parent from
term subject_term,
term predicate_term,
term object_term,
term_relationship tr
where
tr.subject_term_id = subject_term.term_id and
tr.predicate_term_id = predicate_term.term_id and
tr.object_term_id = object_term.term_id and
subject_term.identifier = 'GO:0005739';

This query will return all the direct parents of a term:

+------------+---------------+----------+------------+------------------------------------------+
| identifier | child         | relation | identifier | parent                                   |
+------------+---------------+----------+------------+------------------------------------------+
| GO:0005739 | mitochondrion | is_a     | GO:0043231 | intracellular membrane-bounded organelle |
| GO:0005739 | mitochondrion | is_a     | GO:0044444 | cytoplasmic part                         |
+------------+---------------+----------+------------+------------------------------------------+
2 rows in set (0.00 sec)
spacer
spacer