Difference between revisions of "iRefIndex/PBR Notes"

From irefindex
(→‎Queries: Restrict to 9606 interactors.)
(Added explanatory queries, details. Moved observations to the top.)
 
(12 intermediate revisions by the same user not shown)
Line 5: Line 5:
 
Importing MITAB output from the iRefIndex 5.1 build and a build done using the BIND PSI-XML data, then comparing the interaction details for BIND, the following distributions are observed:
 
Importing MITAB output from the iRefIndex 5.1 build and a build done using the BIND PSI-XML data, then comparing the interaction details for BIND, the following distributions are observed:
  
=== Interactions ===
+
=== Interactions (Human) ===
  
{|
+
{| border="1" cellpadding="5" cellspacing="0"
|              ||         ||     || '''iRefIndex 5.1'''
+
|              ||                                         ||                                     || '''iRefIndex 5.1'''
 
|-
 
|-
|              || (Total) ||     || (9811)
+
|              || (Total)                                 ||                                     || [[#all_human_interactions_irefindex|(7122)]]
 
|-
 
|-
|              ||         ||     || 3818
+
|              ||                                         || ''Exclusive''                        || ''[[#irefindex_human_interactions|1324]]''
 
|-
 
|-
| '''PSI-XML''' || (8916)  || 2923 || 5993
+
| '''PSI-XML''' || [[#all_human_interactions_xml|(8706)]] || ''[[#xml_human_interactions|2908]]'' || [[#common_human_interactions|5798]] (Common)
 
|}
 
|}
  
 
To summarise:
 
To summarise:
  
* Common interactions: 5993
+
* Common human interactions: [[#common_human_interactions|5798]]
* Interactions only in iRefIndex 5.1: 3818
+
* Human interactions only in iRefIndex 5.1: [[#irefindex_human_interactions|1324]]
* Interactions only in BIND PSI-XML: 2923
+
* Human interactions only in BIND PSI-XML: [[#xml_human_interactions|2908]]
  
=== Interactors ===
+
=== Interactors (Human) ===
  
{|
+
{| border="1" cellpadding="5" cellspacing="0"
|              ||        ||     || '''iRefIndex 5.1'''
+
|              ||        ||               || '''iRefIndex 5.1'''
 
|-
 
|-
|              || (Total) ||     || (6896)
+
|              || (Total) ||               || (5669)
 
|-
 
|-
|              ||        ||     || 2511
+
|              ||        || ''Exclusive'' || ''1450''
 
|-
 
|-
| '''PSI-XML''' || (5324)  || 939  || 4385
+
| '''PSI-XML''' || (5152)  || ''933''      || 4219 (Common)
 
|}
 
|}
  
 
To summarise:
 
To summarise:
  
* Common interactors: 4385
+
* Common interactors: 4219
* Interactors only in iRefIndex 5.1: 2511
+
* Interactors only in iRefIndex 5.1: 1450
* Interactors only in BIND PSI-XML: 939
+
* Interactors only in BIND PSI-XML: 933
 +
 
 +
=== BIND Identifiers (Human Interactions) ===
 +
 
 +
There are 183614 [[#all_human|distinct human interaction identifiers]] in the iRefIndex 5.1 release, 188690 in the BIND PSI-XML dataset.
 +
 
 +
There are 10431 [[#all_bind|distinct human BIND interaction identifiers]] in the iRefIndex 5.1 release, 12093 in the BIND PSI-XML dataset.
 +
 
 +
The following distribution of BIND human interaction identifiers was observed:
 +
 
 +
{| border="1" cellpadding="5" cellspacing="0"
 +
|              ||                        ||                            || '''iRefIndex 5.1'''
 +
|-
 +
|              || (Total)                ||                            || [[#all_bind|(10431)]]
 +
|-
 +
|              ||                        || ''Exclusive''              || ''[[#irefindex_all_bind|673]]''
 +
|-
 +
| '''PSI-XML''' || [[#all_bind|(12093)]]  || ''[[#xml_all_bind|2335]]'' || [[#common_all_bind|9758]] (Common)
 +
|}
 +
 
 +
In other words:
 +
 
 +
* There are [[#common_all_bind|9758]] identifiers in common.
 +
* [[#irefindex_all_bind|673]] identifiers only appear in the iRefIndex 5.1 release.
 +
* [[#xml_all_bind|2335]] identifiers only appear in the BIND PSI-XML dataset.
 +
 
 +
Where the correspondence between identifiers and interactions is considered, it might be expected that BIND identifiers should not refer to different interactions. However, if we take the BIND identifiers for interactions exclusive to the iRefIndex 5.1 release and the BIND PSI-XML dataset, as well as for those common to both sources, we see that some identifiers are associated with interactions from these disjoint sets:
 +
 
 +
{| border="1" cellpadding="5" cellspacing="0"
 +
|                              || '''Common'''                    || '''iRefIndex 5.1''' ''only'' || '''BIND PSI-XML''' ''only''
 +
|-
 +
| '''Common'''                || [[#common_bind|(8639)]]        ||                              ||
 +
|-
 +
| '''iRefIndex 5.1''' ''only'' || [[#i5_common_bind|76]]          || [[#i5_bind|(1613)]]          ||   
 +
|-
 +
| '''BIND PSI-XML''' ''only''  || [[#xml_common_bind|0]]          || [[#i5_xml_bind|1088]]        || [[#xml_bind|(3248)]]
 +
|}
 +
 
 +
In other words:
 +
 
 +
* There are [[#common_bind|8639]] common BIND identifiers in total.
 +
* There are [[#i5_bind|1613]] BIND identifiers appearing for interactions only in iRefIndex 5.1.
 +
* There are [[#xml_bind|3248]] BIND identifiers appearing for interactions only in the BIND PSI-XML dataset.
 +
* Although the intersection between "iRefIndex 5.1 only" and "BIND PSI-XML only" should yield zero interactions, since by definition an interaction in one of these sets should not appear in another, [[#i5_xml_bind|1088]] BIND identifiers appear in both.
 +
* Of the interactions that should only be referenced by iRefIndex 5.1, [[#i5_common_bind|76]] BIND identifiers also refer to interactions in both iRefIndex 5.1 and the BIND PSI-XML dataset.
 +
* Of the interactions that should only be referenced by the BIND PSI-XML dataset, [[#xml_common_bind|0]] BIND identifiers also refer to interactions in both iRefIndex 5.1 and the BIND PSI-XML dataset. This is as expected.
 +
 
 +
The <tt>export_i5_common_bind</tt> and <tt>export_i5_xml_bind</tt> result sets contain examples of such BIND identifiers which refer to different interactions. For example:
 +
 
 +
* BIND identifier 100771 refers to <tt>5TlbBV7VB0MFa/R1cmyeByxSFjw</tt> in only the iRefIndex 5.1 release, but also to <tt>pXZtvCDYsluwWrmdXf5FJmQ0y54</tt> in that release and in the BIND PSI-XML dataset.
 +
* BIND identifier 100321 refers to <tt>VwWuNAmhR/FDx6FtF6FacwAmzr0</tt> in only the iRefIndex 5.1 release, but to <tt>Dr5ri7vTj02leEUMKr35jj1fP/c</tt> in only the BIND PSI-XML dataset.
 +
 
 +
See [https://hfaistos.uio.no/bugzilla/show_bug.cgi?id=133 bug #133] for further details and discussion.
 +
 
 +
See [https://hfaistos.uio.no/bugzilla/show_bug.cgi?id=135 bug #135] for details and discussion of the broader activity.
  
 
== Queries ==
 
== Queries ==
  
Common interactions:
+
<div id="all_human_interactions_irefindex">All human interactions in iRefIndex 5.1:
 +
 
 +
select count(distinct i5_mitab_interactions.rigid)
 +
from i5_mitab_sources
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  and taxA = 9606
 +
  and taxB = 9606
 +
where i5_mitab_sources.name = 'bind';
 +
</div>
 +
 
 +
<div id="all_human_interactions_xml">All human interactions in BIND PSI-XML:
 +
 
 +
select count(distinct mitab_interactions.rigid)
 +
from mitab_sources
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
  and taxA = 9606
 +
  and taxB = 9606
 +
where mitab_sources.name = 'bind';
 +
</div>
 +
 
 +
<div id="common_human_interactions">Common human interactions (join the above queries on rigid):
  
 
  select count(distinct mitab_sources.rigid)
 
  select count(distinct mitab_sources.rigid)
 
  from i5_mitab_sources
 
  from i5_mitab_sources
inner join mitab_sources
 
  on i5_mitab_sources.rigid = mitab_sources.rigid
 
  and i5_mitab_sources.name = mitab_sources.name
 
 
  inner join i5_mitab_interactions
 
  inner join i5_mitab_interactions
 
   on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 
   on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 
   and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 
   and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 
   and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 
   and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  where mitab_sources.name = 'bind'
+
  and i5_mitab_interactions.taxA = 9606
   and taxA = 9606
+
  and i5_mitab_interactions.taxB = 9606
   and taxB = 9606;
+
  inner join mitab_sources
 +
  on i5_mitab_sources.rigid = mitab_sources.rigid
 +
  and i5_mitab_sources.name = mitab_sources.name
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
   and mitab_interactions.taxA = 9606
 +
   and mitab_interactions.taxB = 9606
 +
where mitab_sources.name = 'bind';
 +
</div>
  
Interactions only in iRefIndex 5.1:
+
<div id="irefindex_human_interactions">Human interactions only in iRefIndex 5.1:
  
 
  select count(distinct i5_mitab_sources.rigid)
 
  select count(distinct i5_mitab_sources.rigid)
Line 66: Line 153:
 
   and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 
   and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 
   and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 
   and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  and i5_mitab_interactions.taxA = 9606
 +
  and i5_mitab_interactions.taxB = 9606
 
  where i5_mitab_sources.name = 'bind'
 
  where i5_mitab_sources.name = 'bind'
  and taxA = 9606
+
   and i5_mitab_sources.rigid not in (
  and taxB = 9606
+
    select distinct mitab_sources.rigid
   and i5_mitab_sources.rigid not in (select rigid from mitab_sources where mitab_sources.name = 'bind');
+
    from mitab_sources
 +
    inner join mitab_interactions
 +
      on mitab_sources.uidA = mitab_interactions.uidA
 +
      and mitab_sources.uidB = mitab_interactions.uidB
 +
      and mitab_sources.rigid = mitab_interactions.rigid
 +
      and mitab_interactions.taxA = 9606
 +
      and mitab_interactions.taxB = 9606
 +
    where mitab_sources.name = 'bind'
 +
    );
 +
</div>
  
Interactions only in BIND PSI-XML:
+
<div id="xml_human_interactions">Human interactions only in BIND PSI-XML:
  
  select count(distinct rigid)
+
  select count(distinct mitab_sources.rigid)
 
  from mitab_sources
 
  from mitab_sources
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
  and mitab_interactions.taxA = 9606
 +
  and mitab_interactions.taxB = 9606
 
  where mitab_sources.name = 'bind'
 
  where mitab_sources.name = 'bind'
   and rigid not in (
+
   and mitab_sources.rigid not in (
  select count(distinct i5_mitab_sources.rigid)
+
    select distinct i5_mitab_sources.rigid
  from i5_mitab_sources
+
    from i5_mitab_sources
  inner join i5_mitab_interactions
+
    inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
+
      on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
    and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
+
      and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
    and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
+
      and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  where i5_mitab_sources.name = 'bind'
+
      and i5_mitab_interactions.taxA = 9606
     and taxA = 9606
+
      and i5_mitab_interactions.taxB = 9606
    and taxB = 9606
+
    where i5_mitab_sources.name = 'bind'
  );
+
     );
 +
</div>
  
 
Common interactors:
 
Common interactors:
Line 105: Line 210:
 
   ) as X
 
   ) as X
 
  inner join (
 
  inner join (
   select uidA as uid
+
   select mitab_sources.uidA as uid
 
   from mitab_sources
 
   from mitab_sources
   where name = 'bind'
+
  inner join mitab_interactions
 +
    on mitab_sources.uidA = mitab_interactions.uidA
 +
   where name = 'bind' and taxA = 9606
 
   union
 
   union
   select uidB as uid
+
   select mitab_sources.uidB as uid
 
   from mitab_sources
 
   from mitab_sources
   where name = 'bind'
+
  inner join mitab_interactions
 +
    on mitab_sources.uidB = mitab_interactions.uidB
 +
   where name = 'bind' and taxB = 9606
 
   ) as Y
 
   ) as Y
 
   on X.uid = Y.uid;
 
   on X.uid = Y.uid;
Line 131: Line 240:
 
   where name = 'bind' and taxB = 9606
 
   where name = 'bind' and taxB = 9606
 
   ) as X
 
   ) as X
  where X.uid not in (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind');
+
  where X.uid not in (
 +
  select mitab_sources.uidA as uid
 +
  from mitab_sources
 +
  inner join mitab_interactions
 +
    on mitab_sources.uidA = mitab_interactions.uidA
 +
  where name = 'bind' and taxA = 9606
 +
  union
 +
  select mitab_sources.uidB as uid
 +
  from mitab_sources
 +
  inner join mitab_interactions
 +
    on mitab_sources.uidB = mitab_interactions.uidB
 +
  where name = 'bind' and taxB = 9606
 +
  );
  
 
Interactors only in BIND PSI-XML:
 
Interactors only in BIND PSI-XML:
  
 
  select count(distinct X.uid)
 
  select count(distinct X.uid)
  from (select uidA as uid from mitab_sources where name = 'bind' union select uidB as uid from mitab_sources where name = 'bind') as X
+
  from (
 +
  select mitab_sources.uidA as uid
 +
  from mitab_sources
 +
  inner join mitab_interactions
 +
    on mitab_sources.uidA = mitab_interactions.uidA
 +
  where name = 'bind' and taxA = 9606
 +
  union
 +
  select mitab_sources.uidB as uid
 +
  from mitab_sources
 +
  inner join mitab_interactions
 +
    on mitab_sources.uidB = mitab_interactions.uidB
 +
  where name = 'bind' and taxB = 9606
 +
  ) as X
 
  where X.uid not in (
 
  where X.uid not in (
 
   select i5_mitab_sources.uidA as uid
 
   select i5_mitab_sources.uidA as uid
Line 150: Line 283:
 
   where name = 'bind' and taxB = 9606
 
   where name = 'bind' and taxB = 9606
 
   );
 
   );
 +
 +
== Retrieving BIND Identifiers ==
 +
 +
The following queries determine the appropriate set of interactions and then derive the BIND identifiers from them.
 +
 +
<div id="all_human">All distinct human interaction identifiers in iRefIndex 5.1:
 +
 +
select count(distinct uid)
 +
from mitab_interaction_identifiers
 +
inner join mitab_interactions
 +
  on mitab_interaction_identifiers.rigid = mitab_interactions.rigid
 +
  and mitab_interaction_identifiers.uidA = mitab_interactions.uidA
 +
  and mitab_interaction_identifiers.uidB = mitab_interactions.uidB
 +
  and taxA = 9606
 +
  and taxB = 9606;
 +
 +
All distinct human interaction identifiers in BIND PSI-XML:
 +
 +
select count(distinct uid)
 +
from i5_mitab_interaction_identifiers
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_interaction_identifiers.rigid = i5_mitab_interactions.rigid
 +
  and i5_mitab_interaction_identifiers.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_interaction_identifiers.uidB = i5_mitab_interactions.uidB
 +
  and taxA = 9606
 +
  and taxB = 9606;
 +
</div>
 +
 +
<div id="all_bind">All BIND identifiers for human interactions in iRefIndex 5.1:
 +
 +
create table export_all_i5_bind as
 +
select distinct i5_mitab_interaction_identifiers.uid
 +
from i5_mitab_sources
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  and i5_mitab_interactions.taxA = 9606
 +
  and i5_mitab_interactions.taxB = 9606
 +
inner join i5_mitab_interaction_identifiers
 +
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
where name = 'bind' and uid like 'bind:%';
 +
 +
All BIND identifiers for human interactions in BIND PSI-XML:
 +
 +
create table export_all_xml_bind as
 +
select distinct mitab_interaction_identifiers.uid
 +
from mitab_sources
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
  and mitab_interactions.taxA = 9606
 +
  and mitab_interactions.taxB = 9606
 +
inner join mitab_interaction_identifiers
 +
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
where name = 'bind' and uid like 'bind:%';
 +
</div>
 +
 +
<div id="common_all_bind">Common BIND identifiers for human interactions:
 +
 +
create table export_all_common_bind as
 +
select distinct export_all_i5_bind.uid
 +
from export_all_i5_bind
 +
inner join export_all_xml_bind
 +
  on export_all_i5_bind.uid = export_all_xml_bind.uid;
 +
</div>
 +
 +
<div id="irefindex_all_bind">BIND identifiers for human interactions only in iRefIndex 5.1:
 +
 +
select distinct uid
 +
from export_all_i5_bind
 +
where uid not in (
 +
  select uid
 +
  from export_all_xml_bind
 +
  );
 +
</div>
 +
 +
<div id="xml_all_bind">BIND identifiers for human interactions only in PSI-XML:
 +
 +
select distinct uid
 +
from export_all_xml_bind
 +
where uid not in (
 +
  select uid
 +
  from export_all_i5_bind
 +
  );
 +
</div>
 +
 +
<div id="common_bind">BIND identifiers '''for common interactions''' where those interactions are known by the '''same identifier''' (join the above queries on rigid, matching the uid values):
 +
 +
create table export_common_bind as
 +
select distinct i5_mitab_interaction_identifiers.uid
 +
from i5_mitab_sources
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  and i5_mitab_interactions.taxA = 9606
 +
  and i5_mitab_interactions.taxB = 9606
 +
inner join i5_mitab_interaction_identifiers
 +
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
inner join mitab_sources
 +
  on i5_mitab_sources.rigid = mitab_sources.rigid
 +
  and i5_mitab_sources.name = mitab_sources.name
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
  and mitab_interactions.taxA = 9606
 +
  and mitab_interactions.taxB = 9606
 +
inner join mitab_interaction_identifiers
 +
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
where mitab_sources.name = 'bind'
 +
  and i5_mitab_interaction_identifiers.uid = mitab_interaction_identifiers.uid
 +
  and i5_mitab_interaction_identifiers.uid like 'bind:%';
 +
</div>
 +
 +
<div id="common_bind_plus">BIND identifiers and RIG identifiers from the above:
 +
 +
create table export_common_bind_plus as
 +
select distinct i5_mitab_interaction_identifiers.uid, i5_mitab_interaction_identifiers.rigid
 +
from i5_mitab_sources
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  and i5_mitab_interactions.taxA = 9606
 +
  and i5_mitab_interactions.taxB = 9606
 +
inner join i5_mitab_interaction_identifiers
 +
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
inner join mitab_sources
 +
  on i5_mitab_sources.rigid = mitab_sources.rigid
 +
  and i5_mitab_sources.name = mitab_sources.name
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
  and mitab_interactions.taxA = 9606
 +
  and mitab_interactions.taxB = 9606
 +
inner join mitab_interaction_identifiers
 +
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
where mitab_sources.name = 'bind'
 +
  and i5_mitab_interaction_identifiers.uid = mitab_interaction_identifiers.uid
 +
  and i5_mitab_interaction_identifiers.uid like 'bind:%';
 +
</div>
 +
 +
<div id="i5_bind">BIND identifiers '''for interactions''' only in iRefIndex 5.1:
 +
 +
create table export_i5_bind as
 +
select distinct i5_mitab_interaction_identifiers.uid
 +
from i5_mitab_sources
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  and i5_mitab_interactions.taxA = 9606
 +
  and i5_mitab_interactions.taxB = 9606
 +
inner join i5_mitab_interaction_identifiers
 +
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
where i5_mitab_sources.name = 'bind'
 +
  and i5_mitab_interaction_identifiers.uid like 'bind:%'
 +
  and i5_mitab_sources.rigid not in (
 +
    select distinct mitab_sources.rigid
 +
    from mitab_sources
 +
    inner join mitab_interactions
 +
      on mitab_sources.uidA = mitab_interactions.uidA
 +
      and mitab_sources.uidB = mitab_interactions.uidB
 +
      and mitab_sources.rigid = mitab_interactions.rigid
 +
      and mitab_interactions.taxA = 9606
 +
      and mitab_interactions.taxB = 9606
 +
    inner join mitab_interaction_identifiers
 +
      on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
      and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
      and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
    where mitab_sources.name = 'bind'
 +
    );
 +
</div>
 +
 +
<div id="i5_bind_plus">BIND identifiers and RIG identifiers from the above:
 +
 +
create table export_i5_bind_plus as
 +
select distinct i5_mitab_interaction_identifiers.uid, i5_mitab_interaction_identifiers.rigid
 +
from i5_mitab_sources
 +
inner join i5_mitab_interactions
 +
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
  and i5_mitab_interactions.taxA = 9606
 +
  and i5_mitab_interactions.taxB = 9606
 +
inner join i5_mitab_interaction_identifiers
 +
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
where i5_mitab_sources.name = 'bind'
 +
  and i5_mitab_interaction_identifiers.uid like 'bind:%'
 +
  and i5_mitab_sources.rigid not in (
 +
    select distinct mitab_sources.rigid
 +
    from mitab_sources
 +
    inner join mitab_interactions
 +
      on mitab_sources.uidA = mitab_interactions.uidA
 +
      and mitab_sources.uidB = mitab_interactions.uidB
 +
      and mitab_sources.rigid = mitab_interactions.rigid
 +
      and mitab_interactions.taxA = 9606
 +
      and mitab_interactions.taxB = 9606
 +
    inner join mitab_interaction_identifiers
 +
      on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
      and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
      and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
    where mitab_sources.name = 'bind'
 +
    );
 +
</div>
 +
 +
<div id="xml_bind">BIND identifiers '''for interactions''' only in BIND PSI-XML:
 +
 +
create table export_xml_bind as
 +
select distinct mitab_interaction_identifiers.uid
 +
from mitab_sources
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
  and mitab_interactions.taxA = 9606
 +
  and mitab_interactions.taxB = 9606
 +
inner join mitab_interaction_identifiers
 +
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
where mitab_sources.name = 'bind'
 +
  and mitab_interaction_identifiers.uid like 'bind:%'
 +
  and mitab_sources.rigid not in (
 +
    select distinct i5_mitab_sources.rigid
 +
    from i5_mitab_sources
 +
    inner join i5_mitab_interactions
 +
      on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
      and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
      and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
      and i5_mitab_interactions.taxA = 9606
 +
      and i5_mitab_interactions.taxB = 9606
 +
    inner join i5_mitab_interaction_identifiers
 +
      on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
      and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
      and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
    where i5_mitab_sources.name = 'bind'
 +
    );
 +
</div>
 +
 +
<div id="xml_bind_plus">BIND identifiers and RIG identifiers from the above:
 +
 +
create table export_xml_bind_plus as
 +
select distinct mitab_interaction_identifiers.uid, mitab_interaction_identifiers.rigid
 +
from mitab_sources
 +
inner join mitab_interactions
 +
  on mitab_sources.uidA = mitab_interactions.uidA
 +
  and mitab_sources.uidB = mitab_interactions.uidB
 +
  and mitab_sources.rigid = mitab_interactions.rigid
 +
  and mitab_interactions.taxA = 9606
 +
  and mitab_interactions.taxB = 9606
 +
inner join mitab_interaction_identifiers
 +
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
where mitab_sources.name = 'bind'
 +
  and mitab_interaction_identifiers.uid like 'bind:%'
 +
  and mitab_sources.rigid not in (
 +
    select distinct i5_mitab_sources.rigid
 +
    from i5_mitab_sources
 +
    inner join i5_mitab_interactions
 +
      on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
      and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
      and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
      and i5_mitab_interactions.taxA = 9606
 +
      and i5_mitab_interactions.taxB = 9606
 +
    inner join i5_mitab_interaction_identifiers
 +
      on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
      and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
      and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
    where i5_mitab_sources.name = 'bind'
 +
    );
 +
</div>
 +
 +
<div id="i5_common_bind">BIND identifiers referring to different interactions in iRefIndex and common data:
 +
 +
create table export_i5_common_bind as
 +
select distinct I.uid, I.rigid as i5_rigid, C.rigid as common_rigid
 +
from export_i5_bind_plus as I
 +
inner join export_common_bind_plus as C
 +
  on I.uid = C.uid
 +
order by I.uid, I.rigid, C.rigid;
 +
</div>
 +
 +
<div id="i5_xml_bind">BIND identifiers referring to different interactions in iRefIndex and BIND PSI-XML data:
 +
 +
create table export_i5_xml_bind as
 +
select distinct I.uid, I.rigid as i5_rigid, X.rigid as xml_rigid
 +
from export_i5_bind_plus as I
 +
inner join export_xml_bind_plus as X
 +
  on I.uid = X.uid
 +
order by I.uid, I.rigid, X.rigid;
 +
</div>
 +
 +
<div id="i5_common_bind">BIND identifiers referring to different interactions in BIND PSI-XML and common data:
 +
 +
create table export_xml_common_bind as
 +
select distinct X.uid, X.rigid as xml_rigid, C.rigid as common_rigid
 +
from export_xml_bind_plus as X
 +
inner join export_common_bind_plus as C
 +
  on X.uid = C.uid
 +
order by X.uid, X.rigid, C.rigid;
 +
</div>
 +
 +
BIND identifier coverage for '''any usage''' of an identifier:
 +
 +
create table export_identifiers_bind as
 +
select coalesce(A.uid, B.uid) as uid, (case when A.uid is not null then 1 else 0 end) as irefindex, (case when B.uid is not null then 1 else 0 end) as bindpsixml
 +
from (
 +
  select uid
 +
  from i5_mitab_interaction_identifiers as A
 +
  where A.uid like 'bind:%'
 +
  ) as A
 +
full outer join (
 +
  select uid
 +
  from mitab_interaction_identifiers as B
 +
  where B.uid like 'bind:%'
 +
  ) as B
 +
  on A.uid = B.uid
 +
order by A.uid, B.uid;
 +
 +
BIND identifier coverage '''for human interactions''':
 +
 +
create table export_identifiers_human_bind as
 +
select coalesce(A.uid, B.uid) as uid, (case when A.uid is not null then 1 else 0 end) as irefindex, (case when B.uid is not null then 1 else 0 end) as bindpsixml
 +
from (
 +
  select distinct i5_mitab_interaction_identifiers.uid
 +
  from i5_mitab_sources
 +
  inner join i5_mitab_interactions
 +
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
 +
    and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
 +
    and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
 +
    and i5_mitab_interactions.taxA = 9606
 +
    and i5_mitab_interactions.taxB = 9606
 +
  inner join i5_mitab_interaction_identifiers
 +
    on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
 +
    and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
 +
    and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
 +
  where i5_mitab_sources.name = 'bind'
 +
    and i5_mitab_interaction_identifiers.uid like 'bind:%'
 +
  ) as A
 +
full outer join (
 +
  select distinct mitab_interaction_identifiers.uid
 +
  from mitab_sources
 +
  inner join mitab_interactions
 +
    on mitab_sources.uidA = mitab_interactions.uidA
 +
    and mitab_sources.uidB = mitab_interactions.uidB
 +
    and mitab_sources.rigid = mitab_interactions.rigid
 +
    and mitab_interactions.taxA = 9606
 +
    and mitab_interactions.taxB = 9606
 +
  inner join mitab_interaction_identifiers
 +
    on mitab_sources.uidA = mitab_interaction_identifiers.uidA
 +
    and mitab_sources.uidB = mitab_interaction_identifiers.uidB
 +
    and mitab_sources.rigid = mitab_interaction_identifiers.rigid
 +
  where mitab_sources.name = 'bind'
 +
    and mitab_interaction_identifiers.uid like 'bind:%'
 +
  ) as B
 +
  on A.uid = B.uid
 +
order by A.uid, B.uid;
  
 
== Other Stuff ==
 
== Other Stuff ==

Latest revision as of 13:43, 26 October 2009

Preliminary BIND PSI-XML parsing results.

Results

Importing MITAB output from the iRefIndex 5.1 build and a build done using the BIND PSI-XML data, then comparing the interaction details for BIND, the following distributions are observed:

Interactions (Human)

iRefIndex 5.1
(Total) (7122)
Exclusive 1324
PSI-XML (8706) 2908 5798 (Common)

To summarise:

  • Common human interactions: 5798
  • Human interactions only in iRefIndex 5.1: 1324
  • Human interactions only in BIND PSI-XML: 2908

Interactors (Human)

iRefIndex 5.1
(Total) (5669)
Exclusive 1450
PSI-XML (5152) 933 4219 (Common)

To summarise:

  • Common interactors: 4219
  • Interactors only in iRefIndex 5.1: 1450
  • Interactors only in BIND PSI-XML: 933

BIND Identifiers (Human Interactions)

There are 183614 distinct human interaction identifiers in the iRefIndex 5.1 release, 188690 in the BIND PSI-XML dataset.

There are 10431 distinct human BIND interaction identifiers in the iRefIndex 5.1 release, 12093 in the BIND PSI-XML dataset.

The following distribution of BIND human interaction identifiers was observed:

iRefIndex 5.1
(Total) (10431)
Exclusive 673
PSI-XML (12093) 2335 9758 (Common)

In other words:

  • There are 9758 identifiers in common.
  • 673 identifiers only appear in the iRefIndex 5.1 release.
  • 2335 identifiers only appear in the BIND PSI-XML dataset.

Where the correspondence between identifiers and interactions is considered, it might be expected that BIND identifiers should not refer to different interactions. However, if we take the BIND identifiers for interactions exclusive to the iRefIndex 5.1 release and the BIND PSI-XML dataset, as well as for those common to both sources, we see that some identifiers are associated with interactions from these disjoint sets:

Common iRefIndex 5.1 only BIND PSI-XML only
Common (8639)
iRefIndex 5.1 only 76 (1613)
BIND PSI-XML only 0 1088 (3248)

In other words:

  • There are 8639 common BIND identifiers in total.
  • There are 1613 BIND identifiers appearing for interactions only in iRefIndex 5.1.
  • There are 3248 BIND identifiers appearing for interactions only in the BIND PSI-XML dataset.
  • Although the intersection between "iRefIndex 5.1 only" and "BIND PSI-XML only" should yield zero interactions, since by definition an interaction in one of these sets should not appear in another, 1088 BIND identifiers appear in both.
  • Of the interactions that should only be referenced by iRefIndex 5.1, 76 BIND identifiers also refer to interactions in both iRefIndex 5.1 and the BIND PSI-XML dataset.
  • Of the interactions that should only be referenced by the BIND PSI-XML dataset, 0 BIND identifiers also refer to interactions in both iRefIndex 5.1 and the BIND PSI-XML dataset. This is as expected.

The export_i5_common_bind and export_i5_xml_bind result sets contain examples of such BIND identifiers which refer to different interactions. For example:

  • BIND identifier 100771 refers to 5TlbBV7VB0MFa/R1cmyeByxSFjw in only the iRefIndex 5.1 release, but also to pXZtvCDYsluwWrmdXf5FJmQ0y54 in that release and in the BIND PSI-XML dataset.
  • BIND identifier 100321 refers to VwWuNAmhR/FDx6FtF6FacwAmzr0 in only the iRefIndex 5.1 release, but to Dr5ri7vTj02leEUMKr35jj1fP/c in only the BIND PSI-XML dataset.

See bug #133 for further details and discussion.

See bug #135 for details and discussion of the broader activity.

Queries

All human interactions in iRefIndex 5.1:
select count(distinct i5_mitab_interactions.rigid)
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and taxA = 9606
  and taxB = 9606
where i5_mitab_sources.name = 'bind';
All human interactions in BIND PSI-XML:
select count(distinct mitab_interactions.rigid)
from mitab_sources
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and taxA = 9606
  and taxB = 9606
where mitab_sources.name = 'bind';
Common human interactions (join the above queries on rigid):
select count(distinct mitab_sources.rigid)
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interactions.taxA = 9606
  and i5_mitab_interactions.taxB = 9606
inner join mitab_sources
  on i5_mitab_sources.rigid = mitab_sources.rigid
  and i5_mitab_sources.name = mitab_sources.name
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and mitab_interactions.taxA = 9606
  and mitab_interactions.taxB = 9606
where mitab_sources.name = 'bind';
Human interactions only in iRefIndex 5.1:
select count(distinct i5_mitab_sources.rigid)
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interactions.taxA = 9606
  and i5_mitab_interactions.taxB = 9606
where i5_mitab_sources.name = 'bind'
  and i5_mitab_sources.rigid not in (
    select distinct mitab_sources.rigid
    from mitab_sources
    inner join mitab_interactions
      on mitab_sources.uidA = mitab_interactions.uidA
      and mitab_sources.uidB = mitab_interactions.uidB
      and mitab_sources.rigid = mitab_interactions.rigid
      and mitab_interactions.taxA = 9606
      and mitab_interactions.taxB = 9606
    where mitab_sources.name = 'bind'
    );
Human interactions only in BIND PSI-XML:
select count(distinct mitab_sources.rigid)
from mitab_sources
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and mitab_interactions.taxA = 9606
  and mitab_interactions.taxB = 9606
where mitab_sources.name = 'bind'
  and mitab_sources.rigid not in (
    select distinct i5_mitab_sources.rigid
    from i5_mitab_sources
    inner join i5_mitab_interactions
      on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
      and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
      and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
      and i5_mitab_interactions.taxA = 9606
      and i5_mitab_interactions.taxB = 9606
    where i5_mitab_sources.name = 'bind'
    );

Common interactors:

select count(distinct X.uid)
from (
  select i5_mitab_sources.uidA as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select i5_mitab_sources.uidB as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  ) as X
inner join (
  select mitab_sources.uidA as uid
  from mitab_sources
  inner join mitab_interactions
    on mitab_sources.uidA = mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select mitab_sources.uidB as uid
  from mitab_sources
  inner join mitab_interactions
    on mitab_sources.uidB = mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  ) as Y
  on X.uid = Y.uid;

Interactors only in iRefIndex 5.1:

select count(distinct X.uid)
from (
  select i5_mitab_sources.uidA as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select i5_mitab_sources.uidB as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  ) as X
where X.uid not in (
  select mitab_sources.uidA as uid
  from mitab_sources
  inner join mitab_interactions
    on mitab_sources.uidA = mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select mitab_sources.uidB as uid
  from mitab_sources
  inner join mitab_interactions
    on mitab_sources.uidB = mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  );

Interactors only in BIND PSI-XML:

select count(distinct X.uid)
from (
  select mitab_sources.uidA as uid
  from mitab_sources
  inner join mitab_interactions
    on mitab_sources.uidA = mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select mitab_sources.uidB as uid
  from mitab_sources
  inner join mitab_interactions
    on mitab_sources.uidB = mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  ) as X
where X.uid not in (
  select i5_mitab_sources.uidA as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  where name = 'bind' and taxA = 9606
  union
  select i5_mitab_sources.uidB as uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  where name = 'bind' and taxB = 9606
  );

Retrieving BIND Identifiers

The following queries determine the appropriate set of interactions and then derive the BIND identifiers from them.

All distinct human interaction identifiers in iRefIndex 5.1:
select count(distinct uid)
from mitab_interaction_identifiers
inner join mitab_interactions
  on mitab_interaction_identifiers.rigid = mitab_interactions.rigid
  and mitab_interaction_identifiers.uidA = mitab_interactions.uidA
  and mitab_interaction_identifiers.uidB = mitab_interactions.uidB
  and taxA = 9606
  and taxB = 9606;

All distinct human interaction identifiers in BIND PSI-XML:

select count(distinct uid)
from i5_mitab_interaction_identifiers
inner join i5_mitab_interactions
  on i5_mitab_interaction_identifiers.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interaction_identifiers.uidA = i5_mitab_interactions.uidA
  and i5_mitab_interaction_identifiers.uidB = i5_mitab_interactions.uidB
  and taxA = 9606
  and taxB = 9606;
All BIND identifiers for human interactions in iRefIndex 5.1:
create table export_all_i5_bind as
select distinct i5_mitab_interaction_identifiers.uid
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interactions.taxA = 9606
  and i5_mitab_interactions.taxB = 9606
inner join i5_mitab_interaction_identifiers
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
where name = 'bind' and uid like 'bind:%';

All BIND identifiers for human interactions in BIND PSI-XML:

create table export_all_xml_bind as
select distinct mitab_interaction_identifiers.uid
from mitab_sources
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and mitab_interactions.taxA = 9606
  and mitab_interactions.taxB = 9606
inner join mitab_interaction_identifiers
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
where name = 'bind' and uid like 'bind:%';
Common BIND identifiers for human interactions:
create table export_all_common_bind as
select distinct export_all_i5_bind.uid
from export_all_i5_bind
inner join export_all_xml_bind
  on export_all_i5_bind.uid = export_all_xml_bind.uid;
BIND identifiers for human interactions only in iRefIndex 5.1:
select distinct uid
from export_all_i5_bind
where uid not in (
  select uid
  from export_all_xml_bind
  );
BIND identifiers for human interactions only in PSI-XML:
select distinct uid
from export_all_xml_bind
where uid not in (
  select uid
  from export_all_i5_bind
  );
BIND identifiers for common interactions where those interactions are known by the same identifier (join the above queries on rigid, matching the uid values):
create table export_common_bind as
select distinct i5_mitab_interaction_identifiers.uid
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interactions.taxA = 9606
  and i5_mitab_interactions.taxB = 9606
inner join i5_mitab_interaction_identifiers
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
inner join mitab_sources
  on i5_mitab_sources.rigid = mitab_sources.rigid
  and i5_mitab_sources.name = mitab_sources.name
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and mitab_interactions.taxA = 9606
  and mitab_interactions.taxB = 9606
inner join mitab_interaction_identifiers
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
where mitab_sources.name = 'bind'
  and i5_mitab_interaction_identifiers.uid = mitab_interaction_identifiers.uid
  and i5_mitab_interaction_identifiers.uid like 'bind:%';
BIND identifiers and RIG identifiers from the above:
create table export_common_bind_plus as
select distinct i5_mitab_interaction_identifiers.uid, i5_mitab_interaction_identifiers.rigid
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interactions.taxA = 9606
  and i5_mitab_interactions.taxB = 9606
inner join i5_mitab_interaction_identifiers
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
inner join mitab_sources
 on i5_mitab_sources.rigid = mitab_sources.rigid
 and i5_mitab_sources.name = mitab_sources.name
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and mitab_interactions.taxA = 9606
  and mitab_interactions.taxB = 9606
inner join mitab_interaction_identifiers
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
where mitab_sources.name = 'bind'
  and i5_mitab_interaction_identifiers.uid = mitab_interaction_identifiers.uid
  and i5_mitab_interaction_identifiers.uid like 'bind:%';
BIND identifiers for interactions only in iRefIndex 5.1:
create table export_i5_bind as
select distinct i5_mitab_interaction_identifiers.uid
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interactions.taxA = 9606
  and i5_mitab_interactions.taxB = 9606
inner join i5_mitab_interaction_identifiers
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
where i5_mitab_sources.name = 'bind'
  and i5_mitab_interaction_identifiers.uid like 'bind:%'
  and i5_mitab_sources.rigid not in (
    select distinct mitab_sources.rigid
    from mitab_sources
    inner join mitab_interactions
      on mitab_sources.uidA = mitab_interactions.uidA
      and mitab_sources.uidB = mitab_interactions.uidB
      and mitab_sources.rigid = mitab_interactions.rigid
      and mitab_interactions.taxA = 9606
      and mitab_interactions.taxB = 9606
    inner join mitab_interaction_identifiers
      on mitab_sources.uidA = mitab_interaction_identifiers.uidA
      and mitab_sources.uidB = mitab_interaction_identifiers.uidB
      and mitab_sources.rigid = mitab_interaction_identifiers.rigid
    where mitab_sources.name = 'bind'
    );
BIND identifiers and RIG identifiers from the above:
create table export_i5_bind_plus as
select distinct i5_mitab_interaction_identifiers.uid, i5_mitab_interaction_identifiers.rigid
from i5_mitab_sources
inner join i5_mitab_interactions
  on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
  and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
  and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
  and i5_mitab_interactions.taxA = 9606
  and i5_mitab_interactions.taxB = 9606
inner join i5_mitab_interaction_identifiers
  on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
  and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
  and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
where i5_mitab_sources.name = 'bind'
  and i5_mitab_interaction_identifiers.uid like 'bind:%'
  and i5_mitab_sources.rigid not in (
    select distinct mitab_sources.rigid
    from mitab_sources
    inner join mitab_interactions
      on mitab_sources.uidA = mitab_interactions.uidA
      and mitab_sources.uidB = mitab_interactions.uidB
      and mitab_sources.rigid = mitab_interactions.rigid
      and mitab_interactions.taxA = 9606
      and mitab_interactions.taxB = 9606
    inner join mitab_interaction_identifiers
      on mitab_sources.uidA = mitab_interaction_identifiers.uidA
      and mitab_sources.uidB = mitab_interaction_identifiers.uidB
      and mitab_sources.rigid = mitab_interaction_identifiers.rigid
    where mitab_sources.name = 'bind'
    );
BIND identifiers for interactions only in BIND PSI-XML:
create table export_xml_bind as
select distinct mitab_interaction_identifiers.uid
from mitab_sources
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and mitab_interactions.taxA = 9606
  and mitab_interactions.taxB = 9606
inner join mitab_interaction_identifiers
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
where mitab_sources.name = 'bind'
  and mitab_interaction_identifiers.uid like 'bind:%'
  and mitab_sources.rigid not in (
    select distinct i5_mitab_sources.rigid
    from i5_mitab_sources
    inner join i5_mitab_interactions
      on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
      and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
      and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
      and i5_mitab_interactions.taxA = 9606
      and i5_mitab_interactions.taxB = 9606
    inner join i5_mitab_interaction_identifiers
      on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
      and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
      and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
    where i5_mitab_sources.name = 'bind'
    );
BIND identifiers and RIG identifiers from the above:
create table export_xml_bind_plus as
select distinct mitab_interaction_identifiers.uid, mitab_interaction_identifiers.rigid
from mitab_sources
inner join mitab_interactions
  on mitab_sources.uidA = mitab_interactions.uidA
  and mitab_sources.uidB = mitab_interactions.uidB
  and mitab_sources.rigid = mitab_interactions.rigid
  and mitab_interactions.taxA = 9606
  and mitab_interactions.taxB = 9606
inner join mitab_interaction_identifiers
  on mitab_sources.uidA = mitab_interaction_identifiers.uidA
  and mitab_sources.uidB = mitab_interaction_identifiers.uidB
  and mitab_sources.rigid = mitab_interaction_identifiers.rigid
where mitab_sources.name = 'bind'
  and mitab_interaction_identifiers.uid like 'bind:%'
  and mitab_sources.rigid not in (
    select distinct i5_mitab_sources.rigid
    from i5_mitab_sources
    inner join i5_mitab_interactions
      on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
      and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
      and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
      and i5_mitab_interactions.taxA = 9606
      and i5_mitab_interactions.taxB = 9606
    inner join i5_mitab_interaction_identifiers
      on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
      and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
      and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
    where i5_mitab_sources.name = 'bind'
    );
BIND identifiers referring to different interactions in iRefIndex and common data:
create table export_i5_common_bind as
select distinct I.uid, I.rigid as i5_rigid, C.rigid as common_rigid
from export_i5_bind_plus as I
inner join export_common_bind_plus as C
  on I.uid = C.uid
order by I.uid, I.rigid, C.rigid;
BIND identifiers referring to different interactions in iRefIndex and BIND PSI-XML data:
create table export_i5_xml_bind as
select distinct I.uid, I.rigid as i5_rigid, X.rigid as xml_rigid
from export_i5_bind_plus as I
inner join export_xml_bind_plus as X
  on I.uid = X.uid
order by I.uid, I.rigid, X.rigid;
BIND identifiers referring to different interactions in BIND PSI-XML and common data:
create table export_xml_common_bind as
select distinct X.uid, X.rigid as xml_rigid, C.rigid as common_rigid
from export_xml_bind_plus as X
inner join export_common_bind_plus as C
  on X.uid = C.uid
order by X.uid, X.rigid, C.rigid;

BIND identifier coverage for any usage of an identifier:

create table export_identifiers_bind as
select coalesce(A.uid, B.uid) as uid, (case when A.uid is not null then 1 else 0 end) as irefindex, (case when B.uid is not null then 1 else 0 end) as bindpsixml
from (
  select uid
  from i5_mitab_interaction_identifiers as A
  where A.uid like 'bind:%'
  ) as A
full outer join (
  select uid
  from mitab_interaction_identifiers as B
  where B.uid like 'bind:%'
  ) as B
  on A.uid = B.uid
order by A.uid, B.uid;

BIND identifier coverage for human interactions:

create table export_identifiers_human_bind as
select coalesce(A.uid, B.uid) as uid, (case when A.uid is not null then 1 else 0 end) as irefindex, (case when B.uid is not null then 1 else 0 end) as bindpsixml
from (
  select distinct i5_mitab_interaction_identifiers.uid
  from i5_mitab_sources
  inner join i5_mitab_interactions
    on i5_mitab_sources.uidA = i5_mitab_interactions.uidA
    and i5_mitab_sources.uidB = i5_mitab_interactions.uidB
    and i5_mitab_sources.rigid = i5_mitab_interactions.rigid
    and i5_mitab_interactions.taxA = 9606
    and i5_mitab_interactions.taxB = 9606
  inner join i5_mitab_interaction_identifiers
    on i5_mitab_sources.uidA = i5_mitab_interaction_identifiers.uidA
    and i5_mitab_sources.uidB = i5_mitab_interaction_identifiers.uidB
    and i5_mitab_sources.rigid = i5_mitab_interaction_identifiers.rigid
  where i5_mitab_sources.name = 'bind'
    and i5_mitab_interaction_identifiers.uid like 'bind:%'
  ) as A
full outer join (
  select distinct mitab_interaction_identifiers.uid
  from mitab_sources
  inner join mitab_interactions
    on mitab_sources.uidA = mitab_interactions.uidA
    and mitab_sources.uidB = mitab_interactions.uidB
    and mitab_sources.rigid = mitab_interactions.rigid
    and mitab_interactions.taxA = 9606
    and mitab_interactions.taxB = 9606
  inner join mitab_interaction_identifiers
    on mitab_sources.uidA = mitab_interaction_identifiers.uidA
    and mitab_sources.uidB = mitab_interaction_identifiers.uidB
    and mitab_sources.rigid = mitab_interaction_identifiers.rigid
  where mitab_sources.name = 'bind'
    and mitab_interaction_identifiers.uid like 'bind:%'
  ) as B
  on A.uid = B.uid
order by A.uid, B.uid;

Other Stuff

This is what I did for the unnecessary "new BIND vs. other sources" experiment...

Downloaded the following:

  • BioGrid
  • CORUM
  • DIP
  • HPRD
  • Intact
  • MINT
  • MIPS (MPPI)
  • OPHID

Modified the following:

  • BioGrid - removed all but human data

Parsed all sources mentioned above.

Removed all non-9606 records.

Did ROG assignment and so on.

Made the reports.