iRefIndex/PBR Notes

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.