Skip to content

Instantly share code, notes, and snippets.

@ypandit
Created October 11, 2012 15:57
Show Gist options
  • Save ypandit/3873380 to your computer and use it in GitHub Desktop.
Save ypandit/3873380 to your computer and use it in GitHub Desktop.
SQL to get data in GO Annotation File Format (GAF)
SELECT 'dictyBase' db, /* Column 1 */
GENE_ID.accession db_id, /* Column 2 */
GENE.uniquename gene_symbol, /* Column 3 */
TO_CHAR(q_prop.value) qualifier, /* Column 4 */
'GO' || ':' || GO_ID.accession go_id, /* Column 5 */
'PMID' || ':' || pub.uniquename db_reference, /* Column 6 */
evsyn.synonym_ evidence_code, /* Column 7 */
TO_CHAR(with_prop.value) with_from, /* Column 8 */
cv.name aspect, /* Column 9 */
GENE.name object_name, /* Column 10 */
'' object_synonym, /* Column 11 */
type.name object_type, /* Column 12 */
'taxon' || ':' || '44689' taxon, /* Column 13 */
TO_CHAR(date_prop.value) date_created, /* Column 14 */
TO_CHAR(source_prop.value) assigned_by, /* Column 15 */
'' annotation_extension, /* Column 16 */
'' gp_id /* Column 17 */
FROM feature_cvterm fcvt
/* Gene */
JOIN feature GENE ON GENE.feature_id = fcvt.feature_id
JOIN dbxref GENE_ID ON GENE_ID.dbxref_id = GENE.dbxref_id
JOIN cvterm type ON type.cvterm_id = GENE.type_id
/* Qualifier */
JOIN feature_cvtermprop q_prop ON q_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm qterm ON qterm.cvterm_id = q_prop.type_id
/* Date created */
JOIN feature_cvtermprop date_prop ON date_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm date_term ON date_term.cvterm_id = date_prop.type_id
/* Assigned by */
JOIN feature_cvtermprop source_prop ON source_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm source_term ON source_term.cvterm_id = source_prop.type_id
/* GO */
JOIN cvterm GO ON GO.cvterm_id = fcvt.cvterm_id
JOIN cv ON cv.cv_id = GO.cv_id
JOIN dbxref GO_ID ON GO_ID.dbxref_id = GO.dbxref_id
JOIN db ON db.db_id = GO_ID.db_id
/* Publication */
JOIN pub ON pub.pub_id = fcvt.pub_id
/* Evidence code */
JOIN feature_cvtermprop ev_prop ON ev_prop.feature_cvterm_id=fcvt.feature_cvterm_id
JOIN cvterm evterm ON evterm.cvterm_id=ev_prop.type_id
JOIN cv ev ON ev.cv_id=evterm.cv_id
JOIN cvtermsynonym evsyn on evterm.cvterm_id=evsyn.cvterm_id
/* Organism */
JOIN organism ON organism.organism_id = gene.organism_id
/* With-From */
JOIN feature_cvtermprop with_prop ON with_prop.feature_cvterm_id = fcvt.feature_cvterm_id
JOIN cvterm with_term ON with_term.cvterm_id = with_prop.type_id
WHERE cv.name IN('molecular_function', 'biological_process', 'cellular_component')
AND db.name = 'GO'
AND GO.is_obsolete = 0
AND ev.name like 'evidence_code%'
AND qterm.name = 'qualifier'
AND date_term.name = 'date'
AND with_term.name = 'with'
AND source_term.name = 'source'
AND pub.pubplace = 'PUBMED'
AND organism.common_name = 'dicty'
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment